Re: [sqlite] Common index for multiple databases

[Paul Sanderson]

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 indexes associated with the customers
table.  You can also create an accounting.db containing all the accounting
tables (but not the customers table) and all the indexes that belong to
those tables.  Lather, Rinse, Repeat.  Create multiple databases and do not
duplicate table names -- that is each table goes in one and only one
database).

You then "open" a :memory: database and "ATTACH" your other database hunks
as required for the application.  You refer to the tables only by table
name and provided that you have not duplicated tables in multiple database
files, then SQLite3 will operate on the appropriate attached database.
Since the indexes associated with a table must be stored in the same
database file as the data table itself, all the indexes will be kept
up-to-date.  You will not be able to have the database enforce referential
integrity across multiple "attached" databases, nor use cross "attachment"
triggers (but you cannot do that now anyway).

---

If you do, for some reason, choose this approach then by default SQLite
limits the number of attached databases to 10, you can adjust this up to a
max of 125. More info below:

https://www.sqlite.org/limits.html

Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing index size

[Paul Sanderson]

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 size of an index on strings/blobs ?
>
> I have tables which are a key + value, the key is an "integer primary key
> autoincrement", which is used for references in all other tables of the
> schema.
> The values are cryptographic GUIDs (so 256 to 512 bits in size) with a
> "compact" encoding (either base64 or blob rather than hexadecimal strings),
> but they still represent gigabytes of data.
>
> Those tables have an index on the value, and my problem is that the size of
> this index (as reported by dbstat or sql3_analyzer) is about the same
> as the table.
>
> As these are cryptographic GUIDs, the first few bytes of a values are in
> practice unique, so in theory I can index just the first few bytes (using
> substr()),
> this indeed reduces in a much smaller index, but this also requires
> adapting all queries that search by value.
>
> Before starting down that route, is there another way?
>
> My searches on those indexes are by either exact value or by value start
> (human search & auto-completion)
>
> Eric
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minimum Delta Time

[Paul Sanderson]

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
>
> update TimeEvents
> set EndTime = max(
> current_timestamp,
> datetime(StartTime, '+10 minutes')
> )
> where
> EventID = ?
> and (EndTime is null or EndTime = '');
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Stephen Chrzanowski
> Sent: Wednesday, July 11, 2018 10:25 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Minimum Delta Time
>
> I've got an application that I've written that keeps track of time spent on
> particular tasks.  I do many things throughout the day, going between
> different tasks, and tasks I've already worked on for today, so this tool
> is incredibly helpful to help justify my warming a chair.
>
> I'd prefer the SQL code to handle this particular procedure I'm about to
> describe.  Obviously, in the application, I can make an additional trip to
> the database to yank out the start time, add the 10 minutes, then do the
> update, but I'd rather the database deal with this particular business rule
> since the handling of the actual values is done at the database level.
>
> At the bottom of this email is the Delphi code and the table schema I'm
> interested in.
>
> What I have is a button on the UI that toggles start/stop work times on a
> particular task.  The Delphi Code below shows the toggling methodology.  My
> interest is modifying the Stop part so that at a minimum, there is a 10
> minute delta between the start and end time.  So if I start a timer at
> 11:00, then stop at 11:01, I want the database to update the end time to
> 11:10.
>
> I suspect a SQLite CASE statement may be of help, but I'm not sure how to
> check for the 10 minute delta then update the row with the altered time or
> the real time.
>
>
>
> *Delphi Code:*
> tbl:=db.GetTable('select EndTime from TimeEvents where EventID=? order by
> StartTime desc',[EventID]);
> // If this task doesn't have a previous timer, or, this task has no
> currently running timers, make a new timer
> // otherwise, stop the currently running timer
> if (tbl.RowCount=0)or(tbl.FieldByName['EndTime']<>'') then begin
>   db.ExecSQL('insert into TimeEvents (EventID) values (?)',[EventID]);
> end else begin
>   db.ExecSQL('update TimeEvents set EndTime=current_timestamp where
> EventID=? and (EndTime is null or EndTime="")',[EventID]);
> end;
>
> *Table Schema*
> CREATE TABLE [TimeEvents](
>   [EventNumber] INTEGER PRIMARY KEY AUTOINCREMENT,
>   [EventID] integer NOT NULL REFERENCES [tEvents]([EventID]) ON DELETE
> CASCADE,
>   [StartTime] DATETIME(10) NOT NULL DEFAULT CURRENT_TIMESTAMP,
>   [EndTime] DATETIME);
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Easier and pretty obvious :) Thanks Keith



Paul
www.sandersonforensics.com
SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>

On 29 June 2018 at 23:20, Keith Medcalf  wrote:

> >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 the list status=1
>
> Translation into SQL using English to SQL Translator, using the most
> direct translation on the "problem statement" above directly into SQL:
>
> create table names (id int, status int, name text);
> insert into names values (1, 1, 'paul');
> insert into names values (2, 1, 'helen');
> insert into names values (3, 0, 'steve');
> insert into names values (4, 0, 'steve');
> insert into names values (5, 0, 'pete');
> insert into names values (6, 0, 'paul');
>
> -- I want a query that returns all of the records with status = 1
>
> SELECT id,
>status,
>name
>   FROM names
>  WHERE status == 1
>
> -- and
>
> UNION
>
> -- unique records, based on name, where the status = 0 and the name is not
> in the list [of names where] status=1
>
> SELECT id,
>status,
>name
>   FROM names
>  WHERE status == 0
>AND name NOT IN (SELECT name
>   FROM names
>  WHERE status == 1)
> GROUP BY name;
>
> Returns the rows:
>
> 1|1|paul
> 2|1|helen
> 3|0|steve
> 5|0|pete
>
> If the table is bigger than trivial (ie, contains more than the number of
> rows you can count with your fingers) then you will need the appropriate
> indexes 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.sqlite.org] On Behalf Of Paul Sanderson
> >Sent: Friday, 29 June, 2018 09:50
> >To: General Discussion of SQLite Database
> >Subject: [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
> >the list
> >status=1
> >
> >
> >
> >So from the above I would want to see
> >
> >
> >
> >1, 1, paul
> >
> >2, 1, helen
> >
> >3, 0, steve (or 4, 0, steve)
> >
> >5, 0, pete
> >
> >
> >
> >I could do something like
> >
> >
> >
> >Select * from names where status = 1 or name not in (select name from
> >names
> >where status = 1)
> >
> >
> >
> >But this gets both rows for steve, e.g.
> >
> >
> >
> >1, 1, paul
> >
> >2, 1, helen
> >
> >3, 0, steve
> >
> >4, 0, steve
> >
> >5, 0, pete
> >
> >while I am not bothered about which of the two steves I get back, I
> >must
> >have all occurences of names with status = 1
> >
> >I am probably missing somethng obvious
> >
> >Paul
> >www.sandersonforensics.com
> >SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Thanks Ryan

As often is the case the the actual problem is more complex than my example
- sometimes we over simplify to, well, simplify - but you have both given
me some ideas and I'll go away and play.

Paul

Paul
www.sandersonforensics.com
SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>

On 29 June 2018 at 20:24, R Smith  wrote:

> The solution from David works perfectly, just want to point out some CTE
> things since you mention getting into it.
> 1st - A nice thing about CTE is that, in the case of a non-recursive CTE
> (like this one), it can well be replaced by a simple sub-query, however,
> the CTE can be referenced more than once, unlike a sub-query.
> 2nd - One can even use the CTE in other sub-queries.
>
> With this in mind, here is another option for the query which is
> functionally equivalent (i.e. it's not better, simply showing alternate CTE
> use):
>
> with status_one as (
>   select *
> from names
>where status = 1
> )
> select min(id), status, name
>   from names
>  where status = 0 and name not in (select name from status_one)
>  group by status, name
> union all
> select * from status_one
> ;
>
>
> CTE capability is one of my favourite additions ever to SQLite (I may have
> mentioned this before), so I hope you too find them useful and joyful.
>
> Cheers,
> Ryan
>
>
> On 2018/06/29 6:45 PM, David Raymond wrote:
>
>> with status_one as (
>>select *
>>from names
>>where status = 1
>> ),
>> one_names as (
>>select distinct name
>>from status_one
>> )
>> select min(id), status, name
>> from names
>> where status = 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 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
>>
>> 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 the
>> list
>> status=1
>>
>>
>>
>> So from the above I would want to see
>>
>>
>>
>> 1, 1, paul
>>
>> 2, 1, helen
>>
>> 3, 0, steve (or 4, 0, steve)
>>
>> 5, 0, pete
>>
>>
>>
>> I could do something like
>>
>>
>>
>> Select * from names where status = 1 or name not in (select name from
>> names
>> where status = 1)
>>
>>
>>
>> But this gets both rows for steve, e.g.
>>
>>
>>
>> 1, 1, paul
>>
>> 2, 1, helen
>>
>> 3, 0, steve
>>
>> 4, 0, steve
>>
>> 5, 0, pete
>>
>> while I am not bothered about which of the two steves I get back, I must
>> have all occurences of names with status = 1
>>
>> I am probably missing somethng obvious
>>
>> Paul
>> www.sandersonforensics.com
>> SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Thank You David - I was just starting to play with CTEs



Paul
www.sandersonforensics.com
SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>

On 29 June 2018 at 17:45, David Raymond  wrote:

> with status_one as (
>   select *
>   from names
>   where status = 1
> ),
> one_names as (
>   select distinct name
>   from status_one
> )
> select min(id), status, name
> from names
> where status = 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 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
>
> 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 the list
> status=1
>
>
>
> So from the above I would want to see
>
>
>
> 1, 1, paul
>
> 2, 1, helen
>
> 3, 0, steve (or 4, 0, steve)
>
> 5, 0, pete
>
>
>
> I could do something like
>
>
>
> Select * from names where status = 1 or name not in (select name from names
> where status = 1)
>
>
>
> But this gets both rows for steve, e.g.
>
>
>
> 1, 1, paul
>
> 2, 1, helen
>
> 3, 0, steve
>
> 4, 0, steve
>
> 5, 0, pete
>
> while I am not bothered about which of the two steves I get back, I must
> have all occurences of names with status = 1
>
> I am probably missing somethng obvious
>
> Paul
> www.sandersonforensics.com
> SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 the list
status=1



So from the above I would want to see



1, 1, paul

2, 1, helen

3, 0, steve (or 4, 0, steve)

5, 0, pete



I could do something like



Select * from names where status = 1 or name not in (select name from names
where status = 1)



But this gets both rows for steve, e.g.



1, 1, paul

2, 1, helen

3, 0, steve

4, 0, steve

5, 0, pete

while I am not bothered about which of the two steves I get back, I must
have all occurences of names with status = 1

I am probably missing somethng obvious

Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

 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 type 0, 8 & 9
respectively and there is no data stored for the column - the content of
the column is inherent in the serial types array.

Blobs and Strings with no content are serial types 12 & 13, and there is
also zero bytes of data associated with each.



Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ROWID....

[Paul Sanderson]

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 2018 at 13:03, Kevin Benson  wrote:

> On Sat, Jun 9, 2018 at 7:03 AM Luuk  wrote:
>
> >
> > In the docs (https://www.sqlite.org/autoinc.html) it says:
> > In SQLite, table rows normally have a 64-bit signed integer ROWID
> >  
> >
> > Question:
> > Why it this a signed integer, and not an unsigned integer?
> >
> > Simply by choice? of is there something more to say about this?
> >
>
> http://sqlite.1065341.n5.nabble.com/Use-of-AUTOINCREMENT-td74775.html#
> a74786
> --
>--
>   --
>  --Ö¿Ö--
> K e V i N
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 unless you have a
huge number of columns in a table will pretty much always be the case, then
finding out the offset to a given field in the payload is a case of just
reading and determining the size of each serial type until you get to the
entry you want. If the payload data you want is in the same page, before or
after a blob, then accessing it is just a case of reading from the current
page buffer. It is only when the blob is so large that the data you want is
in an overflow page that things slow down, it shouldn't matter if some of
the record overflows, as long as the data you want is on the b-tree leaf
page.



If the blob is very big then you may need to read multiple overflow pages
to get to the data you want.



It is also worth bearing in mind that this does not just apply to blobs, if
you have a large string field, or multiple string fields that cause a
record to overflow, then you have exactly the same problem with records
that come after the string fields.


Paul
www.sandersonforensics.com
SQLite Forensics Book 

On 6 June 2018 at 10:15, Hick Gunter  wrote:

> Your schema has a major flaw that is addressed in SQLite documentation:
>
> Do not put any fields after blob fields, especially if the content is
> typically large. SQLite accesses fields in the order of defintion, so to
> access your ANH_PRC_id field, it has to retrieve the 1MB ANP_VALUE blob.
>
> Putting small and frequently acessed fields at the beginning of the
> definition allows SQLite to retrieve these fields without expanding the
> whole row.
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Aydin Ozgur Yagmur
> Gesendet: Mittwoch, 06. Juni 2018 11:06
> An: SQLite mailing list 
> Betreff: [EXTERNAL] Re: [sqlite] Sqlite delete slow in 4GB
>
> Thank you very quick response.
>
> We have already planned to change file system. But this problem seem not
> related with the file size, because we did not hit the limit and when i try
> to delete same data in ,for example, 2 GB-sized db, i encounter with the
> same problem. This is my table and index definitions.
>
> Thanks for your help.
>
> CREATE TABLE "ANHXT" (
>   "id" integer primary key autoincrement,
>   "ANH_AD" text,
>   "ANH_DBGMHWID" text,
>   "ANH_TYPE" integer,
>   "ANH_INDEXNO" int64_t)
> CREATE TABLE "PRCXT" (
>   "id" integer primary key autoincrement,
>   "ANP_SEGMENTNO" integer not null,
>   "ANP_VALUE" blob,
> ==>  "ANH_PRC_id" bigint,
>   constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references
> "ANHXT" ("id") on update cascade on delete cascade deferrable initially
> deferred) CREATE UNIQUE INDEX UQC_ANH_TYPE on ANHXT( ANH_TYPE)CREATE UNIQUE
> INDEX UQC_ANP_SEGMENTNO_ANAHTARID on PRCXT( ANP_SEGMENTNO,ANH_PRC_id)CREATE
> INDEX findex on PRCXT( ANH_PRC_id)
>
>
>
> On Wed, Jun 6, 2018 at 11:42 AM, Karl Billeter 
> wrote:
>
> > On Wed, Jun 06, 2018 at 09:27:57AM +0300, Aydin Ozgur Yagmur wrote:
> >
> > > I have been using fat32 file system. I have a database which has 4
> > > GB
> > size.
> >
> > Er... I'm surprised there aren't more problems due to 4GB being the
> > max file size supported by fat32.  Any chance to change it to exFAT?
> >
> >
> > K
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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,
>
> > Sqlite delete too slow in 4 GB database
>
> What does:
>
> 'pragma secure_delete;'
>
> and
>
> 'pragma auto_vacuum;'
>
> say, on that db?
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 the select and updates run inside a explicit transaction or they
> > run in individual implicit transactions?
> >
>
> implicit - does that make a big difference in this case?
>
>
> If you really want a single query you could write something like:
> >
> > WITH data(id, c1, c2 /*, ... */) AS (VALUES
> > (123, 'abc', 'xyz' /*, ... */),
> > (456, 'xxx', 'yyy' /*, ... */),
> > (789, 'xyz', 'xyz' /*, ... */)
> > /*  ...  */
> > ) UPDATE tab
> > SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE data.id =
> > tab.id)
> > WHERE id IN (SELECT id FROM data);
> >
> >
> But for that again means all the data (or the single query) needs to be
> built up in memory.
>
> cheers,
> Torsten
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ALTER TABLE

[Paul Sanderson]

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_schema = yes;
sqlite> update sqlite_master set sql = 'create table test (d1, d2, d3)'
where name = 'test';
sqlite> .quit

PS C:\sqlite> sqlite3 writ.db
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> select * from test;
d1|d2|d3
1|2|3

You could potentially do the same to modify the type of a column and
SQLites column affinity rules could/should help display the data correctly.
I have not tested this for different column types

sqlite> create table test2 (c1 int, c2 int, c3 int);
sqlite> insert into test2 values(1, 2, 3);
sqlite> pragma writable_schema = yes;
sqlite> update sqlite_master set sql = 'create table test2 (d1 text, d2
text, d3 text)' where name = 'test2';
sqlite> .quit

