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. Paul

Re: [sqlite] sqlite journal file question

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 from

Re: [sqlite] binding multiple values in a query

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] 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] Huge WAL log

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-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 -Forensic

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

is 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 the f received the following segmentation

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. I'm the maintainer of SQLite3

[sqlite] using a hex integer as input to DateTime

, '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.php?195-SQLite-Forensic-Toolkit

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)1326 572786 http

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]. Just for the record, do you have

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

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/776648412c30dce206f1024ff849c2cb025bb006 signature.asc Description: This is a digitally signed message part

[sqlite] Bus error with Evolution 3.12.9 and SQLite 3.8.7.4

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_bug.cgi?id=742080 [2

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 even more input for this issue. I understand why there is implicit savepoint, when I remove row

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

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 ___ sqlite-users mailing list sqlite-users@sqlite.org http

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

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 would create a really large temp file instead of using memory. Which would still be sub-optimal

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 a

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

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

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] Feature Request - RowCount

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] '.timer on' in the shell tool

. 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 ___ sqlite-users mailing list sqlite

[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

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

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 de...@ukr.net 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 fair number of rows from a table due to the limit

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 does not allow us to remove fair number of rows from a table due

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 not run into the

Re: [sqlite] How to speed up database open

On Thu, Dec 11, 2014 at 10:58 AM, Paul de...@ukr.net 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_stat1. So consider compiling without

[sqlite] How to speed up database open

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] How to speed up database open

Hello, Simon. On 11 Dec 2014, at 11:51am, Paul de...@ukr.net 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 them open all the time. Just

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

Re: [sqlite] How to speed up database open

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

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 when data is being added or updated, since

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

On Thu, Dec 11, 2014 at 10:58 AM, Paul de...@ukr.net 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_stat1. So consider compiling without

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// In that case maybe keep the Stat1 tables and there is also

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. So

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: @sandersonforens Tel

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

table_info ( + tableName + )) for colrow in columns: colName = colrow[1] colType = colrow[2] print (colName = + colName) DB.execute(UPDATE + tableName + SET + colName + = REPLACE( + colName + , 'paul', 'sandy')) DB.commit() Paul www.sandersonforensics.com skype

[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] appending the output of a query

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 -Forensic Toolkit for SQLite http

Re: [sqlite] appending the output of a query

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: r3scue193 twitter

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

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.sandersonforensics.com

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,

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

