[sqlite] INSERT OR IGNORE query inside of a body of trigger, fired by INSERT OR REPLACE query, works as if it was INSERT OR REPLACE.

Let's say I have a following database structure: CREATE TABLE properties ( ?name? TEXT NOT NULL, ?value TEXT, ?PRIMARY KEY(name) ) WITHOUT ROWID; CREATE TABLE foo ( ?id??? TEXT NOT NULL, ?PRIMARY KEY(id) ); CREATE TRIGGER foo_inserted ? AFTER INSE

[sqlite] Performance comparison between SQLite and SQL Server?

The quality and helpfulness of this mailing list makes me wish for a SQHeavy...

[sqlite] What's the reason for alias names not being visible in subqueries?

, Feb 8, 2016 at 3:38 AM, Paul wrote: > > I see, thank you for pointing out. > > > > I wanted to use it on table with conditional relations with 3 different > > child tables. > > Though I could use a trick and fit data selection into one query, > > effici

[sqlite] What's the reason for alias names not being visible in subqueries?

emens Ladisch" : > Paul wrote: > > I am curious what is a particular reason that aliased columns in a query > > not visible to sub-queries? > > Because the SQL standard says so. > > > Of course it is possible to duplicate expression in sub-query ... > > B

[sqlite] What's the reason for alias names not being visible in subqueries?

Hello! I am curious what is a particular reason that aliased columns in a query not visible to sub-queries? CREATE TABLE foo( id INTEGER, bar INTEGER ); INSERT INTO foo VALUES(1, 2), (3, 4); SELECT 1 as super_id, (SELECT bar FROM foo WHERE id = super_id); Gives an error: Error: no suc

[sqlite] Question about LIMIT 1 fitness on lookups by unique index

>From now on I wont use "LIMIT 1" in these cases any more. Such queries will be more readable and surely not slower. Thank you for explanation! Paul 5 February 2016, 14:21:35, by "Richard Hipp" : > On 2/5/16, Paul wrote: > > > > Does it have any

[sqlite] Question about LIMIT 1 fitness on lookups by unique index

now, finally I want to make thing clear. Thanks, Paul

[sqlite] UPSERT

What is the reason to pay extra overhead if it can be omitted? And actually, SQLite does a great job with concurrency, from my experience. There's really smart locking model employed. And this is really tedious to work around: 1) You cannot disable FK constraints inside a transaction 2) Would

[sqlite] Are there practical limits to a not-so simple schema?

If you ever going to use ANALYZE on your database and database is going to be open frequently (like once per request) consider dropping sqlite_stat3 and sqlite_stat4 tables. SQLite reads content of those tables on each open. Number of tables greatly contributes to amount of data stored in ther

[sqlite] Porting SQLITE-3.10 into VxWorks-6.9

Hi All,

[sqlite] SQLite - Support for VSS writer