PS C:\sqlite> sqlite3 writ.db
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> select * from test2;
d1|d2|d3
1|2|3

The same idea should also work to add/remove a foreign key -  but you would
of course need to understand any implications and ensure that the existing
data does not cause a constraint conflict.

The only suggestion I have a problem with is dropping a column. Every
record in the b-tree would need to be modified to remove the now redundant
data. The on;ly exception I can see to this is if you are dropping the last
column from a table

sqlite> pragma writable_schema = yes;
sqlite> update sqlite_master set sql = 'create table test2 (e1 text, e2
text)' where name = 'test2';
sqlite> .quit

PS C:\sqlite> sqlite3 writ.db
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> select * from test2;
e1|e2
1|2
sqlite> pragma integrity_check;
integrity_check
ok

I have not done any thorough testing as this sort of thing is outside my
main area of interest, but it might give you some ideas. It goes without
saying that messing with the sqlite_schema is dangerous territory.





Paul
www.sandersonforensics.com
SQLite Forensics Book 

On 22 May 2018 at 20:34, Igor Korot  wrote:

> Hi, Charles,
>
> On Tue, May 22, 2018 at 2:30 PM, Charles Leifer 
> wrote:
> > As a workaround, you can always rename the existing table, create the new
> > table with desired attributes, and do a INSERT INTO ... SELECT FROM
> > old_table. Then you can safely drop the old table.
>
> But the table_name will be different.
> Also the data in the old table might be referencing some other table.
> So this process is not really very
> straightforward...
>
> Thank you.
>
> >
> > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz 
> wrote:
> >
> >> > ALTER TABLE ADD COLUMN has existed for a long time.
> >>
> >> Yes, sorry, I mixed things up.
> >>
> >> The order of importance is imho:
> >> 1. RENAME COLUMN (shouldn't be too hard)
> >> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> >> 3. MODIFY COLUMN
> >>
> >> > What kind of MODIFY COLUMN changes do you have in mind?
> >>
> >> I understand this can be difficult as there are many possible operations
> >> that might be incompatible with the data already stored in that column.
> >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE
> CASCADE
> >> action of a foreign key column. Also adding/removing a foreign key
> would be
> >> useful.
> >>
> >> Kind regards,
> >> Thomas
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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

> Hi,
>
> I'm experimenting with an email server, Mail In a Box. It's a free product
> and gives you a mail server in a box with SSL certificates, multiple
> domains and seems to work well.
>
> One thing it doesn't do is allow people to specify emails to block.
>
> It uses SQLite to store the underlying data necessary to power Postfix. Dr
> Richard Hipp, add another new application to your list :)
>
> I've worked out how Postfix calls SQLite (pretty easy), worked out what I
> want to do, and am trying to write a single line of SQL that does it.
>
> The need is that the SQL must generate a single string answer depending on
> the email address that is fed in through the query. There are no options
> here, it must return a value even if there is nothing in the table. It is
> not practical to add a table with every email address that returns OK.
>
> For this example the only strings it can return are 'OK' and 'DISCARD',
> though the RFC allows other strings. If a query is done on an email and it
> is blocked then it must return DISCARD (or an action in the action column.
> If no email is in the table matching then it must return 'OK'.
>
> As a piece of pseudo code this would ne
>
> function GetMailStatus(emailAddress)
> {
> IF emailAddress is present in blocked THEN
> return action associated with emailAddress -- Action is
> normally DISCARD
>
>  return 'OK'
> }
>
> I've created the table
>
> CREATE TABLE blocked (id INTEGER PRIMARY KEY AUTOINCREMENT,
>email TEXT NOT NULL UNIQUE ,
>   action TEXT NOT NULL DEFAULT 'DISCARD')
>
> I can insert values
>
> insert into blocked (email) values('rwillett.dr...@example.com')
> insert into blocked (email) values('rwillett+dr...@example.com')
>
> And this is the code that I have written that works but it looks poor to
> my untutored eyes
>
> select CASE
> WHEN EXISTS (select 1 from blocked where email = '
> rwillett.dr...@example.com')
> THEN (select action from blocked where email = '
> rwillett.dr...@example.com')
> ELSE 'OK'
> END
>
>
> In the Postfix query I'll replace
>
> email = 'rwillett.dr...@example.com'
>
> with email = '%s'
>
> so that the email address is passed in.
>
> My worry is that the query has two selects and 'feels' bad. It works but
> feels dirty...
>
> Is there a better way under SQLite?
>
> Any suggestions welcomed.
>
> Rob
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 <
abrozynieprzelozy314...@gmail.com> wrote:

> I would make something like this:
>
> CREATE TABLE blocked(
> mail TEXT PRIMARY KEY,
> action TEXT NOT NULL DEFAULT 'DISCARD'
> ) WITHOUT ROWID;
>
> INSERT INTO blocked(mail) VALUES('badm...@example.com');
>
> SELECT coalesce(action, 'OK') AS action
> FROM (SELECT 'goodm...@example.com' AS mail)
> LEFT JOIN blocked USING(mail);
>
> SELECT coalesce(action, 'OK') AS action
> FROM (SELECT 'badm...@example.com' AS mail)
> LEFT JOIN blocked USING(mail);
>
>
> 2018-05-16 10:22 GMT+02:00, Rob Willett :
> > Hi,
> >
> > I'm experimenting with an email server, Mail In a Box. It's a free
> > product and gives you a mail server in a box with SSL certificates,
> > multiple domains and seems to work well.
> >
> > One thing it doesn't do is allow people to specify emails to block.
> >
> > It uses SQLite to store the underlying data necessary to power Postfix.
> > Dr Richard Hipp, add another new application to your list :)
> >
> > I've worked out how Postfix calls SQLite (pretty easy), worked out what
> > I want to do, and am trying to write a single line of SQL that does it.
> >
> > The need is that the SQL must generate a single string answer depending
> > on the email address that is fed in through the query. There are no
> > options here, it must return a value even if there is nothing in the
> > table. It is not practical to add a table with every email address that
> > returns OK.
> >
> > For this example the only strings it can return are 'OK' and 'DISCARD',
> > though the RFC allows other strings. If a query is done on an email and
> > it is blocked then it must return DISCARD (or an action in the action
> > column. If no email is in the table matching then it must return 'OK'.
> >
> > As a piece of pseudo code this would ne
> >
> > function GetMailStatus(emailAddress)
> > {
> >   IF emailAddress is present in blocked THEN
> >   return action associated with emailAddress -- Action is
> normally
> > DISCARD
> >
> >   return 'OK'
> > }
> >
> > I've created the table
> >
> > CREATE TABLE blocked (id INTEGER PRIMARY KEY AUTOINCREMENT,
> >  email TEXT NOT NULL UNIQUE ,
> >action TEXT NOT NULL DEFAULT 'DISCARD')
> >
> > I can insert values
> >
> > insert into blocked (email) values('rwillett.dr...@example.com')
> > insert into blocked (email) values('rwillett+dr...@example.com')
> >
> > And this is the code that I have written that works but it looks poor to
> > my untutored eyes
> >
> > select CASE
> >  WHEN EXISTS (select 1 from blocked where email =
> > 'rwillett.dr...@example.com')
> >  THEN (select action from blocked where email =
> > 'rwillett.dr...@example.com')
> >  ELSE 'OK'
> >  END
> >
> >
> > In the Postfix query I'll replace
> >
> > email = 'rwillett.dr...@example.com'
> >
> > with email = '%s'
> >
> > so that the email address is passed in.
> >
> > My worry is that the query has two selects and 'feels' bad. It works but
> > feels dirty...
> >
> > Is there a better way under SQLite?
> >
> > Any suggestions welcomed.
> >
> > Rob
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] question about DB

[Paul Sanderson]

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 are some order of penalty in attach (2+)
> databases vs one big DB ?
>
> Three databases of 2.4Gb size ?  SQLite is designed to expect this.  Slow
> down for attaching compared with one big database is very very small.  No
> problem.
>
> Make sure you have indexes to help the WHERE and ON clauses.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This list is getting spammed again

[Paul Sanderson]

ahh disregard - it was there

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

On 11 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. different number in subject) to each user on the list and see
> > who is sending the spam based on the replies?
>
> My guess is that the spammer harvests our addresses from nabble, or from
> the Mailman archive.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This list is getting spammed again

[Paul Sanderson]

Prob not nabble as my message does not seem to have made it there yet,
didn't check mailman - I suspect that they are subscribed as spam is sent
within seconds.

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

On 11 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. different number in subject) to each user on the list and see
> > who is sending the spam based on the replies?
>
> My guess is that the spammer harvests our addresses from nabble, or from
> the Mailman archive.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This list is getting spammed again

[Paul Sanderson]

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://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 11 May 2018 at 10:12, Cecil Westerhof  wrote:

> 2018-05-10 1:09 GMT+02:00 Simon Slavin :
>
> >
> >
> > On 9 May 2018, at 9:37pm, Cecil Westerhof 
> wrote:
> >
> > > ​I am bitten by it also now. I posted a question and within two
> minutes I
> > > got a spam message​
> >
> > I got three or four of these, each one soon after I'd posted a message.
> > Then I got no more.  I didn't do anything to stop them and I have checked
> > my spam system to see if it stopped them, but the spam system didn't
> > receive any more.
> >
>
> ​I marked the sender as spammer. (It was from one sender.) And the messages
> are send to spam now. I got a few more. I'll watch if this triggers again,
> or that it is a 'smart' spammer that stops when you do not reply.
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 9 May 2018 at 21:31, Igor Tandetnik  wrote:

> On 5/9/2018 4:19 PM, Cecil Westerhof wrote:
>
>> I have a table where I use 'unused' to signify that a record is not yet
>> used. I want to know the number of unused records (but only if there are
>> unused records) and the total number of records.
>>
>
> Something like this perhaps:
>
> select count(*) Total, sum(totalUsed = 'unused') NotUsed from quotes;
>
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Handle BigInt

[Paul Sanderson]

Perhaps, but that is only part of the story, and all of that is hidden from
the user and is only relevant in terms of how the number is stored on disk.
You can define a column as int, smallint, largeint, bigint, etc and,
irrespective of which you use, SQLite will save the data to disk
transparently using the smallest on disk format possible for the value you
have saved.

For the record there is another possibility for the values 0 and 1 where
SQLite uses no storage at all, other than the type byte in the serial types
array.

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 May 2018 at 17:05, Gerry Snyder <mesmerizer...@gmail.com> wrote:

> From the docs:
>
> *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 Sanderson <
> sandersonforens...@gmail.com
> > wrote:
>
> > 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://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 May 2018 at 16:29, dmp <da...@dandymadeproductions.com> wrote:
> >
> > > Hello,
> > >
> > > Given a conversion from a database table that contains BigInt, long,
> > > field from PostgreSQL to a SQLite similar table.
> > >
> > > CREATE TABLE postgresqltypes (
> > >   data_type_id serial,
> > >   bigInt_type bigint)
> > >
> > > CREATE TABLE sqlitetypes (
> > >   data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
> > >   int_type INTEGER)
> > >
> > > How to store, for values outside range for Integer,
> > > String or Real?
> > >
> > > danap.
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Handle BigInt

[Paul Sanderson]

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://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 May 2018 at 16:29, dmp  wrote:

> Hello,
>
> Given a conversion from a database table that contains BigInt, long,
> field from PostgreSQL to a SQLite similar table.
>
> CREATE TABLE postgresqltypes (
>   data_type_id serial,
>   bigInt_type bigint)
>
> CREATE TABLE sqlitetypes (
>   data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
>   int_type INTEGER)
>
> How to store, for values outside range for Integer,
> String or Real?
>
> danap.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 work address for a fully functional demo licence

On 21 March 2018 at 15:30, Ron Watkins  wrote:

> I have a table which contains a datetime column:
>
>
>
> table|foo|foo|2|CREATE TABLE foo (
>
> dttmdatetimenot null
>
> i int not null
>
> )
>
>
>
> I want to select out the max(i) value for each day where there are multiple
> records per day.
>
>
>
> select date(dttm) dt,max(i) from foo group by dt order by 1;
>
>
>
> However, it’s returning only 1 row, with no date column shown. How can I
> get an actual “date” listed in the first column, and also get 1 row per
> “date” value.
>
>
>
> |5283598256
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Actually it is totally different Chris

>  I read that - but my point was more that some people seem to think that
an
> int primary key can be auto incrementing, it can't.

an INT primary key cannot be autoincrementing

An INTEGER primary key and an INTEGER primary key autoincrement work in
essentially the same way. i.e. if you insert a row and do not specifically
assign a value to the pk (i.e. you assign NULL) the value assigned will
usually be one more than last pk used.

if you have an INT primary key and add a new row with no value assigned to
the PK then null will be stored (all null values are treated as unique in
SQLite and so as far as the PK is concerned all rows are different).

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (id int primary key, data text);
sqlite> insert into test (data) values('row 1');
sqlite> insert into test (data) values('row 2');
sqlite> select id, data from test;
  |row 1
  |row 2

of course the rowid is still there hidden behind the scenes and you can
access it with

sqlite> select rowid, id, data from test;
1|  |row 1
2|  |row 2

but if you want to use the rowid as the PK then you should probably use an
INTEGER pk so it becomes an alias for the rowid in the first place.

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 20 March 2018 at 16:44, Chris Locke <sql...@chrisjlocke.co.uk> wrote:

> >  some people seem to think 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 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 auto incrementing, it can't.
> >
> >
> > SQLite version 3.18.0 2017-03-28 18:48:43
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> create table test (id integer primary key autoincrement);
> > sqlite> create table test2 (id int primary key autoincrement);
> > Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
> > sqlite>
> >
> > 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 20 March 2018 at 08:48, R Smith <ryansmit...@gmail.com> wrote:
> >
> > >
> > > On 2018/03/20 10:24 AM, Paul Sanderson wrote:
> > >
> > >> Autoincrement can ONLY be used with an integer primary key
> > >>
> > >
> > > I think Peter's shouting is more about the inability to distinguish via
> > > SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY,
> both
> > > of which are of course integer and can be auto-incrementing, but only
> one
> > > of which is an alias for rowid.
> > >
> > >
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

 I read that - but my point was more that some people seem to think that an
int primary key can be auto incrementing, it can't.


SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (id integer primary key autoincrement);
sqlite> create table test2 (id int primary key autoincrement);
Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
sqlite>

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 20 March 2018 at 08:48, R Smith <ryansmit...@gmail.com> wrote:

>
> On 2018/03/20 10:24 AM, Paul Sanderson wrote:
>
>> Autoincrement can ONLY be used with an integer primary key
>>
>
> I think Peter's shouting is more about the inability to distinguish via
> SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY, both
> of which are of course integer and can be auto-incrementing, but only one
> of which is an alias for rowid.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 SMALL PIECE OF SQL TO RELIABLY CHECK FOR INTEGER PRIMARY KEY
>
> YES I AM SHOUTING
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 16 January 2018 at 09:39, John G  wrote:

> I've not tried it, but this article from OSXdaily says you can get the
> command line (Terminal) in iOS.
>
> http://osxdaily.com/2018/01/08/get-terminal-app-ios-command-line/
>
> That probably does not solve the fork requirement, and I'm sure it is
> sandboxed.
>
> John G
>
>
>
> On 15 January 2018 at 15:00, Richard Hipp  wrote:
>
> > On 1/15/18, Shane Dev  wrote:
> > >
> > > Did the Apple engineers tell you why it is not possible to compile and
> > run
> > > the SQLite shell on iOS?
> > >
> >
> > You cannot get a command-line prompt on iOS, and the SQLite shell
> > requires a command-line prompt (like bash).  If i understand
> > correctly, iOS does not allow fork() as a security measure.
> >
> > The previous paragraph is not authoritative.  It is merely my
> > recollection.  I have not researched the issue.
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Paul Sanderson]

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

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 19 December 2017 at 16:15, Dinu  wrote:

> Hi sub sk79,
> I have so far from this thread the following suggestions:
> 1) Copy table -> TRUNCATE -> copy back; this doesn't work, what was
> supposed
> to be TRUNCATE semantics (DELETE FROM without WHERE) has the same
> performance as with WHERE.
> 2) Structure alterations; either drop table, or drop indexes : I am
> reluctant to do this; my evangelical instinct tells me hacking the
> semantics
> of life might lead to implosion of Earth :)
> 3) "Deleted" bit field - presumably the "soft delete" as you call it; I am
> analyzing this, but here the question is whether we include the bit in the
> indexes. If so, performing a heap of UPDATEs should be even more
> inefficient; if we don't include it in the index, the problem of the cost
> of
> filtering the row needs some analysis which I will probably do; the problem
> with this solution is that is has residual effects: we run some pretty
> complex queries against this table, with complicated joins and we already
> got some surprising execution plans that needed query rewriting. So with
> this "deleted" bit out of the index pool, we need to check various other
> queries to make sure they are still optimized to what we need.
>
> All this said and done, 3 hours to delete 15G of data seems atrocious even
> if you do it by standards resulted from generations of DOD and NSA
> inbreeding... so I'm still hopeful for some DB-related solution.
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] same code produces a different database file on different computers

[Paul Sanderson]

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-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 19 December 2017 at 12:32, Karl Forner  wrote:

> Hello,
>
> I encountered a weird behavior recently.
> The exact same code (executed from a docker container using the same image)
> produced a different database file on two computers, as verified by a MD5
> or sha256 hash of the two files.
> But using the* .sha3sum* of sqlite3.exe I could check that the contents of
> the two databases are indeed identical.
>
> To sum up, same content, but the files are somewhat different.
>
> Is this something expected or known ?
> If so, is there a way to prevent this ?
>
> Our use case  if that we track the sha256 hashes of the files, that could
> have been produced on different computers to know of the outputs are up to
> date. Until now, since ~ 4 years it has always seem to work.
>
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Paul Sanderson]

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 Toolkit for SQLite
email from a work address for a fully functional demo licence

On 18 December 2017 at 10:03, Dinu  wrote:

> Rowan Worth-2 wrote
> > I'm not sure what you're worried about? Dropping and recreating identical
> > indices within a transaction won't cause a visible structure change to
> > concurrent readers -- that's the point of a transaction.
>
> I honestly don't see how in any DB system the client process would not
> crash
> if the index it's running a curson on were to be removed. Even if SQLite
> were to pull this magic out of the hat, starving client processes for the
> lack of an index (a full scan query would probably take in excess of 30s)
> would quickly pile up the clients to the point where one would have to kill
> them anyway.
> So with this in mind, I'm really not looking for a barbaric fix to this,
> I'm
> more of tryng to understand the problem and find a viable, semantically
> stable solution (and maybe trigger some improvements in SQLite, if there's
> a
> system bug).
>
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
current test client 3.18.0

insert into blobs values(zeroblob(2147483647));

fails wih string or blob too big. Not had time to investigate :(



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 15 December 2017 at 16:30, x  wrote:

> Suppose I execute “attach :memory: as mem” and then create a table in mem
> that requires more space than the available RAM can hold what will happen?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime to bigint

[Paul Sanderson]

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
62135596800 is the difference in seconds between 1/1/1970 and 1/1/0001
and 1000 converts it to nanoseconds

HTH

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 8 December 2017 at 16:53, Tibor Balog  wrote:

> Hi there,
>
> I am very new to SQLite but like it sofar.
> Run into something couldn’t find an answer may someone can put me in the
> right direction..
>
> Have an application uses SQLite table with a column “Datum” defined as
> bigint.
> I have an identical table with column “Datum” as DateTime “-mm-dd”
>
> How can I convert this column -preferable in place- to bigint?
>
> Some more info:
>
> “2004-08-05” should be converted to “6322726080” .
>
> Thank You,
> in advence.
> (Tibor)
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 21 November 2017 at 16:43, Martin Raiber  wrote:

> On 21.11.2017 17:30 John McKown wrote:
> > On Tue, Nov 21, 2017 at 10:27 AM, Drago, William @ CSG - NARDA-MITEQ <
> > william.dr...@l3t.com> wrote:
> >
> >>> I really need to come up with an alternative to the mailing list.
> >>> Perhaps some kind of forum system.  Suggestions are welcomed.
> >>> --
> >>> D. Richard Hipp
> >>> d...@sqlite.org
> >> Please, not a forum. The email list is instant, dynamic, and
> convenient. I
> >> don't think checking into a forum to stay current with the brisk
> activity
> >> here is very practical or appealing.
> > ​I completely agree. The problem with a forum is mainly that it is not
> _a_
> > forum. It is a forum per list. Which means I spend way too much time
> > "polling" 8 to 10 web "forums" during the day just to see if anybody has
> > said anything of interest.
>
> I am using Discourse as community forum and I cannot really see any
> downside to that except for the increased server requirements.
> Individuals who want to use it like a mailing list still can do that
> (enable mailing list mode). They have a FAQ wrt. to cos/prons mailing
> list: https://meta.discourse.org/t/discourse-vs-email-mailing-lists/54298
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Energy consumption of SQLite queries

[Paul Sanderson]

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. Just look at process on a windows system via
the task manager or a linux system using top to get a very simplistic idea
of the different tasks that are using processor time - Sort by processor
usage and the list is always changing even when you are doing nothing. Add
in variable speed fans and processor throttling to manage temperature/power
consumption etc. and you have a mammoth task.

Good luck :)

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 21 November 2017 at 00:36, Simon Slavin  wrote:

> On 20 Nov 2017, at 10:54pm, Ali Dorri  wrote:
>
> > I am doing a research on the energy consumed by a query in SQLite. I
> have a
> > program which fills a database with blocks of data. Then, it attempts to
> > remove some data from the database. I don't know how to measure the
> energy
> > consumed from my host, i.e., my laptop which has both the SQLite and the
> > program, from the time I generated the query till the query is finished
> and
> > control returns back to my program.
>
> This is a hardware question, not anything to do with a particular piece of
> software.
>
> If you have a desktop computer, get one of those gadgets that you plug
> into the power socket and monitors how much power is passed to things that
> plug into them:
>
> 
>
> On a laptop, since the power is taken from an internal battery, and mains
> power is used to recharge it inconsistently, monitoring power usage from
> the mains is pointless.  See if the firmware provides a display or an API
> function which shows how much is going out.
>
> Then set up side-by-side comparisons, one with your computer doing those
> things in SQLite and one without.  The differences between the two power
> consumptions is how much power SQLite is using.  Unless you have really
> detailed power measurement, the results will be small and probably
> meaningless.
>
> Since you mention doing side-by-side comparisons with other databases,
> your setup should probably be comparing the same computer doing things in
> different DBMSs.  Maybe set up some procedure for doing something 10,000
> times and see how much power is used in total.
>
> Worth noting that power consumption from SQLite will be terribly
> inconsistent, based on what data is cached, how many database pages need to
> be accessed, and the state of the journal files.  This pales into
> insignificance, however, with the inconsistency of most other DBMSs, which
> perform far more internal caching and indexing.  You will get very
> different results from the same setup depending on how long the DBMS server
> has been running, not just on how long the computer has been turned on.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
email from a work address for a fully functional demo licence

On 21 November 2017 at 10:35, Dominique Devienne 
wrote:

> Just FYI. Not sure if something changed on the mailer's settings.
> Possibly/likely linked to GMail changing it's SPAM heuristics I guess. --DD
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Grouping and grabbing one item

[Paul Sanderson]

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
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 18 October 2017 at 19:03, jose isaias cabrera 
wrote:

>
> So simple!  Thanks, Darko.
>
> -Original Message- From: Darko Volaric
> Sent: Wednesday, October 18, 2017 1:57 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Grouping and grabbing one item
>
>
> 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;
>
>
> On Oct 18, 2017, at 7:23 PM, jose isaias cabrera 
>> wrote:
>>
>>
>> CREATE TABLE Tasks (
>> id INTEGER PRIMARY KEY,
>> Pid INTEGER,
>> bd TEXT,
>> ed TEXT,
>> task TEXT,
>> target TEXT,
>> amt REAL
>> );
>>
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (1,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (1,'2017-09-28','2017-10-01','DOC','es-ES',100);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (1,'2017-09-28','2017-10-01','DOC','it-IT',120);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (1,'2017-09-28','2017-10-01','DOC','fr-FR',110);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (1,'2017-09-28','2017-10-01','VAL','es-ES',70);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (1,'2017-09-28','2017-10-01','VAL','fr-FR',75);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (1,'2017-09-28','2017-10-01','VAL','it-IT',80);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (2,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (2,'2017-09-28','2017-10-01','DOC','es-ES',100);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (2,'2017-09-28','2017-10-01','DOC','it-IT',120);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (2,'2017-09-28','2017-10-01','DOC','fr-FR',110);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (2,'2017-09-28','2017-10-01','VAL','es-ES',70);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (2,'2017-09-28','2017-10-01','VAL','fr-FR',75);
>> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
>> (2,'2017-09-28','2017-10-01','VAL','it-IT',80);
>>
>> I know I can do,
>>
>> select max(ed),target, sum(amt) from Tasks where Pid=1 group by target
>> HAVING amt > 0;
>>
>> and get,
>>
>> 2017-10-01|es-ES|100.0
>> 2017-10-01|fr-FR|185.0
>> 2017-10-01|it-IT|200.0
>>
>> but, I would like to add the ed of the task='QUOTE' to the beginning of
>> the list.  So, the result would look like this,
>>
>> 2017-09-27|2017-10-01|es-ES|100.0
>> 2017-09-27|2017-10-01|fr-FR|185.0
>> 2017-09-27|2017-10-01|it-IT|200.0
>>
>> I know how to select it by itself,
>>
>> SELECT ed from Tasks where task = 'QUOTE' and Pid = 1;
>>
>> but I need to add it to the beginning of the list with a JOIN or
>> something. Any thoughts?  Thanks.
>>
>> josé
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Grouping and grabbing one item

[Paul Sanderson]

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: 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 18 October 2017 at 18:23, jose isaias cabrera 
wrote:

>
> CREATE TABLE Tasks (
>  id INTEGER PRIMARY KEY,
>  Pid INTEGER,
>  bd TEXT,
>  ed TEXT,
>  task TEXT,
>  target TEXT,
>  amt REAL
> );
>
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (1,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (1,'2017-09-28','2017-10-01','DOC','es-ES',100);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (1,'2017-09-28','2017-10-01','DOC','it-IT',120);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (1,'2017-09-28','2017-10-01','DOC','fr-FR',110);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (1,'2017-09-28','2017-10-01','VAL','es-ES',70);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (1,'2017-09-28','2017-10-01','VAL','fr-FR',75);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (1,'2017-09-28','2017-10-01','VAL','it-IT',80);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (2,'2017-09-27','2017-09-27','QUOTE','es-ES fr-FR it-IT',0);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (2,'2017-09-28','2017-10-01','DOC','es-ES',100);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (2,'2017-09-28','2017-10-01','DOC','it-IT',120);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (2,'2017-09-28','2017-10-01','DOC','fr-FR',110);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (2,'2017-09-28','2017-10-01','VAL','es-ES',70);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (2,'2017-09-28','2017-10-01','VAL','fr-FR',75);
> INSERT INTO Tasks (Pid,bd,ed,task,target,amt) VALUES
> (2,'2017-09-28','2017-10-01','VAL','it-IT',80);
>
> I know I can do,
>
> select max(ed),target, sum(amt) from Tasks where Pid=1 group by target
> HAVING amt > 0;
>
> and get,
>
> 2017-10-01|es-ES|100.0
> 2017-10-01|fr-FR|185.0
> 2017-10-01|it-IT|200.0
>
> but, I would like to add the ed of the task='QUOTE' to the beginning of
> the list.  So, the result would look like this,
>
> 2017-09-27|2017-10-01|es-ES|100.0
> 2017-09-27|2017-10-01|fr-FR|185.0
> 2017-09-27|2017-10-01|it-IT|200.0
>
> I know how to select it by itself,
>
> SELECT ed from Tasks where task = 'QUOTE' and Pid = 1;
>
> but I need to add it to the beginning of the list with a JOIN or
> something. Any thoughts?  Thanks.
>
> josé
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 calculations but would just require a conversion for display.





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 25 September 2017 at 09:12, David Wellman 
wrote:

> Hi,
>
>
>
> We're designing a new feature which will involve a lot of date/time
> calculations which we intend to do within the SQLite engine. As far as we
> can tell it has the functions that we need. Basically we'll be loading data
> into SQLite and performing analysis and calculations using SQL.
>
>
>
> The question has come up as to how we should store date/time values in our
> tables? Basically how should we define our date/time columns?
>
>
>
> For us an obvious choice is to store dates/times as REAL. We're working in
> a
> Windows environment and so in the application code we're dealing with MS
> serial date values.
>
>
>
> Looking at the 'date and tine functions' page
> (http://www.sqlite.org/lang_datefunc.html ) it says "All five date and
> time
> functions take a time string as an argument". So my initial reaction is to
> store dates and times as TEXT. I think this means that when passing such
> date/time values into the functions there is one less conversion to do.
>
>
>
> But then looking at some of the examples on that page I came across the
> following:
>
> Compute the date and time given a unix timestamp 1092941466.
>
> SELECT datetime(1092941466, 'unixepoch');
>
>
>
> In the sql syntax that I'm used to (which I thought was the same for
> SQLite)
> a series of digits like that shown above is treated as a numeric data value
> (and type), not text (i.e. a  "time string"). If that was meant to be
> string
> data (i.e. text) then I'd have expected:
>
>
>
> SELECT datetime('1092941466', 'unixepoch');
>
>
>
> So to clarify: Is our idea of storing date/time values as TEXT data a
> sensible one?
>
>
>
> We're essentially thinking of performance and there are almost certainly
> pros and cons to doing this.
>
> Pro: (I think) more efficient processing as the data is supplied to these
> functions as text which is what they're expecting/require - and therefore
> there is no additional conversion required.
>
> Con: Our full timestamps will be 19 bytes compared to a REAL which is only
> 8
> bytes. So they will require more storage/disk space which ultimately means
> more I/O to read the same number of rows and columns.
>
>
>
> I accept that from a performance perspective there may not be much in it,
> but I'd be interested in people's thoughts.
>
>
>
> In anticipation, many thanks.
>
>
>
> Cheers,
>
> Dave
>
>
>
>
>
>
>
> Ward Analytics Ltd - information in motion
>
> Tel: +44 (0) 118 9740191
>
> Fax: +44 (0) 118 9740192
>
> www:   http://www.ward-analytics.com
>
>
>
> Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
> United Kingdom, GU1 3SR
>
> Registered company number: 3917021 Registered in England and Wales.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
effectively be nil.

If on the otherhand the average record is 100 bytes you may well fit more
records into the page, conversely changing the page size to 64K would also
reduce the number of reads.

I suspect that biggest time savings may be gained by reducing disk I/O.

Better advice could possibly be given if we know the full table schema
including typical sizes for data in any fields/

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 10 August 2017 at 14:13, R Smith  wrote:

> On 2017/08/10 1:19 PM, x wrote:
>
>> Thanks for the replies. I’m not sure I agree with Gunter and Ryan though.
>> I’m thinking about this more from the gain in speed rather than saving
>> space.
>>
>> To clarify, I’m suggesting replacing a compound key (made up of several
>> integer cols) with an integer primary key (which sqlite will use rather
>> than the row id). I have done my homework on this so I’m familiar with
>> Gunter’s points regarding ‘between’ and ‘high end bits’ but will the
>> between on a single integer key not be faster than matching on the first m
>> fields of an n compound key? If an index is needed on any non-high bit col
>> an expression index would work just as fast for lookups (I suppose inserts
>> would be slower). The savings on space would contribute to the speed as
>> each disk read would contain more records.
>>
>
> Ok, if you require ALL the packed records all the time, and will always
> access it by the primary value (the first of the packed values) and is very
> very sure you won't ever need expanding the value range, then you might
> actually get a speed gain from it.
>
> Problem is, the gain will be minuscule, and the price is high. Lots of
> development time, loss of useful SQL aggregates and other functionality,
> possible future reworks... All of that for a very small speed gain?  If you
> are wanting that, why not simply use a custom structure and avoid SQLite
> completely? The speed gain will actually be significant then, and you're
> going to lose the SQL-ness of it anyway, so that shouldn't matter.
>
> A structured array mapped to a physical byte-stream will be several times
> faster than SQLite (or any other RDBMS for that matter).  SQL as supported
> by the average RDBMS is only really helpful when you are looking for
> SET-type relational data handling or very large data (and your use case is
> specifically not for large data). Most RDBMSes have great optimizations for
> speeding up resolving of relational-type questions and their every-day-use
> advantages are legion, they are however without exception NOT faster than -
> NOR intended to be faster than - simple byte/structured array handling.
>
> You might even find a synergy between using your own structured array
> together with an SQLite DB which only get accessed once you need more
> information than persists in the array itself - it's easy to make a pilot
> and test the speed gains. And please do that before investing the time to
> develop a fully fledged dual system.
>
>
>
>> Even forgetting about keys, if you packed say 8 columns into one int64
>> column would you not be saving a minimum of 7 bits?
>>
>
> No you won't, SQLite stores Integer much more efficiently. Unless you mean
> use ONLY the 64-bit index and not storing the values in separate fields in
> the DB at all, in which case yes, you will save a few bytes, possibly less
> than 7 though (I need to affirm the exact number, don't know off the
> top...).
>
> Cheers,
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Summarising (missing) values

[Paul Sanderson]

That simple - I was over thinking it

cheers

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional 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 if there is no previous row:
>
>   WITH ranges(first) AS (
> SELECT _id
> FROM messages
> WHERE _id - 1 NOT IN (SELECT _id FROM messages)
>   )
>
> The corresponding last row is the first row at or behind the start row
> that has no next row:
>
>   SELECT first,
>  (SELECT min(_id)
>   FROM messages
>   WHERE _id >= ranges.first
> AND _id + 1 NOT IN (SELECT _id FROM messages)
>  ) AS last
>   FROM ranges;
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Summarising (missing) values

[Paul Sanderson]

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
FROM cte
WHERE cte.x < (SELECT Max(messages._id) FROM messages))
SELECT *
FROM cte
WHERE cte.x NOT IN (SELECT messages._id FROM messages)



Or to get the upper and lower bounds of the records that are present I can
use a couple of queries such as


SELECT messages._id
FROM messages
WHERE messages._id - 1 NOT IN (SELECT messages._id FROM messages)

which gives me (on my test values)

334
1077
1701
2385
2390
2393

and


SELECT messages._id
FROM messages
WHERE messages._id + 1 NOT IN (SELECT messages._id FROM messages)

334
1297
1701
2386
2390
3336





What I would like is a single query that summarises the values that are
present in (or missing from) a table. e.g.


334, 344
1077, 1297
1701, 1701
2385, 2386
2390, 2390
2393, 3336

Different approaches to this would be of interest
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite problem with opening database

[Paul Sanderson]

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 2017, at 1:41pm, Gregor Pavuna  wrote:
>
> > As it seems there's some sort of server problem(Windows 2012 server). My
> guess is server is caching 64bit files and serving them to 32bit operating
> systems. I tested on my test server with laptop (32bit windows 7) and it
> works fine. Than i went to client and connected laptop there and it didn't
> work with their files.
>
> You are keeping your application on a server ?  Or a library ?  Does the
> problem go away if you keep your application and libraries on the client
> computer instead ?
>
> > I also googled that out, but couldn't find anything on that topic. Any
> suggestions?
>
> Whatever the problem, it seems that it’s related to the Windows
> application loading system, not SQLite.  So you might find another forum is
> able to help you better than this one.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] syntax error near AS

[Paul Sanderson]

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
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 6 July 2017 at 06:03, Keith Medcalf  wrote:

>
> Do you know of any implementation of SQL that accepts an AS clause for the
> updated table?  I don't think any do.
>
> Some versions have a FROM extension and you CAN specify an alias for the
> updated table in that clause, however, as far as I know the update table
> cannot be aliased and the "set  = ..." the  must always be
> a column in the updated table and while you may be allowed to "adorn" it in
> some implementations, any adornments are ignored (or trigger an error
> message if they are not the same as the updated table).
>
> --
> ˙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 John McMahon
> > Sent: Wednesday, 5 July, 2017 21:17
> > To: SQLite Users
> > Subject: [sqlite] syntax error near AS
> >
> > Hi
> >
> > Wondering if someone else can spot the syntax error in the following
> > statement. "locns" is an attached database. There are four "AS" terms in
> > the statement, they all alias tables.
> >
> > Ok, found it. It seems that an alias for an "UPDATE" table name is not
> > permitted. Is there a particular reason for this?
> > I would think it a convenience especially when using long table names
> > and attached databases.
> >
> > John
> >
> > sqlite> UPDATE locns.xxx_last_delivery AS tgt
> >... >  SET
> >... > tgt.del_date =  (
> >... >  SELECT src.last_del_d
> >... >  FROM   main.updates AS src
> >... >  WHERE  src.custnum = tgt.custnum),
> >... > tgt.del_qty = (
> >... >  SELECT src.last_del_q
> >... >  FROM   main.updates AS src
> >... >  WHERE  src.custnum = tgt.custnum)
> >... >  WHERE
> >... > tgt.custnum  = (
> >... >  SELECT src.custnum
> >... >  FROM   main.updates AS src
> >... >  WHERE  src.last_del_d IS NOT NULL
> >... >  ANDsrc.last_del_d > tgt.del_date)
> >... >  ;
> > Error: near "AS": syntax error
> >
> > --
> > Regards
> > John McMahon
> >li...@jspect.fastmail.fm
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FOREING KEY constraint

[Paul Sanderson]

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

On 3 July 2017 at 15:05, Keith Medcalf  wrote:

>
> You can get foreign key constraints with a pragma.
> Check constraints need to parse the SQL.
>
>
> --
> ˙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 Clemens Ladisch
> > Sent: Monday, 3 July, 2017 08:00
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: Re: [sqlite] FOREING KEY constraint
> >
> > J. King wrote:
> > > The sqlite_master table should have this information.
> > >
> > > SELECT count() FROM sqlite_master WHERE name IS your_constraint_name
> AND
> > tbl_name IS your_table_name;
> >
> > Constraints do not have separate entries in the sqlite_master table.
> > And there is no other mechanism to get this information without parsing
> > the SQL.
> >
> >
> > Regards,
> > Clemens
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Document typo?

[Paul Sanderson]

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 number
  pagetype   STRING, -- 'internal', 'leaf' or 'overflow'
  ncell  INTEGER,-- Cells on page (0 for overflow)
  payloadINTEGER,-- Bytes of payload on this page
  unused INTEGER,-- Bytes of unused space on this page
  mx_payload INTEGER,-- Largest payload size of all cells on this page
  pgoffset   INTEGER,-- Offset of page in file
  pgsize INTEGER,-- Size of the page
  schema TEXT HIDDEN -- The database being analyzed
);