. But 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 in sqlite3StrAccumAppend (p=0xfffe8548, z

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 SQLite version 3.8.7 2014-10-17 11:24:17 All

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

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 VALUES(2);

Re: [sqlite] Column name as a variable

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 http

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

[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] 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-SQLite

[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

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] Recursive CTE on joined table

this. Is it 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

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

I am looking at recursive cte and saw this example I have googled but can't see what cnt(x) actually signifies - cnt is not a function - could someone point me to a resource so I can understand this construct Thanks WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE

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

Re: [sqlite] Index without backing table

On Mon, 03 Nov 2014 11:50:17 +0200 Paul de...@ukr.net wrote: Would be nice to have ability to store both key and payload in the index. (Let's call it index-only table) This could be a feature that sets some limitations on a table, like being unable to have more than one index

Re: [sqlite] Index without backing table

Would be nice to have ability to store both key and payload in the index. (Let's call it index-only table) This could be a feature that sets some limitations on a table, like being unable to have more than one index or inefficient table scans, but it will also give some advantage in

Re: [sqlite] Index without backing table

On 3 Nov 2014, at 9:50am, Paul de...@ukr.net wrote: So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY as a replacement for ROWID and table itself is an index? It would appear that the answer is yes. I'm not going to go beyond the official documentation at http

Re: [sqlite] Index without backing table

3 November 2014, 13:48:30, by Clemens Ladisch clem...@ladisch.de: Paul wrote: Are additional indices, created for WITHOUT ROWID, potentially less efficient and more cumbersome? For tables with a rowid, the index stores the indexed columns and the rowid. For WITHOUT ROWID tables

Re: [sqlite] Index without backing table

3 November 2014, 13:56:36, by Richard Hipp d...@sqlite.org: On Mon, Nov 3, 2014 at 6:48 AM, Clemens Ladisch wrote: Paul wrote: Are additional indices, created for WITHOUT ROWID, potentially less efficient and more cumbersome? For tables with a rowid, the index stores

Re: [sqlite] Index without backing table

than one index or inefficient table scans, but it will also give some advantage in special cases like mine. - About your case, Edward. I believe you also need mapping where Key is Big (text) but Value is small (offset in a file?)? Hi Paul: Not sure

[sqlite] Index without backing table

Is there a way to have index without table? Is it possible to achieve this with virtual talbe tricks but without implementing B-tree myself? I need this both for space effciency and to minimize disk I/O (by avoiding table updates). ___ sqlite-users

Re: [sqlite] Index without backing table

31 October 2014, 14:19:56, by Richard Hipp d...@sqlite.org: I don't really understand what you are asking, but I suspect that http://www.sqlite.org/withoutrowid.html is probably the answer you are looking for. I have just realized, how stupid my question is. Actually I don't even have to

Re: [sqlite] [regression] SQLite 3.8.7 causes Evolution to crash

Dear Richard, Am Mittwoch, den 22.10.2014, 21:53 -0400 schrieb Richard Hipp: On Wed, Oct 22, 2014 at 5:14 PM, Paul Menzel wrote: after the upgrade of libsqlite3 from 3.8.6 to 3.8.7 Evolution crashes with a segmentation fault. pool[6371]: segfault at 0 ip (null) sp a67d26ec

Re: [sqlite] Full outer joins

Really!! I can accept that it would not be needed as often as other joins but I can imagine that anyone who wrtes software that populates databases and who subsequently changes their software or needs to benchmark against some other datasource would find this useful. Cheers Paul On 22 October

[sqlite] what is wrong with this query

I have two versions of the same table with minor differences. I open one database (test1) and attach the second (test2) SELECT test2.table1.* FROM test2.table1 LEFT JOIN table1 table11 ON test2.table1.id = table11.id Gives an error condition near *: syntax error. provide an alias for the

[sqlite] [regression] SQLite 3.8.7 causes Evolution to crash

open a bug report in the SQLite bug tracker and help analyzing the bug. Please find the backtrace pasted at the end of the message. Thanks, Paul [1] https://bugzilla.gnome.org/show_bug.cgi?id=738965 [2] https://mail.gnome.org/archives/evolution-list/2014-October/msg00126.html #0 0x

[sqlite] [regression] SQLite 3.8.7 causes Evolution to crash

open a bug report in the SQLite bug tracker and help analyzing the bug. Please find the backtrace pasted at the end of the message. Thanks, Paul [1] https://bugzilla.gnome.org/show_bug.cgi?id=738965 [2] https://mail.gnome.org/archives/evolution-list/2014-October/msg00126.html #0 0x

[sqlite] Full outer joins

I have two tables from two versions of the same database each table has an integer id primary key I want to find the rows that dont appear in both tables. So I think I need a full outer join which I understand is not supported by SQLite I have seen this which gives an alternative, but it seems

[sqlite] line break, or similar, in query results

have to be a newline - just soemthng to make it more readable) * I know texas isn't a city :) 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

Re: [sqlite] line break, or similar, in query results

Thanks Clemens 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

Re: [sqlite] following a trail of references

Thanks both - modified the code for my example but it failed :( I'll keep at it and see if I can get my head around it. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit

[sqlite] decoding a bitmask

I have a table with an integer value which is a bitmask. one or more of the bits can be set and each bit has a corresponding meaning. so using the windows file attribute as an example we have 0c01 readonly 0x02 hidden 0x04 system 0x10 directory 0x20 archive none, any or all could be set I'd

Re: [sqlite] decoding a bitmask

, but fails (a blank string is returned) when multiple bits are set. Any ideas why? Thanks Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit http://sandersonforensics.com

Re: [sqlite] decoding a bitmask

Thanks all - agree rookie mistake with xor, had this been a C++ exercise I would have have been OK - SQL seems to make my mind go blank... Thanks for the case explanation Mark - v helpful. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http

[sqlite] following a trail of references

I think this might be beyond the ability of SQL - but there are cleverer people on here than me, so I might be wrong :) Lets say we have a table Create table (id integer primary key, previousid integer, location text) previousid contains a pointer to ID or 0 for no previous id, so for instance

[sqlite] Best page size and cache size in high memory environment

the page size only for the temp database? pragma temp.page_size=65536 ? Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 2 joins on one table

Lovely - thanks On 30 September 2014 00:14, David Empson demp...@emptech.co.nz wrote: On 30/09/2014, at 12:04 pm, Paul Sanderson sandersonforens...@gmail.com wrote: I two tables of the form create table1 (person1 text, person2 text) create table2 (person text, picture blob

[sqlite] 2 joins on one table

I two tables of the form create table1 (person1 text, person2 text) create table2 (person text, picture blob) Is it possible to create a join so I can get a resultant dataset of the form person1, person1picture, person2, person2picture Thanks ___

Re: [sqlite] Atomic database structure initialization

There is also a PRAGMA user_version (see http://www.sqlite.org/pragma.html#pragma_schema_version) which will let you store a number in the database header so you can keep track of what version of the user schema you have implemented in the database. Initially, when the database is

Re: [sqlite] Atomic database structure initialization

Paul wrote: pragma user_version; returns a single row with a single value which is the version, and the command, pragma user_version=n; lets you change it to n. Perhaps you can use this as a flag to tell yourself that you are working with an uninitialized database (value

Re: [sqlite] Atomic database structure initialization

Paul wrote: pragma user_version; returns a single row with a single value which is the version, and the command, pragma user_version=n; lets you change it to n. Perhaps you can use this as a flag to tell yourself that you are working with an uninitialized database (value

Re: [sqlite] Atomic database structure initialization

Paul wrote: I can check whether user_version matches magic number without transaction. No. Executing PRAGMA user_version will start an automatic transaction if you didn't already start an explicit one. Only when user_version does not match magic number I start transaction

Re: [sqlite] Atomic database structure initialization

On 22 Sep 2014, at 1:13pm, Paul de...@ukr.net wrote: The only thing I am worried about is whether pragma user_version=n; respects transactions and will be rolled back automatically in case if something happens between that statement and COMMIT. SQLite version 3.8.5 2014

Re: [sqlite] Atomic database structure initialization

Paul wrote: I can check whether user_version matches magic number without transaction. No. Executing PRAGMA user_version will start an automatic transaction if you didn't already start an explicit one. Only when user_version does not match magic number I start transaction

Re: [sqlite] Atomic database structure initialization

Thank you for help, guys! I knew that sqlite is a great piece of software, now I have even more proofs :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Atomic database structure initialization

if this, I think, common problem has standard ways of solving. Regards, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

Paul wrote: My goal is to make structure initialization of an *abstract* database atomic. [...] if database file is missing it must be created and initialized. http://www.sqlite.org/transactional.html Just do the check for the database structure and the initialization inside

Re: [sqlite] Atomic database structure initialization

Paul wrote: Paul wrote: My goal is to make structure initialization of an *abstract* database atomic. [...] if database file is missing it must be created and initialized. http://www.sqlite.org/transactional.html Just do the check for the database structure

Re: [sqlite] Atomic database structure initialization

On 19 Sep 2014, at 8:34am, Paul de...@ukr.net wrote: if database file is missing it must be created and initialized. For that purpose I need to provide a guarantee that *on_create* callback will be called strictly once. Can you check to see whether the database file exists using

[sqlite] Create join and add unique column

I want to create a join on two tables and add a unique number to each returned row. Can this be done with a SQL query? Thanks -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786 http

Re: [sqlite] Create join and add unique column

Thanks - I like the temporary table idea, but now sure how it would work. say for instance I have two tables create tab1 (id int, name text) 1, 'paul' 2, 'helen' 3, 'melanie' create tab2 (id int, country text) 1, 'uk' 2, 'scotland' I can create a temporary table create table tab3 as select

Re: [sqlite] Create join and add unique column

_rowid_ is probably the answer with a temporary table On 16 September 2014 13:00, Paul Sanderson sandersonforens...@gmail.com wrote: Thanks - I like the temporary table idea, but now sure how it would work. say for instance I have two tables create tab1 (id int, name text) 1, 'paul' 2

Re: [sqlite] Create join and add unique column

select _rowid_, * from tab3 does the trick - thanks all On 16 September 2014 13:13, Paul Sanderson sandersonforens...@gmail.com wrote: _rowid_ is probably the answer with a temporary table On 16 September 2014 13:00, Paul Sanderson sandersonforens...@gmail.com wrote: Thanks - I like

Re: [sqlite] Create join and add unique column

Thanks Ryan. That doesn't work for me though as I am looking for a generic solution that will work on multiple tables - so no hard coding of column definitions :( I think I am getting there On 16 September 2014 15:38, RSmith rsm...@rsweb.co.za wrote: On 2014/09/16 15:32, Paul Sanderson wrote

Re: [sqlite] Create join and add unique column

definition :( On 16 September 2014 18:18, Paul Sanderson sandersonforens...@gmail.com wrote: Thanks Ryan. That doesn't work for me though as I am looking for a generic solution that will work on multiple tables - so no hard coding of column definitions :( I think I am getting there On 16 September

  1   2   3   4   5   >