X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=README;h=7076ba57e15f63de4babd1cfabe2443964559e67;hb=1687dad4f05e3fde92e9d978f6cec31d23d58de3;hp=987579b13c72de79777c217c7517317694530b7b;hpb=2e1b177557b84f0327b3b87fb7a573d90ccb7ba3;p=dbsrgits%2FSQL-Translator.git diff --git a/README b/README index 987579b..7076ba5 100644 --- a/README +++ b/README @@ -1,261 +1,143 @@ -NAME - SQL::Translator - convert schema from one database to another + SQL::Translator README + +SQL::Translator is a group of Perl modules that manipulate structure +data definitions (mostly database schemas) in interesting ways, such +as converting among different dialects of CREATE syntax (e.g., +MySQL-to-Oracle), visualizations of schemas (pseudo-ER diagrams +GraphViz or GD), automatic code generation (using Class::DBI), +converting non-RDBMS files to SQL schemas (xSV text files, Excel +spreadsheets), serializing parsed schemas (via Storable, YAML and +XML), creating documentation (HTML and POD), and more. We also +have the ability to talk directly to a database through DBI to +query for the structures of several databases. + +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.06, parsers exist for the following: + + Databases: + MySQL + Oracle + PostgreSQL + SQLite + Sybase + + DBI-MySQL + DBI-PostgreSQL + DBI-SQLite + DBI-Sybase + + Other: + xSV : arbitrarily delimited text files + Excel : Microsoft Excel spreadsheets + XML-SQLFairy : SQLFairy's XML format + YAML/Storable: Serialized schema objects + +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 + + Serialization: + Storable : using Perl's Storable module + YAML : YAML format + XML-SQLFairy : structure of the schema described in SQLFairy's XML + + Other: + TTSchema : to any text format via Template Toolkit + +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: interface to Diagram producer +* sqlt-diff : diff two schemas to generate schema mutation file +* sqlt-graph : interface to GraphViz producer +* sqlt-dumper : create a data dumper script from a schema +* sqlt : command-line interface for text-to-text translations +* sqlt.cgi : CGI interface for all SQLFairy functions + +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 + + $ perl Build.PL + $ ./Build + $ ./Build test + $ su + # ./Build install + +MANUAL + +To read the manual: + + $ perldoc SQL::Translator::Manual -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 - ); - - my $output = $translator->translate( - from => "MySQL", - to => "Oracle", - 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", - ); - } +COPYRIGHT - # The filename hash key could also be: - datasource => \$data, +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. - You get the idea. +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. - filename, data +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 - 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. +BUGS - $tr->filename("/my/data/files/create.sql"); +Please use http://rt.cpan.org/ for reporting bugs. - or: +PRAISE - my $create_script = do { - local $/; - open CREATE, "/my/data/files/create.sql" or die $!; - ; - }; - $tr->data(\$create_script); +If you find this module useful, please use +"http://cpanratings.perl.org/rate/?distribution=SQL-Translator" to rate it. - 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. +SEE ALSO - trace +Check out the SQLFairy homepage at Sourceforge for more information, +mailing lists, etc.: - Turns on/off the tracing option of Parse::RecDescent. + http://sqlfairy.sourceforge.net/ AUTHORS - Ken Y. Clark, , darren chamberlain , - Chris Mungall , Allen Day - - -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. - - 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. - - 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 - -BUGS - Please use http://rt.cpan.org/ for reporting bugs. - -SEE ALSO - the perl manpage, the SQL::Translator::Parser manpage, the - SQL::Translator::Producer manpage, the Parse::RecDescent manpage +See the included AUTHORS file.