the sqlite3.c source shows it correctly with path defined as TEXT

#define VTAB_SCHEMA \
  "CREATE TABLE xx( "   \
  "  name   TEXT, /* Name of table or index */" \
  "  path   TEXT, /* Path to page from root */" \
  "  pageno INTEGER,  /* Page number */"\
  "  pagetype   TEXT, /* 'internal', 'leaf' or 'overflow' */"   \
  "  ncell  INTEGER,  /* Cells on page (0 for overflow) */" \
  "  payloadINTEGER,  /* Bytes of payload on this page */"  \
  "  unused INTEGER,  /* Bytes of unused space on this page */" \
  "  mx_payload INTEGER,  /* Largest payload size of all cells */"  \
  "  pgoffset   INTEGER,  /* Offset of page in file */" \
  "  pgsize INTEGER,  /* Size of the page */"   \
  "  schema TEXT HIDDEN   /* Database schema being analyzed */" \
  ");"





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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Providing incrementing column to query

[Paul Sanderson]

Actually I just need a unique number - but sorted in code now.

Thank You

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

On 24 June 2017 at 15:57, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> Of course, if the traversal order is different than the row return order
> then you will not get ascending logical row numbers unless you do something
> like:
>
> select logicalrow() as SequenceNumber,
>t.*
>   from (...query ...) as t;
>
> If you need logical row numbers it is much better (and far more efficient)
> to assign them in your program as the results are returned.
>
> Out of curiosity, why do you need logical result 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 Sanderson
> > Sent: Saturday, 24 June, 2017 06:18
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Providing incrementing column to query
> >
> > Hmm thanks Clemens
> >
> > Have written an extension to do it - some of my tables are very big and
> > feel that the extension might be a better route.
> >
> > 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 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
> > > column,
> > > > some of these queries involve without rowid tables.
> > > >
> > > > Is there a way of doing this with a SQL query?
> > >
> > > First, define a sort order, and ensure that it does not have
> duplicates.
> > > Then use a correlated subquery to count how many rows would come before
> > > the current one in that order:
> > >
> > >   SELECT (SELECT count(*)
> > >   FROM MyTable AS T2
> > >   WHERE T2.name <= MyTable.Name
> > >  ) AS row_number,
> > >  name,
> > >  age
> > >   FROM MyTable
> > >   ORDER BY name;
> > >
> > > It would be a better idea to count returned rows in your program.
> > >
> > >
> > > Regards,
> > > Clemens
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Providing incrementing column to query

[Paul Sanderson]

Hmm thanks Clemens

Have written an extension to do it - some of my tables are very big and
feel that the extension might be a better route.

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 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
> column,
> > some of these queries involve without rowid tables.
> >
> > Is there a way of doing this with a SQL query?
>
> First, define a sort order, and ensure that it does not have duplicates.
> Then use a correlated subquery to count how many rows would come before
> the current one in that order:
>
>   SELECT (SELECT count(*)
>   FROM MyTable AS T2
>   WHERE T2.name <= MyTable.Name
>  ) AS row_number,
>  name,
>  age
>   FROM MyTable
>   ORDER BY name;
>
> It would be a better idea to count returned rows in your program.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Providing incrementing column to query

[Paul Sanderson]

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

Denis, 41

 

I want to return a result set

 

1, Steve, 34

2, Eric, 27

3, Denis, 41

 

 

Is there a way of doing this with a SQL query?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] imposter tables

[Paul Sanderson]

Thanks Richard

I understand how it works, quite simple really, and knowing they are used
by the RBU extension explains why.

I am working on a book and I just wanted to understand how/where it could
be used where a view, created on the same columns in an index, couldn't be.



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 15 June 2017 at 12:58, R Smith <rsm...@rsweb.co.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.
>>
>
> I'm thinking the easiest way to describe it is perhaps in programming
> terms -
> a view is like a function that computes and produces a result that may or
> may not be a simple reflection of an internal variable,
> and
> an imposter table is rather more like a variable that lives at the same
> memory address as another data structure so that querying it queries the
> underlying structure direct (this part is also achievable by a view), but
> changes to it also change in the underlying data structure (very unlike a
> view).
>
> This is great if you are looking for a way to update an index
> independently from its data, but is also, as has been pointed out numerous
> times, quite dangerous - but then, breaking it is mostly fixable by a
> simple REINDEX.
>
> Not sure if you can install such an imposter table on a corrupt database
> file, moreso than a View anyway, but that actually might have some utility
> towards retrieving or fixing (or at least gaining a better understanding
> of) a corrupted data table / index, which I think Paul might be interested
> in.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] imposter tables

[Paul Sanderson]

Can you create an imposter table on a view. A view has no associated b-tree
so I would think not!

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 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 that you know the imposter table is showing
> > you exactly what is in the index, where the view is my interpretation of
> > the SQL needed to show what is in the index. Is this the main benefit? or
> > am I missing something?
>
> Hmm.  If you create a view with calculated columns, and then create an
> impostor table on that view, do you get a virtual table with pre-calculated
> columns ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] imposter tables

[Paul Sanderson]

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 Skype

CREATE INDEX chat_idx_chat_room_name_service_name ON chat(room_name,
service_name)

.imposter chat_idx_chat_room_name_service_name imptable

can be simulated with a view

CREATE TEMP VIEW impview AS select room_name, service_name, _rowid_ FROM
chat

Querying either impview or imptable should, as far as I can see, produce
the same results.

I can see that the imposter table will be faster as it links directly to
the b-tree, but with the risk of corrupting the index as described on
https://sqlite.org/imposter.html.

The only benefit I can see is that you know the imposter table is showing
you exactly what is in the index, where the view is my interpretation of
the SQL needed to show what is in the index. Is this the main benefit? or
am I missing something?

Are there instances where a view created as I have done above cannot
simulate an imposter table?



Cheers






Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786 <+44%201326%20572786>
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 +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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

I think you guys might be missing my point :)

We know the integer primary key is an alias for the rowid - but as that
table is created we also get a completely redundant index, a second b-tree,
which is essentially a one to one mapping of rowids 1=1, 2=2, 3=3 etc.

The index takes up space that is not required and when updating the table
and will also need to be updated when an insert etc takes places using up
cycles and disk I/O.

Yes Unique is redundant in the create statement, but it would be a small
optimisation, unless I am missing something, for SQLite to detect this and
not create the autoindex to start with.

Just a thought



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 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:08 PM
> > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> > Subject: Re: [sqlite] auntondex with unique and integer primary key
>
> > I just thought it might be an area for optimisation as a redundant index
> is
> > built.
>
> According to the docs, it's only a pointer and not a duplicate when
> specified exactly as 'INTEGER PRIMARY KEY'. The semantics change
> when you add AUTOINCREMENT to it.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Thanks Simon

I am aware that a PK must be unique :)

It's not me that's declaring it as unique - I get to look at thousands of
databases that other people create and it is these where I have noticed it
(Chrome and Skype are two).

I just thought it might be an area for optimisation as a redundant index is
built.




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 19 May 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?
> >
> > 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 (3);
> >
> > The table is created and populated as expected, but an
> > sqlite_autoindex_test_1 is also created with content that mirrors exactly
> > the rowid/id.
> >
> > Is the autoindex redundant and is this an opportunity for optimisation?
>
> There’s no point in declaring the primary key as unique.  A primary key
> has to be unique.  SQLite will enforce uniqueness whether you tell it to or
> not.
>
> sqlite> Create table test1(id integer primary key);
> sqlite> Create table test2(id integer unique primary key);
> sqlite> Create table test3(id integer primary key unique);
> sqlite> PRAGMA index_list(test1);
> sqlite> PRAGMA index_list(test2);
> 0|sqlite_autoindex_test2_1|1|u|0
> sqlite> PRAGMA index_list(test3);
> 0|sqlite_autoindex_test3_1|1|u|0
> sqlite>
>
> It appears that SQLite does not notice that you have declared a primary
> key as unique.  It’s really this that’s causing the problem.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] auntondex with unique and integer primary key

[Paul Sanderson]

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 (3);

The table is created and populated as expected, but an
sqlite_autoindex_test_1 is also created with content that mirrors exactly
the rowid/id.

Is the autoindex redundant and is this an opportunity for optimisation?


Using 3.18.0

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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT NULL integer primary key

[Paul Sanderson]

Ahh being dull and in a hurry
thanks


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo 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 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
> > constraint be obeyed?
>
> Hello Paul,
>
> The constraint is obeyed, since there is no NULL values in the database.
>
> To put it in another way: constraints are properties of the *database
> content*, not of the *operations* on content. They're static, not dynamic.
>
> That's why constraints can be checked with PRAGMA
> schema.foreign_key_check, which tells if the current state of the database
> content is valid.
>
> That's also why the insert statement above succeeds, as long as the value
> that is eventually inserted in the database is NOT NULL.
>
> Gwendal Roué
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] NOT NULL integer primary key

[Paul Sanderson]

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
constraint be obeyed?


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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 SQLite
email from a work address for a fully functional demo licence

On 12 April 2017 at 14:08, Ron Barnes  wrote:

