Re: [sqlite] Stored Procedures

> That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very > cheap" either - it doubles writes (once to the rollback journal, once to > the DB), forces syncs, and likely results in a more seek heavy i/o pattern > (this depends a bit on schema design and whether the app

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

AFAIK the only way to access single memory database from multiple connections is through shared cache. 2018-05-15 0:27 GMT+02:00, Keith Medcalf : > >>2018-05-13 12:50 GMT+02:00, Techno Magos : > >>> Hello > >>> I do not have clear examples to post on this but would like to

Re: [sqlite] Broken DB?

http://www.sqlite.org/howtocorrupt.html 2018-04-27 23:58 GMT+02:00, Kevin O'Gorman: > I've got a working site, but I made a copy of the database in order to do > some development work. > I've hit a snag that looks like a problem in the data. > > Ive written a management command to show the

[sqlite] '.open' without args crashes shell

SQLite version 3.24.0 2018-05-09 16:32:00 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .version SQLite 3.24.0 2018-05-09 16:32:00 9f7a6ae878cd17ff4de7c55e654406773e0ea2b9fe1c4e2a9fc2b0da84d059a4 zlib

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

I have a few ideas you could try with a file db. 1. Use VFS with no locks - named "win32-none" or "unix-none" depending on your system (4th argument to sqlite_open_v2). 2. Run "pragma locking_mode=exclusive;" on each connection or compile SQLite with -DSQLITE_DEFAULT_LOCKING_MODE=1 3. Compile

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

Try to open N separate database connections (without shared cache) and load content using sqlite3_deserialize with SQLITE_DESERIALIZE_READONLY flag. http://www.sqlite.org/c3ref/deserialize.html SQLite won't copy data but use provided buffer so you won't have N copies of databse. 2018-05-13 12:50

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

I would make something like this: CREATE TABLE blocked( mail TEXT PRIMARY KEY, action TEXT NOT NULL DEFAULT 'DISCARD' ) WITHOUT ROWID; INSERT INTO blocked(mail) VALUES('badm...@example.com'); SELECT coalesce(action, 'OK') AS action FROM (SELECT 'goodm...@example.com' AS mail)

Re: [sqlite] Thoughts about the sqlite3IntFloatCompare function from the vdbeaux.c file.

Some test: https://ideone.com/zkHHty 2018-05-18 13:36 GMT+02:00, Abroży Nieprzełoży <abrozynieprzelozy314...@gmail.com>: > > 1: static int sqlite3IntFloatCompare(i64 i, double r){ > 2: if( sizeof(LONGDOUBLE_

[sqlite] Thoughts about the sqlite3IntFloatCompare function from the vdbeaux.c file.

