bin/create-history-tables

   1 #!/usr/bin/perl -Tw
   2
   3 use strict;
   4 use DBI;
   5 use DBIx::DBSchema 0.21;
   6 use DBIx::DBSchema::Table;
   7 use DBIx::DBSchema::Column;
   8 use DBIx::DBSchema::ColGroup::Unique;
   9 use DBIx::DBSchema::ColGroup::Index;
  10 use FS::UID qw(adminsuidsetup);
  11 use FS::Record qw(dbdef);
  12
  13 my $user = shift or die &usage;
  14 my $dbh = adminsuidsetup $user;
  15
  16 my $schema = dbdef();
  17
  18 #false laziness w/fs-setup
  19 my @tables = scalar(@ARGV)
  20                ? @ARGV
  21                : grep { ! /^(h|pg)_/ } $schema->tables;
  22 foreach my $table ( @tables ) {
  23   next if grep { /^h_$table/ } $schema->tables;
  24   warn "creating history table for $table\n";
  25   my $tableobj = $schema->table($table)
  26     or die "unknown table $table (did you run dbdef-create?)\n";
  27   my $h_tableobj = DBIx::DBSchema::Table->new( {
  28     name        => "h_$table",
  29     primary_key => 'historynum',
  30     unique      => DBIx::DBSchema::ColGroup::Unique->new( [] ),
  31     'index'     => DBIx::DBSchema::ColGroup::Index->new( [
  32                      @{$tableobj->unique->lol_ref},
  33                      @{$tableobj->index->lol_ref}
  34                    ] ),
  35     columns     => [
  36                      DBIx::DBSchema::Column->new( {
  37                        'name'    => 'historynum',
  38                        'type'    => 'serial',
  39                        'null'    => 'NOT NULL',
  40                        'length'  => '',
  41                        'default' => '',
  42                        'local'   => '',
  43                      } ),
  44                      DBIx::DBSchema::Column->new( {
  45                        'name'    => 'history_date',
  46                        'type'    => 'int',
  47                        'null'    => 'NULL',
  48                        'length'  => '',
  49                        'default' => '',
  50                        'local'   => '',
  51                      } ),
  52                      DBIx::DBSchema::Column->new( {
  53                        'name'    => 'history_user',
  54                        'type'    => 'varchar',
  55                        'null'    => 'NOT NULL',
  56                        'length'  => '80',
  57                        'default' => '',
  58                        'local'   => '',
  59                      } ),
  60                      DBIx::DBSchema::Column->new( {
  61                        'name'    => 'history_action',
  62                        'type'    => 'varchar',
  63                        'null'    => 'NOT NULL',
  64                        'length'  => '80',
  65                        'default' => '',
  66                        'local'   => '',
  67                      } ),
  68                      map {
  69                        my $column = $tableobj->column($_);
  70                        $column->type('int')
  71                          if $column->type eq 'serial';
  72                        $column->default('')
  73                          if $column->default =~ /^nextval\(/i;
  74                        ( my $local = $column->local ) =~ s/AUTO_INCREMENT//i;
  75                        $column->local($local);
  76                        $column;
  77                      } $tableobj->columns
  78                    ],
  79   } );
  80   foreach my $statement ( $h_tableobj->sql_create_table($dbh) ) {
  81     $dbh->do( $statement )
  82       or die "CREATE error: ". $dbh->errstr. "\ndoing statement: $statement";
  83   }
  84
  85 }
  86
  87 $dbh->commit or die $dbh->errstr;
  88 $dbh->disconnect or die $dbh->errstr;
  89
  90 sub usage {
  91   die "Usage:\n  create-history-tables user [ table table ... ] \n";
  92 }
  93