[sqlite] Find non-numeric character in text field
I would like to do a where on a text field and check if the values have non-numeric characters, which is in this case is anything other than 1,2,3,4,5,6,7,8,9,0 or a space character. Is this possible without using a UDF or a very long OR construction? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need Help SQL
> I'm using Olaf Schmidt's VB SQLite binder. That does use parameterized statements. Look at the methods and properties of the cCommand object in the object browser. Also look at the demo code that comes with dhRichClient3. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford Sent: 12 October 2009 20:16 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Need Help SQL #>Doesn't your VB SQLite binding support parameterized statements? If so, #>I'd suggest you dump it and find a better one. #> #>Igor Tandetnik I'm not up on the terminology. I'm using Olaf Schmidt's VB SQLite binder. He'd probably be better to answer this question than I. :-) Rick ___ 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 profiler
OK, thanks. In that case I do that in my application code. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Christian Schwarz Sent: 23 September 2009 14:33 To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite profiler > Maybe, what is it? http://en.wikipedia.org/wiki/Profiling_(computer_programming) Cheers, Christian ___ 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 profiler
Maybe, what is it? RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mcnamaragio Sent: 23 September 2009 14:16 To: sqlite-users@sqlite.org Subject: [sqlite] Sqlite profiler Hello, Would anyone be interested in sqlite profiler? If yes what features would you expect from it? Thank you. -- View this message in context: http://www.nabble.com/Sqlite-profiler-tp25531129p25531129.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] Any way to speed up this SQL?
Thanks, that works and is a lot faster. I got this down to under 0.5 sec now. I took '¬' for the upper limit character and that should always be fine. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: 12 September 2009 14:56 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Any way to speed up this SQL? RB Smissaert wrote: > Have 2 tables with both one text field called term and need to run a > SQL like this, to count the records in table1 where the start of term > in table1 equals a term in table2: > > select > count(a.rowid) > from table1 a inner join table2 b on > (lower(b.term) = lower(substr(a.term,1,length(b.term Try writing the condition as a.term collate nocase between b.term and b.term || 'Z' Replace 'Z' with a character that compares above any characters that may appear in your strings. If in doubt, try using CAST(X'EFBFBF' as text) (this is U+ represented in UTF-8, and should compare greater than anything valid). Make sure there is an index with collate nocase on a.term (an index on b.term won't be helpful). Igor Tandetnik ___ 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] Any way to speed up this SQL?
Thanks for the tip and will have a look at that. I have in the meantime made this a lot faster by making the data in both tables upper case and making the small table smaller by taking out invalid records. I can then run a simpler join with glob, although I noticed it still doesn't use the index. Sounds like your solution will be faster still. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of marbex Sent: 12 September 2009 12:25 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Any way to speed up this SQL? I had a similar issue. I wanted to find strings that started as another string in the same table and field. My solution was to create a temp table that consisted of the id, the string and the first word of the string which I then indexed. The table had 30 000 records and the total processing time went down from 15-20 minutes to 15 seconds! Applying that solution to your case, not knowing the nature of your data (I had names), I guess you can do something like this: - Get the length of the shortest string in table2.term. Lets say it's 3. - Create a tmptable of table1 Create temp tmptable1 as select term, lower(substr(term,1,3)) shortest from table1 - Create a tmptable of table2 Create temp tmptable2 as select term, lower(substr(term,1,3)) shortest from table2 - Index the shortest fields Create index idx_tmptable1_shortest on tmptable1(shortest) Create index idx_tmptable2_shortest on tmptable2(shortest) -Run this sql select count(a.rowid) from tmptable1 a inner join tmptable2 b on a.shortest=b.shortest where (lower(b.term) = lower(substr(a.term,1,length(b.term)))) RB Smissaert wrote: > > Have 2 tables with both one text field called term and need to run a SQL > like this, to count the records in table1 where the start of term in > table1 > equals a term in table2: > > select > count(a.rowid) > from table1 a inner join table2 b on > (lower(b.term) = lower(substr(a.term,1,length(b.term > > term is indexed in both tables, but not surprisingly, this query runs very > slow, taking some 40 minutes. Table1 is large, maybe some 1 million rows > and > table2 is small, maybe some 30.000 rows. All rows in table2 are unique, > but > table1 has many duplicates. > > Any suggestions to speed this up? > I could also tackle this in code rather than in SQL. > > RBS > > > > > ___ > 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/Any-way-to-speed-up-this-SQL--tp25412299p25413614.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
[sqlite] Any way to speed up this SQL?
Have 2 tables with both one text field called term and need to run a SQL like this, to count the records in table1 where the start of term in table1 equals a term in table2: select count(a.rowid) from table1 a inner join table2 b on (lower(b.term) = lower(substr(a.term,1,length(b.term term is indexed in both tables, but not surprisingly, this query runs very slow, taking some 40 minutes. Table1 is large, maybe some 1 million rows and table2 is small, maybe some 30.000 rows. All rows in table2 are unique, but table1 has many duplicates. Any suggestions to speed this up? I could also tackle this in code rather than in SQL. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does SQLite have an Instr function?
In my particular case it is simple and I have solved the problem by adding a
function via the VB wrapper.
RBS
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Darren Duncan
Sent: 11 September 2009 22:52
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Does SQLite have an Instr function?
RB Smissaert wrote:
> Does SQLite have a string function that produces the first position of a
> string within another string?
> For example select Instr('abcd', 'c') would produce 3
> Looked in the documentation and the forum, but couldn't see it.
This also isn't a simple problem since the answer would vary depending on
your
abstraction level in dealing with characters; for example, is a character a
language-dependent grapheme, a language-independent grapheme, a Unicode
codepoint, a byte, an integer, etc. If asked the length of a character
string
that has an accented letter, say, the answer would vary depending on which
of
the above abstractions they want the answer in, and for less abstracted
answers,
it is affected by what codepoints or bytes are used for the character, etc.
Similarly, asking "at what position does the substring match" is not simple.
In
practice, it is simpler to deal with strings than characters, and asking
simply
*if* a string is a substring of another, is a much simpler question, and
LIKE
does that already. -- Darren Duncan
___
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] Does SQLite have an Instr function?
OK, thanks, saves me looking further.
I can add this function via the VB wrapper (Olaf Schmidt's dhRichClient3),
but thought it might be faster if there was a pure SQLite implementation.
RBS
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: 11 September 2009 22:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Does SQLite have an Instr function?
RB Smissaert
wrote:
> Does SQLite have a string function that produces the first position
> of a string within another string?
> For example select Instr('abcd', 'c') would produce 3
Unfortunately, no. Of course, SQLite does provide a way for you to add
your own custom functions.
Igor Tandetnik
___
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
[sqlite] Does SQLite have an Instr function?
Does SQLite have a string function that produces the first position of a
string within another string?
For example select Instr('abcd', 'c') would produce 3
Looked in the documentation and the forum, but couldn't see it.
RBS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] tool to browse a sqlite database
This is now all sorted and it was indeed a simple bug in the wrapper. When parsing out the create table statement it hadn't anticipated the double quotes surrounding the tables and fields. I understand that this is in fact the standard/recommended way, although I don't do it myself and prefer: CREATE TABLE Table1([Field1] Integer etc. The wrapper has been updated and (for VB/VBA users) can be downloaded here: www.datenhaus.de/Downloads/dhRichClient3.zip www.datenhaus.de/Downloads/dhRichClient3-Demo.zip RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker Sent: 08 March 2009 14:38 To: General Discussion of SQLite Database Subject: Re: [sqlite] tool to browse a sqlite database RB Smissaert wrote: >What SQLite version produced the file World.db3? I am not 100% sure about the exact SQLite version which I used to create the original World.db3, but I am VACUUMing it regularly to bring it up to date with recent versions. So I expect it should be some version after 3.6.8. >I ask as my wrapper doesn't pick correctly the fields of a table. >This is Olaf Schmidt's VB wrapper dhRichClient with SQLite 3.6.11. I just did a PRAGMA integrity_check; on World.db3 with the SQLite3.exe v. 3.6.11 and it reports 'ok'. Looks like a wrapper problem to me. >BTW, SQLiteSpy looks a very nice GUI tool. Thanks! Ralf ___ 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] tool to browse a sqlite database
OK, thanks to clarify that. There is no problem with the table SQLiteSpy.db3, so what difference in the files Word.db3 and SQLiteSpy.db3 could possibly explain this problem? I have reported this to Olaf and I am sure he will shed light on this. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker Sent: 08 March 2009 14:38 To: General Discussion of SQLite Database Subject: Re: [sqlite] tool to browse a sqlite database RB Smissaert wrote: >What SQLite version produced the file World.db3? I am not 100% sure about the exact SQLite version which I used to create the original World.db3, but I am VACUUMing it regularly to bring it up to date with recent versions. So I expect it should be some version after 3.6.8. >I ask as my wrapper doesn't pick correctly the fields of a table. >This is Olaf Schmidt's VB wrapper dhRichClient with SQLite 3.6.11. I just did a PRAGMA integrity_check; on World.db3 with the SQLite3.exe v. 3.6.11 and it reports 'ok'. Looks like a wrapper problem to me. >BTW, SQLiteSpy looks a very nice GUI tool. Thanks! Ralf ___ 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] tool to browse a sqlite database
What SQLite version produced the file World.db3? I ask as my wrapper doesn't pick correctly the fields of a table. This is Olaf Schmidt's VB wrapper dhRichClient with SQLite 3.6.11. BTW, SQLiteSpy looks a very nice GUI tool. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker Sent: 08 March 2009 09:13 To: General Discussion of SQLite Database Subject: Re: [sqlite] tool to browse a sqlite database BareFeet wrote: >See a comparison of several GUI SQLite tools here: >http://www.tandb.com.au/sqlite/compare/?ml SQLiteSpy is missing from the list. It is available from http://www.yunqa.de SQLiteSpy is a Unicode SQLite3 database browser GUI for Win32. Features include: * Database at a Glance - The schema treeview displays all items contained in a database, including tables, columns, indexes and triggers. Press F5 to update the schema tree, double-click a table or view to display its data, use the context menu for frequently used commands. * Grid Cell Editing - Table cells are editable in the grid: Display a table via the schema treeview, select a cell and press F2 to invoke the editor. Then modify and confirm to write your changes back to the table. * Data Type Display - The native SQL data types are displayed with different background colors to help detect type errors. Type errors can cause performance degradation or wrong SELECT result sets if NULL values are confused with empty strings. * Full Unicode - SQLiteSpy fully supports SQLite's Unicode capabilities. Data display and entry is completely realized as Unicode, including SQL commands. * Multiple SQL Edits - Modern tabs are used to edit and display multiple SQL queries for easy comparison of query statements and results. SQL queries are executed by typing or loading them into the SQL edit. Then press F9 to run the query, or CTRL+F9 to run the current line or selection only. * Time Measurement - SQL execution time is automatically measured and displayed to help optimize queries. * Regular Expressions - The SQL keyword REGEXP is supported and adds the complete regular expression syntax of Perl 5.10 to SQLiteSpy. * Mathematical SQL Functions - The following mathematical SQL functions are available in addition to the SQLite default: ACOS(), ASIN(), ATAN(), ATAN(), ATAN2(), CEIL(), CEILING(), COS(), COT(), DEGREES(), EXP(), FLOOR(), LN(), LOG(), LOG(), LOG2(), LOG10(), MOD(), PI(), POW(), RADIANS(), SIGN(), SIN(), SQRT(), TAN(), TRUNCATE(). * Data Compression - The SQL functions COMPRESS() applies zlib's deflate to any text or BLOB value. The raw deflate data stream is returned. UNCOMPRESS() inflates this stream back to the original. Integers, Doubles, and Nulls are returned unchanged. * Compact Result Storage - The internal data storage mechanism uses SQLite's native data types for optimal compatibility. As a result, SQLiteSpy uses far less memory than other SQLite managers and handles large tables much more efficiently. * Built in SQLite Engine - SQLiteSpy comes as a single file executable with the SQLite database engine already build into the application. There is no need to distribute any DLLs, which makes SQLiteSpy easy to deploy with customers. * Easy Install & Uninstall - To run SQLiteSpy, just extract the SQLiteSpy.exe file to any directory and execute the file. No installation is needed - when first started, the program creates a single file SQLiteSpy.db3 (a SQLite3 database) to store options and settings. It does not write any other files or to the registry. Uninstalling is as simple as deleting two files only: The application's executable and its options database file. * Freeware - SQLiteSpy is Freeware for personal and educational use. If you are using SQLiteSpy commercially, your donation is welcome to promote the ongoing development of this software. Ralf ___ 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] Lock SQLite file by overwriting bytes and then un-lock?
Thanks; will do that. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MikeW Sent: 26 January 2009 12:02 To: sqlite-users@sqlite.org Subject: Re: [sqlite]Lock SQLite file by overwriting bytes and then un-lock? RB Smissaert writes: > > Thanks for the tip, but does that work on a Windows Mobile device? > > RBS Don't know - also look at http://www.freeotfe.org/ (Google: encrypted files windows mobile) Cheers, MikeW ___ 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] Lock SQLite file by overwriting bytes and then un-lock?
Thanks for the tip, but does that work on a Windows Mobile device? RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MikeW Sent: 26 January 2009 11:40 To: sqlite-users@sqlite.org Subject: Re: [sqlite]Lock SQLite file by overwriting bytes and then un-lock? RB Smissaert writes: > > For some reason this mail went to the junk mail folder. > That sounds good and I would be happy to buy this, but I am not sure about: > > All you need to do is replace the DLL > I have number of dll's: > For the desktop: an ActiveX dll and a plain Windows dll. The ActiveX is used > by my VBA/VB6 app and that ActiveX uses the plain Windows dll. These files > are compiled by Olaf Schmidt. > On the WM6 device: A .net dll System.Data.SQLite.DLL and a little Basic4PPC > dll, SQLDevice.dll, which I think is also a .net dll. These files are > compiled by Erel, the author of Basic4PPC. > How would SEE fit in with all this? > > RBS Since you are running Windows, I would have the app and standard SQLite DB, and all ancillary data, stored on an properly encrypted drive. e.g. http://www.truecrypt.org/ You can't be too careful with sensitive data like this. Regards, MikeW ___ 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] Lock SQLite file by overwriting bytes and then un-lock?
For some reason this mail went to the junk mail folder. That sounds good and I would be happy to buy this, but I am not sure about: > All you need to do is replace the DLL I have number of dll's: For the desktop: an ActiveX dll and a plain Windows dll. The ActiveX is used by my VBA/VB6 app and that ActiveX uses the plain Windows dll. These files are compiled by Olaf Schmidt. On the WM6 device: A .net dll System.Data.SQLite.DLL and a little Basic4PPC dll, SQLDevice.dll, which I think is also a .net dll. These files are compiled by Erel, the author of Basic4PPC. How would SEE fit in with all this? RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp Sent: 25 January 2009 19:40 To: General Discussion of SQLite Database Subject: Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock? On Jan 25, 2009, at 2:32 PM, RB Smissaert wrote: > Yes, you are right there. It won't be much good for anything else > then a > casual peek at the device. This is a clinical database, so it involves > patients, diagnoses, medications etc. The ID data and the clinical > data are > stored in different tables, but even then with a simple hex editor > it won't > be that difficult to match the 2 up. > So, I suppose the only solution is to encrypt the data. Problem is > that the > SQLite on the device can't decrypt and even if it could it would be a > different system than the one used on the desktop. The DB file is > made on > the desktop and then copied to the device and on the desktop I use > Olaf > Schmidt's VB wrapper dhRichClient and SQLite 3.6.1. The device app is > developed with Basic4PPC and that is based on .Net and uses SQLite > 3.3.12. > Not sure there is an easy solution there without affecting the speed > of the > application. http://www.hwaci.com/sw/sqlite/see.html is often used to meet HIPAA requirements. SEE supports cross-platform databases. The key can be entered using PRAGMAs so no new interfaces are needed in your wrapper. All you need to do is replace the DLL. And by purchasing an SEE license, you get the additional satisfaction of knowing that you are helping to support the ongoing development and maintenance of SQLite :-) D. Richard Hipp d...@hwaci.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] Lock SQLite file by overwriting bytes and then un-lock?
Thanks for the suggestion. The problem is that I use 2 different wrappers, one for the desktop and one for the device. Possibly, somehow it would be possible to add SSE to the desktop wrapper, but it will be very difficult to get it added to the device wrapper as I have no control over that. Will bear this option in mind though. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kees Nuyt Sent: 25 January 2009 20:08 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock? On Sun, 25 Jan 2009 18:29:28 -, "RB Smissaert" wrote in General Discussion of SQLite Database : >Would it be possible to make a SQLite file un-usable by overwriting bytes in >certain places and then (via an encrypted password) make the file usable >again by putting the right bytes back in the right places? >I use a VB wrapper that can encrypt the database, but I am using this SQLite >file on a Windows Mobile device and the SQLite wrapper used there can't do >that. >I can do this quite easy by picking some fixed bytes at the beginning of the >file, but it then is too easy to pick this up by comparing with a normal >SQLite file. So, maybe I need to overwrite sqlite_master, but will it be >possible to always find the start and end of that part of the file? >Thanks for any ideas about this. > >RBS If the security is important to you, it might be worth to invest in SEE, the proprietary, licenced, SQLite Encryption Extension: http://www.sqlite.org/support.html It makes sure only your application can access the database. -- ( Kees Nuyt ) c[_] ___ 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] Lock SQLite file by overwriting bytes and then un-lock?
Yes, you are right there. It won't be much good for anything else then a casual peek at the device. This is a clinical database, so it involves patients, diagnoses, medications etc. The ID data and the clinical data are stored in different tables, but even then with a simple hex editor it won't be that difficult to match the 2 up. So, I suppose the only solution is to encrypt the data. Problem is that the SQLite on the device can't decrypt and even if it could it would be a different system than the one used on the desktop. The DB file is made on the desktop and then copied to the device and on the desktop I use Olaf Schmidt's VB wrapper dhRichClient and SQLite 3.6.1. The device app is developed with Basic4PPC and that is based on .Net and uses SQLite 3.3.12. Not sure there is an easy solution there without affecting the speed of the application. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent: 25 January 2009 18:48 To: General Discussion of SQLite Database Subject: Re: [sqlite] Lock SQLite file by overwriting bytes and then un-lock? On Sun, Jan 25, 2009 at 06:29:28PM -, RB Smissaert scratched on the wall: > Would it be possible to make a SQLite file un-usable by overwriting bytes in > certain places and then (via an encrypted password) make the file usable > again by putting the right bytes back in the right places? > I use a VB wrapper that can encrypt the database, but I am using this SQLite > file on a Windows Mobile device and the SQLite wrapper used there can't do > that. > I can do this quite easy by picking some fixed bytes at the beginning of the > file, but it then is too easy to pick this up by comparing with a normal > SQLite file. So, maybe I need to overwrite sqlite_master, but will it be > possible to always find the start and end of that part of the file? > Thanks for any ideas about this. To what end? What are you trying to protect against? If the database has sensitive information, especially strings, much of it can be recovered by simply dumping the database file with a hex-editor. Simply making the file unreadable by the SQLite library provides only the bare minimum of protection. If all you want to do is prevent the casual user from browsing the file, then I suppose this will work. Although your indication of scrambling the first few bytes as being "too easy" to fix seems to indicate you're after something stronger. If you really need to protect the data, you need to protect the data itself, not the access mechanism. You either need to re-write the VFS to encrypt whole database pages, or you need to encrypt the data itself before it is stored into the database. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ 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
[sqlite] Lock SQLite file by overwriting bytes and then un-lock?
Would it be possible to make a SQLite file un-usable by overwriting bytes in certain places and then (via an encrypted password) make the file usable again by putting the right bytes back in the right places? I use a VB wrapper that can encrypt the database, but I am using this SQLite file on a Windows Mobile device and the SQLite wrapper used there can't do that. I can do this quite easy by picking some fixed bytes at the beginning of the file, but it then is too easy to pick this up by comparing with a normal SQLite file. So, maybe I need to overwrite sqlite_master, but will it be possible to always find the start and end of that part of the file? Thanks for any ideas about this. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why the difference in these 2 SQLite files?
Some progress in this. Doing a trivial file write (set Read-Only to True and then back to False) gives me the same speed benefit. So at least this takes SQLite out of the equation in solving this problem. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 22:15 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Why the difference in these 2 SQLite files? OK, any DB write to this file on the desktop (not the mobile device) will make the query go fast. A completely trivial update will do it, so it looks somehow the db write on the desktop does makes it go fast. I can't check now if the same applies when doing this db write on the first PC and hopefully it does. Maybe I need some app to compare the 2 db files to see what is going on here. Baffling me. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:54 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Why the difference in these 2 SQLite files? Have now also looked at the full EXPLAIN and although understand that it looks indentical to me for both databases: Slow - addropcode p1 p2 p3 0 Goto0 34 1 MemLoad 0 0 2 If 0 31 3 MemInt 1 0 4 MemInt 0 1 5 Integer 1 0 6 MustBeInt 0 0 7 MemStore2 0 8 IfMemZero 2 31 9 IfMemPos2 13 10 Pop 1 0 11 MemInt -1 3 12 Goto0 14 13 MemStore3 1 14 Integer 0 0 15 OpenRead1 15123 keyinfo(1,BINARY) 16 SetNumColumns 1 2 17 Integer 823 0 18 IsNull -1 30 19 MakeRecord 1 0 d 20 MemStore4 0 21 MoveGe 1 30 22 MemLoad 4 0 23 IdxGE 1 30 + 24 Column 1 0 25 MemInt 1 1 26 Pop 1 0 27 MemIncr -1 2 28 IfMemZero 2 30 29 Next1 22 30 Close 1 0 31 MemLoad 1 0 32 Callback1 0 33 Halt0 0 34 Transaction 0 0 35 VerifyCookie0 397 36 Goto0 1 37 Noop0 0 Fast - addropcode p1 p2 0 Goto0 34 1 MemLoad 0 0 2 If 0 31 3 MemInt 1 0 4 MemInt 0 1 5 Integer 1 0 6 MustBeInt 0 0 7 MemStore2 0 8 IfMemZero 2 31 9 IfMemPos2 13 10 Pop 1 0 11 MemInt -1 3 12 Goto0 14 13 MemStore3 1 14 Integer 0 0 15 OpenRead1 15123 16 SetNumColumns 1 2 17 Integer 831 0 18 IsNull -1 30 19 MakeRecord 1 0 20 MemStore4 0 21 MoveGe 1 30 22 MemLoad 4 0 23 IdxGE 1 30 24 Column 1 0 25 MemInt 1 1 26 Pop 1 0 27 MemIncr -1 2 28 IfMemZero 2 30 29 Next1 22 30 Close 1 0 31 MemLoad 1 0 32 Callback1 0 33 Halt0 0 34 Transaction 0 0 35 VerifyCookie0 400 36 Goto0 1 37 Noop0 0 So, what possibly could explain this? There can be no cache effect as I can reverse it from fast to slow and vice versa by copying one or the other database. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:00 To: sqlite-users@sqlite.org Subject: [sqlite] Why the difference in these 2 SQLite files? Have a large (about half a Gb) SQLite db file, made with version 3.6.1. I make this file on one PC (Windows XP) put the file on a USB stick, take it home, copy it to the home PC (Win XP) local drive and then from there copy the file to a Windows mobile (WM6) device, a Samsung Omnia. On that device I access the db file with an application written with Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC is 3.3.12. Now the thing I don't understand. I run the following query (on the mobile device): SELECT EXISTS( SELECT PATIENT_ID FROM TABLE1 WHERE
Re: [sqlite] newbie question regarding my sqlite code
Try this: select avg(age) from acoda union all select avg(durata) from main union all select sum(età) from dipendenti RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of silvio grosso Sent: 10 January 2009 10:41 To: sqlite-users@sqlite.org Subject: [sqlite] newbie question regarding my sqlite code Hello, I am a new sqilte user and I am learning sqlite code in my spare time.. I have always used sql code with Microsoft Access and Base (openoffice). Therefore, sorry to ask a question very simple . When I write the very simple code: select sum(età) from dipendenti everything works fine and the result is 100. When I try a bit longer query the result for the sum regarding the column età from the table dipendenti changes and it is wrong. That is 25200? (instead of the right value 100!). The query is: select avg(age), avg(durata), sum(età) from acoda, main, dipendenti In the above query the avg results for the column age (table acoda) and the column durata (table main) are right. The only value wrong is the third, that it, sum (for the table dipendenti, column age, 25200 instead of the right value 100). The column age in the table dipendenti is not present in the other two tables (acoda, main). What's wrong with the second query? Thanks in advance. Best regards ___ 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] Why the difference in these 2 SQLite files?
OK, any DB write to this file on the desktop (not the mobile device) will make the query go fast. A completely trivial update will do it, so it looks somehow the db write on the desktop does makes it go fast. I can't check now if the same applies when doing this db write on the first PC and hopefully it does. Maybe I need some app to compare the 2 db files to see what is going on here. Baffling me. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:54 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Why the difference in these 2 SQLite files? Have now also looked at the full EXPLAIN and although understand that it looks indentical to me for both databases: Slow - addropcode p1 p2 p3 0 Goto0 34 1 MemLoad 0 0 2 If 0 31 3 MemInt 1 0 4 MemInt 0 1 5 Integer 1 0 6 MustBeInt 0 0 7 MemStore2 0 8 IfMemZero 2 31 9 IfMemPos2 13 10 Pop 1 0 11 MemInt -1 3 12 Goto0 14 13 MemStore3 1 14 Integer 0 0 15 OpenRead1 15123 keyinfo(1,BINARY) 16 SetNumColumns 1 2 17 Integer 823 0 18 IsNull -1 30 19 MakeRecord 1 0 d 20 MemStore4 0 21 MoveGe 1 30 22 MemLoad 4 0 23 IdxGE 1 30 + 24 Column 1 0 25 MemInt 1 1 26 Pop 1 0 27 MemIncr -1 2 28 IfMemZero 2 30 29 Next1 22 30 Close 1 0 31 MemLoad 1 0 32 Callback1 0 33 Halt0 0 34 Transaction 0 0 35 VerifyCookie0 397 36 Goto0 1 37 Noop0 0 Fast - addropcode p1 p2 0 Goto0 34 1 MemLoad 0 0 2 If 0 31 3 MemInt 1 0 4 MemInt 0 1 5 Integer 1 0 6 MustBeInt 0 0 7 MemStore2 0 8 IfMemZero 2 31 9 IfMemPos2 13 10 Pop 1 0 11 MemInt -1 3 12 Goto0 14 13 MemStore3 1 14 Integer 0 0 15 OpenRead1 15123 16 SetNumColumns 1 2 17 Integer 831 0 18 IsNull -1 30 19 MakeRecord 1 0 20 MemStore4 0 21 MoveGe 1 30 22 MemLoad 4 0 23 IdxGE 1 30 24 Column 1 0 25 MemInt 1 1 26 Pop 1 0 27 MemIncr -1 2 28 IfMemZero 2 30 29 Next1 22 30 Close 1 0 31 MemLoad 1 0 32 Callback1 0 33 Halt0 0 34 Transaction 0 0 35 VerifyCookie0 400 36 Goto0 1 37 Noop0 0 So, what possibly could explain this? There can be no cache effect as I can reverse it from fast to slow and vice versa by copying one or the other database. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:00 To: sqlite-users@sqlite.org Subject: [sqlite] Why the difference in these 2 SQLite files? Have a large (about half a Gb) SQLite db file, made with version 3.6.1. I make this file on one PC (Windows XP) put the file on a USB stick, take it home, copy it to the home PC (Win XP) local drive and then from there copy the file to a Windows mobile (WM6) device, a Samsung Omnia. On that device I access the db file with an application written with Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC is 3.3.12. Now the thing I don't understand. I run the following query (on the mobile device): SELECT EXISTS( SELECT PATIENT_ID FROM TABLE1 WHERE PATIENT_ID = 123) There is an index on field PATIENT_ID. Takes query takes quite long, say about 1 second. Now I do the following: On the home PC I access the SQLite db file, again with 3.6.1 and I drop a table in that database. This table is only small and completely irrelevant In the above query. After doing that I copy that db file to the mobile device, so overwriting the old file. Now when I run that same query it is a multitude faster, say maybe 20 milli-seconds. I can achieve the
Re: [sqlite] Why the difference in these 2 SQLite files?
Have now also looked at the full EXPLAIN and although understand that it looks indentical to me for both databases: Slow - addropcode p1 p2 p3 0 Goto0 34 1 MemLoad 0 0 2 If 0 31 3 MemInt 1 0 4 MemInt 0 1 5 Integer 1 0 6 MustBeInt 0 0 7 MemStore2 0 8 IfMemZero 2 31 9 IfMemPos2 13 10 Pop 1 0 11 MemInt -1 3 12 Goto0 14 13 MemStore3 1 14 Integer 0 0 15 OpenRead1 15123 keyinfo(1,BINARY) 16 SetNumColumns 1 2 17 Integer 823 0 18 IsNull -1 30 19 MakeRecord 1 0 d 20 MemStore4 0 21 MoveGe 1 30 22 MemLoad 4 0 23 IdxGE 1 30 + 24 Column 1 0 25 MemInt 1 1 26 Pop 1 0 27 MemIncr -1 2 28 IfMemZero 2 30 29 Next1 22 30 Close 1 0 31 MemLoad 1 0 32 Callback1 0 33 Halt0 0 34 Transaction 0 0 35 VerifyCookie0 397 36 Goto0 1 37 Noop0 0 Fast - addropcode p1 p2 0 Goto0 34 1 MemLoad 0 0 2 If 0 31 3 MemInt 1 0 4 MemInt 0 1 5 Integer 1 0 6 MustBeInt 0 0 7 MemStore2 0 8 IfMemZero 2 31 9 IfMemPos2 13 10 Pop 1 0 11 MemInt -1 3 12 Goto0 14 13 MemStore3 1 14 Integer 0 0 15 OpenRead1 15123 16 SetNumColumns 1 2 17 Integer 831 0 18 IsNull -1 30 19 MakeRecord 1 0 20 MemStore4 0 21 MoveGe 1 30 22 MemLoad 4 0 23 IdxGE 1 30 24 Column 1 0 25 MemInt 1 1 26 Pop 1 0 27 MemIncr -1 2 28 IfMemZero 2 30 29 Next1 22 30 Close 1 0 31 MemLoad 1 0 32 Callback1 0 33 Halt0 0 34 Transaction 0 0 35 VerifyCookie0 400 36 Goto0 1 37 Noop0 0 So, what possibly could explain this? There can be no cache effect as I can reverse it from fast to slow and vice versa by copying one or the other database. RBS -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of RB Smissaert Sent: 09 January 2009 20:00 To: sqlite-users@sqlite.org Subject: [sqlite] Why the difference in these 2 SQLite files? Have a large (about half a Gb) SQLite db file, made with version 3.6.1. I make this file on one PC (Windows XP) put the file on a USB stick, take it home, copy it to the home PC (Win XP) local drive and then from there copy the file to a Windows mobile (WM6) device, a Samsung Omnia. On that device I access the db file with an application written with Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC is 3.3.12. Now the thing I don't understand. I run the following query (on the mobile device): SELECT EXISTS( SELECT PATIENT_ID FROM TABLE1 WHERE PATIENT_ID = 123) There is an index on field PATIENT_ID. Takes query takes quite long, say about 1 second. Now I do the following: On the home PC I access the SQLite db file, again with 3.6.1 and I drop a table in that database. This table is only small and completely irrelevant In the above query. After doing that I copy that db file to the mobile device, so overwriting the old file. Now when I run that same query it is a multitude faster, say maybe 20 milli-seconds. I can achieve the same by dropping an index, again in a small table, completely unrelated to the above query and I see the same speed gain. I have compared the 2 different database files, so stats, all the different pragma's etc. and I can't see the difference. I have also compared the SQLite query plans and they are the same (using the index) for both files. So what possibly could explain the difference in speed? I know it is a bit of a long-winded question, but maybe somebody has some idea what is going on here and if so, very grateful for that as I can't see it. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sql
[sqlite] Why the difference in these 2 SQLite files?
Have a large (about half a Gb) SQLite db file, made with version 3.6.1. I make this file on one PC (Windows XP) put the file on a USB stick, take it home, copy it to the home PC (Win XP) local drive and then from there copy the file to a Windows mobile (WM6) device, a Samsung Omnia. On that device I access the db file with an application written with Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC is 3.3.12. Now the thing I don't understand. I run the following query (on the mobile device): SELECT EXISTS( SELECT PATIENT_ID FROM TABLE1 WHERE PATIENT_ID = 123) There is an index on field PATIENT_ID. Takes query takes quite long, say about 1 second. Now I do the following: On the home PC I access the SQLite db file, again with 3.6.1 and I drop a table in that database. This table is only small and completely irrelevant In the above query. After doing that I copy that db file to the mobile device, so overwriting the old file. Now when I run that same query it is a multitude faster, say maybe 20 milli-seconds. I can achieve the same by dropping an index, again in a small table, completely unrelated to the above query and I see the same speed gain. I have compared the 2 different database files, so stats, all the different pragma's etc. and I can't see the difference. I have also compared the SQLite query plans and they are the same (using the index) for both files. So what possibly could explain the difference in speed? I know it is a bit of a long-winded question, but maybe somebody has some idea what is going on here and if so, very grateful for that as I can't see it. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete to leave x rows per group
Thanks, nice and simple. I had come up with something that works as well, but probably more complex than needed. This is with my actual data: delete from sqlite_tablePa where rowid not in (select rowid from sqlite_tablePa where (select count(*) from sqlite_tablePa as s where s.patient_id = sqlite_tablePa.patient_id and s.rowid < sqlite_tablePa.rowid) < 3) I think I will stick with your solution as it is simpler and most likely faster as well. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: 03 July 2008 22:54 To: sqlite-users@sqlite.org Subject: Re: [sqlite] delete to leave x rows per group RB Smissaert <[EMAIL PROTECTED]> wrote: > Can this be done in SQLite SQL? > > ID Value > --- > 1 A > 1 B > 1 C > 1 D > 1 E > 2 A > 2 B > 2 C > 2 D > 2 E > 2 F > > Delete rows to leave x rows per ID, say 3 rows, so we get: delete from tableName where rowid not in ( select rowid from tableName t2 where t2.ID = tableName.ID order by Value desc limit 3 ); Igor Tandetnik ___ 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
[sqlite] delete to leave x rows per group
Can this be done in SQLite SQL? ID Value --- 1 A 1 B 1 C 1 D 1 E 2 A 2 B 2 C 2 D 2 E 2 F Delete rows to leave x rows per ID, say 3 rows, so we get: ID Value --- 1 C 1 D 1 E 2 D 2 E 2 F If there were less than 3 rows for a group then that group should be ignored, so now rows should be deleted from that group. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA journal_mode = OFF slower?
Done some testing with 3.5.9 with PRAGMA journal_mode = OFF and it seems that strangely it makes DB writing queries slower. I use SQLite from VB/VBA with the wrapper from Olaf Schmidt. Should it not be that PRAGMA journal_mode = OFF should make inserts, create index etc. faster rather than slower? Has anybody else seen the same? This is on Windows XP. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sorting records in random order
I compared the speeds and found them to be the same. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: 07 May 2008 22:25 To: General Discussion of SQLite Database Subject: Re: [sqlite] sorting records in random order Samuel Neff wrote: > This query runs slow: > > SELECT id FROM data ORDER BY random(); > > but this equivalent query runs very fast: > > SELECT id FROM (SELECT id, random() r FROM data) ORDER BY r; > I couldn't see how these would be different so I fired up the explain command. As I expected, these two produce identical code (except for the integer id assigned to the ephemeral table used for the sort). I don't think here will be any difference in speed between these two statements. SQLite version 3.5.7 Enter ".help" for instructions sqlite> create table t (id integer primary key, a text); sqlite> .explain sqlite> explain select id from t order by random(); addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 explain select id from t order by random( ); 00 1 OpenEphemeral 1 3 0 keyinfo(1,BINARY) 00 2 Goto 0 26000 3 OpenRead 0 2 000 4 SetNumColumns 0 0 000 5 Rewind 0 14000 6 Rowid 0 1 000 7 MakeRecord 1 1 200 8 Function 0 0 3 random(-1) 00 9 Sequence 1 4 000 10Move 2 5 000 11MakeRecord 3 3 600 12IdxInsert 1 6 000 13Next 0 6 000 14Close 0 0 000 15OpenPseudo 2 0 000 16SetNumColumns 2 1 000 17Sort 1 24000 18Column 1 2 200 19Integer1 6 000 20Insert 2 2 600 21Column 2 0 100 22ResultRow 1 1 000 23Next 1 18000 24Close 2 0 000 25Halt 0 0 000 26Transaction0 0 000 27VerifyCookie 0 1 000 28TableLock 0 2 0 t 00 29Goto 0 3 000 sqlite> explain select id from (select id, random() r from t) order by r; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 explain select id from (select id, random () r from t) order by r; 00 1 OpenEphemeral 2 3 0 keyinfo(1,BINARY) 00 2 Goto 0 26000 3 OpenRead 1 2 000 4 SetNumColumns 1 0 000 5 Rewind 1 14000 6 Rowid 1 1 000 7 MakeRecord 1 1 200 8 Function 0 0 3 random(-1) 00 9 Sequence 2 4 000 10Move 2 5 000 11MakeRecord 3 3 600 12IdxInsert 2 6 000 13Next 1 6 000 14Close 1 0 000 15OpenPseudo 3 0 000 16SetNumColumns 3 1 000 17Sort 2 24000 18Column 2 2 200 19Integer1 6 000 20Insert 3 2 600 21Column 3 0 100 22ResultRow 1 1 000 23Next 2 18000 24Close 3 0 000 25Halt 0 0 000 26Transaction0 0 000 27VerifyCookie 0 1 000 28TableLock 0 2 0 t 00 29Goto 0 3 000 sqlite> Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-use
Re: [sqlite] temp tables and PRAGMA temp_store
Hi Olaf, Aaah, that is a funny one! Will test today then with PRAGMA temp_store = FILE and see if that makes it slower! Bart -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Olaf Schmidt Sent: 30 April 2008 04:24 To: sqlite-users@sqlite.org Subject: Re: [sqlite] temp tables and PRAGMA temp_store RB Smissaert <[EMAIL PROTECTED]> writes: > > Using the latest SQLite and trying to speed up the making of some > intermediate tables. I thought I could do that by doing > PRAGMA temp_store = MEMORY and CREATE TEMP TABLE etc. > I do run the PRAGMA directly after establishing the SQLite connection. > So far I haven't seen any speed increase yet and I am wondering if maybe > I am doing something wrong here. > Does this PRAGMA only work on a newly created db file? Have tried that, but > again no difference. > Would you expect a speed increase from doing the above? > I am running this from VB with the wrapper from Olaf Schmidt. Hi Bart, the reason why you don't see any changes is, that this is already the default in my version of the SQLite-engine compile (sqlite35_engine.dll). ;-) Olaf ___ 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
[sqlite] temp tables and PRAGMA temp_store
Using the latest SQLite and trying to speed up the making of some intermediate tables. I thought I could do that by doing PRAGMA temp_store = MEMORY and CREATE TEMP TABLE etc. I do run the PRAGMA directly after establishing the SQLite connection. So far I haven't seen any speed increase yet and I am wondering if maybe I am doing something wrong here. Does this PRAGMA only work on a newly created db file? Have tried that, but again no difference. Would you expect a speed increase from doing the above? I am running this from VB with the wrapper from Olaf Schmidt. Thanks for any advice. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implicit INDEX?
Have tested this now on a table of some 30 rows (no indexes at all) and with 100 rows to find in the middle of the table, sorted asc on time-stamp field. It gave me a speed increase of about 25%. If I looked for rows at the beginning of the table the speed increase was more, some 50% faster. If I looked for rows at the end (highest timestamp) then the simple select was quite a lot faster than the one with limit etc. Maybe not the kind of increase you were interested in, but still something and with no extra overhead at all, just a different SQL. Probably only worth it if looking for early times. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 14 April 2008 19:11 To: General Discussion of SQLite Database Subject: Re: [sqlite] Implicit INDEX? How about this: select * from table1 where rowid >= (select rowid from table1 where time_stamp = xxx limit 1) and rowid < (select rowid from table1 where time_stamp > xxx limit 1) RBS > Donald, > >> To test this, I think you'd want to create a select for some of the most >> recent data (i.e. data at the tail of the database), perhaps after >> clearing cache. I suspect this will take the full table scan time to >> return any values. > > I'd actually just thought of that. All my test SELECTs happened > to be using data close to the "front" of the file. As suspected, if I try > it on "later" data, I have to wait for the table scan, then I get the > data. I'd be erroneously assuming that SQLite was somehow searching for > the first value quickly, then scanning the table. > >> Two thoughts: >> >> 1). Easy. >> How bad is the extra 0.8 GByte cost of the index? At today's prices, >> it's only about 20 cents on ordinary 5" drives. (but maybe you're >> programming a cellphone) > > No, it's nothing terribly constrained. I'm just trying to > understand the mechanisms and do what I can to keep the size down where > possible. I was somewhat surprised to find that adding an index on a > single INTEGER column nearly doubled the size of the database and wanted > to figure out if there was a way around it, given that the column will > always be sorted. (And given my, perhaps erroneous understanding that > creating an INDEX just makes sure that the column stays sorted so SQLite > can search through it more intelligently) > >> 2). Fancy. >> You could create your own sparse index table mapping a ROWID to say, >> every thousandth timestamp. Then you could create upper and lower ROWID >> bounds on any query based on timestamps. Maybe you import the records >> in batches already and can create the new table at the same time. > > This is closer to what I'm probably going to do. The data gets > pulled in every 5 minutes, but between runs, very little actually changes. > So the idea is to store only the changes along with a full dump say once > or twice every day. Then I can just query the values from time> to and compute the state of everything from > that data. > > Thanks, > > Chris > ___ > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any way to disable journaling & rollback?
DRH, I would be seriously interested in a PRAGMA to disable/avoid a journal file as in my application I don't need it at all and it only slows down my DB writes. Would it be possible to add this? If so, thanks in advance. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald Sent: 11 April 2008 14:06 To: General Discussion of SQLite Database Subject: Re: [sqlite] Any way to disable journaling & rollback? Regarding: " removing the call of FlushFileBuffers for each transaction made my application run 20 times faster." Since you don't need the integrity protection that transactions afford, would you not get the same performance gain using the standard source and setting SYNCHRONOUS to zero? This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ 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] PHP Code That Can Store and Retrieve Images
Kees, Thanks for the interest in this and replied off-list. Bart -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt Sent: 12 March 2008 22:06 To: General Discussion of SQLite Database Subject: Re: [sqlite] PHP Code That Can Store and Retrieve Images On Wed, 12 Mar 2008 18:44:36 -, Bart wrote: >Kees, > >Would you be interested to do a project for me for a fee? [..] >Regards, Bart Smissaert Answered in private mail. -- ( Kees Nuyt ) c[_] ___ 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] PHP Code That Can Store and Retrieve Images
Kees, Would you be interested to do a project for me for a fee? I need to upload/download data to/from a hosted SQLite 3 DB. This has to be done from VBA or from a VB6 AX dll. I have posted this to RAC, but there seems little interest/progress. If interested then could you contact me off-list? I could correspond in Dutch. Regards, Bart Smissaert -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt Sent: 12 March 2008 18:39 To: General Discussion of SQLite Database Subject: Re: [sqlite] PHP Code That Can Store and Retrieve Images On Wed, 12 Mar 2008 09:10:44 -0400, you wrote: >Here is a link to the PHP code to generate the base64 string and to convert >the string back to an image. > >http://fundisom.com/phparadise/php/image_handling/base64_image_encode > In PHP it might be better to serialize(). That works for every value type. Just my 0.02 -- ( Kees Nuyt ) c[_] ___ 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] PHP Code That Can Store and Retrieve Images
Funny you ask that as just 2 days ago I posted a little project on RAC to do exactly this. In my case it has to be called from VBA or VB. Unfortunately and surprisingly no takers yet. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Robert L Cochran Sent: 11 March 2008 21:15 To: General Discussion of SQLite Database Subject: [sqlite] PHP Code That Can Store and Retrieve Images Is there open source PHP code (PHP 5.x compatible) that can store and retrieve images from an SQLite 3.5.6 database? For SQLite version 3.5.x, I need to use the PHP PDO functions if I am using PHP 5.2.5, right? I want to show a group of people about 45 photos which I would like to store on an SQLite database and then retrieve. Thanks Bob Cochran ___ 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] when to analyze?
Thanks for that explanation. In my app tables are dropped and created and inserted with data continuously, so the recommended use won't quite apply, but I understand better now. I noticed sqlite_stat1 doesn't get updated if a table is dropped or renamed with ALTER TABLE, so I take it I will have to take care of this myself, particularly with a table rename. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 06 February 2008 20:27 To: General Discussion of SQLite Database Subject: Re: [sqlite] when to analyze? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Suppose we have a table with some 10 million rows and this table was > analysed, so sqlite_stat1 has the stats of this table then is it worth it to > analyze again after adding say 1000 more rows? The indexing is still the > same, so no indexes are dropped or created. Also the data of the added rows > won't be dramatically different from the existing rows. > My guess it is not worth the time it will take and I could fine out by > experimenting, but maybe somebody has some thoughts about this and could > tell me. > Briefly: Your guess is correct For additional background into why ANALYZE exists and what it accomplishes for you, please see http://www.sqlite.org/cvstrac/wiki?p=QueryPlans SQLite rarely needs to do ANALYZE at all. You can usually get it to pick efficient query plans without having to ANALYZE. The recommended use of ANALYZE, if you use it at all, is to run it once during development on a dataset that is characteristic of the kinds of data your application will store. Retain the results of this ANALYZE as they are found in the sqlite_stat1 table. Then, when you deploy your application and create a new database, run ANALYZE once as soon as the schema is loaded but before any data is added. Such an ANALYZE will take almost no time because your database is empty. Then delete all of the information from the newly created sqlite_stat1 table and replace it with the data you saved from the ANALYZE you ran during development on your sample dataset. The result of this will be that SQLite will plan queries with an eye toward optimizing databases that are typical for your application. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ 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
[sqlite] when to analyze?
Suppose we have a table with some 10 million rows and this table was analysed, so sqlite_stat1 has the stats of this table then is it worth it to analyze again after adding say 1000 more rows? The indexing is still the same, so no indexes are dropped or created. Also the data of the added rows won't be dramatically different from the existing rows. My guess it is not worth the time it will take and I could fine out by experimenting, but maybe somebody has some thoughts about this and could tell me. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Duplicates in sqlite_stat1
Noticed that sqlite_stat1 can have duplicates on tbl, idx: tbl idx stat --- table1 idx190 2 1 table1 idx290 2 table1 idx12577 2 1 table1 idx22577 2 Is there any harm in this, so would SQLite know that it has to look at the last added stats? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] strange problem with DELETE
SQLite 3.5.4, Win XP, VBA with the wrapper dhRichClient
Running a query like this:
delete from sqlite_stat1
where
not tbl in
('table1', 'table2', 'table3')
The strange thing is that rows are deleted where tbl is one of the listed
tables.
Have tried all kind of alterations, such as making it case-insensitive,
leaving off single quotes or doing instead double-quotes, but I always get
deletes that should not happen. Must be overlooking something simple here,
but can't see it and thanks for any advice.
RBS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RE: [sqlite] Re: how to do this case when?
There isn't much in it, but it looks the one with IFNULL is the fastest. Will stick to that one. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 21:17 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Re: how to do this case when? Thanks; I came up with number 3, but I like your number 1. Any idea what could be the fastest or will it all be the same? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 20:59 To: SQLite Subject: [sqlite] Re: how to do this case when? RB Smissaert <[EMAIL PROTECTED]> wrote: > How do I alter this SQL, so that the original field remains the same > when > there is no match? A case when else end should do it, but I can't get > it > right. > > UPDATE Table1 SET Field1 = > (SELECT Field2 FROM Table2 > WHERE Table1.Field1 = Table2.Field1) UPDATE Table1 SET Field1 = IFNULL( (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1), Field1); -- or UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1 union all select Table1.Field1; ); -- or UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) where exists (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: how to do this case when?
Thanks; I came up with number 3, but I like your number 1. Any idea what could be the fastest or will it all be the same? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 20:59 To: SQLite Subject: [sqlite] Re: how to do this case when? RB Smissaert <[EMAIL PROTECTED]> wrote: > How do I alter this SQL, so that the original field remains the same > when > there is no match? A case when else end should do it, but I can't get > it > right. > > UPDATE Table1 SET Field1 = > (SELECT Field2 FROM Table2 > WHERE Table1.Field1 = Table2.Field1) UPDATE Table1 SET Field1 = IFNULL( (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1), Field1); -- or UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1 union all select Table1.Field1; ); -- or UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) where exists (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] how to do this case when?
Found this now: UPDATE Table1 SET Field1 = (case when Field1 IN (SELECT Field1 FROM Table2) then (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) else Table1.Field1 end) Have a feeling though that this can be shorter. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2008 20:15 To: sqlite-users@sqlite.org Subject: [sqlite] how to do this case when? How do I alter this SQL, so that the original field remains the same when there is no match? A case when else end should do it, but I can't get it right. UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] how to do this case when?
How do I alter this SQL, so that the original field remains the same when there is no match? A case when else end should do it, but I can't get it right. UPDATE Table1 SET Field1 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field1) Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Number of elements in IN clause
>SELECT * FROM maintable WHERE key IN stuff;
Thanks for that tip. Didn't know you could do that.
Can't remember this as standard SQL.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 28 January 2008 12:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Number of elements in IN clause
Felix Radensky <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is there any limitation on the number of elements in IN clause ?
> Can one have, e.g. thousands of elements ? Also, can having
> to many elements become inefficient at some point and one
> has to use some other technique, i.e. comparing elements one
> by one in a loop ?
>
You can create a table that contains the elements that you would
normally put in your IN clause:
CREATE TEMP TABLE stuff(x);
INSERT INTO stuff VALUES('one');
INSERT INTO stuff VALUES('two');
INSERT INTO stuff VALUES('one million');
Then run your query this way:
SELECT * FROM maintable WHERE key IN stuff;
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Could this cause slow queries?
Latest SQLite version with the VB wrapper from Olaf Schmidt, dhRichClient.dll. Running this in VBA Excel on Windows XP. Have a suspicion that maybe you could get slow queries if a table repeatedly gets a DELETE FROM TABLE followed by re-populating the table with inserts, so cyling this repeatedly. It is the delete query that gets slow. No logical explanation and no idea yet what exactly happens, but definitely something strange going on. Maybe it somehow has to do with my application or maybe the wrapper, but these are simple queries and there and the data is all simple and nothing unusual there. I have dealt with this now by dropping the table once in every Excel session and this seems to do the trick. Is there anything in the SQLite code that could make this happen or should I look at my app or the wrapper? Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Slow query on one machine
I can't do anything like that as it is a machine of a customer and I have no access to it. Just wondering what possibly could explain such a difference. The relevant thing is that are load of queries coming after this and they are all fine. So it specific to that particular table at that particular point on that particular machine, all else behaves normal. RBS -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: 19 January 2008 14:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Slow query on one machine On Jan 18, 2008 3:32 PM, RB Smissaert <[EMAIL PROTECTED]> wrote: > The application > that runs this is exactly the same on both machines. The slow machine is > actually slightly slower specification wise, but that can't explain the huge > differences in timings. > Have you run spinrite ( a disk diagnostic/maintenance program ) on the slow machine? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Slow query on one machine
Trying to figure out why (with one particular customer) some queries have very different timings on one machine compared to another machine. It has to do with updating one particular SQLite table with more recent data from an Interbase table. I give the database (S for SQLite and I for Interbase), SQL query and time in seconds of slow machine and fast machine: DB|Query|slow PC|fast PC - S|SELECT MAX(ENTRY_ID) FROM ENTRY_ATTRIBUTES|0.26|8.46 I|SELECT ENTRY_ID, ATTRIBUTE_TYPE, TYPE_SPECIFIC_INFO, NUMERIC_VALUE FROM ENTRY_ATTRIBUTE WHERE ENTRY_ID > 15085882|0.05|0.07 S|INSERT OR IGNORE INTO ENTRY_ATTRIBUTES VALUES(?,?,?,?)|7.51|0.14 S|analyze ENTRY_ATTRIBUTES|431.96|0.03 All I can think of is that somehow there is something wrong with the SQLite table ENTRY_ATTRIBUTES on the slow machine and I suggested dropping that table and making a new one, but that made no difference. The application that runs this is exactly the same on both machines. The slow machine is actually slightly slower specification wise, but that can't explain the huge differences in timings. I think the only way for me to find is to get hold of that SQLite file, but for now having some trouble getting hold of this file. Any ideas? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Helping with table definition?
This is code I used a while ago. Don't use it anymore as I have a better
way to do this via my VB wrapper. There are some lines that deal with code
in other parts of my application, but I take it you can see that.
In case you didn't know this is VB(A).
Function GetSQLiteTableInfo2(strDB As String, _
strTable As String, _
Optional strSelect As String, _
Optional strOmitFields As String, _
Optional strAlias As String, _
Optional strFields As String, _
Optional strDataTypes As String, _
Optional bCurrentConnection As Boolean) As
String()
'will produce the table fields as an 0-based 1-D array
'and make the strings:
'field1,field2,field3 etc.
'field1, field2, field3 etc.
'datatype1,datatype2,datatype3 etc.
'--
Dim r As Long
Dim c As Long
Dim strSQL As String
Dim arr
Dim arr2
Dim arr3
Dim strAlias2 As String
Dim arrOmitFields
Dim bOmit As Boolean
Dim bDoneFirst As Boolean
Dim lRows As Long
Dim strError As String
Dim lDBHandle As Long
10 If Len(strAlias) > 0 Then
20 strAlias2 = strAlias & "."
30 End If
40 If Len(strOmitFields) > 0 Then
50 arrOmitFields = Split(strOmitFields, ",")
60 bOmit = True
70 End If
80 If bShowErrors Then
90 On Error GoTo 0
100 Else
110 On Error GoTo ERROROUT
120 End If
130 If bCurrentConnection = False Then
140 OpenDB strDB
150 End If
160 strSQL = "pragma table_info('" & strTable & "')"
170 arr = GetFromDB(strSQL, lRows, strError, strDB)
180 If lRows = -1 Then
190 GoTo ERROROUT
200 End If
210 If bOmit Then
220 For c = 0 To UBound(arr)
230If ValueIn1DArray(CStr(c + 1), arrOmitFields) = -1 Then
240 If bDoneFirst = False Then
250 strSelect = strAlias2 & arr(c, 1)
260 strFields = arr(c, 1)
270 strDataTypes = arr(c, 2)
280 bDoneFirst = True
290 Else
300 strSelect = strSelect & ", " & strAlias2 & arr(c, 1)
310 strFields = strFields & "," & arr(c, 1)
320 strDataTypes = strDataTypes & "," & arr(c, 2)
330 End If
340End If
350 Next
360 Else
370 For c = 0 To UBound(arr)
380If c = 0 Then
390 strFields = arr(c, 1)
400 strSelect = strAlias2 & arr(c, 1)
410 strDataTypes = arr(c, 2)
420Else
430 strFields = strFields & "," & arr(c, 1)
440 strSelect = strSelect & ", " & strAlias2 & arr(c, 1)
450 strDataTypes = strDataTypes & "," & arr(c, 2)
460End If
470 Next
480 End If
490 arr2 = Split(strFields, ",")
500 ReDim arr3(0 To UBound(arr2)) As String
510 For r = 0 To UBound(arr2)
520 arr3(r) = arr2(r)
530 Next
540 GetSQLiteTableInfo2 = arr3
550 Exit Function
ERROROUT:
560 ReDim arr2(0 To 6) As String
570 arr2(0) = "-1"
580 arr2(1) = CStr(Err.Number)
590 arr2(2) = Err.Description
600 arr2(3) = CStr(Erl)
610 arr2(4) = strTable
620 arr2(5) = strOmitFields
630 arr2(6) = strAlias
650 GetSQLiteTableInfo2 = arr2
End Function
Have a nice Christmas as well.
RBS
-Original Message-
From: Cesar D. Rodas [mailto:[EMAIL PROTECTED]
Sent: 25 December 2007 01:35
To: sqlite-users@sqlite.org
Subject: [sqlite] Helping with table definition?
Hello,
Merry Christmas for every one!
I am wondering if someone did a function (the language doesn't care very
much) to get the table information and want to share him/her code.
I know that you can have the SQL definition of a table doing a "select *
from sqlite_master where type='table' ", but I need to parse SQL and
understand it.
Thanks in advance.
--
Best Regards
Cesar D. Rodas
http://www.cesarodas.com
http://www.thyphp.com
http://www.phpajax.org
Phone: +595-961-974165
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] DeviceSQL
Couldn't find anywhere how much this costs. Newsgroup search shows nil. Has anybody downloaded and tried the demo? RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 12 December 2007 17:10 To: sqlite-users@sqlite.org Subject: Re: [sqlite] DeviceSQL Be careful about speculative comments. For all anyone knows, said product could use SQLite internally with a couple of proprietary optimizations here and there that may make it faster in specific cases. The sqlite public domain license would allow that sort of thing. Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] DB managers that do searches?
> happy user of sqliteman Thanks for the tip, it is quite nice. Two things: Help doesn't launch from the interface and it always seems to give Row(s) returned: 256 even when there are lot more. Another nice one is SQL2006 Pro from OsenXPSuite. RBS -Original Message- From: Bernie Cosell [mailto:[EMAIL PROTECTED] Sent: 31 October 2007 19:20 To: sqlite-users@sqlite.org Subject: RE: [sqlite] DB managers that do searches? On 31 Oct 2007 at 11:37, James Dennett wrote: > Bernie Cosell wrote: > > I guess you've never used a [good] GUI-driven DB > manager/administration > > pgm. > > Your guess (luckily for me) is very wrong. It's just that I call these > GUIs, not "DB managers". Ah... a terminology problem.. I'll just point out that the section in the wiki that has all of these pgms in it is called "Management Tools". > .. They're handy. I have a number of them > installed on the machine on which I write this, and I use them in > addition to command line tools. As I mentioned in another msg on this thread, we use phpMyAdmin for our MySQL databases at work and I can't remember the last time anyone at work needed (or wanted) to use the command line tool. Different strokes... > That's not a problem, is it? Just a question of using a tool at the > right level. If you want to automate things, writing code is often a > good way. If you want to do ad hoc work, a visual tool can be much more > convenient. Just so. Only difference between us here is that I have virtually no use for the command line tool: if I need to automate, I'll just write a little Perl/DBI pgm to do it (I have dozens of 'em..:o)) and I use the "visual tool" for everything else. I'd rather write a small Perl program that try to cobble up a script to be read into the command line app. As above, YMMV... > So you're looking for a graphical tool to allow you to manually view and > modify information in a SQLite3 database? Yes, and I'm now the happy user of sqliteman, so my search is over..:o). It's "query manager" does *exactly* what I needed and works wonderfully. (and indeed, easily found the index conflict I was trying to sort out easily.) /Bernie\ -- Bernie Cosell Fantasy Farm Fibers mailto:[EMAIL PROTECTED] Pearisburg, VA --> Too many people, too few sheep <-- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Wrapper ADO like for VB6
No, haven't tried dhRPCServer as I only work with a local database and single users. RBS -Original Message- From: Giuliano [mailto:[EMAIL PROTECTED] Sent: 26 September 2007 13:58 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Wrapper ADO like for VB6 Thanks, I have seen and it seams to be really interesting. I will try it for sure. Did you also try the dhRPCServer that should act like a server/client? - Original Message - From: "RB Smissaert" <[EMAIL PROTECTED]> To: Sent: Wednesday, September 26, 2007 2:47 PM Subject: RE: [sqlite] Wrapper ADO like for VB6 >I am using dhSQLite, which you can download from here: > http://www.thecommon.net/2.html > > I have tried several wrappers for VB(A) (about 4 or 5) and this > is the best one. Good support as well. > > RBS > > > -Original Message- > From: Giuliano [mailto:[EMAIL PROTECTED] > Sent: 26 September 2007 13:35 > To: sqlite-users@sqlite.org > Subject: [sqlite] Wrapper ADO like for VB6 > > Hello, > > I am porting a big program from SQLServ.. to SQLite and I have > seen that there are many VB6 wrappers available. I would like to > get some suggestion from people who already use one, to be sure > to make the right decision (important: ADO like, stability, upgrades > etc...) > > Many thanks in advance, > Giuliano > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Wrapper ADO like for VB6
I am using dhSQLite, which you can download from here: http://www.thecommon.net/2.html I have tried several wrappers for VB(A) (about 4 or 5) and this is the best one. Good support as well. RBS -Original Message- From: Giuliano [mailto:[EMAIL PROTECTED] Sent: 26 September 2007 13:35 To: sqlite-users@sqlite.org Subject: [sqlite] Wrapper ADO like for VB6 Hello, I am porting a big program from SQLServ.. to SQLite and I have seen that there are many VB6 wrappers available. I would like to get some suggestion from people who already use one, to be sure to make the right decision (important: ADO like, stability, upgrades etc...) Many thanks in advance, Giuliano - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] [Visual Basic] How do you work with SQLite?
Several reasons. Main one is that we won't be allowed as this is a third party application clinical database. The other one is that it would cause too much slow-down of the regular clinical front-end application. This is reporting software and apart from some rare exceptions there is no writing back to Interbase. RBS -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: 10 August 2007 02:58 To: sqlite-users@sqlite.org Subject: RE: [sqlite] [Visual Basic] How do you work with SQLite? At 05:25 PM 8/9/2007, you wrote: >I use it mainly to manipulate data obtained from an Interbase database. >All the data will eventually be dumped to Excel. >I use 2 ways to move data from Interbase to SQLite. >One, via an ADO recordset after connecting to Interbase with ODBC. This >recordset will then be dumped to SQLite via the free VB wrapper from Olaf >Schmidt, dhSQLite. Very good and fast wrapper with a rich object model. >Two, via a custom Delphi dll that moves data directly from Interbase to >SQLite. This is maybe slightly faster than the first method, but not that >much difference. So, no ODBC with this method and no ADO recordset. > >Moving data from SQLite to Excel is all done with dhSQLite. This wrapper can >be downloaed from: http://www.thecommon.net/2.html >Very much recommended for anybody who works with VB or VBA. > >RBS RBS, So why can't you manipulate the data in Interbase? It's reasonably fast. Mike - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] [Visual Basic] How do you work with SQLite?
I use it mainly to manipulate data obtained from an Interbase database. All the data will eventually be dumped to Excel. I use 2 ways to move data from Interbase to SQLite. One, via an ADO recordset after connecting to Interbase with ODBC. This recordset will then be dumped to SQLite via the free VB wrapper from Olaf Schmidt, dhSQLite. Very good and fast wrapper with a rich object model. Two, via a custom Delphi dll that moves data directly from Interbase to SQLite. This is maybe slightly faster than the first method, but not that much difference. So, no ODBC with this method and no ADO recordset. Moving data from SQLite to Excel is all done with dhSQLite. This wrapper can be downloaed from: http://www.thecommon.net/2.html Very much recommended for anybody who works with VB or VBA. RBS -Original Message- From: Gilles Ganault [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 22:46 To: sqlite-users@sqlite.org Subject: [sqlite] [Visual Basic] How do you work with SQLite? Hello I was wondering: how do you VB developpers work with SQLite? Currently, I use a variant array that I fill with data from SQLite, and use ComponentOne's grid object to display the data through its LoadArray() method; If/once the data is updated by the user, I write the array back to SQLite. Do you use ADO et al? Other ways to connect VB and SQLite? Thank you. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple fields update
Yes, thanks, I just found out. It can work without the WHERE clauses. RBS -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 06 August 2007 21:59 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multiple fields update RB Smissaert wrote: > I am sure this SQL used to be fine with SQLite: > > update table1 > set > field1 = 0 where field1 = 2, > field2 = 3 where field2 = 2 > > Now however I get a syntax error near , > > Has this changed? > > > RBS > Does the thread below ring a bell? Does it help? Gerry --- Yes, thanks, that works indeed. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 11 March 2007 02:55 To: SQLite Subject: [sqlite] Re: What is wrong with this UPDATE? RB Smissaert <[EMAIL PROTECTED]> wrote: > > UPDATE > > A3SQLADC_J > > SET > > ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL, > > START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL > > > > near ",": syntax error > Make it UPDATE A3SQLADC_J SET ADDED_DATE = (case when ADDED_DATE = 0 OR ADDED_DATE IS NULL then '' else ADDED_DATE end), START_DATE = (case when START_DATE = 0 OR START_DATE IS NULL then '' else START_DATE end); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Multiple fields update
I am sure this SQL used to be fine with SQLite: update table1 set field1 = 0 where field1 = 2, field2 = 3 where field2 = 2 Now however I get a syntax error near , Has this changed? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
Poor comparison in this case. Are you going to make a mathematical model when you got a little stream to cross and you have a few available planks to do it? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 05 August 2007 16:43 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index? We learn mathematics etc so that we can make numerical models which give us design information. Imagine trying to build every combination of a bridge to settle on a design! Make a mathematical model and get it close to optimal at the first attempt. RB Smissaert wrote: > Yes, I suppose you are right there. > I will see if I can put together a report that runs all possible types of > queries (sequentially) and then see if I have left anything out that would > cause problems. > > RBS > > > -Original Message- > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > Sent: 05 August 2007 03:35 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the > index? > > RB Smissaert wrote: > >> I think an application that >>would produce all the needed indexes based on the table and all the > > possible > >>queries would be helpful. Anybody done such an app? > > _All_ possible queries? Not practical for any significant number of > columns. N factorial gets big fast. > > The indexes would be much larger than the data base itself. > > I'm afraid you are going to have to settle for doing an intelligent > design of the data base. > > > Gerry > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
Yes, I suppose you are right there. I will see if I can put together a report that runs all possible types of queries (sequentially) and then see if I have left anything out that would cause problems. RBS -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 05 August 2007 03:35 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert wrote: > I think an application that > would produce all the needed indexes based on the table and all the possible > queries would be helpful. Anybody done such an app? _All_ possible queries? Not practical for any significant number of columns. N factorial gets big fast. The indexes would be much larger than the data base itself. I'm afraid you are going to have to settle for doing an intelligent design of the data base. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Problem with glob '137*' ?
Queries where there is a glob comparison on a string that could be interpreted as a number always seem a bit slower than when comparing to a string that can't be compared to a number. So for example: select f from t where f glob '137*' is slower than: select f from t where f glob 'abc*' Is this there any explanation for this and is there any way round it? Or can SQLite not be to blame here and is it something in my wrapper? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
Ok, I guessed something like that were the case, but what I didn't get was the purpose of the logN, without knowing the base for that. So why not simply something like: if (N / m) > 2 then most likely index will be helpful. It sure is tricky to add these indexes as getting it wrong will seriously affect performance. Trouble in this particular case is that I am dealing with lots of different queries set by the user. I think an application that would produce all the needed indexes based on the table and all the possible queries would be helpful. Anybody done such an app? I think though that I am getting close now to having it all covered and thanks again for all the assistance. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 23:45 To: SQLite Subject: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > But then if the base of the logarithm doesn't matter then > how is this equation going to help you? > > m==N/logN > > So, basically it comes down to some experimenting? Well, it tells you that if m is much smaller than N (say, by two orders of magnitude or more), it's a pretty safe bet that index will be useful. If m is close to N (say, within an order of magnitude), it is a pretty safe bet the index will be unhelpful. In the middle lies an area where it's more or less a wash. Most real world problems tend to fall into the two well-defined areas. If you find your particular problem to fall into the gray area, then yes, you might want to experiment. But in this case, even if you find that an index helps, it is unlikely to help by much, so any advantage may be outweighed by additional space requirements and slowdown on inserts and updates. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
But then if the base of the logarithm doesn't matter then how is this equation going to help you? m==N/logN So, basically it comes down to some experimenting? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 21:32 To: SQLite Subject: [sqlite] Re: Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > OK, will have a look at the wiki. > >> There's no "m" on the right hand side. >> m equals N divided by logarithm of N. > > What is the base of that logarithm then? Doesn't matter. All calulations shown are order of magnitude, only accurate modulo multiplication by some unknown constant. Choosing different base for the logarithm simply changes this constant. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: Re: How does SQLite choose the index?
OK, will have a look at the wiki. > There's no "m" on the right hand side. > m equals N divided by logarithm of N. What is the base of that logarithm then? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 21:03 To: SQLite Subject: [sqlite] Re: Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > Thanks; I have seen this O(N) etc. explanations a lot, but not sure > what they exactly mean. http://en.wikipedia.org/wiki/Big_O_notation Roughly, we say that an algorithm has complexity O(N) (where N is the size of its input) when there exists some constant C such that the running time of an algorithm on this input is no more than C*N. >> and for each entry would perform a logN > > Does the logN here mean m log N or something else? Yes, logN is the same as log N or log(N) - a logarithm of N. >> m==N/logN > > Ditto, does this mean break even point roughly when m equals N / (m > log N) ? There's no "m" on the right hand side. m equals N divided by logarithm of N. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: Re: How does SQLite choose the index?
Thanks; I have seen this O(N) etc. explanations a lot, but not sure what they exactly mean. Does it in this case simply mean O * N and O * (m log N) ? > and for each entry would perform a logN Does the logN here mean m log N or something else? > m==N/logN Ditto, does this mean break even point roughly when m equals N / (m log N) ? Sorry, these might be basic questions, but would like to get this clear. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 20:01 To: SQLite Subject: [sqlite] Re: Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > One thing I am not sure about yet is when an index would be helpful > in the > first place in relation to the data in the field. > I understand an index is going to help little if the values in a > particular > field can only for example be 1 or 0, but roughly when does it become > useful > to add an index? Suppose you have a table with N records. You run a query like "select * from t where f='x'; " which selects m records. Without an index on t(f), the query would run in O(N) time. With the index, it would be O(m log N) (it will scan m entries in the index, and for each entry would perform a logN lookup in the main table, by rowid). Thus, when m is close to N (that is, the query selects almost all records), an index actually performs worse than a linear scan. The break-even point is somewhere on the order m==N/logN. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Re: How does SQLite choose the index?
Thanks; I think I now know most rules to create indexes in a sensible way. One thing I am not sure about yet is when an index would be helpful in the first place in relation to the data in the field. I understand an index is going to help little if the values in a particular field can only for example be 1 or 0, but roughly when does it become useful to add an index? This is when the cost (time) of adding the index doesn't matter. I don't have to worry about inserts in this case. Maybe this whole topic should be covered somewhere in the documentation. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 16:14 To: SQLite Subject: [sqlite] Re: Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > How does the field order in indexes work with joins? > So for example given the query: > > select > t1.a, > t1.b, > t2.c > from > table1 t1 inner join table2 t2 on > (t1.id1 = t2.id2) > where > t1.a = 'abc' > > would the index need to be > (a, id1) > or > (id1, a) Doesn't matter. SQLite internally converts the original query to something like select t1.a, t1.b, t2.c from table1 t1, table2 t2 where t1.id1 = t2.id2 and t1.a = 'abc'; It then knows that the two operands of the AND can be checked in any order, so it could use either index. If, on the other hand, the last condition were t1.a >= 'abc', then an index on (id1, a) could be used to satisfy both conditions, but an index on (a, id1) only works for inequality but doesn't help with t1.id1=t2.id2 > Does the field order in the tables have anything to do with this No. > or is it just the field order in the query The field order in the query doesn't matter much, either. SQLite is smart enough to rearrange the checks in a variety of ways. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: How does SQLite choose the index?
How does the field order in indexes work with joins? So for example given the query: select t1.a, t1.b, t2.c from table1 t1 inner join table2 t2 on (t1.id1 = t2.id2) where t1.a = 'abc' would the index need to be (a, id1) or (id1, a) Does the field order in the tables have anything to do with this or is it just the field order in the query and field order in the index that matter? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 15:49 To: SQLite Subject: [sqlite] Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > So, basically it is best to make one large index (apart from the > primary > integer key?) that includes all fields that could be in a WHERE > clause or a > JOIN or a GROUP BY or a HAVING or an ORDER BY? That depends on the queries you want to speed up. Index columns can only be used from left to right, with no skips, to satisfy the conditions of the query. For example, if you have an index on columns (a, b, c) and a query like select * from t where a='x' and c='z'; then the index can be used to satisfy a='x' condition, but then a linear check of all records having a='x' is performed to satisfy c='z'. If you run such a query often and need it to run fast, you may want another index on (a, c), or perhaps (a, c, b). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: How does SQLite choose the index?
> Index columns can only be used from left to right, with no skips, > to satisfy the conditions of the query. Ah, yes, I forgot about that one. So, I will need some more indexes. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 15:49 To: SQLite Subject: [sqlite] Re: Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > So, basically it is best to make one large index (apart from the > primary > integer key?) that includes all fields that could be in a WHERE > clause or a > JOIN or a GROUP BY or a HAVING or an ORDER BY? That depends on the queries you want to speed up. Index columns can only be used from left to right, with no skips, to satisfy the conditions of the query. For example, if you have an index on columns (a, b, c) and a query like select * from t where a='x' and c='z'; then the index can be used to satisfy a='x' condition, but then a linear check of all records having a='x' is performed to satisfy c='z'. If you run such a query often and need it to run fast, you may want another index on (a, c), or perhaps (a, c, b). Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: How does SQLite choose the index?
Thanks, that was very useful. I didn't realize that table values could be obtained from the index. I suppose it makes sense when you think about it. So, basically it is best to make one large index (apart from the primary integer key?) that includes all fields that could be in a WHERE clause or a JOIN or a GROUP BY or a HAVING or an ORDER BY? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 15:18 To: SQLite Subject: [sqlite] Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > I get this query plan (explain query plan): > > order from detail > > 0 0 TABLE ENTRY AS E WITH INDEX > IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID > > My question is why doesn't it pick the index: > IDX3$ENTRY$READ_CODE > > Not sure, but I would think that is more effective. It's exactly the same in terms of efficiency. In fact, it is completely pointless to have two indexes where the column list of one is a strict prefix of the column list of another. The latter can be used, equally efficiently, everywhere the former can be used. In some cases the latter may even be more efficient. Consider: create table t (a text, b text); create index ta on t(a); create index tab on t(a, b); select a, b from t where a='xyz'; If SQLite chooses to use index ta, then it needs to perform a lookup in the table (by rowid) to retrieve the value of b. But if it uses index tab, then the value of b is stored in the index, and the table itself doesn't need to be consulted at all. So fewer pages to read from disk. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How does SQLite choose the index?
Given this table: CREATE TABLE AMorb37F6_E ([PATIENT_ID] INTEGER, [ENTRY_ID] INTEGER PRIMARY KEY, [READ_CODE] TEXT, [ADDED_DATE] TEXT, [START_DATE] TEXT) And these indexes: IDX10$ENTRY$PATIENT_ID IDX11$ENTRY$TERM_TEXT IDX12$ENTRY$READ_CODE$ADDED_DATE IDX13$ENTRY$READ_CODE$START_DATE IDX14$ENTRY$READ_CODE$PROBLEM_ID IDX15$ENTRY$READ_CODE$ADDED_DATE$PROBLEM_ID IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID IDX2$ENTRY$ADDED_BY IDX3$ENTRY$READ_CODE IDX4$ENTRY$ENCOUNTER_ID IDX5$ENTRY$ADDED_DATE IDX6$ENTRY$UPDATED_DATE IDX7$ENTRY$START_DATE IDX8$ENTRY$PROBLEM_ID IDX9$ENTRY$ENTRY_FLAGS And this query: SELECT E.PATIENT_ID, E.ENTRY_ID, E.READ_CODE, E.ADDED_DATE, E.START_DATE FROM ENTRY E WHERE E.READ_CODE GLOB 'G2*' AND (NOT E.DORMANT_FLAG = 1) ORDER BY E.PATIENT_ID ASC, E.ENTRY_ID ASC I get this query plan (explain query plan): order fromdetail 0 0 TABLE ENTRY AS E WITH INDEX IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID My question is why doesn't it pick the index: IDX3$ENTRY$READ_CODE Not sure, but I would think that is more effective. What are the general rules as to how SQLites picks from the available indexes? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] strategy adding indexes
Re: [sqlite] strategy adding indexes drh Tue, 31 Jul 2007 03:12:54 -0700 T&B <[EMAIL PROTECTED]> wrote: > Hi RBS, > > > - indexes that include all possible combinations of fields that may > > appear > > in a WHERE clause. > > As an aside, note that, AFAIK, indexes are only used: > > 1. To get the first match of a query. If you ask for more than one > matching record, the second, third etc matches are found by searching, > not through the index. No. If an index is used to retrieve the first row in the result, then it continues to be used for every row. Depending on the query, it is often possible to not have to do a full binary search of the index on the second and subsequent rows. Instead, the next matching index entry might be adjacent to the previous one so finding the next value might be as simple as moving one index entry to the left or to the right. But whether or not you can do this depends on the query. And the index is still used for every row, regardless. > > 2. From left to right in the same order as your index. So if you > create index MyIndex on MyTable ( Column1, Column2, Column3 ), then > you must test them in the same order, eg: where Column1 = Value1 and > Column2 = Value2 or Column3 = Value3. If you miss a column in the > sequence or place one out of order, the index won't be used from that > point in the test onwards. The order of terms in a WHERE clause make no difference. All of the following work the same: WHERE column1=1 AND column2=2 AND column3=3 WHERE 2=column2 AND column3=3 AND column1=1 WHERE column2=2 AND 1=column1 AND column3=3 WHERE 3=column3 AND 2=column2 and 1=column1 WHERE column3=3 AND column1=1 AND 2=column2 WHERE 1=column2 AND column3=3 AND column2=2 Notice also that A=B is the same as B=A. But it is the case that you must cover some prefix of terms from the index. So if the index is on column1,column2,column3 in that order, and if you say: WHERE column1=1 AND column2!=7 AND column3=3 Then only the first term (column1) will be used by the index because you cannot use an index on a != operator. If you say WHERE column1!=7 AND column2=2 AND column3=3 Then the index cannot be used at all. If you say: WHERE column1=1 AND column2>3 AND column3=3 Then the first two terms of the index will be used. Each index can use at most one inequality operator and it must be on the right-most column of the index that gets used. > > 3. In equality tests, eg "=" (equals) and "in". If you use "like" for > comparison, the index isn't used. The last test (only) may be one or > two inequality tests, such as ">" or "<". And that last test must be > in sequence (ie rule 2). > http://www.sqite.org/optoverview.html -- D. Richard Hipp <[EMAIL PROTECTED]> Thanks, that is very useful. I wonder now if there would any mileage in writing an application (or does it exist already?) that takes a table (or tables) and all the possible queries and from those 2 produces a sensible plan to add the indexes. I suppose to do that you need a good understanding of the SQLite optimizer and if you have that then maybe you don't need such an application. Just one thing I noticed: When I run a query like for example this: Select from tableX where read_code GLOB 'bd*' and entry_type = 8 It takes about 2 minutes Now when after that I run this query: Select from tableX where read_code GLOB 'bx*' and entry_type = 8 It will run in 10 seconds. Why is this if the second query is not the same as the first? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] strategy adding indexes
Hi Tom, Thanks for that; useful to know. Didn't know about point 1 and 2 and that will complicate matters a bit further. RBS -Original Message- From: T&B [mailto:[EMAIL PROTECTED] Sent: 31 July 2007 00:39 To: sqlite-users@sqlite.org Subject: Re: [sqlite] strategy adding indexes Hi RBS, > - indexes that include all possible combinations of fields that may > appear > in a WHERE clause. As an aside, note that, AFAIK, indexes are only used: 1. To get the first match of a query. If you ask for more than one matching record, the second, third etc matches are found by searching, not through the index. 2. From left to right in the same order as your index. So if you create index MyIndex on MyTable ( Column1, Column2, Column3 ), then you must test them in the same order, eg: where Column1 = Value1 and Column2 = Value2 or Column3 = Value3. If you miss a column in the sequence or place one out of order, the index won't be used from that point in the test onwards. 3. In equality tests, eg "=" (equals) and "in". If you use "like" for comparison, the index isn't used. The last test (only) may be one or two inequality tests, such as ">" or "<". And that last test must be in sequence (ie rule 2). I hope this helps a bit. Some more learned SQLiters out there may care to correct or clarify. Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] strategy adding indexes
What would be a good strategy in adding indexes to the various tables? I know SQLite can only use one index in simple (not intersect etc.) queries, so is it usually best to make: - indexes that include all possible combinations of fields that may appear in a WHERE clause. - make one very large index combining all fields that may appear in a WHERE clause. Or would it be better to make single field indexes and go with intersect etc? I understand that in the end it will come down to a lot of experimenting, but maybe there are some useful guidelines/rules that speed up this large task. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Interrupt SQLite
Thanks, that is a very useful tip! RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 July 2007 00:56 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Interrupt SQLite "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Is it somehow possible to interrupt an ongoing INSERT operation? I made a > mistake in an index and now got into a very long process that I would like > to stop. I am running this from VBA via the dll from Olaf Schmidt, > dhSQLite.dll. I don't want to kill Excel as I would lose some work. > Thanks for any advice. > Try making the journal file read-only. That should stop it. And force a rollback. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Interrupt SQLite
Is it somehow possible to interrupt an ongoing INSERT operation? I made a mistake in an index and now got into a very long process that I would like to stop. I am running this from VBA via the dll from Olaf Schmidt, dhSQLite.dll. I don't want to kill Excel as I would lose some work. Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: inner join
That is an interesting one. Where could I find documentation about coalesce? RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 16 July 2007 12:49 To: SQLite Subject: [sqlite] Re: inner join Andre du Plessis <[EMAIL PROTECTED]> wrote: > I would like to be able to accomplish the following but don't see any > support for inner joins on update queries. > > update A > set Id = B.Id > from A inner join B > on A.Field1 = B.Field_Temp > where B.Price > 0 update A set Id = coalesce( (select Id from B where A.Field1 = B.Field_Temp and B.Price > 0), Id); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: How to store 128 bit values
> So no, you won't gain anything by trying to avoid this column - > it is always there whether you declare it or not. But I found that inserts were faster if I didn't create the table with INTEGER PRIMARY KEY, so it looked I gained there, although I understand I might lose out somewhere else. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 11 July 2007 20:18 To: SQLite Subject: [sqlite] Re: How to store 128 bit values RB Smissaert <[EMAIL PROTECTED]> wrote: > It seems if you do inserts on a table it is faster if you have no > INTEGER > PRIMARY KEY on that table You _always_ have an INTEGER PRIMARY KEY on every table. It's part of SQLite storage mechanism. If you don't explicitly declare one, it's still there under the name ROWID or OID (and a few other synonyms). By explicitly declaring it, you simply give the same column yet another name. So no, you won't gain anything by trying to avoid this column - it is always there whether you declare it or not. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to store 128 bit values
> I have not measured it, but I'm guessing that the speed reduction > caused by inserting INTEGER PRIMARY KEY out of order is much less > than the speed penalty of building a separate index. Thanks. I did measure that and thought that the speed penalty of creating the new index was less than the penalty of inserting with INTEGER PRIMARY KEY, but I will have a look at this again. I suppose it depends a lot on the data. Now of course I also will need to look at the speed penalty in selecting and joining when there is no INTEGER PRIMARY KEY. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 11 July 2007 20:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to store 128 bit values "RB Smissaert" <[EMAIL PROTECTED]> wrote: > > Looking up a record by INTEGER PRIMARY KEY is always twice as > > fast as looking up the same record by any other key > > Didn't realize that, but I have a question in connection with this. > It seems if you do inserts on a table it is faster if you have no INTEGER > PRIMARY KEY on that table and then later create an integer key on that same > field. But that would mean that selects and joins may be slower. > Am I seeing this right or is there any way round this, so have fast inserts > and still have the INTEGER PRIMARY KEY? The b-trees used for tables are optimized for inserting new entries at the end of the table, because this is the common case. If you have an INTEGER PRIMARY KEY, then inserts will therefore be fastest if you insert in primary key order. If you do not have an INTEGER PRIMARY KEY, then SQLite makes one up for you automatically, and the made-up primary key (the rowid) is almost always larger than all previous rowids so the net effect is that you are always appending and always going quickly. I have not measured it, but I'm guessing that the speed reduction caused by inserting INTEGER PRIMARY KEY out of order is much less than the speed penalty of building a separate index. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to store 128 bit values
> Looking up a record by INTEGER PRIMARY KEY is always twice as > fast as looking up the same record by any other key Didn't realize that, but I have a question in connection with this. It seems if you do inserts on a table it is faster if you have no INTEGER PRIMARY KEY on that table and then later create an integer key on that same field. But that would mean that selects and joins may be slower. Am I seeing this right or is there any way round this, so have fast inserts and still have the INTEGER PRIMARY KEY? Thanks for any advice. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 11 July 2007 19:33 To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to store 128 bit values "Steve Krulewitz" <[EMAIL PROTECTED]> wrote: > Hey all -- > > In the application I am working on (Songbird), we have a simple two > table schema representing the tracks in your music collection and the > properties on those tracks. The keys used are all UUIDs (128 bit > number) which we are currently storing in hex string form in a text > column, so they literally appear in the database as > "ee89b823-e709-40c5-bed7-dcb0b2b791a8". We do lots of lookups and > joins on these keys. > > I was wondering if there is much to be gained by storing these 128 bit > values in binary rather than as strings. Storing the UUIDs as BLOBs rather than as hex-encoded strings will requires 21 bytes per key instead of 43. So your indices will have about twice their current fanout. That means that you can expect to roughly double your lookup performance on a cold cache. (If the database, or large parts of it, is already in your OS cache, the difference will be much less and will likely not be noticable.) The downside of using BLOBs is that you cannot see them easily during debugging when you do a "SELECT * FROM...". You have to use constructs like, "SELECT hex(uuid), ... FROM" which is more typing. You can fix that with a VIEW, I suppose. The thing to consider is why you are using 128-bit UUIDs in the first place? Presumably you are doing this so that you can sync and/or merge independently created databases without having to worry about key collisions. If you are not doing syncs or merges or independently generated keys, then I can't think of a good reason to use 128-bit UUIDs in the first place. Just use small integer autogenerated keys from SQLite. Assuming you are doing syncing and merging, what I tend to do in these kinds of situations is to create a mapping between the universally unique ID (UUID) and a small integer ID that is unique in the local database - call the latter the RID. The RID is just an integer and can be your INTEGER PRIMARY KEY for very fast lookups. Looking up a record by INTEGER PRIMARY KEY is always twice as fast as looking up the same record by any other key, and because of high fanout can potentially be much faster if you have a cold cache. So I use the RID for joins or other internal operations and only use the UUID for lookups from external data. For example, your schema might look something like this: CREATE TABLE album( aid INTEGER PRIMARY KEY, -- Internally unique album ID uuid TEXT UNIQUE, -- Universally unique album ID ... ); CREATE TABLE track( tid INTEGER PRIMARY KEY, -- Internally unique track ID uuid TEXT UNIQUE, -- Universally unique track ID aid INTEGER REFERENCES album, -- Album containing this track ... ); CREATE INDEX track_album ON track(aid); A typical query might be to find all tracks of an album: SELECT * FROM track WHERE aid=(SELECT aid FROM album WHERE title=?) And queries like this will run much faster using INTEGER PRIMARY KEYS rather than UUIDs. All that said, for even the largest music collection, your database is unlikely to have more than a few thousand albums and a few tens of thousands of tracks, and with such a small database and running on a modern workstations, probably just about anything you do is going to be fast enough. The optimizations described above are useful if you have tables with millions of entries or if you are doing thousands of queries per second or if you are running on some woefully underpowered portable music player. But for a personal music library running on a workstation at human-interaction speed, use whatever schema makes it easiest for you to type in correct and working code. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] error in round-function?
Just checked my code and luckily I don't round in SQLite. I suppose an easy work-around for now would be to do something like: Select round(field + 0.001, 1) as it will be unlikely you are dealing with 0.949 RBS -Original Message- From: Olaf Schmidt [mailto:[EMAIL PROTECTED] Sent: 10 June 2007 21:34 To: sqlite-users@sqlite.org Subject: Re: [sqlite] error in round-function? Thanks to RBS and Alberto for testing... So it seems the problem is somehow Windows-related (maybe the VC-Compiler). Think I'll override the Round-Function in my wrapper. Regards, Olaf -- View this message in context: http://www.nabble.com/error-in-round-function--tf3897765.html#a11052069 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] error in round-function?
On Windows XP: Did select round(0.95, 1) with your VB wrapper, gives 0 Same in the free SQLite2006 Pro (from OsenXPSuite) and same result. RBS -Original Message- From: Olaf Schmidt [mailto:[EMAIL PROTECTED] Sent: 10 June 2007 19:35 To: sqlite-users@sqlite.org Subject: Re: [sqlite] error in round-function? > > select round(0.95, 1) > 0.9 > > select round(9.95, 1) > 9.9 > > select round(0.995, 2) > 0.99 > > select round(9.995, 2) > 9.99 > (3.3.17 here) As it should be, hmm. On what OS have you tested? If on windows, was it a GCC-compile or a MS-VC-compile? Olaf -- View this message in context: http://www.nabble.com/error-in-round-function--tf3897765.html#a11051093 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Age calculation on literal
This function works and despite the convoluted construction it is pretty
fast, takes about 0.5 sec to convert one field in about 25000 rows.
I thought I can speed it up by replacing all the SQLite functions that
produce a literal by a VB variable. Indeed with this I can get it down to
about 0.3 secs. Does this mean that SQLite recalculates these functions
multiple times for every row? I suppose it does and in a way it makes sense
as 'now' changes over time.
In case any VB user is interested in this:
Function CurrentDateISO8601() As String
Dim lCY As Long
Dim lCM As Long
Dim lCD As Long
Dim strZeroMonth As String
Dim strZeroDay As String
lCY = Year(Date)
lCM = Month(Date)
lCD = Day(Date)
If lCM < 10 Then
strZeroMonth = "0"
End If
If lCD < 10 Then
strZeroDay = "0"
End If
CurrentDateISO8601 = "'" & lCY & "-" & _
strZeroMonth & lCM & "-" & _
strZeroDay & lCD & "'"
End Function
Function ISO8601Date2AgeInMonths(strField As String, _
Optional strAlias As String) As String
Dim strAS As String
Dim strCDate As String
Dim lCM As Long
Dim lCD As Long
Dim strCY As String
Dim strCM As String
Dim strCD As String
lCM = Month(Date)
lCD = Day(Date)
strCY = "'" & CStr(Year(Date)) & "'"
If lCM < 10 Then
strCM = "'0" & CStr(lCM) & "'"
Else
strCM = "'" & CStr(lCM) & "'"
End If
If lCD < 10 Then
strCD = "'0" & CStr(lCD) & "'"
Else
strCD = "'" & CStr(lCD) & "'"
End If
strCDate = CurrentDateISO8601()
If Len(strAlias) > 0 Then
strAS = " AS "
End If
ISO8601Date2AgeInMonths = _
"case when " & strCDate & " >= " & _
"date(" & strField & ", '+' || " & strCY & " - " & _
"strftime('%Y', " & strField & ") || ' years') then " & _
"case when " & strCD & " < strftime('%d', " & strField & ") then " & _
"((" & strCY & " - strftime('%Y', " & strField & ")) * 12 + " & _
"(" & strCM & " - strftime('%m', " & strField & "))) - 1 " & _
"else " & _
"((" & strCY & " - strftime('%Y', " & strField & ")) * 12 + " & _
"(" & strCM & " - strftime('%m', " & strField & "))) - 0 " & _
"end " & _
"else " & _
"case when " & strCD & " < strftime('%d', " & strField & ") then " & _
"((" & strCY & " - strftime('%Y', " & strField & ") - 1) * 12 + " & _
"(" & strCM & " + (12 - strftime('%m', " & strField & " - 1 " & _
"else " & _
"((" & strCY & " - strftime('%Y', " & strField & ") - 1) * 12 + " & _
"(" & strCM & " + (12 - strftime('%m', " & strField & " - 0 " & _
"End " & _
"End" & strAS & strAlias
End Function
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 23:45
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Age calculation on literal
Got the syntax right, but not the logic.
I believe this (VB) function will now get the right SQL to get the age in
months:
Function ISO8601Date2AgeInMonths(strField As String, _
Optional strAlias As String) As String
Dim strAS As String
If Len(strAlias) > 0 Then
strAS = " AS "
End If
ISO8601Date2AgeInMonths = "case when date('now') >= " & _
"date(" & strField & ", '+' || (strftime('%Y',
'now') - " & _
"strftime('%Y', " & strField & ")) || ' years')
then " & _
"case when strftime('%d', 'now') <
strftime('%d', " & strField & ") then " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strFiel
RE: [sqlite] Age calculation on literal
Got the syntax right, but not the logic.
I believe this (VB) function will now get the right SQL to get the age in
months:
Function ISO8601Date2AgeInMonths(strField As String, _
Optional strAlias As String) As String
Dim strAS As String
If Len(strAlias) > 0 Then
strAS = " AS "
End If
ISO8601Date2AgeInMonths = "case when date('now') >= " & _
"date(" & strField & ", '+' || (strftime('%Y',
'now') - " & _
"strftime('%Y', " & strField & ")) || ' years')
then " & _
"case when strftime('%d', 'now') <
strftime('%d', " & strField & ") then " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ")) * 12 + " & _
"(strftime('%m', 'now') - strftime('%m', " &
strField & "))) - 1 " & _
"else " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ")) * 12 + " & _
"(strftime('%m', 'now') - strftime('%m', " &
strField & "))) - 0 " & _
"end " & _
"else " & _
"case when " & _
"strftime('%d', 'now') < strftime('%d', " &
strField & ") " & _
"then " & _
"(strftime('%Y', 'now') - strftime('%Y', " &
strField & ") - 1) * 12 + " & _
"(strftime('%m', 'now') + (12 - strftime('%m', "
& strField & "))) - 1 " & _
"else " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ") - 1) * 12 + " & _
"(strftime('%m', 'now') + (12 - strftime('%m', "
& strField & " - 0 " & _
"End " & _
"End" & strAS & strAlias
End Function
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 21:46
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Age calculation on literal
Got this now, after correcting the brackets:
SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y', '2006-
10-14')) || ' years') <= date('now') then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14'))) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> Got this nearly worked out now, but somehow I can't get the nested case
> when
> syntax right:
>
> SELECT
> case
> when
> date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
RE: [sqlite] Age calculation on literal
Got this now, after correcting the brackets:
SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y', '2006-
10-14')) || ' years') <= date('now') then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14'))) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> Got this nearly worked out now, but somehow I can't get the nested case
> when
> syntax right:
>
> SELECT
> case
> when
> date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
> '2006-10-14')) || ' years') <= date('now')
> then
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
> (strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +
You have an extra closing bracket in the line above.
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
> end
> else
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14' -1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14')))
> end
> end
>
> It will give me an error (from my VB wrapper) syntax error near else.
> Any idea what is wrong here?
Try this instead, I find the extra indentation makes it easier to see what
you are doing.
SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
'2006-10-14')) || ' years') <= date('now')
then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14' -1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Age calculation on literal
Yes, that looks better and thanks for that.
Still get the same error though.
I will keep fiddling with it.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> Got this nearly worked out now, but somehow I can't get the nested case
> when
> syntax right:
>
> SELECT
> case
> when
> date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
> '2006-10-14')) || ' years') <= date('now')
> then
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
> (strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +
You have an extra closing bracket in the line above.
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
> end
> else
> case when
> strftime('%d', 'now') > strftime('%d', '2006-10-14')
> then
> ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14' -1
> else
> (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
> (strftime('%m', 'now') +
> (12 - strftime('%m', '2006-10-14')))
> end
> end
>
> It will give me an error (from my VB wrapper) syntax error near else.
> Any idea what is wrong here?
Try this instead, I find the extra indentation makes it easier to see what
you are doing.
SELECT
case when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
'2006-10-14')) || ' years') <= date('now')
then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14' -1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') - 1) * 12 +
(strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))
end
end
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Age calculation on literal
Got this nearly worked out now, but somehow I can't get the nested case when
syntax right:
SELECT
case
when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
'2006-10-14')) || ' years') <= date('now')
then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14' -1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14')))
end
end
It will give me an error (from my VB wrapper) syntax error near else.
Any idea what is wrong here?
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when
> date('2002-01-01', '+' || (strftime('%Y', 'now') -
> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
> then
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
> end
>
> Why is this?
>
>
This works for me in the sqlite shell as shown below:
C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
...> case when
...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
...> then
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
...> else
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
...> end
...> ;
5
How are you running this query?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Age calculation on literal
> How are you running this query?
I am running this from Excel VBA with a free wrapper from Olaf Schmidt,
dhSQLite, based on 3.3.17. I will check my code, but can't think of a way
why it should add 100 with literals and not on table fields.
Must admit I have been wrong before with these kind of things ...
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when
> date('2002-01-01', '+' || (strftime('%Y', 'now') -
> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
> then
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
> end
>
> Why is this?
>
>
This works for me in the sqlite shell as shown below:
C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
...> case when
...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
...> then
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
...> else
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
...> end
...> ;
5
How are you running this query?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Age calculation on literal
Sorry, forget about this, it was something in the VBA code, so nil to do
with SQLite.
Could I ask you how I would get the age in months? I can see it will be
along similar lines, but maybe you have worked it out already.
I need it to be full calendar months, so, if current date is 2007-05-31 then
DOB Age in months
--
2007-05-01 0
2007-04-30 1
2007-01-01 4
Etc.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when
> date('2002-01-01', '+' || (strftime('%Y', 'now') -
> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
> then
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
> end
>
> Why is this?
>
>
This works for me in the sqlite shell as shown below:
C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
...> case when
...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
...> then
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
...> else
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
...> end
...> ;
5
How are you running this query?
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Age calculation on literal
Thanks to Dennis Cote I got a nice way to get the age from the date in the
form '-nmm-dd'. It works fine when I run it on a field, but when I run
it on a literal date it gives me 100 too much:
select
case when
date('2002-01-01', '+' || (strftime('%Y', 'now') -
strftime('%Y', '2002-01-01')) || ' years') <= date('now')
then
strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
else
strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
end
Why is this?
RBS
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Longest "real" SQL statement
This is one of my biggest and it is part of a number of queries to transpose a table: INSERT OR REPLACE INTO A3BP619_J(PATIENT_ID, ENTRY_ID_E1, START_DATE_E1, ADDED_DATE_E1, SYST_E1, DIAST_E1, ENTRY_ID_E2, START_DATE_E2, ADDED_DATE_E2, SYST_E2, DIAST_E2, ENTRY_ID_E3, START_DATE_E3, ADDED_DATE_E3, SYST_E3, DIAST_E3, ENTRY_ID_E4, START_DATE_E4, ADDED_DATE_E4, SYST_E4, DIAST_E4, ENTRY_ID_E5, START_DATE_E5, ADDED_DATE_E5, SYST_E5, DIAST_E5, ENTRY_ID_E6, START_DATE_E6, ADDED_DATE_E6, SYST_E6, DIAST_E6, ENTRY_ID_E7, START_DATE_E7, ADDED_DATE_E7, SYST_E7, DIAST_E7, ENTRY_ID_E8, START_DATE_E8, ADDED_DATE_E8, SYST_E8, DIAST_E8, ENTRY_ID_E9, START_DATE_E9, ADDED_DATE_E9, SYST_E9, DIAST_E9, ENTRY_ID_E10, START_DATE_E10, ADDED_DATE_E10, SYST_E10, DIAST_E10, ENTRY_ID_E11, START_DATE_E11, ADDED_DATE_E11, SYST_E11, DIAST_E11, ENTRY_ID_E12, START_DATE_E12, ADDED_DATE_E12, SYST_E12, DIAST_E12, ENTRY_ID_E13, START_DATE_E13, ADDED_DATE_E13, SYST_E13, DIAST_E13, ENTRY_ID_E14, START_DATE_E14, ADDED_DATE_E14, SYST_E14, DIAST_E14, ENTRY_ID_E15, START_DATE_E15, ADDED_DATE_E15, SYST_E15, DIAST_E15, ENTRY_ID_E16, START_DATE_E16, ADDED_DATE_E16, SYST_E16, DIAST_E16, ENTRY_ID_E17, START_DATE_E17, ADDED_DATE_E17, SYST_E17, DIAST_E17, ENTRY_ID_E18, START_DATE_E18, ADDED_DATE_E18, SYST_E18, DIAST_E18, ENTRY_ID_E19, START_DATE_E19, ADDED_DATE_E19, SYST_E19, DIAST_E19, ENTRY_ID_E20, START_DATE_E20, ADDED_DATE_E20, SYST_E20, DIAST_E20, ENTRY_ID_E21, START_DATE_E21, ADDED_DATE_E21, SYST_E21, DIAST_E21, ENTRY_ID_E22, START_DATE_E22, ADDED_DATE_E22, SYST_E22, DIAST_E22, ENTRY_ID_E23, START_DATE_E23, ADDED_DATE_E23, SYST_E23, DIAST_E23, ENTRY_ID_E24, START_DATE_E24, ADDED_DATE_E24, SYST_E24, DIAST_E24, ENTRY_ID_E25, START_DATE_E25, ADDED_DATE_E25, SYST_E25, DIAST_E25, ENTRY_ID_E26, START_DATE_ <<---etc.--->> T JOIN GROUP_39 g39 ON (t1.PATIENT_ID = g39.PID) LEFT JOIN GROUP_40 g40 ON (t1.PATIENT_ID = g40.PID) It can be a lot longer even in Excel 2007 as that has many more available columns. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 10 May 2007 00:33 To: sqlite-users@sqlite.org Subject: [sqlite] Longest "real" SQL statement I'm looking for an upper bound on how big legitimate SQL statements handed to SQLite get to be. I'm not interested in contrived examples. I want to see really big SQL statements that are actually used in real programs. "Big" can be defined in several ways: * Number of bytes of text in the SQL statement. * Number of tokens in the SQL statement * Number of result columns in a SELECT * Number of terms in an expression If you are using really big SQL statements, please tell me about them. I'd like to see the actual SQL text if possible. But if your use is proprietary, please at least tell me how big your query is in bytes or tokens or columns or expression terms. Thanks. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] excel and sqlite
I have been using SQLite in Excel for the last half year now (in a commercial application) and I think I will be able to help. Currently I am using the wrapper written by Olaf Schmidt and this works very well: www.datenhaus.de/Downloads/dhSQLite-Demo.zip If you are interested then I can send you a demo workbook. RBS -Original Message- From: steve31415 [mailto:[EMAIL PROTECTED] Sent: 07 May 2007 18:03 To: sqlite-users@sqlite.org Subject: [sqlite] excel and sqlite Hi, I am developing a VBA app in excel 2003 and I would like to know how to setup sqlite so that I can use it. I am a newbie when it comes to databases. Currently I have installed the sqlite ODBC driver (www.ch-werner.de/sqliteodbc/) and have created a database using sqlitebrowser (http://sourceforge.net/projects/sqlitebrowser/). What should I do next? Thanks for any suggestions -- View this message in context: http://www.nabble.com/excel-and-sqlite-tf3705051.html#a10361323 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Search on Age, from DOB column
I am also working with a clinical application, using SQLite and VBA.
I use this function to produce the SQL to convert dates in the ISO8601
format to an integer age.
Function ISO8601Date2Age(strField, Optional strAlias As String) As String
Dim strAS As String
If Len(strAlias) > 0 Then
strAS = " AS "
End If
ISO8601Date2Age = "case when date(" & strField & ", '+' || " & _
"(strftime('%Y', 'now') - strftime('%Y', " & strField &
")) || " & _
"' years') <= date('now') then " & _
"strftime('%Y', 'now') - strftime('%Y', " & strField &
") " & _
"else " & _
"strftime('%Y', 'now') - strftime('%Y', " & strField &
") -1 End" & _
strAS & strAlias
End Function
You may not be coding in VB, but you will get the idea.
RBS
-Original Message-
From: Allan, Mark [mailto:[EMAIL PROTECTED]
Sent: 03 May 2007 11:57
To: sqlite-users@sqlite.org
Subject: [sqlite] Search on Age, from DOB column
Hi,
I need to be able offer the user the ability to search for patients in the
database based on age. i.e. age > 17 or age = 45 etc etc...
I only store the patient DOB in the database however, what is the SQL to
achive this? Can I subract todays date from the DOB and get the number of
years within an SQL string?
The patient table is similar to:-
Patients
{
INTEGER PrimaryKey;
TEXT Surname;
TEXT FirstName;
TIMESTAMP DOB;
...
...
...
}
Thanks in advance for your help.
Mark
DISCLAIMER:
This information and any attachments contained in this email message is
intended only for the use of the individual or entity to which it is
addressed and may contain information that is privileged, confidential, and
exempt from disclosure under applicable law. If the reader of this message
is not the intended recipient, or the employee or agent responsible for
delivering the message to the intended recipient, you are hereby notified
that any dissemination, distribution, forwarding, or copying of this
communication is strictly prohibited. If you have received this
communication in error, please notify the sender immediately by return
email, and delete the original message immediately.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Difference in these indices?
Ok, thanks. A bit more work then to deal with all the indices. Just one question; as I log nearly all my SQL statements to a SQLite table, will this be OK with the double quotes added? RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 28 March 2007 16:10 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Difference in these indices? RB Smissaert wrote: > Does this only apply to table and column names? > I will never use double quote characters in my identifier > names, so there should be no problem there. > > It applies to all the identifiers: table, column, index, trigger, database (using attach as id), transactions (if named which they seldom are), collation, and view names. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Difference in these indices?
Dennis,
OK, you convinced me and I think I will alter this.
Does this only apply to table and column names?
I will never use double quote characters in my identifier
names, so there should be no problem there.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 28 March 2007 15:41
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?
RB Smissaert wrote:
> As to quotes etc.
> As my code works fine as it is I probably will leave this as the double
> quotes look ugly and it will be a reasonably big job to alter all this.
> Did I get you right that the only benefit of doing create "table1" etc.
> is compatibility with running sqlite with SQLite.exe?
>
>
The benefit to using standard quoting for identifiers is portability.
Your table definitions will almost certainly be rejected by almost any
other database engine. Most don't support the same extended quoting
rules that sqlite has added for compatibility with files coming from
other sources.
If adding the escaped quotes to the SQL generation statements doesn't
work for you, then you could create a simple function that adds the
escaped quotes to your identifier variables. If you simplify the problem
and assume you will never use double quote characters in your identifier
names themselves this function is very simple;
Function Quote(id As String) As String
Quote = """" & id & """"
End Function
and your code becomes something like this.
strTable = "table1"
strColumn = "ID"
strSQL = "create " & Quote(strTable) & "(" & Quote(strColumn) & "
INTEGER PRIMARY KEY)"
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Difference in these indices?
Looks then that doing the table creation with INTEGER PRIMARY KEY
Is the way to go, but as always it will come down to a lot of testing.
As to quotes etc.
As my code works fine as it is I probably will leave this as the double
quotes look ugly and it will be a reasonably big job to alter all this.
Did I get you right that the only benefit of doing create "table1" etc.
is compatibility with running sqlite with SQLite.exe?
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 27 March 2007 23:41
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?
RB Smissaert wrote:
> Is it right that this won't affect the speed of any subsequent inserts or
> deletes?
>
Well inserts will be done in id order. If you have predefined ids
assigned by some outside source and specify them when you insert into
sqlite, it will have to insert at random location in the btree. This
will take longer than always appending at the end of the btree. If you
let sqlite assign the ids, or the ids are in order, then this is not an
issue. If you are always going to create the external index afterwards
anyway, it will also probably not make much difference (you would have
to test it each way).
> About the single quotes etc:
> This is VB code, so I can't do:
> Create table "table1"("ID" INTEGER PRIMARY KEY)
>
> I can do:
> Create table table1(ID INTEGER PRIMARY KEY)
>
> As the table and the columns are often variables it will be something
like:
>
> strTable = "table1"
> strColumn = "ID"
>
> strSQL = "create " & strTable & "(" & strColumn & " INTEGER PRIMARY KEY)"
>
>
>
VB and SQL both use the same technique of escaping quotes embedded in
strings using a pair of quotes back to back.
In VB
print "Test ""quoted"" strings."
will output
Test "quoted" strings.
You can do the same with the strings you are building to send to SQLite.
Using the following VB statement
strSQL = "create """ & strTable & """(""" & strColumn & """ INTEGER
PRIMARY KEY)"
will produce a strSQL that contains the string
create "table1"("ID" INTEGER PRIMARY KEY)
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Difference in these indices?
Thanks for clarifying that. I think all the data to be inserted in tables with an INTEGER PRIMARY KEY will be sorted on that key, but I will have to check as sometimes these tables can be big, say a few million rows. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 27 March 2007 23:34 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Difference in these indices? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Thanks for that. > So if I can then I should create the table with INTEGER PRIMARY KEY. > Is it right that this won't affect the speed of any subsequent inserts or > deletes? > That depends on the data. If you insert records in order of ascending integer primary key, then the inserts will be very fast. If you insert records where the integer primary key is randomized, inserts will be reasonably fast until the size of your table exceeds the size of your disk cache. Then each insert will need to do multiple reads and writes to disk as it tries to figure out where in your massive table is the right place to put the new record, and then make space for that new record. All this disk I/O will slow things down dramatically. Every table has an integer primary key whether you declare one or not. If you do not specify an integer primary key then one is created for you automatically named "ROWID" or "OID". If you do not specify a value for the integer primary key when inserting, a value is selected automatically. The value selected is one more than the largest existing integer primary key in that table. That means that if you do not specify integer primary keys, the keys choosen are in ascending order and inserts are very fast. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Difference in these indices?
Thanks for that.
So if I can then I should create the table with INTEGER PRIMARY KEY.
Is it right that this won't affect the speed of any subsequent inserts or
deletes?
About the single quotes etc:
This is VB code, so I can't do:
Create table "table1"("ID" INTEGER PRIMARY KEY)
I can do:
Create table table1(ID INTEGER PRIMARY KEY)
As the table and the columns are often variables it will be something like:
strTable = "table1"
strColumn = "ID"
strSQL = "create " & strTable & "(" & strColumn & " INTEGER PRIMARY KEY)"
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 27 March 2007 22:51
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?
RB Smissaert wrote:
> Is there any difference in an index created like this:
>
> Create table 'table1'([ID] INTEGER PRIMARY KEY)
>
> with this:
>
> Create table 'table1'([ID] INTEGER)
>
> Create unique index idx_table1_ID on table1(ID)
>
> I tended to use the first form, but as that can make subsequent table
> inserts or deletes slower I am now moving to the second form.
>
>
Yes there is. The first uses the key for the btree that stores the table
to hold the id. The second uses a second comlpetely independent btree to
store an index that holds records that contain the id number and the
rowid of the corresponding record in the table. The table itself
contains a rowid as the key of the table btree and the user id field.
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> Create table 'table1'([ID] INTEGER PRIMARY KEY);
sqlite> select * from sqlite_master;
table|table1|table1|2|CREATE TABLE 'table1'([ID] INTEGER PRIMARY KEY)
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> Create table 'table1'([ID] INTEGER);
sqlite> Create unique index idx_table1_ID on table1(ID);
sqlite> select * from sqlite_master;
table|table1|table1|2|CREATE TABLE 'table1'([ID] INTEGER)
index|idx_table1_ID|table1|3|CREATE UNIQUE INDEX idx_table1_ID on table1(ID)
The first version stores on integer for each record, and stores it in
the btree key. The second stores four integers for each record, two in
the table record and two in the index record. You are making your
database much larger for no reason.
Also, you are using literal strings (delimited with a single quote) for
your table names. This is not standard SQL and will not be portable. You
are also using square brackets to quote your column names. This is also
an SQLite and MS extension to standard SQL. You should quote identifiers
such as table and column names with double quotes.
Create table 'table1'([ID] INTEGER PRIMARY KEY)
should be:
Create table "table1"("ID" INTEGER PRIMARY KEY)
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Difference in these indices?
Is there any difference in an index created like this: Create table 'table1'([ID] INTEGER PRIMARY KEY) with this: Create table 'table1'([ID] INTEGER) Create unique index idx_table1_ID on table1(ID) I tended to use the first form, but as that can make subsequent table inserts or deletes slower I am now moving to the second form. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Any way to do this faster?
Had a good look at this now and doing: delete from tableB where not exists (select id from tableA where tableA.id = tableB.id) Is indeed quite a bit faster than doing: delete from tableB where id not in (select tableA.id from tableA) In my case about 3 times as fast. Looking at the query plan with EXPLAIN QUERY PLAN was a good tip! I think though that I gained more by looking more carefully when to put the index on the id column. I need the index, but not before the delete, so I gained a lot by creating the index after the delete. This meant less rows to index plus less work to be done with the delete. Probably there will be more places in my app where looking at the timing of the index creation will speed things up, so thanks again for the advice. RBS -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 26 March 2007 18:16 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Any way to do this faster? [EMAIL PROTECTED] wrote: > > I will need an index on that field later, but > I could drop it prior to the delete and create > a new one after. > > Don't do that. If you need the index, then leave it as is. > Thinking about it I am not sure in any case of the > value of an index after deletes on a table. > Is it usually better to re-index after deletes? > > Indexes are updated automatically as records are added and deleted from a table, that's why they add overhead if they are not serving some purpose. Your index will be correct after you delete the records from tableB. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Any way to do this faster?
Simplified I have the following situation: 2 tables, tableA and tableB both with an integer field, called ID, holding unique integer numbers in tableA and non-unique integer numbers in tableB. Both tables have an index on this field and for tableA this is an INTEGER PRIMARY KEY. Now I need to delete the rows in tableB where this number doesn't appear in the corresponding field in tableA. Currently I do this with this SQL: Delete from tableB where ID not in (select tableA.ID from tableA) When table tableB gets big (say some 10 rows) this will get a bit slow and I wonder if there is a better way to do this. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
I use SQLite as a data manipulator, not as a database. I get data from a server database, dump to SQLite, manipulate the data and finally dump to Excel. As this is reporting software speed is important, so I will go with the fastest method. RBS -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 23:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN RB Smissaert wrote: > Ok, now done some better testing and the method with CASE WHEN is indeed, as > expected a bit faster To me the lookup table method seems like exactly what a relational database is used for. The CASE WHEN would have to be dramatically faster, and in an area where timing was critical, for me to choose that way. If there were thousands of items, and changes were frequent, you wouldn't even consider CASE WHEN, would you? Remember: timing isn't important, except when it is. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
Ok, now done some better testing and the method with CASE WHEN is indeed, as expected a bit faster, I would say about a third. I have only tested this with some 8 different convert values, so maybe it will be different if there are much more different values to convert. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 17:17 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Question about speed of CASE WHEN Done some testing now and surprisingly, it seems the lookup method with a join to a lookup table is very slightly faster than the CASE WHEN construction. There isn't much in it though. Also tried the shorter CASE WHEN construction, like this: > case ENTRY_TYPE > when 9 then 'Issue > when 2 then 'Note' Etc But didn't get that to work as it always produced the first WHEN option. RBS -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 15:29 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN RB Smissaert uttered: > Looking at the fastest way to convert a field in a table and wonder if in > general an update with a CASE WHEN construction or an update with a join to > a lookup table is faster. My guess is that the CASE WHEN form will be faster for small number of possibilities. It compiles into a straight list of compare instructions. > > These conversions are of this type: > > UPDATE A3SQL77D_J > SET ENTRY_TYPE = (case > when ENTRY_TYPE = 9 then 'Issue > when ENTRY_TYPE = 2 then 'Note' > when ENTRY_TYPE = 1 then 'Encounter' > when ENTRY_TYPE = 8 then 'Authorisation' > when ENTRY_TYPE = 11 then 'Prescription' > when ENTRY_TYPE = 5 then 'Treatment' > when ENTRY_TYPE = 3 then 'Problem' > when ENTRY_TYPE = 13 then 'Discontinuation' > when ENTRY_TYPE = 6 then 'Reminder' > when ENTRY_TYPE = 14 then 'Adverse reaction' > when ENTRY_TYPE = -1 then 'Unknown' > when ENTRY_TYPE = 4 then 'Sub-problem' > when ENTRY_TYPE = 7 then 'Battery' > when ENTRY_TYPE = 10 then 'Return-Script' > else ENTRY_TYPE end) > > So, an integer value to be converted to a limited number of strings. > I could figure this out with some testing, but maybe there are some general > rules that apply to this. Testing is probably the best way. However, the difference in speed may not be significant given the increased maintainability of the table lookup based solution. > > Also, would the order of the CASE WHEN options make a difference, > speed-wise, so would it be faster to have the WHEN options in decreasing > order of frequency? The sequence of code generated compares the cases in the order written. So the common cases should go first. > > RBS > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
