[dbsrgits/SQL-Translator.git] / README

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

SYNOPSIS
      use SQL::Translator;

      my $translator     = SQL::Translator->new(
          xlate          => $xlate || {},    # Overrides for field translation
          debug          => $debug,          # Print debug info
          trace          => $trace,          # Print Parse::RecDescent trace
          no_comments    => $no_comments,    # Don't include comments in output
          show_warnings  => $show_warnings,  # Print name mutations, conflicts
          add_drop_table => $add_drop_table, # 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 (aka from)
    parser_args
    producer (aka to)
    producer_args
    filename (aka 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 refernce 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

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