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
|
#!/usr/bin/perl -Tw
#
# create dbdef file for existing mySQL database (needs SHOW|DESCRIBE command
# not in Pg) based on fs-setup
#
# ivan@sisd.com 98-jun-2
use strict;
use DBI;
use FS::dbdef;
use FS::UID qw(adminsuidsetup datasrc);
#needs to match FS::Record
my($dbdef_file) = "/var/spool/freeside/dbdef.". datasrc;
my($dbh)=adminsuidsetup;
my($tables_sth)=$dbh->prepare("SHOW TABLES");
my($tables_rv)=$tables_sth->execute;
my(@tables);
foreach ( @{$tables_sth->fetchall_arrayref} ) {
my($table)=${$_}[0];
#print "TABLE\t$table\n";
my($index_sth)=$dbh->prepare("SHOW INDEX FROM $table");
my($primary_key)='';
my(%index,%unique);
for ( 1 .. $index_sth->execute ) {
my($row)=$index_sth->fetchrow_hashref;
if ( ${$row}{'Key_name'} eq "PRIMARY" ) {
$primary_key=${$row}{'Column_name'};
next;
}
if ( ${$row}{'Non_unique'} ) { #index
push @{$index{${$row}{'Key_name'}}}, ${$row}{'Column_name'};
} else { #unique
push @{$unique{${$row}{'Key_name'}}}, ${$row}{'Column_name'};
}
}
my(@index)=values %index;
my(@unique)=values %unique;
#print "\tPRIMARY KEY $primary_key\n";
foreach (@index) {
#print "\tINDEX\t", join(', ', @{$_}), "\n";
}
foreach (@unique) {
#print "\tUNIQUE\t", join(', ', @{$_}), "\n";
}
my($columns_sth)=$dbh->prepare("SHOW COLUMNS FROM $table");
my(@columns);
for ( 1 .. $columns_sth->execute ) {
my($row)=$columns_sth->fetchrow_hashref;
#print "\t", ${$row}{'Field'}, "\n";
${$row}{'Type'} =~ /^(\w+)\(?([\d\,]+)?\)?( unsigned)?$/
or die "Illegal type ${$row}{'Type'}\n";
my($type,$length)=($1,$2);
my($null)=${$row}{'Null'};
$null =~ s/YES/NULL/;
push @columns, new FS::dbdef_column (
${$row}{'Field'},
$type,
$null,
$length,
);
}
#print "\n";
push @tables, new FS::dbdef_table (
$table,
$primary_key,
new FS::dbdef_unique (\@unique),
new FS::dbdef_index (\@index),
@columns,
);
}
my($dbdef) = new FS::dbdef ( @tables );
#important
$dbdef->save($dbdef_file);
|
