Re: [sqlite] preserve column constraints

[Igor Tandetnik]

"ed"  wrote in message
news:d92e6441090522n431d4434gee6a286aa4b8d...@mail.gmail.com
> Does  'INSERT INTO mem_db SELECT * FROM file_db' work the same as
> 'CREATE TABLE mem_db AS SELECT * FROM file_db'  with regard to
> transactions? Are the inserts going to be handled as one large
> transaction in either scenario?

Yes.

> Do I need to explicitly use the BEGIN
> and COMMIT commands?

You may want to, if you want CREATE TABLE and INSERT to happen together 
atomically. You don't need to just for INSERT.

Igor Tandetnik 



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

Re: [sqlite] preserve column constraints

[Igor Tandetnik]

"ed"  wrote in message
news:d92e64410905221106t5c8b77eay9cbc5a4e3ccb8...@mail.gmail.com
> I guess i was assuming the command "create table x as select * from y"
> should preserve column constraints

Well, it can't, in general. Nothing says that a column in the newly 
created table must correspond to a column in existing table. Consider:

create table x as
select 1, a, b+1, c+d from y;

What constraints should be placed on the four columns of table x, and 
why?

Igor Tandetnik 



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

Re: [sqlite] preserve column constraints

[ed]

Does  'INSERT INTO mem_db SELECT * FROM file_db' work the same as
'CREATE TABLE mem_db AS SELECT * FROM file_db'  with regard to
transactions? Are the inserts going to be handled as one large
transaction in either scenario? Do I need to explicitly use the BEGIN
and COMMIT commands?

thanks,
ed

On Fri, May 22, 2009 at 10:50 AM, Igor Tandetnik  wrote:
> "ed"  wrote in message
> news:d92e64410905221021p39a4f833y960370145e187...@mail.gmail.com
>> I have an application that copies my disk based db into an in memory
>> table (":memory:") using the command
>> "create table mem_db as select * from file_db;"
>>
>> The problem is the disk db has a column set to INTEGER PRIMARY KEY,
>> but this is not preserved when the new in memory db is created. When i
>> do a "select * from sqlite_master;" on the in-memory db, there is no
>> IPK. How can I accomplish creating the in-memory db with the file db
>> data and preserve the column configuration from the original table?
>
> You create the table the way you want it, then run INSERT ... SELECT
> statement to populate it.
>
> You may also find this interesting:
> http://sqlite.org/c3ref/backup_finish.html
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] preserve column constraints

[ed]

ok, thanks
I guess i was assuming the command "create table x as select * from y"
should preserve column constraints and maybe i was going about it
wrong.

thanks for the link to the backup api, i will see if this makes sense
for my application.

thanks,
ed

On Fri, May 22, 2009 at 10:50 AM, Igor Tandetnik  wrote:
> "ed"  wrote in message
> news:d92e64410905221021p39a4f833y960370145e187...@mail.gmail.com
>> I have an application that copies my disk based db into an in memory
>> table (":memory:") using the command
>> "create table mem_db as select * from file_db;"
>>
>> The problem is the disk db has a column set to INTEGER PRIMARY KEY,
>> but this is not preserved when the new in memory db is created. When i
>> do a "select * from sqlite_master;" on the in-memory db, there is no
>> IPK. How can I accomplish creating the in-memory db with the file db
>> data and preserve the column configuration from the original table?
>
> You create the table the way you want it, then run INSERT ... SELECT
> statement to populate it.
>
> You may also find this interesting:
> http://sqlite.org/c3ref/backup_finish.html
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] preserve column constraints

[Igor Tandetnik]

"ed"  wrote in message
news:d92e64410905221021p39a4f833y960370145e187...@mail.gmail.com
> I have an application that copies my disk based db into an in memory
> table (":memory:") using the command
> "create table mem_db as select * from file_db;"
>
> The problem is the disk db has a column set to INTEGER PRIMARY KEY,
> but this is not preserved when the new in memory db is created. When i
> do a "select * from sqlite_master;" on the in-memory db, there is no
> IPK. How can I accomplish creating the in-memory db with the file db
> data and preserve the column configuration from the original table?

You create the table the way you want it, then run INSERT ... SELECT 
statement to populate it.

You may also find this interesting: 
http://sqlite.org/c3ref/backup_finish.html

Igor Tandetnik



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