Date: Wed, 26 Sep 2001 10:50:43 +0300 From: "Dimitar Peikov" <mitko@rila.bg> To: binup@freebsd.org Subject: Perl code for XMLing database creation Message-ID: <200109260750.f8Q7oha64072@earth.rila.bg>
next in thread | raw e-mail | index | archive | help
[-- Attachment #1 --]
There is a slight modification where target SQL code is generated in
dependency of command switches. More than one target SQL servers can be
supported.
[-- Attachment #2 --]
<?xml version="1.0"?>
<updated>
<sql type="mysql">
<datatype name="sequence" value="int auto_increment"/>
</sql>
<sql type="pgsql">
<datatype name="sequence" value="serial"/>
</sql>
<database>
<table name="actions">
<column name="reltag" type="int"/>
<column name="component" type="int"/>
<column name="distribution" type="varchar(255)"/>
<column name="action" type="varchar(255)"/>
<column name="undo" type="varchar(255)"/>
</table>
<table name="dists">
<column name="reltag" type="int"/>
<column name="timestamp" type="datetime"/>
<column name="distribution" type="varchar(128)"/>
</table>
<table name="dirs">
<column name="reltag" type="int"/>
<column name="component" type="int"/>
<column name="timestamp" type="datetime"/>
<column name="mtree" type="varchar(255)"/>
</table>
<table name="files">
<column name="reltag" type="int"/>
<column name="timestamp" type="datetime"/>
<column name="distribution" type="varchar(128)"/>
<column name="filename" type="varchar(255)"/>
<column name="location" type="varchar(255)"/>
<column name="mode" type="int"/>
<column name="uid" type="int"/>
<column name="gid" type="int"/>
</table>
<table name="profile">
<column name="name" type="varchar(128)"/>
<column name="reltag" type="int"/>
<column name="description" type="varchar(255)"/>
</table>
<table name="releases">
<column name="reltag" type="sequence" PK="yes"/>
<column name="name" type="varchar(128)"/>
<column name="floating" type="int"/>
</table>
</database>
</updated>
[-- Attachment #3 --]
#!/usr/bin/perl -s
#
# Dimitar Peikov, 2001-09-25
#
# Usage :
# ./config.pl -mysql config.xml | mysql -u username -p database
# or
# ./config.pl -pgsql config.xml | psql -U username
#
#
# This program creates SQL statements for database creation from
# XML confguration file.
#
# Run with -DEBUG to view the debug information on STDERR
#
# Code prefixed with ### is in case that this script takes care of
# initial database initialization. This code makes three of tables
# needed when INSERT statements needs to be generated.
use XML::Parser;
my $arg = shift;
unless (defined $arg) {
print STDERR "XML file needed.\n";
exit();
};
# Hash collecting SQL server types
my $sql_types = {};
# Hash collecting specific SQL datatype definition
my $sql_element = {};
# Temporary SQL server name variable needed for
# collecting specific SQL datatype definitions
my $sql_element_name = "";
#### Hash of tables in database
###my $sql_tables = {};
#### Temporary table name
###my $sql_table_name = "";
# Target SQL server name
my $sql_server = "";
# Target SQL code
my $sql_code = "";
# Temporary table creation SQL variable
my $table_sql = "";
# Temporary column ID variable
my $column_id = 0;
# XML::Parser object
my $parser = new XML::Parser(Style=>'Subs');
# Known SQL server list
my $sql_error = "";
# Parsing the XML file
$parser->parsefile($arg);
# Check for target SQL server
unless ($sql_server) {
print STDERR "Expected " . $sql_error . ".\n";
exit();
};
# Print SQL code
print $sql_code;
# Subroutines handled XML tags parsing
sub updated {
my $expat = shift;
my $element = shift;
my @attributes = @_;
if (defined $DEBUG) {
print STDERR "Enter updated @attributes\n";
}
}
sub updated_ {
my $expat = shift;
my $element = shift;
if (defined $DEBUG) {
print STDERR "Exit updated\n";
}
}
sub sql {
my $expat = shift;
my $element = shift;
my @attributes = @_;
my %attr = @attributes;
$sql_element_name = $attr{type};
$sql_element = {};
# Fill the list of known SQL servers
$sql_error .= " or" if (length($sql_error));
$sql_error .= " -" . $attr{type};
if (defined $DEBUG) {
print STDERR "Enter sql @attributes\n";
}
}
sub sql_ {
my $expat = shift;
my $element = shift;
$sql_types->{$sql_element_name} = $sql_element;
if (defined $DEBUG) {
print STDERR "Exit sql $sql_element_name\n";
}
}
sub datatype {
my $expat = shift;
my $element = shift;
my @attributes = @_;
my %attr = @attributes;
$sql_element->{$attr{name}} = $attr{value};
if (defined $DEBUG) {
print STDERR "Enter datatype @attributes\n";
}
}
sub datatype_ {
my $expat = shift;
my $element = shift;
if (defined $DEBUG) {
print STDERR "Exit datatype\n";
}
}
sub database {
my $expat = shift;
my $element = shift;
my @attributes = @_;
my %attr = @attributes;
# Detecting target SQL
my $i;
foreach $i (keys %$sql_types) {
if (defined eval("\$" . $i)) {
# Set matched SQL
$sql_server = $i;
if (defined $DEBUG) {
print STDERR "Matched database $i\n";
}
}
}
if (defined $DEBUG) {
print STDERR "Enter database @attributes\n";
}
}
sub database_ {
my $expat = shift;
my $element = shift;
if (defined $DEBUG) {
print STDERR "Exit database\n";
}
}
sub table {
my $expat = shift;
my $element = shift;
my @attributes = @_;
my %attr = @attributes;
$table_sql = "create table " . $attr{name} . " (";
$column_id = 0;
### $sql_table_name = $attr{name};
### $sql_tables->{$sql_table_name} = ();
if (defined $DEBUG) {
print STDERR "Enter table @attributes\n";
}
}
sub table_ {
my $expat = shift;
my $element = shift;
$table_sql .= ");\n";
$sql_code .= $table_sql;
if (defined $DEBUG) {
print STDERR "Exit table\n";
}
}
sub column {
my $expat = shift;
my $element = shift;
my @attributes = @_;
my %attr = @attributes;
$table_sql .= ", " if ($column_id > 0);
$column_id ++;
$table_sql .= $attr{name} . " ";
if (defined $sql_types->{$sql_server}->{$attr{type}}) {
$attr{type} = $sql_types->{$sql_server}->{$attr{type}};
}
$table_sql .= $attr{type};
$table_sql .= " primary key" if (defined $attr{PK} && uc $attr{PK} == "YES");
$table_sql .= " " . $attr{extra} if (defined $attr{extra});
### push @{$sql_tables->{$sql_table_name}}, \%attr;
if (defined $DEBUG) {
print STDERR "Enter column @attributes\n";
}
}
sub column_ {
my $expat = shift;
my $element = shift;
if (defined $DEBUG) {
print STDERR "Exit column\n";
}
}
[-- Attachment #4 --]
Dimitar Peikov
Programmer Analyst
Globalization Group
"We Build e-Business"
RILA Solutions
27 Building, Acad.G.Bonchev Str.
1113 Sofia, Bulgaria
phone: (+359 2) 9797320
phone: (+359 2) 9797300
fax: (+359 2) 9733355
http://www.rila.com
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?200109260750.f8Q7oha64072>