[ic] Create a table definition from your existing database tables.
- MYSQL only
the OnE
bicolspice at yahoo.com
Thu Jun 17 18:28:40 EDT 2004
I created a script that can automatically create table
definition for use with Interchange from an existing
MySQL database table. Any bugs or comments.. please
send me an e-mail.
#!/usr/bin/perl
#
# Use: Create interchange definition from a table
# Author: JuneyM
# E-mail: bicolspice at yahoo dot com
# Date: June 18, 2004
use strict;
use DBI;
my ($dbh, $sth, $db_name, $db_table, $field, $type,
$null, $key, $default, $extra, $db_pass, $db_user,
$pkey );
my ($exit_now, $exit_now_answer, @fieldsNumeric);
print
"------------------------------------------------------\n";
print " Extractor -- table structure extract utility
\n";
print " for Interchange table definition
\n";
print
"------------------------------------------------------\n\n";
print "Enter Database (mysqql:database): ";
$db_name = <STDIN>;
chomp($db_name);
print "Enter the database user: ";
$db_user = <STDIN>;
chomp($db_user);
print "Enter password ($db_user): ";
$db_pass = <STDIN>;
chomp($db_pass);
$exit_now = 0;
print "Enter the table to extract: ";
$db_table = <STDIN>;
chomp($db_table);
$dbh =
DBI->connect("dbi:mysql:$db_name",$db_user,$db_pass)
or die "Error opening database: $DBI::errstr\n";
$sth = $dbh->prepare("describe $db_table")
or die "Prepare failed: $DBI::errstr\n";
$sth->execute()
or die "Coudn't execute query: $DBI::errstr\n";
print "Generating table definitio, please wait\n";
open FILEOUT, ">", "$db_table.mysql";
print FILEOUT "# DSN: mysql:$db_name Table:
$db_table\n";
print FILEOUT "# Generator: table-create.pl -
bicolspice.\n\n";
print FILEOUT "Database $db_table $db_table.txt
__SQLDSN__\n";
print FILEOUT "Database $db_table USER
__SQLUSER__\n";
print FILEOUT "Database $db_table PASS
__SQLPASS__\n\n";
print "Writing database structure...";
while (( $field, $type, $null, $key, $default, $extra)
= $sth->fetchrow_array) {
$pkey = '';
if ($key eq "PRI") {
print FILEOUT "Database $db_table keys\t$field\n";
$pkey = "PRIMARY KEY";
}
if ($key eq "MUL") {
$pkey = "SECONDARY KEY";
}
$null = ($null ne "YES") ? "NOT NULL" : "";
print FILEOUT "Database $db_table
COLUMN_DEF\t\"$field=$type $null $pkey\"\n";
if ($type =~ m/^decimal/i) {
push(@fieldsNumeric, $field);
}
}
print "..done!\n";
print "Writing NUMERIC definition.....";
foreach $field (@fieldsNumeric) {
print FILEOUT "Database $db_table NUMERIC $field\n";
}
print "..done!\n";
$sth->finish();
####
# Determine the indexed columns and append to file
#####
$sth = $dbh->prepare("show index from $db_table")
or die "Prepare failed: $DBI::errstr\n";
$sth->execute()
or die "Coudn't execute show index query:
$DBI::errstr\n";
my (@index_info);
print "Writing INDEX definition....";
while (@index_info = $sth->fetchrow_array) {
if ($index_info[1] == 1) {
print FILEOUT "Database $db_table INDEX " .
$index_info[4] . "\n";
}
}
print "...done!\n";
$sth->finish();
close FILEOUT;
$dbh->disconnect();
print "Table definition saved as $db_table.mysql\n\n";
__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail
More information about the interchange-users
mailing list