1: static int sqlite3IntFloatCompare(i64 i, double r){ 2: if( sizeof(LONGDOUBLE_TYPE)>8 ){ 3: LONGDOUBLE_TYPE x = (LONGDOUBLE_TYPE)i; 4: if( xr ) return +1; 6: return 0; 7: }else{ 8: i64 y; 9: double s; 10:

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

C:\test>sqlite3 test.db SQLite version 3.24.0 2018-05-18 17:58:33 Enter ".help" for usage hints. sqlite> .version SQLite 3.24.0 2018-05-18 17:58:33 c6071ac99cfa4b6272ac4d739fc61a85acb544f6c1c2ae67b31e92aadcc995bd zlib version 1.2.11 msvc-1912 sqlite> CREATE TABLE xqlite_sequence(name TEXT PRIMARY

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

btw 'PRAGMA integrity_check;' returns 'ok' 2018-05-20 18:27 GMT+02:00, Abroży Nieprzełoży : >> The documentation says: >> **"Warning: misuse of this pragma can easily result in a corrupt database >> file."** > > But it would be nice if corrupted dat

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

> The documentation says: > **"Warning: misuse of this pragma can easily result in a corrupt database > file."** But it would be nice if corrupted database didn't crash the application ;) 2018-05-20 16:05 GMT+02:00, Clemens Ladisch : > Abroży Nieprzełoży wr

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

bug has been in the code since version 3.8.7 (2014-10-17). I > think you can wait a few days for the fix. > > > > On 5/19/18, Abroży Nieprzełoży <abrozynieprzelozy314...@gmail.com> wrote: >> C:\test>sqlite3 test.db >> SQLite version 3.24.0 2018-05-18 17:58:33 >>

Re: [sqlite] Function to use to convert a text float to a float for use in ORDER BY

> select col1, col2 from mytable order by cast(body_size as float); And if you care about speed of select, you could create appropriate index: create index mytable_bodysize_index on mytable(cast(body_size as float)); 2018-05-18 0:13 GMT+02:00, Keith Medcalf : > > Why not

[sqlite] A fix for the exp-window-functions branch to build by nmake.

Index: Makefile.msc === --- Makefile.msc +++ Makefile.msc @@ -1305,11 +1305,12 @@ $(TOP)\src\vtab.c \ $(TOP)\src\wal.c \ $(TOP)\src\walker.c \ $(TOP)\src\where.c \ $(TOP)\src\wherecode.c \ - $(TOP)\src\whereexpr.c +

Re: [sqlite] Selecting with distinct on across two columns

SQLite allows you to simply do: select colA, colB, colC from tab group by colA, colB; You can also do: select colA, colB, min(colC) from tab group by colA, colB; or: select colA, colB, max(colC) from tab group by colA, colB; 2018-06-10 1:41 GMT+02:00, Joseph L. Casale : > I have a

Re: [sqlite] Newbie help

You can use this quick piece of software listed below to redirect std i/o of a program to a tcp connection. Assuming the executable file is named tcpcmd.exe. Following command line tcpcmd 12345 "sqlite3 -batch" will run "sqlite3 -batch" and listen on tcp port 12345 (for security reasons it

Re: [sqlite] Possible Input Parser Issue Inf How to convert SQL file into database when a column value is Inf?

or .dump should produce output with 1e999 / -1e999 instead of Inf / -Inf 2018-06-13 1:11 GMT+02:00, Chris Brody : > On Tue, Jun 12, 2018 at 6:40 PM Richard Hipp wrote: >> [...] >> Maybe use 1e999 and -1e999 instead? > > I can confirm on SQLite versions 3.19.2 & 3.24.0: > > sqlite> select 1e999;

Re: [sqlite] Check if the new table has been created

afaik there is no such api. You need to periodically check if something changed. Run pragma schema_version; to get current schema version. If it changes then run select name from sqlite_master where type='table' and name not like 'sqlite_%'; to get the list of table names and compare this

Re: [sqlite] This is driving me nuts

m118...@hotmail.com>: > Hi Abrozy. > > > > I inserted the line > > int64_t Mem=sqlite3_memory_highwater(0); > > immediately after the while loop and added Mem to the cout. The values > returned were > > 2234704 (I==0) > > 2234768 (I==1). >

Re: [sqlite] This is driving me nuts

> I swapped Size[0] with Size[1] so that the resize was going from large to > small. The timings on each pass were then similar but were still around the > 24.xxx mark associated with the i==1 pass. Note the difference between size and capacity of the vector. Reducing the size does not necessarily

Re: [sqlite] This is driving me nuts

I think you can experiment with changing the the working set size limits and see what the effect will be. https://msdn.microsoft.com/en-us/library/cc441804 2018-05-27 17:09 GMT+02:00, curmudgeon : > It seems the array was being optimised away. I had to initialise every

Re: [sqlite] This is driving me nuts

in threshold Abrozy. Thanks anyway. > > > > > From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf > of Abroży Nieprzełoży <abrozynieprzelozy314...@gmail.com> > Sent: Sunday, May 27, 2018 5:23:12 PM >

Re: [sqlite] random rows

hmm... i think: begin transaction; query 'select count(*) from tab' to get total number of records, name it N randomly select 200 integers from the range 0 to N-1 for each number query 'select * from tab limit 1 offset ?' with '?' bound to selected number end transaction; 2018-06-01 2:12

Re: [sqlite] This is driving me nuts

int rc; rc = enableLockMemoryPrivilege(); if (rc < 0) return -1; rc = configSqlitePhysMemory(); if (rc < 0) return -2; return 0; } ------- 2018-05-28 0:23 GMT+02:00, Abr

Re: [sqlite] database locked on select

BTW why not to update all rows by single update query? 2018-05-27 20:30 GMT+02:00, Torsten Curdt : > I am doing a select, then iterate through the resultset and on each row > call update on that row. > I am using the golang driver and ran into the issue that on the update the >

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

Do the select and updates run inside a explicit transaction or they run in individual implicit transactions? pseudocode: - exec(BEGIN);// <- Do you have this? st_sel = prepare(SELECT ...); st_upd = prepare(UPDATE tab SET c1=?1, c2=?2, WHERE id=?3); while(step(st_sel) ==

Re: [sqlite] How does _exec() do a transaction ?

sqlite3_exec doesn't open transaction by itself. Each statement is prepared and executed separately. 2018-05-30 15:33 GMT+02:00, Simon Slavin : > On 30 May 2018, at 2:30pm, Simon Slavin wrote: > >> Does SQLite perform the whole _exec() in one transaction or each command >> in a separate

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

> Are there any plans to implement a DATETIME and/or TIMESTAMP field types? I don't think so. The SQLite team really cares about backward compatibility. You can store timestamp as a unixepoch integer or as a text in format supported by date/time functions.

Re: [sqlite] column types and constraints

SQLite returns what has been saved in the database, the application should control what it writes to the database. If the database can be edited by an untrusted entity, the application should treat the content of the database as untrusted. Even if SQLite enforced data types, someone who can edit

Re: [sqlite] Check if the new table has been created

But that trigger would be executed in a context of a process modifying the database. 2018-06-20 8:24 GMT+02:00, Peter Johnson : > Is it possible to create a trigger on sqlite_master which calls a > user-defined function AFTER INSERT? > > That would avoid having to poll, but it'd still allow the

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

;> ? > > It still does not quarantee that the valuse show for b and c are comming > from the same row... select a, b, min(c) as c from (select a, min(b) as b from t1 group by a) join t1 using(a, b) group by a, b; ? 2018-06-30 15:45 GMT+02:00, Luuk : > > > On 30-6-2018 15:39, Abro

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

> SELECT >a, >(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B, >(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C > FROM t1 t > GROUP BY a Why not select a, min(b) as b, min(c) as c from t1 group by a; ? 2018-06-30 15:12 GMT+02:00, Luuk : > > On 30-6-2018 14:55, Keith Medcalf wrote: >> Note

Re: [sqlite] probably recursive?

I think Barry mean that you can represent the (x,y) pair as a single number like (max(X)-min(X))*(Y-min(Y))+X-min(X) or so, but I don't see how it would be helpful. 2018-05-02 0:20 GMT+02:00, Roman Fleysher: > Dear Barry, > > The statement about the square is not obvious to me. The requirements

Re: [sqlite] Query regarding CVE-2018-8740

I think sqlite-autoconf-3230100 should be OK. http://www.sqlite.org/2018/sqlite-autoconf-3230100.tar.gz Always check download page for the newest version http://www.sqlite.org/download.html 2018-04-27 1:11 GMT+02:00, salil GK: > Hello > >We are using sqlite-autoconf-322 in our product.

Re: [sqlite] Sqlite Sharding HOWTO

> Ideally, I would like to have a way of "seeing" the whole dataset with a > single query spanning all available databases. I think swarmvtab may be helpful. https://www.sqlite.org/swarmvtab.html 2018-07-29 10:34 GMT+02:00, Gerlando Falauto : > Hi, > > I'm totally new to sqlite and I'd like to

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

-- One table with whole files CREATE TABLE content( id INTEGER PRIMARY KEY, data BLOB NOT NULL ); -- And second table with line boundaries CREATE TABLE lines( id INTEGER NOT NULL REFERENCES content(id), line_no INTEGER NOT NULL, bytes_from INTEGER NOT NULL,

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

substr(data, start, len) loads entire value and then substrs it. sqlite3_blob_read reads the desired parts. 2018-08-06 20:59 GMT+02:00, Abramo Bagnara : > Il 04/08/2018 07:07, Abramo Bagnara ha scritto: >> Il 03/08/2018 23:53, Abroży Nieprzełoży ha scritto: >>> -- One tabl

Re: [sqlite] bug: compare number with empty string get different resut

It's documented behaviour. https://www.sqlite.org/datatype3.html#comparisons 2018-08-19 8:58 GMT+02:00, 麦轲数据管家 : > create table t1(c int); > insert into t1 values (1),(2),(3); > select * from t1 where c>'' ; --get nothing(empty result) > select * from t1 where c<''; --get result set:1,2,3 >

Re: [sqlite] Get data in one query

Julian Day is defined in terms of Universal Time. https://en.wikipedia.org/wiki/Julian_day 2018-08-29 22:33 GMT+02:00, David Raymond : > Good to know. Is that actually documented anywhere? All I see is... > > > https://www.sqlite.org/lang_datefunc.html > "Format 11, the string 'now', is

[sqlite] 'replace ... do update...' crash

sqlite> .version SQLite 3.24.0 2018-04-20 18:01:31 279c48f6061f766f5437edd6964c0dd1e10399314eb17b6e5ee34df925a7alt1 zlib version 1.2.11 msvc-1912 CREATE TABLE abc( a TEXT UNIQUE, b TEXT UNIQUE ); CREATE TABLE tt( id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT ); CREATE TRIGGER

Re: [sqlite] Persistent snapshots and rollbacks

The sessions extension: https://www.sqlite.org/sessionintro.html 2018-10-05 17:39 GMT+02:00, Daniel Kraft : > Hi! > > I need the ability to make multiple changes / commits to my SQLite > database but keep snapshots of previous states and potentially roll back > to those states later on. All of

Re: [sqlite] How to refer to `this` table?

2019-02-23 10:03 GMT+01:00, Rocky Ji: > I went from > > https://sqlite.org/lang_createtable.html > > to > > https://sqlite.org/syntax/table-constraint.html > > to > > https://sqlite.org/syntax/expr.html > > and figured expr of `check` in table constraint may contain a nested select > after `not