Re: [sqlite] Valid characters for indentifiers

Kees Nuyt wrote: > It conforms to the SQL standard, you can use the Postgresql docs > as a reference. Actually, neither SQLite nor PostgreSQL conform to the SQL standard. The SQL standard requires that delimited identifiers are case sensitive and can contain double quotes, and that undelimited id

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

yaro wrote: > the problem is due to a temp file named "file" that isn't deleted after my > application closes. SQLite does not create temp files named "file". Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://m

Re: [sqlite] bug: explain Rewind Le

Egor Shalashnikov wrote: > create table t(n number, v varchar2(10)); > insert into t values (1, 'one') > explain select * from t where 0 < n; If you omit the EXPLAIN, is the code executed correctly? > addr opcode p1p2p3p4 p5 comment > - -

Re: [sqlite] [BUG] shell: .import: no special characters in table name

Clemens Ladisch wrote: > Simon Slavin wrote: >> On 19 Oct 2017, at 7:38pm, Clemens Ladisch wrote: >>> the .import command does not work (and can show misleading error messages) >>> if the table name contains special characters: >>> >>> sqlite> .impo

Re: [sqlite] [BUG] shell: .import: no special characters in table name

Simon Slavin wrote: > On 19 Oct 2017, at 7:38pm, Clemens Ladisch wrote: >> the .import command does not work (and can show misleading error messages) >> if the table name contains special characters: >> >> sqlite> .import test.csv temp.t > > What format is th

[sqlite] [BUG] shell: .import: no special characters in table name

Hi, the .import command does not work (and can show misleading error messages) if the table name contains special characters: sqlite> .import test.csv temp.t Error: no such table: temp.t sqlite> select * from temp.t; sqlite> This appears to be caused by inconsistent quoting of the table

Re: [sqlite] Grouping and grabbing one item

no...@null.net wrote: > On Wed Oct 18, 2017 at 07:57:24PM +0200, Darko Volaric wrote: >> select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), >> max(ed),target, sum(amt) from Tasks where Pid=1 group by target >> HAVING amt > 0; > > When I first read that query I wondered if putting a que

Re: [sqlite] XOR operator

R Smith wrote: > I meant a binary operation, not a Boolean operation NOT. "NOT X" = "-X - 1" > Answering (2): A strongly typed language that defines INT/UINT/WORD/ > INT64/etc. as specifically a 32-bit or 64-bit signed/unsigned > representation, or "Byte" as a 8-bit unsigned representation will b

Re: [sqlite] XOR operator

Alex Henrie wrote: > I wanted to use the XOR operator in a query today, but then found out > that SQLite doesn't support it. For boolean values, "a XOR b" = "a <> b". For binary values, "a XOR b" = "(a | b) - (a & b)". Regards, Clemens ___ sqlite-users

Re: [sqlite] Proposed registration for application/vnd.sqlite3 and +sqlite3 (was: Why is Sqlite mediatype not registered at iana)

Keith Medcalf wrote: > I should that the Security Implications are NONE. > > There are no security implications in setting a MIME type for "magic > number" containing SQLite3 databases. RFC 6838 § 4.6 says that | the security considerations MUST NOT state that there are "no security | issues assoc

Re: [sqlite] Proposed registration for application/vnd.sqlite3 and +sqlite3 (was: Why is Sqlite mediatype not registered at iana)

Macintosh file type code: none Contact: SQLite mailing list Intended usage: COMMON Restrictions on usage: none Author/Change controller: Clemens Ladisch Provisional registration? (standards tree only): N/A ==

Re: [sqlite] Seg fault with core dump. How to explore?

Kevin O'Gorman wrote: > my latest trial run ended with a segmentation fault Really a segmentation fault? What is the error message? > This particular program is merging two databases. The result has reached > 25 GB, roughly 1/3 of what I expect of the final result (over 100M rows). > The filesy

Re: [sqlite] CREATE FOREIGN KEY support

