Re: [sqlite] more efficient JSON encoding: idle musing

[Paul van Helden]

>
>
> I experimented with a number of similar ideas for storing JSON when I
> was first designing the JSON components for SQLite.  I was never able
> to find anything that was as fast or as compact as just storing the
> original JSON text.
>

 I've also done a lot of experiments and was surprised at how little a
binary encoding saves in space. Also tried with lookups for keys, but the
lookup values quickly become close to the size of the keys (if not larger)
if keys are mostly shortish.

I'd be happy with a JSON5-like ability to have the quotes on keys optional
if they contain no spaces and no special characters. Seems to reduce the
data size quite significantly.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Either a bug or I don't understand SQL update

[Paul Ausbeck]

I've been using sqlite for some time but haven't used SQL update until 
recently. On my first real use, I've encountered a problem that I can't 
understand at all.

To pare this down, I have two tables, citytax and taxitems2, and I've attached 
a database with just these two tables, total size ~12Kbytes.

I perform the following update using the sqlite3 command line interface:

update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = 
citytax.number and taxitems2.taxrate = "M");

The result is that the salesm  column is set to NULL for all citytax rows. However, I 
would expect that rows 9 and 22 would be non-NULL. I get the the same all-NULL result for 
a taxrate of "G" as well, though I would expect that rows 14, 19, and 58 would 
be non-NULL.

The strangest part is that if I specify taxrates of "P", "R", or "", I get the result that I expect. I've included a listing of 
the taxitems2 table below. As one can see, there are rows where the second column, taxrate, is all of "", "P", "R", 
"G", and "M". The last column is the amount column, non-null for all rows. I must admit that I don't understand what is going on here.

Perhaps even stranger is if I extract the select from the update and give it an 
appropriate row number, I get the expected non-NULL result:

sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and 
taxitems2.taxrate = "M";
1176.72

I've included the contents of the taxitems2 table as well as the schema for 
tables taxitems2 and citytax below. One the one hand, this seems almost 
certainly to be a bug, but on the other, it is so basic, that I can't believe 
that I'm the first to encounter it.

Any assistance that anyone can provide would of course be much appreciated. I'm 
using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both 
environments behave the same in this regard.

As I was writing I had yet another test idea:

update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = 
citytax.number and unicode(taxitems2.taxrate) = 77); /* taxrate = "M" */

and

update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = 
citytax.number and unicode(taxitems2.taxrate) = 71); /* taxrate = "G" */

Both of these updates perform as expected.

Regards,

Paul Ausbeck

sqlite> select * from taxitems2;
WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5
SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0
USX,P,58,14,1,0,0,0,0,0,0,7104.0
TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945
TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945
TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978
TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978
USX,P,58,14,1,0,0,0,0,0,0,5446.4
USX,P,58,14,1,0,0,0,0,0,0,1657.6
AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3
PEM,G,58,10,2,0,0,0.75,2,1.5,4,8
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
HVY,R,58,14,1,0,0,0,0,0,0,72.5
HVY,R,58,14,1,0,0,0,0,0,0,176.4
VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,168
CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2
CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0
WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,65
WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0
WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9
WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4
WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72
WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3
ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394
DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4
DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8
DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7

sqlite> .schema taxitems2
CREATE TABLE taxitems2(
  code TEXT,
  taxrate TEXT,
  citynum INT,
  countynum INT,
  statenum INT,
  citym NUM,
  cityg NUM,
  countym NUM,
  countyg NUM,
  statem NUM,
  stateg NUM,
  amount
);
sqlite> .schema citytax
CREATE TABLE citytax(
  number INT,
  code INT,
  g NUM,
  m NUM,
  comment TEXT,
  taxg NUM,
  taxm NUM,
  salesg NUM,
  salesm NUM,
  salesr NUM,
  salesp NUM,
  stamp INT
);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Paul van Helden]

"In-process" describes it best for me.



On Wed, Jan 29, 2020 at 8:16 AM Darren Duncan 
wrote:

> The concepts I like the best so far are "in-process" or "integrated" or
> something library-themed. -- Darren Duncan
>
> On 2020-01-27 2:18 p.m., Richard Hipp wrote:
> > For many years I have described SQLite as being "serverless", as a way
> > to distinguish it from the more traditional client/server design of
> > RDBMSes.  "Serverless" seemed like the natural term to use, as it
> > seems to mean "without a server".
> >
> > But more recently, "serverless" has become a popular buzz-word that
> > means "managed by my hosting provider rather than by me."  Many
> > readers have internalized this new marketing-driven meaning for
> > "serverless" and are hence confused when they see my claim that
> > "SQLite is serverless".
> >
> > How can I fix this?  What alternative word can I use in place of
> > "serverless" to mean "without a server"?
> >
> > Note that "in-process" and "embedded" are not adequate substitutes for
> > "serverless".  An RDBMS might be in-process or embedded but still be
> > running a server in a separate thread. In fact, that is how most
> > embedded RDBMSes other than SQLite work, if I am not much mistaken.
> >
> > When I say "serverless" I mean that the application invokes a
> > function, that function performs some task on behalf of the
> > application, then the function returns, *and that is all*.  No threads
> > are left over, running in the background to do housekeeping.  The
> > function does send messages to some other thread or process.  The
> > function does not have an event loop.  The function does not have its
> > own stack. The function (with its subfunctions) does all the work
> > itself, using the callers stack, then returns control to the caller.
> >
> > So what do I call this, if I can no longer use the word "serverless"
> > without confusing people?
> >
> > "no-server"?
> > "sans-server"?
> > "stackless"?
> > "non-client/server"?
> ___
> 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 regression with FTS4: slower path is chosen

[Paul]

I has been a while without response, so I just bumping this message.


19 July 2019, 14:21:27, by "Paul" :

> I have a test case when the regression can be observed in queries that
> use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. 
> For some reason the planner decides to search non-FTS table first then
> scan the whole FTS table. Version 3.22.0 is the last unaffected, while
> issue is still present in HEAD.
> 
> Probably it has something to do with a fact that, according to EXPLAIN,
> new version of planner ignores LEFT join and considers it just a JOIN.
> At least it feels that way, anyway.
> 
> Test case:
> 
> 
> CREATE VIRTUAL TABLE search USING FTS4(text);
> 
> WITH RECURSIVE
>  cnt(x) AS (
> SELECT 1
> UNION ALL
> SELECT x+1 FROM cnt
>  LIMIT 2000
>  )
> INSERT INTO search(docid, text) SELECT x, 'test' || x FROM cnt;
> 
> CREATE TABLE foo(s_docid integer primary key, bar integer);
> 
> WITH RECURSIVE
>  cnt(x) AS (
> SELECT 1
> UNION ALL
> SELECT x+1 FROM cnt
>  LIMIT 2000
>  )
> INSERT INTO foo(s_docid, bar) SELECT x, 1 FROM cnt;
> 
> .timer on
> 
> -- Fast
> SELECT COUNT() FROM search LEFT JOIN foo  
>  ON s_docid = docid 
>  WHERE bar = 1 AND search MATCH 'test*';
> 
> -- Fast
> SELECT COUNT() FROM foo 
>  WHERE bar = 1 
>  AND s_docid IN (
>SELECT docid FROM search WHERE search MATCH 'test*'
>  );
> 
> -- Create index, as some real-life queries use searches by `bar`
> CREATE INDEX foo_bar_idx ON foo (bar);
> 
> -- Slow
> SELECT COUNT() FROM search LEFT JOIN foo
>  ON s_docid = docid 
>  WHERE bar = 1 AND search MATCH 'test*';
> 
> -- As fast as before (current workaround)
> SELECT COUNT() FROM foo 
>  WHERE bar = 1 
>  AND s_docid IN (
>SELECT docid FROM search WHERE search MATCH 'test*'
>  );
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query planner regression with FTS4: slower path is chosen

[Paul]

I have a test case when the regression can be observed in queries that
use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. 
For some reason the planner decides to search non-FTS table first then
scan the whole FTS table. Version 3.22.0 is the last unaffected, while
issue is still present in HEAD.

Probably it has something to do with a fact that, according to EXPLAIN,
new version of planner ignores LEFT join and considers it just a JOIN.
At least it feels that way, anyway.

Test case:


CREATE VIRTUAL TABLE search USING FTS4(text);

WITH RECURSIVE
 cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt
 LIMIT 2000
 )
INSERT INTO search(docid, text) SELECT x, 'test' || x FROM cnt;

CREATE TABLE foo(s_docid integer primary key, bar integer);

WITH RECURSIVE
 cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt
 LIMIT 2000
 )
INSERT INTO foo(s_docid, bar) SELECT x, 1 FROM cnt;

.timer on

-- Fast
SELECT COUNT() FROM search LEFT JOIN foo  
 ON s_docid = docid 
 WHERE bar = 1 AND search MATCH 'test*';

-- Fast
SELECT COUNT() FROM foo 
 WHERE bar = 1 
 AND s_docid IN (
   SELECT docid FROM search WHERE search MATCH 'test*'
 );

-- Create index, as some real-life queries use searches by `bar`
CREATE INDEX foo_bar_idx ON foo (bar);

-- Slow
SELECT COUNT() FROM search LEFT JOIN foo
 ON s_docid = docid 
 WHERE bar = 1 AND search MATCH 'test*';

-- As fast as before (current workaround)
SELECT COUNT() FROM foo 
 WHERE bar = 1 
 AND s_docid IN (
   SELECT docid FROM search WHERE search MATCH 'test*'
 );



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

[sqlite] Will ROWID be required to be unique in contentless Full text search

[paul tracy]

My question is whether or not I can rely on my use of non-unique rowids in FTS5 
contentless tables to remain unenforced into the future of SQLite or am I 
exploiting an oversight that may be resolved in the future?
Details ...
I have a key-value pair SQLite table (called props) used for storing properties 
of objects. It has the following columns: objID, propCode, propVal
objID + propCode is my primary key.

