Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX

Using SQLITE_OPEN_FULLMUTEX puts SQLite into serialized mode. This means that mutltiple threads can share a single connection but will block until the mutex is freed because the thread currently holding the mutex has left the SQLite code. Blocked threads will only experience a time delay

Re: [sqlite] [EXTERNAL] Query regression with virtual tables

üngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hick Gunter Gesendet: Montag, 12. November 2018 11:52 An: 'SQLite mailing list' Betreff: [EXTERNAL] [sqlite] Query regression with virtual tables On upgrading from 3.7.14 to 3.24 (at last)

[sqlite] Query regression with virtual tables

On upgrading from 3.7.14 to 3.24 (at last) I found a very detrimental change in the query implementation for a certain type of query: SELECT <...> FROM WHERE a IN () AND b IN () AND c = AND timestamp between AND ORDER BY timestamp DESC LIMIT ,; In 3.7.14 the xBestIndex function was called

Re: [sqlite] [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

Foreign keys are ignored by default and need to be explicitly enabled. I would expect this to include everything that relates to foreign keys. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von a.furi...@lqt.it Gesendet:

Re: [sqlite] [EXTERNAL] sharing in memory db through threads

Have you enabled URI filenames? See https://www.sqlite.org/uri.html for details -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Wasilios Goutas Gesendet: Montag, 05. November 2018 16:28 An:

Re: [sqlite] [EXTERNAL] Index help...

There is no datetime type in SQLite. You are storing ISO Text representations so you should declare the column as TEXT. An Index is only useable for a prefix of equality contstraints followed by ONE inequality constraint. From your index (model_id, confidence, ts) and your query constraints (

Re: [sqlite] [EXTERNAL] INSERT OR REPLACE and foreign keys

This is the expected and documented behaviour. Maybe you are looking for UPSERT? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Roman Fleysher Gesendet: Dienstag, 23. Oktober 2018 19:53 An: General Discussion of SQLite

Re: [sqlite] [EXTERNAL] Re: Fill empty space with random

For an encryption scheme to suffer enigma machine type vulnerabilities, the concept behind it must predate WWII. IIRC the last straw (apart from known clear text like all messages ending with the same greeting) the broke the enigma encoding was the fact that a radio operator on an italian ship

Re: [sqlite] [EXTERNAL] Find key, value duplicates but with differing values

Two nested selects The inner select groups by partId, name, value The outer select groups by partId, name -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Donnerstag, 11. Oktober 2018 18:00 An:

Re: [sqlite] [EXTERNAL] Persistent snapshots and rollbacks

1) Include an "inserted at" timestamp column in each table 2) create a history table for each real table 3) use before triggers to copy the old record to the history table and set the "inserted at" timestamp If you use temp triggers, you can even keep the history tables in a separate database

Re: [sqlite] [EXTERNAL] Re: alter table, other alter category, fails in presence of trigger on 3.25.2

The phrase "integer primary key" is the "wingardium leviosa" of the sqlite world. It must be pronouced correctly, lest you end up with a buffalo on your chest. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thierry

Re: [sqlite] [EXTERNAL] Re: storing unsigned 64 bit values

"unsigned" is ignored by sqlite. Depending on how you inserted the value, it could be stored as a text or as a real value, irrespective of the declared type. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Conor Lennon

Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

Which version are you using? The set of constraint constants was extended in versions 3.10 (LIKE, GLOB, REGEXP), 3.21 (NE, IS*) and most recently 3.25 (FUNCTION) -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von dave

Re: [sqlite] [EXTERNAL] More bug with sqlite3_declare_vtab; also comments about ALTER TABLE

Why would you include clauses that modify the behaviour of *native* tables in the DDL that describes a *virtual* table? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von sql...@zzo38computer.org Gesendet: Freitag, 21.

Re: [sqlite] [EXTERNAL] Last_row_id

