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

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

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] unique values from a subset of data based on two fields

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.sqlite.org] On Behalf Of Paul Sanderson > >Sent: Friday, 2

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

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 Of Paul Sa

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

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

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

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

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

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

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

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

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

Re: [sqlite] This list is getting spammed again

On 11 May 2018 at 10:33, Simon Slavin <slav...@bigfraud.org> wrote: > On 11 May 2018, at 10:26am, Paul Sanderson <sandersonforens...@gmail.com> > wrote: > > > Would it be possible for an admin to run a script that sent an individual > > email (e.g. differen

Re: [sqlite] This list is getting spammed again

/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 <slav...@bigfraud.org> wrote: > On 11 May 2018, at 10:26am, Paul Sanderson <sandersonforens...@gmail.com> >

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

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

Re: [sqlite] How to Handle BigInt

; > *INTEGER*. The value is 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 Sander

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

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

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

y not cause issues. > > > Thanks, > Chris > > > On Tue, Mar 20, 2018 at 9:45 AM, Paul Sanderson < > sandersonforens...@gmail.com> wrote: > > > I read that - but my point was more that some people seem to think that > an > > int primary key can be

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

ndersonforens 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 <ryansmit...@gmail.com> wrote: > > On

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

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

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

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

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

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

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.

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

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

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

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

Re: [sqlite] Summarising (missing) values

demo licence On 18 July 2017 at 11:19, Clemens Ladisch <clem...@ladisch.de> wrote: > Paul Sanderson wrote: > > What I would like is a single query that summarises the values that are > > present in (or missing from) a table. > > A row is the start of a range

[sqlite] Summarising (missing) values

I have a table which includes a numeric "ID" column, values in the column generally increment but there are some gaps. I use the following query to get a list of all "missing" values WITH RECURSIVE cte(x) AS (SELECT (SELECT Min(messages._id) FROM messages) UNION ALL SELECT cte.x + 1

Re: [sqlite] Sqlite problem with opening database

Could your 32 bit app be picking up a 64 bit dll. Could you rename the dll's and hard code the location into your library? May not work for your release code but may help you narrow down the issue. Paul On Wed, 5 Jul 2017 at 18:19, Simon Slavin wrote: > > > On 5 Jul

Re: [sqlite] syntax error near AS

The SQLite syntax diagrams are my first point of call when looking at an error in my code like this. https://sqlite.org/lang_update.html "AS" and an alias are clearly not part of the statement. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] FOREING KEY constraint

pragma foreign_key_list(table_name) may help Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional

[sqlite] Document typo?

https://sqlite.org/dbstat.html The DBStat web page defines that schema of the virtual table as follows with path defined as an integer CREATE TABLE dbstat( name STRING, -- Name of table or index path INTEGER,-- Path to page from root pageno INTEGER,-- Page

Re: [sqlite] Providing incrementing column to query

ult row numbers since they do > not correlate with anything meaningful? > > -- > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > > > -Original Message- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > On Behalf Of Paul

Re: [sqlite] Providing incrementing column to query

-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 24 June 2017 at 13:10, Clemens Ladisch <clem...@ladisch.de> wrote: > Paul Sanderson wrote: > > I Have a number of queries to which I want to supply an incrementing >

[sqlite] Providing incrementing column to query

I Have a number of queries to which I want to supply an incrementing column, some of these queries involve without rowid tables. I have no control over the design of the tables. So for a table defined as: CREATE TABLE (name text, age integer) with values Steve, 34 Eric, 27

Re: [sqlite] imposter tables

.za> wrote: > > On 2017/06/15 10:15 AM, Paul Sanderson wrote: > >> Thanks Richard - the View approach is fine for my needs - just wanted to >> know what the rationale was for the imposter tables given the ability to >> simulate the imposter table with a view. &g

Re: [sqlite] imposter tables

for SQLite email from a work address for a fully functional demo licence On 14 June 2017 at 13:11, Simon Slavin <slav...@bigfraud.org> wrote: > > > On 14 Jun 2017, at 12:52pm, Paul Sanderson <sandersonforens...@gmail.com> > wrote: > > > The only benefit I can see is

[sqlite] imposter tables

I am just taking a look at imposter tables and while the implementation is neat I am just wondering what their use is, or rather what they can achieve that a view can't achieve (and without the risk of DB corruption). For instance an imposter table created on an index such as the following from

Re: [sqlite] auntondex with unique and integer primary key

Ahh - I always let SQLite decide what index to use as I assume that it knows best. I have never used "indexed by" to force the use of a specific index - I see the issue with backward compatibility now. Thanks Richard Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel

Re: [sqlite] auntondex with unique and integer primary key

demo licence On 19 May 2017 at 19:29, Joseph L. Casale <jcas...@activenetwerx.com> wrote: > > -Original Message- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On > > Behalf Of Paul Sanderson > > Sent: Friday, May 19, 2017 12:

Re: [sqlite] auntondex with unique and integer primary key

2017 at 18:49, Simon Slavin <slav...@bigfraud.org> wrote: > > On 19 May 2017, at 6:21pm, Paul Sanderson <sandersonforens...@gmail.com> > wrote: > > > Is the autoindex associated when using unique with an integer primary key > > definition redundant? > > >

[sqlite] auntondex with unique and integer primary key

Is the autoindex associated when using unique with an integer primary key definition redundant? I have seen a number of DBs/tables created in the following form: Create table test(id integer unique primary key); Insert into test values (1); Insert into test values (2); Insert into test values

Re: [sqlite] NOT NULL integer primary key

licence On 18 May 2017 at 17:26, Gwendal Roué <gwendal.r...@gmail.com> wrote: > > > Le 18 mai 2017 à 18:16, Paul Sanderson <sandersonforens...@gmail.com> a > écrit : > > > > Is this a bug? > > > > Create table test (id integer not null primary ke

[sqlite] NOT NULL integer primary key

Is this a bug? Create table test (id integer not null primary key, data text); insert into test values (null, 'row1'); select * from test; 1, row1 I know that if you provide a NULL value to a column define as integer primary key that SQLite will provide a rowid, but should the not null

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

another oops I see Ryan pretty much posted the same as me 5 minutes earlier - I'll go back to bed :) 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

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

Try something like SELECT dateplay.vi, JulianDay('now') AS now, JulianDay(SubStr(Replace(dateplay.vi, '/', '-'), 1, 10)) AS jday, JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/', '-'), 1, 10)) AS diff, CASE WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi,

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 12 April 2017 at 13:37, Paul Sanderson <sandersonforens...@gmail.com> wrote: > Hi Ron > > Your dates are still not 8601 > > with your da

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

Hi Ron Your dates are still not 8601 with your dates above in a table called dateplay and column named vi select vi, julianday('now') as now, julianday(substr(replace(vi, '/', '-'), 1, 10)) as jday, julianday('now') - julianday(substr(replace(vi, '/', '-'), 1, 10)) as diff from

Re: [sqlite] column alignment of views and tables;

(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 4 April 2017 at 15:07, Paul Sanderson <sandersonforens...@gmail.com> wrote: > Oops would need to subtract the string

Re: [sqlite] column alignment of views and tables;

Just shooting out so no time to test. But could you try something like select substring('', 1, length(printf("%2.f", price))) || printf("%2.f", price) from prices Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] BLOB sizes beyond 2GB?

wrote: > On Tue, Mar 28, 2017 at 12:52 PM, Paul Sanderson < > sandersonforens...@gmail.com> wrote: > > > I am sure Richard will correct me if I am wrong. But... > > > > The format for a record is > > > > 1. payload length varint > > 2. rowid varint (opti

Re: [sqlite] BLOB sizes beyond 2GB?

I am sure Richard will correct me if I am wrong. But... The format for a record is 1. payload length varint 2. rowid varint (optional) 3. serial type array varint 4. serial types followed by the data for the serial types The issue are as I see them: The payload length varint above, this is the

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

email from a work address for a fully functional demo licence On 19 March 2017 at 12:07, Paul Sanderson <sandersonforens...@gmail.com> wrote: > What is the average size of the text in the direction field? and what page > size have you set for the database? If the size

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

What is the average size of the text in the direction field? and what page size have you set for the database? If the size of a record is such that only a small handful fit into a page, or worse each record overflows (and your select includes the direction field) then this could impact

Re: [sqlite] Why isn't my time formatting working?

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 8 March 2017 at 22:57, Tim Streater <t...@clothears.org.uk> wrote: > On 08 Mar 2017 at 20:40, Paul Sanderson <san

Re: [sqlite] Why isn't my time formatting working?

The vast majority of dates I see in SQLite databases are unix epoch integer times (seconds since 1/1/1980) with unix milli seconds a close second. Efficient to store, sort and do date arithmetic on but need to be converted to display. I also see unix nano seconds, 100 nano seconds, windows

Re: [sqlite] Non-unique columns in unique index

Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 1 March 2017 at 22:13, Paul

Re: [sqlite] Non-unique columns in unique index

As a bit of an off the wall suggestion you could try an MD5 (or even a partial MD5 - half of the bytes) CREATE table hashes ( hash integer primary key; // just the first 64 bits of the hash of uniquecol and extracol ) as an integer primary key the hash would be an alias of the rowid and so

[sqlite] Documentation error

The process for calculating a checksum text on the SQLite file format page contains two errors. Currently reads: The checksum is an unsigned 32-bit integer computed as follows: 1. Initialize the checksum to the checksum nonce value found in the journal header at offset 12. 2.

Re: [sqlite] New tool for PUTTY logging [Windows]

You could make the CmdEntered field unique, or create a hash on the uppercase content of the command and make that a unique key. Then use INSERT OR IGNORE... Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] Date (from REAL) with Negative Year

rather unintuitively excel uses the OLE automation timestamp that records the number of days since 1899/12/30 http://sandersonforensics.com/forum/content.php?131-A-brief-history-of-time-stamps https://msdn.microsoft.com/en-us/library/system.datetime.tooadate(v=vs.110).aspx now no need for the

Re: [sqlite] Read-only access which does not block writers

Could you use PRAGMA data_version before and after each read to see whether there have been any changes to the DB - not surehow this works in WAL mode? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] Read-only access which does not block writers

Steps 2 and 3 can be swapped Also you can convert an existing database from one mode to another (although not, I suspect (I have not tried), in the middle of a transaction and if in the DB is wal mode and you are changing to journal then this would force a checkpoint). Paul

Re: [sqlite] Pragma to flag unknown pragma?

Most pragmas allow you to issue them in a mode such as to query a current state. So to use your example pragma journal_mode = persist; followed by pragma journal_mode; with a subsequent check to see that the returned value is set to what you want it to be set to (actually the new value is

Re: [sqlite] Issue with Malformed table

If you are unsucessful dro me an email - I might be able to help (not a commercial proposition - just may help me doing some testing with my code) Cheers Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] Data loss during the disk full condition

I haven't seen anything to say what journalling is being used (Rollback, WAL or none). If the latter then SQLite will have nothing to revert to on error. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] Order of fields for insert

> Long columns, especially TEXT or BLOBs which may have lots of data in, should > go at the end. Because you don't want SQLite to have to fetch all that data > from storage just to get at the column after it. To be pedantic SQLite does not need to "fetch" all of the data from strorage before

Re: [sqlite] Order of fields for insert

SQLite does not use any compression when storing data. Occasionally rows have so much data that they overflow to an additonal page(s) so the advice about defining tables so that blobs are at the end of the definition is good - also columns that store long strings might be better at the end of a

Re: [sqlite] Searching for a percent symbol

October 2016 at 11:00, Paul Sanderson <sandersonforens...@gmail.com> wrote: > Brilliant thansks Dominique - I had completely misunderstood it :) > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonfore

Re: [sqlite] Searching for a percent symbol

for a fully functional demo licence On 5 October 2016 at 10:53, Dominique Devienne <ddevie...@gmail.com> wrote: > On Wed, Oct 5, 2016 at 11:50 AM, Paul Sanderson < > sandersonforens...@gmail.com> wrote: > >> Thanks Petite - I have already looked at that - but how? >&

Re: [sqlite] Searching for a percent symbol

/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 5 October 2016 at 10:44, Petite Abeille <petite.abei...@gmail.com> wrote: > >> On Oct 5, 2016, at 11:38 AM, Paul Sanderson <sandersonforens...@gm

[sqlite] Searching for a percent symbol

Hi all I have a column of string values some of which may contain % characters Is it possible to search for just those rows that contain a % eg how could I search for 20%. on a test table containing: I got 20 quid i got 20% of it i got just 20% some money this is an underscore _ ok I tried

Re: [sqlite] smartest way to exchange a sqlite3 database with another empty

It seems that you just want to keep access to all of your historic logging so rather than copy/backup the entire database you could just create a new archive DB (or open an old one), attach it, copy x records to the archive and then delete the same x records from the master. How big is your log

Re: [sqlite] Unable to create two indexes with the same name but on different tables

Sorry what is "very hard" about SELECT * FROM sqlite_master WHERE type = 'index' AND name = 'new_index_name' to see if the index already exists Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

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

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

00). The 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 B

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

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

ealize that truncating > 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 <sandersonfore

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

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

Re: [sqlite] Document ommision bind-parameter description

at 12:20, Richard Hipp <d...@sqlite.org> wrote: > On 7/13/16, Paul Sanderson <sandersonforens...@gmail.com> 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 >>

[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] Bad db feature request

for a fully functional demo licence On 29 June 2016 at 22:10, Dominique Devienne <ddevie...@gmail.com> 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

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:

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

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

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

  1   2   3   >