Re: [sqlite] Table Exists Query

[Pavel Ivanov]

Why not make it this way:

select 'database1', lists.* from database1.lists
union all
select 'database2', lists.* from database2.lists;

This way you will know exactly which lists persist in which database.


Pavel

On Sat, Aug 15, 2009 at 10:57 AM, Andrew Gatt wrote:
> Simon Slavin wrote:
>> On 15 Aug 2009, at 3:12pm, Andrew Gatt wrote:
>>
>>
>>> but by my scheme you can't be sure if the list_1 table exists in
>>> database2,
>>>
>>
>> Add a column to your 'lists' tables which says which database they're
>> part of.  So every row of the database will have the same value.
>>
>>
>>
> Unfortunately the database names change (they are assigned as and when
> the storage is attached) and also the list will only return all the
> items if all the databases are present, a smaller subset otherwise. So
> i'd still have to check if the tables in the databases existed - wouldn't i?
>
> Andrew
>
> ___
> 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] Table Exists Query

[Simon Slavin]

On 15 Aug 2009, at 3:57pm, Andrew Gatt wrote:

> Simon Slavin wrote:
>> On 15 Aug 2009, at 3:12pm, Andrew Gatt wrote:
>>
>>> but by my scheme you can't be sure if the list_1 table exists in
>>> database2,
>>
>> Add a column to your 'lists' tables which says which database they're
>> part of.  So every row of the database will have the same value.
>>
>
> Unfortunately the database names change (they are assigned as and when
> the storage is attached)

Wait ... the first time the storage is attached ?  Or each time you  
unplug then replug that storage ?  Are the names of your databases  
changing ?  Do you have some sort of automated system which works out  
which databases to open depending on which storage devices are  
attached ?

> and also the list will only return all the
> items if all the databases are present, a smaller subset otherwise. So
> i'd still have to check if the tables in the databases existed -  
> wouldn't i?

I think a solution would be to make another table which keeps track of  
which database is on which storage device, and keep that table in a  
consistent place which is always available.

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

Re: [sqlite] Table Exists Query

[Andrew Gatt]

Simon Slavin wrote:
> On 15 Aug 2009, at 3:12pm, Andrew Gatt wrote:
>
>   
>> but by my scheme you can't be sure if the list_1 table exists in
>> database2,
>> 
>
> Add a column to your 'lists' tables which says which database they're  
> part of.  So every row of the database will have the same value.
>
>
>   
Unfortunately the database names change (they are assigned as and when
the storage is attached) and also the list will only return all the
items if all the databases are present, a smaller subset otherwise. So
i'd still have to check if the tables in the databases existed - wouldn't i?

Andrew

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

Re: [sqlite] Table Exists Query

[Andrew Gatt]

Roger Andersson wrote:
>
> I have multiple databases, both have a table called "lists" inside this
> table is the names of other tables in the database that make up the lists.
> Each individual list is made up of the tables that have the same name in
> each database (the list is split between different storage devices in this
> case).
> e.g.
>
> database 1
> "lists" list_1, list_2
> "list_1" item_1, item_2
> "list_2" item_1, item_2
>
> database 2
> "lists" list_1, list_3
> "list_1" item_3, item_4
> "list_3" item_1,item_2
>
> To get an output with all the lists of both databases i can use a union
> command on the "lists" table:
>
> select * from database1.lists union select * from database2.lists;
>
> However if i'm not sure which approach to take to get the contents of a
> specific list. I'm trying to replicate this sort of statement:
>
> select * from database1.list_2 union all select * from database2.list_2;
>
> but by my scheme you can't be sure if the list_1 table exists in database2,
> which if i'm right will error the statement and provide no output?
> The other option is to run a set of statements first checking whether the
> table exists and keeping a record of which database does and building the
> statement that way, but this seems inelegant. I was hoping someone would
> have seen this kind of behaviour before and could point me in the right
> direction of a solution? All comments are welcome.
>
> ___
> Maybe something like 
>
> select db1.* from database1.lists db1 union select db2.* from
> database2.lists db2;
> and
> select db1.* from database1.list_2 db1 union all select db2.* from
> database2.list_2 db2;
>
> /Roger
>   
Thanks for the suggestion, but for the second statement as list_2 does
not exist in database2 wouldn't this statement fail with a compile error
rather than give an output?

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

Re: [sqlite] Table Exists Query

[Simon Slavin]

On 15 Aug 2009, at 3:12pm, Andrew Gatt wrote:

> but by my scheme you can't be sure if the list_1 table exists in
> database2,

Add a column to your 'lists' tables which says which database they're  
part of.  So every row of the database will have the same value.

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

Re: [sqlite] Table Exists Query

[Roger Andersson]

Maybe something like 

select db1.* from database1.lists db1 union select db2.* from
database2.lists db2;
and
select db1.* from database1.list_2 db1 union all select db2.* from
database2.list_2 db2;

/Roger
-Ursprungligt meddelande-
Från: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] För Andrew Gatt
Skickat: den 15 augusti 2009 16:13
Till: General Discussion of SQLite Database
Ämne: [sqlite] Table Exists Query

Hi all,

I have multiple databases, both have a table called "lists" inside this
table is the names of other tables in the database that make up the lists.
Each individual list is made up of the tables that have the same name in
each database (the list is split between different storage devices in this
case).
e.g.

database 1
"lists" list_1, list_2
"list_1" item_1, item_2
"list_2" item_1, item_2

database 2
"lists" list_1, list_3
"list_1" item_3, item_4
"list_3" item_1,item_2

To get an output with all the lists of both databases i can use a union
command on the "lists" table:

select * from database1.lists union select * from database2.lists;

However if i'm not sure which approach to take to get the contents of a
specific list. I'm trying to replicate this sort of statement:

select * from database1.list_2 union all select * from database2.list_2;

but by my scheme you can't be sure if the list_1 table exists in database2,
which if i'm right will error the statement and provide no output?
The other option is to run a set of statements first checking whether the
table exists and keeping a record of which database does and building the
statement that way, but this seems inelegant. I was hoping someone would
have seen this kind of behaviour before and could point me in the right
direction of a solution? All comments are welcome.

Thanks,
Andrew
___
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] Table Exists Query

[Andrew Gatt]

Hi all,

I have multiple databases, both have a table called "lists" inside this
table is the names of other tables in the database that make up the
lists. Each individual list is made up of the tables that have the same
name in each database (the list is split between different storage
devices in this case).
e.g.

database 1
"lists" list_1, list_2
"list_1" item_1, item_2
"list_2" item_1, item_2

database 2
"lists" list_1, list_3
"list_1" item_3, item_4
"list_3" item_1,item_2

To get an output with all the lists of both databases i can use a union
command on the "lists" table:

select * from database1.lists union select * from database2.lists;

However if i'm not sure which approach to take to get the contents of a
specific list. I'm trying to replicate this sort of statement:

select * from database1.list_2 union all select * from database2.list_2;

but by my scheme you can't be sure if the list_1 table exists in
database2, which if i'm right will error the statement and provide no
output?
The other option is to run a set of statements first checking whether
the table exists and keeping a record of which database does and
building the statement that way, but this seems inelegant. I was hoping
someone would have seen this kind of behaviour before and could point me
in the right direction of a solution? All comments are welcome.

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