I have created a contentless FTS5 virtual table as follows: create virtual 
table fti using FTS5(ftiCode, ftiVal, content='');
for every record in props table a corresponding insert is made to the fti 
table, I insert the props.objID into fti.rowid.
Since any given objID in my props table can have multiple props I wind up 
making multiple inserts into the fti table with a non-unique rowid (since I'm 
using rowid to store the objid). This works perfectly fine in all use cases so 
my app runs perfectly. 
All of the docs on rowid say that it is unique but in the FTS5 contentless 
table this unique requirement is not enforced. Since I have to provide all 3 of 
the initial fields whenever a delete is required then this also works to remove 
only the exact record from FTI. I view the rowid in the FTS contentless table 
as simply holding whatever value I send to it and it will return that value in 
a field named rowid but that field isn't really a rowid in the true sense. If 
this interpretation will remain valid into the future then my implementation is 
perfect.

For the curious: Why am I doing this instead of something more traditional? 
Mostly it has to do with elimination of duplicate returns from the FTS5 search. 
But also contentless to save space. When I query the FT index I am interested 
in which objects contain given property values. My table has millions of rows 
so I'm sensitive to space and speed.
If I insert the following rows into fti rowid, ftiCode, ftiValue:
3, 7, A3, 8, B
and then I ... SELECT rowid FROM FTI where FTI MATCH A OR B … I only get one 
row returned with rowid of 3. This was a surprise but was exactly what I 
wanted. A more traditional approach to using FTI would have resulted in two 
rows returned both with the same ID field (object ID in my case). By exploiting 
the fact that the FTS5 does not enforce unique rowids I save myself the expense 
of using DISTINCT.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database Encryption (System.Data.SQLite)

[Paul]

As I was using the unchanged nuget package I assumed it would be a “default” 
encryption as it isn’t something I compiled or changed?

Tithras

Sent from my iPhone

> On 1 Apr 2019, at 15:33, Simon Slavin  wrote:
> 
>> On 1 Apr 2019, at 3:30pm, Mattock Paul  wrote:
>> 
>> Thanks, do we have any idea on what Algorithm is used?
> 
> Since more than one algorithm is available, this would be something selected 
> by the software you were using.  So I can only suggest you read the source 
> code for the bit of the software which created the database.
> 
> It might be possible for someone clever with encryption to work it out, given 
> a copy of your database, but that's not something I've ever tried.
> ___
> 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] Database Encryption (System.Data.SQLite)

[Mattock Paul]

Simon,
Thanks, do we have any idea on what Algorithm is used?

Tithras

> On 01 April 2019 at 14:28 Simon Slavin  wrote:
> 
> 
> On 1 Apr 2019, at 1:15pm, Mattock Paul  wrote:
> 
> > Just to confirm I am using the nuget package (System.Data.SQLite v1.0.109.2)
> 
> This uses PCL Crypto, which in turn accesses crypto implemented in PCL itself 
> rather than implmenting its own.  A list of crypto methods it supports, 
> tabled against OS, can be found here:
> 
> <https://github.com/AArnott/PCLCrypto/wiki/Algorithms-X-platforms-support>
> 
> Also see the 'Legal Key Sizes' link on that page.
> 
> So the bad part is that your cryto is done entirely outside SQLite so we 
> don't know much about it.  But the good part is that there is a source that 
> does.
> ___
> 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] Database Encryption (System.Data.SQLite)

[Mattock Paul]

Jim,
Thanks for the information.

Just to confirm I am using the nuget package (System.Data.SQLite v1.0.109.2) 
and my sqlite database is encrypted without any additional modules associated 
with my release. A code snippet of the section which handles the initial 
database encryption is as follows:

using (SQLiteConnection localDBConnection = new SQLiteConnection(connection))
{
 localDBConnection.SetPassword("password");
 localDBConnection.Open();

 using (SQLiteCommand command = new SQLiteCommand(localDBConnection))
 {
  command.CommandText = createTableSNBuild;
  command.ExecuteNonQuery();
 }
}

Regards,
Tithras

> On 01 April 2019 at 12:52 Jim Borden  wrote:
> 
> 
> From looking at the System.Data.SQLite source (someone please correct me if I 
> am wrong)
> 
> It would use whatever cipher was provided to it via the native library that 
> it was deployed with.  It's designed with sqlite encryption extension in mind 
> but I suppose in theory it would work with any implementation that properly 
> implements the sqlite3_key APIs / PRAGMAs.  As far as I can tell it is not a 
> foregone conclusion in the C# as to what algorithm is used.
> 
> The one on Nuget just ships with the vanilla sqlite which has no encryption 
> support.  Decompiling and searching for the sqlite3_key binding shows that it 
> is not present in the library (which makes sense since it is guarded by an 
> #if in the source base)
> 
> On 2019/04/01 18:27, "sqlite-users on behalf of Mattock Paul" 
>  pmatt...@ntlworld.com> wrote:
> 
> All,
> 
> Would anyone be able to confirm what cipher is used for encrypting an 
> SQLite database when password="" is used?
> 
> 
> I have seen old posts online which state its 128bit but assume this is 
> now wrong and am after completing a design document which requires I state 
> the encryption level.
> 
> 
> Regards,
> 
> Tithras
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> Privacy Policy<http://www.couchbase.com/privacy-policy>
> Marketing 
> Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
> ___
> 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] Database Encryption (System.Data.SQLite)

[Mattock Paul]

All,

Would anyone be able to confirm what cipher is used for encrypting an SQLite 
database when password="" is used?


I have seen old posts online which state its 128bit but assume this is now 
wrong and am after completing a design document which requires I state the 
encryption level.


Regards,

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

[sqlite] System.Data.Sqlite Encryption Cipher

[Mattock Paul extern]

Hi,
Would anyone be able to confirm what encryption cipher is used when 
SetPassword="" is set on database creation?

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

Re: [sqlite] Regarding CoC

[Paul]

If my opinion has any value, even though being atheist, I prefer this CoC 100 
times over
the CoC that is being currently pushed onto the many open-source communities, 
that was
created by some purple-headed feminist with political motives. This one does 
not have 
any hidden intentions (at least, it seems so to me, knowing that you're honest 
person).


23 October 2018, 03:47:29, by "Jim Dossey" :

> I think Donald Knuth would approve.
> 
> On Mon, Oct 22, 2018 at 8:01 PM D Burgess  wrote:
> 
> > > The CoC is fine. Don't change it.
> > +1
> > ___
> > 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] Compiler warning - signed unsigned mismatch if enable sorter references defined

[paul tracy]

Compiling the amalgamation (some previous versions including the current 
Version 3.25.2 (2018-09-25)) generates the following warning with 
SQLITE_ENABLE_SORTER_REFERENCES defined. 

The build works fine and always has so this is really just benign.

The warning is: sqlite3.c(106816): warning C4018: '>=': signed/unsigned mismatch

The code block looks like this:

#ifdef SQLITE_ENABLE_SORTER_REFERENCES
    if( v>=sqlite3GlobalConfig.szSorterRef ){
  pCol->colFlags |= COLFLAG_SORTERREF;
    }
#endif

v is an int. szSorterRef is u32
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[Paul Sanderson]

On 2 August 2018 at 20:08, Keith Medcalf  wrote:

Further to this, you can "emulate" the current structure by creating
multiple databases each containing only the tables needed for that "bit" of
your application.  For example, you can create a customers.db containing
the customers table and all the indexes associated with the customers
table.  You can also create an accounting.db containing all the accounting
tables (but not the customers table) and all the indexes that belong to
those tables.  Lather, Rinse, Repeat.  Create multiple databases and do not
duplicate table names -- that is each table goes in one and only one
database).

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

---

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

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

Paul
www.sandersonforensics.com
SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Full text serch - Matching all except chosen

[paul tracy]

Forgive me if this is the wrong way to do this but I'm a newbie.
I am using version 3.24.0 with FTS5
Is there a way to perform a full text search that returns every row except 
records matching a specified query string?
The following does not work because of a syntax error as the syntax requires a 
query string before the NOT operator: … MATCH 'NOT blah';The following also 
does not work because the * operand cannot be used by itself: … MATCH '* NOT 
blah';In desperation I tried the following which returned data but a seemingly 
random set of data: MATCH NOT 'blah';
I wound up using something like this …
SELECT * FROM mytable WHERE id NOT IN (SELECT id from FullTextIndex WHERE 
FullTextIndex MATCH 'blah');
I think this is much slower on large databases than a full FTS-based query but 
maybe I'm wrong and this is as fast as it gets.
Any insight would be greatly appreciated.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing index size

[Paul Sanderson]

If I understand correctly then changing from a base64 index to a blob
containing the raw bytes would save 25%

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

On 30 July 2018 at 09:32, Eric Grange  wrote:

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

Re: [sqlite] Minimum Delta Time

[Paul Sanderson]

How about just using a trigger to check if endtime is < starttime+10 and
updating if it fires

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

On 11 July 2018 at 17:09, David Raymond  wrote:

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

Re: [sqlite] Fwd: ON CONFLICT parser bug?

[Paul van Helden]

>
>
>
> A SELECT in an UPSERT should always contain a WHERE clause.  Like this:
>
>INSERT INTO t1(a) SELECT a FROM t2 WHERE true ON CONFLICT(A) DO NOTHING;
>
> Without the WHERE clause, the ON keyword tricks the parser into
> thinking it is processing an "ON" join constraint on the FROM clause.
> The WHERE clause resolves the ambiguity.  The extra no-op WHERE clause
> is omitted from the prepared statement by the query optimizer and
> hence does not slow down the execution of the statement.
>
> This is a known limitation of the UPSERT syntax.  I had intended to
> document it, but I apparently forgot to do so, or at least I cannot
> find where I documented it right this second.  It is a messy situation
> that comes about due to our use of an LALR(1) parser (parsers with
> more lookahead also run slower) and by the need to provide full
> backwards compatibility with older versions of SQLite.
>
>
Thanks. Easy to live with! The first time I used UPSERT with a SELECT it
was a JOIN ending with USING(). Worked like a charm!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: ON CONFLICT parser bug?

[Paul van Helden]

Hi,

I've used the new UPSERT with success with a complex SELECT, then got to
scratch my head for a while on a much simpler query, so assuming this might
be a bug:

CREATE TABLE T2 (A INTEGER PRIMARY KEY);
INSERT INTO T2 VALUES (1);
CREATE TABLE T1 (A INTEGER PRIMARY KEY);
INSERT INTO T1 VALUES (1);
INSERT INTO T1 (A) SELECT A FROM T2 ON CONFLICT(A) DO NOTHING;

An SQL error has occurred: near "DO": syntax error

sqlite3_prepare_v2 actually stops the statement after DO, so that pzTail
points to " NOTHING".

More complex SELECT statements (seems anything after the table name, e.g.
WHERE or LIMIT will suffice) works:

INSERT INTO T1 (A) SELECT A FROM T2 WHERE 1=1
ON CONFLICT(A) DO NOTHING

I'm sure others have found this already?

Regards,

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

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

[Paul Sanderson]

Easier and pretty obvious :) Thanks Keith



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

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

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

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

[Paul Sanderson]

Thanks Ryan

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

Paul

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

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

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

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

[Paul Sanderson]

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



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

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

> with status_one as (
>   select *
>   from names
>   where status = 1
> ),
> one_names as (
>   select distinct name
>   from status_one
> )
> select min(id), status, name
> from names
> where status = 0
>   and name not in one_names
> group by status, name
>
> union all
>
> select * from status_one;
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Paul Sanderson
> Sent: Friday, June 29, 2018 11:50 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] unique values from a subset of data based on two fields
>
> I have a table
>
> Create table names (id int, status int, name text)
>
>
>
> 1, 1, 'paul'
>
> 2, 1,'helen'
>
> 3, 0, 'steve'
>
> 4, 0, 'steve'
>
> 5, 0, 'pete'
>
> 6, 0, 'paul'
>
>
>
> I want a query that returns all of the records with status = 1 and unique
> records, based on name, where the status =0 and the name is not in the list
> status=1
>
>
>
> So from the above I would want to see
>
>
>
> 1, 1, paul
>
> 2, 1, helen
>
> 3, 0, steve (or 4, 0, steve)
>
> 5, 0, pete
>
>
>
> I could do something like
>
>
>
> Select * from names where status = 1 or name not in (select name from names
> where status = 1)
>
>
>
> But this gets both rows for steve, e.g.
>
>
>
> 1, 1, paul
>
> 2, 1, helen
>
> 3, 0, steve
>
> 4, 0, steve
>
> 5, 0, pete
>
> while I am not bothered about which of the two steves I get back, I must
> have all occurences of names with status = 1
>
> I am probably missing somethng obvious
>
> Paul
> www.sandersonforensics.com
> SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

I have a table

Create table names (id int, status int, name text)



1, 1, 'paul'

2, 1,'helen'

3, 0, 'steve'

4, 0, 'steve'

5, 0, 'pete'

6, 0, 'paul'



I want a query that returns all of the records with status = 1 and unique
records, based on name, where the status =0 and the name is not in the list
status=1



So from the above I would want to see



1, 1, paul

2, 1, helen

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

5, 0, pete



I could do something like



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



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



1, 1, paul

2, 1, helen

3, 0, steve

4, 0, steve

5, 0, pete

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

I am probably missing somethng obvious

Paul
www.sandersonforensics.com
SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

 On 9 June 2018 at 16:17, Jay Kreibich  wrote:

>
> Without digging through some detailed docs, I’m pretty sure empty string
> and NULL require the same amount of storage space.  If not, the difference
> is maybe one byte.
>
>
You are correct Jay

The serial types NULL, 0 and 1 each have a serial type 0, 8 & 9
respectively and there is no data stored for the column - the content of
the column is inherent in the serial types array.

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



Paul
www.sandersonforensics.com
SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ROWID....

[Paul Sanderson]

As ROWID is often an alias for an integer primary key then it  needs to be
able to represent both negaitive and positive integers other wise you
restrict the range of an integer PK.



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

On 9 June 2018 at 13:03, Kevin Benson  wrote:

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

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

[Paul Sanderson]

The structure of a record is shown in the graphic at this link which is
from my book SQLite Forensics:



www.sqliteforensics.co.uk/pics/table_leaf_format.png



As long as ALL of the serial types (i.e. all of the cell pointer array) is
held in the main B-tree (i.e. not an overflow page) which unless you have a
huge number of columns in a table will pretty much always be the case, then
finding out the offset to a given field in the payload is a case of just
reading and determining the size of each serial type until you get to the
entry you want. If the payload data you want is in the same page, before or
after a blob, then accessing it is just a case of reading from the current
page buffer. It is only when the blob is so large that the data you want is
in an overflow page that things slow down, it shouldn't matter if some of
the record overflows, as long as the data you want is on the b-tree leaf
page.



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



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


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

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

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

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

[Paul Sanderson]

Have you made sure aut_ovacuum is disabled?

pragma *auto_vacuum  * = 0

have you got a nice large pagesize if your records are that big?



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

On 4 June 2018 at 13:01, Olivier Mascia  wrote:

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

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

[Paul Sanderson]

If you are doing each update in a separate transaction it will be much
slower than wrapping them in a single transaction.

See the faq here, it refers to inserts but updates will be the same.

http://sqlite.org/faq.html#q19


Cheers
Paul


On Wed, 30 May 2018 at 09:34, Torsten Curdt  wrote:

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

Re: [sqlite] ALTER TABLE

[Paul Sanderson]

To modify column names if you want to live dangerously you could try
something like this

PS C:\sqlite> sqlite3 writ.db
SQLite version 3.23.1 2018-04-10 17:39:29
Enter ".help" for usage hints.
sqlite> create table test (c1, c2, c3);
sqlite> insert into test values(1, 2, 3);
sqlite> pragma writable_schema = yes;
sqlite> update sqlite_master set sql = 'create table test (d1, d2, d3)'
where name = 'test';
sqlite> .quit

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

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

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

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

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

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

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

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

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





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

On 22 May 2018 at 20:34, Igor Korot <ikoro...@gmail.com> wrote:

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

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

[Paul Sanderson]

or more in tune with the default value in the schema



SELECT CASE count(*) WHEN 0 THEN 'Ok' ELSE action END FROM blocked WHERE
email = 'rwillett.dr...@example.com';


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

On 16 May 2018 at 09:22, Rob Willett <rob.sql...@robertwillett.com> wrote:

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

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

[Paul Sanderson]

How about something like

SELECT CASE count(*) WHEN 0 THEN 'Discard' ELSE 'Ok' END FROM blocked WHERE
email = 'rwillett.dr...@example.com';


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

On 16 May 2018 at 10:35, Abroży Nieprzełoży <
abrozynieprzelozy314...@gmail.com> wrote:

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

Re: [sqlite] question about DB

[Paul Sanderson]

Try it in a command line shell with the timer on

.timer on



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

On 14 May 2018 at 17:27, Simon Slavin <slav...@bigfraud.org> wrote:

> On 14 May 2018, at 3:03pm, sebastian bermudez <sebastianbermu...@live.com>
> wrote:
>
> > the question is, there are some order of penalty in attach (2+)
> databases vs one big DB ?
>
> Three databases of 2.4Gb size ?  SQLite is designed to expect this.  Slow
> down for attaching compared with one big database is very very small.  No
> problem.
>
> Make sure you have indexes to help the WHERE and ON clauses.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This list is getting spammed again

[Paul Sanderson]

ahh disregard - it was there

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

On 11 May 2018 at 10:33, Simon Slavin <slav...@bigfraud.org> wrote:

> On 11 May 2018, at 10:26am, Paul Sanderson <sandersonforens...@gmail.com>
> wrote:
>
> > Would it be possible for an admin to run a script that sent an individual
> > email (e.g. different number in subject) to each user on the list and see
> > who is sending the spam based on the replies?
>
> My guess is that the spammer harvests our addresses from nabble, or from
> the Mailman archive.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This list is getting spammed again

[Paul Sanderson]

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

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

On 11 May 2018 at 10:33, Simon Slavin <slav...@bigfraud.org> wrote:

> On 11 May 2018, at 10:26am, Paul Sanderson <sandersonforens...@gmail.com>
> wrote:
>
> > Would it be possible for an admin to run a script that sent an individual
> > email (e.g. different number in subject) to each user on the list and see
> > who is sending the spam based on the replies?
>
> My guess is that the spammer harvests our addresses from nabble, or from
> the Mailman archive.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This list is getting spammed again

[Paul Sanderson]

Would it be possible for an admin to run a script that sent an individual
email (e.g. different number in subject) to each user on the list and see
who is sending the spam based on the replies?

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

On 11 May 2018 at 10:12, Cecil Westerhof <cldwester...@gmail.com> wrote:

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

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

[Paul Sanderson]

Or

SELECT count(*) AS Total,
  CASE
WHEN Sum(used = 'unused') > 0 THEN Sum(used = 'unused')
  END AS NotUsed
FROM quotes

There might be a more succinct way


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

On 9 May 2018 at 21:31, Igor Tandetnik <i...@tandetnik.org> wrote:

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

Re: [sqlite] How to Handle BigInt

[Paul Sanderson]

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

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

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

On 1 May 2018 at 17:05, Gerry Snyder <mesmerizer...@gmail.com> wrote:

> From the docs:
>
> *INTEGER*. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
> bytes depending on the magnitude of the value.
>
> So perhaps you should have said " SQLite integers are all up to 64 bit."
>
> Gerry
>
> On Tue, May 1, 2018 at 8:56 AM, Paul Sanderson <
> sandersonforens...@gmail.com
> > wrote:
>
> > SQLite integers are all 64 bit - I don't about postgress, so unless
> > postgress allows integers bigger than 64 bit, and you use them, you
> should
> > be OK with your table definitions above.
> >
> > Paul
> > www.sandersonforensics.com
> > skype: r3scue193
> > twitter: @sandersonforens
> > Tel +44 (0)1326 572786
> > http://sandersonforensics.com/forum/content.php?195-SQLite-
> > Forensic-Toolkit
> > -Forensic Toolkit for SQLite
> > email from a work address for a fully functional demo licence
> >
> > On 1 May 2018 at 16:29, dmp <da...@dandymadeproductions.com> wrote:
> >
> > > Hello,
> > >
> > > Given a conversion from a database table that contains BigInt, long,
> > > field from PostgreSQL to a SQLite similar table.
> > >
> > > CREATE TABLE postgresqltypes (
> > >   data_type_id serial,
> > >   bigInt_type bigint)
> > >
> > > CREATE TABLE sqlitetypes (
> > >   data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
> > >   int_type INTEGER)
> > >
> > > How to store, for values outside range for Integer,
> > > String or Real?
> > >
> > > danap.
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Handle BigInt

