Re: [sqlite] Shared binary plus static lib
On Sunday 07 June 2009 14:26:07 Mark Constable wrote: > Would anyone have a clue as to how I could end up with > /usr/lib/libsqlite3.so.0 > as well as /usr/bin/sqlite3 and /usr/lib/libsqlite3.a ? Another one for the list archives. I'm not sure if this is 100% correct but the result works for me on linux with sqlite from CVS. cc -DTHREADSAFE=0 -fPIC -Os -c sqlite3.c cc -shared sqlite3.o -Os -ldl -Wl,-soname -Wl,libsqlite3.so.0 -o libsqlite3.so.0.8.6 ln -s libsqlite3.so.0.8.6 libsqlite3.so cc -DHAVE_READLINE=1 -fPIC -Os -o sqlite3 ./libsqlite3.so shell.c -ldl -lreadline -ltermcap -I/usr/include/readline ar cru libsqlite3.a sqlite3.o --markc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] large db performance considerations
On 8 Jun 2009, at 4:45am, Paul Perry wrote: > The latest content I have added, has over 31,000 records. A basic > select, > such as: > > Select Content from BookContent where DocumentID = 10; > takes nearly 15 seconds. Are there any ways to optimize this? You need an index, obviously. You can't have an index on a VIRTUAL table. Could you instead create a TEMPORARY table and read the data into it ? That can have indices on all the fields you care about. You can even copy all the entries from the VIRTUAL table into it in one instruction using the form of INSERT that has a SELECT in it. It's faster to load the data into the table first, then create the indices. The other aspect of this is to wonder why you're using a VIRTUAL table in the first place. Why aren't you searching the source data instead of a virtual table ? The SELECT you described doesn't use the fts3 feature. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] large db performance considerations
Hello, I was wondering if anybody has a tip for optimizing the query time on a table. I have read several web pages about optimizing SQLite queries, but have not been able to come to any conclusions. I have a table: CREATE VIRTUAL TABLE BookContent using fts3( DocumentID INTEGER PRIMARY KEY, DocumentType INTEGER, ParentDocumentID INTEGER, Category TEXT, Content TEXT, Author TEXT, SrcFileIdent INTEGER, SrcLineNumber INTEGER); The key pieces of information are the Category, Content and Author. Performance is fine with up to 2000 records. I can do basic queries, and all works well. The latest content I have added, has over 31,000 records. A basic select, such as: Select Content from BookContent where DocumentID = 10; takes nearly 15 seconds. Are there any ways to optimize this? The first thing I did was consider made it a regular SQLite table (droped the FTS support), and used the same table schema, and added an index on Author, Content and Category. However, I did not notice any change in time, and the database size grew from 11MB to 15MB. Any suggestions would be greatly qppreciated. Thank you, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Version 2.1
On 8 Jun 2009, at 1:50am, Shane Harrelson wrote: > http://www.sqlite.org/sqlite-2.8.17.tar.gz I think is the oldest > version still available from the SQLite website. > > BTW, I'm not certain what kind of forensic evidence you can get from > your db if it's empty as you say. Yeah. In fact, reading the bare file in hexdump is going to tell you far more than reading it using the SQLite library. More time- consuming, though. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Version 2.1
http://www.sqlite.org/sqlite-2.8.17.tar.gz I think is the oldest version still available from the SQLite website. BTW, I'm not certain what kind of forensic evidence you can get from your db if it's empty as you say. HTH. -Shane On Sun, Jun 7, 2009 at 5:12 PM, Marcus Haßmann wrote: > I tested BearShare Lite Version 5.2.5.1 > The database is located in C:\Program Files\BearShare\db and it is named > "library.db". > The header of the empty database is "** This file contains an SQLite 2.1 > database **" > > Marcus Haßmann > > Simon Slavin schrieb: >> On 7 Jun 2009, at 8:22pm, Marcus Haßmann wrote: >> >> >>> I tested all versions of sqlite browser already - without success. >>> >> >> Are you certain that this really is a sqlite database, and not just a >> file with the right extension ? Does it start with the right header ? >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-user >> > ___ > 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] SQLite Version 2.1
I tested BearShare Lite Version 5.2.5.1 The database is located in C:\Program Files\BearShare\db and it is named "library.db". The header of the empty database is "** This file contains an SQLite 2.1 database **" Marcus Haßmann Simon Slavin schrieb: > On 7 Jun 2009, at 8:22pm, Marcus Haßmann wrote: > > >> I tested all versions of sqlite browser already - without success. >> > > Are you certain that this really is a sqlite database, and not just a > file with the right extension ? Does it start with the right header ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-user > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Version 2.1
It starts with the right header: "** This file contains an SQLite 2.1 database **" Marcus Haßmann Simon Slavin schrieb: > On 7 Jun 2009, at 8:22pm, Marcus Haßmann wrote: > > >> I tested all versions of sqlite browser already - without success. >> > > Are you certain that this really is a sqlite database, and not just a > file with the right extension ? Does it start with the right header ? > > Simon. > ___ > 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] SQLite Version 2.1
On 7 Jun 2009, at 8:22pm, Marcus Haßmann wrote: > I tested all versions of sqlite browser already - without success. Are you certain that this really is a sqlite database, and not just a file with the right extension ? Does it start with the right header ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Version 2.1
Hi Mihai! I tested all versions of sqlite browser already - without success. The next step will be to compile the old sources of sqlite. Or is there a download repository of older sqlite versions? Then, I will be able to develop a little tool for reading out the contents of this older 2.X database. With kindly regards, Marcus Haßmann Mihai Limbasan schrieb: > Hi there! > > An older version of SQLiteBorwser should do the trick - you can get it > from the SourceForge project page at > http://sourceforge.net/projects/sqlitebrowser/. The direct download link > for a suitable precompiled version would be > > - for Linux: > http://sourceforge.net/project/downloading.php?group_id=87946&filename=sqlitebrowser-1.1-i386.tar.gz > - for Win32: > http://sourceforge.net/project/downloading.php?group_id=87946&filename=sqlitebrowser-1.1-win.zip > - for OS X: > http://sourceforge.net/project/downloading.php?group_id=87946&filename=sqlitebrowser-1.1-osx.dmg > > with the source code at > http://sourceforge.net/project/downloading.php?group_id=87946&filename=sqlite-1.1-src.tar.gz > > If that version doesn't do the trick, you can try other versions of the > same project from > http://sourceforge.net/project/showfiles.php?group_id=87946&package_id=91778 > > Hope this helps. > > Mihai Limbasan > > On 06/06/2009 04:55 PM, Marcus Hassmann / Hassmann-Software wrote: > >> Hello@ all! >> >> I need an old version of SQLite to do a forensic investigation for a >> BearShare SQLite database file. >> I didn't find any older version in the download section or anywhere else. >> >> Or does anybody have an old database tool that can read this format? The >> newer one can read version 3 only. >> >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Mit freundlichen Grüßen Marcus Haßmann -- Hassmann-Software In der Pottaschdell 31 66333 Völklingen ICQ: 192 982 963 Tel.: 06898 / 49 32 30 Mobil: 0176 / 400 53 54 5 Email: i...@hassmann-software.de Web: http://www.hassmann-software.de XING: http://www.xing.com/profile/Marcus_Hassmann ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT-ish] ResultSet size
Thank you all, i'm kinda rusty on SQL and ahd forgotten about count(*). -- () ascii ribbon campaign - against html e-mail /\ ascii-rubanda kampajno - kontraŭ html-a retpoŝto ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT-ish] ResultSet size
On Sun, 7 Jun 2009 12:47:58 -0500, P Kishor wrote: >2009/6/7 Nuno Magalhães : >> Greetings, >> >> I'm using SQLite for an academic project, through Java's JDBC >> (sqlitejdbc-0.5.4.jar). After executing a simple select, i can iterate >> the ResultSet all the way (showing to output), no problem. The >> problem, which may be silly, is that i need to get a row count so i >> can initialize a variable. >> >> I tried using rs.getFetchSize() but it returns 0. This is the only >> method i could relate to "getting number of rows" from the method >> list. >> >> I tried rs.last(); but get "SQLException: ResultSet is >> TYPE_FORWARD_ONLY" and wouldn't be able to get back anyway. >> >> I tried iterating the set and using rs.isLast() but i get >> "SQLException: function not yet implemented for SQLite". >> >> I know this si more related to JDBC than SQLite, but maybe someone can >> give me a hint? > >Either run a SELECT Count(*) prior to running your full select query, >or run the SELECT query, iterate over it counting the records, find >the total number in the set, and then iterate over it and display as >you are doing now. Either way, you would have to do a two-pass. I >would prefer the two SELECTs, once for the Count() and second time for >the query, for small results sets ... keep in mind, SELECT Count() in >SQLite is not optimized. It has recently been optimized, but only for the form: SELECT count(*) FROM without WHERE or LIMIT clauses. http://www.sqlite.org/cvstrac/chngview?cn=6316 >For big result sets, I might want to just do >one mongo select and then count the results in memory. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT-ish] ResultSet size
2009/6/7 Nuno Magalhães : > Greetings, > > I'm using SQLite for an academic project, through Java's JDBC > (sqlitejdbc-0.5.4.jar). After executing a simple select, i can iterate > the ResultSet all the way (showing to output), no problem. The > problem, which may be silly, is that i need to get a row count so i > can initialize a variable. > > I tried using rs.getFetchSize() but it returns 0. This is the only > method i could relate to "getting number of rows" from the method > list. > > I tried rs.last(); but get "SQLException: ResultSet is > TYPE_FORWARD_ONLY" and wouldn't be able to get back anyway. > > I tried iterating the set and using rs.isLast() but i get > "SQLException: function not yet implemented for SQLite". > > I know this si more related to JDBC than SQLite, but maybe someone can > give me a hint? Either run a SELECT Count(*) prior to running your full select query, or run the SELECT query, iterate over it counting the records, find the total number in the set, and then iterate over it and display as you are doing now. Either way, you would have to do a two-pass. I would prefer the two SELECTs, once for the Count() and second time for the query, for small results sets ... keep in mind, SELECT Count() in SQLite is not optimized. For big result sets, I might want to just do one mongo select and then count the results in memory. > > TIA, > Nuno Magalhães > > -- > () ascii ribbon campaign - against html e-mail > /\ ascii-rubanda kampajno - kontraŭ html-a retpoŝto > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Carbon Model http://carbonmodel.org/ Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/ Science Commons Fellow, Geospatial Data http://sciencecommons.org Nelson Institute, UW-Madison http://www.nelson.wisc.edu/ --- collaborate, communicate, compete === Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT-ish] ResultSet size
Nuno Magalhaes wrote: > I'm using SQLite for an academic project, through Java's JDBC > (sqlitejdbc-0.5.4.jar). After executing a simple select, i can iterate > the ResultSet all the way (showing to output), no problem. The > problem, which may be silly, is that i need to get a row count so i > can initialize a variable. In general, it is impossible to find out how many rows are in the resultset short of iterating over it and counting. You could run a query like "select count(*) from ...", but that just tells SQLite to do the same thing - iterate over all rows it would have produced, and count them. Thus it takes approximately the same time as the original query, but you get less information back. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [OT-ish] ResultSet size
Greetings, I'm using SQLite for an academic project, through Java's JDBC (sqlitejdbc-0.5.4.jar). After executing a simple select, i can iterate the ResultSet all the way (showing to output), no problem. The problem, which may be silly, is that i need to get a row count so i can initialize a variable. I tried using rs.getFetchSize() but it returns 0. This is the only method i could relate to "getting number of rows" from the method list. I tried rs.last(); but get "SQLException: ResultSet is TYPE_FORWARD_ONLY" and wouldn't be able to get back anyway. I tried iterating the set and using rs.isLast() but i get "SQLException: function not yet implemented for SQLite". I know this si more related to JDBC than SQLite, but maybe someone can give me a hint? TIA, Nuno Magalhães -- () ascii ribbon campaign - against html e-mail /\ ascii-rubanda kampajno - kontraŭ html-a retpoŝto ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "not an error" error inserting data trough a view on fts3 table
Jay A. Kreibich wrote: > > you can create a trigger that fires on an attempt to > DELETE, INSERT, or UPDATE a view and do what you need > in the body of the trigger. > > Jay, this is exactly what the OP did. He has two tables and a view, and inserts into the two tables from within an "instead of" trigger on the view. I suspect the error is due to the fact that one of the tables is an FTS3 virtual table rather than a real table. I haven't used the FTS3 module much so I can't offer any further insight, but I see nothing wrong with the code as posted. Perhaps the OP should file a bug report using this example. If there is a bug it will probably be fixed in short order. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Version 2.1
Hi there! An older version of SQLiteBorwser should do the trick - you can get it from the SourceForge project page at http://sourceforge.net/projects/sqlitebrowser/. The direct download link for a suitable precompiled version would be - for Linux: http://sourceforge.net/project/downloading.php?group_id=87946&filename=sqlitebrowser-1.1-i386.tar.gz - for Win32: http://sourceforge.net/project/downloading.php?group_id=87946&filename=sqlitebrowser-1.1-win.zip - for OS X: http://sourceforge.net/project/downloading.php?group_id=87946&filename=sqlitebrowser-1.1-osx.dmg with the source code at http://sourceforge.net/project/downloading.php?group_id=87946&filename=sqlite-1.1-src.tar.gz If that version doesn't do the trick, you can try other versions of the same project from http://sourceforge.net/project/showfiles.php?group_id=87946&package_id=91778 Hope this helps. Mihai Limbasan On 06/06/2009 04:55 PM, Marcus Hassmann / Hassmann-Software wrote: > Hello@ all! > > I need an old version of SQLite to do a forensic investigation for a > BearShare SQLite database file. > I didn't find any older version in the download section or anywhere else. > > Or does anybody have an old database tool that can read this format? The > newer one can read version 3 only. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Version 2.1
Hello@ all! I need an old version of SQLite to do a forensic investigation for a BearShare SQLite database file. I didn't find any older version in the download section or anywhere else. Or does anybody have an old database tool that can read this format? The newer one can read version 3 only. -- With kindly regards, Marcus Haßmann -- Hassmann-Software In der Pottaschdell 31 66333 Völklingen ICQ: 192 982 963 Tel.: 06898 / 49 32 30 Mobil: 0176 / 400 53 54 5 Email: i...@hassmann-software.de Web: http://www.hassmann-software.de XING: http://www.xing.com/profile/Marcus_Hassmann ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
