[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