Re: [sqlite] Re: Db copy
Iulian Popescu wrote: I looked at the .dump feature implementation in shell.c as well as how vacuum is implemented. Based on that an idea will be to iterate through the metainformation on the SQLITE_MASTER table in order to recreate the new Db schema and for each table run SQL statements that will transfer the content from the old db into the new one. However, I was wondering if there is an easier or computationally less expensive way to accomplish the same thing (do it without going through each table) something like cloning the B-Tree representing the database. Iulian, I looked through the vacuum code as well and that seems like the best approach to me. I think you might be looking for optimizations that aren't needed. The copy operation should be very quick with only two SQL statements executed for each table, one to copy the schema, and one to copy the contents. The data copy itself will be reading records from one memory database and writing that record to the other memory database, one record at a time, but without any conversion from the internal record format. While not as fast as a direct memory copy, it should be pretty quick. I don't think there is any way to clone the B-trees that make up the database. There is certainly no public API to access the B-trees, so even if something works today, there is no guarantee that it will work with the next version of SQLite. If you really need to copy the database faster than the SQL copy allows, you might want to talk to Richard Hipp about some paid support. He can surely let you know if it is possible, and may be able to do it for you for a reasonable fee. HTH Dennis Cote
RE: [sqlite] Re: Db copy
Dennis, I looked at the .dump feature implementation in shell.c as well as how vacuum is implemented. Based on that an idea will be to iterate through the metainformation on the SQLITE_MASTER table in order to recreate the new Db schema and for each table run SQL statements that will transfer the content from the old db into the new one. However, I was wondering if there is an easier or computationally less expensive way to accomplish the same thing (do it without going through each table) something like cloning the B-Tree representing the database. Iulian. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 7:29 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Db copy Iulian Popescu wrote: Thank you very much, but this will require to modify the library as far as I understand. I would rather not do that, is that any other less intrusive way? Iulian, I wasn't suggesting that you modify the library. It is an open source program. I was suggesting that you appropriate some of the source that is used to implement functions similar to what you are trying to do, and incorporate it into your application. The functions in shell.c use the library APIs in the same way as your application. Copy the useful stuff out and modify it to do exactly what you need to do in your own application. It seemed like a good place to start to me. HTH Dennis Cote
[sqlite] Re: Db copy
Iulian Popescu iulianp-3XWcajfmz4ZWk0Htik3J/[EMAIL PROTECTED] wrote: Does anybody have an idea what would be the easiest way to completely copy an attached database to another empty attached database? Making a copy of the underlying file. Igor Tandetnik
RE: [sqlite] Re: Db copy
I'm really sorry - I forgot to mention that both databases are in memory. Iulian. -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 5:32 PM To: SQLite Subject: [sqlite] Re: Db copy Iulian Popescu iulianp-3XWcajfmz4ZWk0Htik3J/[EMAIL PROTECTED] wrote: Does anybody have an idea what would be the easiest way to completely copy an attached database to another empty attached database? Making a copy of the underlying file. Igor Tandetnik
Re: [sqlite] Re: Db copy
Iulian Popescu wrote: I'm really sorry - I forgot to mention that both databases are in memory. Iulian, In that case you will have to copy the contents. The easiest way to do that is probably to modify the code the sqlite shell uses to do a database dump. Instead of writing the generated SQL out to a file like the dump command does, execute the generated SQL statements to create the same tables and records in the second attached database. The shell command is implemented in a few functions in shell.c. I believe there is some similar code in the vacuum functions that copy the entire database to a new file without converting everything into SQL text. This might be faster. You can look at the code in vacuum.c HTH Dennis Cote
RE: [sqlite] Re: Db copy
Thank you very much, but this will require to modify the library as far as I understand. I would rather not do that, is that any other less intrusive way? Iulian. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 6:50 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Db copy Iulian Popescu wrote: I'm really sorry - I forgot to mention that both databases are in memory. Iulian, In that case you will have to copy the contents. The easiest way to do that is probably to modify the code the sqlite shell uses to do a database dump. Instead of writing the generated SQL out to a file like the dump command does, execute the generated SQL statements to create the same tables and records in the second attached database. The shell command is implemented in a few functions in shell.c. I believe there is some similar code in the vacuum functions that copy the entire database to a new file without converting everything into SQL text. This might be faster. You can look at the code in vacuum.c HTH Dennis Cote
Re: [sqlite] Re: Db copy
Iulian Popescu wrote: Thank you very much, but this will require to modify the library as far as I understand. I would rather not do that, is that any other less intrusive way? Iulian, I wasn't suggesting that you modify the library. It is an open source program. I was suggesting that you appropriate some of the source that is used to implement functions similar to what you are trying to do, and incorporate it into your application. The functions in shell.c use the library APIs in the same way as your application. Copy the useful stuff out and modify it to do exactly what you need to do in your own application. It seemed like a good place to start to me. HTH Dennis Cote
