Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

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 sq

Re: [sqlite] unexpected sqlite_busy behaviour within transactions

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.sqlit

Re: [sqlite] Either a bug or I don't understand SQL update

racters 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

Re: [sqlite] Is sqlite3 smart enough to remove redundant command?

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 __

Re: [sqlite] SQL CASE WHEN THEN ELSE END

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

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

Re: [sqlite] Things you shouldn't assume when you store names

tm ) 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

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

Re: [sqlite] Different column items to fields (transpose)

'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

Re: [sqlite] Possible bug in storing text values in numeric columns

sless" 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 ___ sq

Re: [sqlite] Possible bug in storing text values in numeric columns

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

+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/mai

Re: [sqlite] Standard deviation last x entries

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

Re: [sqlite] multi-multi join/bridge table, table creation questions

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

Re: [sqlite] COMMIT, what locking behaviour when current lock is merely SHARED?

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

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

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

ming 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@mailingl

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

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.sqli

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

Re: [sqlite] Please explain multi-access behaviour to me

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

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

Re: [sqlite] Please explain multi-access behaviour to me

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

Re: [sqlite] CTE to Get Path In a Tree

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

Re: [sqlite] Feature request: FILTER on aggregate functions

ich 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.sqlit

Re: [sqlite] Help with sqlite3_value_text

, 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 __

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

Re: [sqlite] is this possible

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 sq

Re: [sqlite] CASE optimization opportunity (was: filling a key/value table)

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

Re: [sqlite] filling a key/value table

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 sq

Re: [sqlite] SQL question, move union to second column

, 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

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

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

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

Re: [sqlite] Grouping guidance

tion='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

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()

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

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

Re: [sqlite] Group by counting 2 different sub-groups

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

) 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

Re: [sqlite] Group by counting 2 different sub-groups

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

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

Re: [sqlite] How to retrieve table names for the given string

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

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

Re: [sqlite] Time Precision

ing 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 sql

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

Re: [sqlite] column types and constraints

s 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

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

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

Re: [sqlite] Understanding SELECT statement

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-b

Re: [sqlite] [OT} Posting through Gmane

o 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

QLITE_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.sqlit

Re: [sqlite] Only see unused when there are unused records

(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

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.sqlit

Re: [sqlite] MIN() and MAX() of set of row values

umber. 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_

Re: [sqlite] MIN() and MAX() of set of row values

ly 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

es 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

umns. 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 mail

Re: [sqlite] Strange concatenation result

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

Re: [sqlite] Dealing with SQLITE_BUSY

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 _

Re: [sqlite] primary key in another column

? 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

Re: [sqlite] primary key in another column

;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

scarded. 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

Re: [sqlite] primary key in another column

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

Re: [sqlite] Can this be done with SQLite

har(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 ?

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.

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. "&

Re: [sqlite] extracting domain names from website addresses efficiently

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

Re: [sqlite] extracting domain names from website addresses efficiently

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.or

Re: [sqlite] How do I combine these to 2 views ...

y, 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?

t 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

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.

/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

Re: [sqlite] 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

e 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

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

Re: [sqlite] Data types for date and time functions

cel 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

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 <i...@tandetnik.org> 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

Re: [sqlite] Proof that a line has been modified

ir 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

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

Re: [sqlite] Getting number of rows with NULL

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

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?

- 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.sqli

Re: [sqlite] Yes, NULL is zero, is it?

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

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

Re: [sqlite] hex and char functions

, 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 ?

han 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

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

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

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

Re: [sqlite] Feature request: support for FROM_UNIXTIME

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

Re: [sqlite] No check for invalid constraints

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

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

Re: [sqlite] Unary + isn't disabling use of index

dex 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 ma

  1   2   3   4   5   6   7   8   9   10   >