Converting SQL Dialects

Has anyone come across / written a script that will convert one
"flavor" or Dialect of SQL to another?

I need to convert Visual FoxPro 6.0 style WHERE clauses to MySQL.

For the most part the problems are converting VFP functions to the
equivalent SQL. For example, Visual FoxPro has a function inlist()
that is used like inlist(X,1,2,3) which converts to the MySQL query "X
IN (1,2,3)". That's easy enough (relatively speaking) but VFP also has
stuff like "EMPTY(X)" where any of Null, the Empty String (for Char),
0000-00-00 (or the VFP equivalent anyways for dates), False (for
Boolean), 0 (for Numeric) are considered empty without needing to
know the data type. So that starts getting a lot more complex since I'd
need to check the data type of the field in the "right" table... to be
able to convert it to something like (X is null OR X="") or (X is null
OR x=0) etc...

These are for customer "stored" queries... I've already manually
converted "system" queries and I'm frustrated to the point of giving
up and adding a column "untested" and let the end user figure it out
but that seems bad from the standpoint of "lazy" and "poor customer
experience".

Thanks!

Matt

P.S. I'm also going to post this to the MySQL general list but my fear
is that they MIGHT say "We only know MySQL so we can't help you with
that other DBMS" I'm hoping that by posting here someone might say
"well it's not to MySQL but I ran script XYZ to convert my VFP to
PostgreSQL..." or similar.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Converting SQL Dialects

On Tue, 2009-07-21 at 13:46 -0400, Matt Neimeyer wrote:
> Has anyone come across / written a script that will convert one
> "flavor" or Dialect of SQL to another?
>
> I need to convert Visual FoxPro 6.0 style WHERE clauses to MySQL.
>
> For the most part the problems are converting VFP functions to the
> equivalent SQL. For example, Visual FoxPro has a function inlist()
> that is used like inlist(X,1,2,3) which converts to the MySQL query "X
> IN (1,2,3)". That's easy enough (relatively speaking) but VFP also has
> stuff like "EMPTY(X)" where any of Null, the Empty String (for Char),
> 0000-00-00 (or the VFP equivalent anyways for dates), False (for
> Boolean), 0 (for Numeric) are considered empty without needing to
> know the data type. So that starts getting a lot more complex since I'd
> need to check the data type of the field in the "right" table... to be
> able to convert it to something like (X is null OR X="") or (X is null
> OR x=0) etc...
>
> These are for customer "stored" queries... I've already manually
> converted "system" queries and I'm frustrated to the point of giving
> up and adding a column "untested" and let the end user figure it out
> but that seems bad from the standpoint of "lazy" and "poor customer
> experience".
>
> Thanks!
>
> Matt
>
> P.S. I'm also going to post this to the MySQL general list but my fear
> is that they MIGHT say "We only know MySQL so we can't help you with
> that other DBMS" I'm hoping that by posting here someone might say
> "well it's not to MySQL but I ran script XYZ to convert my VFP to
> PostgreSQL..." or similar.
>

I'm not sure it's as easy as you think it might be. As you get more into
the various flavours of SQL, you notice their little idiosyncrasies that
only exist within that one particular language branch. As such, it's
often a task best left to people to try and convert from one to the
other, rather than leave it to a machine. How complex are the queries
that you are trying to convert anyway?

Thanks
Ash
www.ashleysheridan.co.uk


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Converting SQL Dialects

Um... It depends? :) These are customer entered queries and vary based
on the end user and the customizations they have. It could be as
simple as WHERE inlist(SalesPerson,"Bob","Bill","Fred") OR it could be
something 12 lines long that pulls in criteria from multiple tables
each of those with their own criteria.

And by easy I meant simply that I would feel comfortable hacking
together something to handle inlist(x,1,2,3) and change it to "X in
(1,2,3)" it's the "rest" that worries me.

Having slept on it, I'm probably going to write something that
converts IN, checks for a list of "forbidden" words and then flags the
resulting updated query if it contains any of those words. This way I
can catch some of the low hanging fruit.

My hope is that if X% of queries don't contain foxpro specific
functions, and IN auto-conversions covers another X% of upgrades, and
auto-convert FunctionX (whatever it is...) gives us another X% of
upgrades... that this will result in a hopefully small number of saved
queries that are flagged for manual upgrading. (And not be so painful
in development that it still nets us saved time)

Matt

