SQL::Statement questions

Hi *,

I have a few questions regarding SQL::Statement:

The first one probably can only be answered by the quthor himself: Will
SQL::Statement be able to understand the "ALTER" command in the
future or do I have to implement it myself? I tried to activate it with
$sql_parser->feature( 'valid_commands', 'ALTER', 1 );
but that obviously isn't enough.
What about statements as "FOREIGN KEY" and "PRIMARY KEY" (see below)?

The second one: I have a file containing several SQL statements mainly
for creating and altering tables. This file is created by a case tool
for database design - for example:

------------------------< snip snip snip >-----------------------------
--- ************************************************************ *
--- Purpose : DDL script
--- Database : C
--- ************************************************************ *

--- =========================
--- DB table: Tray
--- C-Schema: fileSystem
--- =========================

CREATE TABLE Tray (
Tray_ID char NOT NULL,
Trayserver char ,
PRIMARY KEY (Tray_ID)
)
;

[...]

ALTER TABLE Tray
FOREIGN KEY (SpecialTableID) REFERENCES SpecialTable,
;

------------------------< snip snip snip >-----------------------------

I have to parse this file, check the syntax of the statements and then
transform them to something, which is completely different from SQL.
Is SQL::Statement the appropraite module for this or is there a module
that could do this job in a better way?
At the moment I read the file and cat all the lines together up to the next
semicolon. The resulting $sSQL is used as parameter for SQL::Statement:
$sql_statement = SQL::Statement->new( $sSQL, $sql_parser );

One more question: Is there any way to access the "column_defs" hashes
via object methods or do I have to read the structure directly (which
is deprecated as stated in the man page).


TIA.

Bye.
Michael.
--
Michael Hirmke | Telefon +49 (911) 557999
Wilhelm-Spaeth-Strasse 52 | FAX +49 (911) 557664
90461 Nuernberg | E-Mail mailto:mh@mike.franken.de
| WWW http://www.hirmke.de/

Re: SQL::Statement questions

Michael Hirmke wrote:

>Hi *,
>
>I have a few questions regarding SQL::Statement:
>
>The first one probably can only be answered by the quthor himself
>
That's me.

>: Will
>SQL::Statement be able to understand the "ALTER" command in the
>future
>
Yes but not any time soon unless someone wants to collaborate on it or
send patches.

> or do I have to implement it myself? I tried to activate it with
> $sql_parser->feature( 'valid_commands', 'ALTER', 1 );
>but that obviously isn't enough.
>
>
It's not too hard to add support for that to the parser but it's a bit
more involved than what you show.

>What about statements as "FOREIGN KEY" and "PRIMARY KEY" (see below)?
>
>
At the moment SQL::Statement is much better at DML than it is at DDL.
Primary key is recognized as a column definition e.g. "pid INT PRIMARY
KEY" but not as a table defnition e.g. "PRIMARY KEY (col1,col2)".

>The second one: I have a file containing several SQL statements mainly
>for creating and altering tables. This file is created by a case tool
>for database design
>
>[...]
>
>I have to parse this file, check the syntax of the statements and then
>transform them to something, which is completely different from SQL.
>Is SQL::Statement the appropraite module for this or is there a module
>that could do this job in a better way?
>
>
In general, SQL::Statement is meant to work that way. But given the fact
that you are dealing mostly with DDL and that's SQL::Statement's weakest
point, I recommend you look at SQL::Translator which has very robust DDL
handling (but little or no DML handling). If you have both complex DDL
and DML you may need to use both modules.

>One more question: Is there any way to access the "column_defs" hashes
>via object methods or do I have to read the structure directly (which
>is deprecated as stated in the man page).
>
>
Again, because DDL is currently weak, I have not yet created a method to
access the column definitions in a CREATE statement, so you do need to
use the hash structure directly. Using the hash is deprecated because
eventually everything will be accessed by methods, not because there are
problems with the way it works now.

Good luck!

--
Jeff

Re: SQL::Statement questions

Hi Jeff,

[...]
>>The first one probably can only be answered by the quthor himself
>>
>That's me.

I know :)
Thx for developing this module. Even if I can't use it in this case, it
will help me for future scripts!

>>: Will
>>SQL::Statement be able to understand the "ALTER" command in the
>>future
>>
>Yes but not any time soon unless someone wants to collaborate on it or
>send patches.
[...]
>It's not too hard to add support for that to the parser but it's a bit
>more involved than what you show.

IC, but I guess, it nevertheless will be too hard for me.

[...]
>At the moment SQL::Statement is much better at DML than it is at DDL.
>Primary key is recognized as a column definition e.g. "pid INT PRIMARY
>KEY" but not as a table defnition e.g. "PRIMARY KEY (col1,col2)".

[...]
>In general, SQL::Statement is meant to work that way. But given the fact
>that you are dealing mostly with DDL and that's SQL::Statement's weakest
>point, I recommend you look at SQL::Translator which has very robust DDL
>handling (but little or no DML handling). If you have both complex DDL
>and DML you may need to use both modules.

Thx for this hint. I will give SQL::Translator a try.

[...]

>Good luck!

Thx for your answer.

>--
>Jeff

Bye.
Michael.
--
Michael Hirmke | Telefon +49 (911) 557999
Wilhelm-Spaeth-Strasse 52 | FAX +49 (911) 557664
90461 Nuernberg | E-Mail mailto:mh@mike.franken.de
| WWW http://www.hirmke.de/