SQL Fairy

All,

After a lot of starts and stops, I think I've got a real plan for  
getting SQLFairy t-shirts printed up.  My dad worked with an artist  
to create a version of the logo that will print well on black shirts,  
e.g.:

	http://www.cafepress.com/sqlfairy.47879301

Though what I have planned is to use a screenprinter and good, thick  
t-shirts to hopefully create something a little nicer than  
Cafepress's (though I'm sure theirs are decent).

Last time I asked around, almost a dozen people wanted shirts of  
varying colors and sizes.  I need a minimum order of 12 to move  
forward, and I'd like to get just black shirts, so I'd like people to  
reconfirm with me that you'd like a shirt (I've also lost my earlier  
list of people wanting shirts -- d'oh!).  I'm not entirely sure of  
what the cost will be as I'll have to cover my cost to order them and  
them ship them individually to all of your who want them, but let's  
estimate it will cost at least the $21 that Cafepress is charging.

So, please write me off-list if you're interested in ordering a  
black, screenprinted SQLFairy shirt.  I'll confirm with you the cost  
you before I move forward.  I'm very sorry that previous attempts to  
make this happen have stalled, but I'm pretty sure I can make this  
happen this time.

ky

All,

I've started using the MySQL DBI Parser more, and I realized that it  
doesn't parse foreign keys at all.  I did a lot of searching to try  
to figure out how to query MySQL directly to get FKs, and there are  
apparently only two ways:

0) "SHOW TABLE STATUS FROM $db_name LIKE '$table_name'"

This gives you something like this:

mysql> show table status from markers20 like 'marker' \G
*************************** 1. row ***************************
            Name: marker
          Engine: InnoDB
         Version: 9
      Row_format: Dynamic
            Rows: 4629460
Avg_row_length: 158
     Data_length: 735051776
Max_data_length: NULL
    Index_length: 788856832
       Data_free: 0
Auto_increment: 6118531
     Create_time: 2006-02-09 11:23:13
     Update_time: NULL
      Check_time: NULL
       Collation: latin1_swedish_ci
        Checksum: NULL
Create_options:
         Comment: InnoDB free: 3931136 kB; (`marker_type_id`) REFER  
`markers20/marker_type`(`marker_type_id`); (`display_synonym_id`)  
REFER `markers20/marker_synonym`(`marker_synonym_id`);  
(`source_species_id`) REFER `markers20/species`(`species_id`);  
(`germplasm_id`) REFER `markers20/germplasm`(`germplasm_id`);  
(`analysis_id`) REFER `markers20/analysis`(`analysis_id`);  
(`library_id`) REFER `markers20/library`(`library_id`)
1 row in set (0.34 sec)

So you have to parse out the "Comment" with regexes.  Pretty bleh, if  
you ask me.  Why no "SHOW FOREIGN KEYS FROM TABLE"?!  Anyway, not  
terrible except that on my home system running MySQL 5.0.16-standard,  
I get this on the same table definition:

mysql> show table status from markers20 like 'marker' \G
*************************** 1. row ***************************
            Name: marker
          Engine: InnoDB
         Version: 10
      Row_format: Compact
            Rows: 0
Avg_row_length: 0
     Data_length: 16384
Max_data_length: 0
    Index_length: 114688
       Data_free: 0
Auto_increment: 1
     Create_time: 2006-02-22 14:30:05
     Update_time: NULL
      Check_time: NULL
       Collation: latin1_swedish_ci
        Checksum: NULL
Create_options:
         Comment: InnoDB free: 10240 kB; (`marker_type_id`) REFER  
