[sqlite] Use of sqlite3_bind_int() vs sqlite3_bind_int64()

[Paul]

According to datatypes page https://sqlite.org/datatype3.html SQLite choses 
width of the integer automatically. Does it mean that if I let's say want to 
bind a number 5 in the query that inserts/updates rows it will be stored in 
database as 1-byte integer regardless of the use of sqlite3_bind_int() or 
sqlite3_bind_int64()? 
If so, is it safe to *always* use sqlite3_bind_int64()/sqlite3_column_int64() 
and forget about pain int versions? 
___
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 <ddevie...@gmail.com> wrote:

> On Tue, Mar 28, 2017 at 11:08 AM, Richard Hipp <d...@sqlite.org> wrote:
>
> > On 3/27/17, Andrew Cunningham <and...@a-cunningham.com> 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 <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) 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@ma

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 <david.raym...@tomtom.com> 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 <rdrichard...@rad-con.com>
> 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 <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

[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 <pontia...@gmail.com> 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] (dot) output call locks up when used on a named pipe

[Paul Lambert]

I will need to do some testing.  Here is an example of fopen using a named
pipe

   mkfifo fifoname

 /* issue fopen for write end of the fifo */
wr_stream = fopen(fifoname,"w");

from:  
http://www.ibm.com/support/knowledgecenter/SSLTBW_2.1.0/com.ibm.zos.v2r1.cbcpx01/fifopip.htm

The "wb" your listing has add the "b" but based on what I have found
the "b"  adds nothing and is the same as just "w."  The documentation
I found states it is only added for ISO C standard conformance.

I will need to do some more testing and report back.


On Sat, Dec 31, 2016 at 3:32 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 12/31/16, Paul Lambert <eb30...@gmail.com> wrote:
> > I have observed that the file write mode of this function when using the
> > same file name a second time will delete the previous data in the file.
> > This is most likely accomplished by deleting the file and recreating a
> new
> > one.  if so then this explains why the .output function locks up when
> using
> > a named pipe as the pipe is not responding to a delete command.
>
> It just calls fopen().  http://www.sqlite.org/src/
> artifact/6095531aa9?ln=2609
>
> I don't know what fopen() is doing behind the scenes.  Probably it
> works differently on different systems, but I would have guessed that
> it just invoked open() with O_TRUNC.
>
>
> >
> > Pipes are files and the ability to launch an application and place a
> > pending read on the pipe is such that the system will block the read and
> > the application will hang on the read statement until something shows up.
> > In the case of a file this is not true.
> >
> > I believe this command needs to be modified so that it performs a "stat"
> to
> > determine if the target is a file or named pipe.  If a named pipe then
> omit
> > the call to delete it.  This function should additionally check to make
> > sure the target is a file and not a directory too as user typos will most
> > certainly lead to unknown results.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> 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] Out of date online documents

[Paul Lambert]

Web search.  In fact I cannot find any link on the sqlite.org page that
links to the (dot) functions, alias special functions.

Nothing there on the By Alphabetical Name, or SQL as Understood by Sqlite
webpages.  Seems to me that there should be a link on Undertsood by Sqlite
similar to the aggregate functions for "special commands" or "special
functions" too.

On Sat, Dec 31, 2016 at 2:52 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 12/31/16, Paul Lambert <eb30...@gmail.com> wrote:
> > While exploring the sqlite.org website for specific sqlite details I
> found
> > the document at the URL below which is labelled as "draft" and appears to
> > be from version 3.6.11.  The latest release is 3.15 and I currently have
> > version 3.13 installed.
> >
> >
> > https://www.sqlite.org/sessions/sqlite.html
>
> That is indeed wildly obsolete documentation.  But there are no
> hyperlinks to it from the home page.  How did you find it?
>
> --
> 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] (dot) output call locks up when used on a named pipe

[Paul Lambert]

I have used the (dot) .output call in conjunction with a both a file and
name pipe on Linux with Sqlite 3.13 installed.  With a file this functions
successfully  and completes.  When using a named pipe this functions locks
up sqlite and it must be aborted for termination.

I have observed that the file write mode of this function when using the
same file name a second time will delete the previous data in the file.
This is most likely accomplished by deleting the file and recreating a new
one.  if so then this explains why the .output function locks up when using
a named pipe as the pipe is not responding to a delete command.

Pipes are files and the ability to launch an application and place a
pending read on the pipe is such that the system will block the read and
the application will hang on the read statement until something shows up.
In the case of a file this is not true.

I believe this command needs to be modified so that it performs a "stat" to
determine if the target is a file or named pipe.  If a named pipe then omit
the call to delete it.  This function should additionally check to make
sure the target is a file and not a directory too as user typos will most
certainly lead to unknown results.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (dot) commands will execute from sqlite command line but not from query

[Paul Lambert]

On Linux with sqlite 3.13.0  installedI have successfully invoked the (dot)
commands .system and .shell.

Below are the results.  In both instances the application at the target
location launched as expected.  (Ignore the Gtk message as it relates to
the modal dialog box employed in the the application)

sqlite> .system /home/EB30750/Documents/Provox/deltvImport

Gtk-Message: GtkDialog mapped without a transient parent. This is
discouraged.
System command returns 65280
sqlite>

sqlite> .shell /home/EB30750/Documents/Provox/deltvImport

Gtk-Message: GtkDialog mapped without a transient parent. This is
discouraged.
System command returns 65280
sqlite>


Issue 1:  Both of these commands wait for the external program to exist
before returning.  Typically, where there are two versions, one will return
without waiting for the child process to terminate and the other one will
wait.  I see these two functions as the same as the .system function can
invoke the shell this way .system /usr/sh echo 'hello world'  Or .system C:\
*Windows*\System32\cmd type 'hello world'  Might want to consider making
one of these return with no wait.


Issue 2.  No matter how I mix the use of .system in a trigger I cannot get
the syntax checker to allow for  (dot) functions .system or .shell to
work.  I know there is a qualifier about where the (dot) functions are
parsed but I have successfully used other (dot) functions in a trigger.
Triggering external applications on database events is a critical function.


sqlite> CREATE TRIGGER testor
   ...> BEFORE INSERT ON moz_cookies
   ...> BEGIN
   ...> DELETE FROM moz_cookies WHERE name = 'mimeMapApp';
   ...> .system /home/EB30750/Documents/Provox/deltvImport;
   ...> END;
Error: near ".": syntax error
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Out of date online documents

[Paul Lambert]

While exploring the sqlite.org website for specific sqlite details I found
the document at the URL below which is labelled as "draft" and appears to
be from version 3.6.11.  The latest release is 3.15 and I currently have
version 3.13 installed.


https://www.sqlite.org/sessions/sqlite.html
.shell not listed
.system not listed


In the 3.6.11 version the (dot) calls .system and .shell where not part of
sqlite. I later found the same output in a newer version online document
that did show these 2 (dot) functions.

I strongly encourage having all online documentation having a "last"
revised date at the top (and maybe bottom) of each page and labelled either
"current" or "latest revision."  Or, in the case of software revisions also
including the latest release of software the online documents pertain too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[Paul Egli]

On Tue, Dec 13, 2016 at 9:56 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 13 Dec 2016, at 3:53pm, Paul Egli <e...@sourcegear.com> wrote:
>
> > Well if i am missing something, i still cannot see it.
> >
> > Based on these quotes in the docs, i assume that a NULL in the child
> table
> > means that it does not have a parent.
>
> You are correct.  I missed that.
>
> So yes, the original poster was correct, and using an index which left out
> the NULL key values would lead to the right results.  Should be possible to
> use that for an optimization case.  I suppose it might lead to quite a
> saving in filespace for some tables.
>


Great. Just to clarify, SQLite will already use "alternateChildIndex1" from
the example? Or just that it would be possible as an enhancement request?

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

Re: [sqlite] Foreign key child index question

[Paul Egli]

On Tue, Dec 13, 2016 at 9:41 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 13 Dec 2016, at 3:20pm, Paul Egli <e...@sourcegear.com> wrote:
>
> > Why must SQLite find rows where the value is NULL?
>
> Because the related fields in the offspring row might have NULL in them,
> and SQLite needs to know how to find the parent row for that row.
>
>
Well if i am missing something, i still cannot see it.

Based on these quotes in the docs, i assume that a NULL in the child table
means that it does not have a parent.

"All foreign key constraints in SQLite are handled as if MATCH SIMPLE were
specified."

"If "MATCH SIMPLE" is specified, then a child key is not required to
correspond to any row of the parent table if one or more of the child key
values are NULL."

Therefore SQLite would *not *need to find a parent for that row.

So i guess i am still hoping that, if possible, SQLite could be enhanced as
David's question was getting at. :-)
___
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 <daniel.dumit...@gmail.com> 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] Foreign key child index question

[Paul Egli]

On Wed, Dec 7, 2016 at 3:11 PM, Simon Slavin  wrote:

>
> On 7 Dec 2016, at 8:40pm, David Raymond  wrote:
>
> > Question on making indexes for the child fields of foreign keys. I have
> a child table with a number of foreign keys on fields which the majority of
> the time are null. I've currently got indexes on the child fields for the
> purposes of speeding up the foreign key checks, but what I'm wondering is
> if I used a conditional index which has "where fkField is not null", will
> that index be usable by the internal foreign key checker?
>
> Sorry, but the answer is no.  SQLite needs to be able to find the rows
> where the key-value is NULL.  It can’t do that from an index which doesn’t
> include those rows.
>
>
Why must SQLite find rows where the value is NULL? To me, this sounded like
a good enhancement request if it's not already the case. Am i missing
something?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite does not use opportunity to optimise COUNT() queries using partial indexes

[Paul]

Thanks!