[Paul Sanderson]

SQLite integers are all 64 bit - I don't about postgress, so unless
postgress allows integers bigger than 64 bit, and you use them, you should
be OK with your table definitions above.

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

On 1 May 2018 at 16:29, dmp <da...@dandymadeproductions.com> wrote:

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

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

[Paul Sanderson]

how about

select date(dttm) dt,max(i) from foo group by date(dttm) order by 1;


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

On 21 March 2018 at 15:30, Ron Watkins <rwatk...@dssolutions.com> wrote:

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

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

[Paul Sanderson]

Actually it is totally different Chris

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

an INT primary key cannot be autoincrementing

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

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

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

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

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

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

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

On 20 March 2018 at 16:44, Chris Locke <sql...@chrisjlocke.co.uk> wrote:

> >  some people seem to think that an int primary key can be auto
> incrementing, it can't
>
> But it works in the same way  sort of.  Its auto incrementing, with the
> caveat that if the last row is deleted, the previous number will be used
> again.  Depending on the database schema, this may or may not cause issues.
>
>
> Thanks,
> Chris
>
>
> On Tue, Mar 20, 2018 at 9:45 AM, Paul Sanderson <
> sandersonforens...@gmail.com> wrote:
>
> >  I read that - but my point was more that some people seem to think that
> an
> > int primary key can be auto incrementing, it can't.
> >
> >
> > SQLite version 3.18.0 2017-03-28 18:48:43
> > Enter ".help" for usage hints.
> > Connected to a transient in-memory database.
> > Use ".open FILENAME" to reopen on a persistent database.
> > sqlite> create table test (id integer primary key autoincrement);
> > sqlite> create table test2 (id int primary key autoincrement);
> > Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
> > sqlite>
> >
> > Paul
> > www.sandersonforensics.com
> > skype: r3scue193
> > twitter: @sandersonforens
> > Tel +44 (0)1326 572786
> > http://sandersonforensics.com/forum/content.php?195-SQLite-
> > Forensic-Toolkit
> > -Forensic Toolkit for SQLite
> > email from a work address for a fully functional demo licence
> >
> > On 20 March 2018 at 08:48, R Smith <ryansmit...@gmail.com> wrote:
> >
> > >
> > > On 2018/03/20 10:24 AM, Paul Sanderson wrote:
> > >
> > >> Autoincrement can ONLY be used with an integer primary key
> > >>
> > >
> > > I think Peter's shouting is more about the inability to distinguish via
> > > SQL or Pragma between an INTEGER PRIMARY KEY and an INT PRIMARY KEY,
> both
> > > of which are of course integer and can be auto-incrementing, but only
> one
> > > of which is an alias for rowid.
> > >
> > >
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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


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

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

On 20 March 2018 at 08:48, R Smith <ryansmit...@gmail.com> wrote:

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

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

[Paul Sanderson]

Autoincrement can ONLY be used with an integer primary key

https://sqlite.org/autoinc.html

On Tue, 20 Mar 2018 at 06:50, Peter Halasz <pe...@becauseofgames.com> wrote:

> When needed I use a declared INTEGER PRIMARY KEY.
> >
> >
> MAYBE THAT WOULD HAVE BEEN IN THE SURVEY TOO BUT I GUESS THERE WAS NO WAY
> TO INCLUDE A SMALL PIECE OF SQL TO RELIABLY CHECK FOR INTEGER PRIMARY KEY
>
> YES I AM SHOUTING
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT

[Paul]

I would suggest using the PostgreSQL way: 
  https://www.postgresql.org/docs/9.5/static/sql-insert.html

 INSERT INTO ...
   ON CONFLICT [()] DO UPDATE 
   SET foo = ... , bar = ... ;

This approach is really cool, because we can specify which key is more
important and discard other conflicts as an error. For example, given
the following table:

CREATE TABLE foo(
idINTEGER NOT NULL,
foo_key   TEXT NOT NULL,
some_data TEXT,

PRIMARY KEY(id),
UNIQUE (foo_key)
);

INSERT INTO foo(id, foo_key, some_data) VALUES(1, "XXX", "...");
INSERT INTO foo(id, foo_key, some_data) VALUES(2, "YYY", "...");

If we are performing a query:


INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
 

Which record should we update and what columns? 

Having the ability to specify a specific column on which the conflict
is actually an acceptable event lets the developer to make a decision
how to resolve it:

INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
 


19 March 2018, 18:41:34, by "R Smith" :

> On 2018/03/19 1:50 PM, Olivier Mascia wrote:
> >
> > I don't know what any 'standard' SQL defines about this.
> > I know that FirebirdSQL (where I came from, before meeting SQLite) did/does 
> > it this way:
> >
> > UPDATE OR INSERT INTO
> > {tablename | viewname} [()]
> > VALUES ()
> > [MATCHING ()]
> > [RETURNING  [INTO ]]
> 
> Quite right, and the statement in MSSQL is even more convoluted, which, 
> if it was in SQLite like this, would require a dynamically created SQL 
> statement that is worse than simply computing an UPDATE and an INSERT - 
> which a previous poster already lamented.
> 
> My suggestion for UPSERT would be the very simple already SQLite-like 
> syntax of:
> 
> INSERT OR UPDATE INTO t (k1, k2, ... , kn,  f1, f2, ... , fn)
> followed by the usual VALUES clause or SELECT query.
> 
> Any record found to exist with the exact same value in the Primary Key 
> field(s) [ k1 .. kn ] has all other fields (that are NOT Primary Key 
> fields) updated to the new values, and if no such record is found, the 
> row simply gets inserted.  If the inserted row OR updated values cause 
> any other constraint to break, then FAIL hard, the same way (and 
> possibly with the same ON CONFLICT options) as any other single INSERT 
> or UPDATE would be subjected to.
> 
> This is far better than INSERT OR REPLACE since there is no delete, and 
> no multiple-row delete on constraint violations.
> It is simple in terms of converting any current INSERT OR REPLACE query 
> to an INSERT OR UPDATE query requires changing 1 word only.
> 
> Triggers should fire for ON INSERT and ON UPDATE according to whatever 
> actually is required during the operation.
> 
> Adding this has no backward compatibility to break, this did not exist 
> before and it is not schema-specific.
> 
> 
> One possible added refinement might be an optional second field-group 
> that should be ignored over-and-above the PK fields during the UPDATE. 
> (During the INSERT of course all fields MUST be added).
> 
> 2 ways this can be done easily:
> 
>   A - Use a separate 2nd prototype group for Non-Updating fields, Like 
> this perhaps:
> 
> INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) NOT (f1, f2) VALUES 
> (...);  -- This example updates only f3 and f4 if the record already exists.
> 
> I just picked "NOT" as the separator, perhaps "KEEP" gives better 
> clarity (see next example), but any good word would do.
> Primary key fields pk1 and pk2 along with specified non-updating fields 
> f1 and f2 are all ignored during an update, but still used during an 
> insert.
> Adding a PK field to the second set is a no-op as some might like it for 
> legibility. i.e this next query is equivalent to the above:
> 
> INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) KEEP (pk1, pk2, f1, 
> f2) VALUES (...);  -- This example updates only f3 and f4, same as above.
> 
> 
>   B - Use a Marker of sorts for Non-Updating fields, Like this perhaps 
> using the Exclamation mark:
> 
> INSERT OR UPDATE INTO t (pk1, !pk2, !f1, !f2, f3, f4) VALUES (...);  -- 
> Again update only f3 and f4 if the record already exists.
> 
> (Adding the marker to a PK field is a no-op).
> Escaping is not needed since a fieldname starting with the same marker 
> will be in the list of field-names, no ambiguity, and in the case where 
> a set of fields contain fields starting with both one and two markers 
> (for which the programmer should be shot, but let's assume it possible) 
> then the field can simply be enclosed in quotes as is the norm for 
> disambiguation in SQLite. This next example has fields named !f and !!f:
> 
> INSERT OR UPDATE INTO t (pk1, pk2, !"!f", !!f) VALUES (...);  -- Here 
> updating only !!f if the record already exists.
> 
> 
> Personally, I'm partial to option A.
> 
> I know it's a bit of work, but it seems less so than many of the other 
> additions - perhaps let's first have 

Re: [sqlite] UPSERT

[Paul]

19 March 2018, 09:26:15, by "Rowan Worth" <row...@dug.com>:

> On 16 March 2018 at 21:44, Paul <> de...@ukr.net> wrote:
> 
> > A few years back I've been asking the same question. To be honest, there's
> > no more
> > efficient alternative, than the one that can be implemented within library
> > itself.
> > Both performance-wise and productivity-wise.
> >
> > Doing hacks with INSERT + UPDATE or UPDATE + INSERT leaves us with
> > problems:
> >  * Which strategy to choose, INSERT + UPDATE or the reverse? No way to
> > make this generic without hurting performance
> >  * No matter the strategy, we end up with two queries which leads to extra
> > code that has to be maintained and kept in sync plus a penalty from
> > preparing two statements
> >  * Existence of two statements leaves us vulnerable to race conditions,
> > which adds two extra statements to BEGIN and COMMIT a transaction
> >
> 
> I agree with your overall sentiment, but BEGIN/COMMIT actually eliminates
> two statements because in the standard mode of operation (ie. autocommit)
> you're essentially doing:
> 
> (implicit) BEGIN
> INSERT ...
> (implicit) COMMIT
> (implicit) BEGIN
> UPDATE ...
> (implicit) COMMIT
> 
> By making the BEGIN/COMMIT explicit you reduce the overall work when two
> statements are required.

I agree with you here. But then again you have to issue both BEGIN and COMMIT 
through SQL statements, ie through the parser. Even if overhead is small, it's
still present. It all depends on the scenario that database is used in.
If you have a lot of scenarios when you INSERT or UPDATE some data quite 
frequently
then overhead is visible. By manually tweaking strategies in various places 
(whether to use UPDATE first or INSERT) I've managed to improve performance
by tens of percents. Then again, it depends on scenario. Users that use this
model not very often will definitely not benefit that much from UPSERT.
But the ability to work around, and potentially small benefit to an average
user should not be the arguments to dismiss its implementation. After all, 
half of the features in SQLite3 are not that useful to an average user.

> 
> It does seem like sqlite could avoid an extra btree lookup if it
> implemented UPSERT itself, but since the required pages are practically
> guaranteed to be in cache for the second query I wonder how many rows you'd
> need in a table for it to make a significant difference. As you say the
> main benefit would be to avoid synthesising two statements in user code.

It's easy to calculate, exactly twice as much time as it takes to do a B-Tree
lookup. How big is the piece of the pie, again, depends on the scenario.

Fort me personally, the most sad thing is an annoyance. Because I have to
maintain two almost identical queries and manually tweak strategies.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul]