Igor Korot wrote: > 3. DROP TABLE ; > > On step 3 all ttriggers and indexes will be dropped as well, right? Yes. > 4. CREATE TABLE (, FOREIGN KEY() REFERENCE pkTable() ) AS SELECT * > FROM temp; > > what would be the best way to read [triggers and indexes] and apply to the > step 4? Run .schem

Re: [sqlite] ADO.NET driver upgrade puzzle

Roberts, Barry (FINTL) wrote: > As per my original post, all C# access code is making extensive use of > "using" statements. However we do obviously rely on the connection pool > being thread safe, because many threads are writing to different > databases (connections) concurrently. > > There is no

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

David Wellman wrote: > The question has come up as to how we should store date/time values in our > tables? Basically how should we define our date/time columns? SQLite does not have a separate date/time type. If you want to use the built-in date/time function, you can store values in one of thre

Re: [sqlite] ADO.NET driver upgrade puzzle

Roberts, Barry (FINTL) wrote: > The application is multi-threaded > > the system sometimes locks up, or marks one or more of the db3 as malformed. Sounds like a threading problem. You should not have more than one thread accessing the same connection concurrently. > Using (connection) > Open con

Re: [sqlite] Support for named foreign keys?

Csányi Pál wrote: > when will SQLite support the named foreign keys? It does support named foreign keys: CREATE TABLE t ( id PRIMARY KEY, k1 CONSTRAINT name_here REFERENCES t, k2, CONSTRAINT another_name FOREIGN KEY(k2) REFERENCES t ); Regards, Clemens __

Re: [sqlite] Is this behavior expected?

Baruch Burstein wrote: > sqlite> select last_insert_rowid(); > 2 > sqlite> rollback; > sqlite> select last_insert_rowid(); > 2 > > In other words, the rollback doesn't roll back the rowid. says: | For the purposes of this routine, an INSERT is c

Re: [sqlite] Running query in command window is slow

Frank Millman wrote: > I have a fairly complex query. If I execute it using Python, it takes > 1 second. If I copy and paste it into the Sqlite3 command window, it > takes 10 minutes. Probably different SQLite versions. What is the EXPLAIN QUERY PLAN output in both cases? Regards, Clemens _

Re: [sqlite] Create table - Error code: 21

Papa wrote: > // *** THE ERROR IS HERE > this->rc = ::sqlite3_prepare_v2(db, > convert->toString(sql_statement_request).c_str(), -1, &binary_sql_statement, > NULL); > if (this->rc != SQLITE_OK) { > this->apstr = "Error message from SQLite3 "; > thi

Re: [sqlite] SqLite Metadata information

K, Rajasekar wrote: > I need to get some information about the database I have created. Why? What problem are you going to solve with this information? In addition to Simon's remarks: > 1. Charset - character set used in the database Please note that this setting affects only how text v

Re: [sqlite] sqlite3_stmt limitations

heribert wrote: > "Not very much. But preparing a statement is very fast; don't try to be too > clever." > > What do you mean with "don't try to be too clever"? Is preparing for reuse > not really necessary? In many cases, the difference will not be noticeable. I was warning against adding com

Re: [sqlite] sqlite3_stmt limitations

heribert wrote: > The threads prepares their own sqlite3_stmt's with select statements > to the properties currently needed A single statement "SELECT Value FROM T WHERE Name = ?" might suffice. > Is their any limitation of sqlite3_stmt bound to a database? Only memory. > How much memory is use

Re: [sqlite] SQLite Error while trying to BackupDatabase

Karthi M wrote: > SQLite notice (27): delayed 1375ms for lock/sharing conflict at line 42155 > SQLite error (14): os_win.c:42162: (5) winOpen(\\Server6166\2.0\testdb.db) > - Access is denied. Somebody else was accessing the file at the same time. This was probably another database connection, o

Re: [sqlite] CTE question...