> Hello Ryan,
>
> That Code below worked as you said it should.  Awesome! And Thank you!
>
> I now have the days difference for each row.
>
> I have one other question if I may pose it to you, how do I count the
> number of rows, less than a day, or a week or a year and so forth?
>
> I tried this code and a few variants of it but I keep getting errors when
> trying to execute.
>
> Would you examine my code for errors?
>
> SELECTcategory, COUNT(*) AS Expr1
> FROM
>
> ((SELECT  replace(substr(VI_Creation_Date, 1, 10), '/','-')||' 12:00:00'
> AS ISO_Date, julianday('Now') - julianday(replace(substr(VI_Creation_Date,1,
> 10), '/','-')||' 12:00:00') AS DaysSince)
>
> WHEN DaysSince < 2 THEN 'Under 1 Day'
> WHEN DaysSince < 8 THEN 'Under 1 Week'
> WHEN DaysSince < 32 THEN 'Under 1 Month'
> WHEN DaysSince < 366 THEN 'Under 1 Year'
> WHEN DaysSince < 366 THEN 'Under 1 Year'
> WHEN DaysSince < 731 THEN 'Under 2 Year'
> WHEN DaysSince < 1826 THEN 'Under 5 Years'
> WHEN DaysSince < 3651 THEN 'Under 10 Years'
> ELSE 'Over 10 Years' END)  AS category
> FROM   Volume_Information) derivedtbl_1
> GROUP BY category
>
> Thanks,
> -Ron
>
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of R Smith
> Sent: Wednesday, April 12, 2017 8:32 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> question
>
>
> On 2017/04/12 2:13 PM, Ron Barnes wrote:
> > Hi Jim,
> >
> > I ran an overnight job and converted 300+ million dates to the ISO 8601
> format.
> >
> > Here are examples of the new dates.
> >
> >   2017/04/10 07:24:15 PM
> >   2017/03/07 08:08:58 AM
> >   2016/11/06 12:35:15 PM
> >
> > Since this should be easier how would you go about determining the
> Day(s) Difference from the current date?
>
> This is much friendlier. Do you care about the time? If not the conversion
> is VERY easy:
>
> SELECT  replace(substr(VI_Creation_Date, 1, 10), '/','-')||' 12:00:00'
> AS ISO_Date,
>  julianday('Now') - julianday(replace(substr(VI_Creation_Date,
> 1, 10), '/','-')||' 12:00:00') AS DaysSince
>FROM Volume_Information
>
> Cheers!
> Ryan
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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, '/',
'-'), 1, 10)) < 1 THEN 'Under 1 day'
WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 8 THEN 'Under 1 week'
WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 32 THEN 'Under 1 month'
WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 365 THEN 'Under 1 year'
END AS category
FROM dateplay

there may well be a neater way of doing it :)


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 12 April 2017 at 14:08, Ron Barnes  wrote:

> Hello Ryan,
>
> That Code below worked as you said it should.  Awesome! And Thank you!
>
> I now have the days difference for each row.
>
> I have one other question if I may pose it to you, how do I count the
> number of rows, less than a day, or a week or a year and so forth?
>
> I tried this code and a few variants of it but I keep getting errors when
> trying to execute.
>
> Would you examine my code for errors?
>
> SELECTcategory, COUNT(*) AS Expr1
> FROM
>
> ((SELECT  replace(substr(VI_Creation_Date, 1, 10), '/','-')||' 12:00:00'
> AS ISO_Date, julianday('Now') - julianday(replace(substr(VI_Creation_Date,1,
> 10), '/','-')||' 12:00:00') AS DaysSince)
>
> WHEN DaysSince < 2 THEN 'Under 1 Day'
> WHEN DaysSince < 8 THEN 'Under 1 Week'
> WHEN DaysSince < 32 THEN 'Under 1 Month'
> WHEN DaysSince < 366 THEN 'Under 1 Year'
> WHEN DaysSince < 366 THEN 'Under 1 Year'
> WHEN DaysSince < 731 THEN 'Under 2 Year'
> WHEN DaysSince < 1826 THEN 'Under 5 Years'
> WHEN DaysSince < 3651 THEN 'Under 10 Years'
> ELSE 'Over 10 Years' END)  AS category
> FROM   Volume_Information) derivedtbl_1
> GROUP BY category
>
> Thanks,
> -Ron
>
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of R Smith
> Sent: Wednesday, April 12, 2017 8:32 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> question
>
>
> On 2017/04/12 2:13 PM, Ron Barnes wrote:
> > Hi Jim,
> >
> > I ran an overnight job and converted 300+ million dates to the ISO 8601
> format.
> >
> > Here are examples of the new dates.
> >
> >   2017/04/10 07:24:15 PM
> >   2017/03/07 08:08:58 AM
> >   2016/11/06 12:35:15 PM
> >
> > Since this should be easier how would you go about determining the
> Day(s) Difference from the current date?
>
> This is much friendlier. Do you care about the time? If not the conversion
> is VERY easy:
>
> SELECT  replace(substr(VI_Creation_Date, 1, 10), '/','-')||' 12:00:00'
> AS ISO_Date,
>  julianday('Now') - julianday(replace(substr(VI_Creation_Date,
> 1, 10), '/','-')||' 12:00:00') AS DaysSince
>FROM Volume_Information
>
> Cheers!
> Ryan
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Oops hit send too quickly

the replace function replaces / with - in your date string to make the ISO
8601 and substr just makes sure we use the date portion only.

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 12 April 2017 at 13:37, Paul Sanderson <sandersonforens...@gmail.com>
wrote:

> 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
> dateplay
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786 <+44%201326%20572786>
> 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 12 April 2017 at 13:13, Ron Barnes <rbar...@njdevils.net> wrote:
>
>> Hi Jim,
>>
>> I ran an overnight job and converted 300+ million dates to the ISO 8601
>> format.
>>
>> Here are examples of the new dates.
>>
>> 2017/04/10 07:24:15 PM
>> 2017/03/07 08:08:58 AM
>> 2016/11/06 12:35:15 PM
>>
>> Since this should be easier how would you go about determining the Day(s)
>> Difference from the current date?
>>
>> Thanks in advance,
>>
>> -Ron
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Jim Callahan
>> Sent: Tuesday, April 11, 2017 11:22 PM
>> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
>> question
>>
>> This code:
>>
>> SELECT ( substr('02/13/2016',7,4) || '-'
>>   || substr('02/13/2016',1,2) || '-'
>>   || substr('02/13/2016',4,2) ) ;
>>
>> yields
>>
>> 2016-02-13
>>
>> The above code, is dependent on fixed length strings (the leading zero)
>> in other words '02/13/2016' and not '2/13/2016'.
>>
>> If you do not have fixed length date strings,  you would probably have to
>> use globs or regular expressions.
>>
>> *glob(X,Y)*
>>
>> The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note
>> that the X and Y arguments are reversed in the glob() function relative to
>> the infix GLOB <https://sqlite.org/lang_expr.html#glob> operator.
>>
>> https://sqlite.org/lang_corefunc.html#glob
>>
>>
>> The REGEXP operator is a special syntax for the regexp() user function. No
>> regexp() user function is defined by default and so use of the REGEXP
>> operator will normally result in an error message. If an
>> application-defined SQL function <https://sqlite.org/c3ref/crea
>> te_function.html> named "regexp"
>> is added at run-time, then the "*X* REGEXP *Y*" operator will be
>> implemented as a call to "regexp(*Y*,*X*)".
>>
>> https://sqlite.org/lang_expr.html
>>
>>
>> Type of regular expression needed:
>> https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-15e2
>> -4e2c-8389-1266f496e4b2/regular-expression-to-get-date-
>> format-from-string?forum=csharplanguage
>>
>> ​Jim Callahan
>>
>> On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes <rbar...@njdevils.net>
>> wrote:
>>
>> > Hi Jim,
>> >
>> > I could alter the program that populates the Date/Time Column to the
>> > format you specify.  I'm trying real hard not to as that program has
>> > been in use for many years and it would be a significant undertaking
>> > to convert the program then convert the existing data.  Not saying I
>> > won't do it as I'm at that point, just wondering if it's possible to
>> avoid that route.
>> >
>> > If I converted the date/time field, would it be easier to create counts?
>> >
>> > If you could, would you be able to offer a sample Select statement I
>> > can alter to fit my needs?
>> >
>> > Thank you very much for the reply!
>> >
>> > Side note, I'll be visiting Disney in July!
>> >
>> > Regards,
>> >
>> > -Ron
>> >

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

[Paul Sanderson]

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
dateplay

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 12 April 2017 at 13:13, Ron Barnes  wrote:

> Hi Jim,
>
> I ran an overnight job and converted 300+ million dates to the ISO 8601
> format.
>
> Here are examples of the new dates.
>
> 2017/04/10 07:24:15 PM
> 2017/03/07 08:08:58 AM
> 2016/11/06 12:35:15 PM
>
> Since this should be easier how would you go about determining the Day(s)
> Difference from the current date?
>
> Thanks in advance,
>
> -Ron
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jim Callahan
> Sent: Tuesday, April 11, 2017 11:22 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> question
>
> This code:
>
> SELECT ( substr('02/13/2016',7,4) || '-'
>   || substr('02/13/2016',1,2) || '-'
>   || substr('02/13/2016',4,2) ) ;
>
> yields
>
> 2016-02-13
>
> The above code, is dependent on fixed length strings (the leading zero) in
> other words '02/13/2016' and not '2/13/2016'.
>
> If you do not have fixed length date strings,  you would probably have to
> use globs or regular expressions.
>
> *glob(X,Y)*
>
> The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note
> that the X and Y arguments are reversed in the glob() function relative to
> the infix GLOB  operator.
>
> https://sqlite.org/lang_corefunc.html#glob
>
>
> The REGEXP operator is a special syntax for the regexp() user function. No
> regexp() user function is defined by default and so use of the REGEXP
> operator will normally result in an error message. If an
> application-defined SQL function  create_function.html> named "regexp"
> is added at run-time, then the "*X* REGEXP *Y*" operator will be
> implemented as a call to "regexp(*Y*,*X*)".
>
> https://sqlite.org/lang_expr.html
>
>
> Type of regular expression needed:
> https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-
> 15e2-4e2c-8389-1266f496e4b2/regular-expression-to-get-
> date-format-from-string?forum=csharplanguage
>
> ​Jim Callahan
>
> On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes  wrote:
>
> > Hi Jim,
> >
> > I could alter the program that populates the Date/Time Column to the
> > format you specify.  I'm trying real hard not to as that program has
> > been in use for many years and it would be a significant undertaking
> > to convert the program then convert the existing data.  Not saying I
> > won't do it as I'm at that point, just wondering if it's possible to
> avoid that route.
> >
> > If I converted the date/time field, would it be easier to create counts?
> >
> > If you could, would you be able to offer a sample Select statement I
> > can alter to fit my needs?
> >
> > Thank you very much for the reply!
> >
> > Side note, I'll be visiting Disney in July!
> >
> > Regards,
> >
> > -Ron
> >
> > -Original Message-
> > From: sqlite-users
> > [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Jim Callahan
> > Sent: Tuesday, April 11, 2017 9:15 PM
> > To: SQLite mailing list 
> > Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> > question
> >
> > Can you convert the dates to ISO 8601 date time format?
> > https://en.wikipedia.org/wiki/ISO_8601
> >
> > -MM-DD hh:mm:ss
> >
> > ISO date strings (when zero filled) are sortable which necessarily
> > includes comparable (Java speak).
> > By "zero filled" I mean for March you have "03" and not just "3".
> >
> > Then if you could generate/populate the boundary values in ISO format;
> > the comparisons would be straightforward and you could avoid the
> > julian date conversion.
> >
> > Another disadvantage of Julian dates are the different base years used
> > by applications including Unix, MS Access, MS Excel for Windows and MS
> > Excel for MacIntosh. Each application is internally consistent, but
> > the minute you exchange data between applications...
> > https://support.microsoft.com/en-us/help/214330/differences-
> > between-the-1900-and-the-1904-date-system-in-excel
> >
> > Your specification actually requires day counts; so you may need
> > Julian dates after all.
> >
> > Jim Callahan
> > Orlando, FL
> >
> >
> >
> > On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes 
> wrote:
> >
> 

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

[Paul Sanderson]

oops

you need to select the string length from the column width

select substring('', 1,  16 - length(printf("%2.f",
price))) || printf("%2.f", price) from prices


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 4 April 2017 at 15:07, Paul Sanderson <sandersonforens...@gmail.com>
wrote:

> Oops would need to subtract the string length from the column width you
> want - but hopefully you get the idea :)
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786 <+44%201326%20572786>
> 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:05, Paul Sanderson <sandersonforens...@gmail.com>
> wrote:
>
>> 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 <+44%201326%20572786>
>> http://sandersonforensics.com/forum/content.php?195-SQLite-F
>> orensic-Toolkit -Forensic Toolkit for SQLite
>> email from a work address for a fully functional demo licence
>>
>> On 4 April 2017 at 14:47, Hans M. van der Meer <drsme...@gmail.com>
>> wrote:
>>
>>> Simon, thanks.
>>> Now at last, I know how to continue.
>>>
>>> 2017-04-04 15:03 GMT+02:00 Simon Slavin <slav...@bigfraud.org>:
>>>
>>> >
>>> > On 4 Apr 2017, at 11:25am, Hans M. van der Meer <drsme...@gmail.com>
>>> > wrote:
>>> >
>>> > > I am building a simple bookkeeping.
>>> > > With PHP and SQLite i now have tables and views with columns for
>>> values
>>> > and
>>> > > prices: not nicely aligned because decimal values are aligned
>>> different
>>> > > from values that are interpreted as integers.
>>> >
>>> > Numeric values stored in a SQLite database should be stored as numbers.
>>> > This allows you to do calculations on them.  They should not have
>>> alignment
>>> > because they should not be text.
>>> >
>>> > > I like to create reports in which the column of prices and
>>> > > *values are aligned to the right and all figures with two decimals
>>> behind
>>> > > the decimal point.*
>>> > > I can not find a solution, so the question is how to achieve this
>>> >
>>> > SQLite is a database system.  It is used for storing and retrieving
>>> > information, not formatting it for people.  When presenting your
>>> numbers to
>>> > people, do your formatting in PHP using sprintf() or vprintf().
>>> >
>>> > http://php.net/manual/en/function.sprintf.php
>>> > http://php.net/manual/en/function.vprintf.php
>>> >
>>> > See example #5 for sprintf() for "padding" which a word relating to
>>> > alignment.
>>> >
>>> > Simon.
>>> > ___
>>> > sqlite-users mailing list
>>> > sqlite-users@mailinglists.sqlite.org
>>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> >
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
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 14:47, Hans M. van der Meer  wrote:

> Simon, thanks.
> Now at last, I know how to continue.
>
> 2017-04-04 15:03 GMT+02:00 Simon Slavin :
>
> >
> > On 4 Apr 2017, at 11:25am, Hans M. van der Meer 
> > wrote:
> >
> > > I am building a simple bookkeeping.
> > > With PHP and SQLite i now have tables and views with columns for values
> > and
> > > prices: not nicely aligned because decimal values are aligned different
> > > from values that are interpreted as integers.
> >
> > Numeric values stored in a SQLite database should be stored as numbers.
> > This allows you to do calculations on them.  They should not have
> alignment
> > because they should not be text.
> >
> > > I like to create reports in which the column of prices and
> > > *values are aligned to the right and all figures with two decimals
> behind
> > > the decimal point.*
> > > I can not find a solution, so the question is how to achieve this
> >
> > SQLite is a database system.  It is used for storing and retrieving
> > information, not formatting it for people.  When presenting your numbers
> to
> > people, do your formatting in PHP using sprintf() or vprintf().
> >
> > http://php.net/manual/en/function.sprintf.php
> > http://php.net/manual/en/function.vprintf.php
> >
> > See example #5 for sprintf() for "padding" which a word relating to
> > alignment.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB sizes beyond 2GB?

[Paul Sanderson]

I was a bit/very dull, schoolboy error :( re 32-bit - long week and it's
only Tuesday :)