0



In all of the few dozens of schemas. Rowid logic is almost perfect. In 
extremely rare cases, when
primary key id is exposed outside of database, a custom table that keeps last 
allocated id is used.


16 March 2018, 17:37:31, by "Richard Hipp" :


This is a survey, the results of which will help us to make SQLite faster.

How many tables in your schema(s) use AUTOINCREMENT?

I just need a single integer, the count of uses of the AUTOINCREMENT
in your overall schema.  You might compute this using:

   sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l

Private email to me is fine.  Thanks for participating in this survey!
-- 
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] UPSERT

[Paul]

A few years back I've been asking the same question. To be honest, there's no 
more  
efficient alternative, than the one that can be implemented within library 
itself. 
Both performance-wise and productivity-wise.

Doing hacks with INSERT + UPDATE or UPDATE + INSERT leaves us with problems:
 * Which strategy to choose, INSERT + UPDATE or the reverse? No way to make 
this generic without hurting performance
 * No matter the strategy, we end up with two queries which leads to extra code 
that has to be maintained and kept in sync plus a penalty from preparing two 
statements
 * Existence of two statements leaves us vulnerable to race conditions, which 
adds two extra statements to BEGIN and COMMIT a transaction

Even if for some reason we dismiss all of the said above, UPSERT scenario is 
wy to common.
Society wise it's much more efficient to make a change in the library for a 
convenience of
thousands (or millions?) of library users rather than leaving it up for them to 
figure out.


Best regards,
Paul

16 March 2018, 12:25:06, by "Robert M. Münch" <robert.mue...@saphirion.com>:

> Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since it 
> doesn’t has an UPSERT?
> 
> So, if I have a table with 30 columns and my code updates sub-sets out of 
> these columns, I don’t want to write queries that manually retrieve the old 
> values one by one.
> 
> insert or replace into Book (ID, Name, TypeID, Level, Seen) values ( (select 
> ID from Book where Name = "SearchName"),
>"SearchName",
> 5,
> 6,
> (select Seen from Book where Name = "SearchName"));
> 
> So, for every column I don’t want to change I need to add a sub-select 
> statement. If I need to build this statement dynamically, IMO it would be 
> better to handle this code directly in code:
> 
> if(record-exists?){ UPDATE …
> } else { INSERT …
> }
> 
> Any suggestions / feedback?
> 
> Viele Grüsse.
> 
> -- 
> 
> Robert M. Münch, CEO
> M: +41 79 65 11 49 6
> 
> Saphirion AG
> smarter | better | faster
> 
> http://www.saphirion.comhttp://www.nlpp.ch
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.orghttp://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] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

[Paul Wise]

Dan Kennedy wrote:

> To: SQLite mailing list , 

I didn't get your mail because I am not subscribed and was not CCed.

> In frame 0 of thread 1, what do the following gdb commands say?
> 
>print *pCache
>print *pPage

I can't provide that easily because of package upgrades.

However, I got a similar but slightly different crash today:

https://bugzilla.gnome.org/page.cgi?id=traceparser/trace.html_id=238398
https://bugzilla.gnome.org/attachment.cgi?id=368394

-- 
bye,
pabs

http://bonedaddy.net/pabs3/

signature.asc
Description: This is a digitally signed message part
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

That terminal app is still sandboxed. AFAIAA you essentially get access to
the applictaios data folder and you can add, create, delete, etc files
within it.

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

On 16 January 2018 at 09:39, John G <rjkgilles...@gmail.com> wrote:

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

Re: [sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

[Paul Wise]

On Fri, 2017-12-22 at 20:09 -0500, Richard Hipp wrote:

> Nevertheless, we will investigate from the SQLite side,
> just in case.

If you need any information from the core dump, please let me know
which gdb commands to run. I still have one available from the latest
crash and have marked it immutable to prevent it from being removed
until your investigation is complete.

-- 
bye,
pabs

http://bonedaddy.net/pabs3/

signature.asc
Description: This is a digitally signed message part
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

[Paul Wise]

On Fri, 2017-12-22 at 20:09 -0500, Richard Hipp wrote:

> There are no known issues like this with any recent version of SQLite.
> Usually these kinds of things end up being heap corruption in the
> application.  Nevertheless, we will investigate from the SQLite side,
> just in case.

I see, thanks for the information and investigation.

> "SQLite 3.21.0-1" is not an official SQLite product.  It must be a
> version of SQLite that has been customized by Debian.  Where can we
> get a copy of Debian's customized code?

You can browse the Debian source code here:

https://sources.debian.org/src/sqlite3/3.21.0-1/

You can browse the Debian patches here:

https://sources.debian.org/patches/sqlite3/3.21.0-1/

You can download the Debian source code here (debian.tar has patches):

http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0-1.dsc
http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0-1.debian.tar.xz
http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0.orig.tar.xz
http://deb.debian.org/debian/pool/main/s/sqlite3/sqlite3_3.21.0.orig-www.tar.xz

You can view the build logs for the package here, in the Status column:

https://buildd.debian.org/status/package.php?p=sqlite3

Note that the crash was on amd64 and logs are not available for that.

-- 
bye,
pabs

http://bonedaddy.net/pabs3/

signature.asc
Description: This is a digitally signed message part
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store

[Paul Wise]

Hi all,

I got a couple of random crashes GNOME's tracker-store daemon that
appear to be related to sqlite3. I use 3.21.0-1 from Debian buster.

https://bugzilla.gnome.org/show_bug.cgi?id=791243

The short backtraces are available here:

https://bugzilla.gnome.org/page.cgi?id=traceparser/trace.html_id=238220
https://bugzilla.gnome.org/page.cgi?id=traceparser/trace.html_id=238281

The full backtraces are available here:

https://bugzilla.gnome.org/attachment.cgi?id=365000
https://bugzilla.gnome.org/attachment.cgi?id=365891

Is this a bug in sqlite3 or is it caused by data corruption?

PS: please either CC me in response or post on the GNOME bug.

-- 
bye,
pabs

http://bonedaddy.net/pabs3/

signature.asc
Description: This is a digitally signed message part
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiling / building SQLite to include regex

[Paul Hoffman]

Greetings. I understand that SQLite doesn't come natively with regex
support, but that it can be added. My question is how to do so when I
install. I'm building from source from .
Is there a simple recipe for "make REGEX work after installation"?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic DELETE index optimisation?

[Paul Sanderson]

Dinu

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

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

On 19 December 2017 at 16:15, Dinu <dinumar...@gmail.com> wrote:

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

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

[Paul Sanderson]

SQLite stores the verison number of the librrary in the database header.
Different SQlite libraries on different computers would cause this error.

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

On 19 December 2017 at 12:32, Karl Forner <karl.for...@gmail.com> wrote:

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

Re: [sqlite] Atomic DELETE index optimisation?

[Paul Sanderson]

Not sure how relevant it might be, but what page size is the DB set to and
what is the average size of a record?


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

On 18 December 2017 at 10:03, Dinu <dinumar...@gmail.com> wrote:

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

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

[Paul Sanderson]

Try it

create a table and use the zeroblob(n) function to insert lots of blobs of
size n

ie
create table blobs (b blob);

insert into blobs values(zeroblob(1));
insert into blobs values(zeroblob(1));

etc.

interestingly the max blob size is specified as 2147483647 but on my
current test client 3.18.0

insert into blobs values(zeroblob(2147483647));

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



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

On 15 December 2017 at 16:30, x <tam118...@hotmail.com> wrote:

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

Re: [sqlite] DateTime to bigint

[Paul Sanderson]

Hi Tibor

Your date format is windows ticks, i.e. 100 nano seconds intervals since
01/01/0001

You can convert it as follows

SELECT (StrfTime('%s', '2004-08-05') + 62135596800) * 1000 AS Ticks

where StrfTime('%s', '2004-08-05') is the number of seconds between the
provided date and 1/1/1970
62135596800 is the difference in seconds between 1/1/1970 and 1/1/0001
and 1000 converts it to nanoseconds

HTH

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

On 8 December 2017 at 16:53, Tibor Balog <tibor.ba...@gmx.ch> wrote:

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

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

[Paul Sanderson]

What about some sort of poll.

Mail lists might work but the additonal functionality offered by a forum (I
am a member of many) makes them my choice.

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

On 21 November 2017 at 16:43, Martin Raiber <mar...@urbackup.org> wrote:

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

Re: [sqlite] Energy consumption of SQLite queries

[Paul Sanderson]

A pretty much impossible task I would think.

The power usage of SQLite compared to the power usage of different hardware
components would be miniscule. But, there are so many other tasks running
on a system, many in the background, that isolating SQLite from the rest
would be next to impossible. Just look at process on a windows system via
the task manager or a linux system using top to get a very simplistic idea
of the different tasks that are using processor time - Sort by processor
usage and the list is always changing even when you are doing nothing. Add
in variable speed fans and processor throttling to manage temperature/power
consumption etc. and you have a mammoth task.

Good luck :)

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

On 21 November 2017 at 00:36, Simon Slavin <slav...@bigfraud.org> wrote:

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

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

[Paul Sanderson]

Coincidence!  I have just been in my gmail folder marking a load of SQLite
email as 'not spam'

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

On 21 November 2017 at 10:35, Dominique Devienne <ddevie...@gmail.com>
wrote:

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

