[sqlite] ordering result sets
order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime, ''),f.lastdate This worked fine re making sure that non-null nexttimes come BEFORE null nexttimes. How would I extend this so that AFTER non-null nexttimes I get NON-NULL lasttimes and then... null nexttimes and lastimes in any order Also can I ask why you are ordering by nextime is null/'' at the beginning... I don't really understand this Regards Dean ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to move around records in sqlite as the way as in CDaoRecordset of MFC??
hi, i use sqlite in my project. it is good because of its capacity bigger than ACCESS. i used CDaoRecordset of MFC to handle records in ACCESS, whick is convenient to me move around records in table and edit certain record. according to my habit, i wish sqlite has some kind of functions like, move(int) to get to cerctain record, getbookmark(...) to the record marked record and setbookmark() to mark the record, moveprov() to move one record back and movenext() to the record forward, IsEOF and IsBOF to know we are not out of table domain. all in one word, not only move forwardly but also backwardly through records in table. i know sqlite3_get_table( ) , sqlite3_step() function and struct sqlite3_stmt archive some goal mentioned above, but not all!! how ? thanks!!! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to move around records in sqlite as the way as in CDaoRecordset of MFC??
On 18 Dec 2012, at 3:03pm, tigeryth doityth...@163.com wrote: i use sqlite in my project. it is good because of its capacity bigger than ACCESS. i used CDaoRecordset of MFC to handle records in ACCESS, whick is convenient to me move around records in table and edit certain record. according to my habit, i wish sqlite has some kind of functions like, move(int) to get to cerctain record, getbookmark(...) to the record marked record and setbookmark() to mark the record, moveprov() to move one record back and movenext() to the record forward, IsEOF and IsBOF to know we are not out of table domain. all in one word, not only move forwardly but also backwardly through records in table. i know sqlite3_get_table( ) , sqlite3_step() function and struct sqlite3_stmt archive some goal mentioned above, but not all!! Every table has a secret column which can be addressed as id or rowid. There's an index on this column, so you can find values very quickly. You can use this column to uniquely identify a row in the table. Move forward by looking for the next bigger rowid value. Move backwards by looking for the next smaller rowid value. So you can write your own 'movenext()' code that does something like SELECT rowid FROM myTable WHERE rowid [current_rowid] ORDER BY rowid LIMIT 1 You might want to read http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor and get back to us if you still have questions. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to move around records in sqlite as the way as in CDaoRecordset of MFC??
tigeryth wrote: i used CDaoRecordset of MFC to handle records in ACCESS, whick is convenient to me move around records in table and edit certain record. according to my habit, i wish sqlite has some kind of functions like, move(int) to get to cerctain record, getbookmark(...) to the record marked record and setbookmark() to mark the record, moveprov() to move one record back and movenext() to the record forward, IsEOF and IsBOF to know we are not out of table domain. all in one word, not only move forwardly but also backwardly through records in table. i know sqlite3_get_table( ) , sqlite3_step() function and struct sqlite3_stmt archive some goal mentioned above, but not all!! how ? SQLite does not provide any equivalent to a backward-moving cursor. If you insist on using the SQLite C API, (when it appears you are using a library offering a putatively higher level of abstraction), you will need to look at the 'limit' and 'offset' qualifiers for 'select' queries. These can be inefficient for otherwise large datasets, so be careful and consider incorporating similar result subsetting criteria into the 'where' clause. Now, some unsolicited advice: While MFC's database interface was useful in its time, that time is past except for old projects in maintenance. You should be using the ADO.NET SQLite adapter on the .NET platform. (See http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki .) I won't elaborate on this here, (as it is off-topic), but I doubt any sane developer could regret making that transition. -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ordering result sets
e-mail mgbg25171 mgbg25...@blueyonder.co.uk wrote: order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime, ''),f.lastdate This worked fine re making sure that non-null nexttimes come BEFORE null nexttimes. How would I extend this so that AFTER non-null nexttimes I get NON-NULL lasttimes and then... null nexttimes and lastimes in any order order by (case when nexttime is not null then 0 when lasttime is not null then 1 else 2 end), nexttime, lasttime Also can I ask why you are ordering by nextime is null/'' at the beginning... Because that's what you asked for. Allow me to quote: order results firstly by earlest *non-null/empty string* next time (emphasis mine). You do realize that NULL and empty string are two distinct values, right? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suggested Improvement of Date Time Function
Suggesting addition of two new modifiers to SQLITE Date Time functions.
Add new modifier(14):javams and modifier(15):javanano similar to
unixepoch modifier but extended to support milliseconds and
nanoseconds.
The javams modifier (14) only works if it immediately follows a
timestring in the D format. This modifier causes the
D to be interpreted not as a Julian day number as it
normally would be, but as JAVA Millisecond Time - the number of
milliseconds since 1970. If the javams modifier does not follow a
timestring of the form D which expresses the number of
milliseconds since 1970 or if other modifiers separate the java
modifier from prior D then the behavior is undefined. Due
to 13 digit precision limitation the javams modifier only works for
dates between 1653-02-10 06:13:21.001 and 2286-11-20 17:46:39.999
(java times of -9 through 9).
The javanano modifier (15) only works if it immediately follows a
timestring in the DDD format. This modifier causes the
DDD to be interpreted not as a Julian day number as it
normally would be, but as JAVA Nano Second Time - the number of
NanoSeconds since 1970. If the javanano modifier does not follow a
timestring of the form DDD which expresses the number
of nanoiseconds since 1970 or if other modifiers separate the java
modifier from prior DDD then the behavior is
undefined. Due to precision limitations imposed by the implementations
use of 64-bit integers the javanano modifier only works for dates
between 1677-09-21 00:12:44.145224192 and 2262-04-11
23:47:16.854775807 (javanano times of -9223372036854775808 through
+9223372036854775807).
These modifier additions will allow easier and seamless support for
storing high resolution times in the database and enhance
functionality for manipulation.
Presently we are able to store and use javams format via concatenation:
javams format
strftime('%s','now')||substr(strftime('%f','now'),-3,3)
strftime('%Y-%m-%d
%H:%M%S',field/1000,'unixepoch')||substr(strftime('%f','field),-3,3)
We do not use javanano format presently but are merely suggesting it
as a possibility for completeness.
javanano format
strftime('%s','now')||substr(strftime('%f','now'),-3,3)||'00'
//'now' limited to millisecond precision
strftime('%Y-%m-%d
%H:%M%S',field/1000,'unixepoch')||substr(strftime('%f','field),-9,9)
JavaMS support is a higher priority than JavaNano.
Any Thoughts or Input on this matter?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ordering result sets
Thank Igor Again...your solution works a treat... Re my confusion...please contrast these two which both work i.e. order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime, ''), f.lastdate vs order by (case when nexttime is not null then 0 when lasttime is not null then 1 else 2 end), nexttime, lasttime You are of course correct re my requirement i.e. Allow me to quote: order results firstly by earlest *non-null/empty string* next time Given this I fully understand Ordering by that which I require i.e. non-null...non empty string and the latter of the above does precisely that i.e. when nexttime is not null then 0... and... when lasttime is not null BY CONTRAST the first query SEEMS TO MY NAIVE EYE to contradict my requirement of not null i.e. ORDER BY f.nexttime IS NULL I HOPE THIS EXPLAINS MY CONFUSION AS STATED BOTH WORK IT'S JUST THAT THE FIRST ONE SEEMS COUNTER INTUITIVE AND I'D LIKE TO UNDERSTAND THIS BTW NULL = CHR$(0) vs '' = '' YES??? On 19 December 2012 13:42, Igor Tandetnik i...@tandetnik.org wrote: e-mail mgbg25171 mgbg25...@blueyonder.co.uk wrote: order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime, ''),f.lastdate This worked fine re making sure that non-null nexttimes come BEFORE null nexttimes. How would I extend this so that AFTER non-null nexttimes I get NON-NULL lasttimes and then... null nexttimes and lastimes in any order order by (case when nexttime is not null then 0 when lasttime is not null then 1 else 2 end), nexttime, lasttime Also can I ask why you are ordering by nextime is null/'' at the beginning... Because that's what you asked for. Allow me to quote: order results firstly by earlest *non-null/empty string* next time (emphasis mine). You do realize that NULL and empty string are two distinct values, right? -- 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] complex update
Thank you for the principal. I had to rewrite a little since I only wanted to affect the rows that were using the max entry. Adam On Tue, Dec 18, 2012 at 12:58 PM, Igor Tandetnik i...@tandetnik.org wrote: On 12/18/2012 12:27 PM, Adam DeVita wrote: There is a table products where has a location id. Unfortunately duplicate dictionary names got added to list of locations products haslocationid and a bunch of other stuff I can easily get the max (bad) and min (good) location ids associated with each name (I know I should have made the name field UNIQUE... mistakes were made years ago) how do I write an update that essentially says update products set locationid = good where locationid = bad , but do it for each good bad pair ? update Products set locationid = ( select min(locationid) from Locations where name = (select name from Locations L where L.locationid = Products.locationId) ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggested Improvement of Date Time Function
Somebody claiming to be IQ Support Team wrote: Suggesting addition of two new modifiers to SQLITE Date Time functions. Add new modifier(14):javams and modifier(15):javanano similar to unixepoch modifier but extended to support milliseconds and nanoseconds. This seems like an application-level feature. If it were to go into SQLite, then I have a few questions: 1. Should the nanoSecond and milliSecond resolution time functions take into account the gradual slowing of Earth's rotation as reflected in occasional leap-Seconds? 2. Does anybody actually keep times with such high resolution where they are simultaneously concerned with converting to day-of-month, month-of-year and the like? These questions arise from observing that SQLite's time functions are useful mainly for their conversions between time-into-era numbers and more people-friendly forms. The only reason I see to stack this higher resolution functionality onto the existing functionality is to achieve some efficiency that would be less practical with the implementation split between SQLite and application code. However, since anybody can #define SQLITE_OMIT_DATETIME_FUNCS during a build, and use their more specialized (or accurate) functions instead, this efficiency concern seems misplaced. If the SQLite developers were to undertake this enhancement, it should certainly come with another #define to chop out the extra code it would entail. (SQLITE_APPROXIMATE_TIME?) Since you (Mr. Team) have apparently already created this much more accurate set of conversions, perhaps you could offer them as a plug-in replacement for the readily omitted functions. -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggested Improvement of Date Time Function (Larry Brasfield)
On Wed, Dec 19, 2012 at 12:00 PM, sqlite-users-requ...@sqlite.org wrote: Re: Suggested Improvement of Date Time Function (Larry Brasfield) We currently store timestamps in this javams integer format which allows millisecond precision in a fixed 13 character/digit space. Our usage may not be too uncommon as we use this format for storing and graphing financial time series data. I believe FIX 40 messages are millisecond precise but some HFT data streams have microsecond resolution. This format feeds some of our java graphing libraries nicely. There are some advantages in performing integer based time manipulations and calculations over the string conversions. Currently we experience a small disconnect between unixepoch (second precision) and the built in string based date time functions that have ms or better precision. I believe it may be of general interest to at minimum have some integer time supported format that provides millisecond precision and matches java time format. Currently we just concatenate the ms onto the unixepoch time... Don't know if this is the best practice or where it may break other things but it works for our purposes. Our adaptation changes the upper and lower boundaries for sqlites unixepoch format as we restrict to 10 digits. The SQL code used to query and present data is kludgy and I'm certain we're not the only ones band-aiding these issues. Hence we opened this topic for discussion... If there is a better way to do this we are all ears. One of the performance issues we run into is grouping time series data. Ideally we would like the ability to group by timestamp/6 for 1 minute resolution, timestamp/1000 for 1 second or change to any frequency without the query creating a btree for the grouping. When we store the timestamp/6 value in an indexed column the groupings use the index and performs extremely well. Any help on creating dynamic grouped time series without creating btrees for grouping or storing the time calculation in an indexed column... if possible would be appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ordering result sets
On 12/19/2012 10:07 AM, e-mail mgbg25171 wrote: Given this I fully understand Ordering by that which I require i.e. non-null...non empty string and the latter of the above does precisely that i.e. when nexttime is not null then 0... and... when lasttime is not null BY CONTRAST the first query SEEMS TO MY NAIVE EYE to contradict my requirement of not null i.e. ORDER BY f.nexttime IS NULL The expression (f.nexttime IS NULL) evaluates to 0 (which represents false) when f.nexttime is in fact *not* NULL, and to 1 (true) when it is in fact NULL. In other words, it's a shorthand for case when f.nexttime IS NULL then 1 else 0 end or equivalently case when f.nexttime IS NOT NULL then 0 else 1 end BTW NULL = CHR$(0) vs '' = '' YES??? No. NULL is NULL - it doesn't compare equal to anything, not even to itself. For details, see http://en.wikipedia.org/wiki/Null_(SQL) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ordering result sets
Igor case when f.nexttime IS NOT NULL then 0 else 1 end explains it very well and I see that your alternative is indeed an elegant shortcut Additionally... thank you for putting me straight re NULL in SQL I didn;t appreciate that Your help is very much appreciated Dean On 19 December 2012 19:19, Igor Tandetnik i...@tandetnik.org wrote: case when f.nexttime IS NOT NULL then 0 else 1 end ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Version 3.7.15.1
SQLite version 3.7.15.1, a patch release, is now available on the SQLite website: http://www.sqlite.org/ This patch release fixes a single bug the managed to sneak into the 3.7.15 release from last week. Two lines of code changed and one assert() was added: http://www.sqlite.org/src/fdiff?v1=53b991af50dab230v2=74d72b1613aac386#chunk1 The complete patch includes the change above, and some new test cases, and the version number and configure script were updated. A description of the bug that was fixed is here: http://www.sqlite.org/src/info/a7b7803e8d1e869 The bug causes a NULL pointer dereference given some unusual but perfectly legal SQL. The bug is not data dependent and is thus not a security vulnerability (since if an attacker can inject arbitrary SQL into your application, then you have already been compromised). If you application does not use the unusual SQL construct necessary to tickle this bug (and most applications don't) then you are perfectly safe staying with whatever prior version of SQLite you are currently using. Nevertheless, upgrading to 3.7.15.1 is recommended. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) lhask...@bloomberg.net wrote: My suggestion would be to have check_integrity command verify referential integrity as well only if it's executed while the foreign key enforcement is enabled on the connection. The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code that is destined to become 3.7.16) has a new pragma: PRAGMA foreign_key_check; PRAGMA foreign_key_check(TABLE); The second from checks all of the REFERENCES clauses in TABLE. The first form checks the keys on all tables in the database. The result of the pragma is a table, with one row per mismatched key. The row contains the name of the child table, the rowid of the child table, the name of the parent table, and the foreign key index which is an integer that describes the foreign key in PRAGMA foreign_key_list(CHILD). If the foreign_key_check pragma returns an empty set, that means that all of the keys are correct. PRAGMA foreign_key_check works regardless of whether or not foreign keys are currently enabled or disabled. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, 19 Dec 2012 21:10:28 -0500, Richard Hipp d...@sqlite.org wrote: The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code that is destined to become 3.7.16) has a new pragma: PRAGMA foreign_key_check; PRAGMA foreign_key_check(TABLE); The second from checks all of the REFERENCES clauses in TABLE. The first form checks the keys on all tables in the database. [] PRAGMA foreign_key_check works regardless of whether or not foreign keys are currently enabled or disabled. Perfect, thanks! -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Version 3.7.15.1
Thank you for the release! I can't find sqlite-shell-linux-x86-3071501.zip and sqlite-doc-3071501.zip. On 12/19/2012 10:24 PM, D. Richard Hipp wrote: SQLite version 3.7.15.1, a patch release, is now available on the SQLite website: http://www.sqlite.org/ This patch release fixes a single bug the managed to sneak into the 3.7.15 release from last week. Two lines of code changed and one assert() was added: http://www.sqlite.org/src/fdiff?v1=53b991af50dab230v2=74d72b1613aac386#chunk1 The complete patch includes the change above, and some new test cases, and the version number and configure script were updated. A description of the bug that was fixed is here: http://www.sqlite.org/src/info/a7b7803e8d1e869 The bug causes a NULL pointer dereference given some unusual but perfectly legal SQL. The bug is not data dependent and is thus not a security vulnerability (since if an attacker can inject arbitrary SQL into your application, then you have already been compromised). If you application does not use the unusual SQL construct necessary to tickle this bug (and most applications don't) then you are perfectly safe staying with whatever prior version of SQLite you are currently using. Nevertheless, upgrading to 3.7.15.1 is recommended. 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