You shouldn't need to back up the shm file as this will be recreated if needed. You naming convention is also off, you mean mydatabase.sqlite-journal mydatabase.sqlite-wal mydatabase.sqlite-shm so mydatabase.sqlite* would be what's required (which would include the unneeded shm fi

[sqlite] Understanding WALs

Thanks Richard a nice simple answer to pass on. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully

[sqlite] Understanding WALs

to explain to him what is going on. Cheers Paul Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite Request a demo here : http://sandersonforens

[sqlite] How would sqlite read this from disk?

arcane math from the page size (see the file format documentation), a row may overflow to one or more pages . Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit

[sqlite] Database corrupt after hard reboot. Can it be repaired?

base can potentially be used to create a working solution. More information - the DB schema particularly - and what you need recovered for it to be a success (even a partial success) would be good. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44

[sqlite] Encrypted or Corrupt File

What chat application are they from. What was the last thing that you did before they stopped working Is there anything else on your computer that could be opening these fles Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http

[sqlite] Speed of count distinct rows if unique index on all fields

distinct records page and every record in the tree must be read and examined, to count all records just every page header in the tree needs to be read. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195

[sqlite] Doc page revision request

The problem seems to be with the web filter and not the abbreviation cnt. I would suggest that the onus should be on them to adjust their filter to prevent filtering of an innocuous word (its only rude/offensive if the u is added). Paul www.sandersonforensics.com skype: r3scue193 twitter

[sqlite] SQLite transaction time limit

You are probably asking for 8 July 2015, 15:24:18, by "Iryna Atamanova" : > Hi guys, > > I will be very appreciate for your help. > > We have found the next feature of sqllite - it transaction takes more that > 5 seconds, the database has been

[sqlite] Tables and Columns of Database of Whatsapp

The WhatsApp db is encrypted (using encrypt7 IIRC) - there are sources on the web that show how to get around this - but it's not trivial. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-S

[sqlite] This mailing list seems to be public

I thought it had always been open - you just need to subscribe to be able to post. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a

[sqlite] implicit vs explicit joins

Thank You Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 11 June 2015 at 12

[sqlite] implicit vs explicit joins

olumn|0|2|3||00| 10|Column|1|1|4||00| 11|ResultRow|3|2|0||00| 12|IfPos|1|15|0||00| 13|NullRow|1|0|0||00| 14|Goto|0|8|0||00| 15|Next|0|4|0||01| 16|Close|0|0|0||00| 17|Close|1|0|0||00| 18|Halt|0|0|0||00| 19|Transaction|0|0|44|0|01| 20|TableLock|0|5|0|message|00| 21|TableLock|0|12|0|handle|00| 2

[sqlite] Patch for build on ppc64le systems

.db.sqlite.general/85660/match=config+guess which may be related. Replacing config.guess and config.sub worked for me. I am happy to validate operation on the POWER system for you once the latest config.guess and config.sub files are in the sqlite source. Thank you, Paul Paul G Crumley STSM & Manager IBM Research

[sqlite] saving :memory:database to disk

Thanks Marc - it's the latter, very dynamic. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully funct

[sqlite] saving :memory:database to disk

I am contemplating a change to a program whereby a database is initailly created in memory and then later if my users choose they can save it to disk and then switch to using the disk based DB. I can obviously create a new disk based db, iterate through sqlite_master and then populate each table.

[sqlite] Destroy all evidence of a database

database. This might be a level of access you are not concerned with - I guess that all depends on how sensitive sensitive is :) Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-To

[sqlite] Different timings on min() and max()

00 sys 0.00 sqlite> select min(dtime), max(dtime) from intstats; 1416441600|1426999800 CPU Time: user 221.806280 sys 47.434789 sqlite> sqlite> select count(*) from intstats; 607009116 CPU Time: user 6.341036 sys 20.271918 sqlite> sqlite> Thanks, Paul

[sqlite] Why is empty string not equal to empty blob?

> On 19 Mar 2015, at 3:19pm, Paul wrote: > > > This may cause very nasty hard-to-find bugs, since SQLite allows to store > > any content inside BLOB field: > > > > sqlite> create table foo(a int, b int, primary key(a, b)); > > sqlite>

[sqlite] Why is empty string not equal to empty blob?

> On 3/19/15, Paul wrote: > > Maybe this question was already asked and explained. > > Or maybe it is documented somewhere (could not fiund it). > > Sorry, if this is the case, but why does > > > > SELECT '' = x''; > > > &g

[sqlite] Why is empty string not equal to empty blob?

of FOO, when SELECT length(my_blob) FROM FOO; returns 0 for both empty string('') and empty blob (x'') alike? And what is the reason behind this difference? Thanks, Paul

[sqlite] Is 'synchronous' pragma now being set on per database basis?

> On 3/16/15, Paul wrote: > > when the usage is > > > > (1) open database > > (2) execute 'PRAGMA synchronous = OFF;' > > (3) attach database X > > (4) execute 'PRAGMA X.synchronous = OFF;' > > (5) begin transaction > >

[sqlite] Is 'synchronous' pragma now being set on per database basis?

> On 3/16/15, Paul wrote: > > > Is doc page is > > outdated and 'synchronous' pragma is now set for each databse separately? > > It has always been that way. The documentation has recently been > updated to clarify that point. See >

[sqlite] Is 'synchronous' pragma now being set on per database basis?

oc page is outdated and 'synchronous' pragma is now set for each databse separately? How to avoid fsync() for directory? I know that it is 'not safe'. But same can be said about not syncing journal and/or database file when 'PRAGMA synchronous = OFF;' is set. Is there a specific reason for keeping fsync() for directory? Seqlite version that we use is: sqlite3-3.8.7_1 Thanks, Paul