Re: [sqlite] Grouping and grabbing one item

[Paul Sanderson]

ahh bugger - google didn't show the new answers had popped up. Pleased I
came up with a working solution though :)


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

On 18 October 2017 at 19:03, jose isaias cabrera <jic...@barrioinvi.net>
wrote:

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

Re: [sqlite] Grouping and grabbing one item

[Paul Sanderson]

How about

select (SELECT ed from Tasks where task = 'QUOTE' and Pid = 1),
max(ed),target, sum(amt) from Tasks where Pid=1 group by target HAVING amt
> 0;

2017-09-27|2017-10-01|es-ES|170.0
2017-09-27|2017-10-01|fr-FR|185.0
2017-09-27|2017-10-01|it-IT|200.0



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

On 18 October 2017 at 18:23, jose isaias cabrera <jic...@barrioinvi.net>
wrote:

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

[sqlite] one to one relationships

[Paul Alagna]

2 tables keyed alike are in a one to one relationship. IE every record of T1 
will yield one and only one record in T2

PAUL ALAGNA
pjala...@gmail.com <mailto:pjala...@gmail.com>




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

Re: [sqlite] PRAGMA optimize; == no busy handler?

[Paul]

From my personal experience, performance instability of SQLite queries 
(drastically jumping from milliseconds to seconds and back) that is fixable by 
running ANALYZE means that your queries are not optimal (some crucial indices 
are missing). The reason ANALYZE helps is because the info that planner 
receives helps to mitigate the absence of important indices by adapting the 
strategy. Say for example, you have a query that involves few tables being a 
JOIN-ed. There may be the case when SQLite has to scan one of the tables but is 
does not know which, so it uses some default logic that does not account table 
size (since SQLite does not know it). As the result, wrong table may be chosen 
(the largest one).

Why this happens exactly after the first record is inserted? Probably because 
Query Planner is smart and uses some meta info. Probably SQLite does not 
allocate a page for empty tables before the first record is inserted. Query 
Planner may take an advantage of this knowledge and optimizing query 
accordingly.

All of this is just a speculation, of course. What you definitely should do is 
run EXPLAIN QUERY PLAN on the sluggish query. Better yet, run EXPLAIN QUERY 
PLAN on *every* query, just to be sure.

-Paul


> >Don't do that
> Mostly sure, but there's some cases SQLite will skip the busyhandler and 
> immediately return, but they're not permanently-busy conditions. We have 
> multiple threads across multiple processes using the database
> 
> > why you’re doing "PRAGMA optimize" while other parts of your program are 
> > accessing the database
> Because we don't necessarily know there's other work in progress, and we 
> don't have a good opportunity to ensure we do it later but-no-later than when 
> we need it
> 
> >It makes more sense just to put ANALYZE into some sort of end-of-month 
> >procedure
> >or a manual maintenance procedure your users run only when nobody else is 
> >using the system
> That's what we did but the real world isn't that simple
> 
> Last month we did...
> * ANALYZE when the staterepository service starts (effective boot time) if it 
> hasn't been done in a while (1 or 3 days, I forget)
> * ANALYZE at the end of a deployment operation if it hasn't been done in a 
> while (1min I think)
> @ 1st login there's 30+ operations in a short window (1-2 minutes?) and folks 
> are very anal^H^H^H^Hconscious re 1st login perf. The workload is rather 
> variable all told thus the 1min delay. We usually wind up hitting this a 
> couple of times @ 1st login. That primes sqlite_stat1 sufficiently that 
> statements execute in ms (at most) as expected. .
> 
> We recently found a problem where you click on a hyperlink in Edge which 
> (among other things) queries the database and _sometimes_ takes 30s+ instead 
> of near-zero. But we couldn't repro it, and in the rare case someone hit it 
> the problem went away on its own for no apparent reason either. We finally 
> found the problem was a table involved in the query had 1 record and 
> executing the raw SQL took an abnormally long time - but ANALYZE and re-query 
> and time was near-zero. As if we'd added a row to the table for the first 
> time but didn't do ANALYZE so SQLite had no stats to inform its planning, and 
> of course if you reboot after a day or 3 the 'maintenance' would kick in and 
> update stats. Or if something else happened to get installed on the system 
> causing a new deployment operation to call ANALYZE. But the user experience 
> was poor and too unpredictable... 
> 
> PRAGMA optimize; fixed this. We get the ANALYZE benefits we need, but only 
> for those tables that grew significantly (and 0 to >0 qualifies), and 
> 
> We also cache connections for what can be lifetime of a service, so optimize 
> @ connection close is too long to wait.
> 
> 
> My quick hack is to change the PRAGMA optimize per deployment operation to a 
> best-effort - remove the busy handler, try it and restore. I suspect I need 
> something more involved but I'm still weighing my options. The big Q is 
> understanding PRAGMA optimize (and ANALYZE) in relation to busy|locked 
> conditions.
> 
> 
> What do you think of an option to only analyze tables that have grown from 0 
> records to >0 e.g. PRAGMA optimize(0x10002) so 2=ANALYZE if beneficial and 
> 0x1=change the criteria from "increased by 25 times or more since the 
> last" to "increased from 0 records to 1+ record since the last? I've seen 
> problems when we have data w/o analyze but not if we grow from N to >N, 
> thought that could be coincidental where we analyze often enough stats never 
> get too out of proportion to the data. So far the only definitive statement I 
> can make is ANALYZE on a table that went from 0 records to 1+

Re: [sqlite] Why is Sqlite mediatype not registered at iana

[Paul van Genuchten]

Hi Clemens, thank you for your reply. I’d be happy to volunteer for that 
exercise, however not sure if i’m a good fit, since i’m currently not a member 
of the sqlite consortium.

Related to your comment to register sqlite as a structured syntax suffix (RFC 
6839). I’d say why not both. For my use-case (detecting the mediatype of a file 
from its iso19115- or dcat-metadata) it is relevant to have it as a media-type 
preferably at Iana.

Seems the currently widely used application/x-sqlite3 will probably not be 
accepted by iana, we need a more formal type, such as application/vnd.sqlite

Hope to hear from you,
Regards, Paul.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Internally SQLite stores and process numbers as Julian day numbers, the
number of days since noon in Greenwich on November 24, 4714 B.C.

I have not examined the code in any depth but would assume that if you
store the data in the same format it would save on any processing overhead
for calculations but would just require a conversion for display.





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

On 25 September 2017 at 09:12, David Wellman <dwell...@ward-analytics.com>
wrote:

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

[sqlite] Why is Sqlite mediatype not registered at iana

[Paul Van Genuchten]

Hi list, in geopackage (a spatial extension to sqlite) issues we had this 
discussion https://github.com/opengeospatial/geopackage/issues/381

I wonder what is your view on this

Why does sqlite not have an official mediatype, eg. Application/vnd.sqlite, and 
why is it not registered at iana (See above issue for use cases)

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

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

[Paul Sanderson]

Space savings will depend very much on what other data is in the table.

If you have a 4096 byte page size and with an average record size of 1000
bytes then saving 7 bytes for each of the 4 records wont free up enough
space to fit a new record into that page. So savings in this scenario will
effectively be nil.

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

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

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

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

On 10 August 2017 at 14:13, R Smith <rsm...@rsweb.co.za> wrote:

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

Re: [sqlite] Problem with the new pointer-passing interface

[Paul]

To me it seems like that string is very tightly coupled with the actual pointer 
being bound.
I think it's a good idea, in case you cannot make it a literal or static, to 
keep it with
an object whose pointer you bind.


  Hi,  
 
 I came across a problem with the new pointer-passing interface, when I 
tried 
 to incorporate the carray extension into my wxSQLite3 wrapper for SQLite3. 
 
 
 In the comment of the sqlite3_bind_pointer() routine I found this note:  
 
 The T parameter should be a static string, preferably a string literal.  
 
 This comment is a bit misleading, since it doesn't become clear that the 
 string pointer for T must be valid as long as the associated statement is 
 active.  
 
 In my case this is quite cumbersome, since in my wrapper I have to extract 
 the value of parameter T from a temporary string object. That is, shortly 
after 
 having called sqlite3_bind_pointer the temporary string goes out of scope. 
 The effect is that later on the function sqlite3_value_pointer usually 
can't see 
 the correct type value anymore. Therefore sqlite3_value_pointer returns a 
 NULL pointer ... and the carray table is empty.  
 
 IMHO it would be better if the function sqlite3_bind_pointer would make a 
 copy of the type string and would thus be independent of the life span of 
the 
 type parameter.  
 
 Regards,  
 
 Ulrich  
 -- 
 E-Mail privat:  ulrich.te...@gmx.de
 World Wide Web: http://www.telle-online.de
 
 
 ___
 sqlite-users mailing list
 
sqlite-users@mailinglists.sqlite.orghttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Summarising (missing) values

[Paul Sanderson]

That simple - I was over thinking it

cheers

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

On 18 July 2017 at 11:19, Clemens Ladisch <clem...@ladisch.de> wrote:

> Paul Sanderson wrote:
> > What I would like is a single query that summarises the values that are
> > present in (or missing from) a table.
>
> A row is the start of a range if there is no previous row:
>
>   WITH ranges(first) AS (
> SELECT _id
> FROM messages
> WHERE _id - 1 NOT IN (SELECT _id FROM messages)
>   )
>
> The corresponding last row is the first row at or behind the start row
> that has no next row:
>
>   SELECT first,
>  (SELECT min(_id)
>   FROM messages
>   WHERE _id >= ranges.first
> AND _id + 1 NOT IN (SELECT _id FROM messages)
>  ) AS last
>   FROM ranges;
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Summarising (missing) values

[Paul Sanderson]

I have a table which includes a numeric "ID" column, values in the column
generally increment but there are some gaps.

I use the following query to get a list of all "missing" values


WITH RECURSIVE cte(x) AS (SELECT (SELECT Min(messages._id) FROM messages)
UNION ALL
SELECT cte.x + 1
FROM cte
WHERE cte.x < (SELECT Max(messages._id) FROM messages))
SELECT *
FROM cte
WHERE cte.x NOT IN (SELECT messages._id FROM messages)



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


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

