X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=README;h=dda9c79605e07686719f75d083b3305ea259ce8d;hb=0caaf4c3fb70de1a3dcc6e8e70c06c53b56c007c;hp=ef158e0e16619c469749e30f5b5eb17c5621d09e;hpb=d9934baaa2ed4050ea08d924c29ba0a67d1f62a4;p=dbsrgits%2FSQL-Translator.git diff --git a/README b/README index ef158e0..dda9c79 100644 --- a/README +++ b/README @@ -1,272 +1,118 @@ -NAME - SQL::Translator - convert schema from one database to another + SQL::Translator README + +SQL::Translator is a group of Perl modules that converts +vendor-specific SQL table definitions into other formats, such as +other vendor-specific SQL, ER diagrams, documentation (POD and HTML), +XML, and Class::DBI classes. The main focus of SQL::Translator is +SQL, but parsers exist for other structured data formats, including +Excel spreadsheets and arbitrarily delimited text files. Through the +separation of the code into parsers and producers with an object model +in between, it's possible to combine any parser with any producer, to +plug in custom parsers or producers, or to manipulate the parsed data +via the built-in object model. Presently only the definition parts of +SQL are handled (CREATE, ALTER), not the manipulation of data (INSERT, +UPDATE, DELETE). + +As of version 0.03, parsers exist for the following: + + Databases: + MySQL + Oracle + PostgreSQL + Sybase + + Other: + xSV : arbitrarily delimited text files + Excel : Microsoft Excel spreadsheets + XML-SQLFairy: SQLFairy's XML format + +And the following producers exist: + + Databases: + MySQL + Oracle + PostgreSQL + SQLite + Sybase + + + Code Generators: + ClassDBI: Class::DBI classes + + Documentation: + Diagram : quasi-ER diagrams using libgd + GraphViz : ER diagrams using GraphViz + HTML : HTML documentation of schema + POD : Plain Old Documenation of schema + + Other: + XML-SQLFairy: structure of the schema described in SQLFairy's XML + +Included in this distribution are a few scripts designed to be user +interfaces for the actual SQL::Translator modules. In the "bin" +directory, you will find: + +* sqlt-diagram +* sqlt-graph +* sqlt-dumper +* sqlt +* sqlt.cgi + +All scripts not ending in ".cgi" are meant to be run from the command +line with various switches to control the input and output of the +scripts, while the ".cgi" script is a web-form frontend. The script +you'll probably find most useful is "sqlt" which is meant to be the +main interface for translating from text-to-text. The graphic +producers, however, have many extra switches, so there are scripts +specific for each of the the GraphViz and ER-diagram producers. All +scripts start with "sqlt" so it will be easier to identify them on +your system. All the non-CGI scripts will be installed in a system +path when you "make install," but you'll have to manually place the +CGI script into your web CGI directory to use it. + +If you're more interested in using the SQL::Translator modules +directly, then you might be more interested to examine some of the +test scripts in the "t" directory. The test suite is relatively +thorough and should give you an idea of how to parse a file and +manipulate the SQL::Translator::Schema objects. + +INSTALLATION + +The regular method: + + $ perl Makefile.PL + $ make + $ make test + $ su - + # make install -SYNOPSIS - use SQL::Translator; - - my $translator = SQL::Translator->new( - debug => 1, # Print debug info - trace => 0, # Print Parse::RecDescent trace - no_comments => 0, # Don't include comments in output - show_warnings => 0, # Print name mutations, conflicts - add_drop_table => 1, # Add "drop table" statements - - # Make all table names CAPS in producers which support this option - format_table_name => sub {my $tablename = shift; return uc($tablename)}, - - # Null-op formatting, only here for documentation's sake - format_package_name => sub {return shift}, - format_fk_name => sub {return shift}, - format_pk_name => sub {return shift}, - ); - - my $output = $translator->translate( - from => "MySQL", - to => "Oracle", - # Or an arrayref of filenames, i.e. [ $file1, $file2, $file3 ] - filename => $file, - ) or die $translator->error; - - print $output; - -DESCRIPTION - This module attempts to simplify the task of converting one database - create syntax to another through the use of Parsers (which understand - the source format) and Producers (which understand the destination - format). The idea is that any Parser can be used with any Producer in - the conversion process. So, if you wanted Postgres-to-Oracle, you would - use the Postgres parser and the Oracle producer. - -CONSTRUCTOR - The constructor is called "new", and accepts a optional hash of options. - Valid options are: - - * parser / from - - * parser_args - - * producer / to - - * producer_args - - * filename / file - - * data - - * debug - - All options are, well, optional; these attributes can be set via - instance methods. Internally, they are; no (non-syntactical) advantage - is gained by passing options to the constructor. - -METHODS - add_drop_table - - Toggles whether or not to add "DROP TABLE" statements just before the - create definitions. - - custom_translate - - Allows the user to override default translation of fields. For example, - if a MySQL "text" field would normally be converted to a "long" for - Oracle, the user could specify to change it to a "CLOB." Accepts a - hashref where keys are the "from" value and values are the "to," returns - the current value of the field. - - no_comments - - Toggles whether to print comments in the output. Accepts a true or false - value, returns the current value. - - producer - - The "producer" method is an accessor/mutator, used to retrieve or define - what subroutine is called to produce the output. A subroutine defined as - a producer will be invoked as a function (*not a method*) and passed 2 - parameters: its container "SQL::Translator" instance and a data - structure. It is expected that the function transform the data structure - to a string. The "SQL::Transformer" instance is provided for - informational purposes; for example, the type of the parser can be - retrieved using the "parser_type" method, and the "error" and "debug" - methods can be called when needed. - - When defining a producer, one of several things can be passed in: A - module name (e.g., "My::Groovy::Producer"), a module name relative to - the "SQL::Translator::Producer" namespace (e.g., "MySQL"), a module name - and function combination ("My::Groovy::Producer::transmogrify"), or a - reference to an anonymous subroutine. If a full module name is passed in - (for the purposes of this method, a string containing "::" is considered - to be a module name), it is treated as a package, and a function called - "produce" will be invoked: "$modulename::produce". If $modulename cannot - be loaded, the final portion is stripped off and treated as a function. - In other words, if there is no file named - My/Groovy/Producer/transmogrify.pm, "SQL::Translator" will attempt to - load My/Groovy/Producer.pm and use "transmogrify" as the name of the - function, instead of the default "produce". - - my $tr = SQL::Translator->new; - - # This will invoke My::Groovy::Producer::produce($tr, $data) - $tr->producer("My::Groovy::Producer"); - - # This will invoke SQL::Translator::Producer::Sybase::produce($tr, $data) - $tr->producer("Sybase"); - - # This will invoke My::Groovy::Producer::transmogrify($tr, $data), - # assuming that My::Groovy::Producer::transmogrify is not a module - # on disk. - $tr->producer("My::Groovy::Producer::transmogrify"); - - # This will invoke the referenced subroutine directly, as - # $subref->($tr, $data); - $tr->producer(\&my_producer); - - There is also a method named "producer_type", which is a string - containing the classname to which the above "produce" function belongs. - In the case of anonymous subroutines, this method returns the string - "CODE". - - Finally, there is a method named "producer_args", which is both an - accessor and a mutator. Arbitrary data may be stored in name => value - pairs for the producer subroutine to access: - - sub My::Random::producer { - my ($tr, $data) = @_; - my $pr_args = $tr->producer_args(); - - # $pr_args is a hashref. - - Extra data passed to the "producer" method is passed to "producer_args": - - $tr->producer("xSV", delimiter => ',\s*'); - - # In SQL::Translator::Producer::xSV: - my $args = $tr->producer_args; - my $delimiter = $args->{'delimiter'}; # value is ,\s* - - parser - - The "parser" method defines or retrieves a subroutine that will be - called to perform the parsing. The basic idea is the same as that of - "producer" (see above), except the default subroutine name is "parse", - and will be invoked as "$module_name::parse($tr, $data)". Also, the - parser subroutine will be passed a string containing the entirety of the - data to be parsed. - - # Invokes SQL::Translator::Parser::MySQL::parse() - $tr->parser("MySQL"); - - # Invokes My::Groovy::Parser::parse() - $tr->parser("My::Groovy::Parser"); - - # Invoke an anonymous subroutine directly - $tr->parser(sub { - my $dumper = Data::Dumper->new([ $_[1] ], [ "SQL" ]); - $dumper->Purity(1)->Terse(1)->Deepcopy(1); - return $dumper->Dump; - }); - - There is also "parser_type" and "parser_args", which perform analogously - to "producer_type" and "producer_args" - - show_warnings - - Toggles whether to print warnings of name conflicts, identifier - mutations, etc. Probably only generated by producers to let the user - know when something won't translate very smoothly (e.g., MySQL "enum" - fields into Oracle). Accepts a true or false value, returns the current - value. - - translate - - The "translate" method calls the subroutines referenced by the "parser" - and "producer" data members (described above). It accepts as arguments a - number of things, in key => value format, including (potentially) a - parser and a producer (they are passed directly to the "parser" and - "producer" methods). - - Here is how the parameter list to "translate" is parsed: - - * 1 argument means it's the data to be parsed; which could be a string - (filename) or a reference to a scalar (a string stored in memory), - or a reference to a hash, which is parsed as being more than one - argument (see next section). - - # Parse the file /path/to/datafile - my $output = $tr->translate("/path/to/datafile"); - - # Parse the data contained in the string $data - my $output = $tr->translate(\$data); - - * More than 1 argument means its a hash of things, and it might be - setting a parser, producer, or datasource (this key is named - "filename" or "file" if it's a file, or "data" for a SCALAR - reference. - - # As above, parse /path/to/datafile, but with different producers - for my $prod ("MySQL", "XML", "Sybase") { - print $tr->translate( - producer => $prod, - filename => "/path/to/datafile", - ); - } - - # The filename hash key could also be: - datasource => \$data, - - You get the idea. - - filename, data - - Using the "filename" method, the filename of the data to be parsed can - be set. This method can be used in conjunction with the "data" method, - below. If both the "filename" and "data" methods are invoked as - mutators, the data set in the "data" method is used. - - $tr->filename("/my/data/files/create.sql"); - - or: - - my $create_script = do { - local $/; - open CREATE, "/my/data/files/create.sql" or die $!; - ; - }; - $tr->data(\$create_script); - - "filename" takes a string, which is interpreted as a filename. "data" - takes a reference to a string, which is used as the data to be parsed. - If a filename is set, then that file is opened and read when the - "translate" method is called, as long as the data instance variable is - not set. +COPYRIGHT - trace +This program is free software; you can redistribute it and/or modify it +under the terms of the GNU General Public License as published by the +Free Software Foundation; version 2. - Turns on/off the tracing option of Parse::RecDescent. +This program is distributed in the hope that it will be useful, but +WITHOUT ANY WARRANTY; without even the implied warranty of +MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General +Public License for more details. -AUTHORS - Ken Y. Clark, , darren chamberlain , - Chris Mungall , Allen Day - +You should have received a copy of the GNU General Public License along +with this program; if not, write to the Free Software Foundation, Inc., +59 Temple Place, Suite 330, Boston, MA 02111-1307 USA -COPYRIGHT - This program is free software; you can redistribute it and/or modify it - under the terms of the GNU General Public License as published by the - Free Software Foundation; version 2. +BUGS - This program is distributed in the hope that it will be useful, but - WITHOUT ANY WARRANTY; without even the implied warranty of - MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General - Public License for more details. +Please use http://rt.cpan.org/ for reporting bugs. - You should have received a copy of the GNU General Public License along - with this program; if not, write to the Free Software Foundation, Inc., - 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA +PRAISE -BUGS - Please use http://rt.cpan.org/ for reporting bugs. +If you find this module useful, please use +"http://cpanratings.perl.org/rate/?distribution=SQL-Translator" to rate it. SEE ALSO - the perl manpage, the SQL::Translator::Parser manpage, the - SQL::Translator::Producer manpage, the Parse::RecDescent manpage +Check out the SQLFairy homepage at Sourceforge for more information, +mailing lists, etc.: + + http://sqlfairy.sourceforge.net/