That explains a lot. For some reason I thought that 'SELECT COUNT() FROM 
' is optimised. 

 
> Gonna take a stab and answering this.
> http://www.sqlite.org/opcode.html
> 
> The explain output for select count() from foo; uses the "Count" opcode. The 
> description for that is
> "Store the number of entries (an integer value) in the table or index opened 
> by cursor P1 in register P2"
> So that is indeed going to scan through the whole table, as the OpenRead was 
> pointed to the table B-tree and not the index B-tree.
> 
> In the second case "select count() from foo where ref_count = 0" the OpenRead 
> opens up the index (p4 isn't an integer) so that is indeed going through the 
> index.
> 
> Remember also that you can get a more succinct explain by using "explain 
> query plan".
> 
> Here's the output of me running this in a CLI I compiled with the pretty 
> explain comments. Using .eqp full it outputs the "explain query plan" 
> results, then the "explain" results, then the query results.
> 
> (Hmm, random note: It looks like ".eqp full" makes it disregard ".header on" 
> when it gets down to outputting the results. Downgrading to only ".eqp on" 
> respects the ".header on" though.)
> 
> SQLite version 3.15.1 2016-11-04 12:08:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> 
> sqlite> create table foo (id integer primary key, ref_count integer not null);
> 
> sqlite> create index foo_ref_count_idx on foo (ref_count) where ref_count = 0;
> 
> sqlite> .eqp full
> 
> sqlite> select count() from foo;
> --EQP-- 0,0,0,SCAN TABLE foo
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 7 000  Start at 7
> 1 OpenRead   1 2 0 1  00  root=2 iDb=0
> 2 Count  1 1 000  r[1]=count()
> 3 Close  1 0 000
> 4 Copy   1 2 000  r[2]=r[1]
> 5 ResultRow  2 1 000  output=r[2]
> 6 Halt   0 0 000
> 7 Transaction0 0 2 0  01  usesStmtJournal=0
> 8 Goto   0 1 000
> 0
> 
> sqlite> select count() from foo where ref_count = 0;
> --EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX foo_ref_count_idx 
> (ref_count=?)
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 13000  Start at 13
> 1 Null   0 1 100  r[1..1]=NULL
> 2 OpenRead   1 3 0 k(2,,) 02  root=3 iDb=0; 
> foo_ref_count_idx
> 3 Integer0 2 000  r[2]=0
> 4 SeekGE 1 8 2 1  00  key=r[2]
> 5   IdxGT  1 8 2 1  00  key=r[2]
> 6   AggStep0   0 0 1 count(0)   00  accum=r[1] 
> step(r[0])
> 7 Next   1 5 100
> 8 Close  1 0 000
> 9 AggFinal   1 0 0 count(0)   00  accum=r[1] N=0
> 10Copy   1 3 000  r[3]=r[1]
> 11ResultRow  3 1 000  output=r[3]
> 12Halt   0 0 000
> 13Transaction0 0 2 0  01  usesStmtJournal=0
> 14Goto   0 1 000
> 0
> 
> sqlite> select count() from foo where ref_count != 0;
> --EQP-- 0,0,0,SCAN TABLE foo
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 13000  Start at 13
> 1 Null   0 1 100  r[1..1]=NULL
> 2 OpenRead   0 2 0 2  00  root=2 iDb=0; foo
> 3 Rewind 0 8 000
> 4   Column 0 1 200  r[2]=foo.ref_count
> 5   Eq 3 7 2 (BINARY)   54  if r[2]==r[3] 
> goto 7
> 6   AggStep0   0 0 1 count(0)   00  accum=r[1] 
> step(r[0])
> 7 Next   0 4 001
> 8 Close  0 0 000
> 9 AggFinal   1 0 0 count(0)   00  accum=r[1] N=0
> 10Copy   1 4 000  r[4]=r[1]
> 11ResultRow  4 1 000  output=r[4]
> 12Halt   0 0 000
> 13Transaction0 0 2 0  

[sqlite] SQLite does not use opportunity to optimise COUNT() queries using partial indexes

[Paul]

I am not very familiar with the SQLite internals, but I believe that index 
structure is similar 
to that of a table, ie it's a B-TREE with a root containing a node count value. 
If so, then queries
like SELECT COUNT() FROM FOO WHERE <...>;  can be optimised the same way that
queries like SELECT COUNT() FROM FOO; given that condition is equivalent tho 
the 
condition of the partial index.

Example:

sqlite> CREATE TABLE foo(
   ...>   id INTEGER PRIMARY KEY,
   ...>   ref_count INTEGER NOT NULL
   ...> );
sqlite> 
sqlite> CREATE INDEX foo_ref_count_idx ON foo(ref_count) WHERE ref_count = 0;
sqlite> 
sqlite> EXPLAIN SELECT COUNT() FROM foo;
addropcode  p1  p2  p3  p4  p5  
comment   
--  --  --  --  --  --  
--  --
0   Init0   7   0   00  
  
1   OpenRead1   2   0   1   00  
  
2   Count   1   1   0   00  
  
3   Close   1   0   0   00  
  
4   Copy1   2   0   00  
  
5   ResultRow   2   1   0   00  
  
6   Halt0   0   0   00  
  
7   Transactio  0   0   2   0   01  
  
8   TableLock   0   2   0   foo 00  
  
9   Goto0   1   0   00  
  
sqlite> 
sqlite> EXPLAIN SELECT COUNT() FROM foo WHERE ref_count = 0;
addropcode  p1  p2  p3  p4  p5  
comment   
--  --  --  --  --  --  
--  --
0   Init0   13  0   00  
  
1   Null0   1   1   00  
  
2   OpenRead1   3   0   k(2,nil,ni  00  
  
3   Integer 0   2   0   00  
  
4   SeekGE  1   8   2   1   00  
  
5   IdxGT   1   8   2   1   00  
  
6   AggStep 0   0   1   count(0)00  
  
7   Next1   5   1   00  
  
8   Close   1   0   0   00  
  
9   AggFinal1   0   0   count(0)00  
  
10  Copy1   3   0   00  
  
11  ResultRow   3   1   0   00  
  
12  Halt0   0   0   00  
  
13  Transactio  0   0   2   0   01  
  
14  TableLock   0   2   0   foo 00  
  
15  Goto0   1   0   00  
  
sqlite> 
sqlite> EXPLAIN SELECT COUNT() FROM foo WHERE ref_count != 0;
addropcode  p1  p2  p3  p4  p5  
comment   
--  --  --  --  --  --  
--  --
0   Init0   13  0   00  
  
1   Null0   1   1   00  
  
2   OpenRead0   2   0   2   00  
  
3   Rewind  0   8   0   00  
  
4   Column  0   1   2   00  
  
5   Eq  3   7   2   (BINARY)54  
  
6   AggStep 0   0   1   count(0)00  
  
7   Next0   4   0   01  
  
8   Close   0   0   0   00  
  
9   AggFinal1   0   0   count(0)00  
  
10  Copy1   4   0   00  
  
11  ResultRow   4   1   0   00  
 

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 <fwei...@redhat.com> 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 <fwei...@redhat.com> 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”:
>
>   <http://lmdb.tech/doc/>
>
> 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 <slav...@bigfraud.org> wrote:
>
> On 24 Nov 2016, at 11:02am, Florian Weimer <fwei...@redhat.com> 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:
>
> <https://www.sqlite.org/wal.html>
>
> 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 <sh...@google.com> wrote:
> On Tue, Nov 22, 2016 at 10:50 PM, R Smith <rsm...@rsweb.co.za> 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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

[Paul]

> Why don't you just explicitly sort by bar.foo?
> 
> 
> sqlite> EXPLAIN QUERY PLAN SELECT bar.foo as id, foo.baz FROM bar CROSS JOIN 
> foo ON bar.foo = foo.id ORDER BY bar.foo LIMIT 10, 10;
> 0|0|0|SCAN TABLE bar
> 0|1|1|SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?)
> sqlite>

I have explained that in the original message. 'foo' is a master table. There 
are more tables like 'baz' that play role of a partial index.
Query is built dynamically from some collection of data. As a result, various 
combinations of 'foo', 'baz' and co may be in the  resulting query.
The only certain thing is that 'foo' is always in the query, hence the 'id'. 
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

[Paul]

> On 11/17/16, Paul  wrote:
> >
> >> On 11/17/16, Richard Hipp  wrote:
> >> > On 11/17/16, Paul  wrote:
> >> >> That's why there was a LEFT JOIN in the first place, but as it seems,
> >> >> it
> >> >> wasn't that good idea.
> >> >
> >> > Try using CROSS JOIN instead of just JOIN or LEFT JOIN.  The query
> >> > planner in SQLite will not reorder a CROSS JOIN.
> >>
> >> But, as it turns out, if you use CROSS JOIN it goes back to sorting.
> >> Bummer.
> >
> > Yes... unfortunately.
> >
> >>
> >> Ok, a new optimization opportunity.
> >
> > So, in the upcoming releases, some of these queries may get optimised?
> > The ones with the LEFT or wit the CROSS JOIN?
> >
> 
> CROSS JOIN.  I don't think the LEFT JOIN case works, but I will confirm that.
> 

BTW, bar has a FK from foo. When FKs are on I believe SQLite can assume that 
constraints are not violated.
Hence the right part of the LEFT JOIN can never be NULL, specifically the id.
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

[Paul]

 
> On 11/17/16, Richard Hipp  wrote:
> > On 11/17/16, Paul  wrote:
> >> That's why there was a LEFT JOIN in the first place, but as it seems, it
> >> wasn't that good idea.
> >
> > Try using CROSS JOIN instead of just JOIN or LEFT JOIN.  The query
> > planner in SQLite will not reorder a CROSS JOIN.
> 
> But, as it turns out, if you use CROSS JOIN it goes back to sorting.  Bummer.

Yes... unfortunately.

> 
> Ok, a new optimization opportunity.

So, in the upcoming releases, some of these queries may get optimised? 
The ones with the LEFT or wit the CROSS JOIN?

> 
> It gets the correct answer, though, so this is not considered a bug.
> 
> -- 
> 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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

[Paul]

 
> On 11/17/16, Marc L. Allen  wrote:
> > Maybe I'm missing something, but...
> >
> > ORDER BY id
> >
> > Is ordering by the ID the right-hand side of a LEFT join.  As such, it
> > depends on how NULL factors into an ORDER BY.  If NULL comes first, it has
> > to find enough records where the LEFT join fails.
> >
> > Yeah.. I'm probably missing something.
> 
> No, you are correct.  Since the ID column is on the RHS of a LEFT
> JOIN, it cannot use an index for sorting.

Now I see... Thanks for making it clear.

> 
> The foreign key constraint could, in theory, be used by the query
> planner to simplify the LEFT JOIN into an ordinary INNER JOIN.  But
> the query planner in SQLite does not currently know that trick.
> 
> So, one solution is to remove the LEFT keyword from the query in the
> application

Replacing JOIN does not help either:

sqlite> EXPLAIN QUERY PLAN SELECT foo.id FROM bar JOIN foo ON bar.foo = foo.id 
ORDER BY id DESC LIMIT 0, 40;
selectidorder   fromdetail  

--  --  --  

0   0   1   SCAN TABLE foo  

0   1   0   SEARCH TABLE bar USING INTEGER PRIMARY KEY 
(rowid=?) 

My guess is it's because there are no sqlite_stat* tables and SQLite doesn't 
know that bar scan is more efficient.
That's why there was a LEFT JOIN in the first place, but as it seems, it wasn't 
that good idea.


> -- 
> 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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

[Paul]

It's not a solution, because in your example, foo will be scanned until limit 
is reached. 
This may take considerable amount of time, if results are parse.

Also, this solution is totally useless. It's a partial index on 'foo', meaning 
that I can know true or false having only rows of 'foo'.
In the other words: there is no need to do extra searches in 
'partial-index-tables' like bar. The idea is that those tables are orders 
of magnitude  smaller than foo, hence the LEFT JOIN to make it verbose and 
force query planner to scan 'bar' and co first.

 
> Maybe you are looking for
> 
> SELECT id,baz from foo where exists( select 1 from bar where foo = id) ...
> 
> which has the effect of easily extending to an arbitrary number of bar tables 
> via additional exists subqueries that may be connected by logical operators
> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Paul
> Gesendet: Donnerstag, 17. November 2016 13:58
> An: General Discussion of SQLite Database 
> Betreff: [sqlite] Query Planner fails to recognise efficient strategy when 
> '=' condition gives a strong hint
> 
> These are the queries:
> 
> CREATE TABLE foo(
> idINTEGER,
> baz INTEGER,
> PRIMARY KEY(id)
> );
> 
> CREATE TABLE bar(
> foo   INTEGER,
> PRIMARY KEY(foo),
> FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE );
> 
> EXPLAIN QUERY PLAN SELECT foo.id, baz FROM bar LEFT JOIN foo ON bar.foo = 
> foo.id ORDER BY id LIMIT 10, 10;
> 
> selectidorder   fromdetail
> --  --  --  
> ---
> 0   0   0   SCAN TABLE bar
> 0   1   1   SEARCH TABLE foo USING INTEGER PRIMARY 
> KEY (rowid=?)
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
> 
> 'bar.foo = foo.id' gives a strong hint that primary key index of 'bar' can be 
> used for 'ORDER BY id' ordering.
> But it does not happen, hence the plan includes full scan of 'bar' and  TEMP 
> B-TREE construction.
> 
> 
> The reason behind ordering being 'ORDER BY id' instead of 'ORDER BY bar.foo'  
> is that bar pays a role of partial index.
> Database was created just a year before partial indexes were released. Bar is 
> not a single such table, there are more.
> The query is being constructed dynamically and in the end  'ORDER BY id' is 
> appended to ensure that  query is correct no matter how many 
> 'partial-index-tables'  the foo is LEFT JOIN-ed with.
> 
> 
> ___
> 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

[sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

[Paul]

These are the queries:

CREATE TABLE foo(
idINTEGER,
baz INTEGER,
PRIMARY KEY(id)
);

CREATE TABLE bar(
foo   INTEGER,
PRIMARY KEY(foo),
FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE
);

EXPLAIN QUERY PLAN SELECT foo.id, baz FROM bar LEFT JOIN foo ON bar.foo = 
foo.id ORDER BY id LIMIT 10, 10;

selectidorder   fromdetail  

--  --  --  
---
0   0   0   SCAN TABLE bar  

0   1   1   SEARCH TABLE foo USING INTEGER PRIMARY KEY 
(rowid=?)
0   0   0   USE TEMP B-TREE FOR ORDER BY 

'bar.foo = foo.id' gives a strong hint that primary key index of 'bar' can be 
used for 'ORDER BY id' ordering.
But it does not happen, hence the plan includes full scan of 'bar' and  TEMP 
B-TREE construction.


The reason behind ordering being 'ORDER BY id' instead of 'ORDER BY bar.foo'  
is that bar pays a role of partial index.
Database was created just a year before partial indexes were released. Bar is 
not a single such table, there are more.
The query is being constructed dynamically and in the end  'ORDER BY id' is 
appended to ensure that  query is correct
no matter how many 'partial-index-tables'  the foo is LEFT JOIN-ed with.
 
 
___
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 <slav...@bigfraud.org> wrote:
>
> On 16 Nov 2016, at 3:16pm, Andrew Stewart <astew...@arguscontrols.com> 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] Changing ID's to UUID

[Paul Egli]

On Wed, Nov 16, 2016 at 7:59 AM, Keith Medcalf  wrote:

> Using the systemid sequence and the recordid sequence directly however,
> has a 0% probability of collision, so any rational person would use that
> directly and forgo entirely the introduction of uncertainty and bugs using
> "UUID" type crappola will cause.
>

As Dominique said, the issue here is decentralization... and i would add,
particularly in a disconnected environment and/or one with no central
authority. The method you describe does not handle device rollbacks or
cloning.

For example, one of your systems is a mobile device with it's own unique
system id. Periodically, this device broadcasts its inserted data to other
devices. Also, the user backs up the device to their PC every now and then.
At some point the mobile device gets lost or damaged. When they restore
from backup, the last few sequential ids from that system id get reused and
collide. It is also possible to restore from backup to a different device,
even if the original is still alive and well, at which point you have two
different devices with the same system id broadcasting colliding keys.

Theoretically a new, unique system id should be generated any time a system
is backed up or copied anywhere. But when the backup/copying logic is
completely independent and unknowing of your systemid, you are left with
needing to detect if the physical device has changed. This may be
unreliable or impossible on some platforms. And i don't think it would be
possible to detect the case where a rollback happened on the same physical
device.
___
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 <d...@sqlite.org> wrote:
> On 10/29/16, Yuri <y...@rawbw.com> 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 <h...@scigames.at> 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 <sqlite-users@mailinglists.sqlite.org>
> 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] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