which gives me (on my test values)

334
1077
1701
2385
2390
2393

and


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

334
1297
1701
2386
2390
3336





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


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

Different approaches to this would be of interest
Paul


www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite problem with opening database

[Paul Sanderson]

Could your 32 bit app be picking up a 64 bit dll. Could you rename the
dll's and hard code the location into your library?

May not work for your release code but may help you narrow down the issue.

Paul


On Wed, 5 Jul 2017 at 18:19, Simon Slavin <slav...@bigfraud.org> wrote:

>
>
> On 5 Jul 2017, at 1:41pm, Gregor Pavuna <gre...@hermes2.net> wrote:
>
> > As it seems there's some sort of server problem(Windows 2012 server). My
> guess is server is caching 64bit files and serving them to 32bit operating
> systems. I tested on my test server with laptop (32bit windows 7) and it
> works fine. Than i went to client and connected laptop there and it didn't
> work with their files.
>
> You are keeping your application on a server ?  Or a library ?  Does the
> problem go away if you keep your application and libraries on the client
> computer instead ?
>
> > I also googled that out, but couldn't find anything on that topic. Any
> suggestions?
>
> Whatever the problem, it seems that it’s related to the Windows
> application loading system, not SQLite.  So you might find another forum is
> able to help you better than this one.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] syntax error near AS

[Paul Sanderson]

The SQLite syntax diagrams are my first point of call when looking at an
error in my code like this.

https://sqlite.org/lang_update.html

"AS" and an alias are clearly not part of the statement.


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

On 6 July 2017 at 06:03, Keith Medcalf <kmedc...@dessus.com> wrote:

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

Re: [sqlite] FOREING KEY constraint

[Paul Sanderson]

pragma foreign_key_list(table_name) may help


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

On 3 July 2017 at 15:05, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> You can get foreign key constraints with a pragma.
> Check constraints need to parse the SQL.
>
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Clemens Ladisch
> > Sent: Monday, 3 July, 2017 08:00
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: Re: [sqlite] FOREING KEY constraint
> >
> > J. King wrote:
> > > The sqlite_master table should have this information.
> > >
> > > SELECT count() FROM sqlite_master WHERE name IS your_constraint_name
> AND
> > tbl_name IS your_table_name;
> >
> > Constraints do not have separate entries in the sqlite_master table.
> > And there is no other mechanism to get this information without parsing
> > the SQL.
> >
> >
> > Regards,
> > Clemens
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Document typo?

[Paul Sanderson]

https://sqlite.org/dbstat.html

The DBStat web page defines that schema of the virtual table as follows
with path defined as an integer

CREATE TABLE dbstat(
  name   STRING, -- Name of table or index
  path   INTEGER,-- Path to page from root
  pageno INTEGER,-- Page number
  pagetype   STRING, -- 'internal', 'leaf' or 'overflow'
  ncell  INTEGER,-- Cells on page (0 for overflow)
  payloadINTEGER,-- Bytes of payload on this page
  unused INTEGER,-- Bytes of unused space on this page
  mx_payload INTEGER,-- Largest payload size of all cells on this page
  pgoffset   INTEGER,-- Offset of page in file
  pgsize INTEGER,-- Size of the page
  schema TEXT HIDDEN -- The database being analyzed
);

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

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





Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 114, Issue 23

[Paul J. McMillan, Sr.]

Thanks Chris!  I'm using .Net 4.6 (VS 2017), which I don't think works with 
System.Data.SQLite yet, so I downgraded to .net 4.5.2.  Seems to be working 
fine.

Please tell me about your classes.  I'm using EF6.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of sqlite-users-requ...@mailinglists.sqlite.org
Sent: Friday, June 23, 2017 8:00 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: sqlite-users Digest, Vol 114, Issue 23

Send sqlite-users mailing list submissions to
sqlite-users@mailinglists.sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
sqlite-users-requ...@mailinglists.sqlite.org

You can reach the person managing the list at
sqlite-users-ow...@mailinglists.sqlite.org

When replying, please edit your Subject line so it is more specific than "Re: 
Contents of sqlite-users digest..."


Today's Topics:

   1. 3 fixes for 3.19.3 (Danny Couture)
   2. Could not load file or assembly error (Paul J. McMillan, Sr.)
   3. User Authentication Extension is broken in 3.19 branch
  (Thien, Christoph)
   4. Re: UTF8-BOM not disregarded in CSV import (Clemens Ladisch)
   5. Re: Could not load file or assembly error (Chris Locke)


--

Message: 1
Date: Thu, 22 Jun 2017 13:30:46 -0400
From: Danny Couture <couture.da...@gmail.com>
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] 3 fixes for 3.19.3
Message-ID:

Re: [sqlite] Providing incrementing column to query

[Paul Sanderson]

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

Thank You

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

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

>
> Of course, if the traversal order is different than the row return order
> then you will not get ascending logical row numbers unless you do something
> like:
>
> select logicalrow() as SequenceNumber,
>t.*
>   from (...query ...) as t;
>
> If you need logical row numbers it is much better (and far more efficient)
> to assign them in your program as the results are returned.
>
> Out of curiosity, why do you need logical result row numbers since they do
> not correlate with anything meaningful?
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Paul Sanderson
> > Sent: Saturday, 24 June, 2017 06:18
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Providing incrementing column to query
> >
> > Hmm thanks Clemens
> >
> > Have written an extension to do it - some of my tables are very big and
> > feel that the extension might be a better route.
> >
> > Paul
> > www.sandersonforensics.com
> > skype: r3scue193
> > twitter: @sandersonforens
> > Tel +44 (0)1326 572786
> > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
> > Toolkit
> > -Forensic Toolkit for SQLite
> > email from a work address for a fully functional demo licence
> >
> > On 24 June 2017 at 13:10, Clemens Ladisch <clem...@ladisch.de> wrote:
> >
> > > Paul Sanderson wrote:
> > > > I Have a number of queries to which I want to supply an incrementing
> > > column,
> > > > some of these queries involve without rowid tables.
> > > >
> > > > Is there a way of doing this with a SQL query?
> > >
> > > First, define a sort order, and ensure that it does not have
> duplicates.
> > > Then use a correlated subquery to count how many rows would come before
> > > the current one in that order:
> > >
> > >   SELECT (SELECT count(*)
> > >   FROM MyTable AS T2
> > >   WHERE T2.name <= MyTable.Name
> > >  ) AS row_number,
> > >  name,
> > >  age
> > >   FROM MyTable
> > >   ORDER BY name;
> > >
> > > It would be a better idea to count returned rows in your program.
> > >
> > >
> > > Regards,
> > > Clemens
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Providing incrementing column to query

[Paul Sanderson]

Hmm thanks Clemens

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

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

On 24 June 2017 at 13:10, Clemens Ladisch <clem...@ladisch.de> wrote:

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

[sqlite] Providing incrementing column to query

[Paul Sanderson]

I Have a number of queries to which I want to supply an incrementing column,
some of these queries involve without rowid tables.

 

I have no control over the design of the tables.

 

So for a table defined as: 

 

CREATE TABLE (name text, age integer) with values

 

Steve, 34

Eric, 27

Denis, 41

 

I want to return a result set

 

1, Steve, 34

2, Eric, 27

3, Denis, 41

 

 

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

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

[sqlite] Could not load file or assembly error

[Paul J. McMillan, Sr.]

Hi,

 

I'm new to SQLite.  I want to use it in my C# application.  I downloaded the
tool SQLite/SQL Server Compact Toolbox.  Under Data Connections, I keep
getting the error message in red: "Could not load file or assembly
'SQLiteScripting".  Does anyone have an idea of why I'm getting this error?

 

Thanks

Paul McMillan

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

Re: [sqlite] imposter tables

[Paul Sanderson]

Thanks Richard

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

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



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

On 15 June 2017 at 12:58, R Smith <rsm...@rsweb.co.za> wrote:

>
> On 2017/06/15 10:15 AM, Paul Sanderson wrote:
>
>> Thanks Richard - the View approach is fine for my needs - just wanted to
>> know what the rationale was for the imposter tables given the ability to
>> simulate the imposter table with a view.
>>
>
> I'm thinking the easiest way to describe it is perhaps in programming
> terms -
> a view is like a function that computes and produces a result that may or
> may not be a simple reflection of an internal variable,
> and
> an imposter table is rather more like a variable that lives at the same
> memory address as another data structure so that querying it queries the
> underlying structure direct (this part is also achievable by a view), but
> changes to it also change in the underlying data structure (very unlike a
> view).
>
> This is great if you are looking for a way to update an index
> independently from its data, but is also, as has been pointed out numerous
> times, quite dangerous - but then, breaking it is mostly fixable by a
> simple REINDEX.
>
> Not sure if you can install such an imposter table on a corrupt database
> file, moreso than a View anyway, but that actually might have some utility
> towards retrieving or fixing (or at least gaining a better understanding
> of) a corrupted data table / index, which I think Paul might be interested
> in.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] imposter tables

[Paul Sanderson]

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

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

On 14 June 2017 at 13:11, Simon Slavin <slav...@bigfraud.org> wrote:

>
>
> On 14 Jun 2017, at 12:52pm, Paul Sanderson <sandersonforens...@gmail.com>
> wrote:
>
> > The only benefit I can see is that you know the imposter table is showing
> > you exactly what is in the index, where the view is my interpretation of
> > the SQL needed to show what is in the index. Is this the main benefit? or
> > am I missing something?
>
> Hmm.  If you create a view with calculated columns, and then create an
> impostor table on that view, do you get a virtual table with pre-calculated
> columns ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] imposter tables

[Paul Sanderson]

I am just taking a look at imposter tables and while the implementation is
neat I am just wondering what their use is, or rather what they can achieve
that a view can't achieve (and without the risk of DB corruption).

For instance an imposter table created on an index such as the following
from Skype

CREATE INDEX chat_idx_chat_room_name_service_name ON chat(room_name,
service_name)

.imposter chat_idx_chat_room_name_service_name imptable

can be simulated with a view

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

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

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

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

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



Cheers






Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786 <+44%201326%20572786>
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Ahh -