. September 2018 10:40 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] [EXTERNAL] Last_row_id On 17/09/2018 14:05, Hick Gunter wrote: > A trigger program does not return any result rows. True. But a "select" query in the body of a trigger can be used to evaluate a user-def

Re: [sqlite] [EXTERNAL] shell csv import

When running a script from the shell, you can redirect stderr tot he null device using 2>/dev/null or to the same destination as stdout using 2>&1. The latter is also very useful in crontab entries, as neglecting to handle stderr will result in an email tot he user that contains anything

Re: [sqlite] [EXTERNAL] Last_row_id

...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Monday, September 17, 2018 8:06 AM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] Last_row_id A trigger program does not return any result rows. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun

Re: [sqlite] [EXTERNAL] Last_row_id

A trigger program does not return any result rows. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Yadwindersingh Gesendet: Sonntag, 16. September 2018 03:28 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL]

Re: [sqlite] [EXTERNAL] The "natural" order of the query results.

SQLite handles GROUP BY and ORDER BY in basically the same way. If there is an apropirate index, then it will use this index and the rows will be returned in visitation order of this index. If, for exmaple by adding a new index or even an upgrade of the Query Planner, a different execution plan

Re: [sqlite] [EXTERNAL] sqlite3_column_type returns NOT SQLITE_NULL and the following sqlite3_column_text returns with nullptr

You are assuming that the column is either NULL or a SQLITE_TEXT value. This assumption is obviously violated by the real data returned by your query (which you do not show). A second possibility would be that another thread has done something to your prepared statement between retrieving the

Re: [sqlite] [EXTERNAL] Database occasionally very slow for trivial query

NB: SELECT COUNT() FROM requires a traversal of the whole table (or index, if one exists for the field) and returns the number of non-NULL entries, whereas SELECT COUNT() FROM invokes a special opcode to retrieve the total number of rows without actually acessing any of them and so is very

Re: [sqlite] [EXTERNAL] Can you use ORDER BY clause in aggregate functions?

I am not sure what you are trying to achieve. Can you give an example? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von sql...@zzo38computer.org Gesendet: Freitag, 07. September 2018 21:30 An:

Re: [sqlite] [EXTERNAL] Missing function sqlite3_column_index

The name of an output column is not even defined, much less unique, unless the author of the statement has done extra work (using unique column names and/or AS clauses). Consider Select a.*,b.*,c.* ... Where each table has a column named Id. Which index would you like to have returned?

Re: [sqlite] [EXTERNAL] .exit in script is not working

Try the following script: Select "statement"; .exit Select "after exit"; Running the script as an init file produces only the first text. An .exit command in an init file will only terminate the execution of the init file itself, not the sqlite shell as a whole. This is intended.

Re: [sqlite] [EXTERNAL] Query on TEMP view.

In the sqlite shell, enter the .explain command and then EXPLAIN QUERY PLAN for an explanation of the plan, and EXPLAIN for the generated bytecode. This usually helps to understand what sqlite is thinking (although maybe not why). Note that WHERE constraints are applied to the input set

Re: [sqlite] [EXTERNAL] Re: Unsigned

By definition, a pointer (the current virtual memory address of an object) only supports an equality test ("do these two pointers reference the same object"). There is no point in sorting pointers. But you can still do it by implementing a collating sequence which would internally do int

Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Lars Frederiksen Gesendet: Montag, 13. August 2018 14:46 An: 'SQLite mailing list' Betreff: Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg Hi Hick Gunter

Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Lars Frederiksen Gesendet: Montag, 13. August 2018 14:24 An: 'SQLite mailing list' Betreff: Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg Hi Hick Gunter, I do not know exactly what you

Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

You need to show hex() before anyone can verify what encoding is stored. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Lars Frederiksen Gesendet: Montag, 13. August 2018 14:03 An: 'SQLite mailing list' Betreff:

Re: [sqlite] [EXTERNAL] No unicode characters in sqlite3 console prg