Brian Curley wrote: > WITH cte_name --(my_row, code_key) > AS ( >SELECT-- Base record > 1my_row > ,(SELECT >max(code_key) > FROM >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Nico Williams wrote: > I would much prefer to be able to specify which CTEs must be materialized, > and which may be left as internal views. That would give the user a great > deal of control. WITH x AS () MATERIALIZED ... . "Materialized" is the wrong word; you want to prevent only subquery fla

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Jens Alfke wrote: > can someone please tell me how to hoist/factor out the subexpression manually > then? Move the subexpression into a subquery, then prevent subquery flattening (http://www.sqlite.org/optoverview.html#flattening) by violating one of the listed constraints. (These rules might ch

Re: [sqlite] Intel 17

Richard Hipp wrote: > Please try the latest trunk version of SQLite and let me know if it > works for you. I don't have the Intel compiler, but the sign is missing: SELECT CAST('-1e359' AS NUMBER), CAST('-1e360' AS NUMBER); -Inf|Inf I take everything back and state the opposite: I guess an

Re: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

Howard Kapustein wrote: > /* Try to truncate the WAL file to zero bytes if the checkpoint > ** completed and fsynced (rc==SQLITE_OK) and we are in persistent > ** WAL mode (bPersist) and if the PRAGMA journal_size_limit is a > ** non-negative value (pWal->mxW

Re: [sqlite] Intel 17

Dominique Devienne wrote: > sqlite3.c(17654): error #265: floating-point operation result is out of range Could this error be reduced to a warning? > // around line 17644 > - result = 1e308*1e308*s; /* Infinity */ > + result = 1e308*(1e308*s); /* Infinity */ C99 7.12 () says: | 4 The macro INFIN

Re: [sqlite] Selecting data from WITH clause is very slow

Frank Millman wrote: > SELECT acc_no, acc_name, > (SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date <= date_5) > AS 120_days, > (SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date > date_5 > AND invoice_date <= date_4) AS 90_days, > (SELECT SUM(invoice_ba

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

R Smith wrote: > I am using 151 columns for both tests. The only thing that changes > between the two scripts are the words "WITHOUT ROWID" being added says: | WITHOUT ROWID tables will work correctly ... for tables with a single | INTEGER PRIMARY KEY. How

Re: [sqlite] Geeting degrade while using multhi threading

Karthi M wrote: > "Internally SQLite uses locks to serialize calls by multiple threads." > if this is case then how can we achieve concurrency? In general, SQLite serializes accesses to the same connection object. To get higher concurrency (for reading), use multiple connections. Regards, C

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

Paxdo wrote: > For security reasons, a customer wants to be sure that a database line > cannot be modified after its initial insertion (or unmodified without > being visible, with proof that the line has been modified). Including > by technicians who can open the database (SQLITE of course). Somet

Re: [sqlite] Performance impact of UPDATEing multiple columns vs few columns

ghalwasi wrote: > what will be the difference (in context of CPU cycles & performance) > if i UPDATE multiple columns or only few columns. SQLite always rewrites the entire row, so there is no practical difference. Regards, Clemens ___ sqlite-users mai

Re: [sqlite] Join, Union, Subquery or what?

Joe wrote: > my SQLite database has two tables Katalog and ZKatalog with the same > structure. One of the columns is called DDatum. What's the most efficient > way to > > (1) Select records, which are only in Katalog, but not in ZKatalog? SELECT * FROM Katalog EXCEPT SELECT * FROM ZKatalog; >

Re: [sqlite] Sharing data between desktop and Android

Cecil Westerhof wrote: > I am thinking about writing some Android applications. I would like to > share data between the phone (or tablet) and de desktop. What is the best > way to do this? In a way that would also be convenient for other people. There is no good way to go over the USB connection

Re: [sqlite] Issue with updating database content (C++)

Ali Dorri wrote: > I encode the PKs to base64 > [...] > The PK is a BLOB type, i.e., >sql = "CREATE TABLE BC(" \ > "PKBLOB," \ Why do you store a text value in a blob field? > "UPDATE BC set Signature = null and PK = null where PK = '%q' ; That does not up

Re: [sqlite] Output of pragma optimize?

Bart Smissaert wrote: > Looking at the results of sqlite3_column_count (1) and also > sqlite3_column_name (optimize) it > seems that pragma optimize can have an output. What would that output be > and what would be a way to show such ouput? says:

Re: [sqlite] Why is Sqlite mediatype not registered at iana

Paul Van Genuchten wrote: > Why does sqlite not have an official mediatype, eg. Application/vnd.sqlite, > and why is it not registered at iana Because nobody has bothered to register it. Are you volunteering? Please note that SQLite is often used as a platform, i.e., knowing that the content is

Re: [sqlite] GROUP_CONCAT separator and DISTINCT

Dave Blake wrote: > It seems that it is not possible to specify the concatenation separator > when using GROUP_CONCAT with DISTINCT. The documentation says: | In any aggregate function that takes a single argument, that argument | can be preceded by the ke

Re: [sqlite] Do you really need an ORDER BY after a GROUP BY

Cecil Westerhof wrote: > EXPLAIN QUERY PLAN SELECT used, COUNT(used) FROM usedProverbs GROUP BY used > ORDER BY used; > 0|0|0|SCAN TABLE proverbs USING INDEX proverbs_used_idx > > EXPLAIN QUERY PLAN SELECT used, COUNT(used) FROM usedProverbs GROUP BY used; > 0|0|0|SCAN TABLE proverbs USING INDEX p

Re: [sqlite] Do you really need an ORDER BY after a GROUP BY

Cecil Westerhof wrote: > 2017-08-24 11:29 GMT+02:00 Clemens Ladisch : >> Cecil Westerhof wrote: >>> But when I leave the ORDER BY out in this case, the result is the same, but >>> it looks like it is a bit faster. >> >> Are you sure? How does the output of E

Re: [sqlite] Date time functions not working

Matthew Halliday wrote: > select servername, drive, strftime(date_time, -2) as iDATE, SUM(diff_used) AS > DailyUsed > from tmp_dspace_import > group by servername, drive; When there are multiple table rows in a group, which date should be returned? Regards, Clemens _

Re: [sqlite] Upgrade to SQLite 3.20.0 im Mozilla - Consequences for Mozilla Thunderbird

Jörg Knobloch wrote: > Mozilla have upgraded to SQLite 3.20.0 and that has caused the > Thunderbird test suite some test failures [1] as follows: > > Assertion failed: (p->flags & MEM_Dyn)==0 || p->szMalloc==0, file sqlite3.c, > line 70285 Hmm, is there a custom memory allocator somewhere? And on

Re: [sqlite] Do you really need an ORDER BY after a GROUP BY

Cecil Westerhof wrote: > I always use an ORDER BY after a GROUP BY. Without an ORDER BY, there is no guarantee that the result has any specific order (in SQLite and in any other SQL database). > For example: > SELECT used > ,COUNT(used) > FROM usedProverbs > GROUP BY used > ORDER BY

Re: [sqlite] Date time functions not working

Matthew Halliday wrote: > SELECT strftime('%Y-%m-%d',date_time , '-1 days') as DATE from > tmp_dspace_import > > If I run it as a stand-alone single statement it works. > > If I run it as part of a longer query I get either just 131 rows of just > today's data or a collumn of NULL values. Obviousl

Re: [sqlite] Comparing rows

Matthew Halliday wrote: > Unfortunately is comlains after "SET diff_used," and I get "near ",": > syntax error: " Then your SQLite is too old; row values require 3.15 or later. > it won't diferentiate between servers and drives. Oops! > However this does appear to have worked! Seems a bit long

Re: [sqlite] Comparing rows

Matthew Halliday wrote: > I used the SQLite Studio to create the table so used the DATETIME data type > for that, and although I used -mm-dd hh:mm:ss in the script That is correct. > it seems to have reverted it to dd/mm/yy hh:mm:ss. That would not be usable. Check the actual format with the

Re: [sqlite] SQLite Update With CTE

John McMahon wrote: > should be > UPDATE CUSTOMERS -- remove 'as c' > SET > cust1= (select customer from test where custnum = CUSTOMERS.custnum), > WHERE custnum IN (SELECT custnum FROM test) Yes. Regards, Clemens ___ sqlite-users mailing list

Re: [sqlite] Comparing rows

Matthew Halliday wrote: > I have a simple import table: id, servername, drive, capacity, used_mb, > free_mb, free_pc (%) and a date_time field. What is the format of the values in the date_time field? Is there always a constant offset between two consecutive timestamps? Regards, Clemens ___

Re: [sqlite] SQLite Update With CTE

John McMahon wrote: > UPDATE CUSTOMERS as c > SET > cust1= (select customer from test where custnum = c.custnum), > cust2= NULL, > street = (select address from test where custnum = c.custnum), > town = (select town from test where custnum = c.custnum), > post

Re: [sqlite] PRAGMA table_info could not update schema

sanhua.zh wrote: > I find that `PRAGMA table_info(tableName)` will not check the expired schema > which is modified by other sqlite connections. > > Here is the sample code: That code is incomplete and buggy. (Heed the compiler warnings!) Anyway, I can reproduce this with two command-line shell

Re: [sqlite] Why the high cost of a double sort

Simon Slavin wrote: > On 19 Aug 2017, at 10:48pm, Cecil Westerhof wrote: >> I was also told that you never should put a sort on a view. Is that true, >> or a bit to strong? > > Generally, you put the ORDER BY on the SELECT you’re using the consult > the VIEW. Technically speaking a VIEW is just a

Re: [sqlite] Why the high cost of a double sort

Cecil Westerhof wrote: > I have the following query: > SELECT used > FROM usedProverbs > LIMIT 1 > > The view useProverbs is defined as: > CREATE VIEW usedProverbs AS > SELECT * > FROM proverbs > WHERECAST(used AS INT) <> 0 > ORDER BY used ASC > > But I

Re: [sqlite] offset of file position is beyond EOF

Jacky Lam wrote: > I recently meet a case that the file position offset of a pager is beyond > EOF position of db. says: | The lseek() function shall allow the file offset to be set beyond the end | of the existing data in the

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

Jay Kreibich wrote: > On Aug 18, 2017, at 5:33 AM, Clemens Ladisch wrote: >> sanhua.zh wrote: >>> 1. Conn A: Open, PRAGMA journal_mode=WAL >>> 2.ConnB: Open, PRAGMA journal_mode=WAL >>> 3.ConnA: CREATE TABLE sample (i INTEGER); >>> 4.ConnB: PRAGMA tabl

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

sanhua.zh wrote: > 1. Conn A: Open, PRAGMA journal_mode=WAL > 2.ConnB: Open, PRAGMA journal_mode=WAL > 3.ConnA: CREATE TABLE sample (i INTEGER); > 4.ConnB: PRAGMA table_info('sample') > > Firstly, both thread 1 and 2 do initialization for their own conn, which is > to read to schema into memory. >

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

Gwendal Roué wrote: >> Le 16 août 2017 à 08:38, Clemens Ladisch a écrit : >> Gwendal Roué wrote: >>> Serialized accesses from multiple threads is OK when the connection is >>> in the "Multi-thread" or "Serialized" threading modes, but not in t

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

Gwendal Roué wrote: > Serialized accesses from multiple threads is OK when the connection is > in the "Multi-thread" or "Serialized" threading modes, but not in the > "Single-thread" threading mode. says: | 1. *Single-thread*. In this mode, all mutexes are d

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

sanhua.zh wrote: > All 1. 2. 3. steps are run sequentially, which means that the step 2 > runs after step 1 finished and step 3 runs after step 2 finished > theoretically . > Also, I can make sure the memory order between threads. > > Then, is it a safe way to use sqlite connection ? Yes. Multi-t

Re: [sqlite] Fwd: How can I make this faster?

J Decker wrote: > So I have this sql script that inserts into a single table, and it is VERY > slow. Wrap everything into a single transaction. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglist

Re: [sqlite] What's the level of B+-Tree ?

james ni wrote: > the INSERT speed is becoming slower and slower; > > the number of syscalls are increasing quickly; Insert the largest values last. Increase the cache size: . Decrease the amount of data stored in the index. (This is unlikely

Re: [sqlite] What's the level of B+-Tree ?

james ni wrote: > As in the example that I provided, there are 4 cells in a single btree > page. So there must be some mechanism to determine hoe many keys that > one cell can own. One key per cell: | Within an interior b-tree page, each key and the pointer to its | immediate left are combined int

Re: [sqlite] What's the level of B+-Tree ?

ni james wrote: > In the "SQLite File Format" document, the BTree layout is described, > but now I want to know how to get the BTree level (which is the 'K' > value mentioned in the Documentation)? At the end of section 1.5, a "K" is defined. But I don't think that is the same K. Anyway, the doc

Re: [sqlite] Packing integer primary key with field bits

x wrote: > I’m thinking about this more from the gain in speed rather than saving space. Database performance is usually limited by I/O, i.e., you gain speed by saving space. > I have done my homework on this So what are the results of your measurements? Regards, Clemens __

Re: [sqlite] Optimizing searches across several indexes

Wout Mertens wrote: > I have a table with a bunch of data (in json). I want to search on several > values, each one is indexed. However, if I search for COND1 AND COND2, the > query plan is simply > > SEARCH TABLE data USING INDEX cond1Index (cond1=?) > > Is this normal? Yes. A query can use only

Re: [sqlite] TEXT shows as (WIDEMEMO) in DBGrid

Lars Frederiksen wrote: > I have followed 2 tutorials about SQLite, and none of these mentioned the > VARCHAR() possibility Because SQLite pretty much ignores column types. Interpreting "VARCHAR" this way is how FireDAC does things; you have to look into the FireDAC documentation. > But I al

Re: [sqlite] TEXT shows as (WIDEMEMO) in DBGrid

Lars Frederiksen wrote: > CREATE TABLE gms( > gms_id INTEGER PRIMARY KEY, > gms_verb TEXT NOT NULL > ); > > FDTable1.Append; > FDTable1.FieldByName('gms_verb').AsString:= Edit1.Text; > FDTable1.Post; > > But when I put a string in the table 'gms_verb' I only get the primary key > number - the strin

Re: [sqlite] SQLITE PHP syntax issues - INSERT INTO db

Edmondo Borasio wrote: > //If I input the $NewID manually as a string (for example '6', *it works > fine* and updates the db correctly) > *$query1="INSERT INTO > Table"."(ID,name,surname)"."VALUES('6','newName','newSurname');"; * > > //However if I try to use the $NewID variable from above *it does

Re: [sqlite] Problem with the new pointer-passing interface

Ulrich Telle wrote: > In the comment of the sqlite3_bind_pointer() routine I found this note: > > The T parameter should be a static string, preferably a string literal. > > In my case this is quite cumbersome, since in my wrapper I have to extract > the value of parameter T from a temporary string

Re: [sqlite] Poll: Include the recent sqlite3_column_name() fix in the upcoming 3.20.0 release?

Richard Hipp wrote: > Cons: (5) The change might cause breakage for legacy applications that > depend on the older (arguably buggy) behavior. (6) This seems like a > big change to receive so little beta exposure prior to the official > release. I doubt that there are many applications that both d

Re: [sqlite] Does it make sense to COMMIT/ROLLBACK?

Igor Korot wrote: > If I execute "BEGIN TRANSACTION" and for whatever reason the call will fail > will I get an error on COMMIT/ROLLBACK execution? sqlite> begin immediate; Error: database is locked sqlite> rollback; Error: cannot rollback - no transaction is active Regards, Clemens

Re: [sqlite] UPDATE database using parameters

Edmondo Borasio wrote: > $stmt->bind_param($p_name,$bind_value); This looks like PHP's MySQL driver. Which DB are you actually using? Anyway, I recommend you start with the examples from the manual, e.g., : $stmt = $db->prepare('SELECT b

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

Rowan Worth wrote: > On 18 July 2017 at 21:43, David Raymond wrote: >> You can run "begin deferred transaction" then walk away for 3 months >> without upsetting anything. If you need the precise timing then why not >> just use "begin immediate"? > > IMMEDIATE would take a RESERVED lock which is cl

Re: [sqlite] Summarising (missing) values

Paul Sanderson wrote: > What I would like is a single query that summarises the values that are > present in (or missing from) a table. A row is the start of a range if there is no previous row: WITH ranges(first) AS ( SELECT _id FROM messages WHERE _id - 1 NOT IN (SELECT _id FROM m

Re: [sqlite] marketing - The world's most popular open source database

Darren Duncan wrote: > I was reminded today that MySQL still prominently advertises themselves > as "The world's most popular open source database", on their website > and in their product announcements etc. > > However, isn't that claim clearly wrong, given that SQLite for one has > way more insta

Re: [sqlite] Disable trigger?

Thomas Flemming wrote: > Is there a way in SQLite, to temporary disable and enable a trigger without > dropping and recreating ? You could disable recursive triggers, and then make your triggers recursive by executing all your SQL statements through a temporary trigger on some temporary table. B

Re: [sqlite] Undo-Redo

Thomas Flemming wrote: > I'm busy doing a undo-redo with triggers as described here: > https://sqlite.org/undoredo.html > > and wondering, if there is a function to build the string for the > insert-command automatically with all fields from that table. You have to write this function yourself.

Re: [sqlite] Suggestion for PRAGMA SYNCHRONOUS=0 documentation

Rowan Worth wrote: > I do think it's worth a mention in the PRAGMA SYNCHRONOUS=0 documentation > that sqlite3_close() doesn't propagate I/O errors so you might never hear > about write failures in this mode. When writing asynchronously, errors can be detected _after_ the close, so this is not even

Re: [sqlite] Version 3.20.0 coming soon...

Eric Grange wrote: >> Could I suggest that the STMT virtual table be renamed as something like >> sqlite_statements ? >> Or anything else that starts with "sqlite_" ? > > Seconded, especially as more and more virtual tables become available (a > good thing), there is a greater risk of name collisi

Re: [sqlite] Is option -interactive (force interactive i/o) working correctly?

petern wrote: > $ echo "SELECT ('Shouldn''t SQLite shell continue interactively after > processing this statement?')msg;" >/tmp/slsh_in > $ > > Results at first terminal after echo line is sent from second terminal: > > $ #Interactively run sqlite3 from named pipe. > $ mkfifo /tmp/slsh_in > $ ./sql

Re: [sqlite] Remotely use Database File

Milav Soni [Teq Diligent] wrote: > I want to connect/open/read/write the Sqlite3 Database File through > Remotely (using "ssh"). SQLite accesses database files as files. So if you want to open a database through SSH, you have to use a file system that works through SSH. (There is sshfs, which d

Re: [sqlite] VALUES clause quirk or bug?

petern wrote: > I was hoping someone could shed light on what is actually going on in the > VALUE clause. VALUES (a, b), (c, d) ... is actually just a shortcut for SELECT a, b UNION ALL SELECT c, d ... If you want to control the column names, you have to use the second form with AS. Regar

Re: [sqlite] Any way to avoid scanning a large table several times?

Dominique Devienne wrote: > It's a group-by query, so despite using the index, all rowids for the only > 4 different "index entries" must still be counted, > and that's still definitely longer to do that than full scanning the table > once. So why using GROUP BY? The top-level query does not real

Re: [sqlite] Any way to avoid scanning a large table several times?

Dominique Devienne wrote: > On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch wrote: >> You could put kcounts into a temporary table. > > I could it in a table, but then who updates kcounts when keys (or keys_tab) > changes? I did not say "table" but "temporary

Re: [sqlite] Hierarchical Queries with Looping in SQLite DB

Ashif Ahamed wrote: > I 'm trying to achieve , oracle's hierarchical CONNECT BY NOCYCLE PRIOR in > SQLite database hierarchical queries i.e. with clause queries(common table > expression) > > When there is a loop in the data ,with clause queries in SQLite database is > running for long with infinit

Re: [sqlite] List of Warnings when compiling SQLite

bdoom wrote: > https://pastebin.com/muEvCTz2 > sqlite3.c(16979,5): warning : 'SQLITE_4_BYTE_ALIGNED_MALLOC' is not defined, > evaluates to 0 [-Wundef] > 2>#if SQLITE_4_BYTE_ALIGNED_MALLOC > 2>^ > Is this anything to worry about? No. The C standard says that in the #if expression, identifie

Re: [sqlite] Any way to avoid scanning a large table several times?

Dominique Devienne wrote: > with > kcounts(t, c) as ( > select code_type, count(*) >from keys > group by code_type > ), > ... > select ..., >(select c from kcounts where t = 'SET') as "#sets", >(select c from kcounts where t = 'CST') as "#constants", >(select c from

Re: [sqlite] syntax error near AS

John McMahon wrote: > an alias for an "UPDATE" table name is not permitted. Is there a particular > reason for this? The UPDATE statement affects a single table. While an alias might be a convenience, it is not necessary (any naming conflicts in subqueries can be resolved by using an alias on th

Re: [sqlite] FOREING KEY constraint

J. King wrote: > The sqlite_master table should have this information. > > SELECT count() FROM sqlite_master WHERE name IS your_constraint_name AND > tbl_name IS your_table_name; Constraints do not have separate entries in the sqlite_master table. And there is no other mechanism to get this infor

Re: [sqlite] Doc bug: wal.html should mention proper way to remove wal and shm files

積丹尼 Dan Jacobson wrote: > file:///usr/share/doc/sqlite3-doc/wal.html should mention the common case > where > the user (let's say someone from a different project) has encountered > cookies.db cookies.db-shm cookies.db-wal > left behind by some program. User wishs to clean this up, leaving only

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

Robert M. Münch wrote: > Is this behaviour standard or a SQLite variant? Autoincrementing is an SQLite variant. Default values are standard SQL. It should be noted that standard SQL (above Entry SQL level) allows DEFAULT in row value constructors. Regards, Clemens __

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

Simon Slavin wrote: > On 27 Jun 2017, at 8:13pm, Robert M. Münch > wrote: >> CREATE TABLE test(a, b DEFAULT "-", c, d) >> >> Now I would like to use >> >> INSERT VALUES(a,?,c,d) >> >> Where ? is something that the default value is used and not the provided >> value. Is this possible at all? > >

Re: [sqlite] operator precedence

x wrote: > Is there a reason why sqlite doesn’t follow the c convention? Yes. That reason is named "ISO/IEC 9075", but commonly called "the SQL standard". Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://maili

Re: [sqlite] Providing incrementing column to query

Paul Sanderson wrote: > I Have a number of queries to which I want to supply an incrementing column, > some of these queries involve without rowid tables. > > Is there a way of doing this with a SQL query? First, define a sort order, and ensure that it does not have duplicates. Then use a correlat

Re: [sqlite] UTF8-BOM not disregarded in CSV import

Mahmoud Al-Qudsi wrote: > with `.import ……`, SQLite3 includes a BOM (UTF-8) as part of the first > column of the first record. The Unicode Standard 9.0 says in section 3.10: | When represented in UTF-8, the byte order mark turns into the byte | sequence . Its usage at the beginning of a UTF-8 data

Re: [sqlite] sqlite3_exec statement count including create/drop?

petern wrote: > Regarding sqlite3_trace_v2(db, SQLITE_TRACE_PROFILE,...). Calls to > sqlite3_sql() on the third trace callback parameter (cast to sqlite_stmt*) > do retrieve the correct part of the the original sqlite3_exec input SQL > string except for ill formed SQL. Apparently the SQLITE_TRACE

Re: [sqlite] sqlite3_exec statement count including create/drop?

petern wrote: > linker says -> "undefined reference to `sqlite3_trace_v2'" Then your distribution's package appears to be compiled with SQLITE_OMIT_TRACE. Just add sqlite3.c to your project. Regards, Clemens ___ sqlite-users mailing list sqlite-users@

Re: [sqlite] sqlite3_exec statement count including create/drop?

petern wrote: > Is there a C API way to get a total count or notification as each statement > is prepared by sqlite_prepare_v2 within sqlite3_exec? sqlite3_trace_v2() with SQLITE_TRACE_PROFILE is called at the end of each statement. Regards, Clemens __

<    1   2   3   4   5   6   7   8   9   10   >