I always let SQLite decide what index to use as I assume that it knows
best. I have never used "indexed by" to force the use of a specific index -
I see the issue with backward compatibility now.

Thanks Richard

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

I think you guys might be missing my point :)

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

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

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

Just a thought



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

On 19 May 2017 at 19:29, Joseph L. Casale <jcas...@activenetwerx.com> wrote:

> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On
> > Behalf Of Paul Sanderson
> > Sent: Friday, May 19, 2017 12:08 PM
> > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> > Subject: Re: [sqlite] auntondex with unique and integer primary key
>
> > I just thought it might be an area for optimisation as a redundant index
> is
> > built.
>
> According to the docs, it's only a pointer and not a duplicate when
> specified exactly as 'INTEGER PRIMARY KEY'. The semantics change
> when you add AUTOINCREMENT to it.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

Thanks Simon

I am aware that a PK must be unique :)

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

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




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

On 19 May 2017 at 18:49, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 19 May 2017, at 6:21pm, Paul Sanderson <sandersonforens...@gmail.com>
> wrote:
>
> > Is the autoindex associated when using unique with an integer primary key
> > definition redundant?
> >
> > I have seen a number of DBs/tables created in the following form:
> >
> > Create table test(id integer unique primary key);
> > Insert into test values (1);
> > Insert into test values (2);
> > Insert into test values (3);
> >
> > The table is created and populated as expected, but an
> > sqlite_autoindex_test_1 is also created with content that mirrors exactly
> > the rowid/id.
> >
> > Is the autoindex redundant and is this an opportunity for optimisation?
>
> There’s no point in declaring the primary key as unique.  A primary key
> has to be unique.  SQLite will enforce uniqueness whether you tell it to or
> not.
>
> sqlite> Create table test1(id integer primary key);
> sqlite> Create table test2(id integer unique primary key);
> sqlite> Create table test3(id integer primary key unique);
> sqlite> PRAGMA index_list(test1);
> sqlite> PRAGMA index_list(test2);
> 0|sqlite_autoindex_test2_1|1|u|0
> sqlite> PRAGMA index_list(test3);
> 0|sqlite_autoindex_test3_1|1|u|0
> sqlite>
>
> It appears that SQLite does not notice that you have declared a primary
> key as unique.  It’s really this that’s causing the problem.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] auntondex with unique and integer primary key

[Paul Sanderson]

Is the autoindex associated when using unique with an integer primary key
definition redundant?

I have seen a number of DBs/tables created in the following form:

Create table test(id integer unique primary key);

Insert into test values (1);

Insert into test values (2);

Insert into test values (3);

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

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


Using 3.18.0

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT NULL integer primary key

[Paul Sanderson]

Ahh being dull and in a hurry
thanks


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

On 18 May 2017 at 17:26, Gwendal Roué <gwendal.r...@gmail.com> wrote:

>
> > Le 18 mai 2017 à 18:16, Paul Sanderson <sandersonforens...@gmail.com> a
> écrit :
> >
> > Is this a bug?
> >
> > Create table test (id integer not null primary key, data text);
> > insert into test values (null, 'row1');
> > select * from test;
> > 1, row1
> >
> > I know that if you provide a NULL value to a column define as integer
> > primary key that SQLite will provide a rowid, but should the not null
> > constraint be obeyed?
>
> Hello Paul,
>
> The constraint is obeyed, since there is no NULL values in the database.
>
> To put it in another way: constraints are properties of the *database
> content*, not of the *operations* on content. They're static, not dynamic.
>
> That's why constraints can be checked with PRAGMA
> schema.foreign_key_check, which tells if the current state of the database
> content is valid.
>
> That's also why the insert statement above succeeds, as long as the value
> that is eventually inserted in the database is NOT NULL.
>
> Gwendal Roué
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] NOT NULL integer primary key

[Paul Sanderson]

Is this a bug?

Create table test (id integer not null primary key, data text);
insert into test values (null, 'row1');
select * from test;
1, row1

I know that if you provide a NULL value to a column define as integer
primary key that SQLite will provide a rowid, but should the not null
constraint be obeyed?


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in memory

[Paul]

If by any chance you have access to Linux or alike, you can just mount a ramfs 
and move database file over there.
It is a usual file system that lives in RAM. This will 100% guarantee you that 
no disk access will be made by SQLite.


18 May 2017, 08:18:47, by "Gabriele Lanaro" :
 
>   Hi, I'm trying to assess if the performance of my application is dependent
> on disk access from sqlite.
> 
> To rule this out I wanted to make sure that the SQLite DB is completely
> accessed from memory and there are no disk accesses.
> 
> Is it possible to obtain this effect by using pragmas such as cache_size?
> 
> Another solution is to copy the existing db to a :memory: db but I'd like
> to achieve the same effect without doing so (because it will require
> substantial modification of the application). For the sake of argument,
> let's image that using :memory: db is not an option.
> 
> Also using a ramdisk is not an option because I don't have root access to
> the machine.
> 
> Thanks,
> 
> Gabriele
> ___
> 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] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Paul van Helden]

On Mon, May 8, 2017 at 8:20 PM, Scott Robison <sc...@casaderobison.com>
wrote:

> On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <p...@planetgis.co.za>
> wrote:
> > Hi,
> >
> > I use a lot of indexes on fields that typically contain lots of NULLs, so
> > the WHERE NOT NULL partial indexing seems very useful.
> >
> > However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs.
> a
> > normal index, SQLite won't use the index to find Max(IndexedField) if it
> is
> > a partial index.
> >
> > Is this an optimization opportunity? I understand that other kinds of
> > partial indexes might exclude possible Min or Max values, but a NOT NULL
> > index would be fine for mins, maxes and most other things?
>
> This may be an optimization opportunity, but you can easily force the
> use of that index by stating WHERE NOT NULL in the select query
> itself. For example:
>
> CREATE TABLE a(b);
> CREATE INDEX ab on a(b) where b is not null;
>
> sqlite> explain query plan select max(b) from a;
> 0|0|0|SEARCH TABLE a
> sqlite> explain query plan select max(b) from a where b is not null;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX ab
>
> >


Thanks Scott,

That works and fully solves my problem!

Regards,

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

[sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Paul van Helden]

Hi,

I use a lot of indexes on fields that typically contain lots of NULLs, so
the WHERE NOT NULL partial indexing seems very useful.

However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
normal index, SQLite won't use the index to find Max(IndexedField) if it is
a partial index.

Is this an optimization opportunity? I understand that other kinds of
partial indexes might exclude possible Min or Max values, but a NOT NULL
index would be fine for mins, maxes and most other things?

Regards,

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

[sqlite] Malformed databases and multithreading

[Paul Egli]

Main question/comment:
-

On the "how to corrupt" page ( http://sqlite.org/howtocorrupt.html ) i
do not see any mention of using SQLite in an incorrect way with
respect to thread safety. Is there really no way that, for example,
using the same connection on multiple threads at the same time could
either (a) corrupt the database, or (b) lead to a false reports of
corruption?

(Even if such a misuse of SQLite falls under one of the categories
already listed on the page, perhaps said category could be updated to
explicitly tell about the risk of misusing SQLite in this way...
perhaps a "Section 2.6"... or 5.1 or 7.1).

Thoughts?

-P



P.S./Appendix: Further background info (probably TMI)...
-

We are trying to help a client debug some issues in a Xamarin-based
app that is deployed to both Android and iOS. Some of the errors they
are seeing are "malformed database".

They are still building in a way that uses the platform-provided
SQLite on both platforms (no, this will not work on Android N) and no
additional SQLite library is linked in, so they should not be having
the "Multiple copies of SQLite linked into the same application"
problem.

They also routinely see "cannot start a transaction within a
transaction" even though there is no obvious place where or reason why
that would happen in their code. This leads us to believe they may be
using the same connection on multiple threads.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

another oops I see Ryan pretty much posted the same as me 5 minutes earlier
- I'll go back to bed :)

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

On 12 April 2017 at 14:08, Ron Barnes <rbar...@njdevils.net> wrote:

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

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

[Paul Sanderson]

Try something like

SELECT dateplay.vi,
  JulianDay('now') AS now,
  JulianDay(SubStr(Replace(dateplay.vi, '/', '-'), 1, 10)) AS jday,
  JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/', '-'), 1,
10)) AS diff,
  CASE
WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 1 THEN 'Under 1 day'
WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 8 THEN 'Under 1 week'
WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 32 THEN 'Under 1 month'
WHEN JulianDay('now') - JulianDay(SubStr(Replace(dateplay.vi, '/',
'-'), 1, 10)) < 365 THEN 'Under 1 year'
END AS category
FROM dateplay

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


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

On 12 April 2017 at 14:08, Ron Barnes <rbar...@njdevils.net> wrote:

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

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

[Paul Sanderson]

Oops hit send too quickly

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

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

On 12 April 2017 at 13:37, Paul Sanderson <sandersonforens...@gmail.com>
wrote:

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

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

[Paul Sanderson]

Hi Ron

Your dates are still not 8601

with your dates above in a  table called dateplay and column named vi

select vi,
julianday('now')  as now,
julianday(substr(replace(vi, '/', '-'), 1, 10)) as jday,
julianday('now')  - julianday(substr(replace(vi, '/', '-'), 1, 10)) as
diff from
dateplay

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

On 12 April 2017 at 13:13, Ron Barnes <rbar...@njdevils.net> wrote:

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

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

[Paul Sanderson]

oops

you need to select the string length from the column width

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


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

On 4 April 2017 at 15:07, Paul Sanderson <sandersonforens...@gmail.com>
wrote:

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

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

[Paul Sanderson]

Just shooting out so no time to test. But could you try something like

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




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

On 4 April 2017 at 14:47, Hans M. van der Meer <drsme...@gmail.com> wrote:

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

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

[Paul]

Thank you very much for replying so quickly!
 
>   On 3/28/17, Paul  wrote:
> > 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()?
> 
> Correct.
> 
> > If so, is it safe to *always*
> > use sqlite3_bind_int64()/sqlite3_column_int64() and forget about pain int
> > versions?
> 
> Correct.
> 
> Also, always use sqlite3_malloc64() and sqlite3_realloc64() and
> sqlite3_column_int64(), etc.
> -- 
> 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