Re: [sqlite] Clone SQLite databases
csabi81 wrote: > Thanks Steve, I think I am on the right way. > It is a difference between the page sizes of the two databases. I have > opened each database with Firefox SQLite Manager, and at the DB Settings I > have seen a lot of differences, such as page size, Cache size, max page > count etc. How can I Copy all these settings from the original DB to the > clone. Is there a safe way, or I can only copy one by one, by querying and > setting these Settings? > Why don't you simply copy the entire database file (in a transaction to prevent corruption) and then delete the records you don't want? This copy will have all the same pragma settings as the original. This could optionally be followed by a vacuum command to reclaim the now unused space. Note that a vacuum really does yet another copy of the database file. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
I think I have DONE :) I miss one more PRAGMA to copy, the schema_version, which creates the conflict with Firefox, and results a corrupt database. Now everythings work fine. I have only one question: how can I query all the PRAGMAS? I am useing an SQLite library in Delphi, I think that I should use Sqlite3_Prepare_v2, it has a parameter Stmt:pointer; but I dont know how to work with this and obtain for example: PRAGMA page_size; ---> 26 Again, thanks to everyone for help, and if someone can help me in this final problem I will appreciate. csabi81 wrote: > > I have done again a test, its nice to work with you guys :) > > So, I query all the PRAGMAs from the original database, and note them, > temporary on a sheet of paper; > then after dooing a .dump obtain the hole schema of the database, CREATE > TABLE, INDEX, INSERT stc..., saved in a file. I append from the begining > the PRAGMAS, and than create the datanase file based on this schema file. > Good and bad news: the good one is that the size of the file is exactly > the same as the original one, the bad news is that firefox3 still cannot > work with the cloned database. > Is there a priority on how to apply PRAGMAs? this is the only thing I can > imagine that can affect the cloned database file. :( > > Thanks to all > > > > csabi81 wrote: >> >> Thanks Steve, I think I am on the right way. >> It is a difference between the page sizes of the two databases. I have >> opened each database with Firefox SQLite Manager, and at the DB Settings >> I have seen a lot of differences, such as page size, Cache size, max page >> count etc. How can I Copy all these settings from the original DB to the >> clone. Is there a safe way, or I can only copy one by one, by querying >> and setting these Settings? >> >> Thanks >> >> >> Steve Kallenborn wrote: >>> >>> Could there be a different page_size, resulting in differing amounts of >>> 'wasted' space? >>> >>> Check with "PRAGMA page_size;" or sqlite3_analyzer program. >>> >>> Thanks >>>Steve >>> >>> csabi81 wrote: I have made .dump for each database, original and clone, and saved into separated files. Than compare them. Differences are in INSERT INTO entries, in the clone database those entries are missing which I did not copy from the original. But the CREATE TABLE, CREATE INDEX, CREATE TRIGGER Commands are the same 100% >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> >> > > -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p18869124.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
I have done again a test, its nice to work with you guys :) So, I query all the PRAGMAs from the original database, and note them, temporary on a sheet of paper; then after dooing a .dump obtain the hole schema of the database, CREATE TABLE, INDEX, INSERT stc..., saved in a file. I append from the begining the PRAGMAS, and than create the datanase file based on this schema file. Good and bad news: the good one is that the size of the file is exactly the same as the original one, the bad news is that firefox3 still cannot work with the cloned database. Is there a priority on how to apply PRAGMAs? this is the only thing I can imagine that can affect the cloned database file. :( Thanks to all csabi81 wrote: > > Thanks Steve, I think I am on the right way. > It is a difference between the page sizes of the two databases. I have > opened each database with Firefox SQLite Manager, and at the DB Settings I > have seen a lot of differences, such as page size, Cache size, max page > count etc. How can I Copy all these settings from the original DB to the > clone. Is there a safe way, or I can only copy one by one, by querying and > setting these Settings? > > Thanks > > > Steve Kallenborn wrote: >> >> Could there be a different page_size, resulting in differing amounts of >> 'wasted' space? >> >> Check with "PRAGMA page_size;" or sqlite3_analyzer program. >> >> Thanks >>Steve >> >> csabi81 wrote: >>> I have made .dump for each database, original and clone, and saved into >>> separated files. Than compare them. >>> Differences are in INSERT INTO entries, in the clone database those >>> entries >>> are missing which I did not copy from the original. But the CREATE >>> TABLE, >>> CREATE INDEX, CREATE TRIGGER Commands are the same 100% >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p18850113.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
Thanks Steve, I think I am on the right way. It is a difference between the page sizes of the two databases. I have opened each database with Firefox SQLite Manager, and at the DB Settings I have seen a lot of differences, such as page size, Cache size, max page count etc. How can I Copy all these settings from the original DB to the clone. Is there a safe way, or I can only copy one by one, by querying and setting these Settings? Thanks Steve Kallenborn wrote: > > Could there be a different page_size, resulting in differing amounts of > 'wasted' space? > > Check with "PRAGMA page_size;" or sqlite3_analyzer program. > > Thanks >Steve > > csabi81 wrote: >> I have made .dump for each database, original and clone, and saved into >> separated files. Than compare them. >> Differences are in INSERT INTO entries, in the clone database those >> entries >> are missing which I did not copy from the original. But the CREATE TABLE, >> CREATE INDEX, CREATE TRIGGER Commands are the same 100% >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p1884.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
Could there be a different page_size, resulting in differing amounts of 'wasted' space? Check with "PRAGMA page_size;" or sqlite3_analyzer program. Thanks Steve csabi81 wrote: > I have made .dump for each database, original and clone, and saved into > separated files. Than compare them. > Differences are in INSERT INTO entries, in the clone database those entries > are missing which I did not copy from the original. But the CREATE TABLE, > CREATE INDEX, CREATE TRIGGER Commands are the same 100% > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
I have made .dump for each database, original and clone, and saved into separated files. Than compare them. Differences are in INSERT INTO entries, in the clone database those entries are missing which I did not copy from the original. But the CREATE TABLE, CREATE INDEX, CREATE TRIGGER Commands are the same 100% Derrell Lipman wrote: > > On Tue, Aug 5, 2008 at 10:31 AM, csabi81 <[EMAIL PROTECTED]> wrote: > >> >> I have made the test with following results: >> Useing "SELECT ALL * FROM WHERE... order by id;" on the >> original >> database and >> "SELECT ALL * FROM order by id;" on the cloned database give >> me >> the same results. >> outputing the results from SELECT in different files give me two 100% >> identical files. > > > You might also try doing ".dump" on each database from the sqlite shell > and > compare the CREATE entries for tables, triggers, etc. to ensure that they > were created identically. > > Derrell > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p18846161.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
Triggers are also copied. the original database has no views; anything escapes from me? any other parts of a database? It can be a problem because I manipulate Firefox3 sqlite databases, I am working on a small app which securely deletes some well known apps History, one of these apps is Firefox3. Its a project for my studdies. The problem is that after cloning one of the firefox databases it becomes corrupt or something, because Firefox seems not to work properly. So that is why I worried about size. ken-33 wrote: > > I wouldn't be too worried about the size then if you have the indexes and > the data is there. > > How about triggers? Views etc? > > > > --- On Tue, 8/5/08, csabi81 <[EMAIL PROTECTED]> wrote: > From: csabi81 <[EMAIL PROTECTED]> > Subject: Re: [sqlite] Clone SQLite databases > To: sqlite-users@sqlite.org > Date: Tuesday, August 5, 2008, 1:47 PM > > Yes I have Indexes in original database, but I create them in the clone, > useing the SQL obtained from mastertable, as well as the TRIGGERS. > > > Stephen Woodbridge wrote: >> >> csabi81 wrote: >>> Hi everyone >>> >>> I want to clone a database: copy all data from original database to > the >>> destination database with a condition, so not all the entries need to > be >>> copied. I have tried the following: >>> Obtain SQL from mastertable and create the tables in the new database, >>> and >>> using INSERT to copy all data with a WHERE. My problem is that the >>> destination file size became very small; original was 160KB the >>> destination >>> is 55KB, I have deleted only a few entries. When I opened each > database >>> with >>> a SQLite Browser it seems everything OK, but I do not understand why > the >>> filesize became so small? >>> Am I loosing something to copy, or create? >>> >>> Tx for any help. >>> >>> P.S> sorry for my english :P >> >> Do you have indexes on the original DB that you have not added to the >> new DB? >> >> -Steve >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- > View this message in context: > http://www.nabble.com/Clone-SQLite-databases-tp18827472p18837494.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > 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 > > -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p18840065.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
I wouldn't be too worried about the size then if you have the indexes and the data is there. How about triggers? Views etc? --- On Tue, 8/5/08, csabi81 <[EMAIL PROTECTED]> wrote: From: csabi81 <[EMAIL PROTECTED]> Subject: Re: [sqlite] Clone SQLite databases To: sqlite-users@sqlite.org Date: Tuesday, August 5, 2008, 1:47 PM Yes I have Indexes in original database, but I create them in the clone, useing the SQL obtained from mastertable, as well as the TRIGGERS. Stephen Woodbridge wrote: > > csabi81 wrote: >> Hi everyone >> >> I want to clone a database: copy all data from original database to the >> destination database with a condition, so not all the entries need to be >> copied. I have tried the following: >> Obtain SQL from mastertable and create the tables in the new database, >> and >> using INSERT to copy all data with a WHERE. My problem is that the >> destination file size became very small; original was 160KB the >> destination >> is 55KB, I have deleted only a few entries. When I opened each database >> with >> a SQLite Browser it seems everything OK, but I do not understand why the >> filesize became so small? >> Am I loosing something to copy, or create? >> >> Tx for any help. >> >> P.S> sorry for my english :P > > Do you have indexes on the original DB that you have not added to the > new DB? > > -Steve > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p18837494.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] Clone SQLite databases
Yes I have Indexes in original database, but I create them in the clone, useing the SQL obtained from mastertable, as well as the TRIGGERS. Stephen Woodbridge wrote: > > csabi81 wrote: >> Hi everyone >> >> I want to clone a database: copy all data from original database to the >> destination database with a condition, so not all the entries need to be >> copied. I have tried the following: >> Obtain SQL from mastertable and create the tables in the new database, >> and >> using INSERT to copy all data with a WHERE. My problem is that the >> destination file size became very small; original was 160KB the >> destination >> is 55KB, I have deleted only a few entries. When I opened each database >> with >> a SQLite Browser it seems everything OK, but I do not understand why the >> filesize became so small? >> Am I loosing something to copy, or create? >> >> Tx for any help. >> >> P.S> sorry for my english :P > > Do you have indexes on the original DB that you have not added to the > new DB? > > -Steve > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p18837494.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
csabi81 wrote: > Hi everyone > > I want to clone a database: copy all data from original database to the > destination database with a condition, so not all the entries need to be > copied. I have tried the following: > Obtain SQL from mastertable and create the tables in the new database, and > using INSERT to copy all data with a WHERE. My problem is that the > destination file size became very small; original was 160KB the destination > is 55KB, I have deleted only a few entries. When I opened each database with > a SQLite Browser it seems everything OK, but I do not understand why the > filesize became so small? > Am I loosing something to copy, or create? > > Tx for any help. > > P.S> sorry for my english :P Do you have indexes on the original DB that you have not added to the new DB? -Steve ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
On Tue, Aug 5, 2008 at 10:31 AM, csabi81 <[EMAIL PROTECTED]> wrote: > > I have made the test with following results: > Useing "SELECT ALL * FROM WHERE... order by id;" on the > original > database and > "SELECT ALL * FROM order by id;" on the cloned database give me > the same results. > outputing the results from SELECT in different files give me two 100% > identical files. You might also try doing ".dump" on each database from the sqlite shell and compare the CREATE entries for tables, triggers, etc. to ensure that they were created identically. Derrell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
I have made the test with following results: Useing "SELECT ALL * FROM WHERE... order by id;" on the original database and "SELECT ALL * FROM order by id;" on the cloned database give me the same results. outputing the results from SELECT in different files give me two 100% identical files. I have used the commandline utility in this case, but the same results shows the SQLite browser too. ken-33 wrote: > > Doesnt seem like that much data. you could do a sanity check and select > the row counts from each table and use the where clause on the original. > > You could use the .output command selecting the data to different files > (use an order by clause) then compare the two files. > > HTH > > --- On Tue, 8/5/08, csabi81 <[EMAIL PROTECTED]> wrote: > > From: csabi81 <[EMAIL PROTECTED]> > Subject: Re: [sqlite] Clone SQLite databases > To: sqlite-users@sqlite.org > Date: Tuesday, August 5, 2008, 6:42 AM > > Thanks for Reply. > > I have tried VACUUM, nothing happens. The original database file remains > the > same size as before. > What I create in the new database are Tables, Indexes and Triggers as > well. > I dont know if anything escapes from me. > > > Mihai Limbasan wrote: >> >> csabi81 wrote: >>> Hi everyone >>> >>> I want to clone a database: copy all data from original database to > the >>> destination database with a condition, so not all the entries need to > be >>> copied. I have tried the following: >>> Obtain SQL from mastertable and create the tables in the new database, >>> and >>> using INSERT to copy all data with a WHERE. My problem is that the >>> destination file size became very small; original was 160KB the >>> destination >>> is 55KB, I have deleted only a few entries. When I opened each > database >>> with >>> a SQLite Browser it seems everything OK, but I do not understand why > the >>> filesize became so small? >>> Am I loosing something to copy, or create? >>> >>> Tx for any help. >>> >>> P.S> sorry for my english :P >>> >> You might want to try to VACUUM the original database to see whether the >> file size is reduced on it as well. Please read the documentation on the >> VACUUM command here: http://sqlite.org/lang_vacuum.html >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- > View this message in context: > http://www.nabble.com/Clone-SQLite-databases-tp18827472p18829325.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > 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 > > -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p18832398.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
Doesnt seem like that much data. you could do a sanity check and select the row counts from each table and use the where clause on the original. You could use the .output command selecting the data to different files (use an order by clause) then compare the two files. HTH --- On Tue, 8/5/08, csabi81 <[EMAIL PROTECTED]> wrote: From: csabi81 <[EMAIL PROTECTED]> Subject: Re: [sqlite] Clone SQLite databases To: sqlite-users@sqlite.org Date: Tuesday, August 5, 2008, 6:42 AM Thanks for Reply. I have tried VACUUM, nothing happens. The original database file remains the same size as before. What I create in the new database are Tables, Indexes and Triggers as well. I dont know if anything escapes from me. Mihai Limbasan wrote: > > csabi81 wrote: >> Hi everyone >> >> I want to clone a database: copy all data from original database to the >> destination database with a condition, so not all the entries need to be >> copied. I have tried the following: >> Obtain SQL from mastertable and create the tables in the new database, >> and >> using INSERT to copy all data with a WHERE. My problem is that the >> destination file size became very small; original was 160KB the >> destination >> is 55KB, I have deleted only a few entries. When I opened each database >> with >> a SQLite Browser it seems everything OK, but I do not understand why the >> filesize became so small? >> Am I loosing something to copy, or create? >> >> Tx for any help. >> >> P.S> sorry for my english :P >> > You might want to try to VACUUM the original database to see whether the > file size is reduced on it as well. Please read the documentation on the > VACUUM command here: http://sqlite.org/lang_vacuum.html > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p18829325.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] Clone SQLite databases
Thanks for Reply. I have tried VACUUM, nothing happens. The original database file remains the same size as before. What I create in the new database are Tables, Indexes and Triggers as well. I dont know if anything escapes from me. Mihai Limbasan wrote: > > csabi81 wrote: >> Hi everyone >> >> I want to clone a database: copy all data from original database to the >> destination database with a condition, so not all the entries need to be >> copied. I have tried the following: >> Obtain SQL from mastertable and create the tables in the new database, >> and >> using INSERT to copy all data with a WHERE. My problem is that the >> destination file size became very small; original was 160KB the >> destination >> is 55KB, I have deleted only a few entries. When I opened each database >> with >> a SQLite Browser it seems everything OK, but I do not understand why the >> filesize became so small? >> Am I loosing something to copy, or create? >> >> Tx for any help. >> >> P.S> sorry for my english :P >> > You might want to try to VACUUM the original database to see whether the > file size is reduced on it as well. Please read the documentation on the > VACUUM command here: http://sqlite.org/lang_vacuum.html > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p18829325.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clone SQLite databases
csabi81 wrote: > Hi everyone > > I want to clone a database: copy all data from original database to the > destination database with a condition, so not all the entries need to be > copied. I have tried the following: > Obtain SQL from mastertable and create the tables in the new database, and > using INSERT to copy all data with a WHERE. My problem is that the > destination file size became very small; original was 160KB the destination > is 55KB, I have deleted only a few entries. When I opened each database with > a SQLite Browser it seems everything OK, but I do not understand why the > filesize became so small? > Am I loosing something to copy, or create? > > Tx for any help. > > P.S> sorry for my english :P > You might want to try to VACUUM the original database to see whether the file size is reduced on it as well. Please read the documentation on the VACUUM command here: http://sqlite.org/lang_vacuum.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Clone SQLite databases
Hi everyone I want to clone a database: copy all data from original database to the destination database with a condition, so not all the entries need to be copied. I have tried the following: Obtain SQL from mastertable and create the tables in the new database, and using INSERT to copy all data with a WHERE. My problem is that the destination file size became very small; original was 160KB the destination is 55KB, I have deleted only a few entries. When I opened each database with a SQLite Browser it seems everything OK, but I do not understand why the filesize became so small? Am I loosing something to copy, or create? Tx for any help. P.S> sorry for my english :P -- View this message in context: http://www.nabble.com/Clone-SQLite-databases-tp18827472p18827472.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
