[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

[sqlite] Inserting a large amount of data into a large indexed table

Hi I have a table that contains 6 columns of integers, 2 of these columns are indexed. There are about 10 Million rows of data in the table. every now and again I need to add more rows, between about a thousand and a million at a time. I want the process to be as quiick as possible (although I kno

Re: [sqlite] Inserting a large amount of data into a large indexed table

Thanks Oliver that is what I am doing at the moment. I was wondering whether there might be a quicker way maybe using temporary tables for similar - just exploring ideas On Tuesday, August 3, 2010, Oliver Peters wrote: > Am Dienstag, den 03.08.2010, 23:24 +0100 schrieb Paul Sander

[sqlite] copying only new rows from one table to another

Newbie question I have two tables and I want to copy (occasionally) a few thousand columns that have been added to table1 into table2, both tables have the same unique key but otherwise the columns are different. table2 will be initially populated from table1 using INSERT into table table2 SELEC

Re: [sqlite] copying only new rows from one table to another

Thanks igor - ill give that a go. On 6 August 2010 16:36, Igor Tandetnik wrote: > Paul Sanderson wrote: >> I have two tables and I want to copy (occasionally) a few thousand >> columns that have been added to table1 into table2, both tables have >> the same unique key but

[sqlite] Database is locked

I expect that this has been asked before but I cant find anything useful via google. I am updating about 20 rows in a table and setting a particular value to a particular value. The first time I do this all is OK, if I try again a few seconds later I get a database is locked error. The code is (u

[sqlite] implementing a row cache

I need very fast access to various subsets of data from a table so I am trying to write a cache for a subset of the rows. The cache must be “sort” aware. The table is read only do I dfont need to worry about keeping the cache upto date. The table itself has about 30 columns and I need to cache al

Re: [sqlite] implementing a row cache

Thanks for the input SELECT * FROM table WHERE id BETWEEN (currentid-100) AND (currentid+100) is rather obvious and I should have thought of it - an SQL newbie working late at night and then getting up early with the problem running around my head Howver although that works for the primary co

Re: [sqlite] implementing a row cache

Not a huge issue - if the record I want is not in the cache then I can go and get it. for the record I have checked my code just doing the sql queries and it is too slow - hence the additional work here. Initial implementatio is faster and looks workable (although it does introduce additional prob

[sqlite] query help

I have two tables, table b is a subset of table a. both tables have the same primary key I want to update the rows from table a with a single column from table b, what sql command would be most efficient for this? Thanks ___ sqlite-users mailing list sq

Re: [sqlite] query help

Thank You. On 26 October 2010 13:14, Igor Tandetnik wrote: > Paul Sanderson wrote: >> I have two tables, table b is a subset of table a. both tables have >> the same primary key >> >> I want to update the rows from table a with a single column from table >>

[sqlite] Reducing time to create indexes

I have a table with over 1 million rows and 20+ columns all of which are indexed, I reasonably regularly recreate the table with new data and find that the indexing process takes about 30 minutes. Are there any settings/tweaks that I can use to reduce the time required to create the index? The col

Re: [sqlite] Reducing time to create indexes

Unfortunately all of the columns are represented in a grid that users can choose to sort on any column, although they are less likely to sort on certain columns, if they do chose to do so then an excessive delay is not really acceptable. Currently I create ascending and descending indexes for each

Re: [sqlite] Reducing time to create indexes

Thanks All Simon, I'll try that and see what difference itmakes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing time to create indexes

Stuck with a 32 bit Windows set up as the lowest common so in memory is not feasible - I have seen some of my DB's in excess of 2GB - users might object even if I could nick that much memory ___ sqlite-users mailing list sqlite-users@sqlite.org http://sql

Re: [sqlite] Reducing time to create indexes

le to split the table into two with the main fields in one and all of the remainder (including the text fields) in the other. I'll have a play and see what I come up with On 25 November 2010 10:20, Max Vlasov wrote: > On Wed, Nov 24, 2010 at 1:13 AM, Paul Sanderson < > sanderson

Re: [sqlite] Question

As said off topic - but I have never heard of a cell ID being recorded with an SMS message. You can find more info on the SMS message format by search for "sms pdu" (protocol description unit) you can see from this somewhat complex structure that the cell ID is not part of the transmitted message f

[sqlite] forcing flush of journal

I have an sqlite database and an assocaiated .journal file that I need tolook at. I have estalished that there is data in the journal that seems relevant and want flush the journal (if that is the correct term) and to then view the tables with the updated data. Is there a way to do this? Thanks __

Re: [sqlite] forcing flush of journal

Thats what I thought, thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Return a column's contents to the free list

On 9 June 2018 at 16:17, Jay Kreibich wrote: > > Without digging through some detailed docs, I’m pretty sure empty string > and NULL require the same amount of storage space. If not, the difference > is maybe one byte. > > You are correct Jay The serial types NULL, 0 and 1 each have a serial t

[sqlite] unique values from a subset of data based on two fields

I have a table Create table names (id int, status int, name text) 1, 1, 'paul' 2, 1,'helen' 3, 0, 'steve' 4, 0, 'steve' 5, 0, 'pete' 6, 0, 'paul' I want a query that returns all of the records with status = 1 and unique records, based on name, where the status =0 and the name is not in

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

-Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Paul Sanderson > Sent: Friday, June 29, 2018 11:50 AM > To: General Discussion of SQLite Database > Subject: [sqlite] unique values from a subset of data based on two fields &g

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

0 >>and name not in one_names >> group by status, name >> >> union all >> >> select * from status_one; >> >> >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf

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

to achieve performant results. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists

Re: [sqlite] Minimum Delta Time

How about just using a trigger to check if endtime is < starttime+10 and updating if it fires Paul www.sandersonforensics.com SQLite Forensics Book On 11 July 2018 at 17:09, David Raymond wrote: > For a minimum of 10 minutes it'd be something like >

Re: [sqlite] Reducing index size

If I understand correctly then changing from a base64 index to a blob containing the raw bytes would save 25% Paul www.sandersonforensics.com SQLite Forensics Book On 30 July 2018 at 09:32, Eric Grange wrote: > Hi, > > Is there a way to reduce the s

Re: [sqlite] Common index for multiple databases

On 2 August 2018 at 20:08, Keith Medcalf wrote: Further to this, you can "emulate" the current structure by creating multiple databases each containing only the tables needed for that "bit" of your application. For example, you can create a customers.db containing the customers table and all the

Re: [sqlite] Packing integer primary key with field bits

Space savings will depend very much on what other data is in the table. If you have a 4096 byte page size and with an average record size of 1000 bytes then saving 7 bytes for each of the 4 records wont free up enough space to fit a new record into that page. So savings in this scenario will effec

Re: [sqlite] Data types for date and time functions

Internally SQLite stores and process numbers as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. I have not examined the code in any depth but would assume that if you store the data in the same format it would save on any processing overhead for calculation

Re: [sqlite] Grouping and grabbing one item

How about select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1), max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt > 0; 2017-09-27|2017-10-01|es-ES|170.0 2017-09-27|2017-10-01|fr-FR|185.0 2017-09-27|2017-10-01|it-IT|200.0 Paul www.sandersonforensics.com skype:

Re: [sqlite] Grouping and grabbing one item

ahh bugger - google didn't show the new answers had popped up. Pleased I came up with a working solution though :) Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensi

Re: [sqlite] Many ML emails going to GMail's SPAM

Coincidence! I have just been in my gmail folder marking a load of SQLite email as 'not spam' 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] Energy consumption of SQLite queries

A pretty much impossible task I would think. The power usage of SQLite compared to the power usage of different hardware components would be miniscule. But, there are so many other tasks running on a system, many in the background, that isolating SQLite from the rest would be next to impossible. J

Re: [sqlite] Many ML emails going to GMail's SPAM

What about some sort of poll. Mail lists might work but the additonal functionality offered by a forum (I am a member of many) makes them my choice. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?19

Re: [sqlite] DateTime to bigint

Hi Tibor Your date format is windows ticks, i.e. 100 nano seconds intervals since 01/01/0001 You can convert it as follows SELECT (StrfTime('%s', '2004-08-05') + 62135596800) * 1000 AS Ticks where StrfTime('%s', '2004-08-05') is the number of seconds between the provided date and 1/1/1970 6

Re: [sqlite] What happens if an in memory database runs out of memory

Try it create a table and use the zeroblob(n) function to insert lots of blobs of size n ie create table blobs (b blob); insert into blobs values(zeroblob(1)); insert into blobs values(zeroblob(1)); etc. interestingly the max blob size is specified as 2147483647 but on my curre

Re: [sqlite] Atomic DELETE index optimisation?

Not sure how relevant it might be, but what page size is the DB set to and what is the average size of a record? 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] same code produces a different database file on different computers

SQLite stores the verison number of the librrary in the database header. Different SQlite libraries on different computers would cause this error. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-S

Re: [sqlite] Atomic DELETE index optimisation?

Dinu Option 2, dropping and recreating the index with the transaction, seems to be the way forward - I would suggest that if the author of SQlite (Dr Hipp) has put this forward as a solution, as he did earlier in this thread, then it is probably a safe option and will not lead to an implosion of

Re: [sqlite] Can an SQL script be built from within sqlite?

That terminal app is still sandboxed. AFAIAA you essentially get access to the applictaios data folder and you can add, create, delete, etc files within it. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

Autoincrement can ONLY be used with an integer primary key https://sqlite.org/autoinc.html On Tue, 20 Mar 2018 at 06:50, Peter Halasz wrote: > When needed I use a declared INTEGER PRIMARY KEY. > > > > > MAYBE THAT WOULD HAVE BEEN IN THE SURVEY TOO BUT I GUESS THERE WAS NO WAY > TO INCLUDE A SMA

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

: @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 20 March 2018 at 08:48, R Smith wrote: > > On 2018/03/20 10:24 AM, Paul Sanders

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

that an int primary key can be auto > incrementing, it can't > > But it works in the same way sort of. Its auto incrementing, with the > caveat that if the last row is deleted, the previous number will be used > again. Depending on the database schema, this may or may n

Re: [sqlite] How to convert a datetime column to a date?

how about select date(dttm) dt,max(i) from foo group by date(dttm) order by 1; 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 w

Re: [sqlite] How to Handle BigInt

SQLite integers are all 64 bit - I don't about postgress, so unless postgress allows integers bigger than 64 bit, and you use them, you should be OK with your table definitions above. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sanderson

Re: [sqlite] How to Handle BigInt

a signed integer, stored in 1, 2, 3, 4, 6, or 8 > bytes depending on the magnitude of the value. > > So perhaps you should have said " SQLite integers are all up to 64 bit." > > Gerry > > On Tue, May 1, 2018 at 8:56 AM, Paul Sanderson < > sandersonforens...@gmai

Re: [sqlite] Only see unused when there are unused records

Or SELECT count(*) AS Total, CASE WHEN Sum(used = 'unused') > 0 THEN Sum(used = 'unused') END AS NotUsed FROM quotes There might be a more succinct way Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/c

Re: [sqlite] This list is getting spammed again

Would it be possible for an admin to run a script that sent an individual email (e.g. different number in subject) to each user on the list and see who is sending the spam based on the replies? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http:/

Re: [sqlite] This list is getting spammed again

forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 11 May 2018 at 10:33, Simon Slavin wrote: > On 11 May 2018, at 10:26am, Paul Sanderson > wrote: > > > Would it be possible for an admin to run a

Re: [sqlite] This list is getting spammed again

11 May 2018 at 10:33, Simon Slavin wrote: > On 11 May 2018, at 10:26am, Paul Sanderson > wrote: > > > Would it be possible for an admin to run a script that sent an individual > > email (e.g. different number in subject) to each user on the list and see > > who is se

Re: [sqlite] question about DB

Try it in a command line shell with the timer on .timer on Paul www.sandersonforensics.com SQLite Forensics Book On 14 May 2018 at 17:27, Simon Slavin wrote: > On 14 May 2018, at 3:03pm, sebastian bermudez > wrote: > > > the question is, there a

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

How about something like SELECT CASE count(*) WHEN 0 THEN 'Discard' ELSE 'Ok' END FROM blocked WHERE email = 'rwillett.dr...@example.com'; Paul www.sandersonforensics.com SQLite Forensics Book On 16 May 2018 at 10:35, Abroży Nieprzełoży < abrozyniep

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

or more in tune with the default value in the schema SELECT CASE count(*) WHEN 0 THEN 'Ok' ELSE action END FROM blocked WHERE email = 'rwillett.dr...@example.com'; Paul www.sandersonforensics.com SQLite Forensics Book On 16 May 2018 at 09:22, Rob

Re: [sqlite] ALTER TABLE

To modify column names if you want to live dangerously you could try something like this PS C:\sqlite> sqlite3 writ.db SQLite version 3.23.1 2018-04-10 17:39:29 Enter ".help" for usage hints. sqlite> create table test (c1, c2, c3); sqlite> insert into test values(1, 2, 3); sqlite> pragma writable_

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

If you are doing each update in a separate transaction it will be much slower than wrapping them in a single transaction. See the faq here, it refers to inserts but updates will be the same. http://sqlite.org/faq.html#q19 Cheers Paul On Wed, 30 May 2018 at 09:34, Torsten Curdt wrote: > > Do

Re: [sqlite] Sqlite delete too slow in 4 GB database

Have you made sure aut_ovacuum is disabled? pragma *auto_vacuum * = 0 have you got a nice large pagesize if your records are that big? Paul www.sandersonforensics.com SQLite Forensics Book On 4 June 2018 at 13:01, Olivier Mascia wrote: > Hello,

Re: [sqlite] [EXTERNAL] Re: Sqlite delete slow in 4GB

The structure of a record is shown in the graphic at this link which is from my book SQLite Forensics: www.sqliteforensics.co.uk/pics/table_leaf_format.png As long as ALL of the serial types (i.e. all of the cell pointer array) is held in the main B-tree (i.e. not an overflow page) which unle

Re: [sqlite] ROWID....

As ROWID is often an alias for an integer primary key then it needs to be able to represent both negaitive and positive integers other wise you restrict the range of an integer PK. Paul www.sandersonforensics.com SQLite Forensics Book On 9 June 201

[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

t; > Or is 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 > Sande

[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 below of

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

[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://sandersonforensi

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

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

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

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

[sqlite] obtaining bracketing rows where rows are missing

quot;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 www.sandersonfore

[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 http:/

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

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

[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: @s

[sqlite] Why is a separate journal file needed ?

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 26 February 2016 at 09:32, Paul Sanderson

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

[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 query.

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

disch 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

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

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

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

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

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

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

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

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

[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

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

Re: [sqlite] Bad db feature request

As mentioned above there is (or can be) reserved space at the end of each page (documented in the DB header) that can be used for checksums - you just need to write your own extension :) https://www.sqlite.org/fileformat2.html Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonfo

Re: [sqlite] Bad db feature request

for a fully functional demo licence On 29 June 2016 at 22:10, Dominique Devienne wrote: > On Wed, Jun 29, 2016 at 9:54 PM, Paul Sanderson < > sandersonforens...@gmail.com> wrote: > >> As mentioned above there is (or can be) reserved space at the end of >> each page (d

[sqlite] Document ommision bind-parameter description

There is a minor ommision on the BNF page of the database http://www.sqlite.org/docsrc/doc/trunk/art/syntax/all-bnf.html#bind-parameter In the expr section bind-parameter is a dangling hyper link Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] Document ommision bind-parameter description

12:20, Richard Hipp wrote: > On 7/13/16, Paul Sanderson wrote: >> There is a minor ommision on the BNF page of the database >> >> http://www.sqlite.org/docsrc/doc/trunk/art/syntax/all-bnf.html#bind-parameter >> >> In the expr section bind-parameter is a dangling h

Re: [sqlite] Using Bitwise Logic In Query

How does this work for you to get all the even rows SELECT ROWID FROM table WHERE ROWID & 0x01 = 0x00 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 fo

Re: [sqlite] [WAL] Querying/Finding out the WAL size (in pages) without trying to checkpoint

From within SQLite - I don't know if its possible. But if you can query the WAL file size you should be able to determine the number of pages easily enough. pages = (walfilesize-32)/(DBpagesize+24) the only caveats I can think of are: The WAL file is not truncated after a checkpoint so you woul

Re: [sqlite] [WAL] Querying/Finding out the WAL size (in pages) without trying to checkpoint

cating > checkpoints are a good idea, especially after going through "checkpoint > starvation" moments... > > In that case, I think I could make your suggestion work. > > Thank you ! > > On 20 September 2016 at 11:57, Paul Sanderson > wrote: > >> Fro

[sqlite] converting unix10 and unix13 dates in the same column

I have a table with dates in different formats, either 10 digit or 13 digit unix dates 1234345087123 1234567890 1432101234 1456754323012 I want a sql query that will convert both dates, I tried this SELECT CASE dt WHEN (unix10and13.dt < 100) THEN DateTime(unix10and13.dt, 'unixepoc

Re: [sqlite] converting unix10 and unix13 dates in the same column

results of the expression are always > 1 or 0, which never equals DT, so the THEN clause is never executed and the > ELSE is always taken. > > >> -Original Message----- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf

Re: [sqlite] converting unix10 and unix13 dates in the same column

All sorted now thank you ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

  1   2   3   >