[Paul]

Yes, fixed in pre-release snapshot 201610041220.


Thank you.

 
> On 10/5/16, Richard Hipp  wrote:
> > On 10/5/16, Clemens Ladisch  wrote:
> >>   stop
> >>
> >> This looks like a bug.
> >>
> >
> > I think it might be fixed on trunk.  I was just trying to bisect...
> 
> I think this may be a repeat of the problem described by ticket
> https://sqlite.org/src/info/0eab1ac759 and fixed on 2016-09-16 by
> check-in https://sqlite.org/src/info/a92aee5520cfaf85
> 
> -- 
> 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] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

[Paul]

> Paul wrote:
> > I've traced this issue down to the simplest test case:
> >
> > CREATE TABLE IF NOT EXISTS foo
> > (
> >  id  INTEGER,
> >  baz INTEGER,
> >  PRIMARY KEY(id)
> > );
> >
> > CREATE INDEX IF NOT EXISTS baz_foo_idx ON foo(baz, id);
> >
> > CREATE TABLE IF NOT EXISTS bar
> > (
> >  foo INTEGER,
> >  PRIMARY KEY(foo),
> >  FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE
> > );
> >
> > WITH RECURSIVE
> >  cnt(x, y) AS (VALUES(1, 1) UNION ALL SELECT x + 1, x + 1 FROM cnt WHERE x 
> > < 20)
> >  INSERT INTO foo(id, baz) SELECT x, y FROM cnt;
> >
> > WITH RECURSIVE
> >  cnt(x) AS (VALUES(1) UNION ALL SELECT x + 3 FROM cnt WHERE x < 5)
> >  INSERT INTO bar SELECT x FROM cnt;
> >
> > This query takes too much time:
> >
> >  SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) LIMIT 
> > 1;
> 
> EXPLAIN SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) 
> LIMIT 1;
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 24000  Start at 24
> 1 Integer1 1 000  r[1]=1; LIMIT 
> counter
> 2 OpenRead   2 3 0 k(3,,,)02  root=3 iDb=0; 
> baz_foo_idx
> 3 Integer1  2 000  r[2]=1
> 4 Once   0 6 000
> 5 OpenRead   3 4 0 1  00  root=4 iDb=0; bar
> 6 Rewind 3 22000
> 7   Rowid  3 3 000  r[3]=rowid
> 8   IsNull 3 21000  if r[3]==NULL 
> goto 21
> 9   Once   1 11000
> 10  OpenRead   4 4 0 1  00  root=4 iDb=0; bar
> 11  Rewind 4 21000
> 12Rowid  4 4 000  r[4]=rowid
> 13IsNull 4 20000  if r[4]==NULL 
> goto 20
> 14SeekGE 2 202 3  00  key=r[2..4]
> 15  IdxGT  2 202 3  00  key=r[2..4]
> 16  IdxRowid   2 5 000  r[5]=rowid
> 17  ResultRow  5 1 000  output=r[5]
> 18  DecrJumpZero   1 22000  if 
> (--r[1])==0 goto 22
> 19Next   2 15000
> 20  NextIfOpen 4 12000
> 21NextIfOpen 3 7 000
> 22Close  2 0 000
> 23Halt   0 0 000
> 24Transaction0 0 3 0  01  usesStmtJournal=0
> 25TableLock  0 2 0 foo00  iDb=0 root=2 write=0
> 26TableLock  0 4 0 bar00  iDb=0 root=4 write=0
> 27Goto   0 1 000
> 
> If I've understood this correctly, it's the equivalent of this pseudocode:
> 
>   cursor c3 = scan bar
>   for each row in c3:
>   cursor c4 = scan bar
>   for each row in c4:
>   cursor c2 = search (1, c3.rowid, c4.rowid) in baz_foo_idx
>   for each row in c2:
>   output c2.rowid
>   stop
> 
> This looks like a bug.
> 
> As a workaround, drop "id" from the index (the rowid is always part of
> the index anyway).

Thank you for the information! 

We'll stick with the old version for now, until the bug is fixed, since it's 
hard to change database structure since there are millions of copies. Also we 
use 'ORDER BY baz DESC, id DESC' and I'm not sure how will it work out in case 
of index on the single baz field.
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

[Paul]

To add to that, EXPLAIN QUERY PLAN shows that covering index will be used:

sqlite> EXPLAIN QUERY PLAN SELECT id FROM foo WHERE baz = 1 AND id IN 
(SELECT foo FROM bar) LIMIT 1;
selectidorder   fromdetail  
  
--  --  --  
--
0   0   0   SEARCH TABLE foo USING COVERING INDEX 
baz_foo_idx (baz=? AND id=? AND rowid=??)


It is not clear to me, what query algorithm is doing. It seems like it iterates 
through bar and for each row of bar it performs unindexed cross-search in the 
foo. 

