ATTACH, query and differentiate

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

ATTACH, query and differentiate

Andrew Gatt-2
Hi,

I'm not sure if what i am trying to acheive is possible, but if i explain what i'm trying to do someone may be able to point me in the right direction.

I have a database table that holds some information, with each entry having a unique id. So when i query the database i can get a list of ids to pass around. This is a server program on a network and so passing integer ids to clients keeps things simple.

I now want to be able to give the option of opening a second database file, with the same table structure - which i believe is acheived using the ATTACH command. However the ids sent to the client still need to be unique, it is my understanding that a SELECT command will treat both databases as one and so return potentially overlapping ids. Is there anyway to craft the SELECT command - or otherwise -  so that i can know where the id came from and do some form of id mangling, keeping in mind i will need to do the same process in reverse to access the information, given the mangled id.

Any ideas?
Thanks
Andrew
Reply | Threaded
Open this post in threaded view
|

RE: ATTACH, query and differentiate

Griggs, Donald
Hi Andrew,

Perhaps I shouldn't be replying since I don't think I'm understanding your
question fully.

When you wrote:
   "with each entry having a unique id"
did you mean that you are creating a unique identifier, or instead that you
are making  use of the ROWID within sqlite?

Regarding:
   "it is my understanding that a SELECT command will treat both databases
as one and so return potentially overlapping ids"

Can you elaborate on that understanding?   Sqlite 3 can create transactions
that are atomic across multiple databases (if the main database is not an
in-memory one), but I'm not sure what "treat as ... one" would otherwise
mean.

Even for a single database, ROWID values are unique ONLY within a particular
table.  Therefore (to my understanding) if you call out a particular
database, a particular table, and a particular ROWID, then you've uniquely
identified a row.

Dan Kennedy just referred to the ATTACH documentation which may be helpful:
  http://www.sqlite.org/lang_attach.html


Donald Griggs


Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.



-----Original Message-----
From: Andrew Gatt [mailto:[hidden email]]
Sent: Thursday, August 04, 2005 6:56 AM
To: [hidden email]
Subject: [sqlite] ATTACH, query and differentiate
Reply | Threaded
Open this post in threaded view
|

RE: ATTACH, query and differentiate

Andrew Gatt-2
In reply to this post by Andrew Gatt-2
Donald,

Thanks for your reply, its all a bit new to me, so i'm still trying to get my head round it and hopefully be able to explain myself better.

I'm actually creating the unique ids, it is just a number.

I now think my understanding of an attached database is wrong, my first thoughts were that a single SELECT command would query both databases, but now i believe i would have to use the following syntax to do a query on both databases:

SELECT id, name FROM database1.main, database2.main WHERE name == %me% ORDER BY name

Which would produce alphabetically sorted results from both attached tables? But because both the databases will have been created separately, they could have overlapping ids, which i need to avoid.

To avoid overlapping ids i could manually get all the ids from database1 and (for arguments sake) add a '1' to the end and all the ids from database2 and add a '2' to the end. If the client then requested further information on id 6731, i would know it was from database 1, strip that digit out and so get further information from id 673 database1. Doing it this way however means i have to alphabetically sort the results from the two tables, after the two queries.

So i guess i was just wondering if this was all possible in a single query, or sqlite gave some indication of where the results were coming from so i could tag them for id mangling.

I hope i've managed to explain myself better and not made it even worse!

Thanks
Andrew

-----Original Message-----
From: Griggs, Donald [mailto:[hidden email]]
Sent: 04 August 2005 13:56
To: '[hidden email]'
Subject: RE: [sqlite] ATTACH, query and differentiate


Hi Andrew,

Perhaps I shouldn't be replying since I don't think I'm understanding your
question fully.

When you wrote:
   "with each entry having a unique id"
did you mean that you are creating a unique identifier, or instead that you
are making  use of the ROWID within sqlite?

Regarding:
   "it is my understanding that a SELECT command will treat both databases
as one and so return potentially overlapping ids"

Can you elaborate on that understanding?   Sqlite 3 can create transactions
that are atomic across multiple databases (if the main database is not an
in-memory one), but I'm not sure what "treat as ... one" would otherwise
mean.