Re: storing the length in the blob itself this would affect parsing the
serial types where, as now, you can determine the record length by looking
at the serial types and 'skip' through them to load a specific column. If
the length is stored in the record itself then reading past a blob means
that the first part of the blob itself needs to be read. Whether this would
have any significant impact on speed for parsing serial types in general I
don't know.





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 28 March 2017 at 12:33, Dominique Devienne <ddevie...@gmail.com> 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 (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 sum of 3 + 4 above plus all
> of
> > the following data forming the record. So as things stand you can't store
> > any record where the sum of the bytes in the serial types array and the
> > actual data that follows is greater than MAXVARINT because the total
> length
> > must be stored in 1. (MAXVARINT is actually max positive varint - see
> > below).
> >
>
> Good point. But still, MAXVARINT is 64-bit (see below) not 32-bit.
>
> The record format makes extensive use of the variable-length integer or
> > varint representation of 64-bit signed integers defined above.
> >
>
>
> > If you want to use one of the reserved serial types to store a blob of
> 6GB
> > then the serial type itself must be capable of storing the size of the
> > blob. Currently, a blob has *any* serial type of >= 12 and even, so the
> > maximum size for a blob is (MAXVARINT-12)/2 i.e. *any* even serial type
> >=
> > 12 and a text serial type is any odd serial type >= 13. All of the
> > remaining utilised serial types (i.e. those <= 9) refer to fixed length
> > data (ints and a 64 bit real).
> >
>
> I understand that. That's why I put the length in the "old style" blob
> value itself.
> But again, the varint encodes a 64-bit signed integer, and the "new style"
> blob could
> be assumed if the blob length exceed 2GiB (or 4 GiB), not even resorting to
> the
> two reserved serial types.
>
>
> > The remaining 2 serial types (remember these are just two bits from a
> > 64-bit serial type, each serial type is not a separate varint in its own
> > right) could be used to signify something like a 128-bit integer or some
> > other fixed-length data type, but, 1 bit by definition cannot store an
> > arbitrary length value.
> >
>
> I understand that (see above). But using the level of indirection of
> storing
> in the record only the meta-data of the blob, e.g. its full length, its
> in-record
> length (in case using 10, or 11 serial type, which cannot encode the length
> like the traditional text and blob serial types), and the ordered list of
> blob
> pages to read the blob from, seems completely possible.
>
>
> > I guess that the change Richard mentions (to up to 4GB) would be by
> > treating the varints as unsigned integers, rather than signed as they
> > currently are. This could be done (as far as I can see) for all varints
> > other than the rowid without affecting existing DBs.
> >
>
> That would be an implementation limitation though, not a file format
> limitation.
>
> Again, I'm probably naive here, but I still don't clearly see the file
> format limitation,
> and that's what I'm trying to understand. I completely accept this would be
> a lot of
> work and that the incentive for Richard to do it is rather low, to
> extremely low, although
> of course that does bum me out, I have to admit :), but really
> understanding the
> limitation I'm not seeing now is what I'm after here. Thanks, --DD
>
> PS: The alternate scheme of assuming new-style blob for length > 4 GiB,
> which is more backward-compatible, could be further refined via a pragma to
> put it lower, make the DB incompatible with older SQLite versions, but no
> more
> than the many other opt-in features old versions don't support.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB sizes beyond 2GB?

[Paul Sanderson]

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 sum of 3 + 4 above plus all of
the following data forming the record. So as things stand you can't store
any record where the sum of the bytes in the serial types array and the
actual data that follows is greater than MAXVARINT because the total length
must be stored in 1. (MAXVARINT is actually max positive varint - see
below).

If you want to use one of the reserved serial types to store a blob of 6GB
then the serial type itself must be capable of storing the size of the
blob. Currently, a blob has *any* serial type of >= 12 and even, so the
maximum size for a blob is (MAXVARINT-12)/2 i.e. *any* even serial type >=
12 and a text serial type is any odd serial type >= 13. All of the
remaining utilised serial types (i.e. those <= 9) refer to fixed length
data (ints and a 64 bit real).

The remaining 2 serial types (remember these are just two bits from a
64-bit serial type, each serial type is not a separate varint in its own
right) could be used to signify something like a 128-bit integer or some
other fixed-length data type, but, 1 bit by definition cannot store an
arbitrary length value.

I guess that the change Richard mentions (to up to 4GB) would be by
treating the varints as unsigned integers, rather than signed as they
currently are. This could be done (as far as I can see) for all varints
other than the rowid without affecting existing DBs.











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 28 March 2017 at 11:08, Dominique Devienne  wrote:

> On Tue, Mar 28, 2017 at 11:08 AM, Richard Hipp  wrote:
>
> > On 3/27/17, Andrew Cunningham  wrote:
> > > Is it likely the maximum BLOB size will be increased in a not too
> distant
> > > future version of SQLite?
> >
> > The maximum blob size could, in theory, be increased to 4GiB.  But the
> > current file format will not accommodate anything larger than that.
>
>
> Any chance you'd elaborate on which the format is blocking here?
> I have no doubt you're right, but I'd really appreciate a better
> understanding of that limitation.
>
> As a naive developer, I can see the Record Format [1] uses a varint, which
> can go up to 64-bit integers.
> And also that there are Serial Types 10,11, which are "Not used. Reserved
> for expansion".
>
> Which combined with The B-tree Page Format [2], which has only 4 page
> types,
> while a whole bytes is available for blob pages, a new blob-specific page
> type would seem possible.
>
> Given the above, I can (wrongly) imagine use Record Type 10 for "new-style
> blobs",
> which store a varint length for the "blob index" that follows, where that
> blob index is a ordered list
> of page-specific page numbers (as varints or not) where that blob is
> stored.
>
> In such a scheme, updating a single byte of a blob requires changing 1 blob
> page,
> and the page(s) storing the "blob index"; and blobs can also be expanded or
> contracted
> transitionally w/o having to rewrite the whole blob.
>
> I'm just trying to understand how/where that mental model is wrong. Thanks,
> --DD
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Oops - somehow misread the last message - 54 seconds down from 32 minutes -
that's a result!

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 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 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
> performance.
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786 <+44%201326%20572786>
> 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 18 March 2017 at 05:48, Rob Willett <rob.sql...@robertwillett.com>
> wrote:
>
>> We've just implemented a covering index for the last step (is it really?)
>> in our quest to get the query execution time down.
>>
>> To summarise we have gone from 32 mins to 16 mins by updating an index so
>> it doesn't use collate, we took another six minutes off by removing extra
>> fields in the select we didn't need.
>>
>> We have just created a new index which 'covers' all the fields we use in
>> the select, this means (and I paraphrase) that we use the index to get all
>> the data and there is no need to read from the database.
>>
>> Well that was a bit of a surprise, the index creation took 45 mins, we
>> ran the program again and thought, rats, we've cocked it up, it only took
>> 54 secs, we got something wrong. So we checked it and checked again and we
>> hasn't got anything wrong. Our query has moved from 32 mins to 54 secs.
>>
>> We're quite happy with that performance increase. In fact we're
>> delighted, so thanks for all the help in getting us to this stage.
>>
>> We have kept copies of the query planner bytecode output if anybody is
>> interested. Gunter has had copies, but if anybody else would like them,
>> please ask.
>>
>> Many thanks again for all the help,
>>
>> Rob
>>
>>
>> On 17 Mar 2017, at 22:12, Rob Willett wrote:
>>
>> Dear all,
>>>
>>> We progress steadily forward.
>>>
>>> 1. We immediately halved our execution time by moving to an updated
>>> index that doesn't use COLLATE. Thats 32mins to 16mins.
>>>
>>> 2. We then shaved a further six minutes off the execution time by
>>> removing extraneous fields in the select statement, so instead of "select *
>>> ...", we identified which fields we used and directly selected those. So we
>>> are now down to 10 mins or 1/3 of when we started for, to be honest,
>>> virtually no extra work, merely being smarter, or rather you being smarter.
>>>
>>> 3. We have looked through all our indexes and can see that every index
>>> has a COLLATE against it, even if the column is an integer. We have raised
>>> a support call with Navicat.
>>>
>>> 4. The next step is to create a "covering index" to try and get the
>>> whole of the query into the index. However its 22:11 in London and I need
>>> to get home.
>>>
>>> Thanks very much for the help so far. Tomorrow is more tricky but I'll
>>> read up on covering indexes to see how to use them,.
>>>
>>> Rob
>>>
>>> On 17 Mar 2017, at 18:39, Simon Slavin wrote:
>>>
>>> On 17 Mar 2017, at 6:22pm, Rob Willett <rob.sql...@robertwillett.com>
>>>> wrote:
>>>>
>>>> 4. Work through returning just the columns we actually need from our
>>>>> queries. We have a recollection that if we can build an index with all the
>>>>> information necessary in it, we can do all the work in joins rather than
>>>>> paging out to disk. Is this what you are referring to?
>>>>>
>>>>
>>>> It works only where all the columns you need to read are in the same
>>>> table.  The ideal form of a covering index is to have the columns listed in
>>>> this order:
>>>>
>>>> 1) columns needed for the WHERE clause
>>>> 2) columns needed for the ORDER BY clause which aren’t in (1)
>>>> 3) column

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

[Paul Sanderson]

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

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 18 March 2017 at 05:48, Rob Willett  wrote:

> We've just implemented a covering index for the last step (is it really?)
> in our quest to get the query execution time down.
>
> To summarise we have gone from 32 mins to 16 mins by updating an index so
> it doesn't use collate, we took another six minutes off by removing extra
> fields in the select we didn't need.
>
> We have just created a new index which 'covers' all the fields we use in
> the select, this means (and I paraphrase) that we use the index to get all
> the data and there is no need to read from the database.
>
> Well that was a bit of a surprise, the index creation took 45 mins, we ran
> the program again and thought, rats, we've cocked it up, it only took 54
> secs, we got something wrong. So we checked it and checked again and we
> hasn't got anything wrong. Our query has moved from 32 mins to 54 secs.
>
> We're quite happy with that performance increase. In fact we're delighted,
> so thanks for all the help in getting us to this stage.
>
> We have kept copies of the query planner bytecode output if anybody is
> interested. Gunter has had copies, but if anybody else would like them,
> please ask.
>
> Many thanks again for all the help,
>
> Rob
>
>
> On 17 Mar 2017, at 22:12, Rob Willett wrote:
>
> Dear all,
>>
>> We progress steadily forward.
>>
>> 1. We immediately halved our execution time by moving to an updated index
>> that doesn't use COLLATE. Thats 32mins to 16mins.
>>
>> 2. We then shaved a further six minutes off the execution time by
>> removing extraneous fields in the select statement, so instead of "select *
>> ...", we identified which fields we used and directly selected those. So we
>> are now down to 10 mins or 1/3 of when we started for, to be honest,
>> virtually no extra work, merely being smarter, or rather you being smarter.
>>
>> 3. We have looked through all our indexes and can see that every index
>> has a COLLATE against it, even if the column is an integer. We have raised
>> a support call with Navicat.
>>
>> 4. The next step is to create a "covering index" to try and get the whole
>> of the query into the index. However its 22:11 in London and I need to get
>> home.
>>
>> Thanks very much for the help so far. Tomorrow is more tricky but I'll
>> read up on covering indexes to see how to use them,.
>>
>> Rob
>>
>> On 17 Mar 2017, at 18:39, Simon Slavin wrote:
>>
>> On 17 Mar 2017, at 6:22pm, Rob Willett 
>>> wrote:
>>>
>>> 4. Work through returning just the columns we actually need from our
 queries. We have a recollection that if we can build an index with all the
 information necessary in it, we can do all the work in joins rather than
 paging out to disk. Is this what you are referring to?

>>>
>>> It works only where all the columns you need to read are in the same
>>> table.  The ideal form of a covering index is to have the columns listed in
>>> this order:
>>>
>>> 1) columns needed for the WHERE clause
>>> 2) columns needed for the ORDER BY clause which aren’t in (1)
>>> 3) columns needed to be read which aren’t in (2) or (1)
>>>
>>> SQLite detects that all the information it needs for the SELECT is
>>> available from the index, so it doesn’t bother to read the table at all.
>>> This can lead to something like a doubling of speed.  Of course, you
>>> sacrifice filespace, and making changes to the table takes a little longer.
>>>
>>> 5. Sleep (not exactly sure when) and watch three international rugby
 games tomorrow.

>>>
>>> Sleep while waiting for indexes to be created and ANALYZE to work.  May
>>> you see skilled players, creative moves and dramatic play.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org

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

[Paul Sanderson]

Oops - typo in my post above. Unix is of course secs since 1970.

and Tim yes I too always use numerical dates - each to their own though -
my post wasn't trying to say what is best, just what I see.

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 8 March 2017 at 22:57, Tim Streater <t...@clothears.org.uk> wrote:

> On 08 Mar 2017 at 20:40, Paul Sanderson <sandersonforens...@gmail.com>
> wrote:
>
> > 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.
>
> This is also what I do; seconds since the epoch. I can't imagine string
> dates or times as strings. You're also going to need to give the user the
> option to select their date/time format, too, so converting to display
> should be done at display time, not before.
>
> --
> Cheers  --  Tim
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 filetimes, chrome
dates and NSDates/MacAbsolute very regularly.

Interestingly I rarely see dates stored in ISO8601 format/text



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 8 March 2017 at 20:17, David Raymond  wrote:

> Correct. The ISO strings are the de-facto standard since that's what all
> the date and time functions take in.
> http://www.sqlite.org/lang_datefunc.html
>
> "The strftime() routine returns the date formatted according to the format
> string specified as the first argument."
>
> It's there so you can store your datetimes in a standardized way, then
> display them however you or your user wants, be it
> "03/07/2017"
> "3/7/17"
> "7-Mar-2017"
> "20170307"
> "March 7, 2017 AD"
> "The 7th day of the third month of the 17th year of the reign of Tiberius
> Caesar"
>
> The last one would be more in line with the modifiers you can use.
>
> strftime('%m/%d/%Y', TiberiusCaesar, 'start of reign', '+17 years', 'start
> of year', '+3 months', '+7 days')
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jens Alfke
> Sent: Wednesday, March 08, 2017 3:04 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Why isn't my time formatting working?
>
>
> > On Mar 8, 2017, at 11:59 AM, Rob Richardson 
> wrote:
> >
> > Given the lack of an indication of the return type, it seemed to me to
> be reasonable to assume that since I'm passing in a string as one of the
> arguments, I'd get a datetime object out.
>
> SQLite doesn’t have a datetime type, as far as I know. Dates are stored as
> strings.
>
> —Jens
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Hmm - a bit of google foo and refreshing of chances of collions means my
1:9,223,372,036,854,775,808 is way off

That is the chance of any two hashes colliding - you'll have lot sof hashes.

The basic idea might be sound though even if you stick with a full MD5 it
should save a chunk of storage

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 Sanderson <sandersonforens...@gmail.com>
wrote:

> 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
> storage would be 8 bytes plus admin
>
> the chance of a random colliison based on a 64 bit hash would be (I think)
> 1:9,223,372,036,854,775,808
>
> MD5 is broken but would work OK for this
>
> use a trigger to abort the insert into blah if the insert into hashes
> fails.
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786 <+44%201326%20572786>
> 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 21:54, Simon Slavin <slav...@bigfraud.org> wrote:
>
>>
>> On 1 Mar 2017, at 9:41pm, Deon Brewis <de...@outlook.com> wrote:
>>
>> > Yeah ok, but that is paltry compared with the gb's of diskspace that
>> the actual second index takes up. But thanks for clarifying.
>>
>> Ah.  If it’s really GBs of disk space then I can see why you’d look for
>> alternative solutions.
>>
>> But I have a 43 GB database file which could be 20 GB without an extra
>> index.  I could have written that extra check in code, and reduced the file
>> size, but I decided not to.  Because once I had developed procedures to
>> handle a 20 GB file, I might was well be dealing with a 43 GB file anyway.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
storage would be 8 bytes plus admin

the chance of a random colliison based on a 64 bit hash would be (I think)
1:9,223,372,036,854,775,808

MD5 is broken but would work OK for this

use a trigger to abort the insert into blah if the insert into hashes fails.


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 21:54, Simon Slavin  wrote:

>
> On 1 Mar 2017, at 9:41pm, Deon Brewis  wrote:
>
> > Yeah ok, but that is paltry compared with the gb's of diskspace that the
> actual second index takes up. But thanks for clarifying.
>
> Ah.  If it’s really GBs of disk space then I can see why you’d look for
> alternative solutions.
>
> But I have a 43 GB database file which could be 20 GB without an extra
> index.  I could have written that extra check in code, and reduced the file
> size, but I decided not to.  Because once I had developed procedures to
> handle a 20 GB file, I might was well be dealing with a 43 GB file anyway.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation error

[Paul Sanderson]

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. Initialize index X to be N-200 (where N is the size of a database
   page in bytes.
   3. Interpret the four bytes at offset X into the page as a 4-byte
   big-endian unsigned integer. Add the value of that integer to the checksum.
   4. Subtrace 200 from X.
   5. If X is greater than or equal to zero, go back to step 3.


The source states:

** This is not a real checksum. It is really just the sum of the
** random initial value (pPager->cksumInit) and every 200th byte
** of the page data, starting with byte offset (pPager->pageSize%200).
** Each byte is interpreted as an 8-bit unsigned integer.


Should read somethng like:

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. Initialize index X to be N-200 (where N is the size of a database
   page in bytes.
   3. Interpret the* byte* at offset X into the page as *an 8-bit* unsigned
   integer. Add the value of that integer to the checksum.
   4. Subtrac*t* 200 from X.
   5. If X is greater than or equal to zero, go back to step 3.




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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
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 2 February 2017 at 16:22, Stephen Chrzanowski  wrote:
> By a new requirement of my manager, we're asked to log all our SSH sessions
> to our customer machines.  The current Windows search is a PITA, grepping
> for text is burdensome considering the number of sessions I open per day,
> and being a pack rat, I love reading about stuff I did years ago. :]  (Not
> to mention the CYA thing is primary reason for this move -- I'm not
> complaining)
>
> So I'm thinking about writing a tool that'll take the output of the PUTTY
> logs, read them line by line, and insert the data into a SQLite database
> with some referential integrity that will allow me to search against what
> server I'm connecting to, a range of dates the logs, particular text, etc.
> (Granted there is a huge range of error that could happen with this that
> I'm not anticipating, but, meh.  I need something)
>
> Getting the data from the logs into a database is a true no-brainer.  Same
> with parsing and deciding how I want to check for text and properly catalog
> what I'm doing per machine.  Some putty sessions I jump between several
> machines, so during the line reading, I'll be looking for keywords
> (Hostnames) based on the command prompt since how our prompts are globally
> the same across all machines.
>
> During the reading process, what I want to do is read the line in, check
> the database to see what I've read in already exists and react accordingly
> by adding the new entry and setting up the relationships in other tables.
> Childs play, IMO.
>
> But, in my preplanning, scenario development and brain storming, the above
> paragraph is going to destroy my machine doing a [ select * from CmdLine
> where upper(CmdEntered) =upper('SomeText') ] every time I read a new line
> from a new log file to verify if the entry has been made.  So my thought
> leans towards FTS, but, I've never written anything dealing with that.
>
> Is there any kind of special preparation I need to do to the database to
> get it working effectively?  Is there a particular way I have to make my
> queries to see if previous text exists?  Is there a primer, not on the
> theory of how it works in the back end, but, how to generate the SQL call
> and deal with what comes out?  Are there any caveats I need to be aware
> of?  Do I skip FTS and just roll my own word analyzer?
>
> Since Oct 2016, my logs are sitting at just shy of 700meg of text.  Looking
> for what I did on a particular machine even last month would be a pain at
> this point.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 ugly +2



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 13 December 2016 at 13:19, Daniel Dumitriu  wrote:
>> But that does not seems to be the whole story, since if I do:
>>
>> SELECT datetime(42713.1916667+julianday('1900-01-01'));
>>
>> I get 2016-12-11 04:36:00 instead of what you were expecting
>> 2016-12-09 05:15.  Something is a little off.  I suspect that there
> I think he is expecting 2016-12-09 04:36 (column "Excel values" looks
> somehow reversed), so the time part is working.
>
>> You are apparently wanting to use the Excel date format, which appears
>> to be unique to Excel and used nowhere else.  According to the
>> Microsoft documentation
>> (https://support.microsoft.com/en-us/kb/214094) Excel for Windows uses
>> 1900-01-01 as its epoch and advances the value by 1.0 per day.  Excel
>> for Mac reportedly uses 1904-01-01 as the epoch.  So straight away we
>> see that you are going to need to modify your code depending on
>> whether you are running on Windows or Mac.
> Apparently one needs to add 2 to that value, since:
> - 1900-01-01 in Excel (on Windows) gets value 1, not 0
> - Excel notoriously and incorrectly considers 1900 a leap year [1] and
> acknowledges it, but "won't change" [2]
>
> So you should probably try
>
> SELECT datetime(2 + 42713.1916667+julianday('1900-01-01'));
>
> Regards,
> Daniel
>
> [1] https://en.wikipedia.org/wiki/Leap_year_bug
> [2] https://support.microsoft.com/en-us/kb/214326
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
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 24 November 2016 at 21:59, Florian Weimer  wrote:
> On 11/24/2016 10:41 PM, Howard Chu wrote:
>>
>> Florian Weimer wrote:
>>>
>>> On 11/24/2016 02:54 PM, Richard Hipp wrote:

 On 11/24/16, Florian Weimer  wrote:
>
> I'd like to replace the use of Berkeley DB in RPM with SQLite.
>
> The scenario is special in the follow way.  There is no database
> server,
> all access goes directly to the database.  Unprivileged users without
> write access to the RPM database are expected to run read-only queries
> against the database.  Privileged users (basically, root) is
> expected to
> use locking to exclude concurrent writers.  But read-only users should
> not be able to stop acquisition of a write lock.
>
> Is there a way to do this with SQLite?
>>
>>
>> Seems like quite a lot of burden to go this route. SQLite has a much
>> larger footprint than BDB, and much worse performance overall.
>
>
> SQLite has zero footprint for us because central parts of the system use it
> as well, and it is not likely to go away.  We also use the full Transaction
> Data Store, so the Berkeley DB shared object is about twice as large as the
> SQLite object.
>
>> As a
>> compromise you could use SQLightning, which replaces SQLite's Btree
>> layer with LMDB. Since LMDB *does* allow readers that don't block
>> writers.
>
>
> How does it do that?  Does LMDB perform lock-free optimistic reads and
> retroactively verifies that the entire read operation was consistent? The
> web page currently says that “readers need write access to locks and lock
> file”:
>
>   
>
> The restriction on opening the database twice within the same process is
> something which we would have to work around, too.
>
> I see you are working on eliminating the key size limit, which is nice.
>
> Thanks,
> Florian
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
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 24 November 2016 at 12:10, Simon Slavin  wrote:
>
> On 24 Nov 2016, at 11:02am, Florian Weimer  wrote:
>
>> The scenario is special in the follow way.  There is no database server, all 
>> access goes directly to the database.  Unprivileged users without write 
>> access to the RPM database are expected to run read-only queries against the 
>> database.  Privileged users (basically, root) is expected to use locking to 
>> exclude concurrent writers.  But read-only users should not be able to stop 
>> acquisition of a write lock.
>>
>> Is there a way to do this with SQLite?
>
> From the above you would want to use WAL mode.  You can read about it here:
>
> 
>
> To put a database into WAL mode,
>
> 1) Open the database file.
> 2) If the database file is newly created (i.e. blank) Put at least one schema 
> element into the file (e.g. create a TABLE).
> 3) Issue the command "PRAGMA journal_mode=WAL" in any program.
> 4) Close the database file normally.
>
> You can use the SQLite command-line tool to do the above if you wish, rather 
> than having to write your own software to do it.  Once the database is set to 
> this mode, that information is saved in the database file.  All connections 
> which open it will automatically know it must be handled in WAL mode.
>
>> One way that would work is to copy the database file after each modification 
>> to a read-only public view.  But the database can be fairly large, so this 
>> doesn't look feasible until we have reflink support at the file system level.
>
> You should not have to worry about this level of things yourself.  The SQLite 
> library handles this problem for you.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pragma to flag unknown pragma?

[Paul Sanderson]

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 returned when
journal_mode is set).

Likewise you can use pragma secure_delete to either set or query the
secure_delete mode




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 23 November 2016 at 16:36, Scott Hess  wrote:
> On Tue, Nov 22, 2016 at 10:50 PM, R Smith  wrote:
>> On 2016/11/23 2:08 AM, Scott Hess wrote:
>>> https://www.sqlite.org/pragma.html has:
>>> "No error messages are generated if an unknown pragma is issued.
>>> Unknown pragmas are simply ignored. This means if there is a typo in a
>>> pragma statement the library does not inform the user of the fact."
>>>
>>> I just lost some time due to this, even though I was fully aware of
>>> it.  My code wasn't working, so I instrumented to report errors, and
>>> gradually dug things deeper and deeper.  It wasn't until I was
>>> verifying statements line-by-line against sqlite3 in a terminal window
>>> that I saw that I was setting journal_mod rather than journal_mode!
>>>
>>> I realize that pragma don't have the compatibility guarantees that
>>> other syntax has.  But that means I actually _would_ want my code to
>>> start barfing if a PRAGMA stops being supported.  Say I'm issuing
>>> "PRAGMA secure_delete=ON" - presumably it's for a reason, and I'm
>>> going to want to revisit that reason if it's no longer supported.
>>
>> The problem is more the other way round - Backward compatibility is hard
>> when you introduce a new pragma, and a previous version of SQLite "barfs"
>> suddenly when it encounters that statement. Recent posts here re-emphasize
>> the frequency with which old versions are still used out there
>>
>> Silently ignoring an unrecognized pragma is a great way to be able to
>> introduce new functionality without worrying that the old will break.
>
> I may be misunderstanding where you're going with that, but my point
> was that there are some situations where I'm calling a PRAGMA because
> it's important to some implementation detail.  For instance, if your
> app's schema requires operational foreign keys, then simply ignoring
> "PRAGMA foreign_keys = ON" is _not_ kosher (I mean, yes, there's
> nothing SQLite can do to fix being old or compiled that way, but
> signalling "I can't do that, Dave" would be helpful).  How the PRAGMA
> I am _not_ calling are handled doesn't seem super relevant to that
> case, because I'm not calling them.
>
> Of course, my "PRAGMA pedantic_pragma = ON" call would itself succeed
> by failing on an older version of SQLite.  You have to bootstrap
> someplace, so you'd have to make the call to turn it on, then do
> another query to verify that it is on.  Which I guess is basically
> what your PRAGMA should already be doing, though there are subtleties
> (PRAGMA result patterns aren't consistent across different PRAGMA).
>
> -scott
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with Malformed table

[Paul Sanderson]

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
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 16 November 2016 at 15:31, Simon Slavin  wrote:
>
> On 16 Nov 2016, at 3:16pm, Andrew Stewart  wrote:
>
>>   Forgot to ask 1 thing.  What is a quick way to copy an entire table 
>> from database1 to database2?
>
> If they are open in different connections you cannot do it.
>
> If they are open in the same connection using ATTACH then you can do it like 
> this:
>
> open ('new.sqlite')
> ATTACH 'corrupt.sqlite' AS corrupt;
> CREATE TABLE myTable (... same structure as the old one ...);
> INSERT INTO myTable (SELECT * FROM corrupt.myTable)
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
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 30 October 2016 at 00:22, Richard Hipp  wrote:
> On 10/29/16, Yuri  wrote:
>
>>> What does "PRAGMA integrity_check" say about your database?
>>
>> It returns "ok", but this file has been opened and written into again.
>>
>
> This suggests that the problem may be somewhere besides in SQLite.  If
> SQLite were getting confused and zeroing content as a result of the
> disk-full error, then almost certainly integrity_check would report
> errors.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Paul Sanderson]

> 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 a needed column, it just needs to be able to skip it -
unless the data oveflows in to one or more overflow pages then it will
need to fetch each page until it reaches the one with the data in it.
If the complete row is held in one page and your query just needs the
last column - SQLite just needs to know the size of all of the data
that preceedes the column you want. There is still the overhead of
decoding every serial type before the column you require.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of fields for insert

[Paul Sanderson]

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 table definition to avoid the same
sort of overflow.




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 6 October 2016 at 15:25, Hick Gunter  wrote:
> SQLite compresses rows before storing and decompresses rows before returning 
> fields. BLOB fields are the most time consuming to process and so should be 
> placed at the end of the row. Often used fields - i.e. (foreign) key fields - 
> should be placed at the front of the row. This will help most if your select 
> field list is limited to the fields you actually need instead of "*".
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Jeff Archer
> Gesendet: Donnerstag, 06. Oktober 2016 15:46
> An: SQLite mailing list 
> Betreff: [sqlite] Order of fields for insert
>
> Just a quick question.  I am actually deciding if I need to do some 
> performance testing of this but thought I might gain some quick insight.
> My specific insert and table are below but really I am looking for a general 
> answer to the question not just this specific case.
>
> Are there any performance or other considerations of the order of the fields 
> for an insert?
> Are the following equivalent?  regardless of number of values inserting?
>  regardless of size of the data being inserted?
>
> INSERT INTO
> mytable(
> wid1,cnt,
> dat,
> wid3,wid2) VALUES (?,?,?,?)
> - VS -
>
> INSERT INTO
> mytable(
> wid1,wid2,wid3,cnt
> ,dat
> ) VALUES (?,?,?,?)
>
>
> CREATE TABLE
> mytable
> (
> id
>  INTEGER PRIMARY KEY AUTOINCREMENT"
> ,
> wid1
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> wid2
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> wid3
>  INTEGER REFERENCES
> othertable
> (
> id
> ) ON DELETE CASCADE
> ,
> cnt
>  INTEGER DEFAULT
> 1
> ,dat TEXT
> )
>
> Jeff
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching for a percent symbol

[Paul Sanderson]

Thank you also Petite
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 5 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://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 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?
>>>
>>
>> sqlite> create table t (v);
>> sqlite> insert into t values
>>...> ('I got 20 quid'),
>>...> ('i got 20% of it'),
>>...> ('i got just 20%'),
>>...> ('some money'),
>>...> ('this is an underscore _ ok');
>> sqlite> select * from t where v like '%20!% %' escape '!';
>> i got 20% of it
>> sqlite>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching for a percent symbol

[Paul Sanderson]

Brilliant thansks Dominique - I had completely misunderstood it :)
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 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?
>>
>
> sqlite> create table t (v);
> sqlite> insert into t values
>...> ('I got 20 quid'),
>...> ('i got 20% of it'),
>...> ('i got just 20%'),
>...> ('some money'),
>...> ('this is an underscore _ ok');
> sqlite> select * from t where v like '%20!% %' escape '!';
> i got 20% of it
> sqlite>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching for a percent symbol

[Paul Sanderson]

Thanks Petite - I have already looked at that - but how?

How do I search for a % symbol within a string when % is a wild card
and I am escaping that very wildcard?
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 5 October 2016 at 10:44, Petite Abeille <petite.abei...@gmail.com> wrote:
>
>> On Oct 5, 2016, at 11:38 AM, Paul Sanderson <sandersonforens...@gmail.com> 
>> wrote:
>>
>> How casn I find just the rows containing the percent symbol? is it possible?
>
> Use the ESCAPE clause:
>
> "If the optional ESCAPE clause is present, then the expression following the 
> ESCAPE keyword must evaluate to a string consisting of a single character. 
> This character may be used in the LIKE pattern to include literal percent or 
> underscore characters. The escape character followed by a percent symbol (%), 
> underscore (_), or a second instance of the escape character itself matches a 
> literal percent symbol, underscore, or a single escape character, 
> respectively.”
> — The LIKE, GLOB, REGEXP, and MATCH operators
>
> https://www.sqlite.org/lang_expr.html
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Searching for a percent symbol

[Paul Sanderson]

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 this (to search for 20% followed by a space followed by anything):
SELECT *
FROM Strings
WHERE Strings.str LIKE '%20%% %'

i got 20% of it
I got 20 quid

How casn I find just the rows containing the percent symbol? is it possible?


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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 database and how many records are added daily?


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 3 October 2016 at 12:57, Stephen Chrzanowski  wrote:
> Are you looking to keep the logs in the same file, or, are you looking to
> put your log entries in a new file?
>
> If you're interested in just keeping a single file, and if you've got
> access to change your code that is writing to the database, then, what I
> would do is "create table if not exists Logs (LogDate, LogMessage)", then
> do the insert.  Then you can purge previous logs with a simple DROP TABLE
> whenever you want to clean things up.  No worries about OS file handling,
> unless you have a burst of log entries, file sizes are going to pretty much
> stay the same size, etc, since you'll be just freeing pages up internally
> in the database.
>
> As others have mentioned, if you plan on a log per day, then, you'll need
> to close the file handle, reopen with a new file and database.
>
> On Mon, Oct 3, 2016 at 5:18 AM, Luca Ferrari  wrote:
>
>> Hi all,
>> in one of my application I use a sqlite3 database as a log of
>> activity. As you can imagine the file grows as time goes by, so I'm
>> figuring I've to substitute it with an empty one once a good size is
>> reached.
>> What is the right way to do it without having to stop the application
>> (and therefore without knowing when a new I/O operation will be
>> issued)?
>> Does sqlite3 provide some facility that could come into help (e.g.,
>> connected databases)?
>>
>> Thanks,
>> Luca
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
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 30 September 2016 at 14:33, Malte Legenhausen  wrote:
> Hi,
>
>
>
> Could someone please explain why the following sequence of commands fail in 
> sqlite?
>
>
>
> SQLite version 3.14.0
>
>
>
> sqlite> create table t1(one varchar(10));
>
> sqlite> CREATE INDEX unique_one ON t1 (one);
>
> sqlite> create table t2(one varchar(10));
>
> sqlite> CREATE INDEX unique_one ON t2 (one);
>
> Error: index unique_one already exists
>
>
>
> This is an unintended behaviour cause the following command returns with an 
> empty result:
>
> sqlite> PRAGMA INDEX_LIST(t2);
>
>
>
> Debugging this behaviour is very hard cause to make sure an index name is not 
> taken would result in looking in all indexes of all other existing tables. I 
> hope this is not an intended behaviour? Other database systems like postgres 
> can differ between indexes with the same name on different tables.
>
>
>
> If it is intended would it be possible to enhance the documentation?
>
>
>
> Best regards
>
> Malte
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Paul Sanderson]

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