However, according to EXPLAIN, it should iterate over the baz_foo_idx index and 
perform indexed cross-searches in the bar.


 
> I've traced this issue down to the simplest test case:
> 
> CREATE TABLE IF NOT EXISTS foo
> (
>  id  INTEGER,
>  baz INTEGER,
>  PRIMARY KEY(id)
> );
> 
> CREATE INDEX IF NOT EXISTS baz_foo_idx ON foo(baz, id);
> 
> CREATE TABLE IF NOT EXISTS bar
> (
>  foo INTEGER,
>  PRIMARY KEY(foo),
>  FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE
> );
> 
> WITH RECURSIVE
>  cnt(x, y) AS (VALUES(1, 1) UNION ALL SELECT x + 1, x + 1 FROM cnt WHERE x < 
> 20)
>  INSERT INTO foo(id, baz) SELECT x, y FROM cnt;
> 
> WITH RECURSIVE
>  cnt(x) AS (VALUES(1) UNION ALL SELECT x + 3 FROM cnt WHERE x < 5)
>  INSERT INTO bar SELECT x FROM cnt;
> 
> SELECT id FROM foo WHERE baz = 9 AND id IN (SELECT foo FROM bar) LIMIT 0, 
> 10;
> 
> 
> This query takes too much time:   
> 
>  SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) LIMIT 
> 1; 
> 
> 
> It seems like execution time is a function of baz: 
> 
> sqlite> .timer on 
> sqlite> SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) 
> LIMIT 1;
> id
> --
> 1 
> Run Time: real 14.839 user 14.836000 sys 0.00
> sqlite> SELECT id FROM foo WHERE baz = 1000 AND id IN (SELECT foo FROM bar) 
> LIMIT 1;
> id
> --
> 1000  
> Run Time: real 1.577 user 1.576000 sys 0.00
> sqlite> SELECT id FROM foo WHERE baz = 100 AND id IN (SELECT foo FROM bar) 
> LIMIT 1;
> id
> --
> 100   
> Run Time: real 0.232 user 0.232000 sys 0.00
> sqlite> SELECT id FROM foo WHERE baz = 10 AND id IN (SELECT foo FROM bar) 
> LIMIT 1;
> id
> --
> 10
> Run Time: real 0.036 user 0.036000 sys 0.00
> sqlite> SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) 
> LIMIT 1;
> id
> --
> 1 
> Run Time: real 0.001 user 0.00 sys 0.00
>  
>  
> ___
> 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] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

[Paul]

I've traced this issue down to the simplest test case:

CREATE TABLE IF NOT EXISTS foo
(
 id  INTEGER,
 baz INTEGER,
 PRIMARY KEY(id)
);

CREATE INDEX IF NOT EXISTS baz_foo_idx ON foo(baz, id);

CREATE TABLE IF NOT EXISTS bar
(
 foo INTEGER,
 PRIMARY KEY(foo),
 FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE
);

WITH RECURSIVE
 cnt(x, y) AS (VALUES(1, 1) UNION ALL SELECT x + 1, x + 1 FROM cnt WHERE x < 
20)
 INSERT INTO foo(id, baz) SELECT x, y FROM cnt;

WITH RECURSIVE
 cnt(x) AS (VALUES(1) UNION ALL SELECT x + 3 FROM cnt WHERE x < 5)
 INSERT INTO bar SELECT x FROM cnt;

SELECT id FROM foo WHERE baz = 9 AND id IN (SELECT foo FROM bar) LIMIT 0, 
10;


This query takes too much time:   

 SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) LIMIT 1; 


It seems like execution time is a function of baz: 

sqlite> .timer on 
sqlite> SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) 
LIMIT 1;
id
--
1 
Run Time: real 14.839 user 14.836000 sys 0.00
sqlite> SELECT id FROM foo WHERE baz = 1000 AND id IN (SELECT foo FROM bar) 
LIMIT 1;
id
--
1000  
Run Time: real 1.577 user 1.576000 sys 0.00
sqlite> SELECT id FROM foo WHERE baz = 100 AND id IN (SELECT foo FROM bar) 
LIMIT 1;
id
--
100   
Run Time: real 0.232 user 0.232000 sys 0.00
sqlite> SELECT id FROM foo WHERE baz = 10 AND id IN (SELECT foo FROM bar) LIMIT 
1;
id
--
10
Run Time: real 0.036 user 0.036000 sys 0.00
sqlite> SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) LIMIT 
1;
id
--
1 
Run Time: real 0.001 user 0.00 sys 0.00
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

[Paul]

I've traced this issue down to the simplest test case: CREATE TABLE IF NOT 
EXISTS foo ( id INTEGER, baz INTEGER, PRIMARY KEY(id) ); CREATE INDEX IF NOT 
EXISTS baz_foo_idx ON foo(baz, id); CREATE TABLE IF NOT EXISTS bar ( foo 
INTEGER, PRIMARY KEY(foo), FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE 
CASCADE ); WITH RECURSIVE cnt(x, y) AS (VALUES(1, 1) UNION ALL SELECT x + 1, x 
+ 1 FROM cnt WHERE x < 2) INSERT INTO foo(id, baz) SELECT x, y FROM cnt; 
WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x + 1 FROM cnt WHERE x < 
1) INSERT INTO bar SELECT x FROM cnt; This query takes too much time: 
SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) LIMIT 1; 
It seems like execution time is a function of baz: sqlite> .timer on sqlite> 
SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) LIMIT 1; 
id -- 1 Run Time: real 14.839 user 14.836000 sys 0.00 sqlite> 
SELECT id FROM foo WHERE baz = 1000 AND id IN (SELECT foo FROM bar) LIMIT 1; id 
-- 1000 Run Time: real 1.577 user 1.576000 sys 0.00 sqlite> SELECT 
id FROM foo WHERE baz = 100 AND id IN (SELECT foo FROM bar) LIMIT 1; id 
-- 100 Run Time: real 0.232 user 0.232000 sys 0.00 sqlite> SELECT 
id FROM foo WHERE baz = 10 AND id IN (SELECT foo FROM bar) LIMIT 1; id 
-- 10 Run Time: real 0.036 user 0.036000 sys 0.00 sqlite> SELECT id 
FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) LIMIT 1; id -- 1 
Run Time: real 0.001 user 0.00 sys 0.00 
___
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 <pontia...@gmail.com> 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 <fluca1...@infinito.it> 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 <mlegenhau...@gmail.com> 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 <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

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 <davebl...@kodi.tv> 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] trouble with sqlite .import

[Aaron Paul]

Hi Richard,

Thanks for your answer.  I had been looking at the csv exported data in Excel 
and it looked right.  

I opened the csv file in a plain text editor and it was as you said, the data 
was already in date format.  Formatting the Column and re-exporting the csv 
file corrected my issue.

Much appreciated.


Aaron Paul
kalisti...@yahoo.com



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

[sqlite] trouble with sqlite .import

[Aaron Paul]

Greetings,

I’m importing a csv file into a newly created table with .import.  One of the 
columns contain a numerical tag which sometimes resembles a date (example 
08-17).  

These are not dates, but the .import is converting 08-17 into 17-Aug

The odd thing is it is doing this even when the date doesn’t make sense,   like 
35-September.

Any thoughts on how to avoid this?  There are about 1500 rows in this table and 
I’d rather import them correctly than try some scheme to clean it up, but 
thoughts on that would be appreciated if nothing else.

Thanks!


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

[sqlite] small website typo

[Paul Joyce]

https://www.sqlite.org/rtree.html has two sections numbered 3.3



Regards, Paul

Paul Joyce
Chief Technology Officer
pjo...@we-do-it.com<https://mail2.we-do-it.com.au/owa/redir.aspx?C=e20caae2ed7b4b799a6203b19486e8d2=mailto%3apjoyce%40we-do-it.com>
m: +61 450 066 131
www.we-do-IT.com<http://www.we-do-IT.com>

we-do-IT Pty Ltd
Level 12, 200 Queen Street
Melbourne VIC 3000
AUSTRALIA
ABN 26 071 972 891
[http://www.we-do-it.com/images/footerlogo.jpg]<http://www.we-do-it.com>
ISO 9001:2008
Certified for the provision of
IT based Consulting
Training and
Product Development Services
[http://www.we-do-it.com/images/iso9001certified.jpg]

This email remains the property of we-do-IT Pty Ltd and is subject to the 
jurisdiction of section 70 of the Australian Crimes Act 1914.
If you have received this email in error, you are requested to contact the 
sender and delete the email.
___
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] update or replace ...

[Paul Egli]

On Thu, Jun 30, 2016 at 8:17 AM, Paul Egli wrote:

> No chance for race conditions if data changes between the operations.
>

I should say "if data changes *or a read happens* between the operations.
:-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Paul Egli]

On Thu, Jun 30, 2016 at 7:48 AM, Olivier Mascia wrote:

> > Le 30 juin 2016 à 13:34, R Smith a écrit :
> >
> > MERGE dbo.xxx AS T
> >  USING dbo.yyy AS S
> > ON T.SomeID = S.SomeID
> >  WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever
> checking is relevant
> > THEN UPDATE SET T.ValueThatNeedsChanging = NewValue
> >  WHEN NOT MATCHED
> >THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns)
> > VALUES () / SELECT clauses
> > ;
>
> I, hopefully, never used such a piece of coding in my whole life and I
> know, now, why all my human body cells refrained me to ever even approach
> MSSQL. :)
>
> The - useful - "upsert" which I can make good use of is the simpler one
> you can find in FirebirdSQL, where it is called UPDATE OR INSERT with
> pretty much the same syntax as an insert.
>
> Easy to read, useful and effective for what use cases it is designed for.
>
>
I've used MERGE INTO a lot and sometimes wished that SQLite had it, but i
can understand why it's not a priority. To me, the real value of MERGE is
the atomicity of the operation. You can perform inserts, updates, and
deletes all in one statement--No chance for race conditions if data changes
between the operations.

In SQLite where only a single writer is allowed at a time and the only
isolation level available Serializable, all you need to do is BEGIN
IMMEDIATE and you get the atomicity you need, even if it seems unnatural
(to me) to use two or three different statements to do what is logically
one operation.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Paul]

Whatever the case, it will be, on average, 1.5x time less efficient than a 
'I-Hope-Would-Be' UPSERT. It all depends on the probability of INSERT to fail.  
In the scenario, where updates are frequent and inserts are rare, INSERT + 
UPDATE approach takes ~2x performance overhead. In most cases we iterate B-Tree 
2 times: once for INSERT, that will fail and second time for UPDATE.  
In reverse scenario, where inserts are frequent and updates are rare, INSERT + 
UPDATE approach will have almost no overhead. 
But how would you know which scenario would take place, to adjust the order of 
calls? Especially when you write a generic Insert-Or-Update method? So, 
whatever strategy you choose (either UPDATE or INSERT is first), statistically, 
on average, you have 50% of cases where UPDATE would be efficient if performed 
first and 50% of cases where the reverse is true. 

If implemented inside the SQLite engine, overhead can be removed. It is 
possible to descend B-Tree once, either to find the row to UPDATE or a hint 
where to INSERT new row. The only thing that puzzles me is why it wasn't 
implemented years ago. 

30 June 2016, 12:01:56, by "Simon Slavin" < slav...@bigfraud.org >: 

On 30 Jun 2016, at 8:24am, Olivier Mascia < o...@integral.be > wrote:

> Of course writing straight code in C/C++ it's rather simple to emulate 
> situations where you want to update some values in an existing row, creating 
> the row if needed.

The standard way of doing this is to do two commands:

1) INSERT without the REPLACE
2) UPDATE

When step 1 fails because the key values already exist you trap this and 
explicitly ignore it in your code (commented, because you're being nice to 
other programmers).  Then it's always the UPDATE which updates the fields you 
care about.

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] 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 <sh...@google.com> wrote:
> On Wed, Jun 29, 2016 at 10:36 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>> On 29 Jun 2016, at 5:45pm, Drago, William @ CSG - NARDA-MITEQ 
>> <william.dr...@l-3com.com> 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 <yongilj...@gmail.com> 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, Максим Дементьев <deme...@hotmail.com>님이 작성:
>
>> 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

[sqlite] Savepoint Questions

[Paul Medynski]

Hello all,

I have some questions and comments about savepoints, documented here:

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

Here are some excerpts from that documentation that are relevant to my
questions/comments:

1.  "The SAVEPOINT command starts a new transaction with a name. The
transaction names need not be unique."

2.  "The ROLLBACK command with a TO clause rolls back transactions going
backwards in time back to the most recent SAVEPOINT with a matching name.
The SAVEPOINT with the matching name remains on the transaction stack, but
all database changes that occurred after that SAVEPOINT was created are
rolled back."

Also (not documented on that page):

3.  Savepoint names cannot be parameterized in statements, which means you
cannot prepare a savepoint statement ahead of time, and supply the
savepoint name at execution time.

Through some experimentation (using v3.11.0), I have found that #1 and #2
together don't work very well:

begin transaction;
  ... Work A ...
  savepoint Foo;
... Work B ...
savepoint Foo;<--- Allowed by #1
  ... Work C ...
rollback to savepoint Foo;<--- Rolls back Work C, but leaves the
   innermost savepoint Foo active due
   to #2

rollback to savepoint Foo;<--- Does nothing - I can no longer
   rollback past the innermost Foo
   without rolling back the entire
   transaction.

Questions:

A)  If #2 is desirable, then why allow non-unique savepoint names within
the same transaction?  It is impossible to rollback the innermost
savepoint with a non-unique name, and later rollback past that savepoint
using the same name.

B)  If #1 is desirable, then why doesn't ROLLBACK TO pop the matched
savepoint off the stack?  Even without considering #1, I'm having trouble
coming up with a scenario in which I would want the matched savepoint to
remain active.

C)  If #1 and #2 are desirable, then why can't I parameterize savepoint
names?  This would allow me to prepare my savepoint statements once, and
execute them with different names, gaining the efficiencies of prepared
statements for these oft-used actions.  Currently, I am forced to generate
unique savepoint names _and_ prepare new statements every time I want to
create a new savepoint, which is doubly inefficient.

Thoughts?
-Paul

___
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 <jonathan-li...@lightpear.com> 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 

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 <j...@antichoc.net> 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

Re: [sqlite] Use journal after program crashed

[Paul Sanderson]

Cecil, Richard

If the WAL file contains multiple commits then it is possible (with
appropriate tools) to use any of commits and effectively roll back to
a chosen previous version. How much of this data you can get at
depends upon the size that the WAL file will auto checkpoint and also
the size of your transactions. Big transactions mean less of them are
stored in a given WAL.

Even when a WAL is in use the occasions where this technique is useful
are limited. Other than big transactions being a problem, if the
application is correctly shut down and the WAL checkpointed it will be
deleted so previous transactions will be lost, i.e. in order to
engineer a situation when a WAL is present I need to kill my
application rather than close it down.

My software can show how the database looked at each available
checkpoint but it is aimed at forensic use rather than actually making
a working database from a previous transaction.

There is an article on my web site that goes into a little detail on
this and shows how you can, in some circumstances, use multiple
transactions in a WAL file to determine when a record was deleted:

http://sandersonforensics.com/forum/content.php?261-Detecting-when-a-record-was-deleted-in-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 25 May 2016 at 04:36, Richard Hipp <d...@sqlite.org> wrote:
> On 5/24/16, Cecil Westerhof <cldwester...@gmail.com> wrote:
>> 2016-05-25 3:49 GMT+02:00 Richard Hipp <d...@sqlite.org>:
>>
>>> On 5/24/16, Cecil Westerhof <cldwester...@gmail.com> wrote:
>>> > Until now I did not need it, but better inform before I do. ;-)
>>> >
>>> > When a program crashes it is possible that you have a journal file with
>>> > things that are not committed. Is there a way to find out what those
>>> > are?
>>> > (And selectively commit?)
>>>
>
> A rollback journal contains the original content of the database.  The
> database file itself contains the partial uncommitted changes.  When
> the automatic rollback occurs, this will restore the database to its
> original pre-transaction state.
>
> If you try to prevent the automatic rollback in order to "keep" some
> of your uncommitted changes you will corrupt your database.
>
> The WAL file contains changes that have already been committed but not
> moved back into the main database, and possible uncommitted changes.
> The committed changes will automatically be moved into the main
> database the next time the database is opened.  You cannot force
> uncommitted changes to commit.  If you try, you will corrupt the
> database.
>
> Bottom line:  The transaction mechanism is there to protect you from
> problems on a power failure.  If you try to defeat that mechanism, you
> will run into corruption problems.
>
> --
> 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] Podcast with Dr Hipp: SQLite history, success and funding

[Harrington, Paul]

I am a big fan of SQLite and the elegance and simplicity of it and fossil. The 
documentation and support are excellent.

Given that the team takes a long-term perspective, I would prefer if the s/w 
was structured in a more modular fashion so that various components were usable 
as libraries e.g.
1) virtual machine 
2) lemon
3) fossil

I don't need them to be distributed as separate libraries but I think it would 
make experimentation much easier.

In any case, this is a very minor nitpick overall and I continue to be 
delighted at how useful SQLite continues to be on a day to day basis after 
almost 10 years of using it.

pjjH

[sqlite] Controlling of check-pointing of WAL Dbs

[Paul Sanderson]

Can you kill the process rather than shut down your application? If
the last connection doesn't get shut then the WAL will remain and will
be in opened (but not played) when the DB is re-opened.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://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 April 2016 at 12:38, Albert Banaszkiewicz
 wrote:
> Hello.
>
> I am new here, thus I would like to apologize in advance if the question I am 
> going to rise was already asked in the past.
>
> I am using SQLite 3.11.1 (actually it is a NDS consortium customized version 
> based on it).
>
> According to documentation, in case of WAL mode enabled databases, final 
> check-pointing is being done when the last connection to DB is about to be 
> closed.
>
> We are running into the scenario, where we would like to have a complete 
> control over the moment when check-pointing happens. This is easy in case of 
> auto-check-pointing, where it can be completely disabled or customized (via 
> hooks).
> However, it is possible that during the component life-time there is going to 
> be no 'appropriate' moment to schedule it and if the user powers device down, 
> we still want to avoid it since modern OSes typically expect running 
> applications to terminate within the specified (several seconds usually) time 
> intervals. Of course, we don't want to loose any data contained in the WAL 
> journal as well and ideally, we would like to be still able to schedule 
> check-pointing in the 'appropriate' time after reboot.
>
> So finally, the questions:
>
> 1. Is there a way to control (i.e. disable) check-pointing happening at DB 
> closure ? (personally, I could not find anything like this in the code but 
> perhaps I missed something)
> 2. If not, are there any plans to introduce it ? Or is it not feasible at all 
> ?
>
> Thank you in advance,
> Albert
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pascal (almost) style BLOBs

[Paul Sanderson]

You could use a binary PList or some other form of structured data -
the BPList blob could then contain, ints, floats, strings byte arrays
or even embedded binary plists. As it is a structured format then to
an extent it is self documenting (in terms of the stored data types)
Paul
www.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 17 April 2016 at 16:27, William Drago  wrote:
> On 4/17/2016 9:45 AM, Simon Slavin wrote:
>>
>> On 17 Apr 2016, at 1:02pm, William Drago  wrote:
>>
>>> I am forced to consider this approach because my function will be
>>> receiving blobs but will have no idea what those blobs are.
>>
>> Why do you care ?  A BLOB is a BLOB.  It doesn't matter whether it's an
>> integer, a string, a car, a peace treaty or the smell of newly cut grass.
>> You store it when told to and retrieve it when told to.
>
>
> My job is to find all the blob columns and unblob them into arrays of
> whatever type they originally were before they were blob'd.
>
>>> if this "feature" is not clearly documented someone in the future will
>>> have a very hard time figuring out why the blobs don't make sense.
>>
>> BLOBs don't make sense.  They're opaque.  You are not meant to understand
>> anything about what's in a BLOB.  That's for the layer of your software
>> which packs the information into a blob or unpacks the blob back into
>> information.
>
>
> Understood. I'm writing the code that does the packing and unpacking. The
> users are designing their own databases. When they store for example, an
> array of 16 bit integers, or an array of 64 bit floats, I want to give them
> the same thing back when they query the database not just an array of bytes
> that they have to decode themselves.
>
> Since I have control over how the blobs are generated, I can easily add a
> type identifier to each blob so that I know how to unblob it.
>
> -Bill
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] minor documentation typo

[Paul van Helden]

Also in partialindex.html:  The WHERE clause may *not* contains subqueries
should be contain

Very happy to have learned about partial indexes today!

On Fri, Mar 25, 2016 at 1:32 AM, Wolfgang Enzinger 
wrote:

> minor documentation typo here:
> https://www.sqlite.org/partialindex.html
>
> 3.0 Queries Using Partial Indexes
> [...]
> Example: Let the index by
>
> should be
>
> Example: Let the index be
>
> Greetings, Wolfgang
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] obtaining bracketing rows where rows are missing

[Paul Sanderson]

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 15 April 2016 at 11:14, Rowan Worth  wrote:
> sqlite> create table a(a integer primary key);
>
> sqlite> SELECT a1.a, a2.a FROM a AS a1, a AS a2
> WHERE a2.a > a1.a
> GROUP BY a1.a
> HAVING a2.a = min(a2.a)
> AND a2.a <> a1.a + 1;
>
> 3|5
> 5|8
> 11|14
>
> IIRC "HAVING x = min(x)" is not portable SQL but it seems to work in sqlite.
>
> -Rowan
>
>
>
> On 15 April 2016 at 18:04, Paul Sanderson 
> wrote:
>
>> I confused myself with that title.
>>
>> I have a DB with 300 odd rows with an integer primary key, there are a
>> few breaks in the key numbering such that I have something like
>>
>> 1
>> 2
>> 3
>> 5
>> 8
>> 9
>> 10
>> 11
>> 14
>>
>> I can generate a list of misisng rows easily enough , eg
>>
>> 4
>> 6
>> 7
>> 12
>> 13
>>
>> but I would like a table of the rows that bracket the missing rows e.g.
>>
>> 3, 5
>> 5, 8
>> 11, 14
>>
>> any ideas how I might go about this?
>>
>> 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
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] obtaining bracketing rows where rows are missing