[sqlite] error "attempt to write a readonly database"

Thanks - you pointed me in the right direction. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully

[sqlite] Can I copy one column of data to another table?

here: https://www.sqlite.org/lang_createtable.html Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional

[sqlite] error "attempt to write a readonly database"

Thanks Simon I'm an idiot - dev environment was running as admin - so nothing else could write to DB out side of this as a normal users. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.ph

[sqlite] error "attempt to write a readonly database"

I have a database I have created and populated with various tables and data. I now want to create a new table and I get the above error. command is create table testtab (id int, data blob); I am using sqlite 3.8.6 command line to try and create the table (although the DB was created using Devart

[sqlite] Regarding ALTER TABLE doc page

In the footer of it is said: It is important that both of the above procedures be run from within a transaction to prevent other processes from accessing the database file while the schema change is only partially complete. But this statement is n

[sqlite] Appropriate Uses For SQLite

). I don't think this is a good idea - SQLite (Doh! look at the name) does SQL - when something better comes along, then let it do that, but it should IMHO remain true to that. Respectfully submitted. Paul... > D. Richard Hipp -- linehanp at tcd.ie Mob: 00 353 86 864 5772

[sqlite] Sqlite subqueries

Search the mail list for "Column name as a variable" for a similar discussion Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email f

Re: [sqlite] sqlite journal file question

Richard I read that the db on the standby machine is being updated at a record by record level, i.e. not copied in its entirety. In this scenario I can't see the two db files being guaranteed binary compatible. Copying the journal across in this scenario would imo be a mistake.

Re: [sqlite] sqlite journal file question

level so replaying a journal to the wrong DB would almost certaibnly cause corruption. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email f

Re: [sqlite] binding multiple values in a query

uery parser will have some usefull information aboud what is expected in following bind. Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Huge WAL log

s and what I have seen examining these files, not on the workings/code in SQLite so perhaps Dr Hipp could correct me if I'm wrong. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic

Re: [sqlite] using a hex integer as input to DateTime

Sorry Carlos - vanilla sqlite is required. Its not a big issue for me. Cheers Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http

Re: [sqlite] using a hex integer as input to DateTime

Thanks Peter Coding outside of SQLite is easy - it's doing it with just SQLite/SQL that I was after :( Cheers Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -For

Re: [sqlite] using a hex integer as input to DateTime

converted into an integer and copied to base. I have tried various methods such as CREATE TRIGGER hex_trig after insert on dates when (select hex from dates where hex is not null) begin update dates set base = cast(new.hex as int); end but so far have drawn a blank Can this be done? Paul

Re: [sqlite] Segfault with Evolution and patched SQLite 3.8.7.4

Am Freitag, den 09.01.2015, 21:04 -0500 schrieb Richard Hipp: > On 1/9/15, Paul Menzel wrote: > > Am Dienstag, den 30.12.2014, 16:15 +0100 schrieb Paul Menzel: > > > > With still around 1.3 GB free on the partition mounted to `/var/`, > > Evolution crashed with t

Re: [sqlite] Segfault with Evolution and patched SQLite 3.8.7.4 (was: Bus error with Evolution 3.12.9 and SQLite 3.8.7.4)

Am Dienstag, den 30.12.2014, 16:15 +0100 schrieb Paul Menzel: > Am Dienstag, den 30.12.2014, 08:21 -0500 schrieb Richard Hipp: > > On Mon, Dec 29, 2014 at 10:37 AM, László Böszörményi (GCS) wrote: > > > > > it’s not obvious that these might cause such a regression. > &

Re: [sqlite] using a hex integer as input to DateTime

Hmm - why can't I get that to work when the hex value is stored in a column, i.e. select DateTime(base, 'unixepoch') from dates where there is a single row and base = 0x49962d2 Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1

[sqlite] using a hex integer as input to DateTime

(0x49962d2, 'unixepoch') or DateTime(HexToInt(0x49962d2), 'unixepoch') Is this possible? Not a problem if not, but would be nice. Thanks Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.p

Re: [sqlite] Bus error with Evolution 3.12.9 and SQLite 3.8.7.4

