283 lines
10 KiB
Perl
283 lines
10 KiB
Perl
# ====================================================================
|
|
# Gossamer Threads Module Library - http://gossamer-threads.com/
|
|
#
|
|
# GT::SQL::Upgrade
|
|
# Author: Jason Rhinelander
|
|
# CVS Info : 087,071,086,086,085
|
|
# $Id: Upgrade.pm,v 1.7 2008/09/23 23:55:26 brewt Exp $
|
|
#
|
|
# Copyright (c) 2005 Gossamer Threads Inc. All Rights Reserved.
|
|
# ====================================================================
|
|
#
|
|
# Description:
|
|
# Various commonly used SQL upgrade functions used by GT product upgrades.
|
|
#
|
|
|
|
package GT::SQL::Upgrade;
|
|
use strict;
|
|
use vars qw/@ISA @EXPORT $VERSION/;
|
|
require Exporter;
|
|
|
|
# You *must* bump this each time you change or fix any of the code this file or
|
|
# it is guaranteed to cause problems:
|
|
$VERSION = 1.01;
|
|
|
|
@ISA = 'Exporter';
|
|
@EXPORT = qw/add_column alter_column drop_column add_index add_unique drop_index add_table recreate_table/;
|
|
|
|
# Adds a column. Takes 5 args:
|
|
# Output coderef, database object, table name, column name, column definition
|
|
# Returns the return of $editor->add_col
|
|
sub add_column {
|
|
my ($out, $db, $table, $col, $def) = @_;
|
|
$out->("Adding column $col to $table table...\n");
|
|
my $ret = $db->editor($table)->add_col($col => $def);
|
|
$out->($ret ? "\tOkay!\n" : "\tCould not add column $col: $GT::SQL::error\n");
|
|
$ret;
|
|
}
|
|
|
|
# Changes a column. Takes 5 args:
|
|
# Output coderef, database obj, table name, column name, new column definition
|
|
sub alter_column {
|
|
my ($out, $db, $table, $col, $def) = @_;
|
|
$out->("Updating column definition for $col in $table table...\n");
|
|
my $ret = $db->editor($table)->alter_col($col, $def);
|
|
$out->($ret ? "\tOkay!\n" : "\tCould not alter column $col: $GT::SQL::error\n");
|
|
$ret;
|
|
}
|
|
|
|
# Drops a column. Takes 4 args:
|
|
# Output coderef, database object, table name, column name
|
|
# Returns the return of $editor->drop_col
|
|
sub drop_column {
|
|
my ($out, $db, $table, $col) = @_;
|
|
$out->("Dropping column '$col' from table '$table'...\n");
|
|
my $ret = $db->editor($table)->drop_col($col);
|
|
$out->($ret ? "\tOkay!\n" : "\tCould not drop column $col: $GT::SQL::error\n");
|
|
$ret;
|
|
}
|
|
|
|
# Adds indexes. Takes 4-5 args
|
|
# Output coderef, database object, table name, indexes hash reference, and an
|
|
# optional boolean value to make the added indexes unique indexes.
|
|
# Returns the return of $editor->add_index
|
|
sub add_index {
|
|
my ($out, $db, $table, $indexes, $unique) = @_;
|
|
my $editor = $db->editor($table);
|
|
my $cret = 1;
|
|
while (my ($idx, $defn) = each %$indexes) {
|
|
my ($meth, $index_display) = $unique ? (add_unique => 'unique index') : (add_index => 'index');
|
|
$out->("Adding $index_display '$idx' to '$table' table...\n");
|
|
my $ret = $editor->$meth($idx => $indexes->{$idx});
|
|
$out->($ret ? "\tOkay!\n" : "\tCould not add $index_display '$idx': $GT::SQL::error\n");
|
|
$cret = $ret unless $ret;
|
|
}
|
|
$cret;
|
|
}
|
|
|
|
# A simple alias for add_index(..., 1);
|
|
sub add_unique {
|
|
push @_, 1;
|
|
goto &add_index;
|
|
}
|
|
|
|
# Drops an index. Takes 4-5 args:
|
|
# Output coderef, GT::SQL obj, table name, index name, plus an optional boolean
|
|
# value to indicate that the index to drop is a unique index.
|
|
sub drop_index {
|
|
my ($out, $db, $table, $index, $unique) = @_;
|
|
$out->("Dropping index '$index' from '$table' table...\n");
|
|
my $editor = $db->editor($table);
|
|
my $meth = $unique ? 'drop_unique' : 'drop_index';
|
|
my $ret = $editor->$meth($index);
|
|
$out->($ret ? "\tOkay!\n" : "\tCould not drop index '$index': $GT::SQL::error\n");
|
|
$ret;
|
|
}
|
|
|
|
# Adds a table. Takes 3 base, plus unlimited extra arguments:
|
|
# Output coderef, GT::SQL obj, table name
|
|
# Other arguments are read in pairs - the first is a ::Creator method name, the
|
|
# second is the value to pass to the method.
|
|
sub add_table {
|
|
my ($out, $db, $table) = splice @_, 0, 3;
|
|
|
|
$out->("Adding table '$table'...\n");
|
|
my $c = $db->creator($table);
|
|
|
|
while (@_) {
|
|
my ($meth, $arg) = splice @_, 0, 2;
|
|
$c->$meth($arg);
|
|
}
|
|
|
|
my $ret = $c->create;
|
|
if ($ret) {
|
|
$out->("\tOkay!\n");
|
|
}
|
|
else {
|
|
$out->("\tAn error occurred: $GT::SQL::error\n");
|
|
$c->set_defaults;
|
|
$c->save_schema;
|
|
}
|
|
$ret;
|
|
}
|
|
|
|
# Used when recreating a table is necessary (used in at least the Links SQL
|
|
# 2.1.2 -> 2.2.0 upgrade) It creates a temporary table, copies all the data
|
|
# into it, then drops the original table, recreates it, and copies all the data
|
|
# back.
|
|
# Usage:
|
|
# recreate_table($out, $db, $table_name, $condition, ...ARGS...);
|
|
# - $out is the code reference to call with output
|
|
# - $db is the GT::SQL object for the database
|
|
# - $table_name is the name of the table to recreated
|
|
# - $condition is a code reference - it will be called with the table as an
|
|
# argument. If it returns true, the table is recreated, otherwise (if it
|
|
# returns false) recreating the table is skipped.
|
|
# - Remaining arguments are specified in pairs - the first of each pair of
|
|
# arguments is the function to call, the second is the argument to pass to
|
|
# that function. At least a "cols => [ ... ]" pair must be specified.
|
|
# Known problems:
|
|
# - The code that copies any custom columns breaks if any columns have been
|
|
# removed from the new table has fewer columns from the old one - those
|
|
# columns will be copied to the new table.
|
|
# - A change adding not_null to a column will only work for INT's/FLOAT's,
|
|
# for which any previous null values are given a value of 0.
|
|
sub recreate_table {
|
|
my ($out, $db, $table_name, $condition) = splice @_, 0, 4;
|
|
@_ % 2 == 0 or die "Invalid arguments. Usage: recreate_table(INSTALLER_OBJ, GTSQL_OBJ, 'Table', method => val, method => val, ...)";
|
|
my @args = @_;
|
|
my %args = @args;
|
|
my @cols = $args{cols};
|
|
my %cols = @cols;
|
|
|
|
my $table = $db->table($table_name);
|
|
|
|
my $success;
|
|
if ($condition->($table)) {
|
|
RECREATE: {
|
|
$out->("Performing required $table_name table recreation...\n");
|
|
|
|
$out->("\t- Creating temporary storage table...\n");
|
|
my @create;
|
|
my %old_cols = $table->cols;
|
|
my %new_cols = @{$args{cols}};
|
|
|
|
my ($count, @denull) = 0;
|
|
for (keys %old_cols) {
|
|
if (
|
|
!$old_cols{$_}->{not_null} and # Didn't have not_null before
|
|
$new_cols{$_} and # Still exists in the new version of the table
|
|
$new_cols{$_}->{not_null} and # not_null present in the new version
|
|
$new_cols{$_}->{type} =~ /^(?:FLOAT|DOUBLE|DECIMAL|\w*INT)$/ # is a numeric type
|
|
) {
|
|
push @denull, $count;
|
|
}
|
|
$count++;
|
|
}
|
|
|
|
# Retain any custom columns:
|
|
for (keys %old_cols) {
|
|
unless ($cols{$_}) {
|
|
push @create, $_ => $old_cols{$_};
|
|
push @cols, $_ => $old_cols{$_};
|
|
$cols{$_} = $old_cols{$_};
|
|
}
|
|
}
|
|
|
|
my $c = $db->creator($table_name . '_tmp');
|
|
$c->cols(@create);
|
|
|
|
# We should probably 'force' the following create, but that is
|
|
# potentially dangerous if the main table isn't recreated properly.
|
|
my $ret = $c->create;
|
|
if ($ret) {
|
|
$out->("\t\tOkay!\n");
|
|
}
|
|
else {
|
|
$out->("\t\tAn error occurred: $GT::SQL::error\n");
|
|
last RECREATE;
|
|
}
|
|
|
|
my $tmp_table = $db->table($table_name . '_tmp');
|
|
|
|
$out->("\t- Copying existing data to temporary table...\n");
|
|
my $sth = $table->select(keys %old_cols);
|
|
my @recs;
|
|
while () {
|
|
my $row = $sth->fetchrow_arrayref;
|
|
if ($row) {
|
|
my @row = @$row;
|
|
for (@denull) {
|
|
$row[$_] = 0 if not defined $row[$_];
|
|
}
|
|
push @recs, \@row;
|
|
}
|
|
if (!$row or @recs >= 1000) {
|
|
$ret = $tmp_table->insert_multiple([keys %old_cols], @recs) if @recs;
|
|
$out->("\t\tAn error occurred: $GT::SQL::error\n") unless $ret;
|
|
@recs = ();
|
|
last if !$row;
|
|
}
|
|
}
|
|
$out->("\t\tOkay!\n");
|
|
|
|
$out->("\t- Dropping $table_name table...\n");
|
|
$ret = $db->editor($table_name)->drop_table;
|
|
if ($ret) {
|
|
$out->("\t\tOkay!\n");
|
|
}
|
|
else {
|
|
$out->("\t\tAn error occurred: $GT::SQL::error\n");
|
|
}
|
|
|
|
$out->("\t- Creating new $table_name table...\n");
|
|
$c = $db->creator($table_name);
|
|
while (@args) {
|
|
my ($method, $value) = (shift @args, shift @args);
|
|
$c->$method($value);
|
|
}
|
|
|
|
$ret = $c->create('force');
|
|
if ($ret) {
|
|
$out->("\t\tOkay!\n");
|
|
}
|
|
else {
|
|
$out->("\t\tAn error occurred: $GT::SQL::error\n");
|
|
last RECREATE;
|
|
}
|
|
|
|
$out->("\t- Copying temporary data back into new table...\n");
|
|
$sth = $tmp_table->select(keys %old_cols);
|
|
@recs = ();
|
|
while () {
|
|
my $row = $sth->fetchrow_arrayref;
|
|
push @recs, [@$row] if $row;
|
|
if (!$row or @recs >= 1000) {
|
|
$ret = $table->insert_multiple([keys %old_cols], @recs) if @recs;
|
|
$out->("\t\tAn error occurred: $GT::SQL::error\n") unless $ret;
|
|
@recs = ();
|
|
last if !$row;
|
|
}
|
|
}
|
|
$out->("\t\tOkay!\n");
|
|
|
|
$out->("\t- Dropping ${table_name}_tmp table...\n");
|
|
$ret = $db->editor("${table_name}_tmp")->drop_table;
|
|
if ($ret) {
|
|
$out->("\t\tOkay!\n");
|
|
}
|
|
else {
|
|
$out->("\t\tAn error occurred: $GT::SQL::error\n");
|
|
}
|
|
|
|
$success = 1;
|
|
}
|
|
|
|
if (!$success) {
|
|
$out->("\tAn error occurred while attempting to recreate $table_name. Procedure aborted.\n");
|
|
}
|
|
}
|
|
}
|
|
|
|
1;
|