[Paul Sanderson]

I confused myself with that title.

I have a DB with 300 odd rows with an integer primary key, there are a
few breaks in the key numbering such that I have something like

1
2
3
5
8
9
10
11
14

I can generate a list of misisng rows easily enough , eg

4
6
7
12
13

but I would like a table of the rows that bracket the missing rows e.g.

3, 5
5, 8
11, 14

any ideas how I might go about this?

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

[sqlite] Get fields type

[Paul Sanderson]

Or of course you could query the sqlite_master table
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 April 2016 at 18:08, Paul Sanderson  
wrote:
> you could use SQL i.e.
> select typeof(col1) from table1
>
> would return text, integer etc. for each row in the table for the
> specified column.
>
> problems could arise if rows have NULL for given column or SQLite
> slack of strict column affinity mena sthat someone has dropped a
> string into an integer column.
>
> select typeof(col1) from table where col1 not null limit 1
>
> mightbe a better option
> 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 April 2016 at 17:43, Simon Slavin  wrote:
>>
>> On 13 Apr 2016, at 10:57am, hfiandor  wrote:
>>
>>> I have already obtained from a SQLQuery the field?s list. Now I need to
>>> obtain the field?s type, from the SQLQuery obtained from a SQLite table.
>>
>> In the SQLite API you would use this
>>
>> <https://www.sqlite.org/c3ref/column_blob.html>
>>
>> int sqlite3_column_type(sqlite3_stmt*, int iCol);
>>
>> but you're not using the SQLite API.
>>
>> What programming language are you using.
>> What API are you using to access SQLite ?
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Get fields type

[Paul Sanderson]

you could use SQL i.e.
select typeof(col1) from table1

would return text, integer etc. for each row in the table for the
specified column.

problems could arise if rows have NULL for given column or SQLite
slack of strict column affinity mena sthat someone has dropped a
string into an integer column.

select typeof(col1) from table where col1 not null limit 1

mightbe a better option
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 April 2016 at 17:43, Simon Slavin  wrote:
>
> On 13 Apr 2016, at 10:57am, hfiandor  wrote:
>
>> I have already obtained from a SQLQuery the field?s list. Now I need to
>> obtain the field?s type, from the SQLQuery obtained from a SQLite table.
>
> In the SQLite API you would use this
>
> <https://www.sqlite.org/c3ref/column_blob.html>
>
> int sqlite3_column_type(sqlite3_stmt*, int iCol);
>
> but you're not using the SQLite API.
>
> What programming language are you using.
> What API are you using to access SQLite ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] error messages from SQLite

[Paul Sanderson]

Thanks Tim

I am not concerned with errors from function calls - just errors with
SQL queries at the command line etc.
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 2016 at 11:20, Tim Streater  wrote:
> On 19 Mar 2016 at 10:26, Paul Sanderson  
> wrote:
>
>> When executing a sql query I often find I have made a typo or been a
>> bit dull and I'll get an error message back along the lines of
>>
>> Error: near "text": syntax error
>>
>> Is there a way of expanding on this, for instance adding more of the
>> following text or a character offset. The last time this happened I
>> had a create table... command with about 30 text columns and
>> identifying which "text" string the error referred to took a little
>> while.
>
> In my PHP usage of SQLite, I have a shim around the provided functions which 
> logs the SQL in the event of error. The application then displays it too. The 
> shim also expects a small string to identify which actual SQLite call was 
> involved, so I don't have to look through 140 source modules trying to guess 
> what was going on at the time.
>
> --
> Cheers  --  Tim
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Reserved column names

[Paul Sanderson]

Is there a list of reserved column names

I have seen the list or keywords at the link below

http://www.sqlite.org/lang_keywords.html

but for instance create table (abort int) will work fine.

I know that keywords can be quoted but I am interested in just those
that can be used unquoted (even if not advisable)

Without going through all of them can someone point me at a list?

Thanks

[sqlite] error messages from SQLite

[Paul Sanderson]

When executing a sql query I often find I have made a typo or been a
bit dull and I'll get an error message back along the lines of

Error: near "text": syntax error

Is there a way of expanding on this, for instance adding more of the
following text or a character offset. The last time this happened I
had a create table... command with about 30 text columns and
identifying which "text" string the error referred to took a little
while.

Thanks

[sqlite] How to read data from WAL?

[Paul Sanderson]

I have done some manual WAL decoding for my forensic software that can
identifiy a previous DB state - its fun :)...

(From memory)

To determine which pages belong to the last transaction, you need to :

Read the WAL header to obtain the current salt, then read each wal
frame to determine which frames belong to the current transaction and
then you can get the page number from the frame.

To determine which table/index the page belongs to you will need to
check each b-tree (making sure that you take account of pages that are
in the current WAL checkpoint - i.e. exist in the WAL prior to the
page you are looking for).

You will then need to manually decode each page you have identified in
the WAL to get the rows within the page and then you need to determine
what rows have been added/deleted or altered.

The issues here are that a small change to a table (one row
added/edited) can result in many changes to the pages for that table,
e.g. if a string is appended to then the row may no longer fit in the
page that it currently occupies so the row will be moved. Depending on
the key structure for the table this may also result in other rows
being moved to maintain the structure of the B-Tree. So if you read a
page from the WAL and then find the* previous version of that page
(which may be earlier in the WAL or in the DB) and a row is not
present you will need to parse all the changed pages in that tree to
determine whether it has been deleted or just moved.

(* I say THE previous page because you can and very often do have
multiple copies of a page in the WAL.)

All of the information you need is in the DB file format at the link
provided earlier. It is however not as simple as just reading the
format spec though, it took me a lot of experimentation (including
writing some DB/WAL page level visualisation tools) to work out what
is actually going 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 7 March 2016 at 09:49, Sairam Gaddam  wrote:
>>
>> If you're really sure that the database files are identical, the easiest
>> way to do the replication would be to copy the WAL file.
>>
>> What processing do you want to do?
>>
>
> I want to take note of changes and depending on data, I might replicate in
> some other DB. The database files are not necessarily identical. So mere
> copying wont help me. I should get the data from WAL and based on which
> table(can I get these info from WAL?) the data was changed, I should act
> accordingly. So the only way is to read page and extract information from
> it or any other way??
>
>> Is there any other better way other than WAL?
>>
>> Record the SQL statements that are executed.
>
>
> This is one another case, where some optimization could happen, like for
> example if I run a update query and nothing gets updated in a particular
> table which is similar in 2 different databases, then WAL doesn't have any
> changes on that particular table and no need to execute the same query
> again in different DB!
> And I have some other use cases like those.
> So I would like to see the changes from WAL and based on changes and the
> query, I will proceed.
> So i need a better way to read the contents from the WAL.
> One of them is analyzing the pages from WAL, any others??. In PostgreSQL,
> for WAL, they have a module which will give summary of which row in a
> particular table was changed by reading the contents from WAL. Any module
> like that?? because my work will be easy if I get the info of changes that
> way!!
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

I understand this - but, there always a but, I still would like to do
something. Applying the limit anyway and then telling them the query
has been limited might be a solution.

Time is usually not an issue but as the results are loaded into a grid
for display memory can be the issue.

I'll give it some more thought, but thank you for your answers it has helped.
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 March 2016 at 08:09, R Smith  wrote:
>
>
> On 2016/03/05 6:21 PM, Paul Sanderson wrote:
>>
>> Thanks Richard
>>
>> so suppose I have two tables table1 and table2 each with 1000 rows and
>> say 100 columns some containing large blobs.
>>
>> My user choose a query "select * from table1, table2"
>>
>> I can modify the query and do a "select count(*)  from table1, table2"
>> to determine that 1M rows will be returned and ask him if this is what
>> he really wants to do.
>>
>> But what if he has "select * from table1, table2 limit 1000"
>>
>> In this instance there is no need for me to warn him as he has already
>> applied a limit to the amount of data returned.
>>
>> Any suggestions as to how I can detect this , other than parsing the
>> query for "limit x"
>
>
> I see your point, but I think you are missing something - The amount of rows
> returned by a query is a very bad indicator of how long such a query will
> run - AND, if it is a long query, you will waste twice the time for the end
> user.  Further to this, any amount of queries can be made that takes hours
> to complete, but produces perhaps less than 5 rows, or even just 1.
>
> Similarly, but on a very different tack, you can have a query like "SELECT
> xxx FROM A,B,C,D WHERE " without the limit clause, which might itself
> really only produce 10 or less rows (and perhaps take really long) due to
> the WHERE clause. So even if you did parse the LIMIT clause, you are no
> closer to a true tale.
>
> My strategy has always been to run queries as the user enters them and limit
> them to 1000 or such when a LIMIT is not already present. If I do actually
> produce 1000 rows (which means there may be more) then I prompt the user
> with "This query has more (perhaps significantly more) rows, would you like
> to: Load all (May take a long time), Load Next 1000, Stop here." or some
> other mechanism that achieves the same, though the "load next 1000" option
> is harder to implement when you connect via 3rd party tools.
>
> A last thought, LIMIT is not a Query word or in any way affects the Query
> idea that the Query planner sees, it has no algebraic influence on the
> query. It is an output limitation only. If there is room with 100 people in
> it, and I were to ask the curator: Please give me the names of the people
> limited to 10 values, like so:
> SELECT Name FROM People LIMIT 10
> he would take the list of 100 people and read off the 1st 10 of them. This
> limitation doesn't shorten his list or affects the number of people in the
> room, he just stops reading at some point to please me. So if I ask, how
> many people are in the room, but stop reading at the 10th result. he is just
> going to say: 100 people and stop there. Your limiting clause did not change
> that fact.
>
> Were I to ask: How many people would be in the list if I asked you for a
> list of people limited to 10 output values? He would answer 10 - of course,
> that is why this query works:
> SELECT COUNT(*) FROM (SELECT * FROM People LIMIT 10);
>
> Cheers,
> Ryan
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

So

select count (*) from ...

Doesn't require a crystal ball and returns the number of rows but

Select count(*) from ... Limit x

Dies need one, interesting

On Saturday, 5 March 2016, Igor Tandetnik  wrote:

> On 3/5/2016 12:20 PM, Paul Sanderson wrote:
>
>> If it computes many things and doesn't return many rows then I don't
>> really care. I only want to know how many rows a query will return
>> before I execute it in full.
>>
>
> That would require a crystal ball or a time machine. Absent those, it's
> impossible to know how many rows a query will produce until it actually
> runs and produces them.
>
> Again, you can wrap an arbitrary query like this:
>
> select count(*) from (
>   select whatever ...
> );
>
> This query always returns exactly one row and one column, with the value
> being the number of rows that the inner query would have returned. Is this
> not what you want? Of course, it would take approximately as much time to
> run this new query as it would the original query.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at 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] Changing the default page_size in 3.12.0

