3 # create dbdef file for existing mySQL database (needs SHOW|DESCRIBE command
4 # not in Pg) based on fs-setup
6 # ivan@sisd.com 98-jun-2
11 use FS::UID qw(adminsuidsetup datasrc);
13 #needs to match FS::Record
14 my($dbdef_file) = "/var/spool/freeside/dbdef.". datasrc;
16 my($dbh)=adminsuidsetup;
18 my($tables_sth)=$dbh->prepare("SHOW TABLES");
19 my($tables_rv)=$tables_sth->execute;
22 foreach ( @{$tables_sth->fetchall_arrayref} ) {
24 #print "TABLE\t$table\n";
26 my($index_sth)=$dbh->prepare("SHOW INDEX FROM $table");
29 for ( 1 .. $index_sth->execute ) {
30 my($row)=$index_sth->fetchrow_hashref;
31 if ( ${$row}{'Key_name'} eq "PRIMARY" ) {
32 $primary_key=${$row}{'Column_name'};
35 if ( ${$row}{'Non_unique'} ) { #index
36 push @{$index{${$row}{'Key_name'}}}, ${$row}{'Column_name'};
38 push @{$unique{${$row}{'Key_name'}}}, ${$row}{'Column_name'};
42 my(@index)=values %index;
43 my(@unique)=values %unique;
44 #print "\tPRIMARY KEY $primary_key\n";
46 #print "\tINDEX\t", join(', ', @{$_}), "\n";
49 #print "\tUNIQUE\t", join(', ', @{$_}), "\n";
52 my($columns_sth)=$dbh->prepare("SHOW COLUMNS FROM $table");
54 for ( 1 .. $columns_sth->execute ) {
55 my($row)=$columns_sth->fetchrow_hashref;
56 #print "\t", ${$row}{'Field'}, "\n";
57 ${$row}{'Type'} =~ /^(\w+)\(?([\d\,]+)?\)?( unsigned)?$/
58 or die "Illegal type ${$row}{'Type'}\n";
59 my($type,$length)=($1,$2);
60 my($null)=${$row}{'Null'};
62 push @columns, new FS::dbdef_column (
71 push @tables, new FS::dbdef_table (
74 new FS::dbdef_unique (\@unique),
75 new FS::dbdef_index (\@index),
81 my($dbdef) = new FS::dbdef ( @tables );
84 $dbdef->save($dbdef_file);