[dbsrgits/SQL-Translator.git] / README

NAME
    SQL::Translator - convert schema from one database to another

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",
                    );
          }

          # 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 $!;
            <CREATE>;
        };
        $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.

  trace

    Turns on/off the tracing option of Parse::RecDescent.

AUTHORS
    Ken Y. Clark, <kclark@cpan.org>, darren chamberlain <darren@cpan.org>,
    Chris Mungall <cjm@fruitfly.org>, Allen Day
    <allenday@users.sourceforge.net>

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
Commit	Line	Data
46d3d648	1	NAME
	2	SQL::Translator - convert schema from one database to another
	3
	4	SYNOPSIS
	5	use SQL::Translator;
	6
	7	my $translator = SQL::Translator->new(
2e1b1775	8	debug => 1, # Print debug info
	9	trace => 0, # Print Parse::RecDescent trace
	10	no_comments => 0, # Don't include comments in output
	11	show_warnings => 0, # Print name mutations, conflicts
	12	add_drop_table => 1, # Add "drop table" statements
46d3d648	13	);
	14
	15	my $output = $translator->translate(
	16	from => "MySQL",
	17	to => "Oracle",
	18	filename => $file,
	19	) or die $translator->error;
	20
	21	print $output;
	22
	23	DESCRIPTION
	24	This module attempts to simplify the task of converting one database
	25	create syntax to another through the use of Parsers (which understand
	26	the source format) and Producers (which understand the destination
	27	format). The idea is that any Parser can be used with any Producer in
	28	the conversion process. So, if you wanted Postgres-to-Oracle, you would
	29	use the Postgres parser and the Oracle producer.
	30
	31	CONSTRUCTOR
	32	The constructor is called new, and accepts a optional hash of options.
	33	Valid options are:
	34
2e1b1775	35	* parser / from
	36
	37	* parser_args
	38
	39	* producer / to
	40
	41	* producer_args
	42
	43	* filename / file
	44
	45	* data
	46
	47	* debug
	48
46d3d648	49	All options are, well, optional; these attributes can be set via
	50	instance methods. Internally, they are; no (non-syntactical) advantage
	51	is gained by passing options to the constructor.
	52
	53	METHODS
	54	add_drop_table
	55
	56	Toggles whether or not to add "DROP TABLE" statements just before the
	57	create definitions.
	58
	59	custom_translate
	60
	61	Allows the user to override default translation of fields. For example,
	62	if a MySQL "text" field would normally be converted to a "long" for
	63	Oracle, the user could specify to change it to a "CLOB." Accepts a
	64	hashref where keys are the "from" value and values are the "to," returns
	65	the current value of the field.
	66
	67	no_comments
	68
	69	Toggles whether to print comments in the output. Accepts a true or false
	70	value, returns the current value.
	71
	72	producer
	73
	74	The producer method is an accessor/mutator, used to retrieve or define
	75	what subroutine is called to produce the output. A subroutine defined as
	76	a producer will be invoked as a function (not a method) and passed 2
2e1b1775	77	parameters: its container "SQL::Translator" instance and a data
	78	structure. It is expected that the function transform the data structure
	79	to a string. The "SQL::Transformer" instance is provided for
	80	informational purposes; for example, the type of the parser can be
	81	retrieved using the parser_type method, and the error and debug methods
	82	can be called when needed.