tion `/var`. I’ll rebuild SQLite now with the fix applied [2] and try to reproduce the issue by filling up `/var` intentionally. Thank you for the awesome support so far! Thanks, Paul [1] https://packages.debian.org/corekeeper [2] https://www.sqlite.org/src/info/776648412c30dce206f1024ff849c2

Re: [sqlite] Bus error with Evolution 3.12.9 and SQLite 3.8.7.4

Am Montag, den 29.12.2014, 16:37 +0100 schrieb László Böszörményi (GCS): > On Mon, Dec 29, 2014 at 2:09 PM, Paul Menzel wrote: > > using Debian Sid/unstable and upgrading from libsqlite3-0 3.8.7.2 to > > 3.8.7.4, Evolution 3.12.9 started to crash with a bus error [1]. > > Ju

[sqlite] Bus error with Evolution 3.12.9 and SQLite 3.8.7.4

aces attached to the bug reported in the GNOME Bugzilla [1]. Thanks, Paul PS: I have not submitted a bug report to the Debian BTS yet, as I do not know if it is a bug in Evolution or SQLite 3 and I want to avoid a false assignment as done by myself in [3]. [1] https://bugzilla.gnome.org/show_

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

statement rollback may be required is because > any replaced rows will be removed before SQLite has a chance to figure > out if the INSERT actually does violate the PK constraint. > I see, thank you for explanation, Dan. Best regards, Paul

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hi, Dan. > On 12/18/2014 02:41 PM, Paul wrote: > >> I want to confirm that issue is fixed for me. > >> Thanks again, Dan! > >> > >> > >> Please ignore this update, patch fixes this problem as well. > >> > >>> I want to add eve

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

> > I want to confirm that issue is fixed for me. > Thanks again, Dan! > > > Please ignore this update, patch fixes this problem as well. > > > I want to add even more input for this issue. > > I understand why there is implicit savepoint, when I remove row from > > 'parent' table. > > But wh

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

I want to confirm that issue is fixed for me. Thanks again, Dan! Please ignore this update, patch fixes this problem as well. > I want to add even more input for this issue. > I understand why there is implicit savepoint, when I remove row from 'parent' > table. > But why is this also true for

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

> On 12/16/2014 03:08 PM, Paul wrote: > >>>>> The memory is being used by the statement journal, which you have in > >>>>> memory. If the app did not set "journal_mode=memory" and > >>>>> "temp_store=memory", SQLite

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

I want to add even more input for this issue. I understand why there is implicit savepoint, when I remove row from 'parent' table. But why is this also true for a 'child' table when I perform 'INSERT OR REPLACE'? Removing FK reference disables journal growth. I don't understand... I have a new t

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

not happen? > Are you saying there is no YYY savepoint? Or the journal can be truncated > when omiting BEGIN oe XXX but not when they are both present? Please don't mind my last message. I understand now, what is going on. Yet this limitation is pretty depressing. Is there no way in the future for things to change? Can't journal be truncated, or modified pages be merged, after each consequential implicit sub-transaction (YYY) release, while they are still in the cache? Is there any hope for me, except deferred FKs or DELETE FROM foo WHERE id IN (...)? Best regards, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

> > > > > > The memory is being used by the statement journal, which you have in > > > memory. If the app did not set "journal_mode=memory" and > > > "temp_store=memory", SQLite would create a really large temp file > > > instead of using memory. Which would still be sub-optimal, but might > >

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hi Dan. > On 12/15/2014 11:59 PM, Dan Kennedy wrote: > > On 12/15/2014 11:11 PM, Paul wrote: > >> Hello, dear developers > >> > >> Recently I've stumbled upon a very rare and strange bug. > >> The result of this is abnormal memory usage, that doe

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hello Richard. > On Mon, Dec 15, 2014 at 11:11 AM, Paul wrote: > > > > Hello, dear developers > > > > Recently I've stumbled upon a very rare and strange bug. > > The result of this is abnormal memory usage, that does not allow us to > > remove >

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Sorry, I've forgot to mention my sqlite version, here it is: 3.8.7.1 2014-10-29 13:59:56 3b7b72c4685aa5cf5e675c2c47ebec10d9704221 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hello, dear developers Recently I've stumbled upon a very rare and strange bug. The result of this is abnormal memory usage, that does not allow us to remove fair number of rows from a table due to the limit of memory, available for 32bit process. This is strange, because database size is somewha