SQLite will return exactly what you give it. Typically, "double converting" from ISO to UTF will mess up your encoding, as will attempting to display UTF on a console that speaks ISO. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im

Re: [sqlite] [EXTERNAL] (no subject)

meddelelse- Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På vegne af Hick Gunter Sendt: 10. august 2018 11:55 Til: 'SQLite mailing list' Emne: Re: [sqlite] [EXTERNAL] (no subject) This sounds like a windows file access problem that is not letting your application

Re: [sqlite] [EXTERNAL] (no subject)

This sounds like a windows file access problem that is not letting your application access a file created in the context of a cmd box. Can you - from the sqlite shell - insert the desired record into the database? And select it afterwards? If so, then SQlite is working perfectly and it is your

Re: [sqlite] [EXTERNAL] Re: Can date('now') fire a trigger automatically after a day passed?

Maybe you are looking for coalesce(EndDate,datetime('now')) This will pretend that a NULL EndDate is always seen as the 'current datetime'. And you can find those records that need acting upon by the constraint EndDate IS NULL, run whenever your application feels like it needs to "do

Re: [sqlite] [EXTERNAL] Database locks

Your impression is only correct if you are running in WAL journal_mode. In all other modes, readers will block the writer(s) and yo need to set a busy timeout or a busy handler. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag

Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

Good luck with quoted speech that contains more than one sentence. E.g. William Faulkner said, “Never be afraid to raise your voice for honesty and truth and compassion against injustice and lying and greed. If people all over the world...would do this, it would change the earth.”

Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

Please try to avoid using keywords as names, especially if they conflict with the intended datatype. "text blob not null" creates a field of name "text" whose content is a blob and yet you intend to store text data (with embedded newlines) in it. If you store the lines separately, you can

Re: [sqlite] [EXTERNAL] Common index for multiple databases

This is what I think you are asking: - You have a "main office" computer that holds the current information on "everything" - You have several different categories of users (technicians, accountant, ...) that require different subsets of the data - Each user has his own computer, that may be

Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

Judging from the following output, I would say it is data producing, but returns no rows for no table or a table that has no indexes. Just because a given select statement returns no matching rows does not make it invalid asql> create temp table test (i integer, t text); asql> create index

Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading

] Im Auftrag von Dominique Devienne Gesendet: Mittwoch, 01. August 2018 11:34 An: General Discussion of SQLite Database Betreff: Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading On Wed, Aug 1, 2018 at 10:39 AM Hick Gunter wrote: > Are you sure it is not the special case documented

Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading

