Re: [sqlite] Realtime Backup of Database
On Tue, Aug 5, 2008 at 4:36 PM, Till Steinbach <[EMAIL PROTECTED]> wrote: > Hi Ingo! > Although I'm limited to C-Code your code would be really useful for > me. The triggers will be the same for me. When I have the right > triggers the most difficult part is done. The idea with the seperate > table for logging is great. I have no idea yet how to log whole > statements. I'm looking forward to see your code. > > greetings Till Side note: I once looked into using rsync to reduce remote firmware update times for an embedded device over a slow link, and I found just what you were finding -- rsync's overhead is HUGE unless you're dealing with tens or hundreds of megabytes. That said, these devices can also be configured remotely, and they can also request a refresh of all their configuration settings in the event of a problem. The way I accomplished this is by giving each setting a "dirty" flag. When the setting is changed for any reason, the "dirty" flag is set. When the device reports in (so I know that it is still working), it checks for any "dirty" settings and includes them in the report. When the server they report to receives and stores those settings, it sends back a response indicating such. Upon receiving that response, the device clears the "dirty" flag for all settings. This scenario works fine so long as it is impossible for a setting to be changed while the device is reporting in. This is possible for my devices, but it may not be for yours. If that is the case, then a more sophisticated solution will do the job: First, create a table called "generation": create table generation ( id int AUTOINCREMENT not null primary key, -- the autoincrement is kind of important here date date not null default(current_timestamp), reported int not null ) Then, when a configuration row (or other row that needs to be tracked) is to be inserted/changed, do the following steps: 1. Get the max(id) from generation where reported=0. 2. If that's null, insert a new row into generation with reported=0 and get the new row ID 3. Insert/update the relevant row, including generationId= When the device needs to report in: 1. If the 'generation' table is empty, there is nothing to do. Stop now. 2. Select the maximum generation ID from the 'generation' table. We will call this generation G. 3. Mark every generation with ID <= G.ID as reported. 4. Report in, including all rows with generation.Id <= G.ID 5. If the server confirms receipt of the data, delete all rows from generation where generation.Id <= G.ID That *should* make sure that no row gets missed, but I'd feel better if somebody else could sanity check and confirm. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating Indexes
Jeffrey Becker wrote: > I have a table 'SiteMap' defined as : > > Create Table SiteMap > ( > NodeID blob not null PRIMARY KEY, > Title text NOT NULL UNIQUE, > Url text NOT NULL > ); > > I'd like to index on the node's parent value as defined by the > expression ancestor(NodeID,1). 'ancestor' being a user defined > function. > However sqlite doesnt permit indicies on expressions, only columns. > Why is this? More importantly what's my best bet on achieveing > something similar? Create Table SiteMap ( NodeID blob not null PRIMARY KEY, ParentID blob not null, Title text NOT NULL UNIQUE, Url text NOT NULL ); Then index on (ParentID, NodeID) or something like that. -Steve ___ 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] Using transactions gives SQLITE_ERROR
1. yes the methods should still work. 2. PRAGMA synchronous= OFF and PRAGMA journal_mode = OFF will put your DB at risk of corruption, I'd only do this if you can re-create the db from scratch. 3. Using threads will not help Only one connection to the DB can be writing. 4. OK TRY: BEGIN transaction open select cursor, (use either method 1 or 2 from post). Use method 2 if small result set is expected. Perform updates... Close select cursor (fiinalize or reset). Commit transaction. --- On Tue, 8/5/08, Yatin Salian <[EMAIL PROTECTED]> wrote: From: Yatin Salian <[EMAIL PROTECTED]> Subject: [sqlite] Using transactions gives SQLITE_ERROR To: sqlite-users@sqlite.org Date: Tuesday, August 5, 2008, 2:12 PM hi, I am currently using sqlite version 3.5.9 on WinCE 5.0. As per the posts in the forums I do understand that when a cursor is open on a connection we cannot update the database using transactions on the same thread. open cursor... begin transaction; update statements commit transaction. finalize cursor But I found the below post in the forums which suggests different methods for updating the database using transactions when a cursor is open. http://www.mail-archive.com/sqlite-users@sqlite.org/msg08044.html I tried the first method in the post .. But I am still getting SQLITE_ERROR .. with the error description as "cannot commit transaction - SQL statements in progress". Some tips on the queries listed below may help me in resolving the issue. 1) Do the methods mentioned in the link above still hold or am I missing something... ?? 2) The whole reason of using the transactions is to increase the speed as the updates take time on USB disks. Is there any other way of increasing the speed other than PRAGMA synchronous= OFF and PRAGMA journal_mode = OFF ?? 3) Can updating the database using transaction from different threads with work around solve this issue? If yes, then I would be greatful if the details of the work around are provided. 4) If I update the database in the below mentioned sequence without transactions then everything works fine open cursor... update statements finalize cursor As per the sqlite documentation every statement that is executed is treated as a transaction and the database is updated even if the cursor is open.How is this transaction in auto commit mode different from the transactions created by using "begin transaction" and "commit transactions"?? /cheers, Yatin Salian The people who mind don't matter and the people who matter don't mind. ___ 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
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] Realtime Backup of Database
Hi Ingo! Although I'm limited to C-Code your code would be really useful for me. The triggers will be the same for me. When I have the right triggers the most difficult part is done. The idea with the seperate table for logging is great. I have no idea yet how to log whole statements. I'm looking forward to see your code. greetings Till > > I've written a small C# app to add the triggers needed for statement > logging to all or a selected subset of the database tables. > I can send you the source if it is useful for you. > > Ingo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Creating Indexes
I have a table 'SiteMap' defined as : Create Table SiteMap ( NodeID blob not null PRIMARY KEY, Title text NOT NULL UNIQUE, Url text NOT NULL ); I'd like to index on the node's parent value as defined by the expression ancestor(NodeID,1). 'ancestor' being a user defined function. However sqlite doesnt permit indicies on expressions, only columns. Why is this? More importantly what's my best bet on achieveing something similar? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Realtime Backup of Database
Hello! I think you need to add callback function for insert operations and are logging in the function. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Realtime Backup of Database
Till Steinbach wrote: > I don't get the right idea for my application. Something like > capturing the querys that update the database and transmit them to the > remote system would fit the purpose, but that seems to me very > complicated to teach the database. > You could write triggers for the insert, update and delete events, which write SQL statements for the events into a separate table. Then write a small app or a thread in your main app to read this table periodically and send the statements to your backup database where they are executed by a third app. Report success or failure back to the master db and delete the succesfully executed statements from the logging table. I've written a small C# app to add the triggers needed for statement logging to all or a selected subset of the database tables. I can send you the source if it is useful for you. Ingo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using transactions gives SQLITE_ERROR
hi, I am currently using sqlite version 3.5.9 on WinCE 5.0. As per the posts in the forums I do understand that when a cursor is open on a connection we cannot update the database using transactions on the same thread. open cursor... begin transaction; update statements commit transaction. finalize cursor But I found the below post in the forums which suggests different methods for updating the database using transactions when a cursor is open. http://www.mail-archive.com/sqlite-users@sqlite.org/msg08044.html I tried the first method in the post .. But I am still getting SQLITE_ERROR .. with the error description as "cannot commit transaction - SQL statements in progress". Some tips on the queries listed below may help me in resolving the issue. 1) Do the methods mentioned in the link above still hold or am I missing something... ?? 2) The whole reason of using the transactions is to increase the speed as the updates take time on USB disks. Is there any other way of increasing the speed other than PRAGMA synchronous= OFF and PRAGMA journal_mode = OFF ?? 3) Can updating the database using transaction from different threads with work around solve this issue? If yes, then I would be greatful if the details of the work around are provided. 4) If I update the database in the below mentioned sequence without transactions then everything works fine open cursor... update statements finalize cursor As per the sqlite documentation every statement that is executed is treated as a transaction and the database is updated even if the cursor is open.How is this transaction in auto commit mode different from the transactions created by using "begin transaction" and "commit transactions"?? /cheers, Yatin Salian The people who mind don't matter and the people who matter don't mind. ___ 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] SQLite and updating VIEWs
Hey all, A month later, I have an updated plan for this with many more details. I'd really appreciate it if folks would take a look and point out any issues that you see. Here's my blog post explaining the current plan with extensive details: http://shawnwilsher.com/archives/169 Cheers, Shawn Wilsher Mozilla Developer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Realtime Backup of Database
Hi everyone, i need to backup a sqlite database on a remote device for configuration redundancy purposes. Due to a really slow connection between the devices triggering rsync is not the best solution yet. Although rsync is efficient it is transfering kilobytes of data to see what it has to update. Because the updates are always one-way (from the live database to the backup database) it must be sufficient only to transfer the updates. I don't get the right idea for my application. Something like capturing the querys that update the database and transmit them to the remote system would fit the purpose, but that seems to me very complicated to teach the database. I'm stuck with my problem so perhaps here is someone with a really clever idea. Sorry for my bad english, greetings from hamburg, germany Till ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting malformed database on windows ce device
Hi
I have a winodows CE device with a sd-flash card. The application is a
collecting data from an application and to save power it goes to sleep as
often as possible. Data is also collected at regular intervals (up to once
every 2 minutes). The database is located on an SD-flash card.
After a boot I get the following error:
Can't open database: database disk image is malformed (11)
I checked the database with
PRAGMA integrity_check;
And this returns the following:
Page 490: sqlite3BtreeInitPage() returns error code 11
Page 489 is never used
Below is how I initiate the database (just parts of the source that deals
with the database setup):
const char *version = sqlite3_libversion();
sqlite3_enable_shared_cache(true);
sqlite3_soft_heap_limit(NIP_SQLITE_SOFTHEAP_LIMIT); //Setting softheap
limit to 3MB
error = openDB(dbLocation);
rc = exec("PRAGMA locking_mode = EXCLUSIVE;");
rc = exec("PRAGMA synchronous = FULL;");
rc = exec("PRAGMA cache_size = 1000;");
rc = exec("BEGIN;");
rc = exec("create table IF NOT EXISTS data_records ( id integer primary key
autoincrement,deployment_id integer,rectime datetime,datatype integer,data
blob);");
rc = exec("create index IF NOT EXISTS idx_datadate ON data_records
(rectime);");
rc = exec("create index IF NOT EXISTS idx_datatype ON data_records
(datatype);");
rc = exec("create index IF NOT EXISTS idx_depid ON data_records
(deployment_id);");
rc = exec("create table IF NOT EXISTS deps (id integer primary key
autoincrement,stime datetime,desc varchar(100),dcfg blob);");
rc = exec("create trigger IF NOT EXISTS fifo_limit_dep_rec after delete on
deps begin delete from data_records where deployment_id = old.id; end;");
rc = exec("COMMIT;");
sqlite3_close(m_db);
error = openDB(m_dbFileName);
The exec() function is basicly:
function exec() {
sqlite3_prepare16_v2(m_db,query,-1,&stmt,0);
sqlite3_step(stmt);
sqlite3_finalize(stmt);
}
The openDB function is basicly:
rc = sqlite3_open16((LPWSTR)((LPCTSTR)dbLocation),&m_db);
sqlite3_extended_result_codes(m_db,true);
sqlite3_progress_handler(m_db,10,progress_callback,NULL);
rc = exec("CREATE TABLE test(id integer);");//Just to be 100%
sure that database is opened and that there are no problems.
rc = exec("DROP TABLE test;");
Is this the right way to use the database or am I doing something wrong?
This is a rather critical application so it is important that the database
is not corrupted. It is OK if I loose a few datasamples on powerloss, but
corruption is not acceptable.
Best regards
Jardar
___
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
Re: [sqlite] FTS3: (1) cloning index rows? (2) not storing original text?
On Mon, Aug 4, 2008 at 3:31 PM, yaroslavp <[EMAIL PROTECTED]> wrote:
> After some searching I still could not find the answers to the following
> questions. Any answers or pointers would be greatly appreciated.
>
> (1) In my FTS-enabled database, I want to clone some document, stored in a
> binary format. (Which means copying the same contents to a new row with a
> different ID). I do not have immediate access to the original fulltext of
> the document, but I have it's FTS3 index in the database. Can I somehow
> clone the FTS3 virtual row, like I can clone the document row, without
> retrieving and re-indexing the fulltext?
No. While I can't say for certain this will never happen, it's sort
of an esoteric thing to have. Do other database systems have this
kind of thing?
> (2) Is it possible to optimize the size of the database by eliminating the
> storage of the original text in FTS3 virtual table? I found this possible
> for FTS2 ("FTS: Custom Tokenizer / Stop Words" thread), but is there a
> similar reliable method for FTS3?
I don't see why whatever you're doing for fts2 wouldn't work for fts3.
The code is very very very similar.
Long-term I think it would be useful to allow an index-only mode like
this, but to do it right would require fts to handle document
deletions in some different fashion. There are potentially other
reasons to make changes that would let this happen, but it's a pretty
big project so it probably won't happen anytime soon.
-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