Re: [sqlite] '.timer on' in the shell tool

SELECT has returned a lot of data. Specifically that data was writen as an output to your console. If this is the case, a lot of system time was wasted just to write the results. It may be because each written line calls fflush() on stdout. Regards, Paul

Re: [sqlite] Feature Request - RowCount

his can actually be implemented as an SQLite add-on, via virtual table. So for example, instead of doing SELECT COUNT(*) FROM XXX; you could do SELECT count FROM vtb_row_count_cache WHERE table = 'XXX'; Just and idea... Regards, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up database open

> > On Thu, Dec 11, 2014 at 10:58 AM, Paul wrote: > > > > > > > > I have yet to try and test if dropping stat tables worth the effort. > > > > > > > Most of the work is involved in loading sqlite_stat4. On the other hand, > > most of t

Re: [sqlite] How to speed up database open

> > On 2014/12/11 17:58, Paul wrote: > > > >> On 2014/12/11 13:51, Paul wrote: > >> I have yet to try and test if dropping stat tables worth the effort. Some > >> databases in fact can grow pretty big, up to few > >> hundred of megabytes// &

Re: [sqlite] How to speed up database open

> On Thu, Dec 11, 2014 at 10:58 AM, Paul wrote: > > > > > I have yet to try and test if dropping stat tables worth the effort. > > > > Most of the work is involved in loading sqlite_stat4. On the other hand, > most of the benefit comes from sqlite_sta

Re: [sqlite] Counting rows

would count _rowid_ from mytable be quicker Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168

Re: [sqlite] How to speed up database open

Hi Ryan, thanks for reply. > > On 2014/12/11 13:51, Paul wrote: > > In my specific case I need to open database as fast as possible. > > Usual working cycle: open -> select small data set -> close. > > It is irrelevant how much time it takes to open database w

Re: [sqlite] How to speed up database open

2','4 7882 7882',X'040206062712532846f70de34506532846f70de34506'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx2','54 1 1','7888 7901 7901','5 7901 7901',X'040206062714543e590203e7543e590203e7'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','43 1 1','502 505 505','216 505 505',X'0402060600d95355321702f75355321702f7'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','37 1 1','721 743 743','286 743 743',X'04020606011f5367d26e01765367d26e0176'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','4 1 1','881 882 882','318 882 882',X'04020606013f536b48ab007c536b48ab007c'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','24 1 1','967 990 990','339 990 990',X'04020606015453734ece38c8140d53734ece38c8140d'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','55 1 1','1144 1167 1167','386 1167 1167',X'040206060183537de7df0d28fee1537de7df0d28fee1'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','22 1 1','1315 1327 1327','432 1327 1327',X'0402060601b153865fe10f27c29553865fe10f27c295'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','934 1 1','1371 1765 1765','446 1765 1765',X'0402060601bf538942320263538942320263'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','6 1 1','2643 2648 2648','569 2648 2648',X'04020606023a53a3c0f0014953a3c0f00149'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','22 1 1','3070 3085 3085','828 3085 3085',X'04020606033d53b6ce92036653b6ce920366'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','28 1 1','3217 3219 3219','907 3219 3219',X'04020606038c53bab9f6026953bab9f60269'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','21 1 1','3290 3309 3309','926 3309 3309',X'04020606039f53d2eb8814cf393353d2eb8814cf3933'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','1 1 1','3531 3531 3531','1078 3531 3531',X'04020606043753c09eb8027253c09eb80272'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','50 1 1','3560 3576 3576','1090 3576 3576',X'04020606044353c55fb5008853c55fb50088'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','24 1 1','3819 3831 3831','1168 3831 3831',X'04020606049153cfc5d41534a6d353cfc5d41534a6d3'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','28 1 1','4072 4085 4085','1250 4085 4085',X'0402060604e353d868fc2e105d2b53d868fc2e105d2b'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','36 1 1','4185 4201 4201','1286 4201 4201',X'04020606050753ea565437bc965253ea565437bc9652'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','1 1 1','4414 4414 4414','1410 4414 4414',X'04020606058353e36fe32fcf6de753e36fe32fcf6de7'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','7 1 1','5294 5297 5297','1795 5297 5297',X'0402060607045411c37a03415411c37a0341'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','37 1 1','5312 5315 5315','1802 5315 5315',X'04020606070b54126255028e54126255028e'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','21 1 1','5631 5647 5647','1941 5647 5647',X'040206060796541dfb2221d9cc09541dfb2221d9cc09'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','26 1 1','5854 5866 5866','2002 5866 5866',X'0402060607d35423282b105472ae5423282b105472ae'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','5 1 1','6176 6180 6180','2137 6180 6180',X'04020606085a543893a001a9543893a001a9'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','13 1 1','7054 7063 7063','2563 7063 7063',X'040206060a04545777f700b3545777f700b3'); INSERT INTO sqlite_stat4 VALUES('MMM','MMM_idx3','32 1 1','7817 7829 7829','2885 7829 7829',X'040206060b465485b26e2841de3f5485b26e2841de3f'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','0 1','0 1',X'0301010808'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','2 2','1 2',X'0301010907'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','14 1','14 24','12 24',X'0301013523'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','14 1','28 39','13 39',X'0301013624'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','14 1','42 49','14 49',X'0301013720'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','14 1','56 57','15 57',X'030101381a'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','1 1','74 74','20 74',X'0301016208'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','86 86','32 86',X'0301017c06'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','3 1','90 90','35 90',X'0301017f06'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','1 1','94 94','37 94',X'030201008108'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','1 1','99 99','42 99',X'030201008651'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','103 104','46 104',X'030201008a53'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','117 118','59 118',X'030201009754'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','1 1','124 124','65 124',X'030201009d57'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','1 1','139 139','80 139',X'03020101aa08'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','144 144','85 144',X'03020101ba08'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','1 1','149 149','89 149',X'03020104915b'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','1 1','174 174','114 174',X'03020106ea08'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','1 1','199 199','139 199',X'03020107dd08'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','204 204','144 204',X'0302010a5f07'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','207 208','146 208',X'0302020af10330'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','215 215','153 215',X'0302010b0607'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','217 217','154 217',X'0302010b0b07'); INSERT INTO sqlite_stat4 VALUES('NNN','NNN','2 1','219 219','155 219',X'0302010b0f07'); ANALYZE sqlite_master; Thanks, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up database open