[Paul Sanderson]

Records can span multiple pages and the record itself is unrelated to
the block size.

For smaller tables indexes an increase in page size can increase the
DB size - consider a DB with 1024 byte pages and one table that
occupies 6 pages - i.e. 6K. in a new 4096 page size DB it would take
up two pages i.e. 8K.

Without further investigation I suspect that savings would come in
when you have larger tables with relatively large record payloads, I
would think that the free space in a page would be half the average
record length and so by multiplying the page size by 4 would decrease
the free (wasted) space (due to not enough space for another complete
record) also by a factor of four. This is grossly over simplified
though and takes no account of presumably less overflow pages due the
larger page size, pointer maps, and free space due to deleted records.

In short (no testing done) I am not sure that for most implementations
that increasing page size would make any significant space savings and
may likely as mentioned above increase the DB size..

The savings as mentioned earlier are IO related due to matching the
page size to the underlying hardwares block size and for larger
payloads ensuring less IO due to no or lesss overflow pages.




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 March 2016 at 18:43, Jim Callahan  
wrote:
>
>
>> From: Domingo Alvarez Duarte
>> I have a question, is it enough to vacuum a database to update to the new
>> page size ?
>>
>
> Apparently all you need to is "pragma page_size=4096; vacuum;" using the
>> appropriate page size.
>> This makes very easy to convert any(all) database(s) with a single
>> t
>> command from the command-line, like so (Win7 example):
>> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"
>
>
> I am confused.
> The "page" is an in-memory structure; it is how large a chunk the program
> reads from the file (analogous to how large a scoop or shovel), So, unless
> one is using an index, how would the on disk structure be impacted? How
> does SQLite handle the last block (does it expect the file to be an even
> multiple of the block size, or does it accept that the last read might be
> less than a full block?).
>
> For example, if one encountered an old file, would it be enough to rebuild
> the indices?
>
> Or is it simply a matter of closing the file? (close the file with the old
> version and open the file with the new?).
>
> I haven't read the source code so I don't know what assertions, checks or
> assumptions SQLite
> uses.
>
> Jim
>
>
>
> On Sat, Mar 5, 2016 at 11:04 AM,  wrote:
>
>> From: Domingo Alvarez Duarte
>>> I have a question, is it enough to vacuum a database to update to the new
>>> page size ?
>>>
>>
>> Apparently all you need to is "pragma page_size=4096; vacuum;" using the
>> appropriate page size.
>> This makes very easy to convert any(all) database(s) with a single command
>> from the command-line, like so (Win7 example):
>>
>> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

> Just allow them to cancel the query; use sqlite3_progress_handler().

I am using a third party data access component so this is out of my control.

If it computes many things and doesn't return many rows then I don't
really care. I only want to know how many rows a query will return
before I execute it in full. If I can tell that it is going to be a
huge return data set then I can prompt them to change it.

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

[Paul Sanderson]

Thanks Richard

so suppose I have two tables table1 and table2 each with 1000 rows and
say 100 columns some containing large blobs.

My user choose a query "select * from table1, table2"

I can modify the query and do a "select count(*)  from table1, table2"
to determine that 1M rows will be returned and ask him if this is what
he really wants to do.

But what if he has "select * from table1, table2 limit 1000"

In this instance there is no need for me to warn him as he has already
applied a limit to the amount of data returned.

Any suggestions as to how I can detect this , other than parsing the
query for "limit x"

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 5 March 2016 at 16:12, Richard Hipp  wrote:
> On 3/5/16, Paul Sanderson  wrote:
>> Clemens,
>>
>> Tim has the same issue as me, while
>>
>>   SELECT EXISTS (select status from mytable where status=1);
>>
>> works the portion in brackets is still executed in full and this is
>> what we are trying to avoid.
>
> The query planner in SQLite, while not brilliant, is smart enough to
> know that it can stop and return true as soon as it sees the first row
> from the query inside of EXISTS().
>
> So if there is an index on mytable.status, the above will be very
> query.  Or if there are instances of status=1 early in mytable, the
> above will be quick.  But if mytable.status is never 1 and if there is
> no index on mytable.status, then SQLite will scan the entire table to
> verify that status is never 1 before returning 0.  I don't see anyway
> around the latter case, however.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Clemens,

Tim has the same issue as me, while

  SELECT EXISTS (select status from mytable where status=1);

works the portion in brackets is still executed in full and this is
what we are trying to avoid.

I am not working directly with the sqlite library but rather though a
DAC so I am also limited (no pun intended) 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 5 March 2016 at 15:12, Clemens Ladisch  wrote:
> Tim Streater wrote:
>> On 05 Mar 2016 at 13:05, Clemens Ladisch  wrote:
>>> But why do you want to determine the number of rows in the first place?
>>
>> In my case I want to know whether at least one row exists that has a certain 
>> column which has a given value. At the minute I do this:
>>
>>   select count(*) from mytable where status=1 limit 1;
>>
>> Would this:
>>
>>   select count(*) from (select status from mytable where status=1 limit 1);
>>
>> or some other query be faster. Really, I'd like SQLite to stop after finding 
>> one row.
>
> So you want to know whether such a row exists? Then ask for that:
>
>   SELECT EXISTS (select status from mytable where status=1);
>
> This returns a boolean value.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

That doesn't work for me:

I am trying to avoid (select col1, col2, ... colx ) from ...

and want just a row count which is much less resource intensive.

I have users who need to execute queries that generate a large number
of rows, I have other users that create queries that generate a large
number of rows by accident (i.e. cross joins). I have no control over
what they enter but I want to do something to warn them that their
query might result in an over large number of rows and a limit clause
might be a solution.

if however they have already applied a limit clause there is no need
for a warning.

Before I am asked - for some of my users huge data sets might be OK as
they can use my application to massage the data before creating a
report or exporting the results set.

parsing the query and identifying the limit clause is an option, but I
hope there might be a simpler 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 5 March 2016 at 13:05, Clemens Ladisch  wrote:
> Paul Sanderson wrote:
>> I am trying to determine before a query is executed how many rows will
>> be returned. the following query works as expected
>>
>> select count(*) from table
>>
>> but
>>
>> select count(*) from table limit 100
>>
>> still returns the number of rows in the table not the number of rows
>> that would be returned by the query.
>
> "The query" is the one that has the "limit" clause.  What else should
> the database execute?
>
> As documented <http://www.sqlite.org/lang_select.html>, the result rows
> are generated before the LIMIT clause is applied.
>
> To determine how many rows would be returned by an arbitrary query, use:
>
>   SELECT COUNT(*) FROM (SELECT ...);
>
>
> But why do you want to determine the number of rows in the first place?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mixing text and numeric values in comparisons

[Paul van Helden]

Hi again,

CREATE TABLE test (A TEXT);
INSERT INTO test VALUES ('1');
SELECT * FROM test WHERE A=1; -- returns 1
SELECT * FROM test WHERE A=1.0; -- returns nothing because the RHS is
converted to '1.0'

This would not have been a problem if CAST(1.0 AS TEXT) produced '1' (like
FloatToStr does in Delphi)

If this seems silly, what I'm doing above is simulating a
sqlite3_bind_double if you change 1.0 to ? (Integers stored in float fields
will never find a match stored in a text field, without casting)

However:

CREATE TABLE tleft (Txt TEXT);
INSERT INTO tleft VALUES ('1');
CREATE TABLE tright (Flt REAL);
INSERT INTO tright VALUES (1.0);
SELECT * FROM tleft, tright WHERE Txt=Flt; -- returns a match!

Is it because of the following from https://www.sqlite.org/datatype3.html

>  (As an internal optimization, small floating point values with no
> fractional component and stored in columns with REAL affinity are written
> to disk as integers in order to take up less space and are automatically
> converted back into floating point as the value is read out. This
> optimization is completely invisible at the SQL level and can only be
> detected by examining the raw bits of the database file.)
>

Does the cast of Flt to TEXT use the stored integer value in the
comparison? I want my query to do the same!

I therefore make my case that SELECT '1'=1.0 should return 1, and not 0 as
it does at the moment, i.e. CAST(1.0 AS TEXT) must produce '1'

Paul.

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

[Paul Sanderson]

I am trying to determine before a query is executed how many rows will
be returned. the following query works as expected

select count(*) from table

but

select count(*) from table limit 100

still returns the number of rows in the table not the number of rows
that would be returned by the query. is this operation correct?

I can of course parse the query to determine any limit on what will be
returned, but wondered if this is a bug or a design feature.




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] Mixing text and numeric values in comparisons

[Paul van Helden]

On Fri, Mar 4, 2016 at 4:23 PM, Igor Tandetnik  wrote:

> On 3/4/2016 9:15 AM, Paul van Helden wrote:
>
>> So I have to detect integers in order to avoid the .0
>>
>
> WHERE CAST(A as integer) = 1
>
>
Not quite going to work either. I should have said the values are _nearly_
always integers, but they can be text (alphanumeric). So the general case
is to compare anything to anything where there is a high likelihood of
values being integers.

[sqlite] Mixing text and numeric values in comparisons

[Paul van Helden]

Hi Richard,

Thank you for the quick reply. I cannot change the type of the column. The
tables are user defined and need to be matched with other user defined
tables. The values are always integers but can be in any type of column.

So I have to detect integers in order to avoid the .0 How do others on this
list deal with this problem?

Paul.

>
> What is the best way to check for integers? (fabs(val)<LLONG_MAX) &&
> modf(val, )

[sqlite] Mixing text and numeric values in comparisons

[Paul van Helden]

Hi All,

CREATE TABLE test (A TEXT);
INSERT INTO test VALUES ('1');
SELECT * FROM test WHERE A=1; // returns 1
SELECT * FROM test WHERE A=1.0; // returns nothing

How is the second comparison done?

I realize that you can have double values that are too large to convert to
int64, but as I understand, SQLite does convert doubles to integer values
for storage when they are small enough.

The problem presented itself when binding integers from an Access table
with sqlite3_bind_double because the Access table has the integers in a
float field. (Apparently common). I had to change my library to check
integers in double fields and then bind with sqlite3_bind_int64 in order to
hit a result with WHERE.

