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

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

Re: [sqlite] BLOB sizes beyond 2GB?

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

Re: [sqlite] BLOB sizes beyond 2GB?

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

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

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

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

. 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

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

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

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

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

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

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

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

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

[sqlite] Documentation error

erpret 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

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

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

Re: [sqlite] (dot) output call locks up when used on a named pipe

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

Re: [sqlite] Out of date online documents

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] (dot) output call locks up when used on a named pipe

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

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

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

[sqlite] Out of date online documents

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

Re: [sqlite] Foreign key child index question

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

Re: [sqlite] Foreign key child index question

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 migh

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

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

Re: [sqlite] Foreign key child index question

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

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

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

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

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

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

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

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

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

Re: [sqlite] Pragma to flag unknown pragma?

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

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

> 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

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

> 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

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

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

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

> 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

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

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

[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

Re: [sqlite] Issue with Malformed table

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

Re: [sqlite] Changing ID's to UUID

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

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

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

Re: [sqlite] Order of fields for insert

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

Re: [sqlite] Order of fields for insert

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

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

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

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

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

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

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

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

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)

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

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

Re: [sqlite] Searching for a percent symbol

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

Re: [sqlite] Searching for a percent symbol

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

Re: [sqlite] Searching for a percent symbol

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

[sqlite] Searching for a percent symbol

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

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

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

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

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

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

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

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

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

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

087123 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

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

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

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

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

Re: [sqlite] Using Bitwise Logic In Query

How does this work for you to get all the even rows SELECT ROWID FROM table WHERE ROWID & 0x01 = 0x00 Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Too

Re: [sqlite] trouble with sqlite .import

. 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

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

[sqlite] small website typo

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

Re: [sqlite] Document ommision bind-parameter description

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

[sqlite] Document ommision bind-parameter description

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

Re: [sqlite] update or replace ...

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 s

Re: [sqlite] update or replace ...

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

Re: [sqlite] update or replace ...

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

Re: [sqlite] Bad db feature request

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

Re: [sqlite] Bad db feature request

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

Re: [sqlite] WAL: no schema after close

. 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

[sqlite] Savepoint Questions

e 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

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

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

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

Re: [sqlite] Use journal after program crashed

-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

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

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.

[sqlite] Controlling of check-pointing of WAL Dbs

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

[sqlite] Pascal (almost) style BLOBs

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

[sqlite] minor documentation typo

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

[sqlite] obtaining bracketing rows where rows are missing

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

[sqlite] obtaining bracketing rows where rows are missing

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

[sqlite] Get fields type

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

[sqlite] Get fields type

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

[sqlite] error messages from SQLite

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

[sqlite] Reserved column names

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

[sqlite] error messages from SQLite

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

[sqlite] How to read data from WAL?

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

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

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

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

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

[sqlite] Changing the default page_size in 3.12.0

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

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

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

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

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 add

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

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

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

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

[sqlite] Mixing text and numeric values in comparisons

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

. 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

[sqlite] Mixing text and numeric values in comparisons

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 value

[sqlite] Mixing text and numeric values in comparisons

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

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

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

ually 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

[sqlite] Why is a separate journal file needed ?

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

[sqlite] Why is a separate journal file needed ?

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

[sqlite] Why is a separate journal file needed ?

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

[sqlite] Storing Images in Blobs - Best Practices

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

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

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

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

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

[sqlite] Performance comparison between SQLite and SQL Server?

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

<    1   2   3   4   5   6   7   8   >