Are you sure it is not the special case documented in the ATTACH command? (see https://sqlite.org/lang_attach.html) " Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is

Re: [sqlite] [EXTERNAL] Re: Very, very slow commits - Possibly solved

Based on the currently available information I woudl suggest the following schema: CREATE TABLE IF NOT EXISTS "Disruptions" ( "id" INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT, "version" integer NOT NULL, "Disruption_id" INTEGER NOT NULL, "status" integer NOT

Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex/xFilter question

For your JOIN case, I would expect the following "conversation" (simplified and based on the original cost based QP, the NGQP may be doing something else) xBestIndex( table A, no constraints) -> Full table scan FTS(A), cost = CARD(A), count=CARD(A) xBestIndex( table A, OID) -> primary key

Re: [sqlite] [EXTERNAL] Sqlite Sharding HOWTO

We almost exclusively use virtual tables in our application, and this includes virtual table code to access Faircom CTree files and in-memory data dictionaries. The structure (fields, indexes) of these tables is fixed (and identical for corresponding CTree and DD tables), with sharding achieved

Re: [sqlite] [EXTERNAL] How to insert this string?

Insert into sql_replace values ('xxx','''A'',''B'',''C'''); Note: All single quotes. To include a single quote in a single quoted string, it needs to be doubled. Not sure what you are trying to do here. Build SQL queries on the fly? -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] What happens when a call contains two SQL statement

l contains two SQL statement 2018-07-09 8:28 GMT+02:00 Hick Gunter : > Why should a failure in transaction #2 rollback transaction #1? > ​I was thinking that, but was not sure. I thought that maybe everything in a call would be seen as a transaction. But that is not the case then:

Re: [sqlite] [EXTERNAL] What happens when a call contains two SQL statement

Why should a failure in transaction #2 rollback transaction #1? If you want this behaviour, do "begin; delete ...; insert ... on conflict rollback; commit;" to make both statements run in one transaction -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] documentation for sqlite3_index_info-ídxStr typo.

Indeed SQLite does not care about what the VT implementations xBestIndex routine stuffs in there, as long as the xFilter routine can handle it. AFA SQLite is concerned, this is an opaque value of pointer size. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Bind Question -- Is this correct?

V1 uses the value bound to the SQL Variable while performing the concatenation of the text literal '+', the value cast as text, and the text literal 'hours' V2 expects (wrongly) that the text literal '+:StartTimehours' magically replaces part of the string with the value of an SQL variable

Re: [sqlite] [EXTERNAL] Shouldn't have to specify primary key explicitly

Supply a NULL for the INTEGER PRIMARY KEY to tell SQLite to "figure it out for yourself" (c) Siddharta Gautama "Buddha" -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Scott Robertson Gesendet: Donnerstag, 28. Juni 2018

Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index

18 12:56 An: General Discussion of SQLite Database Betreff: Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index On Thu, Jun 28, 2018 at 11:48 AM Dominique Devienne wrote: > > On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter wrote: >> >>

Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index

The xBestIndex function needs to call the sqlite_vtab_collation() function to query the collation name required for each constraint and return the appropriate index number. Subs: yes, yes, see above -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Understanding SELECT statement

There is no DATE datatype in SQLite. Your declaration assigns NUMERIC affinity for the date column. It seems that you are storing TEXT values, which is allowed, but in conflict with your declaration. Since you have not declared INTEGER PRIMARY KEY, you only achieve what UNIQUE alone would

Re: [sqlite] [EXTERNAL] Re: Performance issue with left join in 3.24 compared with 3.22

Add "cross" before the first "join" to force the first table into the outermost loop -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Eric Grange Gesendet: Dienstag, 26. Juni 2018 10:13 An: General Discussion of SQLite

Re: [sqlite] [EXTERNAL] Re: Selecting multiple similar columnname.

There is probably no performance difference between inserting 1 big row with repeated data from *up to* (how do you know how many?) 64 satellites and inserting *up to* 64 records with identical timestamps WITHIN A SINGLE TRANSACTION. This will eliminate your need for wildcard matching of

Re: [sqlite] [EXTERNAL] Explain Query plan

Have you run ANALYZE? This may provide the QP with better cost estimates. LIKE and GLOB can sometimes be optimized (differently, because GLOB is case sensitive and requires COLLATE BINARY and LIKE by default is not and requires COLLATE NOCASE), in which case the index will be used. See

Re: [sqlite] [EXTERNAL] Re: sqlite.org website is now HTTPS-only

R jurisdiction... -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Simon Slavin Gesendet: Freitag, 08. Juni 2018 08:37 An: SQLite mailing list Betreff: Re: [sqlite] [EXTERNAL] Re: sqlite.org website is now HTTPS-only On 8

Re: [sqlite] [EXTERNAL] Feature suggestion / requesst

> > >I've encountered a feature that I think would be awesome: >https://www.postgresql.org/docs/9.3/static/dml-returning.html > >Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING id; > What does this do if the INSERT creates multiple rows? What about inserts generated

Re: [sqlite] [EXTERNAL] Selecting multiple similar columnname.

Adding the attribute "hidden" to a column prevents it from showing up in the expansion of '*' in the select list, while still allowing it to be queried by name. There is no RE matching in the select list; you are expected to know exactly what you want. Having multiple instances of a field or

Re: [sqlite] [EXTERNAL] Re: sqlite.org website is now HTTPS-only

> > >Why can't we have both? I mean the software is in the public domain there is >nothing to hide so what's the point of encrypting the site? > >Cheers and thank for you generosity and work. >Best regards, >George I believe it is because of the EU GDPR, which is designed to placea

[sqlite] how to unsubscribe (was: Size of the SQLite library)

Use the links supplied at the bottom of each and every message to unsubscribe yourself -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dianne Dunn Gesendet: Donnerstag, 07. Juni 2018 06:25 An: SQLite mailing list Betreff:

Re: [sqlite] [EXTERNAL] Re: Sqlite delete slow in 4GB

Your schema has a major flaw that is addressed in SQLite documentation: Do not put any fields after blob fields, especially if the content is typically large. SQLite accesses fields in the order of defintion, so to access your ANH_PRC_id field, it has to retrieve the 1MB ANP_VALUE blob.

Re: [sqlite] [EXTERNAL] Re: Subject: Re: SQL Date Import

Not even Microsoft Excel has a dedicated datetime/timestamp type. It is just a presentation layer attribute of a floating point value. Also, you get to choose the way you want calendar data to be stored. So why? -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Re: random rows

Just an idea: If the table has an INTEGER PRIMARY KEY AUTOINCREMENT then then maximum rowid is available in the sqlite_sequence table. To generate a random sequence of rowids, this can be used, somewhat like SELECT DISTINCT rnd() * (SELECT seq FROM sqlite_sequence WHERE name='') as rowid;

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

ger value, gets interpreted after GROUP BY or ORDER BY as a result column number. Fortunately, SQLite isn't to blame for designing this. By the way, this feature is documented for ORDER BY, but I don't see it for GROUP BY. Mark On 30/05/18 13:28, Hick Gunter wrote: > You have constant integ

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

r, a feature I don't use. These, on the other hand, work as I would have expected: sqlite> select 0 group by cast (0 as int); 0 sqlite> select 0 group by (select 0); 0 Mark On 30/05/18 12:00, Hick Gunter wrote: > Yes. If the expression is a constant integer K, then it is considered an

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Yes. If the expression is a constant integer K, then it is considered an alias for the K-th column of the result set. Columns are ordered from left to right starting with 1. There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 GROUP BY 31" would be.

Re: [sqlite] [EXTERNAL] Re: database locked on select

on the same connection is a valid way of using this. cheers, Torsten On Mon, May 28, 2018 at 10:25 AM Hick Gunter <h...@scigames.at> wrote: > It is possible to bring an external resource into SQlite by writing > user-defined functions and/or virtual tables. This would allow > s

Re: [sqlite] [EXTERNAL] Re: database locked on select

It is possible to bring an external resource into SQlite by writing user-defined functions and/or virtual tables. This would allow something like: UPDATE set () = new_data() where needs_update(); With the UDF returning 1 (TRUE) if the current row (identified by the arguments) needs an update

Re: [sqlite] [EXTERNAL] Re: sqlite3_progress_handler(D,N,X,P)

I can't tell without extensively looking into the source code. I expect not. From my experience I would infer that ResultRow and maybe a few other VMIs are actual "interrupt points", because complex joins of virtual tables can continue for extended periods of time (and read tons of rows) before

Re: [sqlite] [EXTERNAL] sqlite3_progress_handler(D,N,X,P)

If you want elapsed milliseconds, you can set a timer and call sqlite3_interrupt() to have sqlite quit executing the query as soon as possible. This interface is based on "work done". I guess the callback routine gets called at the next "interrupt point" (which is why the number N is only

Re: [sqlite] [EXTERNAL] Open and query sqlite db in a buffer

Yes. Use the sqlite shell program to open the file. Otherwise you would need to work through the whole file format documentation to decode it. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Lloyd Gesendet: Freitag, 18.

Re: [sqlite] [EXTERNAL] sqlite3 not support write and read at the same time?

Ad 2) Yes, this is expected. SQLite uses file locking to implement transactions. Ad 1) Using WAL mode, readers can read data as it was before the write transaction started. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von