Hello, Richard, thanks for quick reply. Unfortunately, no, there is no way. On our servers we have big number of entities that represent client data. Data for different clients can be read at any given point of time by clients and by a bunch of daemons responsible for different maintenance jobs.

Re: [sqlite] How to speed up database open

Hello, Simon. > > On 11 Dec 2014, at 11:51am, Paul wrote: > > > I understand, that having them is a must for a decent performance. > > In my specific case I have millions of individual database files. > > This is one, among other reasons that I can't keep t

[sqlite] How to speed up database open

the pragmas, initialization time will be delayed until the first query, so I guess there is nothing specific about these pragmas. Thanks, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] seeking advice

Hello, Rene > Hi there, > > I have to store and retrieve up to 2000 parameters. > The parameters can have real and integer values. > The max. change rate is 100ms and the max. duration is up to some hours. > > The simple solution would be to use plain binary files. It's fast but not > flexible.

Re: [sqlite] Search for text in all tables

uot; + tableName) columns = DB.execute("pragma table_info (" + tableName + ")") for colrow in columns: colName = colrow[1] colType = colrow[2] print ("colName = " + colName) DB.execute("UPDATE " + tableNa

Re: [sqlite] Search for text in all tables

As above I use pragma table_info tablename then you can loop through each row and check the type column to see if it is text Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic

Re: [sqlite] Search for text in all tables

column UPDATE tablename SET colname = REPLACE(colname, 'oldtext', 'newtext') The gotchas are how you choose to deal with strings stored in non text fields and how unique your terms are/what you do with substrings Paul www.sandersonforensics.com skype: r3scue193 twitter:

Re: [sqlite] appending the output of a query

Thanks Igor Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing

Re: [sqlite] appending the output of a query

ing of the recursive query is guranteed, i.e. the order follows the ID/parent relationship to the root b) a method of concatenating this in reverse order This is an academic exercise so a solution is not actually required but interesting non the less Paul www.sandersonforensics.com skype: r3scue1

Re: [sqlite] appending the output of a query

how I can get the sort applied before the Group_Concat is called - I think this might be what John is referring to. Thanks Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -F

