Re: [sqlite] I suspect not - but is the database separator configurable?

[Ryan Johnson]

On 03/02/2013 10:16 AM, Stephen Chrzanowski wrote:

Just had a thought;

You could do a few things, unfortunately all at the code base level;

1> I don't know if Python will handle it, but I know most other languages
have a string-replace function, or, more specifically, in Delphi, there is
a "format" command in which you would create a string, fill it in with
parameters (Like %s, %d, etc) and then you have an array of variables the
system would fill in.  Create a variable that contains the SQL you need,
but replace what needs to be quoted with a format specifier.  Then,
regenerate the SQL with either quoted or non-quoted variables.  This would
become a PITA if you had 30 field names or so, however.  Not to mention bug
prone.

2> In line with a true string replace, replace all the field names in the
string to a format like '%db..table%' then run a string replace against all
the possible field names and replace them with either a quoted string, or a
non-quoted string.

3> Another option is to manually mangle the string based on what Sybase is
being fed.  So, if you're going to toss it into SQLite:
- scan the string for double-periods, and remember the position
- backup, character by character, until the first non-valid character, and
put a single/double quote
- go forward+1 from the remembered position until the first non-valid
character, and drop the same quote.

4> The last thing, off the top of my head, would be to locate each and
every SQL string, and move it into a file of its own, then duplicate for
SQLite and mangle as needed.  Along with option 1, nightmare to manage.

My thought, #3 would probably be the easiest to implement, as it'd require
one function to be created and an appropriate result returned then going
through the code and applying the function where needed, but it'd probably
the slowest since you'll be practically touching each and every character
in the string before its fed to the DBMS.  #2 would be next in line as a
search and replace based on field names would PROBABLY be more quicker
instead of mangling strings character by character, but it'd involve going
in and modifying the SQL calls themselves, as well as adding the
appropriate string replace commands.
Another option: wrap the appropriate functions from the various classes 
in the python sqlite3 module, and have them convert '..' to '.' in 
queries before passing on. You'd just have to make sure not to replace 
'..' appearing inside string constants.


Assuming you're lazy like me, and only issue sql using methods of the 
Connection object, that means interposing on Connection.execute and 
Connection.executemany; the Connection object has read-only attributes, 
so you'd probably need a wrapper class.


Ryan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I suspect not - but is the database separator configurable?

[Stephen Chrzanowski]

Just had a thought;

You could do a few things, unfortunately all at the code base level;

1> I don't know if Python will handle it, but I know most other languages
have a string-replace function, or, more specifically, in Delphi, there is
a "format" command in which you would create a string, fill it in with
parameters (Like %s, %d, etc) and then you have an array of variables the
system would fill in.  Create a variable that contains the SQL you need,
but replace what needs to be quoted with a format specifier.  Then,
regenerate the SQL with either quoted or non-quoted variables.  This would
become a PITA if you had 30 field names or so, however.  Not to mention bug
prone.

2> In line with a true string replace, replace all the field names in the
string to a format like '%db..table%' then run a string replace against all
the possible field names and replace them with either a quoted string, or a
non-quoted string.

3> Another option is to manually mangle the string based on what Sybase is
being fed.  So, if you're going to toss it into SQLite:
- scan the string for double-periods, and remember the position
- backup, character by character, until the first non-valid character, and
put a single/double quote
- go forward+1 from the remembered position until the first non-valid
character, and drop the same quote.

4> The last thing, off the top of my head, would be to locate each and
every SQL string, and move it into a file of its own, then duplicate for
SQLite and mangle as needed.  Along with option 1, nightmare to manage.

My thought, #3 would probably be the easiest to implement, as it'd require
one function to be created and an appropriate result returned then going
through the code and applying the function where needed, but it'd probably
the slowest since you'll be practically touching each and every character
in the string before its fed to the DBMS.  #2 would be next in line as a
search and replace based on field names would PROBABLY be more quicker
instead of mangling strings character by character, but it'd involve going
in and modifying the SQL calls themselves, as well as adding the
appropriate string replace commands.


