Re: [sqlite] WAL: no schema after close

As above the WAL wont check point unless 1. You tell it too 2. You commit a transaction which takes the wal above (default) 1000 pages 3. you exit the applictaion gracefully The WAL algorithm is doing exactly what it should do and is taking (or rather keeping) the DB in it's last know good

[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

Re: [sqlite] following a trail of references

<http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery> -SQLite Forensic Toolkit http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 12 October 2014 21:18, Igor Tandetnik <i...@tandetnik.org> wrote: > On 10/12/2014 4:03 PM, Paul

[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

nt.php?195-SQLite-Forensic-Toolkit <http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery> -SQLite Forensic Toolkit http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 13 October 2014 13:17, Clemens Ladisch <clem...@ladisch.de> wrote: > Paul Sa

Re: [sqlite] decoding a bitmask

wrence <no...@null.net> wrote: > My apologies for the previous completely wrong mesage. I got mixed up > with operator meaning & precedence... > > On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote: > > > > My actual code is as folows > > &g

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

Another morning another question I have a query that performs a recursive query and outputs a varying number of rows using group by sqlite> with recursive path as (select id, previd, location from cities union all select cities.id, cities.previd, cities.location from path join cities on

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

processing made easy On 14 October 2014 12:09, Clemens Ladisch <clem...@ladisch.de> wrote: > Paul Sanderson wrote: >> 1|0|texas >> 2|1|new york >> 3|2|washington >> 4|0|tampa >> 5|0|atlanta >> 6|5|charleston >> >> I'd like to add a break between gro

[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

Re: [sqlite] Full outer joins

2014 00:58, Richard Hipp <d...@sqlite.org> wrote: > On Tue, Oct 21, 2014 at 7:12 PM, Paul Sanderson < > sandersonforens...@gmail.com> wrote: > >> out of interest why are >> full out joins not supported? >> > > In 14 years, you are the first person to

[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

[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

easy On 12 November 2014 18:00, Igor Tandetnik <i...@tandetnik.org> wrote: > On 11/12/2014 12:52 PM, Paul Sanderson wrote: >> >> I have googled but can't see what cnt(x) actually signifies - cnt is >> not a function > > > The same thing it signifie

[sqlite] Recursive CTE on joined table

I have a recursive cte on a joined table and altough it works fine the SQL is a little complex and teh join makes it a little difficult to follow. I can simplify the SQL by creating a view andthen use the ciew in my recursive cte, but for various reasons I would rather not do this. Is it

[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

key-and-integer-primary-key-sqlite > see this link for more on the subject. > > 2014-11-16 13:56 GMT-02:00 Igor Tandetnik <i...@tandetnik.org>: > >> On 11/16/2014 10:51 AM, Paul Sanderson wrote: >> >>> AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY >

[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

-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 17 November 2014 11:38, Simon Slavin <slav...@bigfraud.org> wrote: > > On 17 Nov 2014, at 10:55am, Paul Sanderson <sandersonforens...@gmai

Re: [sqlite] Column name as a variable

d40591f963be2f868 > > > --- > Theory is when you know everything but nothing works. Practice is when > everything works but no one knows why. Sometimes theory and practice are > combined: nothing works and no one knows why. > >>-Original Message- >>Fr

Re: [sqlite] Column name as a variable

Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 18 November 2014 12:06, Simon Slavin <slav...@bigfraud.org> wrote: > > On 18 Nov 2014, at 8:46am, Paul Sanderson <sandersonforens...@gmail.com> > wrote: >

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

Further to Richards comment the formula to determine when a record overflows into a page file is given in section 1.5 of the file format doc http://www.sqlite.org/fileformat.html The relevant section states: The amount of payload that spills onto overflow pages also depends on the page type.

[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

e http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 2 December 2014 at 18:27, John McKown <john.archie.mck...@gmail.com> wrote: > On Tue, Dec 2, 2014 at 11:51 AM, Richard Hipp <d...@sqlite.org> wrote: > >> On Tue, Dec 2, 20

Re: [sqlite] appending the output of a query

org> wrote: > On 12/2/2014 1:47 PM, Paul Sanderson wrote: >> >> WITH RECURSIVE rcte AS (SELECT rtable.ID, >>rtable.parent, >>rtable.FileName >> FROM rtable >> WHERE rtable.ID = 510 >> UNION ALL >> SELECT rtab

Re: [sqlite] Search for text in all tables

Hi Baruch I have a commercial tool that can do the search all tables bit but not the replacing :) (not usually required by my clients - although I may add it) If you are a coder then it is a relatively straight forward process along the lines of Loop through each table Loop through each

Re: [sqlite] Search for text in all tables

wrote: > >> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson < >> > If you are a coder then it is a relatively straight forward process >> > Loop through each table >> >Loop through each column >> >> This is the part I am having trouble with.

Re: [sqlite] Search for text in all tables

ics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 4 December 2014 at 12:31, Paul Sanderson <sandersonforens...@gmail.com> wrote: > As above I use > > pragma table_info tablename > > then you can loop through each row and check the type column to see if &g

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

[sqlite] using a hex integer as input to DateTime

Evening all I dont think this can be done, but would love to be corrected. I have a column with integer dates stored in hex format so 1234567890 is stored as 49962d2 Obviously DateTime(1234567890, 'unixepoch') work OK, but what I would like to do is something like DateTime(0x49962d2,

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

://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 7 January 2015 at 22:27, Petite Abeille <petite.abei...@gmail.com> wrote: > >> On Jan 7, 2015, at

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

2015 at 00:33, Richard Hipp <d...@sqlite.org> wrote: > On 1/7/15, Paul Sanderson <sandersonforens...@gmail.com> wrote: >> Evening all >> >> I dont think this can be done, but would love to be corrected. I have >> a column with integer dates stored in hex forma

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

d, if in your own program, you'd just execute > > sqlite3_create_function (hdbc, >"hextoint", >-1, >SQLITE_ANY, > globalfunc_info, >S_hext

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

om: > > http://www.monkeybreadsoftware.de/SQLiteExtension/index.shtml > > I guess they would be useful (as generic SQLite load extension) in Your > Toolkit. > You may ask Christian to support a particular need. > > Carlos. > > On 1/15/2015 5:30 AM, Paul Sanderson wrote: >> >&g

Re: [sqlite] Huge WAL log

> I understand that the WAL log must take a lot of space. What I don't > understand is that it was 7x larger than the resulting DB size. (Actual > quotient is even larger because I compared to the DB size that contained > also other tables.) Unlike a rollback journal a WAL file can have multiple

Re: [sqlite] sqlite journal file question

I would say no. The journal file stores pages referenced by page no and when replayed will write those pages back to the main DB at the appropriate physical offset. Although the content of your DB's at a logical level may be the same, it is unlikely that they will be exact copies at a binary level

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] Use journal after program crashed

Cecil, Richard If the WAL file contains multiple commits then it is possible (with appropriate tools) to use any of commits and effectively roll back to a chosen previous version. How much of this data you can get at depends upon the size that the WAL file will auto checkpoint and also the size

Re: [sqlite] Messages posted on Nabble not getting to list

I'm a developer and I much prefer the forum approach. I don't get to read all of the threads on here because I do have a specific interest in a subset of the SQLite subject. I like the ability to subscribe to a thread and get updates when someone replies I like to be able to embed graphics (I

Re: [sqlite] Messages posted on Nabble not getting to list

Theres another reason - consistency. Just look back thorugh this thread and see how things are quoted, often differently depending on the mail client used - much easier to read when a quote is nicely formatted in a highlighted box. Then (just from this thread) there is formatting of dates - all

[sqlite] Destroy all evidence of a database

You haven't said what operating system you are using but I strongly suspect that there will be plenty of pages from your database thrown around by the OS itself in various caches/pagefiles etc. all of course outside of the ability of SQLite to prevent. Getting at these cached pages is not

[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 file). Paul

[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 from a work

[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] implicit vs explicit joins

I have just been asked whether implicit and explicit join are the same and was given an example There queries below produce the same results but explain indicates that they are different. My question though is in what instances are the actual results of such a query likely to be different and is

[sqlite] implicit vs explicit joins

:01, Clemens Ladisch wrote: > Paul Sanderson wrote: >> I have just been asked whether implicit and explicit join are the same >> >> select text, handle.id from message,handle where handle_id = handle.ROWID; > > This is an implicit *inner* join. > >> SELECT mes

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

[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

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

-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 7 March 2015 at 17:56, Simon Slavin wrote: > > On 7 Mar 2015, at 4:42pm, Paul Sanderson > wrote: > >> I have a database I have created and populated with vario

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

Dave I'm not sure exactly what you are trying to do from your description - the schema of the tables you have and those that you want may help. But as a general idea you might be able to use something along the lines of create table newtable as select x, y, z from oldtable More info here:

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

functional demo licence On 7 March 2015 at 19:44, Simon Slavin wrote: > > On 7 Mar 2015, at 7:31pm, Paul Sanderson > wrote: > >> 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. > > A mistake we'

[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] saving :memory:database to disk

s this for more of a dynamic database whereby the users can add/remove > tables and indexes? > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Paul > Sanderson >

[sqlite] Query help

I suspect this is easy i have three tables create table t1 (recno int, a int, b int) create table t2 (a int, name text) create table t3 (b int, name text) I want to create a query that lists all rows in t1 but rather than the integers a and b I want to display the associated names from t2 and

[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

Re: [sqlite] Create join and add unique column

tember 2014 12:02 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Create join and add unique column > > could create a temporary table with a incrementing key and 'insert into > temp_table select join ...' something like that? > maybe use the existing key

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,

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

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, Pa

Re: [sqlite] Create join and add unique column

ginal type 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 thi

Re: [sqlite] Create join and add unique column

It all helped me thanks - just not in solving this particular problem (which I have now manage to do) Thanks all. On 16 September 2014 19:33, jose isaias cabrera <jic...@cinops.xerox.com> wrote: > > "RSmith" wrote... > > >> On 2014/09/16 15:32, Paul Sand

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

[sqlite] Testing for a null string

I am sure tihs is basic but. I have a database with a text column and i want to return all rows where the column has no value I have tried select * from db where f = NULL select * from db where f = "" select * from db where f = '' all return 0 records when I knopw that most fields are empty

[sqlite] SQL query help

Hi all I am trying to create a query that works to craete a subset of a table based on duplicate items Examples work best so consider the contrived table with the following rows 10 socata 7 socata 13 cessna 2 piper 7 piper 55 piper 1 diamond I want to see the subset that is 10 socata 7 socata 2

Re: [sqlite] SQL query help

Hmm thanks Roger Table could have a few million rows, i'll have a play and see what the run time is. The relevant column is indexed On 20 August 2011 17:14, Roger Andersson <r...@telia.com> wrote: >  On 08/20/11 05:42 PM, Paul Sanderson wrote: >> Hi all >> >> I

[sqlite] Could someone explain why this query is so slow

The query below takes about 10 mins to run, any idea why this would be? select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 ORDER BY afo The same query without the ORDER BY takes a few seconds. select ID FROM rtable WHERE search > 0 and MD5 is NULL and isf = 0 There are

Re: [sqlite] Could someone explain why this query is so slow

MP B-TREE FOR ORDER BY Which seems to indicate that the b-tree is still being created (I'll test shortly, but running another long test at the moment) On 21 September 2011 14:33, Igor Tandetnik <itandet...@mvps.org> wrote: > Paul Sanderson <sandersonforens...@gmail.com> wrote: >&g

Re: [sqlite] Could someone explain why this query is so slow

Ahh I was sure that this was being created :( ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL help

Hi I have a large table with some duplicate rows that I want to delete. Essentially I have two columns, one containing a date and one containing a number. The number column can contain duplicates. For any row containing duplicate values I want to remove all rows bar the oldest. i.e. if the data

Re: [sqlite] SQL help

Thanks all, dates are stored internally as integers -- Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding WALs

I am trying to get my head around the operation of WAL files and an oddity that I can't explain. As I understand it when a transaction is committed when a DB is in WAL mode each changed page is appended to the WAL. Multiple transactions work in the same way until a checkpoint occurs. The WAL has

[sqlite] Understanding WALs

functional demo licence On 11 November 2015 at 00:24, Richard Hipp wrote: > On 11/10/15, Paul Sanderson wrote: >> >> As the last thing I did was a wal-checkpoint then according to the >> documentation the page should have been written to the DB (it was see >> pic

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

Counting all rows vs counting distinct rows is a very different task. In simple terms For all rows the process is read every leaf page in the index query the cell count field (number of records) add cell count to the to the total count repeat for the remaining leaf pages For distinct records

[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

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

Gunnar If the data is very important then don't do anything that will change the computer. If you have sometools that can take an image copy of the disk (DD to another drive) or maybe the free accessdata imaging software then I would recommend doing this first - it will leave all options open

[sqlite] How would sqlite read this from disk?

It reads a complete page at a time so there is no seeking other than to the start of each row - in the sense of a disk seek. Note that there may be multiple required rows on the same page if the row length is much less than the page length, or if rows are longer than a size determined by some

[sqlite] Get fields type

you could use SQL i.e. select typeof(col1) from table1 would return text, integer etc. for each row in the table for the specified column. problems could arise if rows have NULL for given column or SQLite slack of strict column affinity mena sthat someone has dropped a string into an integer

[sqlite] Get fields type

functional demo licence On 13 April 2016 at 18:08, Paul Sanderson wrote: > you could use SQL i.e. > select typeof(col1) from table1 > > would return text, integer etc. for each row in the table for the > specified column. > > problems could arise if rows have NULL for given col

[sqlite] obtaining bracketing rows where rows are missing

I confused myself with that title. I have a DB with 300 odd rows with an integer primary key, there are a few breaks in the key numbering such that I have something like 1 2 3 5 8 9 10 11 14 I can generate a list of misisng rows easily enough , eg 4 6 7 12 13 but I would like a table of the

[sqlite] obtaining bracketing rows where rows are missing

C "HAVING x = min(x)" is not portable SQL but it seems to work in sqlite. > > -Rowan > > > > On 15 April 2016 at 18:04, Paul Sanderson > wrote: > >> I confused myself with that title. >> >> I have a DB with 300 odd rows with an integer primary key,

[sqlite] Pascal (almost) style BLOBs

You could use a binary PList or some other form of structured data - the BPList blob could then contain, ints, floats, strings byte arrays or even embedded binary plists. As it is a structured format then to an extent it is self documenting (in terms of the stored data types) Paul

[sqlite] Controlling of check-pointing of WAL Dbs

Can you kill the process rather than shut down your application? If the last connection doesn't get shut then the WAL will remain and will be in opened (but not played) when the DB is re-opened. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

[sqlite] Storing Images in Blobs - Best Practices

How do you know that it is sqlite that is corrupting the images, could your conversion to nsdata (or elsewhere prior to writing to teh DB) be the culprit? Given your schema if a record has become corrupt then the following two columns (the data for which which would be stored contiguously after

[sqlite] Why is a separate journal file needed ?

You would need some sort of allocation table for the journal (wal or rollback) and on a rollback commit or a wal checkpoint the allocation would need to be cleared (or maybe some "in use" bit cleared) to show that the pages were now free to be re-used. The additional data stored with rollback

[sqlite] Why is a separate journal file needed ?

WAL files can be many times bigger than a database - the default WAL checkpoint size is when the WAL grows to > 1000 pages. You can get a DB (for example) with 100 pages and a WAL of 1000 (with multiple different copies of the same page). Paul www.sandersonforensics.com skype: r3scue193 twitter:

[sqlite] Are BLOB stored inline with the rest of the record fields?

What is the likely max length of a row in your table? if your row length is less than page length-35 then it will fit in to one page. The amount of wasted page depends on what fraction of a page size your typical record is. If you have a record that is 600 bytes long on a DB with 1024 byte page

[sqlite] obtainingthe number of rows returned by a query (with a limit)

I am trying to determine before a query is executed how many rows will be returned. the following query works as expected select count(*) from table but select count(*) from table limit 100 still returns the number of rows in the table not the number of rows that would be returned by the

[sqlite] obtainingthe number of rows returned by a query (with a limit)

wrote: > Paul Sanderson wrote: >> I am trying to determine before a query is executed how many rows will >> be returned. the following query works as expected >> >> select count(*) from table >> >> but >> >> select count(*) from table limit 100

[sqlite] Fastest way to find whether at least one row has a certain column value

Clemens, Tim has the same issue as me, while SELECT EXISTS (select status from mytable where status=1); works the portion in brackets is still executed in full and this is what we are trying to avoid. I am not working directly with the sqlite library but rather though a DAC so I am also

[sqlite] Fastest way to find whether at least one row has a certain column value

ress for a fully functional demo licence On 5 March 2016 at 16:12, Richard Hipp wrote: > On 3/5/16, Paul Sanderson wrote: >> Clemens, >> >> Tim has the same issue as me, while >> >> SELECT EXISTS (select status from mytable where status=1); >> &g

[sqlite] obtainingthe number of rows returned by a query (with a limit)

> Just allow them to cancel the query; use sqlite3_progress_handler(). I am using a third party data access component so this is out of my control. If it computes many things and doesn't return many rows then I don't really care. I only want to know how many rows a query will return before I

[sqlite] Changing the default page_size in 3.12.0

Records can span multiple pages and the record itself is unrelated to the block size. For smaller tables indexes an increase in page size can increase the DB size - consider a DB with 1024 byte pages and one table that occupies 6 pages - i.e. 6K. in a new 4096 page size DB it would take up two

[sqlite] obtainingthe number of rows returned by a query (with a limit)

So select count (*) from ... Doesn't require a crystal ball and returns the number of rows but Select count(*) from ... Limit x Dies need one, interesting On Saturday, 5 March 2016, Igor Tandetnik wrote: > On 3/5/2016 12:20 PM, Paul Sanderson wrote: > >> If it computes

[sqlite] Fastest way to find whether at least one row has a certain column value

for a fully functional demo licence On 6 March 2016 at 08:09, R Smith wrote: > > > On 2016/03/05 6:21 PM, Paul Sanderson wrote: >> >> Thanks Richard >> >> so suppose I have two tables table1 and table2 each with 1000 rows and >> say 100 columns some conta

[sqlite] How to read data from WAL?

I have done some manual WAL decoding for my forensic software that can identifiy a previous DB state - its fun :)... (From memory) To determine which pages belong to the last transaction, you need to : Read the WAL header to obtain the current salt, then read each wal frame to determine which

[sqlite] error messages from SQLite

When executing a sql query I often find I have made a typo or been a bit dull and I'll get an error message back along the lines of Error: near "text": syntax error Is there a way of expanding on this, for instance adding more of the following text or a character offset. The last time this

[sqlite] Reserved column names

Is there a list of reserved column names I have seen the list or keywords at the link below http://www.sqlite.org/lang_keywords.html but for instance create table (abort int) will work fine. I know that keywords can be quoted but I am interested in just those that can be used unquoted (even if

[sqlite] error messages from SQLite

-Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 19 March 2016 at 11:20, Tim Streater wrote: > On 19 Mar 2016 at 10:26, Paul Sanderson > wrote: > >> When executing a sql query I often find I have made a typo or been a >> bit dull

[sqlite] indexing speed

I have a table with just a few columns, one is a SHA1 hash and the second an MD5, there are about 17 Million rows in the table if I create an index on the SHA1 column using "create index if not exists sha1index on hashtable(sha1)" the process takes about 3 minutes, if I follow this immediately by

  1   2   3   >