Even for a single database, ROWID values are unique ONLY within a particular
table.  Therefore (to my understanding) if you call out a particular
database, a particular table, and a particular ROWID, then you've uniquely
identified a row.

Dan Kennedy just referred to the ATTACH documentation which may be helpful:
  http://www.sqlite.org/lang_attach.html


Donald Griggs


Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.



-----Original Message-----
From: Andrew Gatt [mailto:[hidden email]]
Sent: Thursday, August 04, 2005 6:56 AM
To: [hidden email]
Subject: [sqlite] ATTACH, query and differentiate
Reply | Threaded
Open this post in threaded view
|

Re: ATTACH, query and differentiate

Jay Sprenkle
In reply to this post by Andrew Gatt-2
> I now want to be able to give the option of opening a second database file, with the same table structure - which i believe is acheived using the ATTACH command. However the ids sent to the client still need to be unique, it is my understanding that a SELECT command will treat both databases as one and so return potentially overlapping ids. Is there anyway to craft the SELECT command - or otherwise -  so that i can know where the id came from and do some form of id mangling, keeping in mind i will need to do the same process in reverse to access the information, given the mangled id.


If you attach the second database a join will let you connects rows in
one table with rows in another table. Then a select can return 2
columns, the id from each table. The two id's concatenated will create
a simple unique key that can be used to find rows in either table.

example:
two tables, named 'a' and 'b'
select a.id, b.id
 from a
 inner join b on b.something = a.something

result:
a.id    b.id
23       34

create a single key by using 6 digits (or binary if you understand
that) for each:

000023000034

There's your unique key that can be used to find rows in either or both tables.
Reply | Threaded
Open this post in threaded view
|

Re: ATTACH, query and differentiate

Dennis Cote
In reply to this post by Andrew Gatt-2
Andrew Gatt wrote:

>Donald,
>
>Thanks for your reply, its all a bit new to me, so i'm still trying to get my head round it and hopefully be able to explain myself better.
>
>I'm actually creating the unique ids, it is just a number.
>
>I now think my understanding of an attached database is wrong, my first thoughts were that a single SELECT command would query both databases, but now i believe i would have to use the following syntax to do a query on both databases:
>
>SELECT id, name FROM database1.main, database2.main WHERE name == %me% ORDER BY name
>
>  
>
>Which would produce alphabetically sorted results from both attached tables? But because both the databases will have been created separately, they could have overlapping ids, which i need to avoid.
>
>To avoid overlapping ids i could manually get all the ids from database1 and (for arguments sake) add a '1' to the end and all the ids from database2 and add a '2' to the end. If the client then requested further information on id 6731, i would know it was from database 1, strip that digit out and so get further information from id 673 database1. Doing it this way however means i have to alphabetically sort the results from the two tables, after the two queries.
>
>So i guess i was just wondering if this was all possible in a single query, or sqlite gave some indication of where the results were coming from so i could tag them for id mangling.
>
>I hope i've managed to explain myself better and not made it even worse!
>
>Thanks
>Andrew
>  
>
Andrew,

I think what you are after is more like this;

select id * 10 + 1 as uid, name from main.sometable where name like '%me%'
union
select id * 10 + 2 as uid, name from database2.sometable where name like
'%me%'
order by name

This will combine the results of the two queries, one on the sometable
of each database, and adjust the returned id so that the correct
database can be determined from the uid (by using the modulus operator
to get the database number, ie. uid % 10 is the database). This should
work as long as your id values are small enough.

To return other data from a record specified by this uid value you can
use a case statement like this;

select case :uid % 10
    when 1 then
       select name, otherdata from main.sometable where id = :uid / 10
    when 2 then
       select name, otherdata from database2.sometable where id = :uid / 10
    end;

You can then bind the :uid value (or generate the sql string on the fly)
and get the requested data from the correct database.

HTH
Dennis Cote

Reply | Threaded
Open this post in threaded view
|

RE: ATTACH, query and differentiate

