Re: [sqlite] [EXTERNAL] UPSERT

No. Insert or replace will delete any and all records that violate constraints, and then insert a new record. This may cause ON DELETE CASCADE foreign key constraints to fire. Do it in code as you suggested yourself. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Indexing multiple values per row

So in your application you don't care about when a temperature was measured? Creating a table temps (patient_id, timestamp, temperature) with an index on (timestamp,temperature) would yield a fast way to access patients with elevated temperatures within a time frame. Other than that, using

Re: [sqlite] [EXTERNAL] Boolean casting

Since there is no BOOLEAN dataype in SQLite, and BOOLEAN is assigned NUMERIC affinity, the sqlite3_column_type() function must be returning SQLITE_INTEGER (because storing 0 and 1 as integers is preferred over floats). So how does the getColumnType() function determine a datatype of BOOLEAN? My

Re: [sqlite] [EXTERNAL] count optimisation

The statement "select count() from " is optimized to retrieve the count without visiting each row. This is well documented behaviour. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Donnerstag, 15. März 2018

Re: [sqlite] [EXTERNAL] Re: Selecting a non-unique column name in a WHERE clause

Using the shortcut '*' for a field list only work if you don't care about column names. SQLite will pick column names for you that may or may not be intuitive and that may change between releases of SQLite. As soon as you find yourself trying to guess column names, you should throw away '*'

Re: [sqlite] [EXTERNAL] what internal table contains column names in a table?

Pragma table_info -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von mike otwell Gesendet: Donnerstag, 01. März 2018 15:50 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] what internal table contains

Re: [sqlite] [EXTERNAL] Re: High performance and concurrency

Use 1 connection for each thread. Sharing a connections between threads may cause transactions to be larger than each thread thinks. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Shevek Gesendet: Donnerstag, 01. März

Re: [sqlite] [EXTERNAL] Re: Strange concatenation result

Just to make things clear, I am not on the SQLite dev team, so I have no influence on the documentation. I am just extrapolating from experience. What should substr('abcd',0,-2) return? 'cd' or just 'd'? Or maybe just an empty string? If you start numbering at the left with 1, 0 is just left

Re: [sqlite] [EXTERNAL] Re: Strange concatenation result

"The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left." The substr(x,y,z) function is defined only for nonzero values of y. SQlite can return whatever it feels like if you insist on

Re: [sqlite] [EXTERNAL] "Database is locked" in diagnostic tools C#

un...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Friday, 16 February 2018 5:36 PM To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] [EXTERNAL] "Database is locked" in diagnostic tools C# The "database is locked" message me

Re: [sqlite] [EXTERNAL] "Database is locked" in diagnostic tools C#

The "database is locked" message means that the transaction was unable to complete within the designated busy timeout and was therefore rolled back. Getting this message implies that you are running multiple processes. Depending on the journal mode, a writer process may block both readers and

Re: [sqlite] [EXTERNAL] Re: Question about threadsafe

IF the error codes are checked AND two writers are simultaneously able to write to the db file, THEN the file locking is probably broken in the file system layer (e.g. network file systems), which SQLite can do nothing about. In Journal mode, the first process to acquire a RESERVED lock will

Re: [sqlite] [EXTERNAL] Question about threadsafe

If you wrap your INSERTS into a single transaction, then one process wins and the loser waits until the winner is finished. If each INSERT is ist own (automatic) transaction, the processes will win/loose on each single INSERT, giving the appearance of two simultaneous writers, while actually

Re: [sqlite] [EXTERNAL] question about covering index

SQLite can only use a covering index whose prefix satifies the WHERE and/or ORDER BY clause(es). WHERE x=1 ORDER BY y The WHERE constraint can be handled by an index that starts off with x. The ORDER BY can be handled by an index that starts off with y. SQLite *may* realise that an index on

Re: [sqlite] [EXTERNAL] Question about threadsafe