46d3d648	83
46d3d648	84	When defining a producer, one of several things can be passed in: A
2e1b1775	85	module name (e.g., "My::Groovy::Producer", a module name relative to the
	86	"SQL::Translator::Producer" namespace (e.g., MySQL), a module name and
	87	function combination ("My::Groovy::Producer::transmogrify"), or a
46d3d648	88	reference to an anonymous subroutine. If a full module name is passed in
	89	(for the purposes of this method, a string containing "::" is considered
	90	to be a module name), it is treated as a package, and a function called
2e1b1775	91	"produce" will be invoked: "$modulename::produce". If $modulename cannot
46d3d648	92	be loaded, the final portion is stripped off and treated as a function.
46d3d648	93	In other words, if there is no file named
2e1b1775	94	My/Groovy/Producer/transmogrify.pm, "SQL::Translator" will attempt to
	95	load My/Groovy/Producer.pm and use transmogrify as the name of the
	96	function, instead of the default "produce".
46d3d648	97
	98	my $tr = SQL::Translator->new;
	99
	100	# This will invoke My::Groovy::Producer::produce($tr, $data)
	101	$tr->producer("My::Groovy::Producer");
	102
	103	# This will invoke SQL::Translator::Producer::Sybase::produce($tr, $data)
	104	$tr->producer("Sybase");
	105
	106	# This will invoke My::Groovy::Producer::transmogrify($tr, $data),
	107	# assuming that My::Groovy::Producer::transmogrify is not a module
	108	# on disk.
	109	$tr->producer("My::Groovy::Producer::transmogrify");
	110
	111	# This will invoke the referenced subroutine directly, as
	112	# $subref->($tr, $data);
	113	$tr->producer(\&my_producer);
	114
	115	There is also a method named producer_type, which is a string containing
	116	the classname to which the above produce function belongs. In the case
	117	of anonymous subroutines, this method returns the string "CODE".
	118
	119	Finally, there is a method named producer_args, which is both an
	120	accessor and a mutator. Arbitrary data may be stored in name => value
	121	pairs for the producer subroutine to access:
	122
	123	sub My::Random::producer {
	124	my ($tr, $data) = @_;
	125	my $pr_args = $tr->producer_args();
	126
	127	# $pr_args is a hashref.
	128
	129	Extra data passed to the producer method is passed to producer_args:
	130
	131	$tr->producer("xSV", delimiter => ',\s*');
	132
	133	# In SQL::Translator::Producer::xSV:
	134	my $args = $tr->producer_args;
	135	my $delimiter = $args->{'delimiter'}; # value is ,\s*
	136
	137	parser
	138
	139	The parser method defines or retrieves a subroutine that will be called
	140	to perform the parsing. The basic idea is the same as that of producer
	141	(see above), except the default subroutine name is "parse", and will be
2e1b1775	142	invoked as "$module_name::parse($tr, $data)". Also, the parser
	143	subroutine will be passed a string containing the entirety of the data
	144	to be parsed.
46d3d648	145
	146	# Invokes SQL::Translator::Parser::MySQL::parse()
	147	$tr->parser("MySQL");
	148
	149	# Invokes My::Groovy::Parser::parse()
	150	$tr->parser("My::Groovy::Parser");
	151
	152	# Invoke an anonymous subroutine directly
	153	$tr->parser(sub {
	154	my $dumper = Data::Dumper->new([ $_[1] ], [ "SQL" ]);
	155	$dumper->Purity(1)->Terse(1)->Deepcopy(1);
	156	return $dumper->Dump;
	157	});
	158
	159	There is also parser_type and parser_args, which perform analogously to
	160	producer_type and producer_args
	161
	162	show_warnings
	163
	164	Toggles whether to print warnings of name conflicts, identifier
	165	mutations, etc. Probably only generated by producers to let the user
	166	know when something won't translate very smoothly (e.g., MySQL "enum"
	167	fields into Oracle). Accepts a true or false value, returns the current
	168	value.
	169
	170	translate
	171
	172	The translate method calls the subroutines referenced by the parser and
	173	producer data members (described above). It accepts as arguments a
	174	number of things, in key => value format, including (potentially) a
	175	parser and a producer (they are passed directly to the parser and
	176	producer methods).
	177
	178	Here is how the parameter list to translate is parsed:
	179
	180	* 1 argument means it's the data to be parsed; which could be a string
2e1b1775	181	(filename) or a reference to a scalar (a string stored in memory),
2e1b1775	182	or a reference to a hash, which is parsed as being more than one
46d3d648	183	argument (see next section).
	184
	185	# Parse the file /path/to/datafile
	186	my $output = $tr->translate("/path/to/datafile");
	187
	188	# Parse the data contained in the string $data
	189	my $output = $tr->translate(\$data);
	190
	191	* More than 1 argument means its a hash of things, and it might be
	192	setting a parser, producer, or datasource (this key is named
	193	"filename" or "file" if it's a file, or "data" for a SCALAR
	194	reference.
	195
	196	# As above, parse /path/to/datafile, but with different producers
	197	for my $prod ("MySQL", "XML", "Sybase") {
	198	print $tr->translate(
	199	producer => $prod,
	200	filename => "/path/to/datafile",
	201	);
	202	}
	203
	204	# The filename hash key could also be:
	205	datasource => \$data,
	206
	207	You get the idea.
	208
	209	filename, data
	210
	211	Using the filename method, the filename of the data to be parsed can be
	212	set. This method can be used in conjunction with the data method, below.
	213	If both the filename and data methods are invoked as mutators, the data
	214	set in the data method is used.
	215
	216	$tr->filename("/my/data/files/create.sql");
	217
	218	or:
	219
	220	my $create_script = do {
	221	local $/;
	222	open CREATE, "/my/data/files/create.sql" or die $!;
	223	<CREATE>;
	224	};
	225	$tr->data(\$create_script);
	226
	227	filename takes a string, which is interpreted as a filename. data takes
	228	a reference to a string, which is used as the data to be parsed. If a
	229	filename is set, then that file is opened and read when the translate
	230	method is called, as long as the data instance variable is not set.
	231
	232	trace
	233
	234	Turns on/off the tracing option of Parse::RecDescent.
	235
	236	AUTHORS
	237	Ken Y. Clark, <kclark@cpan.org>, darren chamberlain <darren@cpan.org>,
	238	Chris Mungall <cjm@fruitfly.org>, Allen Day
	239	<allenday@users.sourceforge.net>
	240
	241	COPYRIGHT
	242	This program is free software; you can redistribute it and/or modify it
	243	under the terms of the GNU General Public License as published by the
	244	Free Software Foundation; version 2.
	245
	246	This program is distributed in the hope that it will be useful, but
247	WITHOUT ANY WARRANTY; without even the implied warranty of
248	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
249	Public License for more details.
250
251	You should have received a copy of the GNU General Public License along
252	with this program; if not, write to the Free Software Foundation, Inc.,
253	59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
254
2e1b1775	255	BUGS
	256	Please use http://rt.cpan.org/ for reporting bugs.
	257
46d3d648	258	SEE ALSO
	259	the perl manpage, the SQL::Translator::Parser manpage, the
	260	SQL::Translator::Producer manpage, the Parse::RecDescent manpage
	261