Re: [sqlite] [EXTERNAL] Re: probably recursive?

Counterexample: List of Tuples: (1,1), (1,3),(3,1),(3,3),(2,2) with nX = nY = 2 (2,2) is within the "square" but needs to be removed -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Barry Smith Gesendet: Mittwoch, 02. Mai

Re: [sqlite] [EXTERNAL] copmile SQLite with extension?

See http://sqlite.org/loadext.html -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thomas Kurz Gesendet: Donnerstag, 26. April 2018 14:06 An: SQLite mailing list Betreff: [EXTERNAL]

Re: [sqlite] [EXTERNAL] Window functions

t into (pre-allocated) RAM. S. On 04/26/2018 07:44 AM, Hick Gunter wrote: > "Window functions" aka "scrollable cursors" require that the whole result set > ist stored somewhere. Or at least the current position of all of the > tables/indices involved in producin

Re: [sqlite] [EXTERNAL] Window functions

"Window functions" aka "scrollable cursors" require that the whole result set ist stored somewhere. Or at least the current position of all of the tables/indices involved in producing it, provided that such positions are settable. You then need to keep track of how many records need to be

Re: [sqlite] [EXTERNAL] Re: Any operation to trigger osMunmap?

For PSS to become smaller, pages have to be either unmapped or evicted from main memory by the OS. Probably the only way to force SQLite to unmap the memory is to set a new mmap_size (possibly after closing and reopening the database connection, which will cause a boatload of IO that most

Re: [sqlite] [EXTERNAL] kooky thought: a vm-only build (for embedded). feasible?

There have been some inquries on the list into executing pre-generated bytecode. If you have a fixed schema you may be able to create a valid sqlite3_stmt pointer from bytecode stored somewhere. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Preupdate hook column name

ff: Re: [sqlite] [EXTERNAL] Preupdate hook column name I can't do that (execute other statements) inside a sqlite3_preupdate_hook callback. On Fri, Apr 13, 2018 at 6:50 AM, Hick Gunter <h...@scigames.at> wrote: > See pragma table_info; > > -Ursprüngliche Nachricht

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

Just to be sure of the real issue: - your application runs just fine with a small amount of memory (2M) for most queries - a small subset of queries requires "the whole db" to be kept in memory for performance reasons - the application should revert to "small memory" when the "whole db" query

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

AFAICT the showmap program is just a pretty-printer for the /proc//smaps file on android. You need to compare three states: 1) before mmap() is called (there should be no section referring to your file) 2) after mmap() is called (the section should be there, but RSS=PSS=0) 3) after the loop

Re: [sqlite] [EXTERNAL] Preupdate hook column name

See pragma table_info; -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von João Ramos Gesendet: Donnerstag, 12. April 2018 20:54 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite]

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

