Re: [sqlite] Make sqlite3 database searchable on Mac OS X
On 11 Feb 2020, at 4:10am, Peng Yu wrote: > It seems that sqlite3 databases are not searchable by Spotlight on Mac OS X. > Is there a way to make them searchable? Thanks. A long time ago I wrote a SQLite indexer for … I think it was Sherlock back then. It went through all text fields (ignoring numbers and BLOB) and indexed them. It worked fine. But every time even a single field in a big SQLite database changed, my Mac slowed to a crawl, because the entire file got reindexed. That was how it worked. Hmm. I see Core Spotlight allows entity registering. You'd add hooks to SQLite so that every time a row was added, updated or deleted another routine was called, which told Spotlight about the new (or vanished) entity. Problem is, it would work only for apps which used your own library with the extra programming in. Without that, you're back to reindexing the entire database file any time anything in it changes. Whichever way you did it, it would slow down SQLite a lot. Sorry. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Make sqlite3 database searchable on Mac OS X
Hi, It seems that sqlite3 databases are not searchable by Spotlight on Mac OS X. Is there a way to make them searchable? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to group this?
On 10 Feb 2020, at 10:41pm, Wolfgang Enzinger wrote: > Am Mon, 10 Feb 2020 01:42:14 + schrieb Simon Slavin: > >> On 10 Feb 2020, at 1:25am, no...@null.net wrote: >> >> create two VIEWs, [...]. Index both VIEWs on (id, date), > > I don't think that creating an index on a view actually works, does it? You're right. What was I thinking ? Maybe I've used another implementation of SQL that it does work on. Thanks for picking me up on it. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On Monday, 10 February, 2020 14:36, Simon Slavin wrote: >Does this problem affect unnumbered indexes too ? In other words if I >have >(?,?,?,?,?) >and bind to the fifth one using the index do I have the same problems as >having >(?1,?2,?3,?4,?5) >and bind to the fifth one using its number ? What particular problem are you referring to? When you use a "parameter" in an SQL statement, then during prepare time the total number of unique parameters (and the highest used index) is recorded. An array must be allocated attached to the "statement" containing enough entries to allow all these parameters to be used. So if you use a parameter "?437" then the array size will be allocated of sufficient size to hold 437 parameters. In addition, whenever a "named parameter" is used (?N, :name, @name, $name) then the parameter name is entered into yet another data structure which records which "index position" corresponds to that name. This is so that the sqlite3_bind_parameter_name and sqlite3_bind_parameter_index can map between the name and the number. So if you use (?,?,?,?,?) then an array of 5 parameters will be required, and no name<->index mapping VList will be created. However, if you use: (?1,?2,?3,?4,?5) then an array of 5 parameters will be required, and a name<->index mapping VList will be created for all 5 of the named parameters. If you use: (?,?,?,?5,?4) then an array of 5 parameters will be required, and a name<->index mapping VList will be created for the 2 named parameters (?5 and ?4). If you were to use this: (?,:t,?5,?2) then an array of 5 parameters will be required (the max index used is 5, even though the parameter at index 4 is not referenced) and the name<->index mapping VList will contain ":t"<->2, "?5"<->5. The "?2" name will not be recorded since parameter index 2 already has a name. Perhaps in the case of ?N parameters the extra step of recording the name could be skipped since all "not otherwise named" ?N parameters will always have name ?N and index N. But at the moment the name is recorded in the VList for ?N parameters if no name is registered for the parameter at index N. There is probably a reason for this though I don't know what it is. The generated VDBE program will always refer to parameters by their index and not by their names. The name<->index VList table is kept attached to the statement for use by the sqlite3_bind_parameter_name and sqlite3_bind_parameter_index API functions. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to group this?
Am Mon, 10 Feb 2020 01:42:14 + schrieb Simon Slavin: > On 10 Feb 2020, at 1:25am, no...@null.net wrote: > > create two VIEWs, [...]. Index both VIEWs on (id, date), I don't think that creating an index on a view actually works, does it? Wolfgang ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
Does this problem affect unnumbered indexes too ? In other words if I have (?,?,?,?,?) and bind to the fifth one using the index do I have the same problems as having (?1,?2,?3,?4,?5) and bind to the fifth one using its number ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On 2/10/20, Digital Dog wrote: > Maybe they should be treated as a > dictionary/hashtable/linked list or similar? > Parameter look-ups are on the critical path. How much performance are you willing to give up in order to have parameters with larger numbers? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On Mon, Feb 10, 2020 at 8:27 PM Richard Hipp wrote: > On 2/10/20, Digital Dog wrote: > > > > Nobody bothered to actually show the downside of increasing this value to > > e.g. 10 thousands but everybody immediately proceeded to grumble. > > What is the justifiable rationale to not change the default? > > The maximum number of variables used to be unlimited (or, at least, > only limited by the ability of a 32-bit integer to count them). But > that was shown to facilitate a DOS attack. Anyone who could inject > SQL could do "SELECT ?2147483647;". This would cause SQLite to > allocate an array of 2147483648 elements, each of which is 72 bytes in > size, in which to store all of the parameters, potentially using up > all available memory in the process. Even ?32767 uses a big chunk of > heap memory - more than embedded systems people are comfortable giving > up - especially if the attacker can trick the system into creating > multiple prepared statements with ?32767 in them. The allocated array > lives for the life of the prepared statement. > Thanks, that makes a lot of sense. However maybe this needs rearchitecting, so that only used keys are allocated, not all up to the one specified in "SELECT ?2147483647".In this case I think SQLite should only allocate space for this single parameter. Maybe they should be treated as a dictionary/hashtable/linked list or similar? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On 2/10/20, Digital Dog wrote: > > Nobody bothered to actually show the downside of increasing this value to > e.g. 10 thousands but everybody immediately proceeded to grumble. > What is the justifiable rationale to not change the default? The maximum number of variables used to be unlimited (or, at least, only limited by the ability of a 32-bit integer to count them). But that was shown to facilitate a DOS attack. Anyone who could inject SQL could do "SELECT ?2147483647;". This would cause SQLite to allocate an array of 2147483648 elements, each of which is 72 bytes in size, in which to store all of the parameters, potentially using up all available memory in the process. Even ?32767 uses a big chunk of heap memory - more than embedded systems people are comfortable giving up - especially if the attacker can trick the system into creating multiple prepared statements with ?32767 in them. The allocated array lives for the life of the prepared statement. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation error
The page https://sqlite.org/src/doc/begin-concurrent/doc/begin_concurrent.md contains raw html: INSERT INTO t1(b) VALUES(blob-value>); -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
Huh so the typical ranting for a valid use case has happened. > Another alternative is to construct the command as a string. begging for SQL injection. Thanks, no. > all the suggestions and examples with temporary tables and that's what you call easy for the programmer? Nobody bothered to actually show the downside of increasing this value to e.g. 10 thousands but everybody immediately proceeded to grumble. What is the justifiable rationale to not change the default? And while I'm on the line, I would like to ask for another change: to enable GENERATE_SERIES by default in command line shell builds. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to group this?
Sorry, I made a typo. The windows should be ORDER BY ROWID, not ORDER BY ID. Jen On Mon, Feb 10, 2020 at 09:19:59AM -0700, Jen Pollock wrote: > I think the following works: > > SELECT s.ID 'ID', s.Date Date, Systolic, Diastolic > FROM > (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Systolic, row_number() OVER > id_date r > FROM pressure > WHERE TERM = 'Systolic' > WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) > ) s > JOIN > (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Diastolic, row_number() OVER > id_date r > FROM pressure > WHERE TERM = 'Diastolic' > WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) > ) d > ON s.ID = d.ID AND s.Date = d.Date AND s.r = d.r > ORDER BY s.ID, s.Date; > > You will need a fairly recent version of SQLite, older versions don't > have window functions. And I have assumed that there is a Diastolic > value for every Systolic value and vice versa. > > Jen > > On Mon, Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote: > > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > > > > 1308 15/Mar/2013 Systolic 127 701559 > > 1308 15/Mar/2013 Diastolic 81 701568 > > 1308 27/Jun/2013 Systolic 132 701562 > > 1308 27/Jun/2013 Systolic 141 701563 > > 1308 27/Jun/2013 Systolic 143 701564 > > 1308 27/Jun/2013 Diastolic 82 701571 > > 1308 27/Jun/2013 Diastolic 85 701572 > > 1308 27/Jun/2013 Diastolic 94 701573 > > 278975701 08/Mar/2018 Systolic 136 1583551 > > 278975701 08/Mar/2018 Diastolic 99 1583591 > > 278975701 04/Apr/2018 Systolic 119 1583552 > > 278975701 04/Apr/2018 Systolic 124 1583553 > > 278975701 04/Apr/2018 Systolic 130 1583554 > > 278975701 04/Apr/2018 Diastolic 74 1583592 > > 278975701 04/Apr/2018 Diastolic 75 1583593 > > 278975701 04/Apr/2018 Diastolic 85 1583594 > > > > These are systolic and diastolic blood pressures for 2 people with the ID's > > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc. > > Systolic and diastolic values are a pair and should be grouped in one row. > > This is no problem if there is only one pair for one date, but sometimes > > there multiple pairs per date. > > The pairing should be based on the rowed if there are multiple pairs by > > date, so for ID 1308 > > I should get: > > > > 127/81 > > 132/82 > > 141/85 > > 143/94 > > > > What should be the SQL to group like this? > > > > RBS > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to group this?
I think the following works: SELECT s.ID 'ID', s.Date Date, Systolic, Diastolic FROM (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Systolic, row_number() OVER id_date r FROM pressure WHERE TERM = 'Systolic' WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) ) s JOIN (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Diastolic, row_number() OVER id_date r FROM pressure WHERE TERM = 'Diastolic' WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) ) d ON s.ID = d.ID AND s.Date = d.Date AND s.r = d.r ORDER BY s.ID, s.Date; You will need a fairly recent version of SQLite, older versions don't have window functions. And I have assumed that there is a Diastolic value for every Systolic value and vice versa. Jen On Mon, Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote: > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > > 1308 15/Mar/2013 Systolic 127 701559 > 1308 15/Mar/2013 Diastolic 81 701568 > 1308 27/Jun/2013 Systolic 132 701562 > 1308 27/Jun/2013 Systolic 141 701563 > 1308 27/Jun/2013 Systolic 143 701564 > 1308 27/Jun/2013 Diastolic 82 701571 > 1308 27/Jun/2013 Diastolic 85 701572 > 1308 27/Jun/2013 Diastolic 94 701573 > 278975701 08/Mar/2018 Systolic 136 1583551 > 278975701 08/Mar/2018 Diastolic 99 1583591 > 278975701 04/Apr/2018 Systolic 119 1583552 > 278975701 04/Apr/2018 Systolic 124 1583553 > 278975701 04/Apr/2018 Systolic 130 1583554 > 278975701 04/Apr/2018 Diastolic 74 1583592 > 278975701 04/Apr/2018 Diastolic 75 1583593 > 278975701 04/Apr/2018 Diastolic 85 1583594 > > These are systolic and diastolic blood pressures for 2 people with the ID's > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc. > Systolic and diastolic values are a pair and should be grouped in one row. > This is no problem if there is only one pair for one date, but sometimes > there multiple pairs per date. > The pairing should be based on the rowed if there are multiple pairs by > date, so for ID 1308 > I should get: > > 127/81 > 132/82 > 141/85 > 143/94 > > What should be the SQL to group like this? > > RBS > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to group this?
On 2/9/20 11:44 PM, Rowan Worth wrote: On Mon, 10 Feb 2020 at 11:12, Richard Damon wrote: On 2/9/20 7:24 PM, Bart Smissaert wrote: ID ENTRY_DATE TERM NUMERIC_VALUE ROWID 1308 15/Mar/2013 Systolic 127 701559 1308 15/Mar/2013 Diastolic 81 701568 1308 27/Jun/2013 Systolic 132 701562 1308 27/Jun/2013 Systolic 141 701563 1308 27/Jun/2013 Systolic 143 701564 1308 27/Jun/2013 Diastolic 82 701571 1308 27/Jun/2013 Diastolic 85 701572 1308 27/Jun/2013 Diastolic 94 701573 278975701 08/Mar/2018 Systolic 136 1583551 278975701 08/Mar/2018 Diastolic 99 1583591 278975701 04/Apr/2018 Systolic 119 1583552 278975701 04/Apr/2018 Systolic 124 1583553 278975701 04/Apr/2018 Systolic 130 1583554 278975701 04/Apr/2018 Diastolic 74 1583592 278975701 04/Apr/2018 Diastolic 75 1583593 278975701 04/Apr/2018 Diastolic 85 1583594 These are systolic and diastolic blood pressures for 2 people with the ID's 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc. Systolic and diastolic values are a pair and should be grouped in one row. This is no problem if there is only one pair for one date, but sometimes there multiple pairs per date. The pairing should be based on the rowed if there are multiple pairs by date, so for ID 1308 I should get: 127/81 132/82 141/85 143/94 What should be the SQL to group like this? RBS To be honest, I think the problem is fundamentally badly designed. You say pair the two readings by ROWID, but they of course don't have the same ROWID, but you seem to be saying to pair them sorted by ROWID (1st to 1st, 2nd to 2nd, etc). The fundamental problem is what if there isn't the same number of each? You may say that you know that there will always be the same number, but there is no constraint that forces this, so any general program is going to have to deal with the possibility (and at least throw out an error when it sees that). Yeah, it would have been easier to group the readings at write time - eg. via another column storing the time of day or the "nth reading of the day". You could still add the latter, post-hoc. Note that ROWID is not persistent -- see quirk #6 here: https://www.sqlite.org/rowidtable.html I would expect that VACUUM's renumbering happens to maintain the row order, but I doubt it's guaranteed. If you do have an INTEGER PRIMARY KEY it's better to refer to that directly. -Rowan I think Vacuum maintains Row order but not RowID, unless it has been aliased to a user defined column. Yes, one solution would be to add another column that can be used either alone or paired with other columns to allow a join to find the pair of readings. So modified, the writing application could be updated to write the same value into that column for the two readings, and/or a fix-it app could be run to add it to existing data. SQL itself is unlikely to have a simple way to do this, as the problem isn't solvable in general, which is what the SQL solution would be attempting, unless the SQL solution was basically doing the fixup operation, finding the duplicate ID/Date rows and adding the pairing value. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to group this?
I fully agree with you, but I sofar I have no control over this data, I have it like I showed. As far as I can see there always will be a secondary value, but as you say I can't be sure. All this has to do with changing our clinical coding system from Read codes to Snomed. In the old setup there was the concept of a secondary value (systolic >> diastolic), but it seems in this particular case that is missing. I get the data by running searches (not SQL) on a clinical database and I have no control over this database. I will see if I can get better data with a different search, to do with blood pressure values. RBS On Mon, Feb 10, 2020 at 3:12 AM Richard Damon wrote: > On 2/9/20 7:24 PM, Bart Smissaert wrote: > > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > > > > 1308 15/Mar/2013 Systolic 127 701559 > > 1308 15/Mar/2013 Diastolic 81 701568 > > 1308 27/Jun/2013 Systolic 132 701562 > > 1308 27/Jun/2013 Systolic 141 701563 > > 1308 27/Jun/2013 Systolic 143 701564 > > 1308 27/Jun/2013 Diastolic 82 701571 > > 1308 27/Jun/2013 Diastolic 85 701572 > > 1308 27/Jun/2013 Diastolic 94 701573 > > 278975701 08/Mar/2018 Systolic 136 1583551 > > 278975701 08/Mar/2018 Diastolic 99 1583591 > > 278975701 04/Apr/2018 Systolic 119 1583552 > > 278975701 04/Apr/2018 Systolic 124 1583553 > > 278975701 04/Apr/2018 Systolic 130 1583554 > > 278975701 04/Apr/2018 Diastolic 74 1583592 > > 278975701 04/Apr/2018 Diastolic 75 1583593 > > 278975701 04/Apr/2018 Diastolic 85 1583594 > > > > These are systolic and diastolic blood pressures for 2 people with the > ID's > > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc. > > Systolic and diastolic values are a pair and should be grouped in one > row. > > This is no problem if there is only one pair for one date, but sometimes > > there multiple pairs per date. > > The pairing should be based on the rowed if there are multiple pairs by > > date, so for ID 1308 > > I should get: > > > > 127/81 > > 132/82 > > 141/85 > > 143/94 > > > > What should be the SQL to group like this? > > > > RBS > > To be honest, I think the problem is fundamentally badly designed. You > say pair the two readings by ROWID, but they of course don't have the > same ROWID, but you seem to be saying to pair them sorted by ROWID (1st > to 1st, 2nd to 2nd, etc). The fundamental problem is what if there isn't > the same number of each? You may say that you know that there will > always be the same number, but there is no constraint that forces this, > so any general program is going to have to deal with the possibility > (and at least throw out an error when it sees that). > > > -- > Richard Damon > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