On Tue, Jul 21, 2009 at 1:54 PM, Ashley
Sheridan wrote:
> On Tue, 2009-07-21 at 13:46 -0400, Matt Neimeyer wrote:
>> Has anyone come across / written a script that will convert one
>> "flavor" or Dialect of SQL to another?
>>
>> I need to convert Visual FoxPro 6.0 style WHERE clauses to MySQL.
>>
>> For the most part the problems are converting VFP functions to the
>> equivalent SQL. For example, Visual FoxPro has a function inlist()
>> that is used like inlist(X,1,2,3) which converts to the MySQL query "X
>> IN (1,2,3)". That's easy enough (relatively speaking) but VFP also has
>> stuff like "EMPTY(X)" where any of Null, the Empty String (for Char),
>> 0000-00-00 (or the VFP equivalent anyways for dates), False (for
>> Boolean), 0 (for Numeric) are considered empty without needing to
>> know the data type. So that starts getting a lot more complex since I'd
>> need to check the data type of the field in the "right" table... to be
>> able to convert it to something like (X is null OR X="") or (X is null
>> OR x=0) etc...
>>
>> These are for customer "stored" queries... I've already manually
>> converted "system" queries and I'm frustrated to the point of giving
>> up and adding a column "untested" and let the end user figure it out
>> but that seems bad from the standpoint of "lazy" and "poor customer
>> experience".
>>
>> Thanks!
>>
>> Matt
>>
>> P.S. I'm also going to post this to the MySQL general list but my fear
>> is that they MIGHT say "We only know MySQL so we can't help you with
>> that other DBMS" I'm hoping that by posting here someone might say
>> "well it's not to MySQL but I ran script XYZ to convert my VFP to
>> PostgreSQL..." or similar.
>>
>
> I'm not sure it's as easy as you think it might be. As you get more into
> the various flavours of SQL, you notice their little idiosyncrasies that
> only exist within that one particular language branch. As such, it's
> often a task best left to people to try and convert from one to the
> other, rather than leave it to a machine. How complex are the queries
> that you are trying to convert anyway?
>
> Thanks
> Ash
> www.ashleysheridan.co.uk
>
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Converting SQL Dialects

On Tue, Jul 21, 2009 at 2:13 PM, Matt Neimeyer wrote:
> Um... It depends? :) These are customer entered queries and vary based
> on the end user and the customizations they have. It could be as
> simple as WHERE inlist(SalesPerson,"Bob","Bill","Fred") OR it could be
> something 12 lines long that pulls in criteria from multiple tables
> each of those with their own criteria.
>
> And by easy I meant simply that I would feel comfortable hacking
> together something to handle inlist(x,1,2,3) and change it to "X in
> (1,2,3)" it's the "rest" that worries me.
>
> Having slept on it, I'm probably going to write something that
> converts IN, checks for a list of "forbidden" words and then flags the
> resulting updated query if it contains any of those words. This way I
> can catch some of the low hanging fruit.
>
> My hope is that if X% of queries don't contain foxpro specific
> functions, and IN auto-conversions covers another X% of upgrades, and
> auto-convert FunctionX (whatever it is...) gives us another X% of
> upgrades... that this will result in a hopefully small number of saved
> queries that are flagged for manual upgrading. (And not be so painful
> in development that it still nets us saved time)
>
> Matt

You might even be able to convert EMPTY(X) to COALESCE(X, '') = ''.
MySQL seems to be pretty forgiving with its implicit type-casting.

Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Converting SQL Dialects

On Tue, Jul 21, 2009 at 06:54:35PM +0100, Ashley Sheridan wrote:

