Re: [sqlite] Performance of writing blobs

Dominique Devienne wrote: > On Mon, Jun 11, 2018 at 4:27 PM Clemens Ladisch wrote: >> It does write to the same pages, but those pages must be copied to the >> rollback journal so that they can be restored if the transaction is >> rolled back. (Or are the two passes inside

Re: [sqlite] Performance of writing blobs

Dominique Devienne wrote: > My assumption > was that after the zeroblob(N), there was enough room in the main DBs > pages, such that the subsequent blob open+write+close did not need to > generate any "page churn" (i.e. journal activity) and could write directly > to the pages created on initial in

Re: [sqlite] Explain Query plan

David Burgess wrote: >> Have you run ANALYZE? > > Yes. And LIKE now uses the index, but like is still ~100 times slower. A case-sensitive index cannot be used for a case-insensitive search. Create a COLLATE NOCASE index, or use GLOB. Regards, Clemens

Re: [sqlite] Feature suggestion / requesst

Hick Gunter wrote: >> 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? It re

Re: [sqlite] Filename encoding on Unix platforms

Christopher Head wrote: > (1) SQLite developers believe that Unix filenames should be UTF-8 ... > (2) SQLite developers believe that Unix filenames should be locale-encoded ... This might sound like a truism, but one of the goals of the SQLite library is to actually work. Therefore, it does not m

Re: [sqlite] Reset the cursor

Igor Korot wrote: > res = sqlite3_step( stmt ); > > Now I'd like the cursor in the recordset of the "stmt" to go to the record 1 > so I can process those records again. Strictly speaking, it is not possible to go back in the _same_ cursor. You'd have to execute the query again (by calling sqli

Re: [sqlite] "cursored" queries and total rows

Wout Mertens wrote: > To do paged queries on a query like > > SELECT colVal FROM t WHERE b=? LIMIT 10 This does not make sense without an ORDER BY. > To know how many rows there are in the query, I do > > SELECT COUNT(*) FROM t WHERE b=? > > Are there any efficiency tricks here? No. SQL

Re: [sqlite] This is driving me nuts

x wrote: > Why would window’s increase it’s cache size during step ascending then > reduce it during step descending in such a consistent manner? Windows has several different caching strategies. says: | FILE_FLAG_RANDOM_ACCE

Re: [sqlite] This is driving me nuts

x wrote: > I’ve written the console app below to illustrate. It prints out the amount of > RAM available every 20 million steps. > GlobalMemoryStatusEx(&status); > return status.ullAvailPhys / (1024 * 1024); This is "physical memory currently available", which is affected by othe

Re: [sqlite] This is driving me nuts

x wrote: > const int Size[]={11200,10}; // 112 million, 1 billion > > v.resize(Size[i]); > > // NB the exact same code is executed whether i is 0 or 1. > The only thing that > // changes is the size() of v and v isn't even used in the >

Re: [sqlite] Potential issue for date function when given Julian days in the range 999BC to 1BC

Inderjit Gill wrote: > When the date function is given a date between 999BC and 1BC ... The documentation says: | These functions only work for dates between -01-01 00:00:00 and | -12-31 23:59:59 (julidan day numbers 1721059.5 through 5373484.5). | For dates outside that range, the results

Re: [sqlite] Database file with a nonstandard sqlite_seuence schema causes crash.

Abroży Nieprzełoży wrote: > sqlite> PRAGMA writable_schema=1; The documentation says: **"Warning: misuse of this pragma can easily result in a corrupt database file."** > sqlite> CREATE TABLE xqlite_sequence(name TEXT PRIMARY KEY, seq INTEGER) > WITHOUT ROWID; SQLite does not use SQL but raw V

Re: [sqlite] R*Tree / In Memory Database / GUI Object Hit Testing

Robert M. Münch wrote: > I’m wondering if the R*Tree index of Sqlite could be used to implement > GUI object hit testing? Yes, that would be possible. > We could populate a r*tree table with (runtime-object-memory-pointer, > x0, y0, x1, y0) pretty easy. Now the user clicks the mouse somewhere, >

Re: [sqlite] Is this really the best way to do this?

Stephen Chrzanowski wrote: > On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch wrote: >> SELECT action FROM blocked WHERE email = ? >> UNION ALL >> SELECT 'OK' >> LIMIT 1; > > Out of curiosity, where's the guarantee that the OK won't be displaye

Re: [sqlite] Is this really the best way to do this?

Rob Willett wrote: > select CASE > WHEN EXISTS (select 1 from blocked where email = > 'rwillett.dr...@example.com') > THEN (select action from blocked where email = > 'rwillett.dr...@example.com') > ELSE 'OK' > END SELECT action FROM blocked WHERE email = ? UNION ALL SELECT 'OK'

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

Kevin O'Gorman wrote: > why is a read-only database being serialized at all? The database file is read only, the in-memory data structures are not. For example, when the cache size is smaller than the DB size, pages must be removed from and added to the internal list of cached pages. When using m

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

Techno Magos wrote: > So, memory sqlite is not really usable with multiple threads (readers). > While one might expect that multiple readers of *memory *content could > scale even better than with file content. Concurrent accesses to the same in-memory data structures must be serialized. In shar

Re: [sqlite] shell edit quoting

David Burgess wrote: >> Where do the quotes around the value come from? > > I typed them. Simgle set of double quotes I meant the quotes around the entire value returned by the SELECT. >> Are you using the standard command-line shell, and which output mode? > > yes and the default mode The defau

Re: [sqlite] shell edit quoting

> sqlite> select sql from sql_procs where name = 'a'; > sql > "select * from ""mytable""; > " Where do the quotes around the value come from? Are you using the standard command-line shell, and which output mode? Regards, Clemens ___ sqlite-users mailin

Re: [sqlite] Does WAL mode's SQLITE_BUSY special circumstances invoke the busy handler?

Donald Shepherd wrote: > The documentation on WAL databases includes a section with caveats re: > SQLITE_BUSY, included below. Do these invoke the busy handler (if > configured) or just return SQLITE_BUSY immediately? In general, SQLite calls the busy handler if there is a chance that the lock wi

Re: [sqlite] Insert with foreign keys enabled prevented in transaction

J Decker wrote: > foreign key mismatch - "option4_map" referencing "option4_name" > > create table `option4_name` (`name_id` char(36) NOT NULL, > ... > create table `option4_map` (... > FOREIGN KEY (`name_id`) REFERENCES `option4_name`(`name_id`) name_id in the parent table must b

Re: [sqlite] without rowid and secondary indexes

Olivier Mascia wrote: >> Le 10 avr. 2018 à 13:20, Clemens Ladisch a écrit : >>> select NAME,count(NAME) from SHEETS group by NAME having SHEET>? and NAME >>> is not NULL; >> >> The SHEET value is from some random row in the group, and the NAME value is >

Re: [sqlite] without rowid and secondary indexes

Olivier Mascia wrote: > [...] > So I should probably even better go with: > > create index ... where NAME is not NULL; > > as my only queries involving NAME have a 'where NAME is not NULL' restriction. To be sure, check with EXPLAIN QUERY PLAN whether the index is actually used. > select NAME,c

Re: [sqlite] Creating UDF?

Dr. Mucibirahman İLBUĞA wrote: > Is there a very simple example how to create UDF in SQLite?! From the Python documentation: import sqlite3 import md5 def md5sum(t): return md5.md5(t).hexdigest() con = sqlite3.connect(":memory:") con.create_function("md5", 1, md5sum) cur = con

Re: [sqlite] Missing keywords

Klaas Van B. wrote: > Since 3.23 the words FALSE and TRUE should be added to SQLite Query Language: > SQLite Keywords. But FALSE and TRUE are not keywords. says that | keywords ... may not be used as the names of tables, indices, columns, | ... or any o

Re: [sqlite] SQLite between forks

Simone Mosciatti wrote: > it is suggested in several place to don't share a connection between forks. Because of how locking and file handles interact. > However I fail to see how this can be a problem for purely in-memory database. In-memory databases do not use a file handle or file locking.

Re: [sqlite] The upcoming 3.23.0 release

Eric Grange wrote: >> Add the LEFT JOIN strength reduction optimization that converts a LEFT >> JOIN into an ordinary JOIN > > A question on this one: I have been using LEFT JOIN for many years (and not > juste in SQLIte) in cases where a JOIN could have been used as a way to > "hint" query optimiz

Re: [sqlite] possible bug: select clause column alias shadowing

Jean-Christophe Deschamps wrote: > At 23:36 18/03/2018, you wrote: >> In other words, aliases in the SELECT clause are evaluated _after_ the >> FROM and WHERE clauses are done. > > I must be misinterpreting: I was talking about the SQL standard. (I might have mentioned that somewhere ...) > sel

Re: [sqlite] possible bug: select clause column alias shadowing

Simon Slavin wrote: > As best I can find, SQL92 does not specify what happens when you choose > an AS clause giving a value name the same as a column. | 7.3 | | Function | |Specify a table or a grouped table. | | Format | | ::= | | [ ] | [ ] | [ ] |

Re: [sqlite] How to optimise a somewhat-recursive query?

I have not looked at the schema and queries in detail. But at a first glance: > CREATE VIEW v_most_recent_lookup_per_url AS > ... > ORDER BY url_id; Drop the ORDER BY; it is useless in a view used in another query, and just might slow things down. > 011SEARCH TABLE lookups

Re: [sqlite] sqlite3_serialize / sqlite3_deserialize (3.23.0 draft)

Olivier Mascia wrote: > What are use cases for these sqlite3_serialize / deserialize? A common question is "how do I save my in-memory database to disk?" (The common answer is "use the backup API" or "use an on-disk DB in the first place".) Anyway, there are cases where you have a blob containing

Re: [sqlite] pragma foreign_key_check

Mark Wagner wrote: > The documentation for foreign_key_check says I should be receiving 4 > columns per violation. I only seem to be getting 1. > > CREATE TABLE t (id int primary key, value); > CREATE TABLE t1 (id integer primary key, v references t(value)); > sqlite> pragma foreign_key_check; > E

Re: [sqlite] Why some options (ex. SQLITE_ENABLE_EXPLAIN_COMMENTS, SQLITE_ENABLE_DBPAGE_VTAB) are permanently enabled in Makefile.am, but are documented as user-defined?

Yuri wrote: > Makefile.am has: > AM_CFLAGS = @THREADSAFE_FLAGS@ @DYNAMIC_EXTENSION_FLAGS@ @FTS5_FLAGS@ > @JSON1_FLAGS@ @ZLIB_FLAGS@ @SESSION_FLAGS@ -DSQLITE_ENABLE_FTS3 > -DSQLITE_ENABLE_RTREE > sqlite3_CFLAGS = $(AM_CFLAGS) -DSQLITE_ENABLE_EXPLAIN_COMMENTS > -DSQLITE_ENABLE_DBPAGE_VTAB -DSQLITE

Re: [sqlite] Why this LIMIT

Cecil Westerhof wrote: > I see that in certain older queries I use: > LIMIT (SELECT COUNT(*) FROM TABLE) > > ​It looks like​ this has no use (limiting the selected records to the > number of records there are). Anyone an idea what could be a reason for > this? Trying to put some table data in

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

Balaji Ramanathan wrote: > I have some columns repeated multiple times, and I find that > some of the repeated columns have postfixes like ":52449101", > ":3016716403", ":197485499", ":3551085770", etc. Where do these postfixes > come from? SQLite adds a number to make the name unique. After thr

Re: [sqlite] Question regarding 3.23.0 (pending) and TRUE/FALSE

Olivier Mascia wrote: > What values will be considered FALSE 0, and they keyword "FALSE". FALSE is an alias for the integer 0. > and hence will TRUE be NOT FALSE or equality to some other specific value? What exactly do you mean with "be" and "equality"? The SQL = and IS operators work as speci

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

Adrián Medraño Calvo wrote: > The following SQL script shows a query selecting data from a recursive > CTE and filtering it. I expected the optimizer to apply the filter to > the recursive CTE directly, and indeed the documentation of > pushDownWhereTerms (src/select.c:3833) indicates this possibi

Re: [sqlite] High performance and concurrency

Shevek wrote: > Why would I have a transaction of non-zero size on a read-only connection? What do you mean with "size"? A read-only transaction still puts a shared lock on the database file. A read-only transaction will not change the DB file, but SQLite has lots of internal data structures in

Re: [sqlite] Dealing with SQLITE_BUSY

Nick wrote: > I use sqlite3_open() to open two connections, and I have configured > journal_mode=WAL, threadsafe=2. > > Connection 1 is doing: > sqlite3_exec(db1, "BEGIN", 0, 0, &zErrMsg); > sqlite3_exec(db1, "SELECT * FROM t1;", 0, 0, &zErrMsg); > sqlite3_exec(db1, "INSERT INTO t1 VALUES(1, 1, \"a

Re: [sqlite] Help with row values

Dominique Devienne wrote: > in https://www.sqlite.org/src/info/f3112e67cdb27c1a > to fix above ticket, I see queries with order by +a, > but in https://www.sqlite.org/lang_select.html#orderby > I don't see any obvious mention about that +. "Unary plus" does not change the value: sqlite> select

Re: [sqlite] Proposed registration for application/vnd.sqlite3 and +sqlite3

I wrote: > Type name: > > application > > Subtype name: > > vnd.sqlite3 > +suffix > > +sqlite3 ... and they are registered: https://www.iana.org/assignments/media-types/media-types.xhtml https://www.iana.org/assignments/media-type-structured-suffix/media-type-structured-suffix.xhtml Rega

Re: [sqlite] calculated columns in views

Simon Slavin wrote: > On 12 Feb 2018, at 6:00pm, Istvan Heckl wrote: >> Because of this bug at the moment SQLite can not be used with Entity >> Framework if calculated columns are involved. > > SQLite does not have calculated columns. I guess he means something like this (which works fine in plai

Re: [sqlite] sqlite3_close_v2 leading to database corruption

Jens Alfke wrote: > 3. The database is closed with sqlite3_close_v2. The statement is still > alive, so the database handle secretly remains open in "zombie mode". > 4. We delete the database's parent directory and all files in it. > [...] > This scenario isn't covered in "How To Corrupt An SQLite

Re: [sqlite] fts5 giving results for substring searches for Hindi content.

raj Singla wrote: > create virtual table idx4 using "fts4" (content); > create virtual table idx5 using "fts5" (content); > ... > select * from idx4 where idx4 match 'पाकि';-- no results returned > select * from idx5 where idx5 match 'पाकि';-- returns नीरजा भनोट के FTS4 and FTS5 have different def

Re: [sqlite] Performance issue in different versions

Nick wrote: > I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2. How? Your own copy, or the system one? Did you use the same configuration? Especially SQLITE_SECURE_DELETE? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mai

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

Danny Milosavljevic wrote: > I'm trying to prepare a statement for "SELECT a FROM t ORDER BY ?" and > then sqlite3_bind_int the parameter to 1 (on sqlite 3.19.3). > > Expected result: Orders result by column "a", in ascending order. > Observed result: Orders in some strange order. Ordering by colu

Re: [sqlite] missing subquery flattening

Mark Brand wrote: > Shouldn't we expect subquery flattening to happen in V2 below? > > -- no flattening > CREATE VIEW V2 AS > SELECT * FROM X > LEFT JOIN ( > SELECT * FROM X > LEFT JOIN Y ON Y.a = X.a > ) Z > ON Z.a = X.a; > > -- manually flattened version of V2 > CR

Re: [sqlite] UTF8 and NUL

J Decker wrote: > U+009C 156 String Terminator ST "ST is used as the closing delimiter of a control string opened by APPLICATION PROGRAM COMMAND (APC), DEVICE CONTROL STRING (DCS), OPERATING SYSTEM COMMAND (OSC), PRIVACY MESSAGE (PM), or START OF STRING (SOS)." Regards, Clemens _

Re: [sqlite] sqlite command line tool fails to dump data

J Decker wrote: >> *If any NUL characters occur at byte| offsets less than the value of the >> fourth parameter then the resulting| string value will contain embedded >> NULs.* > > So it's best used as data, and not keys > and I see unless custom aggregate()s or function()s ... If you want embedde

Re: [sqlite] sqlite command line tool fails to dump data

J Decker wrote: > insert into test (a,b) values ( ?,? ) > bind 'hello\0world.' 'te\0st' says: | If a non-negative fourth parameter is provided to sqlite3_bind_text() | or sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter | must be the

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

x wrote: > is there an easy way of checking if the db file has been modified > since the RowIDs query has been run? The easiest way is to keep the transaction open, then you know that there have not been any modifications, as far as you can see. :o) > I’m sure I read something about a db related

Re: [sqlite] WAL and pragma uncommitted

Deon Brewis wrote: > What is the level of consistency (or rather inconsistency) for > read_uncommitted? In read_uncommited mode, read-only transactions to not take the database file lock. However, most sqlite3_xxx() function calls still lock the in-memory database object(s) (this is required for

Re: [sqlite] WAL and pragma uncommitted

Hannah Massey wrote: > If I use separate connections for the reading threads then is there an > advantage to using "shared cache" for those connections? The shared cache would be useful to reduce memory usage (which should not be a concern except in embedded systems), but concurrent accesses to th

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

petern wrote: > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0)) > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips; > sum(s),"(SELECT sum(s) FROM flips)" > 1,3 > --Expected output is 1,1. > > Why isn't the constant notional table table [flips] materialized just once > per CTE?

Re: [sqlite] sqlite_column_table_name() and table alias name

Jake Chen wrote: >> On 15 Jan 2018, at 9:14 PM, Clemens Ladisch wrote: >> SELECT id FROM articles JOIN tags USING (id); > > `id` is ambiguous here. It's not, because of the USING clause. Regards, Clemens ___ sqlite-users ma

Re: [sqlite] report a bug for fts5

叶落天下秋 wrote: > select * from fts where fts match 'H & M'; > synatx error near & The documentation says: | An FTS5 bareword is a string of one or more consecutive characters that are all either: | | * Non-ASCII range characters (i.e. unicode codepoint

Re: [sqlite] sqlite_column_table_name() and table alias name

J Decker wrote: > What is the expected output? And just out of curiosity: what should the table name be for these columns? SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags USING (id); Regards, Clemens ___ sqlite-users mailing l

Re: [sqlite] Defect: single row table cross join causes infinite loop

petern wrote: > there is an infinite loop when params table column "n" is used. > WITH params(n) AS ( >VALUES (5) > ), > coinflip(flip,side) AS ( >SELECT 1, random()>0 >UNION ALL >SELECT flip+1, random()>0 FROM coinflip > ) > SELECT flip,side FROM coinflip,params WHERE flip%n=

Re: [sqlite] possible bug: separator string and quote mode

p dev wrote: > https://www.sqlite.org/cli.html 5. Changing Output Formats > > All columns are separated from each other by a comma (or whatever alternative > character is selected using ".separator"). This is indeed a bug in the documentation; quote mode ignores the .separator setting. > I hav

Re: [sqlite] database type graph

merlinverde...@infomed.sld.cu wrote: > How can I design in a database a database type graph? As the first step, by telling us what a "database type graph" is. The meaning was probably lost in translation. Regards, Clemens ___ sqlite-users mailing list

Re: [sqlite] drop table if exists X

Kenneth Shuai wrote: > conn = sqlite3.connect(db_dest) > > sql = """ > attach db_source.db as db_source; > drop table if exists tbs1 # it is used to check if tbs1 already in db_dest. > """ > Conn.execute(sql) Okay, let's try: >>> conn = sqlite3.connect(db_dest) Traceback (most recent call la

Re: [sqlite] The performance of indexed select

Nick wrote: > Or in another word, if a TEXT column has similar meaning with an INTEGER > column in my applications,(such as use userID instead of userName, still the > way that the data works in my head:) ) is it recommended to use INTEGER one > in order to get a less index pages? Yes; an index on

Re: [sqlite] The performance of indexed select

Nick wrote: >I am trying to analysis the performance of indexed select. > >CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT); >CREATE INDEX t2c ON t2(c); Show the query that you are trying to analyze. Regards, Clemens ___ sqlite-users mailing list sqlite

Re: [sqlite] Database Variable

Gregory Moore wrote: > void *_database; > > int openCode = sqlite3_open(databasePathC, (sqlite3**)&_database); > > is there some advantage to declaring the database variable as a void > pointer and casting it as sqlite3 verses just declaring it as sqlite3? Not really. A plain void pointer does no

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Wolfgang Enzinger wrote: > Am Mon, 1 Jan 2018 16:20:21 +0100 schrieb Clemens Ladisch: >> It is indeed possible to change the query so that SQLite uses rowid >> lookups for the R-tree filter (INDEX 1). However, any likelihood on the >> R-tree search expression still did no

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Luuk wrote: >> Clemens Ladisch wrote: >>> Luuk wrote: >>>> On 01-01-18 03:14, Shane Dev wrote: >>>>> select * from nodes where not exists (select * from edges where >>>>> child=nodes.id); >>>> >>>> Changing t

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Shane Dev wrote: > Apart from visual inspection and testing, is there anyway to be sure your > query selects the same results as my query? Can I interest you in things like relational algebra or tuple calculus? ;-) >>> select * from nodes where not exists (select * from edges where >>> child=nod

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Luuk wrote: > On 01-01-18 03:14, Shane Dev wrote: >> select * from nodes where not exists (select * from edges where >> child=nodes.id); > > Changing this to: > > select * from nodes where not exists (select 1 from edges where > child=nodes.id); > > saved in my test about 10% of time Then I hav

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Wolfgang Enzinger wrote: > Am Mon, 1 Jan 2018 10:45:50 +0100 schrieb Clemens Ladisch: >> Wolfgang Enzinger wrote: >>> Let SQLite know about that likelihood in a JOIN query >> >> This does not appear to change anything with a virtual table: >> >>

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Shane Dev wrote: > CREATE TABLE nodes(id integer primary key, description text); > CREATE TABLE edges(parent not null references nodes, child not null > references nodes, primary key(parent, child)); > > select * from nodes where not exists (select * from edges where > child=nodes.id); > > This w

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Wolfgang Enzinger wrote: > First, query the overall extent of your data, like this: > SELECT MIN(mingx),MAX(maxgx),MIN(mingy),MAX(maxgy) FROM flst_shape_index; This results in a full table scan. Instead of caching these values manually, it would be a better idea to read them from the index: SE

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Andrea Aime wrote: > So I was wondering, is there any opportunity to run a blazing fast pre-query > against > the index that will tell me whether joining/subquerying into the rtree is > going to be a win, or not? Each node in an R-tree index stores the coordinates of the leaf objects/child node

Re: [sqlite] .load fails

Joop! wrote: >sqlite> .load /usr/lib/sqlite3/pcre.so >Error: /usr/lib/sqlite3/pcre.so.so: cannot open sqlite3 always adds ".so". Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/

Re: [sqlite] Kind of function out of common table expression

Yannick Duchêne wrote: >I wonder is there is a way with SQLite3, to reuse a often used and >moderately long common table expression without the need to copy/paste >it in every query text it is used in. If you do not need dynamic parameters, use a view. Otherwise, you have to write out the subquery

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

Karl Forner wrote: > On Tue, Dec 19, 2017 at 3:12 PM, Clemens Ladisch wrote: >> Where does the hex number in "excluded_mice_temp2b5036f270" come from? >> Is it random? > > Yes, thanks, I think I got it. In order to alter tables, we first move them > to a new

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

Karl Forner wrote: > Here's a screenshot of some of the diffs using vbindiff > > https://ibb.co/kNm0X6 SQLite uses hash tables for schema objects, so different names might result in such differences. Where does the hex number in "excluded_mice_temp2b5036f270" come from? Is it random? Regards, C

Re: [sqlite] Atomic DELETE index optimisation?

Dinu wrote: > The WAL size is in excess of 9G atm. > The WAL size is growing ever slower (was about 10M/s, now it's 1M every 2s, > slowing down). This indicates to me a non-linear process that I can link > only to the B-trees, it's the only non-linear component I can think of that > could cause thi

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

Tony Papadimitriou wrote: > I really don't know what the standard says, but here are two different > opinions in implementation. > > MySQL example: You know that the "SQL" in "MySQL" is actually the abbreviation of "something quite loose"? ;-) Anyway, it appears even MySQL conforms to SQL-92 sub

Re: [sqlite] Can a trigger recursively update a table?

Shane Dev wrote: > On 14 December 2017 at 12:59, Clemens Ladisch wrote: >> Shane Dev wrote: >>> Can we conclude there is no single CTE or other SQL statement which can >>> update a branch of the tree starting with a flexibly specified node? >> >> That shoul

Re: [sqlite] Can a trigger recursively update a table?

Shane Dev wrote: > Can we conclude there is no single CTE or other SQL statement which can > update a branch of the tree starting with a flexibly specified node? That should be possible when you enable recursive triggers: begin update hierarchy set status = null where id = old.id; delet

Re: [sqlite] API, sqlite3_value_bytes and sqlite3_value_blob...

dave wrote: > "... the pointer returned from sqlite3_value_blob(), .. can be invalidated > by a subsequent call to sqlite3_value_bytes(), ..." > Is that statement still true? The returned pointer is invalidated when you call a function that forces SQLite to convert the value into another type. R

Re: [sqlite] Automatic index, despite existing index?

Olivier Mascia wrote: > 20171201 120319.404 284: automatic index on REMINDER(USER_LOGON) > > Here is that part of the schema: > > CREATE INDEX IX_REMINDER_USER on REMINDER(USER_LOGON); > > Could it be that it might need a DESC index? Probably not; the index direction usually does not matter. But

Re: [sqlite] Automatic index on mi(ID) or tal(ID)

Olivier Mascia wrote: > I'm seeing such things in my error.log out of SQLite 3.21.0 (but I am sure it > was already the case with 3.20): > > automatic index on tal(ID) > automatic index on mi(ID) > ... > > I do not have a clue as to what these 'tal(ID)' and 'mi(ID)' refer to. "tal" and "mi" sound

Re: [sqlite] EXISTS optimisation?

Dinu wrote: > I triple-checked it and it is indeed generating different execution plans Probably different indexes? Are these actual tables? >> select * from t where exists (select * from lookup where x = t.x); >> select * from t where x in (select x from lookup); >> select t.* from t join looku

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

Peter Halasz wrote: > However, it's quite clear to me, as only a user of just a few of these > tools and libraries, that they would be improved greatly if they had a > direct way of querying which field in a SQLite table was acting as an alias > of ROW ID. The rowid column must have the type "inte

Re: [sqlite] How to use sqlite3_table_column_metadata?

Bart Smissaert wrote: > If I pass a valid connection and valid table name but null for all other > input parameters I get: > > return value: 0 > > How does that work? The documentation says: | If the column-name parameter to s

Re: [sqlite] sqlite3_get_auxdata() defect

petern wrote: > Evidently the sqlite3_get_auxdata() API is very buggy. The documentation says that | under some circumstances the associated metadata may be preserved. You have found circumstances where the metadata is not preserved. This is not a bug. I can see that you are unhappy, but that i

Re: [sqlite] sqlite3_get_auxdata() defect

petern wrote: > So, at the very least, the documentation at > https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete because: > > 1. The identical function in a different column of the same SELECT has a > separate meta-data cache. This is an implementation detail of the current version.

Re: [sqlite] How to use sqlite3_table_column_metadata?

Bart Smissaert wrote: > Having some difficulty using sqlite3_table_column_metadata, used from VB6. > > I keep getting a zero for the first output argument (should be pointer to > declared data type string) > and the return value of the function keeps giving 1. The documentation

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

Shane Dev wrote: > Any ideas to achieve this? Use another CTE to bring all rows into the correct order. Then a standard tree walk: WITH RECURSIVE data AS ( SELECT id, line, par, 0 AS close FROM vtag UNION ALL SELECT id, line, par, 1 FROM vparent_closetag ORDER BY id, close -- thi

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

Simon Slavin wrote: > On 26 Nov 2017, at 8:02am, Shane Dev wrote: >> Any ideas to achieve this? > > Use the UNION keyword to combine the results of the two SELECT commands That would not order the close tags correctly. >> Any ideas to achieve this? Would it be possible to have parentid fields?

Re: [sqlite] Simple read-only program very slow

Kevin O'Gorman wrote: > I wrote a super simple program to the read the file and count how many > records are already there. I got impatient waiting for it so I killed > the process and added an output of one dot (".") per 1000 records. It > went very fast for what I estimate was around 200 dots a

Re: [sqlite] Help . . . I want to Export my Firefox Bookmarks to .CSV in one click, using SQLite3 in a .BAT file

You have to do two things to run SQL statements from a batch file: 1. Use quotes so that all parameters to sqlite3.exe are a single parameter; and 2. Use quotes so that SQL strings are delimited correctly. e-mail wrote: > sqlite3.exe -csv "C:\...\places.sqlite" "SELECT ... > datetime(...,"unixepo

Re: [sqlite] EXISTS optimisation?

Constantin Emil MARINA wrote: > I am wondering if in SQLITE the EXISTS clause is expanded and optimized in > any way. No. > This is generated by the observation that 2 algebrically equivalent queries, > SELECT WHERE EXISTS () > and > SELECT WHERE id IN (SELECT ...) > produce different execut

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

Shane Dev wrote: > Why do I want store ID numbers whose values may change? Why not. Because the name "ID" implies that its value _identifies_ the row. If it changes, it is not an ID. > Obviously, this would be bad idea if the ID column was referenced by > other column / table. In that case, I wou

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

Simon Slavin wrote: > UPDATE fruit SET id = id+1 WHERE id >=2; This is unlikely to work because some ID values can conflict in the middle of the execution. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://maili

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

Shane Dev wrote: > CREATE TABLE fruit(id integer primary key, name text); > > id|name > 1|apple > 2|pear > 3|kiwi > > Is there an easy way to insert 'banana' between apple and pear while still > maintaining a consistent order of the ID field? It would be possible, but not easy, especially not in p

Re: [sqlite] Starting with TCL

Simon Slavin wrote: > I thought in Tcl everything was a function ? In Tcl, all values are strings. A list is a string with entries as words according to the Tcl syntax rules. A dictionary is a list with an even number of elements (key/value pairs). (An array is not a v

Re: [sqlite] Valid characters for indentifiers

Simon Slavin wrote: > On 14 Nov 2017, at 7:56am, Clemens Ladisch wrote: >> The documentation claims ANSI SQL 1992 compatiblity; all identifiers >> conforming to the standard are supported. > > SQLite is case-insensitive for entity names. SQL92 says that case matters. Wha

Re: [sqlite] Valid characters for indentifiers

Jens Alfke wrote: > On Nov 11, 2017, at 6:31 AM, Simon Slavin wrote: >> There is no documentation for this. Which means that even if you find >> that, say, macrons are allowed in this version, they might not be allowed >> in the next version. > > Um, really? That sounds bad for compatibility. Why

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