[Paul Sanderson]

ah OK - being dull thank you
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 29 September 2016 at 14:29, Keith Medcalf <kmedc...@dessus.com> wrote:
> You query is incorrect.  It should be:
>
> SELECT CASE
>   WHEN (unix10and13.dt < 100)
>  THEN DateTime(unix10and13.dt, 'unixepoch')
>   WHEN (unix10and13.dt > 100)
> THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
>   ELSE dt
>   END AS converted
> FROM unix10and13;
>
> When your case, you are using the CASE  WHEN  ...
>
> So, the THEN clauses are comparing the value of DT to the result on (dt < 
> 100) or (dt > 100).  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 Behalf Of Paul Sanderson
>> Sent: Thursday, 29 September, 2016 07:14
>> To: General Discussion of SQLite Database
>> Subject: [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, 'unixepoch')
>>   WHEN (unix10and13.dt > 100)
>> THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
>>   ELSE dt
>>   END AS converted
>> FROM unix10and13
>>
>> But this returns the original values - i.e. the else portion is being
>> evaluated but one of the previous expressions should evaluate to true
>> surely? Any ideas why this is failing?
>>
>>
>>
>> I am also interested (because I tried and failed) in coding a second
>> query that would return all four rows but in two columns each with
>> either a unix10 or 13 date in the correct column - something like
>> this:
>>
>> unix10,  unix13
>>,1234345087123
>> 1234567890,
>> 1432101234,
>>,1456754323012
>>
>> any suggestions to achieve this approach?
>>
>>
>>
>> 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
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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, 'unixepoch')
  WHEN (unix10and13.dt > 100)
THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
  ELSE dt
  END AS converted
FROM unix10and13

But this returns the original values - i.e. the else portion is being
evaluated but one of the previous expressions should evaluate to true
surely? Any ideas why this is failing?



I am also interested (because I tried and failed) in coding a second
query that would return all four rows but in two columns each with
either a unix10 or 13 date in the correct column - something like
this:

unix10,  unix13
   ,1234345087123
1234567890,
1432101234,
   ,1456754323012

any suggestions to achieve this approach?



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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Please let me know if this works for you - I've never tried it so at
the moment it's just an idea :)
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 20 September 2016 at 13:05, Jose Arroyo <jose.m.arroyo...@gmail.com> wrote:
> The writer process doesn't ever do "truncating" checkpoints, even if it
> does handle checkpoints manually. I'm starting to realize 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 <sandersonforens...@gmail.com>
> wrote:
>
>> 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 would need to
>> make sure your checkpoint was done in truncate mode:
>>
>> PRAGMA wal_checkpoint(truncate)
>>
>> WAL auto checkpoints are always passive so you would need to disable
>> autocheckpointing and do all checkpoints yourself manually as above to
>> ensure WAL is truncated.
>>
>> PRAGAM wal_autocheckpoint = 0
>>
>> 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 20 September 2016 at 08:59, Jose Arroyo <jose.m.arroyo...@gmail.com>
>> wrote:
>> > From looking at the source code, it seems to me that the values returned
>> > on wal_hooks come from some sqlite's internal variables so it doesn't
>> seem
>> > to be possible to query the DB for it. I suspect that the WAL size can be
>> > calculated from the WAL index file somehow, but I don't really grok how
>> it
>> > is actually used from the docs online (
>> > https://www.sqlite.org/fileformat.html#wal_index_format).
>> >
>> > My current issue is that I'm encountering WAL checkpoint starvation and
>> I'm
>> > trying to figure out a way to create checkpoint gaps only when needed.
>> The
>> > scenario is pretty standard, one writer process and several readers
>> > processes. The writer notifies the readers in some specific DB updates
>> > scenarios, the readers query, do some stuff, update some fields by
>> > messaging the writer process who will queue up those updates. After a
>> > certain DB size, checkpoints start failing more and more often to the
>> point
>> > that the WAL size starts growing unbounded.
>> >
>> > The caveat is that all this happens under a SPARQL layer and that I don't
>> > have much room to modify how the writer process works. So I was wondering
>> > if I could access the current number of pages in the WAL from outside the
>> > writer process in a cheap way.
>> >
>> > Any advice or guidance is greatly appreciated
>> >
>> > Cheers,
>> > José M. Arroyo
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 would need to
make sure your checkpoint was done in truncate mode:

PRAGMA wal_checkpoint(truncate)

WAL auto checkpoints are always passive so you would need to disable
autocheckpointing and do all checkpoints yourself manually as above to
ensure WAL is truncated.

PRAGAM wal_autocheckpoint = 0

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 20 September 2016 at 08:59, Jose Arroyo  wrote:
> From looking at the source code, it seems to me that the values returned
> on wal_hooks come from some sqlite's internal variables so it doesn't seem
> to be possible to query the DB for it. I suspect that the WAL size can be
> calculated from the WAL index file somehow, but I don't really grok how it
> is actually used from the docs online (
> https://www.sqlite.org/fileformat.html#wal_index_format).
>
> My current issue is that I'm encountering WAL checkpoint starvation and I'm
> trying to figure out a way to create checkpoint gaps only when needed. The
> scenario is pretty standard, one writer process and several readers
> processes. The writer notifies the readers in some specific DB updates
> scenarios, the readers query, do some stuff, update some fields by
> messaging the writer process who will queue up those updates. After a
> certain DB size, checkpoints start failing more and more often to the point
> that the WAL size starts growing unbounded.
>
> The caveat is that all this happens under a SPARQL layer and that I don't
> have much room to modify how the writer process works. So I was wondering
> if I could access the current number of pages in the WAL from outside the
> writer process in a cheap way.
>
> Any advice or guidance is greatly appreciated
>
> Cheers,
> José M. Arroyo
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using Bitwise Logic In Query

[Paul Sanderson]

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 for SQLite
email from a work address for a fully functional demo licence


On 8 September 2016 at 11:07, Dave Blake  wrote:
> Looking for the best way to query a table with an integer column by value
> of the lower 16 bits of the data in that column. Does SQLite support
> bitwise logic?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Document ommision bind-parameter description

[Paul Sanderson]

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


On 13 July 2016 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
>>
>> In the expr section bind-parameter is a dangling hyper link
>
> That BNF version of the grammar is unmaintained and is obsolete in
> multiple respects.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Document ommision bind-parameter description

[Paul Sanderson]

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
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bad db feature request

[Paul Sanderson]

The OP wanted something to check his systems for corruption - if the
SQLite dev team don't want to add checksums then the OP could possibly
solve his own problem by adding them to his own internal version.

Extension may have been a bad choice of word - he can modify/compile
his own version of SQLite add checksums - and also add a corruption
engine if he wants.
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 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 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
>
>
> Can you really? What kind of extension?
>
> The pager is not an extension point.
>
> The VFL is one, but https://www.sqlite.org/c3ref/io_methods.html is byte
> oriented, not page oriented.
> Plus there are different types of pages, with different format. Does the
> checksum go in the same place for all them?
> Official SQLite would ignore the extra info, and probably either not
> rewrite it, or keep it as-is, when modifying a page,
> making the page appear invalid. This is precisely the kind of things that
> if not built in is kinda useless I'm afraid.
>
> Plus w/o a format change, even if built-in to the latest SQLite, older
> SQLite apps wouldn't keep the checksums up-to-date too.
> This and index-based-row-counts and probably other things do require a
> backward-incompatible change IMHO. --DD
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bad db feature request

[Paul Sanderson]

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: @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 29 June 2016 at 18:52, Scott Hess  wrote:
> On Wed, Jun 29, 2016 at 10:36 AM, Simon Slavin  wrote:
>> On 29 Jun 2016, at 5:45pm, Drago, William @ CSG - NARDA-MITEQ 
>>  wrote:
>>> Aren't there things like that already built in to the hard disk controllers 
>>> (CRC, Reed Solomon, etc.)?
>>
>> Yes.  But they operate at the level they understand.  For instance ...
>>
>> A change is made in a field which involves changing just one page of data.  
>> In terms of the SQLite file format this would mean that a table page is 
>> overwritten -- a one disk sector change.  If SQLite checksums existed then 
>> this would mean that the checksum, stored in the table pointer page, would 
>> also have to be updated.  Which would mean that another disk sector has to 
>> be changed too.
>>
>> Now suppose there's a big in the storage medium driver which means it 
>> occasionally writes the correct data to the wrong sector on disk.  Without 
>> checksums this fault would not be noticed: since the wrong sector on disk 
>> was updated the wrong checksum on disk would be updated too.  The errors 
>> would match.
>
> I think the bigger problem is that delegating this means that you
> assume the entire underlying stack is working correctly.  For
> instance, the disk may have elaborate error-correction protocols that
> are working correctly per sector, but SQLite's pages may span sectors.
> Or the underlying disk may be perfect and the filesystem doesn't
> provide the same guarantees.  Or someone is running things over NFS.
> Having the page checksum embedded in the page at the SQLite level
> would provide end-to-end confidence.
>
> Chaining the checksums is a whole different level of assurance.  To
> the best of my knowledge _all_ legitimately (1) corrupted databases
> I've seen had pages which were individually valid, but not valid when
> taken together.  Like an index page referred to a row which wasn't
> present in the table page.  This implies that the atomicity guarantees
> SQLite relies on were broken at the filesystem or disk level.
>
> -scott
>
> (1) I consider a system where the filesystem is simply broken to not
> be legitimate corruption.  For instance, if you get a page of random
> which doesn't appear to have every been SQLite data in the first
> place.  There's not much SQLite can do about that kind of thing.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL: no schema after close

[Paul Sanderson]

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. In your case this
is a new blank DB.

It is not enough to commit a transaction as the wal can and often does
contain the data for multiple transactions.

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 3 June 2016 at 11:32, Yongil Jang  wrote:
> In my guess, if you call following command after for each operation and
> schema exits exactly... Your test may removes *-wal file.
>
> conn.execute("PRAGMA wal_checkpoint;")
>
> I hope it is correct answer.
>
> Regards.
>
> 2016년 6월 3일 (금) 19:19, Максим Дементьев 님이 작성:
>
>> Hello,
>>
>> I’ve got a set of python 3 unittests which use sqlite (versions 3.12.0 and
>> 3.13.0 under Linux 4.4.6-gentoo x86_64).
>>
>> There is one test which closes the database and opens it again during the
>> test.
>> If I use the WAL (by using conn.execute("PRAGMA journal_mode=WAL;")) and
>> run all unittests, this unittest produces a database file with the empty
>> schema after first close, so it fails.
>> When I run it alone (or without WAL), it passes.
>>
>> I’ve checked “hexdump -C” of the normal and the bad cases after first
>> close, in both cases the size of database file is the same, they contain
>> common data for inserted rows, but there is no “CREATE TABLE ...”
>> statements in the bad database file.
>>
>> There are conn.commit() calls after each modification SQL statement. I
>> even tried to put explicit "BEGIN; ... COMMIT;" around "CREATE TABLE IF NOT
>> EXISTS..." statements, it doesn't help.
>>
>> Is it a well-known problem ?
>>
>> Regards,
>> Max
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
different and with differing timezones, depending on the users
settings. Trivial but forum software takes care of all of this for
you.

Tends to be die hard linux users who like mail lists (and text only
email) and the rest of us have moved on :)
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 27 May 2016 at 11:43, Jonathan Moules  wrote:
> I think there are two different use cases for a mailing list such as this, 
> and they're each better served by different access method; either email or 
> forums.
>
> One use case is the individual with a long-term interest in a 
> project/technology. Because of the long-term interest, an email list (which 
> is a "push" technology - it comes to you) is ideal - you sign up once, set up 
> mail filtering, and then read at your leisure in the client of your choice.
>
> The other use case is people with a one-off question. "Why isn't this query 
> working?", "What's the SQLite function for XYZ?" etc. For me at least, 
> whenever I have had one of these in the past for a particular technology, 
> signing up to a mailing list is a chore. You sign up, wait for the 
> confirmation email, get spammed by tons of stuff you're not interested in 
> (you don't plan on being subscribed long enough to spend the time setting up 
> filters), then have to unsubscribe when you've got the answer, and then 
> confirm the unsubscription.
> Then two months later you have another query and have to do the entire 
> process again. Personally I loathe going through this process for one-off 
> questions.
>
> In this second use-case, a forum (being "pull" based - you get it when you 
> want it) is better. Sign-up, correspond, then forget about it. If there's 
> another question a few months later, you can login again using the old 
> credentials and continue where you left off.
>
> Unfortunately I don't think there's much forum/list software out there that 
> facilitates *both* use-cases. Google Groups tries to, but I've not 
> encountered anything else (not that I've gone looking).
> I've seen some lists try and punt this "forum" component off to domain 
> specific stackexchange sub-sites, but that has it's own problem (splitting 
> the community between venues).
> Just my 2p,
> Cheers,
> Jonathan
>
>
>
>
>  On Fri, 27 May 2016 10:55:30 +0100 Rob Willett 
> rob.sql...@robertwillett.com wrote 
>
> I agree with Tim.
>
> I filter all my SQLite messages to its own folder and read as needed.
>
> I prefer this method to a forum as I can then keep the messages with me.
> I’m often off the internet and its good to have them for reading. Also
> I can look back through them and get hints and tips about issues.
>
> Its not perfect but it works for me. I get around the distraction of
> emails coming in through client filtering, I did start filtering at the
> server so that only important stuff came through immediately but that
> didn’t work well. I now use Mailmate and like the power and lack of
> graphical eye candy it offers.
>
> I’d actively vote against a SQLite forum rather than e-mail (if
> anybody asked me, which I doubt…) SQLite is important to our work so
> having it around is fine by me,
>
> YMMV
>
> Rob
>
>
> On 27 May 2016, at 10:34, Tim Streater wrote:
>
>  On 27 May 2016 at 08:56, Darren Duncan dar...@darrenduncan.net
>  wrote:
> 
>  On 2016-05-26 9:00 PM, Balaji Ramanathan wrote:
>  The main advantage of forums, and I follow a bunch of them, is 
> that
>  I choose
>  when I want to stop my regular day job and be distracted by them
>  rather than
>  emails coming in and distracting me all the time.
> 
>  That's not an argument for web forums, rather that's an argument for
>  not using
>  your work email to subscribe to non-work discussion lists; use a
>  non-work email
>  for the discussion lists instead. You can also configure your email
>  client to
>  only check email when you tell it to rather than constantly. --
>  Darren Duncan
> 
>  Filter the sqlite mails into their own mailbox. They can then be read
>  at a convenient moment.
> 
>  I certainly don't want a whole lot of sub-forums and the like, each of
>  which has to be checked in case there's something interesting there.
> 
>  --
>  Cheers -- Tim
>  ___
>  sqlite-users mailing list
>  sqlite-users@mailinglists.sqlite.org
>  http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> 

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

[Paul Sanderson]

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 understand why some would not
allow this but most forums I am in are closed/strictly monitored)

I like the ability to split a thread when it has gone off topic.

I like formatting my posts :)

Forums were after all partly designed to address the short comings of
email lists (although email clients have also moved on)

There are forums that also support an email interface so I am sure if
there is a will there is a a way

I can live with things how they are though.


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 27 May 2016 at 06:23, Jean-Christophe Deschamps  wrote:
>
> At 03:46 27/05/2016, you wrote:
>>
>> If SQLite goes this route, I will probably (as with the others) stop
>> reading it too.
>
>
> Seconded.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users