[sqlite] appending the output of a query

I have a query that returns one column but a number of rows so for instance SELECT name from tab might return a b c d I would like to append these terms and get a single line/string a_b_c_d I want to just use a single SQL query to do this, is it possible? Paul

Re: [sqlite] Much unused space in sqlite3 database when using blobs

#x27;t draw any conclusions. I also assume that you started a new instance of sqlite between tests? Perhaps I could suggest that you do a similar test to mine just adding one (or a small handful) of blobs to the table and see if this sheds any light on what is going on. Paul www.sandersonfo

Re: [sqlite] Read overflow in CREATE VIRTUAL TABLE query (formerly Corrupting pointers to the lookaside smallacator)

Thank you very much, Dan! > On 11/27/2014 05:56 PM, Paul wrote: > > Currently we use various versions of SQLite: > > > > SQLite version 3.8.0.1 2013-08-29 17:35:01 > > SQLite version 3.8.2 2013-12-06 14:53:30 > > SQLite version 3.8.6 2014-08-15 11:46:33 > >

Re: [sqlite] Read overflow in CREATE VIRTUAL TABLE query (formerly Corrupting pointers to the lookaside smallacator)

analyzing core file it seems like very much an sqlite bug :/ Tell me if you need more info on this. Thanks. > On 11/27/2014 03:20 PM, Paul wrote: > > Here is how it looks with debug symbols are on: > > > > #0 0x28c4113e in memcpy () from /lib/libc.so.7 > > #1 0x08854c20

Re: [sqlite] Corrupting pointers to the lookaside smallacator

Here is how it looks with debug symbols are on: #0 0x28c4113e in memcpy () from /lib/libc.so.7 #1 0x08854c20 in sqlite3StrAccumAppend (p=0xfffe8548, z=0x2c3fffda "vtb_enyqkyxs USING vtable_module_343", N=41) at sqlite3.c:21563 #2 0x087edf30 in sqlite3VXPrintf (pAccum=0xfffe8548, bFlags=1, fmt

Re: [sqlite] Corrupting pointers to the lookaside smallacator

We observe very similar problem. #1 0x087ec9f7 in sqlite3VXPrintf () #2 0x087f816d in sqlite3MPrintf () #3 0x088781e5 in sqlite3VtabFinishParse () #4 0x0885190f in yy_reduce () #5 0x0884d4d8 in sqlite3Parser () #6 0x087fc0ce in sqlite3RunParser () #7 0x088aa396 in sqlite3Prepare () #8 0x087fae18

Re: [sqlite] insert or ignore with foreign keys

> > I guess the example below shows the intended behaviour for Sqlite? > > PRAGMA FOREIGN_KEYS=1; > CREATE TABLE t1 ( > id INTEGER PRIMARY KEY > ); > > CREATE TABLE t2( > id INTEGER PRIMARY KEY, > t1_id INT NOT NULL, > CONSTRAINT fk FOREIGN KEY(t1_id) REFERENCES t1(id) > ); > > INSERT INTO t1 V

Re: [sqlite] Column name as a variable

It would have just been a nice elegant solution to a minor problem. Lots of other ways of skinning this cat. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic

Re: [sqlite] Column name as a variable

x27;t want to code one for my users if it could be done with SQL. Cheers all. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite

Re: [sqlite] Column name as a variable

Thanks Simon I suspected as much - UNION is no good for me, it's easy enough to iterrate through in C. But not really what I was after. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-S

[sqlite] Column name as a variable

Is it possible to get a row count for each of the tables in a database using a SQL query. i.e. is there a way I could use each row in sqlite_master and use table_name to somehow do a select count(*) from sqlite.master.table_name Thanks Paul www.sandersonforensics.com skype: r3scue193 twitter

Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

Thanks for the link Bernard Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre

[sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, temp text UNIQUE NOT NULL); works OK CREATE TABLE IF NOT EXISTS test (id INT PRIMARY KEY AUTOINCREMENT, temp text UNIQUE NOT NULL); gives error AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY Paul

[sqlite] Recursive CTE on joined table

possible to use a recursive cte that refers to a cte? Thanks Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum

Re: [sqlite] cnt(x) what do the brackets signify

Thanks Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made

<    1   2   3   4   5   6   7   8   >