On Sun, Feb 3, 2013 at 3:50 AM, Message Adams wrote:

>
> Thanks for everyone's help. Unfortunately, as Ryan highlighted, the double
> quotes around the table 'database..name' cannot be handled by sybase. It's
> a shame as I rather like Sqlite and works soo well with Python.
>
> All the best,
> Marc
>
>
>
>
> On 02/02/13 07:56, Mohit Sindhwani wrote:
>
>> Hi Ryan,
>>
>> On 2/2/2013 1:55 AM, Ryan Johnson wrote:
>>
>>> That would break sybase, though: the quotes would also tell it to treat
>>> the db name and periods as part of the table name, too:
>>>
>>> sqlite3> create table foo(x,y);
>>> sqlite3> .tables
>>> foo
>>> sqlite3> select * from "main.foo";
>>> Error: no such table: main.foo
>>>
>>
>> Since the OP (you aren't the OP, right?)  seems to have control over the
>> code, I wonder if a compile-time macro could be used to attach the quotes
>> around the database name only in the test environment. But, otherwise, I
>> think we're at the end of what can be done with SQLite.
>>
>> Best Regards,
>> Mohit.
>>
>>
>>
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I suspect not - but is the database separator configurable?

[Message Adams]

Thanks for everyone's help. Unfortunately, as Ryan highlighted, the 
double quotes around the table 'database..name' cannot be handled by 
sybase. It's a shame as I rather like Sqlite and works soo well with Python.


All the best,
Marc



On 02/02/13 07:56, Mohit Sindhwani wrote:

Hi Ryan,

On 2/2/2013 1:55 AM, Ryan Johnson wrote:
That would break sybase, though: the quotes would also tell it to 
treat the db name and periods as part of the table name, too:


sqlite3> create table foo(x,y);
sqlite3> .tables
foo
sqlite3> select * from "main.foo";
Error: no such table: main.foo


Since the OP (you aren't the OP, right?)  seems to have control over 
the code, I wonder if a compile-time macro could be used to attach the 
quotes around the database name only in the test environment. But, 
otherwise, I think we're at the end of what can be done with SQLite.


Best Regards,
Mohit.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I suspect not - but is the database separator configurable?

[Mohit Sindhwani]

Hi Ryan,

On 2/2/2013 1:55 AM, Ryan Johnson wrote:
That would break sybase, though: the quotes would also tell it to 
treat the db name and periods as part of the table name, too:


sqlite3> create table foo(x,y);
sqlite3> .tables
foo
sqlite3> select * from "main.foo";
Error: no such table: main.foo


Since the OP (you aren't the OP, right?)  seems to have control over the 
code, I wonder if a compile-time macro could be used to attach the 
quotes around the database name only in the test environment. But, 
otherwise, I think we're at the end of what can be done with SQLite.


Best Regards,
Mohit.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I suspect not - but is the database separator configurable?

[Ryan Johnson]

On 01/02/2013 12:28 PM, Mohit Sindhwani wrote:

Hi Marc,

On 1/2/2013 10:42 PM, message adams wrote:

My applications actually run against sybase, but I'd love to use a
connection to an in-memory sqlite to carry out my testing.
As part of the unit-test, I'd pass the sqlite conenction into my source
code hoping it would be none the wiser.

The only real problem I see, is sybase uses a double period separator
between the database and table. e.g.
select * from database..table

... whereas sqlite
select * from database.table


Can you use double quotes in the query?

For example, this works in SQLite3

sqlite> create table "db..abc"(p INTEGER, q INTEGER);
sqlite> .tables
db..abc
sqlite> select * from "db..abc";
sqlite>
That would break sybase, though: the quotes would also tell it to treat 
the db name and periods as part of the table name, too:


sqlite3> create table foo(x,y);
sqlite3> .tables
foo
sqlite3> select * from "main.foo";
Error: no such table: main.foo


Ryan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I suspect not - but is the database separator configurable?

[Mohit Sindhwani]

Hi Marc,

On 1/2/2013 10:42 PM, message adams wrote:

My applications actually run against sybase, but I'd love to use a
connection to an in-memory sqlite to carry out my testing.
As part of the unit-test, I'd pass the sqlite conenction into my source
code hoping it would be none the wiser.

The only real problem I see, is sybase uses a double period separator
between the database and table. e.g.
select * from database..table

... whereas sqlite
select * from database.table


Can you use double quotes in the query?

For example, this works in SQLite3

sqlite> create table "db..abc"(p INTEGER, q INTEGER);
sqlite> .tables
db..abc
sqlite> select * from "db..abc";
sqlite>

Best Regards,
Mohit.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I suspect not - but is the database separator configurable?

[message adams]

Thanks for getting back to me Pavel.
Unfortunately the code needs to include the database prefix, so it looks
like I cannot use sqlite in this instance.

I'll have to find another opportunity to use sqlite :-)

thanks again,
Marc





On 1 February 2013 14:53, Pavel Ivanov  wrote:

> On Fri, Feb 1, 2013 at 6:42 AM, message adams 
> wrote:
> > Greetings;
> >
> > I've recently started using sqlite within Python, to help unit-test my
> > applications.
> >
> > My applications actually run against sybase, but I'd love to use a
> > connection to an in-memory sqlite to carry out my testing.
> > As part of the unit-test, I'd pass the sqlite conenction into my source
> > code hoping it would be none the wiser.
> >
> > The only real problem I see, is sybase uses a double period separator
> > between the database and table. e.g.
> > select * from database..table
>
> No, it's not double period separator. It's actually
> database.table_owner.table, but table_owner can be omitted, in that
> case it's defaulted to 'dbo'.
>
> > ... whereas sqlite
> > select * from database.table
> >
> >
> > I assume the sqlite database separator is not configurable, but was
> > wondering if anybody's resolved a similar issue?
>
> SQLite doesn't have a notion of users, thus it doesn't and won't
> support the "double period" separator. SQLite even has a different
> notion of databases that can go before table name. So to make queries
> work both in SQLite and in Sybase don't use database name at all, make
> it "select * from table".
>
>
> Pavel
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I suspect not - but is the database separator configurable?

[Pavel Ivanov]

On Fri, Feb 1, 2013 at 6:42 AM, message adams  wrote:
> Greetings;
>
> I've recently started using sqlite within Python, to help unit-test my
> applications.
>
> My applications actually run against sybase, but I'd love to use a
> connection to an in-memory sqlite to carry out my testing.
> As part of the unit-test, I'd pass the sqlite conenction into my source
> code hoping it would be none the wiser.
>
> The only real problem I see, is sybase uses a double period separator
> between the database and table. e.g.
> select * from database..table

No, it's not double period separator. It's actually
database.table_owner.table, but table_owner can be omitted, in that
case it's defaulted to 'dbo'.

> ... whereas sqlite
> select * from database.table
>
>
> I assume the sqlite database separator is not configurable, but was
> wondering if anybody's resolved a similar issue?

SQLite doesn't have a notion of users, thus it doesn't and won't
support the "double period" separator. SQLite even has a different
notion of databases that can go before table name. So to make queries
work both in SQLite and in Sybase don't use database name at all, make
it "select * from table".


Pavel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] I suspect not - but is the database separator configurable?

[message adams]

Greetings;

I've recently started using sqlite within Python, to help unit-test my
applications.

My applications actually run against sybase, but I'd love to use a
connection to an in-memory sqlite to carry out my testing.
As part of the unit-test, I'd pass the sqlite conenction into my source
code hoping it would be none the wiser.

The only real problem I see, is sybase uses a double period separator
between the database and table. e.g.
select * from database..table

... whereas sqlite
select * from database.table


I assume the sqlite database separator is not configurable, but was
wondering if anybody's resolved a similar issue?


Many thanks for any help,
Marc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users