Not necessarily. Only if you run queries that access more than 256M of the db file AND there is no contention for memory resources. Why is the PSS of your process such a concern? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

No. You are confusing several issues here. There is "process size", which is the number of byte addresses that are theoretically valid within a process, as registered in the "process page table". Attempting to reference an address that has no corresponding page table entry causes a SEGV.

Re: [sqlite] [EXTERNAL] Constraints must be defined last?

Yes. See syntax diagram in http://sqlite.org/lang_createtable.html. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von J Decker Gesendet: Mittwoch, 11. April 2018 19:41 An: General Discussion of SQLite Database

Re: [sqlite] [EXTERNAL] Help using 'attach database' or Why is this an error?

Try ATTACH DATABASE "test.db" as con2; -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von J Decker Gesendet: Mittwoch, 11. April 2018 16:51 An: General Discussion of SQLite Database

Re: [sqlite] [EXTERNAL] Does mmap increase PSS?

Your guess is wrong. "pragma cache_size=2000" will limit the internal page cache to 2000 PAGES (of whatever page size the db file has) PER OPEN FILE. To set a limit of 2000kBytes PER OPEN FILE, use "pragma cache_size=-2000". The page cache is process private memory. Whatever portion is

Re: [sqlite] [EXTERNAL] Internal Logic Error

