Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?
On 3/6/2020 8:52 PM, Xinyue Chen wrote: If I change IS NOT FALSE to IS TRUE, the results will be different. NULL IS TRUE is always false. NULL IS NOT FALSE is always true. So it's not surprising that the results are different. SQL uses trivalent logic. NULL is neither FALSE nor TRUE. I assume they should perform in the same way? You assume incorrectly. if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should also be always true. False. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unexpected sqlite_busy behaviour within transactions
On 2/22/2020 7:50 AM, softw...@quantentunnel.de wrote: A busy_timout is honored by write commands outside of transactions and by single write commands inside transactions, but not if preceded by a read command. I did not find this behaviour in the documentation, thus it might be a bug. It's documented in the description of sqlite3_busy_handler: https://sqlite.org/c3ref/busy_handler.html . Look for a paragraph mentioning "deadlock". -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Either a bug or I don't understand SQL update
"M" refers to a column named M, specifically, citytax.m . Similarly, "G" is a reference to citytax.g. String literals in SQL are enclosed in single quotes, as in 'M' and 'G' ; double quotes are used to enclose names (helpful for names that contain spaces or other characters not allowed in identifiers). SQLite has a feature for backward compatibility, whereby a string enclosed in double quotes is taken as a string literal if it doesn't match any name in scope. That's why "P" and "R" work, but "M" and "G" don't. Igor Tandetnik On 1/29/2020 6:11 PM, Paul Ausbeck wrote: I've been using sqlite for some time but haven't used SQL update until recently. On my first real use, I've encountered a problem that I can't understand at all. To pare this down, I have two tables, citytax and taxitems2, and I've attached a database with just these two tables, total size ~12Kbytes. I perform the following update using the sqlite3 command line interface: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and taxitems2.taxrate = "M"); The result is that the salesm column is set to NULL for all citytax rows. However, I would expect that rows 9 and 22 would be non-NULL. I get the the same all-NULL result for a taxrate of "G" as well, though I would expect that rows 14, 19, and 58 would be non-NULL. The strangest part is that if I specify taxrates of "P", "R", or "", I get the result that I expect. I've included a listing of the taxitems2 table below. As one can see, there are rows where the second column, taxrate, is all of "", "P", "R", "G", and "M". The last column is the amount column, non-null for all rows. I must admit that I don't understand what is going on here. Perhaps even stranger is if I extract the select from the update and give it an appropriate row number, I get the expected non-NULL result: sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and taxitems2.taxrate = "M"; 1176.72 I've included the contents of the taxitems2 table as well as the schema for tables taxitems2 and citytax below. One the one hand, this seems almost certainly to be a bug, but on the other, it is so basic, that I can't believe that I'm the first to encounter it. Any assistance that anyone can provide would of course be much appreciated. I'm using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both environments behave the same in this regard. As I was writing I had yet another test idea: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 77); /* taxrate = "M" */ and update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 71); /* taxrate = "G" */ Both of these updates perform as expected. Regards, Paul Ausbeck sqlite> select * from taxitems2; WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5 SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0 USX,P,58,14,1,0,0,0,0,0,0,7104.0 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 USX,P,58,14,1,0,0,0,0,0,0,5446.4 USX,P,58,14,1,0,0,0,0,0,0,1657.6 AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3 PEM,G,58,10,2,0,0,0.75,2,1.5,4,8 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 HVY,R,58,14,1,0,0,0,0,0,0,72.5 HVY,R,58,14,1,0,0,0,0,0,0,176.4 VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,168 CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2 CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,65 WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9 WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4 WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72 WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3 ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394 DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4 DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8 DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7 sqlite> .schema taxitems2 CREATE TABLE taxitems2( code TEXT, taxrate TEXT, citynum INT, countynum INT, statenum INT, citym NUM, cityg NUM, countym NUM, countyg NUM, statem NUM, stateg NUM, amount ); sqlite>
Re: [sqlite] Is sqlite3 smart enough to remove redundant command?
On 1/29/2020 12:04 AM, Peng Yu wrote:
Suppose that I have the following command, which writes two entries
with the same key. So the 1st entry will be overwritten by the 2nd
entry. Therefore, there is no need to write the 1st entry. Is sqlite3
smart enough to not to write the 1st entry? Or it will write both the
1st entry and the 2nd entry? Thanks.
conn=sqlite3.connect("my.db")
c=conn.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS sqlar(
name TEXT PRIMARY KEY
, mode INT
, mtime INT
, sz INT
, data BLOB)
''')
c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', ["a", 0, 0, 1, "1"])
c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', ["a", 0, 0, 1, "2"])
conn.commit()
Do you expect the first c.execute() call to look into the future and somehow
know that the second c.execute() call is coming next?
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL CASE WHEN THEN ELSE END
On 1/26/2020 9:44 PM, Jose Isaias Cabrera wrote: CASE ( SELECT WYear FROM t2 WHERE pid = a.a ) WHEN c.WYear = 2020 THEN “YES” ELSE “NO” END ) AS DIGITAL This should probably be simply case c.WYear when 2020 then 'YES' else 'NO' end or equivalently case when c.WYear=2020 then 'YES' else 'NO' end -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Things you shouldn't assume when you store names
On 11/11/2019 2:56 PM, Igor Tandetnik wrote: On 11/11/2019 12:30 PM, Jose Isaias Cabrera wrote: Igor Tandetnik, on Monday, November 11, 2019 11:02 AM, wrote... Most people have to figure out what Unicode they are using, count the bytes, divide by... and on, and on. Not me, I just take that UTF8, or UTF16 string, convert it to UTF32, and do a count. And then what do you do with that count? What do you use it for? Say that I am writing a report and I only want to print the first 20 characters of a string A sequence of Unicode codepoints U+006F U+0302 U+0301 should be rendered as a single grapheme ( ố ) - what a human would think of as a "character". This is an actual character in Vietnamese. Now, if you have several such triplets in a row in your string, and you chop it at 20 codepoints, you'll only print 7 graphemes / "characters". Moreover, you'll end up dropping the last combining accent, producing a different grapheme (ô) and potentially altering the meaning of the text. (Don't know how much of a danger this is in Vietnamese, but I know that combining viramas https://www.compart.com/en/unicode/combining/9 are vital to Indic languages, and dropping one will in fact often produce a valid but different word). A more colorful example: Emoji characters are composed of a long sequence of Unicode codepoints: 👨👩👶👶 would be U+1F468 U+200D U+1F469 U+200D U+1F476 U+200D U+1F476 ( https://emojipedia.org/family-man-woman-baby-baby/ ) . Truncating such a sequence at an arbitrary point is likely to produce a valid emoji with a very different meaning. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Things you shouldn't assume when you store names
On 11/11/2019 12:30 PM, Jose Isaias Cabrera wrote: Igor Tandetnik, on Monday, November 11, 2019 11:02 AM, wrote... Most people have to figure out what Unicode they are using, count the bytes, divide by... and on, and on. Not me, I just take that UTF8, or UTF16 string, convert it to UTF32, and do a count. And then what do you do with that count? What do you use it for? Say that I am writing a report and I only want to print the first 20 characters of a string A sequence of Unicode codepoints U+006F U+0302 U+0301 should be rendered as a single grapheme ( ố ) - what a human would think of as a "character". This is an actual character in Vietnamese. Now, if you have several such triplets in a row in your string, and you chop it at 20 codepoints, you'll only print 7 graphemes / "characters". Moreover, you'll end up dropping the last combining accent, producing a different grapheme (ô) and potentially altering the meaning of the text. (Don't know how much of a danger this is in Vietnamese, but I know that combining viramas https://www.compart.com/en/unicode/combining/9 are vital to Indic languages, and dropping one will in fact often produce a valid but different word). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Things you shouldn't assume when you store names
On 11/11/2019 12:50 PM, Richard Damon wrote: Writing 20 UTF-32 characters may ALSO print less than 20 glyphs to the screen. Or more, depending on what you mean by "glyph". See e.g. U+FDFB (ARABIC LIGATURE JALLAJALALOUHOU, https://www.fileformat.info/info/unicode/char/fdfb/index.htm ) or U+FB03 (LATIN SMALL LIGATURE FFI, https://www.fileformat.info/info/unicode/char/fb03/index.htm) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Things you shouldn't assume when you store names
On 11/11/2019 10:49 AM, Jose Isaias Cabrera wrote: So, yes, it's bulky, but, if you want to count characters in languages such as Arabic, Hebrew, Chinese, Japanese, etc., the easiest way is to convert that string to UTF32, and do a string count of that UTF32 variable. Between ligatures and combining diacritics, the number of Unicode codepoints in a string has little practical meaning. E.g. it is not necessarily correlated with the width of the string as displayed on the screen or on paper; or with the number of graphemes a human would say the string contains, if asked. Most people have to figure out what Unicode they are using, count the bytes, divide by... and on, and on. Not me, I just take that UTF8, or UTF16 string, convert it to UTF32, and do a count. And then what do you do with that count? What do you use it for? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Different column items to fields (transpose)
On 10/23/2019 12:28 PM, Bart Smissaert wrote: Have a table created like this: create table num_values(id integer, entry_date integer, term_text text, numeric_value Real) For this problem I am only interested in the rows that have either 'Weight', 'Height' or 'BMI' in the term_text field and I can make table that have only these values. Now usually for a particular id and entry_date there are 3 rows for the above mentioned 3 values for term_text, but this is not always so. So, table date could be like this: id entry_date term_text, numeric_value -- 2 40100Weight 80 2 40100Height 170 2 40100BMI 27.7 2 40200Weight 90 2 40200Height 170 2 40200BMI31.1 3 38000Weight 86 4 40100Weight 66 4 40100Height 160 4 40100BMI 25.8 4 40100Weight 67 I want to run a select (or table insert) to get the data like this id entry_date weight height bmi 2 4010080170 27.7 2 4020090170 31.1 3 3800086 4 4010066 160 25.8 Something along these lines, perhaps: select id, entry_date, max(case term_text when 'Weight' then numeric_value else 0 end) weight, max(case term_text when 'Height' then numeric_value else 0 end) height, max(case term_text when 'BMI' then numeric_value else 0 end) bmi from num_values group by id, entry_date; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in storing text values in numeric columns
On 10/13/2019 8:04 PM, Shawn Wagner wrote: That's what I told the guy having the original issue to do, yes. That's not important. My concern is why a conversion that's only supposed to happen if it's lossless is in fact happening and causing data loss. You define the term "lossless" differently than that article does. The conversion is lossless under the article's definition, even while it's not lossless under the definition you insist upon (but which makes no sense for a column of NUMERIC affinity). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in storing text values in numeric columns
On 10/13/2019 7:25 PM, Shawn Wagner wrote: I wouldn't call that conversion, or any other, lossless unless it can be turned back into a string that's character for character identical with the one that was originally inserted. If you want the text preserved character for character, store it in a column with TEXT affinity. NUMERIC makes little sense for this requirement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in storing text values in numeric columns
On 10/13/2019 5:11 PM, Shawn Wagner wrote: The documentation for a column with NUMERIC affinity says When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. "Lossless and reversible" here clearly means that the numerical value is preserved, not that the exact text representation is. Thus, a couple paragraphs down in the same article, you'll find "A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 30, not as the floating point value 30.0." It is clear in this example that '3.0e+5' will in fact get coerced to a number, and that number will *not* in fact be rendered as '3.0e+5' when converted back to text. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Standard deviation last x entries
On 10/12/2019 10:08 AM, Bart Smissaert wrote: How do I get the standard deviation of the last 4 entries (there could be less than 4) of an integer column grouped by an integer ID entry in another column in the same table. What do you mean by "last 4 entries"? What determines the order? How does one tell which entry is the first and which is the last? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why aren't there date/time parsing built-in functions in SQLite
On 9/10/2019 7:05 AM, Keith Medcalf wrote:
select value,
round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)
from test;
Another possibility: strftime('%s', '1970-01-01 ' || value)
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multi-multi join/bridge table, table creation questions
On 8/14/2019 8:54 AM, dboland9 wrote: I'm doing a Python app (not that the language really matters - just for context) that uses SQLite to store data. I have a many-to-many (MTM) relationship. After a lot of Googling and reading I have concluded that: I need to create the join/bridge table just like all the other tables. In other words, it will not be created automatically using some SQL that I have yet to understand. True or false? True. There is no special built-in syntax for many-to-many relationships (nor for one-to-many relationships, for that matter). You just use tables that store each other's keys. In all the examples I have seen, the join/bridge table is populated (INSERT INTO) manually - as in not done by SQL. Here is my problem. I will need to insert the primary keys as foreign keys into the table. In the examples I have seen, they knew what those number were (all 12 of them). I won't have that situation as the primary keys from the other tables are AUTO_NUMBER. That means I have to query those tables before I can INSERT INTO the join/bridge tables. Too much work. Is there a better easier way? How about some complete examples? You may be looking for sqlite3_last_insert_rowid API function ( https://www.sqlite.org/c3ref/last_insert_rowid.html ) and/or last_insert_rowid() SQL function ( https://www.sqlite.org/lang_corefunc.html#last_insert_rowid ). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COMMIT, what locking behaviour when current lock is merely SHARED?
On 8/9/2019 11:00 AM, Olivier Mascia wrote: A connection holds a SHARED lock. It did start a DEFERRED transaction then read something. Now it executes COMMIT. Will the lock be upgraded from SHARED to EXCLUSIVE for the very short duration of the COMMIT (the connection made no writing, as evidenced by its SHARED lock), or will this specific case elude upgrading the lock and simply exit the transaction, releasing the SHARED-lock to NO lock? It must be the latter, otherwise readers won't be able to get out in the presence of RESERVED or PENDING lock from a prospective writer. You'd have deadlocks all the time. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Floating point literals
On 7/31/2019 5:15 PM, Eric Reischer wrote: I understand you can *retrieve* a non-quantized value using sqlite3_column_double(), but I don't see a way to set one without having to printf() the floating point value. sqlite3_bind_double Can this be done using sqlite3_bind_* interfaces, or do they quantize as well? Yes. No; except that I seem to recall it mentioned that NaN is treated as SQL NULL, and negative zero is normalized to positive zero. The goal is to copy the straight 8-byte (or precision-extended 4-byte) IEEE value into the column into the database (where the column is defined as a FLOAT) without having to build a SQL statement that has an obscene number of digits in each floating point field. That's precisely what bound parameters and sqlite3_bind_X functions are for. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.
On 7/31/2019 12:32 PM, test user wrote: In some runtimes, the scheduling of functions is unpredictable, so although you will not have a `sleep 5` in the code, the runtime can produce this effect on loaded systems or with programs with long running sync functions. An example of how you might use this: - Connection 1: Get a read transaction. - Connection 2: Get a write transaction, write a lot of data, commit. - Connection 2: SELECT report summary B. - Connection 1: SELECT report summary A, - Diff A and B to see what changed. Suppose you discovered that B and A are in fact the same. How do you know whether that occurred because a) "get a read transaction" is "broken" in that it doesn't actually acquire the lock as you expected, or because b) Connection 2 just happened to write lots of data and commit before connection 1 obtained a read transaction? In other words, in your example A == B is possible even if BEGIN worked the way you expect it to work, and grabbed a read lock immediately. Similarly, A != B is possible with BEGIN working the way it does now, if SELECT on Connection 1 just happens to beat the write on Connection 2. It's a matter of timing and scheduling, which you yourself posit is unpredictable. So, since both A==B and A!=B are possible with either behavior of BEGIN, why again do you care how BEGIN behaves? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.
On 7/31/2019 6:36 AM, test user wrote: As an example, the client of this library could: - A. Obtain a "read transaction", *without running any SELECTs*. - B. Complete 20 write transactions in another process. - C. Begin reading from the read transaction (A) at the point before the transactions had occurred. In the current world, that client would execute BEGIN, then wait a bit, then start reading and discover the data written by another process. How does it know that those writes occurred between BEGIN and SELECT, and not before BEGIN? Why would that distinction matter to it in the first place? Do you envision some other channel of communication and synchronization between these two processes, outside the SQLite database, that would help establish that writes occurred after BEGIN? With SQLite alone, it's impossible to tell whether the sequence of events was A-B-C or B-A-C - both sequences produce the exact same observable behavior. So guarding against B squeezing between A and C seems rather pointless. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_LOCKED means "database table is locked"?
On 6/19/2019 10:39 AM, Carsten Müncheberg wrote: Is there really something like a table lock? Yes there is: https://sqlite.org/sharedcache.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please explain multi-access behaviour to me
On 6/17/2019 11:35 PM, Simon Slavin wrote: I suspect that a lot of software is using BEGIN when it should be using BEGIN EXCLUSIVE. A lot of software doesn't have persistent, long-lived reader transactions. If your readers come in, get their data, and quickly get out, then a writer has sufficient opportunity to squeeze through, with a simple timeout. See also: sqlite3_busy_timeout ( https://www.sqlite.org/c3ref/busy_timeout.html ), PRAGMA busy_timeout ( https://www.sqlite.org/pragma.html#pragma_busy_timeout ) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please explain multi-access behaviour to me
On 6/17/2019 8:21 PM, Simon Slavin wrote: On 18 Jun 2019, at 1:09am, Igor Tandetnik wrote: A connection doesn't need to check locks on every statement - only when it tries to spill to disk, most commonly during commit. I think I understand what you wrote. So the bit of my program can think that its changes were written to the database and only later might my program find that they weren't ? Why would it think that, when it didn't successfully commit the transaction? Should I have used BEGIN EXCLUSIVE ? If that's what your application's logic calls for, then yes, sure. This way, you'll get an error on BEGIN EXCLUSIVE statement. Note that, once BEGIN EXCLUSIVE succeeds, all readers will be locked out until the write transaction commits or rolls back, thus reducing the concurrency. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please explain multi-access behaviour to me
On 6/17/2019 7:49 PM, Simon Slavin wrote: Is session B complaining that session A has a lock ? Yes. If session A had a lock why was there no complaint for the INSERT ? A connection doesn't need to check locks on every statement - only when it tries to spill to disk, most commonly during commit. See https://sqlite.org/lockingv3.html . Specifically, Session A holds a SHARED lock. Session B holds a RESERVED lock up until the time you ask it to commit, at which point it tries and fails to promote it to EXCLUSIVE. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CTE to Get Path In a Tree
On 5/12/2019 6:19 AM, Philip Bennefall wrote: Hi everyone, I have a tree of folders and I want to find the complete path from any arbitrary point back to the top level directory. The schema is: CREATE TABLE IF NOT EXISTS folders( id INTEGER PRIMARY KEY, parentFolderId INTEGER REFERENCES folders(id) ON DELETE CASCADE ON UPDATE CASCADE, name TEXT NOT NULL); I made the following CTE: WITH RECURSIVE folderTree (id, path) AS(SELECT id, name FROM folders WHERE parentFolderId IS NULL UNION ALL SELECT folders.id, folderTree.path || '/' || folders.name FROM folders, folderTree WHERE folders.parentFolderId = folderTree.id) SELECT path FROM folderTree WHERE id=?1; This produces the correct result, but I am wondering if there is a more efficient way? This query seems to generate the entire tree and then do a table scan to find just the one row I am looking for. Can I start from the given row and *only* traverse upwards through the levels until I find a node with no parent? Just reverse the conditions. Something like this (not tested): WITH RECURSIVE folderPath(id, parentId, path) AS(SELECT id, parentFolderId, name FROM folders WHERE id=?1) UNION ALL SELECT f.id, f.parentFolderId, f.name || '/' || fp.path FROM folders f join folderPath fp on (f.id = fp.parentId)) SELECT path FROM folderPath WHERE parentId is null; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: FILTER on aggregate functions
On 5/6/2019 5:19 PM, Shawn Wagner wrote: I just found out that postgres (And possibly others?) supports FILTER on aggregate functions in general, not just when they're used as a window function. Trivial example: SELECT count(*), count(*) FILTER (WHERE amount > 100) FROM blah which is a lot cleaner than SELECT count(*), sum(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM blah sum(amount > 100) is sufficient. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with sqlite3_value_text
On 4/12/2019 10:51 AM, x wrote: I’m still confused by utf strings. For simplicity, suppose I set up an sqlite function that takes a single string parameter and I want to scan the string to count the number of occurrences of a certain character . If I knew the string was made up entirely of ascii chars I’d do this char *c = &sqlite3_value_text(0)[0]; int count=0; while (*c) if (*c++ == SearchChar) count++; How do I do the same thing if the string param is a utf-8 or utf-16 string and the SearchChar is a Unicode character? The problem you need to solve is "count occurrences of a substring in a string". The substring in question could consist of one byte representing a single ASCII character, or a sequence of bytes comprising a UTF-8 encoding of one Unicode character. This really has nothing to do with SQLite. I’m confused by the fact that Unicode characters are not a fixed number of bytes so if I do this e.g. wchar_t *c = (wchar_t*) sqlite3_value_text(0); That's just wrong. sqlite3_value_text does *not* return a pointer to a sequence of wchar_t. Any attempt to actually use `c` pointer would exhibit undefined behavior. does this mean a complete temporary copy of the value of sqlite3_value_text(0) has to be constructed by the compiler such that all characters of the newly constructed string are fixed width? If so, I’m just wanting to check if there’s a way of avoiding this overhead. You seem to ascribe some magical properties to a cast. Nothing is "constructed" by it - it simply tells the compiler "take this pointer to a memory block, and believe that it contains something different than what the type of the original pointer suggests; trust me, I know what I'm doing". If you prefer UTF-16 encoding over UTF-8, there's sqlite3_value_text16 for that. If you are unsure what UTF-8 and UTF-16 mean, see https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/ -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this possible
On 3/29/2019 9:55 AM, Dan Kennedy wrote: On 29/3/62 03:00, Igor Tandetnik wrote: On 3/28/2019 3:21 PM, Mark Wagner wrote: Imagine I have these two tables and one view defining a join. CREATE TABLE t (foo); CREATE TABLE s (bar); CREATE VIEW v as select * from t join s on (foo = q); Surprisingly, this last statement succeeds. But if you then close the database and try to open it again, it'll fail with "no such column: q". So, don't do this - you are creating an unusable database file with corrupted schema. The error doesn't occur unless you actually query the view though, correct? Yes, I think the tool I was using tries to use the view right after opening the database, probably to figure out what columns it offers. This led me to erroneously conclude that merely opening the database triggers the error. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this possible
On 3/28/2019 3:21 PM, Mark Wagner wrote: Imagine I have these two tables and one view defining a join. CREATE TABLE t (foo); CREATE TABLE s (bar); CREATE VIEW v as select * from t join s on (foo = q); Surprisingly, this last statement succeeds. But if you then close the database and try to open it again, it'll fail with "no such column: q". So, don't do this - you are creating an unusable database file with corrupted schema. I don't believe you can create a parameterized view. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE optimization opportunity (was: filling a key/value table)
See if something like this works better: INSERT INTO logidx(keyid,value,location) SELECT id, val, location from ( SELECT k.id id, (case k.name when 'type' then l.type when 'name' then l.name when 'size' then l.size else NULL end) AS val, l.location location FROM logfile l, logkey k ) WHERE val IS NOT NULL; Or WITH RawData AS ( SELECT k.id id, (case k.name when 'type' then l.type when 'name' then l.name when 'size' then l.size else NULL end) AS val, l.location location FROM logfile l, logkey k ) INSERT INTO logidx(keyid,value,location) SELECT id, val, location from RawData WHERE val IS NOT NULL; Igor Tandetnik On 3/26/2019 10:15 AM, Hick Gunter wrote: This works as expected, thanks. SQLite implements this as a pair of coroutines: Routine A) does a nested scan of the logfile (outer loop = just read the logifle once) and the logkey table (inner loop) Routine B) does the actual insert(s) into the logidx table Unfortunately, the necessary check of the generated val causes the CASE expression to be evaluated twice (see bytecode). Instructions 16 thru 25 implement the CASE, leaving R12 holding the computed value Instruction 26 checks that something the evaluates to TRUE has been found Instructions 28 thru 37 duplicate the CASE, leaving R3 with the re-computed value The same effect could be achieved by copying R12 into R3 when buildung the record, just as R11 is copied to R4 9 VFilter0 437 00 iplan=r[7] zplan='' 10 VColumn0 4711 00 r[11]=vcolumn(47); atx_txlog.period_no 11 Lt 134211(BINARY) 53 if r[11]r[14] goto 42 13 Explain130 0 SCAN TABLE keys AS k 00 14 Rewind 1 43000 15Column 1 1 15 00 r[15]=keys.name 16Ne 171915(NOCASE) 52 if r[15]!=r[17] goto 19 17VColumn0 1512 00 r[12]=vcolumn(15); atx_txlog.event_type 18Goto 0 26000 19Ne 182215(NOCASE) 52 if r[15]!=r[18] goto 22 20VColumn0 2812 00 r[12]=vcolumn(28); atx_txlog.retailer_loc_id 21Goto 0 26000 22Ne 192515(NOCASE) 52 if r[15]!=r[19] goto 25 23VColumn0 6412 00 r[12]=vcolumn(64); atx_txlog.ticket_key_string 24Goto 0 26000 25Null 0 12000 r[12]=NULL 26IfNot 1241100 27Rowid 1 2 000 r[2]=rowid 28Ne 173115(NOCASE) 52 if r[15]!=r[17] goto 31 29VColumn0 15300 r[3]=vcolumn(15); atx_txlog.event_type 30Goto 0 38000 31Ne 183415(NOCASE) 52 if r[15]!=r[18] goto 34 32VColumn0 28300 r[3]=vcolumn(28); atx_txlog.retailer_loc_id 33Goto 0 38000 34Ne 193715(NOCASE) 52 if r[15]!=r[19] goto 37 35VColumn0 64300 r[3]=vcolumn(64); atx_txlog.ticket_key_string 36Goto 0 38000 37Null 0 3 000 r[3]=NULL 38Copy 114 000 r[4]=r[11] 39VColumn0 6 500 r[5]=vcolumn(6); atx_txlog.sync_offset 40Yield 6 0 000 41 Next 1 15001 42VNext 0 10000 -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Igor Tandetnik Gesendet: Donnerstag, 21. März 2019 17:29 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] filling a key/value table On 3/21/2019 12:04 PM, Hick Gunter wrote: I have an external data store that is accessible to sqlite as a virtual table. The equivalent SQL declaration is similar to: CREATE TABLE logfile (timestamp INTEGER, type INTEGER, name TEXT, size INTEGER, ...); I would like to create an index as a native SQLite table declared like: CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary key (keyid, value, location) ) WITHOUT ROWID; The fields of interest are stored in a config table: CREATE TABLE logkey( ID INTEGER PRIMARY KEY, name TEXT ); INSERT INTO logkey(name) VALUE
Re: [sqlite] filling a key/value table
On 3/21/2019 12:04 PM, Hick Gunter wrote:
I have an external data store that is accessible to sqlite as a virtual table.
The equivalent SQL declaration is similar to:
CREATE TABLE logfile (timestamp INTEGER, type INTEGER, name TEXT, size INTEGER,
...);
I would like to create an index as a native SQLite table declared like:
CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary key
(keyid, value, location) ) WITHOUT ROWID;
The fields of interest are stored in a config table:
CREATE TABLE logkey( ID INTEGER PRIMARY KEY, name TEXT );
INSERT INTO logkey(name) VALUES ('type'),('name'),('size');
The naive method of inserting values is thus:
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.type,l.location from
logkey k, logfile l where k.name = 'type'AND l.type IS NOT NULL;
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.name,l.location from
logkey k, logfile l where k.name = 'name'AND l.name IS NOT NULL;
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.size,l.location from
logkey k, logfile l where k.name = 'size'AND l.size IS NOT NULL;
This hast he disadvantage of requiring a complete scan of the virtual logidx
table for each kind of entry.
Any ideas on how to create all the tuples with only one pass of the logidx
table?
Something like this:
INSERT INTO logidx(keyid,value,location)
SELECT k.id,
(case k.name when 'type' then l.type when 'name' then l.name when 'size' then
l.size else NULL end) AS val,
l.location
FROM logfile l, logkey k WHERE val IS NOT NULL;
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question, move union to second column
On 3/13/2019 8:32 PM, Bart Smissaert wrote: Sorry, ignore that, can see now that all is a reserved word. You can enclose it in double quotes, as in "All", if you really want it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question, move union to second column
On 3/13/2019 8:08 PM, Bart Smissaert wrote: But I would like the result to be in 3 columns, so result in this case would be: Place Not_Attended All --- A 3 7 B 2 3 Something like this (not tested): select PLACE, sum(ID not in (select ID from ATTENDED)) Not_Attended, count(*) All from PERSONS group by PLACE; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BigInt loss accuracy
On 2/23/2019 1:48 PM, Derek Wang wrote: x = 10 for i in range(22): x = 10*x y = x + 3 s = 'insert into bi values (%s, %s, %s)' % (i, y, y) Print `s`. I suspect you are losing precision on Python side, during text formatting. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about floating point
On 12/20/2018 1:34 PM, Dennis Clarke wrote: A more interesting topic of discussion would be the speed and complexity of circuitry designed for another number base such as 5 or even decimal. https://en.wikipedia.org/wiki/Ternary_computer -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grouping guidance
On 12/13/2018 3:41 PM, Igor Tandetnik wrote: On 12/13/2018 3:27 PM, Don V Nielsen wrote: Making a mountain out of a mole hill, but isn't the solution more complex that that? The description has to be Foo & Bar. But if given the following, then the simple answer dies. select Request from MyTable group by Request having sum(Description='Foo') > 0 and sum(Description='Bar') > 0; ... and sum(Description not in ['Foo', 'Bar']) = 0 Add conditions to taste. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grouping guidance
On 12/13/2018 3:27 PM, Don V Nielsen wrote: Making a mountain out of a mole hill, but isn't the solution more complex that that? The description has to be Foo & Bar. But if given the following, then the simple answer dies. select Request from MyTable group by Request having sum(Description='Foo') > 0 and sum(Description='Bar') > 0; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grouping guidance
On 12/13/2018 1:58 PM, Joseph L. Casale wrote: I have data that resembles the following: Request NumberDescription REQ0090887 TASK0236753 Foo REQ0090887 TASK0234920 Bar REQ0090887 TASK0234921 Bar REQ0090887 TASK0237261 Foo REQ0086880 TASK0224045 Foo REQ0086903 TASK0224555 Bar REQ0086990 TASK0223977 Bar REQ0087061 TASK0226748 Foo REQ0087061 TASK0223810 Bar I want to group request values where each request contains both a task with description Foo and Bar. So in the above example, only REQ0090887 and REQ0087061 meet this. select Request from MyTable group by Request having count(distinct Description) = 2 -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_text() and sqlite3_column_int()
On 12/6/2018 12:58 PM, Prajeesh Prakash wrote: When i am trying to fetch the table data using sqlite3_column_text() i am getting wrong column entry even if i gave the correct column number as the second argument. Show your query, your sqlite3_column_text() call, explain what result you expect and what you observe instead. Remember that columns are numbered starting from 0 (zero). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Group by counting 2 different sub-groups
On 11/24/2018 8:51 PM, Bart Smissaert wrote: Ok, in the first count column I would like the grouped counts for patients in the views on_non_insulin or on_insulin and in the second count column I would like the grouped counts for patients the view diabetics. Well, you already know the technique for this. select gp_name, sum(emis_number in (select emis_number from diabetics), sum(emis_number in (select emis_number from on_non_insulin) OR emis_number in (select emis_number from on_insulin)) from patients group by gp_name; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Group by counting 2 different sub-groups
On 11/24/2018 7:59 PM, Bart Smissaert wrote: Thanks, was aware, but the SQL was indeed wrong as posted and should have brackets around the 2 or conditions. In this case, as far as I can tell you should end up with diab_count equal to pat_count, since "emis_number in(select emis_number from diabetics)" is true for every row, guaranteed by the WHERE clause. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Group by counting 2 different sub-groups
On 11/24/2018 7:44 PM, Bart Smissaert wrote: The very much simplified example works fine, but my real SQL is a bit more complex: select gp_name, count(*) as pat_count, sum(emis_number in(select emis_number from diabetics)) as diab_count from patients where emis_number in(select emis_number from diabetics) and emis_number in(select emis_number from on_non_insulin) or emis_number in(select emis_number from on_insulin) group by gp_name order by pat_count asc Just in case you are not aware, AND has higher precedence than OR. Your query does ( (Diabetics AND Non-Insulin) OR Insulin ) as opposed to ( Diabetics AND (Non-Insulin OR Insulin) ). Apologies if you knew that and really meant the former. The above SQL works, but gives too low counts for diab_count . Show sample data, the result you expect, and the result you observe instead. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Group by counting 2 different sub-groups
On 11/24/2018 6:59 PM, Bart Smissaert wrote: Have a table called Table1 like this: id Type -- 1 a 2 b 3 a 4 c 5 a 6 b 7 c 8 c 9 b 10 a Table create is this: CREATE TABLE [Table1]([id] INTEGER PRIMARY KEY, [Type] TEXT) Then there are 2 views, created like this CREATE VIEW View1 as select id from Table1 where id < 8 CREATE VIEW View2 as select id from Table1 where id < 5 I can run a SQL like this: select type, count(id) as id_count_view2 from Table1 where id in(select id from view2) group by type and that will give me: Type id_count_view2 a 2 b 1 c 1 But I would like in 2 columns the counts of both views, so I would like this output: Type id_count_view2 id_count_view1 - a 2 3 b 1 2 c 1 2 How should this be done? select type, sum(id in (select id from view2)) id_count_view2, sum(id in (select id from view1)) id_count_view1, from Table1 group by type; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve table names for the given string
On 9/28/2018 9:51 AM, Thomas Kurz wrote: No, it's PRAGMA table_info (...); It has to be pragma_table_info if you want to use it as part of a SELECT statement. See https://www.sqlite.org/pragma.html#pragfunc -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve table names for the given string
On 9/28/2018 2:16 AM, Revathi Narayanan wrote: Thanks Richard. But I am getting an error like near ( syntax error. Pragma table_info(a.name) It's pragma_table_info , in one word; two underscores, no spaces. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve table names for the given string
On 9/27/2018 9:43 AM, Revathi Narayanan wrote:
I have one requirement like I want to display all the table names for the
given column name.
Ex: If the table T1 and T2 has column names like C1 then it should display
both the table names T1 and T2.
With sufficiently recent SQLite version, you can select from
pragma_table_info('tablename') and get the same resultset as PRAGMA
table_info(tablename): https://www.sqlite.org/pragma.html#pragfunc . These
functions can participate in joins: the table name doesn't have to be a literal.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Missing function sqlite3_column_index
On 9/2/2018 10:13 AM, Sebastian wrote: for sqlite3_bind_parameter_name there is an inverse function sqlite3_bind_parameter_index. But for sqlite3_column_name, I could not find such a function. Column names may not be unique, or meaningful. E.g. select a, a, 1+2 from mytable; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Time Precision
On 7/1/2018 2:37 PM, danap wrote:
The time precision treated with and defined, ISO-8601, seems to be
with regard to seconds. Storage of an Integer for time as an example
in SQLite:
sqlite> SELECT STRFTIME('%s', 'now', 'localtime');
1530446557
A 10 digit value. The issue I'm having is with regard to storage
of time, in milliseconds, a 13 digit value. I would assume a more
appropriate precision for a scientific community.
select (julianday('now') - julianday('1970-01-01'))*24*60*60*1000
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 6/27/2018 10:43 PM, Richard Hipp wrote: On 6/27/18, Igor Tandetnik wrote: On 6/27/2018 9:14 PM, Richard Hipp wrote: On 6/27/18, Mark Wagner wrote: Thanks for all the good background. FWIW this came up because someone had created a row with something like: (column_name non null). Needless to say, this created a column without a "not null" constraint. It should have. I get an error when I type: Note the typo: "non null" where "not null" was meant. This creates a column with type "non". I'm not sure why "null" is accepted though - no path through syntax diagram seems to allow it at that spot. Perhaps there's an undocumented column constraint "NULL", to complement "NOT NULL"? "NULL" without the "NOT" is a valid constraint. It's not mentioned here though: https://sqlite.org/syntax/column-constraint.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 6/27/2018 9:14 PM, Richard Hipp wrote: On 6/27/18, Mark Wagner wrote: Thanks for all the good background. FWIW this came up because someone had created a row with something like: (column_name non null). Needless to say, this created a column without a "not null" constraint. It should have. I get an error when I type: Note the typo: "non null" where "not null" was meant. This creates a column with type "non". I'm not sure why "null" is accepted though - no path through syntax diagram seems to allow it at that spot. Perhaps there's an undocumented column constraint "NULL", to complement "NOT NULL"? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 6/27/2018 6:56 PM, Mark Wagner wrote: I recently pointed out that sqlite doesn't enforce type names and constraints when creating tables but I was unable to explain/justify this behavior. https://sqlite.org/datatype3.html SQLite attempts to be maximally compatible with a wide variety of database engines, and those use all kinds of naming conventions for their data types. SQLite itself doesn't really have column data types - at least, not in the traditional sense. It has a concept of type affinity. To this end, SQLite accepts any sequence of names, optionally followed by one or two numbers in parentheses, as a valid column type: https://sqlite.org/syntax/type-name.html . These names and numbers are largely ignored, except to the extent that a column type affinity is gleaned from them via a simple substring match. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding SELECT statement
On 6/26/2018 10:42 AM, Csányi Pál wrote: Igor Tandetnik ezt írta (időpont: 2018. jún. 26., K, 16:10): On 6/26/2018 9:15 AM, Csányi Pál wrote: Then I get help and this code: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays S) This says "insert all dates that aren't already present", which of course avoids UNIQUE constraint. Just do not understand what are the 'T' and 'S' means out there, after FROM clause. Are they CTEs? Are they virtual tables? They are aliases. You could write TeachingSaturdaysInSchoolYear.aDate in place of T.aDate without a change in meaning; T.aDate is merely shorter and more convenient. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding SELECT statement
On 6/26/2018 9:15 AM, Csányi Pál wrote: I have the 'SchoolYearTeachingDays' table with just one column, in which are dates: CREATE TABLE SchoolYearTeachingDays ( aDate DATE PRIMARY KEY UNIQUE ); I filled it with many dates which are unique. These dates excludes dates for Sundays and for Saturdays. I have another, the 'TeachingSaturdaysInSchoolYear' table: CREATE TABLE TeachingSaturdaysInSchoolYear ( idINT PRIMARY KEY UNIQUE, aDate DATE, TimetableForTheDay TEXT ); This table holds just two dates. These two dates are for two Saturdays. On these two Saturdays we have to teach students. When I do the following query on this table, I get these two records: 2018-04-14 2018-05-05 I want to INSERT these two dates from the 'TeachingSaturdaysInSchoolYear' table into 'SchoolYearTeachingDays' table. I am trying with this query: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear ; but I get this error: Error: UNIQUE constraint failed: SchoolYearTeachingDays.aDate This means that, despite your assumption to the contrary, at least one of those two dates is already present in SchoolYearTeachingDays Then I get help and this code: INSERT INTO SchoolYearTeachingDays SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate NOT IN (SELECT S.aDate FROM SchoolYearTeachingDays S) This says "insert all dates that aren't already present", which of course avoids UNIQUE constraint. I wish to know followings. How many times want to inserts the SELECT query the one of the date from the TeachingSaturdaysInSchoolYear table into SchoolYearTeachingDays table? I don't understand this question. That is: the how many times wants select statement to insert one record from first table into second table? Each row in the resultset of SELECT statement is inserted once, of course. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT} Posting through Gmane
On 6/15/2018 9:33 AM, Lifepillar wrote: I use Gmane to follow this mailing list and Fossil's. I have subscribed to both lists. While I can post through Gmane to this list, however, I cannot post to fossil-users: messages keep bouncing back with "Address not found, ..., The response was 550 unknown user". Fellow Gmane user here. At one point, sqlite-users mailing list changed its email address (from sqlite-us...@sqlite.org to sqlite-users@mailinglists.sqlite.org if I recall correctly), but Gmane had the old address. I asked Gmane to have the email updated in their system, and they did - simple as that. Maybe something similar happened to fossil-users However, that was before https://lars.ingebrigtsen.no/2016/07/28/the-end-of-gmane , while gmane.org site was still functioning and Lars still responded to email. I have no idea who's in charge now, if anyone, and how to go about support requests. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset the cursor
On 6/4/2018 12:31 AM, Igor Korot wrote: Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 so I can process those records again. I thought that this will be a job of sqlite_reset(), but when I called it and started re-processing the recordset I got SQLITE_DONE on the very first iteration. sqlite_reset definitely works. The problem must be somewhere in the code you haven't shown. Can you reproduce in a small complete example? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Only see unused when there are unused records
On 5/9/2018 4:19 PM, Cecil Westerhof wrote: I have a table where I use 'unused' to signify that a record is not yet used. I want to know the number of unused records (but only if there are unused records) and the total number of records. Something like this perhaps: select count(*) Total, sum(totalUsed = 'unused') NotUsed from quotes; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On 5/8/2018 4:02 PM, Mike Clark wrote: Has there been any thought of implementing some kind of stored procedure feature for SQLite? Or does this feature exist in some other form, with another name? Triggers are kind of like stored procedures. You can create a dedicated view and put INSTEAD OF trigger on it. To "call" the trigger, insert a row into the view - the trigger could use column values, available via new.columnName, as its parameters. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MIN() and MAX() of set of row values
On 3/30/2018 1:04 PM, Peter Da Silva wrote: On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" wrote: can think of to do it is to devise a metric to turn a pair (a, b) into one number. Problem is you can't uniquely order pairs of points. Yes you can. Is (1, 2) greater or lesser than (2, 1)? "select (1, 2) < (2, 1)" says lesser. For further discussion, see https://www.sqlite.org/rowvalue.html#row_value_comparisons -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MIN() and MAX() of set of row values
On 3/30/2018 12:10 PM, Simon Slavin wrote: On 30 Mar 2018, at 3:48pm, Mark Brand wrote: SELECT MIN((a,b)) = (1, 2) ok FROM T; --Error: near line 4: row value misused SELECT MIN((SELECT a, b)) = (1, 2) ok FROM T; --Error: near line 7: row value misused SELECT (1, 2) = MIN(SELECT a, b FROM T); --Error: near line 10: near "SELECT": syntax error You got very close to the right syntax. SQLite version 3.19.3 2017-06-27 16:48:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE T (a, b); sqlite> INSERT INTO T (a, b) VALUES (1, 2), (1, 3), (2, 3); sqlite> SELECT min(a,b) FROM T; 1 1 2 I don't think that's what the OP had in mind. They didn't want the smaller of a and b for each row, but rather the lexicographically smallest (a, b) pair among all rows. Row values support less-than comparison, so it kind of makes sense to expect MIN to work on them, too. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] possible bug: select clause column alias shadowing
On 3/18/2018 5:31 AM, Moritz Bruder wrote:
I just came across a strange behaviour in sqlite3 (3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d). Consider the
following test case:
CREATE TABLE test (name varchar);
INSERT INTO test VALUES ("foo"),("bar");
-- Returns a single row with a single column: 'foo!'
SELECT (test.name || '!') AS tname
FROM test
WHERE tname = 'foo!'
--Returns an empty result.
SELECT (test.name || '!') AS name
FROM test
WHERE name = 'foo!';
What happens is that the identifier "name", defined in the SELECT-clause, gets shadowed
by the table's column "name".
If I recall correctly, SQL standard doesn't allow aliases from SELECT to be
used in WHERE clause, only in ORDER BY and, possibly, HAVING (I'm not sure of
the latter). SQLite allows aliases in WHERE as an extension, but prefers the
real column name in case of conflict, so as to match the behavior of other DBMS.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting a non-unique column name in a WHERE clause
On 3/8/2018 10:41 PM, Balaji Ramanathan wrote:
CREATE VIEW TripDetailsRaw AS select *
from Trip
inner join Place P1 on Trip.Origin = P1.PlaceID
inner join Place P2 on Trip.Destination = P2.PlaceID;
When I look at the output of this view (I type in "select * from
tripdetailsraw" at the sqlite command line, I see one set of columns from
P1 and another set of columns from P2 with ":1" appended to the repeated
column names ("PlaceName:1", "Country:1", etc.). But I have no idea how to
add a WHERE clause to my select that references these columns.
Don't use "select *" in the view. Explicitly select columns you need, assign
aliases to them as needed. As in
select p1.country as OriginCountry, p2.country as DestinationCountry, ...
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange concatenation result
On 2/26/2018 12:23 AM, Gary Briggs wrote: Evening I'm seeing a weird effect when concatenting things: WITH q(tape,dp) AS (SELECT '04E', 1) SELECT SUBSTR(tape,1,dp-1) || SUBSTR(tape,dp,1)+1 || SUBSTR(tape,dp+1) AS expect_14E, || has higher precedence than +. Your expression is an arithmetic sum of two values: SUBSTR(tape,1,dp-1) || SUBSTR(tape,dp,1) -- 0 + 1 || SUBSTR(tape,dp+1) -- '14E', converted to integer 14 -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dealing with SQLITE_BUSY
On 2/22/2018 5:54 AM, Nick wrote: I use sqlite3_open() to open two connections, and I have configured journal_mode=WAL, threadsafe=2. Connection 1 is doing: sqlite3_exec(db1, "BEGIN", 0, 0, &zErrMsg); sqlite3_exec(db1, "SELECT * FROM t1;", 0, 0, &zErrMsg); sqlite3_exec(db1, "INSERT INTO t1 VALUES(1, 1, \"aa\”)”, 0, 0, &zErrMsg); //SQLITE_BUSY sqlite3_exec(db1, "COMMIT", 0, 0, &zErrMsg); I got that SQLITE_BUSY as connection 2 was writing the db at the same time. I have called sqlite3_busy_timeout() but I find that it does not work if INSERT runs after a SELECT within BEGIN and COMMIT. Is it expected? Yes. See http://sqlite.org/c3ref/busy_handler.html , the part about a deadlock. See also the discussion of BEGIN IMMEDIATE and BEGIN EXCLUSIVE here: http://sqlite.org/lang_transaction.html ; either will avoid deadlocks. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
On 1/26/2018 6:20 PM, Roman Fleysher wrote: I think I effectively did as you suggested using triggers. I insert NULL into the ID column to create a row. This triggers the trigger to run update on the table to populate the columns based on the just created ID. Is this what you suggested? Roughly, though running a single statement at the end seems simpler, and likely goes faster, than setting up a trigger. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
On 1/26/2018 6:03 PM, Roman Fleysher wrote: My implementation of "for Each row" requires all columns to be populated. It is a dumb thing: You said: After table is filled, an operation "for each row" will... I suggest running this UPDATE statement at the end of "table is filled", before "an operation will..." part. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
On 1/26/2018 5:47 PM, Roman Fleysher wrote: I will use this table as a manager. There will be multiple columns holding various file names. The names can be random, but I want humans to be able to easily inspect. After table is filled, an operation "for each row" will get files in some columns and produce files in other columns. This is done outside of SQLite. "For each row" will process several rows in parallel because they are independent. Some operations might fail and will be recored in the proper columns. After all the work is done, the manager table is discarded. I'm still not sure I understand, but: while you are building out this manager table, can't you leave fileName column blank, and then right before processing, run UPDATE A SET fileName='prefix_'||ID; on it? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key in another column
On 1/26/2018 4:43 PM, Roman Fleysher wrote: I would like to use primary key as a way to create unique column entry: CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL) such that file name is always prefix followed by the ID for the content to be: ID fileName 1 prefix_1 2 prefix_2 Why do you want to store redundant data? What's the actual problem this is supposed to help you solve? As stated, this looks like an XY problem ( http://xyproblem.info/ ) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can this be done with SQLite
On 1/22/2018 4:36 PM, Cecil Westerhof wrote:
When I do this, I get:
sqlite> SELECT *
...> FROM playYouTubeVideo
...> WHERE key BETWEEN '1' AND '5'
...> ;
1|1.0
2|2.0
3|3.0
4|4.0
5|5.0
[snip]
sqlite> SELECT *
...> FROM playYouTubeVideo
...> WHERE key BETWEEN '1' AND '5'
...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0
But I want the last one needs to be 1.0.
Something along these lines, perhaps:
update playYouTubeVideo set key=char(61440+unicode(key));
update playYouTubeVideo set key=case when key=char(61440+unicode('1')) then '5'
else char(unicode(key)-61440-1) end;
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?
On 12/21/2017 12:50 PM, Simon Slavin wrote: SQLite version 3.19.3 2017-06-27 16:48:08 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10)); sqlite> INSERT INTO MyTable VALUES (15); sqlite> INSERT INTO MyTable VALUES (5); Error: CHECK constraint failed: noless sqlite> INSERT OR IGNORE INTO MyTable VALUES (6); sqlite> SELECT * FROM MyTable; 15 sqlite> What the hell ? Why does that work ? Isn’t it a huge bug ? How did you discover it ? My understanding is that using INSERT OR IGNORE meant that bad inserts would fail, but they would do so silently, without triggering an error result. Isn't that precisely what happened in your example? Inserting 6 failed silently. What again seems to be the problem? If I understand correctly, "the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong" means that a row that violates constraints will not be inserted. And in your example, it was not. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can a trigger recursively update a table?
On 12/12/2017 6:44 PM, Shane Dev wrote: However, if I try to create a trigger with this statement - http://www.sqlite.org/lang_with.html """ Limitations And Caveats - The WITH clause cannot be used within a CREATE TRIGGER. """ -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] extracting domain names from website addresses efficiently
On 12/11/2017 12:22 PM, Klaus Maas wrote: There is 1 item in the last where condition of which I do not understand the purpose: (instr(subdomain, '.') = 0 and subdomain = long) That's a zero-dot case, when the original domain is simply 'com', say. Added for completeness. This means that anything in the form of 'ftp://test/' would output the string between the two delimiters (:// and /), in this case 'test'. But that is not a domain name in the format domain.tld. Feel free to adjust to taste. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] extracting domain names from website addresses efficiently
Something like this (could likely be simplified further, but this should give the idea): with recursive subdomains as ( SELECT substr(link, instr(link, '://')+3, instr(substr(link, instr(link, '://')+3), '/')-1) AS long, substr(link, instr(link, '://')+3, instr(substr(link, instr(link, '://')+3), '/')-1) as subdomain FROM links union all select long, substr(subdomain, instr(subdomain, '.') + 1) from subdomains where instr(subdomain, '.') > 0) select * from subdomains where (instr(subdomain, '.') = 0 and subdomain = long) OR (instr(subdomain, '.') > 0 and instr(substr(subdomain, instr(subdomain, '.') + 1), '.')=0); The main point is to recursively build a table of all suffixes, then select just the suffixes you want. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I combine these to 2 views ...
On 11/26/2017 3:02 AM, Shane Dev wrote:
Hello,
I am try to combine the following 2 views - vtag and vparent_closetag
sqlite> select id, level, line from vtag;
id|lev|line
id level line
1 0
2 1
3 1
4 2
5 1
6 2
7 3
8 2
sqlite> select id, level, line from vparent_closetag;
id level line
1 0
3 1
5 1
6 2
desired result-
order level line
1 0
2 1
3 1
4 2
5 1
6 1
7 2
8 3
9 2
10 2
11 1
12 0
Any ideas to achieve this?
That's what I'd do. Run a query like this:
select t.id, t.level, t.line tag, ct.line closing_tag
from vtag t left join vparent_closetag ct using (id)
order by t.id;
In the application, run through the resultset in order, keep a stack of {level,
closing_tag} pairs for those rows where closing_tag is not null. Whenever level is
reduced from one row to the next (so level_current < level_previous), pop from the
stack and insert into the result all pairs from the top of the stack where level
>= level_current. At the end, pop and insert all the remaining stack elements (as
if there were a last row with level=0).
It might be possible to express this in SQL with a clever recursive CTE query,
but it'd be easier to implement this in application code; and would most likely
work much faster.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can someone explain these outputs for me?
On 11/11/2017 8:55 PM, Balaji Ramanathan wrote: 3.When there is a mathematical expression after the string, I get a 0. My string is nowhere to be seen in the output SQLite> select '- '||cast(-1.5 as integer)*-1 0 || has the highest precedence. Your expression is interpreted as ( '- '||cast(-1.5 as integer) ) * -1 . The string produced by the stuff in parentheses doesn't look like a valid number, and so becomes 0 when coerced to the same. Basically, you are doing select 'foobar' * -1 4.But when I add 1 instead of multiplying, it produces output that seems to evaluate everything before the addition to zero SQLite> select '- '||cast(-1.5 as integer)+1 1 0 * -1 == 0 0 + 1 == 1 5.Enclosing the mathematical expression in a printf produces the correct output SQLite> select '- '|| printf(cast(-1.5 as integer)*-1) - 1 So would enclosing in parentheses. The point is not printf() call, but changing the order of evaluation. 6.If the output starts with a number, then it doesn't seem to matter what follows. Notice that the last part of the expression below is the same as the expression in query number 3 above, but it works fine now whereas previously it produced a zero as the output SQLite> select cast(1.5 as integer)||'-'||(cast(-1.5 as integer)*-1) 1-1 The last part is parenthesized here, whereas it wasn't in prior examples. That makes all the difference. I am sure it has something to do with order of operations and the affinity of the operands, but can someone give me a summary that I can understand readily? The only mentions of the "||" operator on the SQLite website ( https://sqlite.org/lang_expr.html) don't really explain what is going on in the above examples. The part of the article you quote that you seem to overlook is "in order from highest to lowest precedence" -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binding an order by
On 10/5/2017 2:45 PM, Stephen Chrzanowski wrote: Given the query: select EventID, Airline, ContactInfo,TicketID,CreateDate from tEvents where Resolved=:Resolved order by :OrderBy I wanted to bind :OrderBy with field names You can't. A bound parameter can only appear where a literal is allowed by syntax. Field names you'll have to embed directly into the query. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec.
On 10/4/2017 10:17 PM, Hegde, Deepakakumar (D.) wrote: There is a problem we are facing with the multiple DB process write operation. Following the procedure: 1) Open the Database in the two process as: sqlite3_open_v2 (pcDbName, &psRaceSqlite->sSqliteInfo.pSqlHandle, (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX), NULL); 2) Both the link are added the busy handler and busy handler function is retries for 1 times with 10ms second of delay. sqlite3_busy_handler(psRaceSqlite->sSqliteInfo.pSqlHandle, dbm_sqlite_busy_handler,psRaceSqlite); 3) In one thread there is a continuous record insertion with begin and commit operation. This begin and commit will be done with for every 200 record insertion. time for a single begin to the commit is about 1 second. Use BEGIN IMMEDIATE or BEGIN EXCLUSIVE here. For rationale, see http://sqlite.org/c3ref/busy_handler.html the paragraph that mentions "deadlock". Also http://sqlite.org/lang_transaction.html and http://sqlite.org/lockingv3.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_errcode()
On 10/1/2017 1:56 PM, Bart Smissaert wrote: This is in the documentation of sqlite3_errcode(): If the most recent API call was successful, then the return value from sqlite3_errcode() is undefined. I can see that if there is no error then then return value of sqlite3_errcode() can be SQLITE_ROW (100) or SQLITE_DONE (101). So, to check if there is an error (other than looking at all the return values) There is no other way to check if there is an error than by looking at all the return values. That's what the documentation is telling you: the value returned by sqlite3_errcode() is only meaningful if the return value of the most recent API call indicated that that call has failed. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strftime and julianday
On 9/27/2017 8:06 PM, Stephen Chrzanowski wrote:
Messing around tonight with a work project, dealing with times, I noticed
the following:
C:\Users\Stephen>sqlite3
SQLite version 3.8.10.1 2015-05-09 12:14:55
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select strftime('%H:%M',0.0);
12:00
I would have expected 0:00, not 12:00. Does this tie into julianday being
a 12 hour offset?
Yes. A Julian day starts at noon, not midnight. strftime('%H:%M',-0.5); would
give you midnight of that day.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE FOREIGN KEY support
On 9/27/2017 9:18 AM, Igor Korot wrote: On Wed, Sep 27, 2017 at 7:04 AM, Clemens Ladisch wrote: Alternatively, if you're feeling adventurous, you can use PRAGMA writable_schema to modify the table definition in place: https://stackoverflow.com/a/42970982/11654 Just curious - why I should feel adventurous? If you make a mistake, you can render the schema un-parseable and the database un-openable, effectively losing all the data in it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Data types for date and time functions
On 9/25/2017 8:23 AM, Stephan Buchert wrote:
I was just going to write that you can easily convert an MS serial date
value stored in Sqlite to a date string (using 40777 as example):
sqlite> select date('1899-12-31', 40777||' days');
2011-08-23
However, according to
https://support.office.com/en-us/article/DATEVALUE-function-df8b07d4-7761-4a93-bc33-b7471bbff252
the answer should be 2011-08-22.
1900 was not a leap year, 2000 was, can it be, that the MS Excel software
has this wrong?
Yes, it's a famous Excel date bug, originally introduced in Lotus 1-2-3 and
ported into Excel for compatibility:
http://support.microsoft.com/kb/214326
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL prepare statement - help
On 9/13/2017 9:20 PM, Papa wrote:
void write(const std::string& table_name, const int pos, const int64_t data) {
...
std::string apstr = "INSERT INTO (";
apstr += table_name.data();
apstr += ", ";
apstr += data;
I'm pretty sure this calls string::operator+=(char), appending to the string a
character whose ASCII code happens to be char(data). Which is unlikely to be
what you had in mind. I suspect you get a warning from the compiler on this
line, about losing significant bits.
Anyway, as already noted, the INSERT statement you are trying to build is not a
valid SQL syntax.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proof that a line has been modified
On 9/7/2017 2:32 PM, Jens Alfke wrote: On Sep 7, 2017, at 10:24 AM, Igor Tandetnik wrote: "Device will refuse to install" is precisely an instance of "security built in at the OS level". Yes, but that's beside the point; it wasn't the relevant part of the example. Any software, privileged or not, can verify the signature and detect whether the binary has been modified. That's what the OP wants. It might be difficult to keep the private key secret. A technician that has direct access to SQLite database file probably also has access to the binary used to manipulate it; and that binary would need the private key lying around someplace accessible. Basically, if you can't trust your own admin personnel, you'd need a kind of military-grade security a la NSA post-Snowden, e.g. requiring two separate people to authenticate before access is granted. That gets pretty expensive pretty quickly. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proof that a line has been modified
On 9/7/2017 1:16 PM, Jens Alfke wrote: On Sep 7, 2017, at 10:06 AM, Simon Slavin wrote: In that case any solution implemented entirely within SQLite is insecure because the admins can simply replace the entire file. Or use a hex editor to replace the checksum values. In cases like this the security the OP is asking for has to be built in at the OS level. No, there are plenty of high level ways of tamper-proofing files. See my initial response. (For example, Apple and Google use digital signatures to tamper-proof applications distributed through their app stores. Any modification to the app binary invalidates the signature, and the device will refuse to install or launch it.) "Device will refuse to install" is precisely an instance of "security built in at the OS level". -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
On 9/5/2017 4:05 PM, Igor Tandetnik wrote: On 9/5/2017 4:00 PM, Stephen Chrzanowski wrote: select count(*) from teaInStock where "Last Used" IS NULL; On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. What is this "hole" of which you speak? This query can very well report a number other than 1 or 0. count(*) returns the number of rows satisfying the condition in the WHERE clause; in this case, the number of rows having NULL in "Last Used" column. In what way do you believe this fails to satisfy the OP's requirements? It's possible I misunderstand what it is the OP is trying to do. But in any case, the query I show is equivalent to the query the OP has shown (which, apparently, does what they want), except formulated in a less roundabout way. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
On 9/5/2017 4:00 PM, Stephen Chrzanowski wrote: select count(*) from teaInStock where "Last Used" IS NULL; On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. What is this "hole" of which you speak? This query can very well report a number other than 1 or 0. count(*) returns the number of rows satisfying the condition in the WHERE clause; in this case, the number of rows having NULL in "Last Used" column. In what way do you believe this fails to satisfy the OP's requirements? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting number of rows with NULL
On 9/5/2017 3:45 PM, Cecil Westerhof wrote:
It is not very important, but I am just curious. I need to know how many
records are not yet used. I do that with:
SELECT COUNT(*) - COUNT("Last Used") AS "Not Used"
FROM teaInStock
Is that the correct way, or is there a better way?
Why not be explicit about what you are trying to do?
select count(*) from teaInStock where "Last Used" IS NULL;
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Yes, NULL is zero, is it?
On 8/31/2017 11:49 PM, J Decker wrote: That and MinGW defines NULL as 0 if C++ and not void(*) I don't see how this is relevant. The OP isn't doing cout << NULL , they are doing cout << binary_sql_statement , where binary_sql_statement is presumably a sqlite_statement* - definitely a pointer. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Yes, NULL is zero, is it?
On 8/31/2017 10:20 PM, Keith Medcalf wrote: Why do you think that a pointer to an arbitrary data block can be sent to cout? Because cout provides operator<<(void*) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Yes, NULL is zero, is it?
On 8/31/2017 9:43 PM, Papa wrote: This is not a SQLite3 problem, it shows to be a MinGW (mingw32/7.1.0/...) -> Target: 64bit, however, I just wanted to know if anyone here has experience the same problem. std::string sql_statement_request; ... rc = sqlite3_prepare_v2(db, sql_statement_request.data(), -1, &binary_sql_statement, NULL); std::cout << binary_sql_statement << std::endl; // ==> 0 (zero) Well, apparently, sqlite3_prepare_v2 call failed. Figure out why. I'm not sure I quite grasp the nature of your difficulty. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using multi-value indexes for subset queries
On 8/9/2017 10:50 AM, Wout Mertens wrote: in experimenting with indexes I found that if you create an index on (a, b) and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index, great. However, if you write SELECT * FROM data WHERE a = 1, it won't use the index. Are you sure? How do you determine that? My experience is different. Case in point, I have an app that allows searching across 4 of parameters, and I am wondering if I could just use a single 4-valued index If you have an index on (a, b, c, d), it'll help for conditions like "a=1" or "a=1 and b=2" or "a=1 and b=2 and c=3" - conditions that use a prefix on an index. But it won't help with "b=2" or "c=3" or "b=2 and c=3". Think of it this way. Imagine you have a phone book, with entries sorted by last name and then first name. In this book, it's easy to find people named "Smith, John", or all people with last name of Smith - but it won't at all help to find all people with first name of John. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] hex and char functions
On 8/7/2017 9:38 AM, x wrote: Related Select hex(char(65,133,66)); returns ‘41C28542’ whereas I expected ‘418542’. What is the ‘C2’ about? Two-byte sequence C2 85 is the UTF-8 encoding of the Unicode codepoint U+0085. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] hex and char functions
On 8/7/2017 9:38 AM, x wrote: In c++ I tried to call a sqlite udf using the following sql UnicodeString SQL=“select udf(‘5\u00856’);” You are using a narrow string literal to try and hold a Unicode character. You are at the whim of your compiler as to how it represents the latter in the former. My guess is, it tries to convert according to the system default code page (I assume Windows here), and since the character is not in fact representable therein, it's converted to '?'. That is, the character was lost before the program even ran, let alone before SQLite got involved. Also, what's UnicodeString? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation, which one is correct ?
On 8/5/2017 5:44 AM, 倪磊 wrote: I verified this through Linux Perf tools. What does that mean? How do these tools convince you that a transaction was committed? Examine the database before and after, using SQLite command line utility. Does "test" table contain one more row after than it did before? That's what matters. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?
On 6/17/2017 10:36 AM, Robert M. Münch wrote: To get a traditional VIEW on this, we want to transpose the data, or create a pivot of it. Here is a pretty simple structure of such a VIEW for the first three columns: For the first three (or any fixed N) columns, yes. But I thought you wanted a view that somehow automatically becomes wider or narrower as rows are inserted or deleted in the underlying table. I don't believe such a thing is possible. That's what I need So you already know the answer to your question, then? What else would you like assistance with? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?
On 6/17/2017 8:17 AM, Robert M. Münch wrote: On 17 Jun 2017, at 14:10, Igor Tandetnik wrote: I don't think so. The number and names of columns in the view are determined at the time CREATE VIEW statement is executed. That won't be a problem as we can update the VIEWs. The question is, how does a query look like that transposes the data from a flat to column structure? By the same token, I don't believe such a query exists. The schema of the resultset can be derived entirely from the syntax of the SELECT statement, without consulting underlying data. So you could call, say, sqlite3_column_count() right after preparing the statement, before it has a chance to look at the actual contents of the table. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?
On 6/17/2017 1:59 AM, Robert M. Münch wrote: Hi, suppose I store my data like this: Table A: table, column, data Now I want to create a VIEW named A.table, with columns A.column and rows A.data Is that possible? I don't think so. The number and names of columns in the view are determined at the time CREATE VIEW statement is executed. I don't believe you can have a view whose schema magically changes as the data in the underlying table changes. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: support for FROM_UNIXTIME
On 6/13/2017 11:21 AM, René Cannaò wrote: I would like to have support for FROM_UNIXTIME() function, as available in MySQL: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_from-unixtime Some background about this feature request. ProxySQL (https://github.com/sysown/proxysql/) is a proxy for MySQL , and it uses SQLite (currently bundled) to store configuration and export metrics using a MySQL interface built on top of SQLite iself. A feature request for ProxySQL was to have FROM_UNIXTIME() available ( https://github.com/sysown/proxysql/issues/758), and this was made available in: * for SQLite 3.15 : https://github.com/sysown/proxysql/commit/b49966a8509f2c85e2507534ed6f1843a654ac81 * for SQLite 3.19 : https://github.com/sysown/proxysql/blob/312a04c73dafc6f5c23bb308c1a70b5f3728899e/deps/sqlite3/from_unixtime.patch Any reason the proxy could not install FROM_UNIXTIME as a custom function ( https://sqlite.org/c3ref/create_function.html )? Why does it need to be hacked directly into SQLite library? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No check for invalid constraints
On 5/27/2017 9:47 AM, Aflah Bhari wrote: I'm using SQLITE version 3.16.0 on macOS Sierra 10.12.4. I'm experiencing a unique bug where if I put in an primary key constraint with underscores then the primary key does not auto increment when I insert values. *For example:* CREATE TABLE sensor_status(status_id INTEGER *PRIMARY_KEY*, status text NOT_NULL UNIQUE); Here, INTEGER PRIMARY_KEY is the type. SQLite accepts pretty much any sequence of characters as the column type - e.g. create table t(col FOO_BAR BAZ); For details, see http://sqlite.org/datatype3.html I'm not sure what the implications of this are on other constraints such as NOT_NULL Ain't no such constraint. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query plan gone haywire lays waste to my library's performance
On 4/28/2017 4:37 PM, Jens Alfke wrote: CREATE TABLE docs (doc_id INTEGER PRIMARY KEY, docid TEXT UNIQUE NOT NULL, expiry_timestamp INTEGER); CREATE INDEX docs_docid ON docs(docid); For the record, this index is redundant. There's already an automatically created index on docs(docid), thanks to UNIQUE clause. This might be what confuses SQLite. See whether the behavior changes if you drop it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unary + isn't disabling use of index
On 4/28/2017 5:30 PM, Jens Alfke wrote: Another query using the wrong plan, even though I’ve added a unary “+” to prevent use of indexes on ‘current’: SELECT sequence, revs.doc_id, docid, revid, deleted FROM revs JOIN docs ON docs.doc_id = revs.doc_id WHERE sequence > ? AND +current=1 ORDER BY revs.doc_id, deleted, revid DESC 0 0 0 SCAN TABLE revs USING COVERING INDEX revs_current 0 1 1 SEARCH TABLE docs USING INTEGER PRIMARY KEY (rowid=?) 0 0 0 USE TEMP B-TREE FOR RIGHT PART OF ORDER BY Where the revs_current index is CREATE INDEX revs_current ON revs(doc_id, current desc, deleted, revid desc); It seems the index is used to implement "ORDER BY revs.doc_id" part. Try "ORDER BY +revs.doc_id" -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