What is the best way to check for integers? (fabs(val)

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

[Paul Sanderson]

What is the likely max length of a row in your table? if your row
length is less than page length-35 then it will fit in to one page.

The amount of wasted page depends on what fraction of a page size your
typical record is.

If you have a record that is 600 bytes long on a DB with 1024 byte
page size then you would "waste" about 1024-600 = 424 bytes per record
. 

If your page size is 4096 then for the same record size you fit 6
records into the page so would waste about 4096 - (600*6) = 496 / 6 =
82 bytes per record.

Pick your page size appropriately although there is usually more to
think about with page size than just wasted space.
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 March 2016 at 08:58, Simon Slavin  wrote:
>
>> On 3 Mar 2016, at 8:16am, Eric Grange  wrote:
>>
>>> All BLOBs are stored inline.  The only complication comes when the total
>>> row length is longer than a page.
>>
>> Ok thanks!
>>
>> That is besides my original question but what is the complication in that
>> case?
>> Is the blob partially stored in the row, or is some indirection to other
>> pages used, with only some pointer stored in the row?
>
> SQLite database files are split up into pages.  Every page in a database is 
> the same number of bytes long.  Every page belongs to the header, or to free 
> space, or to a specific TABLE or INDEX.
>
> SQLite stores all the values for a row together.  Changing the value of any 
> column for a row requires rewriting the entire row, and then the row entry 
> for every index on that table which was changed. [1]
>
> Ideally a row of data would fit in a page.  This simplifies the job of 
> reading a row from disk or changing the data in a row.  SQLite does this 
> where possible.
>
> But it's possible for a row to be longer than a page.  This happens 
> especially where the row contains a long string or a long BLOB.  In that case 
> SQLite has to fetch more than one page from disk just to access the values 
> for that row.  And it's possible for a BLOB to be so long that it requires 
> one or more pages entirely to itself.  So the word 'inline' is a little 
> tricky here because there is more than one 'line' involved.
>
> Simon.
>
> [1] Handwave a few exceptions.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why is a separate journal file needed ?

[Paul Sanderson]

WAL files can be many times bigger than a database - the default WAL
checkpoint size is when the WAL grows to > 1000 pages. You can get a
DB (for example) with 100 pages and a WAL of 1000 (with multiple
different copies of the same page).
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 26 February 2016 at 08:46, Rowan Worth  wrote:
> On 24 February 2016 at 21:49, Richard Hipp  wrote:
>
>> On 2/24/16, Simon Slavin  wrote:
>> > Why can't the information which SQLite
>> > stores in a journal file be put in the database file ?
>>
>> Doing so would double the size of the database file.  Every database
>> file would contain extra space (normally unused) set aside for the
>> journal.
>>
>
> Surely this extra space would be proportional to the size of the largest
> [recent] transaction, rather than the size of the database itself? To be
> specific I'm thinking of rollback journals, I don't have experience with
> WAL mode.
>
> -Rowan
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why is a separate journal file needed ?

[Paul Sanderson]

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

The additional data stored with rollback journals (page number before
and checksum after) and wals (wal frame before) would also need to be
implemented else where and thereofre add extra logic.

It sounds doable but more complex.
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 February 2016 at 15:46, Igor Tandetnik  wrote:
> On 2/24/2016 10:08 AM, Stephen Chrzanowski wrote:
>>
>> IMO, all that, plus the fact that you have an easy roll back mechanism.
>> Anything that needs to be put in the database is external to the pristine
>> database.  Lock the database with a transaction, fill up the journal, the
>> power goes out, your pristine database isn't touched.
>
>
> Rollback journal works the other way round. New data is written to database
> file; previous content of overwritten pages is copied to journal file.
> Committing a transaction is fast - simply delete the journal. Rolling back
> (e.g. after power failure) means copying original pages from the journal
> back to the database file.
>
> In the world you describe - how would you implement committing a
> transaction? At some point, something must write to the database file; at
> that point, the file is no longer "pristine". What happens if power goes out
> in the middle of this?
>
> Note also that a typical application commits much more often than it rolls
> back, so the system should be designed to make the former fast; the latter
> can be slow.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why is a separate journal file needed ?

[Paul]

Good point!

I can assume that the problem would be a sparsity of database file. If you mix 
normal pages and journal then database will be fragmented. 

You can't avoid it. Even if you start writing journal at the end of file, 
hoping that you can later truncate it at commit, there is no way to know how 
far, from the current end of file, should journal file start. At some point, if 
transaction is big enough, new allocated pages in database will hit the spot in 
file where journal pages are. 


24 February 2016, 14:53:55, by "Simon Slavin" :

>   In case you're new to this group, please understand that I'm experienced 
> with SQLite and I understand how it works and how it uses journal files.
> 
> Can someone explain to me -- or point at a web page which does -- why a 
> separate journal file is needed.  Why can't the information which SQLite 
> stores in a journal file be put in the database file ?  Given how SQLite 
> structures its database files this would presumably mean that the main 
> database would have journal pages as well as table and index pages.  Or that 
> there would be 'stable' table and index pages and 'journal' table and index 
> pages. 
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Storing Images in Blobs - Best Practices

[Paul Sanderson]

How do you know that it is sqlite that is corrupting the images, could
your conversion to nsdata (or elsewhere prior to writing to teh DB) be
the culprit?

Given your schema if a record has become corrupt then the following
two columns (the data for which which would be stored contiguously
after the blob) would also be corrupt.


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 17 February 2016 at 22:35, Simon Slavin  wrote:
>
> On 17 Feb 2016, at 10:23pm, Rooney, Joe  
> wrote:
>
>>  1.  The database table has four fields: Employee GUID (varchar), 
>> EmployeePic (Blob), isPicAvail (Bool) and picDate (int).
>
> Store the BLOB field at the end of the row.  This will make accessing the two 
> fields currently after it faster.
>
>> I use the same UPDATE call, as above in 4. I don't delete the previous Blob, 
>> just assuming that SQLite will take care of those details.
>
> It should do.
>
>> The process works well except that occasionally two images end up getting 
>> corrupted. Not just one, it always happens in pairs. I get the upper part of 
>> both images, with just the lower part showing garbage.
>
> Run PRAGMA integrity_check() on the database and tell us if it finds errors.
>
> If possible copy the database to a computer and download and use the analysis 
> utility from the SQLite download page.
>
>> The questions:
>>
>>  *   Is this general approach wise?
>>  *   Should I clear the old Blob before rewriting the new image, or should I 
>> simply delete the old rec and write a new one?
>>  *   Should I store the mime string coming back in the JSON return rather 
>> than converting the string to an image, and then converting it every time we 
>> need to display the image.
>>  *   Should I simply store a string link to the image and load that whenever 
>> it is needed.
>
> Yes.
> No, you should be able to just rewrite it.
> You wrote you were storing the NSData.  That's not just the bytes of the 
> image itself.  It doesn't matter which you do but be clear which you're 
> doing.  Storing MIME is probably a waste of space.
> I think you should store the content, not a link.
>
> Questions from me:
>
> What language are you writing in ?
> What API are you using to access SQLite ?
> Check the first few bytes of a BLOB and be certain that they are the first 
> few bytes of what would be in an image file stored on disk.
> How are you writing your BLOB ?  Are you writing the whole field at once or 
> are you using the specialist BLOB calls ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT OR IGNORE query inside of a body of trigger, fired by INSERT OR REPLACE query, works as if it was INSERT OR REPLACE.

[Paul]

Oh, thanks for pointing me to this statement! 
Didn't know that 'OR IGNORE' is an alias of 'ON CONFLICT IGNORE'.


17 February 2016, 17:32:32, by "Dan Kennedy" :

>   On 02/17/2016 08:47 PM, Paul wrote:
> > Let's say I have a following database structure:
> >
> > CREATE TABLE properties
> > (
> >   name  TEXT NOT NULL,
> >   value TEXT,
> >   PRIMARY KEY(name)
> > ) WITHOUT ROWID;
> >
> > CREATE TABLE foo
> > (
> >   idTEXT NOT NULL,
> >   PRIMARY KEY(id)
> > );
> >
> > CREATE TRIGGER foo_inserted
> >AFTER INSERT ON foo
> >   BEGIN
> > INSERT OR IGNORE INTO properties(name, value) VALUES('foo_inserts', 0);
> > UPDATE properties SET value = value + 1 WHERE name = 'foo_inserts';
> >   END;
> >
> > With a clean database I perform set of queries:
> >
> > INSERT OR REPLACE INTO foo(id) VALUES(1);
> > INSERT OR REPLACE INTO foo(id) VALUES(2);
> > INSERT OR REPLACE INTO foo(id) VALUES(3);
> >
> ...
> > I've made different test cases and came to a conclusion that 'OR IGNORE' 
> > clause inside
> > a query within a body of trigger suddenly works as if it was 'OR REPLACE'.
> 
> The ON CONFLICT clause of the outer statement overrides the ON CONFLICT 
> clause of the statement within the trigger:
> 
> https://www.sqlite.org/mark/lang_createtrigger.html?However+if+an*used+instead
> 
> (scroll the page down a bit to see the highlighted statement)
> 
> Dan.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT OR IGNORE query inside of a body of trigger, fired by INSERT OR REPLACE query, works as if it was INSERT OR REPLACE.

[Paul]

Let's say I have a following database structure: 

CREATE TABLE properties 
( 
?name? TEXT NOT NULL, 
?value TEXT, 
?PRIMARY KEY(name) 
) WITHOUT ROWID; 

CREATE TABLE foo 
( 
?id??? TEXT NOT NULL, 
?PRIMARY KEY(id) 
); 

CREATE TRIGGER foo_inserted 
? AFTER INSERT ON foo 
?BEGIN 
?? INSERT OR IGNORE INTO properties(name, value) VALUES('foo_inserts', 0); 
?? UPDATE properties SET value = value + 1 WHERE name = 'foo_inserts'; 
?END; 

With a clean database I perform set of queries: 

INSERT OR REPLACE INTO foo(id) VALUES(1); 
INSERT OR REPLACE INTO foo(id) VALUES(2); 
INSERT OR REPLACE INTO foo(id) VALUES(3); 

SELECT * FROM foo; 
id??? 
-- 
1 
2 
3 

SELECT * FROM properties; 
name value 
---? -- 
foo_inserts? 1? 

The results are not very much expected. If I try different example, again with 
a clean database I get: 

INSERT INTO foo(id) VALUES(1); 
INSERT INTO foo(id) VALUES(2); 
INSERT INTO foo(id) VALUES(3); 

SELECT * FROM foo; 
id??? 
-- 
1 
2 
3? 

SELECT * FROM properties; 
name value 
---? -- 
foo_inserts? 3 

Everything works perfectly. This is also true for INSERT OR IGNORE INTO 
foo. 

I've made different test cases and came to a conclusion that 'OR IGNORE' clause 
inside 
a query within a body of trigger suddenly works as if it was 'OR REPLACE'. 

[sqlite] Performance comparison between SQLite and SQL Server?

[Paul van Helden]

The quality and helpfulness of this mailing list makes me wish for a
SQHeavy...