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