You are not showing your schema or the statement you are executing. Also, Sqlite3_StdCall.dll is not maintained by the Sqlite development team. Can you reproduce the error while executing your statement in the sqlite3 shell? -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Re: UPPER function depends on Locale?

But there already is a step by step instruction document right here http://sqlite.org/loadext.html that tells you exactly how to build a loadable extension, which can contain user defined functions, complete with references to examples. I suggest you use compress.c as a template, as this has

Re: [sqlite] [EXTERNAL] Re: what is server-process-edition?

Think of transactions as cars and the rows as the paving stones of a paved car park, and writing data as parking your car. You cannot park two cars on the same paving stones at the same time without creating a collision. The second car will have to leave the car park and try again.

Re: [sqlite] [EXTERNAL] Re: Is it possible to CREATE TABLE from other tables in a complex way?

>-Ursprüngliche Nachricht- >Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag von David Raymond >Gesendet: Freitag, 23. März 2018 21:52 >An: SQLite mailing list >Betreff: [EXTERNAL] Re: [sqlite] Is it possible to

Re: [sqlite] [EXTERNAL] Re: The upcoming 3.23.0 release

Replace LEFT with CROSS in all cases that you used LEFT as a hint. Not at all complex, apart from you having to discern between "hint" and "necessities". -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Eric Grange

Re: [sqlite] [EXTERNAL] R*Trees query data cached?

d heap parameters, and disabled a few unwanted #defines which has decreased initial query time. Do the above times give you any clues about the initial query time? Any other ideas would be helpful. Thanks again. From: Hick Gunter <h...@scigames.at> To: 'SQLite mailing list' &

Re: [sqlite] [EXTERNAL] Re: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

There are no stored procedures in SQLite. But you could try creating a trigger program. Not sure if this works... CREATE TRIGGER BEFORE UPDATE ON WHEN NOT EXISTS SELECT 1 FROM WHERE BEGIN INSERT INTO ( VALUES (NEW. ...); END; -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] Unexpected optimization

SQLite uses the result of ANALYZE for computing costs of native tables and your reported costs for queries from virtual tables. You are expected to return estimated disk access counts for the constraints given (plus more detail, depending on SQLite version). Are you sure you need LEFT joins as

Re: [sqlite] [EXTERNAL] How to convert a datetime column to a date?

Whatever is in your dttm column (there is no datetime type in sqlite) does not parse as a valid timestring, so date() returns NULL for each and every row, giving just 1 result row. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im

Re: [sqlite] [EXTERNAL] R*Trees query data cached?

t see a .describe in the SQLite documentation. I've tried to use .schema but that returns an error. Do you have another suggestion to obtain the table information you had in mind? From: Hick Gunter <h...@scigames.at> To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org&

Re: [sqlite] [EXTERNAL] R*Trees query data cached?

SQLite does not have "query caching". It does have a "page cache" that will keep heavily used pages iin memory. There is also the possibility of a file-system/os-level cache. To break down the 1.6 seconds required for the first query, try executing an sql script. In linux this would be along

Re: [sqlite] [EXTERNAL] Lazy virtual table creation

Interesting. How do you discern between "names of virtual tables that are not yet loaded" and "names of virtual tables that do not exist"? We have two strategies here: 1) "Cloned" general tables: These have identical structures, but contents is partitioned by one or more fields. The backing

Re: [sqlite] [EXTERNAL] Re: Lazy virtual table creation

IIRC it is NOT safe to call sqlite3_prepare() or sqlite3_step() withtin the authorizer callback, so schema changes are out of the question, as you would have to prepare/step a "CREATE VIRTUAL TABLE" statement for on-the-fly virtual table creation. "The authorizer callback must not do anything

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