More than one concurrent writer is not supported. WAL mode only allows readers to see the state of the db as it was at the start of their transaction while writers' changes are written to the WAL file. Threadsafe refers to the interoperation of multiple threads within a single process. Single

Re: [sqlite] [EXTERNAL] How to parameterize a loadable extension at runtime

You can pass parameters to the xCreate function in the CREATE VIRTUAL TABLE statement, if the setting you desire remains unchanged during the lifetime of the table. CREATE VIRTUAL TABLE USING [ ( ,...) ]; You can declare hidden fields in the call to sqlite3_declare_vtab() call within your

Re: [sqlite] [EXTERNAL] Re: Optimization - don't understand.

I think you are optimizing the performance of a conceptually inefficient query. If you are looking for a recipe that contains apples, do you read the entire cook book, checking each recipe for apples? Maybe it is much more efficient to look up apples in the index of ingredients and retrieve

Re: [sqlite] [EXTERNAL] Optimization - don't understand.

The additional fields are not contained in the index idxPostsThreadUser, so SQLite is forced to read the original row instead of just the index. Your query is searching the complete posts table, joining all the threads, tags and users together, and then discarding those without a matching tag.

Re: [sqlite] [EXTERNAL] Re: Easiest way to get day of week as a string (not a number)?

Waiting for someone to post a CTE solution along the lines of WITH weekday(dayno,dayname) AS (SELECT (0,'Sun) ) .. SELECT dayname FROM weekday WHERE dayno = strftime(...). -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag

Re: [sqlite] [EXTERNAL] sqlite3_set_last_insert_rowid

See http://sqlite.org/c3ref/last_insert_rowid.html "Some virtual table implementations may INSERT rows into rowid tables as part of committing a transaction (e.g. to flush data accumulated in memory to disk). In this case subsequent calls to this function return the rowid associated with these

Re: [sqlite] [EXTERNAL] Groups in C API

The feature in last paragraph is already the case with plain ORDER BY. All records in "group" order. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Stephan Buchert Gesendet: Dienstag, 30. Jänner 2018 12:17 An:

Re: [sqlite] [EXTERNAL] Groups in C API

SQLite is special WRT to non-aggregate columns in aggregate queries. Most other engines will not allow columns that are neither group fields nor aggregated fields. SQLite OTOH does, and it promises that these fields are "related" to the group tuple. Consider: CREATE TABLE letters (type

Re: [sqlite] [EXTERNAL] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

This orders the results by the constant expression bound to the parameter, which *may* mean that they are returned in the order that arises from the selected query plan *if* the sort is "stable" (i.e. records that collate the same are returned in input order). Binding a number here is NOT

Re: [sqlite] [EXTERNAL] Groups in C API

What do you mean with groups ending and starting? The GROUP BY clause has the effect of returning one record per group (i.e. distinct tuple of the group expression), with the accumulated values belonging to that group. Each result record is therefore in a separate group. SQLite will (except

Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'

An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins' Are you sure you’re using 3.22 Gunter? I can’t see any trace of t2 in the first explain (with query plan omitted). From: Hick Gunter<mailto:h...@scigames

Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'

The "unused left join" is not "unused". Use "explain" instead of "explain query plan" and you will see that the left join is performed in both cases. The difference is that t2 has an INTEGER PRIMARY KEY and a lookup there is apparently not printed out in the query plan. Table t3 has an explicit

Re: [sqlite] [EXTERNAL] sqlite as 'streaming mode' query engine?

If your stream mode virtual table stalls in it's xNext method until the next row from the stream is available (or the stream is closed, in which case it needs to set an EOF flag for the xEof method to return), then each row will be aggregated as it becomes available. The result will not be

Re: [sqlite] [EXTERNAL] Bug in unique index

Not a bug. Unique(v1,v2) implies unique(v2,v1) which makes the second definition superflous (1,2) is distinct from (2,1) (because tuples are ordered), no violation of unique. If you want to exclude equivalent sets, your check needs to be v1 < v2. -Ursprüngliche Nachricht- Von:

Re: [sqlite] [EXTERNAL] Re: sqlite 3.21.0 bug? SELECT CAST ('9223372036854775807 ' AS NUMERIC);

I think I may have found the problem: The ToNumeric opcode calls sqlite3VdbeMemNumerify() which has (in 3.7.14.1) the following code: if( 0==sqlite3Atoi64(pMem->z, >u.i, pMem->n, pMem->enc) ){ MemSetTypeFlag(pMem, MEM_Int); }else{ pMem->r = sqlite3VdbeRealValue(pMem);

Re: [sqlite] [EXTERNAL] Re: Can this be done with SQLite

You need to use a temporary table because by the time you select the key 1 value it has already been overwritten. BEGIN; CREATE TEMP TABLE new_speed AS SELECT (key +4) % 5 AS key, speed FROM playYouTubeVideo; UPDATE playYouTubeVideo SET speed = SELECT speed FROM new_speed WHERE new_speed.key =

Re: [sqlite] [EXTERNAL] get stmt row count

No. The only viable query plan is scan and sort (see "explain query plan" output). The sqlite3_prepare() family of calls creates the SQL program (see "explain" output) and sets ist initial state. If the value you require is not null, you must call one of the sqlite3_bind() functions. This sets

Re: [sqlite] [EXTERNAL] SQLite Application Server Concurrency

Accessing SQLite files via network filesystems is high on the list of "how to corrupt SQLite databases", see section 2.1 in http://sqlite.org/howtocorrupt.html WAL mode requires shared memory. How are you going to do that with processes running on different PCs? See sections 4 and 6 of

Re: [sqlite] [EXTERNAL] bind blob lifetime

A bound blob or string is destroyed "after SQLite has finished with it". This should be the case when sqlite3_clear_bindings() is called. Are you sure it is not deleted then? Code reading suggests it should be. Other times are when the parameter is re-bound, or the statement finalized.

Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

ey visible as the primary key of the table. Hmm, during a vacuum, when SQLite re-numbers the rowids, if you made a foreign key to the rowid with "on update cascade" does it in fact cascade since it's not a normal transaction? -Original Message- From: sqlite-users [mailto:sqlit

Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Hick Gunter <h...@scigames.at> Sent: Monday, January 8, 2018 1:19:00 PM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL' >-Ursprüngliche Nachricht---

Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

>-Ursprüngliche Nachricht- >Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag von x >Gesendet: Montag, 08. Jänner 2018 11:39 >An: SQLite mailing list >Betreff: [EXTERNAL] Re: [sqlite] difference between 'ID IS NULL'

Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

that new version can not break sql-s for milions of users using sqlite. But this change does. Sorry but I did not ask how can I fix my sql-s. I know I can create table and insert into. Change select to select d as d is just ridiculous. Rename column name into same name. What is that? Hick Gunter

Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

The behaviour does not need to match what you think of as consistent. The only way to force a certain column name is with the AS clause *on the outermost statement*. Otherwise, the column name is implementation defined and may change between releases. You should not be relying on column names

Re: [sqlite] [EXTERNAL] Can a SELECT statement be used within a trigger?

INSERT and INSTEAD OF INSERT triggers have no result set. The select will be performed, but it's result set is discarded. Same for UPDATE and INSTEAD OF UPDATE triggers. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von

Re: [sqlite] [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist?

INSERT OR IGNORE ... -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Patrick Skelton Gesendet: Donnerstag, 21. Dezember 2017 12:56 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] How do I insert a

Re: [sqlite] [EXTERNAL] How to detect cycles in a hierarchical table?

The most useles answer would be: Yes; run a recursive CTE query and if it does not terminate, then there are loops ;) -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Shane Dev Gesendet: Mittwoch, 20. Dezember 2017 22:32

Re: [sqlite] [EXTERNAL] performance impact of index creation order

You are not showing the definition of data. Some table constraints (e.g. UNIQUE or PRIMARY KEY) are implemented via an index, so creating a superflous index that duplicates an automatically created index only serves to waste space in the file and time to maintain. It is possible that creating

Re: [sqlite] [EXTERNAL] create index implies analyze?

CREATE INDEX will populate the index with references to all of the rows in the table. Bulk loads may run considerably faster if no indices are present at load time (not yet created or dropped beforehand), but created right after the data has been inserted. If you run INSERT or UPDATE statements

Re: [sqlite] [EXTERNAL] Can we create a table where the value of one particular column <> another column?

Use a trigger and make it raise an exception, something like (not tested, just an example of how it might work): CREATE TRIGGER BEFORE INSERT ON WHEN NEW.parent == NEW.child BEGIN RAISE(FAIL, 'connecting to self not allowed') END; You may also require a similar BEFORE UPDATE trigger to avoid

Re: [sqlite] [EXTERNAL] Re: printf() with UTF-8 and \n \t format

The most common "problem" with UTF-8 and string lengths is that multibyte UTF-8 characters (most often characters with diacritical marks, e.g. german umlaut or special characters like the EUR sign) get truncated in between their constituent bytes. This leads to invalid byte sequences at the

Re: [sqlite] [EXTERNAL] Re: same code produces a different database file on different computers

The internal table sqlite_master has a field named sql that contains the text of the create statements. This may be what you are looking at. Does the difference show up in the sqlite_master table contents? -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Re: What happens if an in memory database runs out of memory

Pragma temp_store and the preprocessor macro SQLITE_TEMP_STORE determine where temp tables and indices are stored. See http://sqlite.org/pragma.html#pragma_temp_store -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

I guess you might be runing into the effect described here http://sqlite.org/wal.html : " Very large write transactions. A checkpoint can only complete when no other transactions are running, which means the WAL file cannot be reset in the middle of a write transaction. So a large change to a

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

As you are running in WAL mode, readers will still be seeing the state of the data BEFORE you started the delete transaction. This holds true also for readers that run while your delete transaction is running. Nobody is pulling out the rug from under any transaction at all. Even though you are

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

SQLite does this too (I'm not sure about the "sort rowid" bit, but it would seem reasonable); and similarly for an update, it will first SELECT the affected rows in their result form and insert them all later. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Re: Atomic DELETE index optimisation?

If I understand correctly, you are running a single statement equivalent to DELETE FROM WHERE ; Since SQLite can drop a whole table very much faster than deleting ist rows one by one, try: BEGIN; CREATE TABLE temp_data AS SELECT * FROM old_data WHERE ; DROP TABLE old_data; ALTER TABLE

Re: [sqlite] [EXTERNAL] Is this a bug with expression evaluation?

This is well documented behaviour, see the explanation of affinity. See http://sqlite.org/datatype3.html#affinity If you require floating point arithmetic, you must introduce REAL affinity, either by including a field with storage class REAL, a cast operation or a real literal value

Re: [sqlite] [EXTERNAL] Re: How to index data based on custom comparisons?

Select from blob_index idx cross join data_table dt on (idx.rowid = dt.rowid) where ; Assuming that the rowid of the blob_index is generated from and identical to the rowid of the data table -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] 2D query

select group_concat(members,'. ')||'.' from (select sex||': '||group_concat(name) as members from people group by sex); group_concat(members,'. ')||'.' --- F: Alex,Jane. M: Alex,John. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] values ?

You have to SELECT that -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mark Wagner Gesendet: Dienstag, 12. Dezember 2017 19:35 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite]

Re: [sqlite] [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

29. November 2017 11:10 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN? On Wed Nov 29, 2017 at 09:21:47AM +, Hick Gunter wrote: > What about loading your UDF in the CLI? Do you mean wit

Re: [sqlite] [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

What about loading your UDF in the CLI? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von no...@null.net Gesendet: Mittwoch, 29. November 2017 09:58 An: SQLite mailing list Betreff:

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

I suspect that the udf() function is called once to build the record and once again to build the parameter list for the trigger program. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von no...@null.net Gesendet: Dienstag,

Re: [sqlite] [EXTERNAL] Re: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Sorry to rain on your parade, but " The sqlite_sequence table does not track ROWID changes associated with UPDATE statement, only INSERT statements.", so if you change the (auto-generated) rowid, your "row age to row id" relation no longer holds. Same goes for updating the sqlite_sequence entry

Re: [sqlite] [EXTERNAL] help with EXPLAIN QUERY PLAN

COVERING INDEX means that all required fields for the query can be read from the index alone, without accessing the row itself. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Donnerstag, 23. November 2017

Re: [sqlite] [EXTERNAL] how into insert row into middle of table with integer primary key

Not without deleting and reinserting (or alternatively, updating) every single row past the desired insert position, since you have declared that the id field is a synonym for the internal rowid. Had you declared "id real primary key" you could have gotten away with using the arithmetic mean

Re: [sqlite] [EXTERNAL] Possible User Defined Function (UDF) Bug?

I was unable to replicate a double call of the udf using the sql you provided. Can you provide the original SQL (both for the INSERT and the CREATE TRIGGER) and the explain output (SQLite byte code, i.e .explain followed by explain )? -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Error: ambiguous column name

A bare field name is legal in SQL only when it is unique; otherwise it needs to be qualified by table name or table alias. In a self join, the table name is identical, so using aliases is mandatory. Result column names are not well defined in the SQL standard (apart from the use of the AS

Re: [sqlite] [EXTERNAL] Re: Does wal-file support MMAP？

I take it you have already fine tuned your database schema and statements to utilize optimum query plans, which may cut orders of magnitude from execution times and found the performance to still be lacking. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Most efficient way to detect on-disk change

There is also http://sqlite.org/pragma.html#pragma_user_version which more closely resembles what you have now. I strongly suspect that an update cycle of the user_version should be done within the transaction performing the changes. BEGIN Read user version Write updated user version COMMIT

Re: [sqlite] [EXTERNAL] Unexpected result from SELECT * FROM (subquery);

Because the query planner needs to know the name(s) of the table(s) required for the query at "prepare time" and your query does not provide the name until "run time". -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Shane

Re: [sqlite] [EXTERNAL] memory leak

You are preparing statements inside the loop, but only finalizing the last one (i.e. outside the loop) -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Lev Gesendet: Sonntag, 05. November 2017 01:28 An:

Re: [sqlite] [EXTERNAL] DB on JFFS

In addition to putting mutiple inserts within a single transaction (this saves "disc" IO), you might like to look into using parameters (this saves on CPU spent parsing/compiling the statements. This leaves you with: (setup) connect prepare BEGIN (loop) bind step reset (cleanup) END finalize

Re: [sqlite] [EXTERNAL] deadlock between query and insert in-memory db

In frame 0, the pointer passed to sqlite3_value_type is NULL, which is causing the segfault In frame 1, an unknown column (index is "optimized out") of the current row of an SQLite3 Statement is queried The rest of the stack is Python SQLite Wrapper and Python implementation code. Can't help

Re: [sqlite] [EXTERNAL] Re: SQLite DB - DateTime field values are not consistent

My point is that there is no datetime magic performed for TEXT fields. If you feel the database should handle it, go ahead and write triggers/check constraints. If you feel the application should handle it, make it convert to and from just one single format (we use 64bit numerical timestamps

Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent

There is no "date" datatype in SQLite, an das you yourself attest, SQLite is returning exactly whatever was originally inserted. It is up to your application to define the allowed format for storing datetimes and to provide conversion between the chosen storage format and the presentation to

Re: [sqlite] [EXTERNAL] sqlite_master structure

If an entry refers to an index, the field "name" will contain the name of the index, while the field "tbl_name" contains the name of the table the index refers to. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Igor

Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....

If your xBestIndex function indicates that your virtual table supports an index on the constraint with cost x and you have a single OR clause, the QP will assign a cost of 2*x to performing 2 keyed lookups/partial table scans If your XbestIndex function indicates that your virtual tabel does no

Re: [sqlite] [EXTERNAL] virtual tables, and theTrue meaning of pIdxInfo->estimatedCost, pIdxInfo->estimatedRows, and pIdxInfo->idxFlags...

I can provide some info coming from our experience with SQLite 3.7.14: Since most SQl processing is IO bound, the "estimated cost" should be the number of disk IO operations required to retrieve the rows. The later addition of "estimated rows" reflects to the fact that some virtual table

[sqlite] materializing views over virtual tables

Still using SQLite 3.7.14: I have two identically declared virtual tables that differ only in the backing store (memory section vs. CTree files), and a view. CREATE VIRTUAL TABLE pools_MM using Memory(); CREATE VIRTUAL TABLE pools_CT using CTree(); CREATE VIEW pools_VW AS SELECT * FROM pools_MM

Re: [sqlite] [EXTERNAL] one to one relationships

What is the question? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Paul Alagna Gesendet: Dienstag, 17. Oktober 2017 08:35 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] one to one relationships 2

Re: [sqlite] [EXTERNAL] current_timestamp locale

There is no "DATE" type in SQLite. The current_timestamp is a TEXT value equivalent to datetime('now') which returns an ISO formatted datetime string in UTC (-MM-DD HH:MM:SS). -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im

Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....

In our virtual table implementations, we are using the rowid to return the location of the record in the backing store (e.g. record offset in the file used as a backing store, offset within a shared memory section or maybe even the memory address of the record image) and also implement fast

Re: [sqlite] [EXTERNAL] Trigger Question

INSTEAD OF triggers are only allowed on views. From the documentation I would suggest using a BEFORE trigger and calling RAISE() in the trigger program. I expect RAISE(IGNORE) to silently abort the calling statement. -Ursprüngliche Nachricht- Von: sqlite-users

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

My guess is that you are not connecting to any database file, i.e. calling the constructor without a file name AND not calling the openCreateDatabase method before attemting to create a table. BTW: Your data model assumes a "Name" may have only 1 street address, but up to 4 telephone numbers

Re: [sqlite] [EXTERNAL] Re: sqlite3_stmt limitations

SQLite will block access to the whole database during a write operation. Readers will have to wait until the write completes, even if the object they are interested in is not affected by updates. Protecting each object's data with a posix read-write lock will allow readers to access any object

Re: [sqlite] [EXTERNAL] Re: sqlite3_stmt limitations

Make sure each thread has ist own private connection to the SQLite database (see https://sqlite.org/inmemorydb.html) Prepare the statement once in each reader thread and use the bind functions to set the constraint values The writer thread will need to prepare statements to populate the db

Re: [sqlite] [EXTERNAL] sqlite3_stmt limitations

If your "properties" are independant of each other and the reader thread accessing intermediate values is not a problem, you can just continue (good luck is bound to run out in 15 years of non threadsafe operation). Consider using atomic instructions to read/update single properties. If

Re: [sqlite] *** suspected spam or bulk *** Re: [EXTERNAL] Number of rows in answer set

This was already addressed. If the ORDER BY clause cannot be fulfilled by virtue of the query plan, the full result set must be retrieved and sorted before even the first row can be returned. This is expensive (memory and/or IO bandwidth) and makes the query seem unresponsive and therefore

Re: [sqlite] [EXTERNAL] Number of rows in answer set

The reason "select count(*) from t" is super fast is the special "count" opcode that does the "running" in just one go, instead of calling "Column", "AggStep" and "Next" in a loop. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im

Re: [sqlite] [EXTERNAL] Number of rows in answer set

sqlite] [EXTERNAL] Number of rows in answer set On Mon, Sep 18, 2017 at 10:37 AM, Hick Gunter <h...@scigames.at> wrote: > SQLite uses some nifty heuristics to estimate the number of rows it > expects to process while formulating a query plan. [...] > Is there any way to get at that

Re: [sqlite] [EXTERNAL] Number of rows in answer set

n England and Wales. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: 18 September 2017 09:37 To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] Number of rows in answer set SQLite uses some nifty heurist

Re: [sqlite] [EXTERNAL] Number of rows in answer set

SQLite uses some nifty heuristics to estimate the number of rows it expects to process while formulating a query plan. The only way to come up with the exact number of result rows is to actually run the query. At which point you already know how many rows have been returned. -Ursprüngliche

Re: [sqlite] [EXTERNAL] Re: Table-valued functions as PIVOT

Should not the result (for a simple pivot) be more like Field | Alice | Bob | Eve - age | 42| 27 | 16 with one row for each column of the original table? Or maybe even: Using pivot (,,); -Ursprüngliche Nachricht- Von: sqlite-users

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

Try fl_value(...) IN () -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jens Alfke Gesendet: Dienstag, 12. September 2017 19:26 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite]

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

If you have 4 set clauses, the those 4 fields will be updated with the same values for all the rows matching the where clause with just a single call to sqlite3_step(). If you actually want to update only 1 of the fields in 1 record, then you must bind the current values (which are unknown and

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

SQLite currently implements UPDATE by pretending it is SELECTing all the fields, except a SET clause causes the expression(s) to be evaluated instead of the current field value(s). Are you using a single prepared statement and binding values (in which case, how do you know what values to bind

Re: [sqlite] [EXTERNAL] Re: Should the INTEGER not be cast to a REAL

The following code fragment from explain output illustrates the problem: asql> explain insert into t values (0); addr opcode p1p2p3p4 p5 comment - - -- - ... 5 Integer0 3 0

Re: [sqlite] [EXTERNAL] Is there a performance difference between COUNT(*) and COUNT(name)

Count() needs to extract the field from the record, tallying only those that are NOT NULL. Count(*) returns the total number of records in the table, with no need to extract a specific field. When looking into efficiency, try using the .explain/explain feature. asql> explain select count(a)

Re: [sqlite] [EXTERNAL] Better way to use a large constant

lite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Cecil Westerhof Gesendet: Dienstag, 05. September 2017 14:16 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Better way to use a large constant 2017-09-05 13:55 GMT+0

Re: [sqlite] [EXTERNAL] Better way to use a large constant

Try 250E15. Just 1 constant instead of 6 constants and 5 multiplication operations (for each and every row). -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Cecil Westerhof Gesendet: Dienstag, 05. September 2017 13:49 An:

Re: [sqlite] [EXTERNAL] Re: SQLITE bug

Are you really proposing to restore just one or a selected set of tables from a backup of the database? What state does a transaction that touches one or more tables that are restored and one or more tables that aren't go to? It can't be "commited" because some data is not in the final state,

Re: [sqlite] [EXTERNAL] Re: sqlite3_value_subtype setting not preserved with GROUP BY or ORDER BY

See description of JSON1 extension: "For functions that accept "value" arguments (also shown as "value1" and "value2"), those arguments is usually understood to be a literal strings that are quoted and becomes JSON string values in the result. Even if the input value strings look like

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

The number of keys in an sqlite index page depends on the size of the database pages and the size of the (compressed) key value, which is stored in the same "row format" as the data. Child segment pointers are stored at the beginning of the page and key contents are stored at the end. The page

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

h disk read would contain more records. Even forgetting about keys, if you packed say 8 columns into one int64 column would you not be saving a minimum of 7 bits? From: Hick Gunter<mailto:h...@scigames.at> Sent: 10 August 2017 10:53 To: 'SQLite mailing list'<mailto:sqlite-users@maili

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