Andrew Gatt-2
In reply to this post by Andrew Gatt-2
>>Donald,
>>
>>Thanks for your reply, its all a bit new to me, so i'm still trying to get my head round it and hopefully be able to explain myself better.
>>
>>I'm actually creating the unique ids, it is just a number.
>>
>>I now think my understanding of an attached database is wrong, my first thoughts were that a single SELECT command would query both databases, but now i believe i would have to use the following syntax to do a query on both databases:
>>
>>SELECT id, name FROM database1.main, database2.main WHERE name == %me% ORDER BY name
>>
>>
>>
>>Which would produce alphabetically sorted results from both attached tables? But because both the databases will have been created separately, they could have overlapping ids, which i need to avoid.
>>
>>To avoid overlapping ids i could manually get all the ids from database1 and (for arguments sake) add a '1' to the end and all the ids from database2 and add a '2' to the end. If the client then requested further information on id 6731, i would know it was from database 1, strip that digit out and so get further information from id 673 database1. Doing it this way however means i have to alphabetically sort the results from the two tables, after the two queries.
>>
>>So i guess i was just wondering if this was all possible in a single query, or sqlite gave some indication of where the results were coming from so i could tag them for id mangling.
>>
>>I hope i've managed to explain myself better and not made it even worse!
>>
>>Thanks
>>Andrew
>>  
>>
>Andrew,

>I think what you are after is more like this;
>
>select id * 10 + 1 as uid, name from main.sometable where name like '%me%'
>union
>select id * 10 + 2 as uid, name from database2.sometable where name like
>'%me%'
>order by name
>
>This will combine the results of the two queries, one on the sometable
>of each database, and adjust the returned id so that the correct
>database can be determined from the uid (by using the modulus operator
>to get the database number, ie. uid % 10 is the database). This should
>work as long as your id values are small enough.
>
>To return other data from a record specified by this uid value you can
>use a case statement like this;
>
>select case :uid % 10
>    when 1 then
>       select name, otherdata from main.sometable where id = :uid / 10
>    when 2 then
>       select name, otherdata from database2.sometable where id = :uid / 10
>    end;
>
>You can then bind the :uid value (or generate the sql string on the fly)
>and get the requested data from the correct database.
>
>HTH
>Dennis Cote

Dennis,

A ha! This seems perfect, one quick question, when you say "id values are small enough", are we talking about an integer size limit hard coded into sqlite? I mean with the *10 is the value of id ultimately limited to 6553? This may cause an issue, however if we are talking 32 bit then that won't be a problem.

Thank you for your help, i'm continually impressed with how much you can do with an sql statement!

Andrew
Reply | Threaded
Open this post in threaded view
|

Re: ATTACH, query and differentiate

Dennis Cote
Andrew Gatt wrote:

> A ha! This seems perfect, one quick question, when you say "id values
> are small enough", are we talking about an integer size limit hard
> coded into sqlite? I mean with the *10 is the value of id ultimately
> limited to 6553? This may cause an issue, however if we are talking 32
> bit then that won't be a problem.
>
>Thank you for your help, i'm continually impressed with how much you can do with an sql statement!
>
>  
>
Andrew,

Actually internally sqlite3 supports 64 bit integers, so you should be
safe for even the largest tables.

However, your interface may only support 32 bit integers, so you may
need to use this lower limit. Which would mean the ids in your tables
must be less than about 200,000,000. This should still be plenty for
most applications. ;-)

Glad to help.
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: ATTACH, query and differentiate

Dennis Cote
In reply to this post by Andrew Gatt-2
Andrew,

I though I should point out a couple of problem with my proposed case
statement before you run into them. A case expression can only return a
single value, so you can't return name and otherdata at the same time,
you can only get one field (otherdata in this case). Also the subselects
in the case expression must be enclosed in brackets. Finally you may
want to alias the result of the case expression to give it a name that
matches the data you are returning since the default name of an
expression result column is the expression itself (which isn't really
very useful in this case). Try this instead;

select
    case :uid % 10
    when 1 then
        (select otherdata from main.sometable where id = :uid / 10)
    when 2 then
        (select otherdata from database2.sometable where id = :uid / 10)
    end
as otherdata;

Dennis Cote