[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. 


# 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 " Extractor  -- table structure extract utility
print "               for Interchange table definition

print "Enter Database (mysqql:database): ";
$db_name = <STDIN>;

print "Enter the database user: ";
$db_user = <STDIN>;

print "Enter password ($db_user): ";
$db_pass = <STDIN>;

$exit_now = 0;

print "Enter the table to extract: ";
$db_table = <STDIN>;

$dbh =
  	or die "Error opening database: $DBI::errstr\n";

$sth = $dbh->prepare("describe $db_table") 
	or die "Prepare failed: $DBI::errstr\n";

	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:
print FILEOUT "# Generator: table-create.pl  -

print FILEOUT "Database $db_table $db_table.txt 
print FILEOUT "Database $db_table  USER     
print FILEOUT "Database $db_table  PASS     

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";


# Determine the indexed columns and append to file

$sth = $dbh->prepare("show index from $db_table") 
	or die "Prepare failed: $DBI::errstr\n";

	or die "Coudn't execute show index query:

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";

close FILEOUT;

print "Table definition saved as $db_table.mysql\n\n";

Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!

More information about the interchange-users mailing list