`markers20/marker_type`(`marker_
1 row in set (0.04 sec)

Agh!  The "Comment" is truncated!

1) "SHOW CREATE TABLE $table_name"

This works, but it puts me right back to parsing the raw SQL just  
like the Parse::RecDescent parser.  But I really need this to work,  
and I can't be worrying about whether this is going to work or fail  
depending on some strange combination of software versions, so I  
think I'm going to go the way of the DBI::SQLite parser and build up  
all the "CREATE" statements into a scalar, set that as the "data" and  
then call the regular parser to work on that.  It has the advantage  
of working off the pretty solid work already done there, of covering  
more syntax, and of querying the db directly for the schema, but it  
also means using the slower text-parsing version rather than querying  
system catalogs.

For my own purposes, I see no way around this.  "Slow and steady wins  
the race," so I think I'll commit this if no one objects or has  
better ideas.

ky

On Feb 8, 2006, at 12:19 AM, Chuck Phares wrote:

> Hello Ken,
>
> I hope you don't mind me contacting you directly.  I came across your
> SQL Translator project on CPAN.  What a wonderful bit of coding!
> Count me amongst the grateful users of this very useful tool.
>
> The problem I am trying to solve is porting a bunch of (very complex)
> view definitions from Sybase to PostgreSQL.  Most of the required
> changes are pretty obvious, but I am not very familiar with the ansi
> join syntax.  My sql experience is based on older software, I guess.
> I am only familiar with joins via the 'where' clause.
>
> I was hoping you might be able to point me in the right direction.  I
> have spent quite a bit of time searching for some sort of tool which
> can translate queries written with Sybase proprietary outer join
> syntax to ansi standard outer join syntax.  This seems like the
> perfect candidate for automated translation.  The grammar rules are
> well defined and understood.  Imagine my surprise when I came up empty
> after banging on Google for several hours!
>
> Are there any resources you can point me to which can re-write
> old-style joins to the newer syntax?
>
> I appriciate any assistance you can lend me, and thanks again for such
> a useful program.
>
> Sincerely,
>
> Chuck Phares

Chuck,

I'm glad you found SQLT and that it has been useful to you.  I've  
never had any luck finding the ANSI standards for SQL, and running  
down any formalized grammar rules for the proprietary dbs has been  
much harder than for the open-source alternatives (where you can just  
look at the source!).  Further, SQLT has never attempted to translate  
any SELECT/UPDATE/DELETE statements -- we stick to just CREATE/ALTER/ 
DROP, those statements having to do with the structure of a database,  
not the manipulation of data -- and, of course, the interesting parts  
of views are the SELECTs.  So SQLT may not be what you need to  
translate your views, but perhaps we can still help, if only to  
provide moral support and technical guidance.  :-)

I'm cc'ing the list here as we have some pretty sharp Sybase guys in  
the project.  Perhaps they can help.

ky

If you send me a couple of the view definitions, I can try and do either
a hand-translation (easy) to ANSI JOIN syntax or modify the parser to do
an automated translation (less-easy!)

pjjH


-----Original Message-----
From: sqlfairy-developers-admin@...
[mailto:sqlfairy-developers-admin@...] On Behalf Of
Ken Youens-Clark
Sent: Wednesday, February 08, 2006 9:17 AM
To: Chuck Phares
Cc: SQL Fairy
Subject: [sqlfairy-developers] Re: Translating Sybase outer join syntax
to ansi outer join syntax...


On Feb 8, 2006, at 12:19 AM, Chuck Phares wrote:

> Hello Ken,
>
> I hope you don't mind me contacting you directly.  I came across your
> SQL Translator project on CPAN.  What a wonderful bit of coding!
> Count me amongst the grateful users of this very useful tool.
>
> The problem I am trying to solve is porting a bunch of (very complex)
> view definitions from Sybase to PostgreSQL.  Most of the required
> changes are pretty obvious, but I am not very familiar with the ansi
> join syntax.  My sql experience is based on older software, I guess.
> I am only familiar with joins via the 'where' clause.
>
> I was hoping you might be able to point me in the right direction.  I
> have spent quite a bit of time searching for some sort of tool which
> can translate queries written with Sybase proprietary outer join
> syntax to ansi standard outer join syntax.  This seems like the
> perfect candidate for automated translation.  The grammar rules are
> well defined and understood.  Imagine my surprise when I came up empty
> after banging on Google for several hours!
>
> Are there any resources you can point me to which can re-write
> old-style joins to the newer syntax?
>
> I appriciate any assistance you can lend me, and thanks again for such
> a useful program.
>
> Sincerely,
>
> Chuck Phares

Chuck,

I'm glad you found SQLT and that it has been useful to you.  I've =20
never had any luck finding the ANSI standards for SQL, and running =20
down any formalized grammar rules for the proprietary dbs has been =20
much harder than for the open-source alternatives (where you can just =20
look at the source!).  Further, SQLT has never attempted to translate =20
any SELECT/UPDATE/DELETE statements -- we stick to just CREATE/ALTER/=20
DROP, those statements having to do with the structure of a database, =20
not the manipulation of data -- and, of course, the interesting parts =20
of views are the SELECTs.  So SQLT may not be what you need to =20
translate your views, but perhaps we can still help, if only to =20
provide moral support and technical guidance.  :-)

I'm cc'ing the list here as we have some pretty sharp Sybase guys in =20
the project.  Perhaps they can help.

ky


-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log
files
for problems?  Stop!  Download the new AJAX search engine that makes
searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D103432&bid=3D230486&dat=3D=
121642
--=20
sqlfairy-developers mailing list
sqlfairy-developers@...
https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers

Hi list,

I try to convert my own postgresql schema to a "standard" postgresql
schema with sql::translator as follows:
sqlt -f PostgreSQL -t PostgreSQL mytest.sql > foo.sql

mytest.sql:
-------
CREATE TABLE "test" (
  "Id"  bigserial NOT NULL PRIMARY KEY,
  "description" text,
  CONSTRAINT check_descr CHECK ("description" IN ('start','end'))
);
--------

This results in an empty foo.sql.
It seems to have problems with the "CHECK"-part. Any suggestions how to
solve this problem ?

Best Regards,

Matthias Hofherr

--=20
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 Matthias Hofherr             EMail: Matthias_Hofherr@...
 GeNUA Gesellschaft fuer Netzwerk- und Unix-Administration mbH
 Domagkstra=DFe 7, D-85551 Kirchheim     Tel.+49 (89) 99 19 50-0
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

On 1/31/06, Matt S Trout <dbix-class@...> wrote:
> On Mon, Jan 30, 2006 at 09:31:01PM -0600, Ken Youens-Clark wrote:

> As noted in a different e-mail, we already have one. And it doesn't use T=
T :)

What does it use? :)

> The primary usage for DBIx::Class (and if they were to adopt it, Jift::DB=
I)
> users will be to generate SQL schemas off their ORM definitions, since th=
e
> ORMs contain all the necessary information plus some extra metadata that
> doesn't really need to go to SQL.

> Maybe the best answer is to split the package so that the ORM and similar
> template-y producers end up in a separate package which depends on TT?

Where's the value in splitting up a toolkit like SQLT on dependency
lines? Where
does that leave the poor user that wants to have DBxyz support but doesn't
have because he didn't realise that it used TemplateX to produce it's
DDL and DBIx::Jifty::Class only specified SQLT as a dependancy not
SQLT::TemplateX?

There's a lot of value to users /and/ developers of SQLT working as a
cohesive whole with a well defined set of dependencies and
default/preferred tools for jobs like SQL Parsing and text templating.

If the project were to be split up into separate distributions it
would make more sense to do it along database flavour. This would also
be the time to add product version support (brand plus version e.g Pg
7.3 vs Pg 8.1) :

SQL::Translator    - SQLT::Schema*, Parser and Producer Base classes
and a test suite (with schemas)
SQL::Translator::Flavour::* - Parsers and producers for a specific
Database Tool (e.g SQT::Flavour::Pg::72::Producer)

Then you only install what you want and get the tools to do what you need.

Frankly, I'm amazed that I can't get a response to an email I sent
containing an
updated DBI parser for PostgreSQL but this particular question drags
on for a month.

Clayton

P.S. Ken: I too am working on a Class::DBI producer with relationship
and column inflation support. I will mail the list separately about
it.
--

Clayton Scott
clayton.scott@...

Hrrm, I look away for a few days, and the list wakes up.. I should use a 
better mail client...

Anyway, on the subject:

As Matt said, we've now got a DBIx::Class parser, which creates SQLT 
schemas from a loaded DBIx::Class Schema, and a producer which spits out 
an all-in-one DBIx::Class schema definition file. Theres no 
create-in-memory one yet, but will be when I figure out how ;)

Ken, others:

Having done this, Ive realised SQLT is a bit self-contained about some 
things it maybe shouldnt be, for example, all the parser and producer args 
are stuck in the "sqlt" script, which means if I write an external 
parser/producer, and want a new arg, I have to hack SQLT files. 

Any objection to me attempting to make SQLT allow the Parsers/Producers 
define their required arguments, and the "sqlt" script query them? (via 
SQL::Translator->parser, when it sets which parser we're using, etc, 
maybe?) It would also need to query them when outputting the "usage" 
message..

This would presumably be needed it we start splitting SQLT into bits 
anyway..

hmm, there was something else, but Ive forgotten what it was..

Jess

On Tue, 31 Jan 2006, Matt S Trout wrote:

> On Mon, Jan 30, 2006 at 08:58:51PM -0600, Ken Youens-Clark wrote:
> > On Oct 15, 2005, at 5:47 AM, Paul Makepeace wrote:
> > 
> > >I'm looking into creating a DBIx::Class producer. Is anyone working on
> > >this already? One thing I noted when using the Class::DBI producer was
> > >that there could very usefully be a base class specifier on the sqlt
> > >command line - OK to do that too?
> > 
> > OK, first off, have I really not checked in on SQLT mail since  
> > October?  I stink.  Sorry.  I have had another kid since then, but,  
> > jeez, that only goes so far!
> > 
> > Anyway, I don't believe anyone has worked on a DBIx::Class producer,  
> > but I'd be very interested in seeing one.  I've been doing a lot  
> > lately with Class::DBI, and I like it but can see it's drawbacks.   
> > I've actually rolled my own CDBI producer (some Perl plus a TT  
> > template) that I'm going to float in updating our CDBI producer, and  
> > I might add the work I did on an SPOPS producer.  The more code  
> > generators the better!
> 
> Jess Robinson has written both parsers and producers; due to the slow
> release cycle of SQL::Translator compared to DBIx::Class we've rolled them
> into DBIx::Class instead; the latest dev release on CPAN (0.04999_06)
> contains them and they'll go "public" when 0.05 ships (hopefully this week)
> 
> -- 
>      Matt S Trout       Offering custom development, consultancy and support
>   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
> Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information
> 
>  + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
> for problems?  Stop!  Download the new AJAX search engine that makes
> searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
> -- 
> sqlfairy-developers mailing list
> sqlfairy-developers@...
> https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
> 
>