> On Tue, 2009-07-21 at 13:46 -0400, Matt Neimeyer wrote:
> > Has anyone come across / written a script that will convert one
> > "flavor" or Dialect of SQL to another?
> >
> > I need to convert Visual FoxPro 6.0 style WHERE clauses to MySQL.
> >
> > For the most part the problems are converting VFP functions to the
> > equivalent SQL. For example, Visual FoxPro has a function inlist()
> > that is used like inlist(X,1,2,3) which converts to the MySQL query "X
> > IN (1,2,3)". That's easy enough (relatively speaking) but VFP also has
> > stuff like "EMPTY(X)" where any of Null, the Empty String (for Char),
> > 0000-00-00 (or the VFP equivalent anyways for dates), False (for
> > Boolean), 0 (for Numeric) are considered empty without needing to
> > know the data type. So that starts getting a lot more complex since I'd
> > need to check the data type of the field in the "right" table... to be
> > able to convert it to something like (X is null OR X="") or (X is null
> > OR x=0) etc...
> >
> > These are for customer "stored" queries... I've already manually
> > converted "system" queries and I'm frustrated to the point of giving
> > up and adding a column "untested" and let the end user figure it out
> > but that seems bad from the standpoint of "lazy" and "poor customer
> > experience".
> >
> > Thanks!
> >
> > Matt
> >
> > P.S. I'm also going to post this to the MySQL general list but my fear
> > is that they MIGHT say "We only know MySQL so we can't help you with
> > that other DBMS" I'm hoping that by posting here someone might say
> > "well it's not to MySQL but I ran script XYZ to convert my VFP to
> > PostgreSQL..." or similar.
> >
>
> I'm not sure it's as easy as you think it might be. As you get more into
> the various flavours of SQL, you notice their little idiosyncrasies that
> only exist within that one particular language branch. As such, it's
> often a task best left to people to try and convert from one to the
> other, rather than leave it to a machine. How complex are the queries
> that you are trying to convert anyway?

I have to agree with Ash here. I was a FoxPro 2.6a DOS programmer (just
at the beginning of VFP). We hired a programmer who did a lot of his
queries in FoxPro's crippled version of SQL, but the rest of us just
used regular FoxPro for queries. Remembering what FoxPro SQL was like,
and now having worked with MySQL and PostgreSQL, I wouldn't wish the
conversion of one SQL dialect to another on anyone.

Paul

--
Paul M. Foster

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Converting SQL Dialects

> You might even be able to convert EMPTY(X) to COALESCE(X, '') = ''.
> MySQL seems to be pretty forgiving with its implicit type-casting.

Hmm... The new system I've written properly handles the datatype and
EMPTY... So this would be a hack to much around with regexs to replace
EMPTY in customer built selects.

Now I fiddled around with this on 5.1.33-community on Windows and I
get the following odd results...

select coalesce(0,"")=0,coalesce("","")=0,coalesce(0,"")=0,coalesce ("","")=0;
returns 1, 1, 1, 1

but...

select coalesce(0,"")="",coalesce("","")="",coalesce(0,"")="",coale sce("","")="";
returns 0, 1, 0, 1

Which implies that in certain circumstances "" = 0 but 0 != "" (unless
I'm missing something).

Either way it looks like I can use coalesce(X,"")=0 which should be useful!

Matt

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Converting SQL Dialects

On Thu, Jul 23, 2009 at 3:39 PM, Matt Neimeyer wrote:
>> You might even be able to convert EMPTY(X) to COALESCE(X, '') =3D ''.
>> MySQL seems to be pretty forgiving with its implicit type-casting.
>
> Hmm... The new system I've written properly handles the datatype and
> EMPTY... So this would be a hack to much around with regexs to replace
> EMPTY in customer built selects.
>
> Now I fiddled around with this on 5.1.33-community on Windows and I
> get the following odd results...
>
> select coalesce(0,"")=3D0,coalesce("","")=3D0,coalesce(0,"")=3D0,co alesce=
("","")=3D0;
> returns 1, 1, 1, 1
>
> but...
>
>  select coalesce(0,"")=3D"",coalesce("","")=3D"",coalesce(0,"")=3D"" =
,coalesce("","")=3D"";
> returns 0, 1, 0, 1
>
> Which implies that in certain circumstances "" =3D 0 but 0 !=3D "" (unles=
s
> I'm missing something).
>
> Either way it looks like I can use coalesce(X,"")=3D0 which should be use=
ful!
>
> Matt
>

That is interesting. The whole purpose of the COALESCE function in SQL
is to substitute a non-null value for a null value. Since there aren't
any nulls in either of your statements, you are effectively running
these:

select 0=3D0, ''=3D0, 0=3D0, ''=3D0
select 0=3D'', ''=3D'', 0=3D'', ''=3D''

COALESCE(X, '') =3D 0 is probably the better option though, since the
result of COALESE should be implicitly cast to the datatype of X
before performing the equality comparison. In MySQL, casting an empty
string to an integer would result in the value 0 while casting to a
date would produce the date value equivalent to the integer 0.

The benefit of COALESCE is that it should be standard SQL. I know it
works on MySQL and SQL Server, and I think it works on Oracle and
others as well.

You may take a performance hit for using COALESCE in conditions like
that since it usually means the condition can't use an index, but
considering you're moving from using similar logic in FoxPro that may
not matter for you.

Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php