Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

[Hick Gunter]

I didn't forget the automatic index. I even referred to it. But you are right 
about the need to INTEGER PRIMARY KEY to be able to use the rowid as a foreign 
key. This also prevents VACCUM from renumbering the records, so doing that 
would require extra work at the application level (and ON UPDATE CASCADE, which 
is beyond the scope of VACUUM). But even without renumbering, 4 byte rowids (29 
usable bits) will last for nearly a billion records.

I still hold: If you "care" about the actual value of an "id" (not "rowid"), 
you should not be using the INTEGER PRIMARY KEY to hold it. If you "don't care" 
about the actual value of the rowid, then you should not be setting it

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von David Raymond
Gesendet: Montag, 08. Jänner 2018 19:13
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Don't forget the automatic index that gets created when the primary key isn't 
an alias of the rowid.

Also why on earth would you ever have a foreign key that references the rowid 
if you're not using it as the id of the record? You even mentioned "vaccum 
allows SQLite to renumber the rows using the smallest possible numbers" so you 
would never, ever want to use rowid as a foreign key field unless it was an 
integer primary key visible as the primary key of the table.

Hmm, during a vacuum, when SQLite re-numbers the rowids, if you made a foreign 
key to the rowid with "on update cascade" does it in fact cascade since it's 
not a normal transaction?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Monday, January 08, 2018 1:02 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Lets take a simple example of an employee table and couple of requirements:

x) Ids shall be non-reusable
x) Ids shall be indicative of the country the employee is located in at the 
time of hiring
x) Ids shall be assigned in a way that employee counts and seniority may not be 
simply deducted
x) Ids shall be usable for 100 years' worth of business

Looking at country numbering schemes, the UN currently uses 3 digit numbers (10 
bits), using 16 bits gives some wiggle room.
Looking at the largest employers, the US DoD currently employs 3.2 million, at 
a turnover rate of 25% for uniformed personnel, giving an estimated range of 
80.000.000 (27 bits) so using 32 bits gives us some wiggle room.

The Employee ID can be built as the sum of the coutry coude left shifted by 32 
bits plus the country-specific serial number, giving 48 bits of data, and 
scrambled in a way that ensures that some of the high order bits are set, 
yielding 15 digit global employee ids, that SQLite stores as 7 byte integers.

Declaring

Create table employee( id integer primary key, ...)

forces SQLite to use this 7 byte value as the rowid, not only in the employee 
table, but also in all the foreign keys that refer to it. Let's assume that 
there are a dozen relations that refer to the employee id. This gives a total 
of 13 * 7 = 91 bytes of storage for storing and referencing employee ids.

Declaring

Create table employee (id integer, ... , primary key (id));

allows SQLite to use (and reuse) the rowid. Even if you store 1 million active 
records (about twice the size of the US Postal Service), letting SQLite have 
cutody of the rowid uses only up to 3 bytes for a rowid. The total is then 7 + 
12*3 = 43 bytes of storage for storing the employee id and the rowid 
references, which translates to over 50% savings.

The smaller size of rowids in the internal btree structures should also just 
about cancel out the need to keep an index that translates employee ids to 
rowids. Purging records and then performing a vaccum allows SQLite to renumber 
the rows using the smallest possible numbers.

So, not assigning an external meaning to the rowid (and forcing the inner 
workings to comply) can, at least in some cases, actually save space.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Montag, 08. Jänner 2018 16:59
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Gunter, doing it your way would obviously take up more space so what gains 
would make up for that? Faster inserts maybe?



I tried inserting a record in Tbl with an ID 1 less than the min existing ID 
and then did the same with an ID of 1 more than the existing max ID. There was 
little time difference between the two insertions although neither were exactly 
lightning fast.




From: sqlite-users  on behalf 

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Shane Dev]

Thanks Donald. So simple in hindsight

On 8 January 2018 at 23:20, Donald Griggs  wrote:

>
>  select random() > 0;  -- random zero or one
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Simon Slavin]

On 8 Jan 2018, at 9:55pm, Shane Dev  wrote:

> The statement -
> 
> select cast(round(abs(random())/9223372036854775808) as int)
> 
> means I want sqlite to toss a virtual coin and return 0 or 1.

Your code is meant to be self-documenting.  Any time you find a number like 
9223372036854775808 in your code, think hard about what you’re doing.

SELECT random() & 1

should return either 0 or 1 on a random basis.  & is binary AND, | is binary OR.

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

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Donald Griggs]

Regarding:

 select cast(round(abs(random())/9223372036854775808) as int)

 means I want sqlite to toss a virtual coin and return 0 or 1. It looks
like
 a kludge but I could find neither a simpler nor clearer way of
expressing
 this, can you?

Maybe:

 select abs(random() % 2); -- random zero or one
 or
 select random() > 0;  -- random zero or one

where the comment is the most important part.;-)


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

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[R Smith]

On 2018/01/08 10:42 PM, Shane Dev wrote:

Hi Ryan,

Do you mean it would be more efficient to generate entries such as these
(with randomly chosen integers and running numbers) in the application and
then committing them to the database with sqlite3_exec or similar?

For a large number of entries, I assumed there would be greater overhead in
preparing and binding the values than both generating and storing them with
a single RCTE.


I more intended to suggest you generate the consecutive list of numbers 
in your code . SQLite has to jump through some CTE hoops to do that, but 
in your code it's as simple as:

i = 0;
while sqlite_step ...
  i++;
...

The random numbers would be more efficient in your code too, though it 
is also straight forward in SQL.



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

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Shane Dev]

On 8 January 2018 at 21:58, Simon Slavin  wrote:
>
>
> num+1, cast(round(abs(random())/9223372036854775808) as int) from
>
> you’ve probably looking at sanity in the rear view mirror.  Suppose
> someone has to read your code and figure out what it’s meant to do.  If you
> expect your code to be read by others, the amount of documentation you'll
> have to write takes longer than writing the software properly.


The statement -

select cast(round(abs(random())/9223372036854775808) as int)

means I want sqlite to toss a virtual coin and return 0 or 1. It looks like
a kludge but I could find neither a simpler nor clearer way of expressing
this, can you?


>
> > For a large number of entries, I assumed there would be greater overhead
> in
> > preparing and binding the values than both generating and storing them
> with
> > a single RCTE.
>
> SQLite is just software written in C.  There’s no reason to believe it’ll
> be any more efficiently than your own C code.  Even your respect for the
> SQLite development team shouldn’t outweigh the advantage of working with
> code you write yourself.
>
>
I think Ryan was suggesting to calculate the entries in the main
application code and then use sqlite to perform millions of inserts - which
would imply a call to sqlite3_exec with an extremely long *sql string
(or sqlite3_prepare_v2()
with millions of call to sqlite3_bind_int64, etc).

Wouldn't it be more efficient call one sqlite3_exec with *sql being a
single RCTE which does all the work? An additional advantage is that it
relies on sqlite's own c code which I would hope is more
thoroughly debugged and portable that my own.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Simon Slavin]

On 8 Jan 2018, at 8:42pm, Shane Dev  wrote:

> Do you mean it would be more efficient to generate entries such as these
> (with randomly chosen integers and running numbers) in the application and
> then committing them to the database with sqlite3_exec or similar?

There’s the question of understanding and debugging the code.  It’s possible to 
do strange and complicated things entirely in SQLite.   Especially with RCTEs 
and triggers.  But when you see things like

num+1, cast(round(abs(random())/9223372036854775808) as int) from

you’ve probably looking at sanity in the rear view mirror.  Suppose someone has 
to read your code and figure out what it’s meant to do.  If you expect your 
code to be read by others, the amount of documentation you'll have to write 
takes longer than writing the software properly.

> For a large number of entries, I assumed there would be greater overhead in
> preparing and binding the values than both generating and storing them with
> a single RCTE.

SQLite is just software written in C.  There’s no reason to believe it’ll be 
any more efficiently than your own C code.  Even your respect for the SQLite 
development team shouldn’t outweigh the advantage of working with code you 
write yourself.

How low do you need the overhead to be ?  Have you tried a simple solution and 
found it takes an unacceptable time ?  Did you get complaints from your 
customers ?

You can spend two days writing complicated code which shaves 45 milliseconds 
off your execution time.  Code which is difficult to debug and too complicated 
for other people to understand.  Or you could spend those two days adding a 
useful function to your program.  Or lying on a beach.

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

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Shane Dev]

Hi Ryan,

Do you mean it would be more efficient to generate entries such as these
(with randomly chosen integers and running numbers) in the application and
then committing them to the database with sqlite3_exec or similar?

For a large number of entries, I assumed there would be greater overhead in
preparing and binding the values than both generating and storing them with
a single RCTE.

On 8 January 2018 at 10:23, R Smith  wrote:

>
> On 2018/01/08 11:17 AM, Shane Dev wrote:
>
>>
>> P.S one reason I am using SQL instead the main application to perform such
>> calculations is precisely to avoid using variables (and hence the evils of
>> mutable state). Why do you say it is more efficient?
>>
>
> Because it is much more efficient, memory used in stead of computations
> through the DB engine. It's even vastly more efficient when you do these
> things in your programming language (Zero parsing to start with)...
>
> That however doesn't mean you should only do it this way. There are
> obviously merits (such as mutable state evility, if there is such a word)
> to using a less efficient method.
>
> ___
> 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] Compilation failure for sqlite3_analyzer

[Joe Mistachkin]

Tony Papadimitriou wrote:
>
> sqlite3_analyzer.c(207289): fatal error C1083: Cannot open include file: 
> 'tcl.h': No such file or directory
>

By default, the Makefile for MSVC will now look for the Tcl include files
within the "compat\tcl\include" sub-directory in the source tree.  There
are now two ways to build the targets that require Tcl:

1.  To continue using the previous hard-coded default Tcl directory,
add the argument "TCLDIR=C:\Tcl" to the nmake command line.

2.  Use something like the following commands in order to link the
"compat\tcl" directory within the source tree to the "C:\Tcl"
directory (via an NTFS junction):

CD /D C:\path\to\sqlite\compat
MKLINK /J tcl C:\Tcl

--
Joe Mistachkin 

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

Re: [sqlite] Retrieving constraint name

[Igor Korot]

Hi, Keith,

On Mon, Dec 11, 2017 at 12:07 AM, Keith Medcalf  wrote:
>
> After stripping out comments and so forth of course ...

Could you please clarify that statement?
There is a patch from Cezary linked in this thread. I guess he is
successfully using it in his own application.

Thank you.

>
>
> ---
> 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 petern
>>Sent: Sunday, 10 December, 2017 22:37
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Retrieving constraint name
>>
>>Igor/Cezary,
>>
>>It is remarkable how 'struct Parse' already contains the constraint
>>name as
>>Cezary pointed out.
>>->  Token constraintName;/* Name of the constraint currently being
>>parsed */
>>But is not included in the 'struct FKey' linked list node that is
>>reeled in
>>to produce columns in the PRAGMA report.
>>For the official release, presumably, test cases would have to be
>>added in
>>addition to simply hooking it up as suggested.
>>
>>In the meantime, parsing wouldn't be difficult even with primitive
>>built-in
>>SQL string functions.  Consider how the constraint name must occur
>>within
>>the comma delimited part of the well formed CREATE TABLE statement.
>>When
>>obtained from sqlite_master, the statement is guaranteed to be well
>>formed.  Simply examine each comma delimited candidate part.  If
>>present,
>>the first word between keyword CONSTRAINT and keyword REFERENCES is
>>the
>>constraint name.
>>
>>Peter
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>On Sun, Dec 10, 2017 at 7:29 PM, Igor Korot 
>>wrote:
>>
>>> Hi,
>>>
>>> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta
>>
>>> wrote:
>>> > Hello,
>>> >
>>> > On 2017-12-11 01:04, Igor Korot wrote:
>>> >
>>> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta
>>
>>> >> wrote:
>>> >
>>> >
>>> >>> On 2017-12-10 07:21, Igor Korot wrote:
>>> >
>>> >
>>>  The CREATE TABLE statement supports the following syntax:
>>> 
>>>  CREATE TABLE( , CONSTRAINT  FOREIGN
>>>  KEY() REFERENCES (ref_column_list>);
>>> >
>>> >
>>>  [...] If not - does this mean that the only way to get the
>>name is to
>>>  parse the
>>>  sql
>>>  from sqlite_master? Or there is a better way?
>>> >
>>> >
>>> >>> The answer is ``not''. Constraint names are ignored and
>>disappearing
>>> >>> without
>>> >>> a trace except for ``CHECK'' constraint (the name is used to
>>build an
>>> >>> error
>>> >>> message). Unparsed ``sql'' column of ``sqlite_master'' is the
>>sole
>>> place
>>> >>> which contains an indirect info about ``FOREIGN KEY''
>>constraint's
>>> name.
>>> >
>>> >
>>> >> Thank you for confirming.
>>> >
>>> > You are welcome. BTW, SQLite parses SQL every time it creates a
>>table
>>> > (by a SQL command or after an opening of BTree file) -- I believe
>>there
>>> > is no better way. You do not need to parse SQL on your own (it is
>>hard,
>>> > if not impossible, to establish a link between a name and a
>>particular
>>> > constraint). All you need is to append ``char *'' field to
>>``struct
>>> > FKey'' and to inject a function
>>``build.c:sqlite3CreateForeignKey()'':
>>> > ``pParse->constraintName'' will contain the constraint's name
>>(note
>>> > that the name is not dequoted -- you will have to dequote it;
>>look at
>>> > ``build.c:sqlite3AddCheckConstraint()'' to know how assigning of
>>a
>>> > constraint's name is done). This will allow you to build your own
>>map of
>>> > ``FOREIGN KEY'' names. For example, if you want to expand
>>``PRAGMA
>>> > foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case
>>> > PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field.
>>>
>>> Thank you, but I need to keep the official SQLite code.
>>>
>>>
>>> >
>>> >
>>> > -- best regards
>>> >
>>> > Cezary H. Noweta
>>> > ___
>>> > 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compilation failure for sqlite3_analyzer

[Tony Papadimitriou]

sqlite3_analyzer.c(207289): fatal error C1083: Cannot open include file: 
'tcl.h': No such file or directory
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

[David Raymond]

Answering one of my own questions: You can't do a foreign key to a rowid unless 
it's an integer primary key.

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

"The parent key is the column or set of columns in the parent table that the 
foreign key constraint refers to. This is normally, but not always, the primary 
key of the parent table. The parent key must be a named column or columns in 
the parent table, not the rowid."



-Original Message-
From: David Raymond 
Sent: Monday, January 08, 2018 1:13 PM
To: 'SQLite mailing list'
Subject: RE: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Don't forget the automatic index that gets created when the primary key isn't 
an alias of the rowid.

Also why on earth would you ever have a foreign key that references the rowid 
if you're not using it as the id of the record? You even mentioned "vaccum 
allows SQLite to renumber the rows using the smallest possible numbers" so you 
would never, ever want to use rowid as a foreign key field unless it was an 
integer primary key visible as the primary key of the table.

Hmm, during a vacuum, when SQLite re-numbers the rowids, if you made a foreign 
key to the rowid with "on update cascade" does it in fact cascade since it's 
not a normal transaction?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Monday, January 08, 2018 1:02 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Lets take a simple example of an employee table and couple of requirements:

x) Ids shall be non-reusable
x) Ids shall be indicative of the country the employee is located in at the 
time of hiring
x) Ids shall be assigned in a way that employee counts and seniority may not be 
simply deducted
x) Ids shall be usable for 100 years' worth of business

Looking at country numbering schemes, the UN currently uses 3 digit numbers (10 
bits), using 16 bits gives some wiggle room.
Looking at the largest employers, the US DoD currently employs 3.2 million, at 
a turnover rate of 25% for uniformed personnel, giving an estimated range of 
80.000.000 (27 bits) so using 32 bits gives us some wiggle room.

The Employee ID can be built as the sum of the coutry coude left shifted by 32 
bits plus the country-specific serial number, giving 48 bits of data, and 
scrambled in a way that ensures that some of the high order bits are set, 
yielding 15 digit global employee ids, that SQLite stores as 7 byte integers.

Declaring

Create table employee( id integer primary key, ...)

forces SQLite to use this 7 byte value as the rowid, not only in the employee 
table, but also in all the foreign keys that refer to it. Let's assume that 
there are a dozen relations that refer to the employee id. This gives a total 
of 13 * 7 = 91 bytes of storage for storing and referencing employee ids.

Declaring

Create table employee (id integer, ... , primary key (id));

allows SQLite to use (and reuse) the rowid. Even if you store 1 million active 
records (about twice the size of the US Postal Service), letting SQLite have 
cutody of the rowid uses only up to 3 bytes for a rowid. The total is then 7 + 
12*3 = 43 bytes of storage for storing the employee id and the rowid 
references, which translates to over 50% savings.

The smaller size of rowids in the internal btree structures should also just 
about cancel out the need to keep an index that translates employee ids to 
rowids. Purging records and then performing a vaccum allows SQLite to renumber 
the rows using the smallest possible numbers.

So, not assigning an external meaning to the rowid (and forcing the inner 
workings to comply) can, at least in some cases, actually save space.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Montag, 08. Jänner 2018 16:59
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Gunter, doing it your way would obviously take up more space so what gains 
would make up for that? Faster inserts maybe?



I tried inserting a record in Tbl with an ID 1 less than the min existing ID 
and then did the same with an ID of 1 more than the existing max ID. There was 
little time difference between the two insertions although neither were exactly 
lightning fast.




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Monday, January 8, 2018 1:19:00 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list 

Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

[David Raymond]

Don't forget the automatic index that gets created when the primary key isn't 
an alias of the rowid.

Also why on earth would you ever have a foreign key that references the rowid 
if you're not using it as the id of the record? You even mentioned "vaccum 
allows SQLite to renumber the rows using the smallest possible numbers" so you 
would never, ever want to use rowid as a foreign key field unless it was an 
integer primary key visible as the primary key of the table.

Hmm, during a vacuum, when SQLite re-numbers the rowids, if you made a foreign 
key to the rowid with "on update cascade" does it in fact cascade since it's 
not a normal transaction?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Monday, January 08, 2018 1:02 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Lets take a simple example of an employee table and couple of requirements:

x) Ids shall be non-reusable
x) Ids shall be indicative of the country the employee is located in at the 
time of hiring
x) Ids shall be assigned in a way that employee counts and seniority may not be 
simply deducted
x) Ids shall be usable for 100 years' worth of business

Looking at country numbering schemes, the UN currently uses 3 digit numbers (10 
bits), using 16 bits gives some wiggle room.
Looking at the largest employers, the US DoD currently employs 3.2 million, at 
a turnover rate of 25% for uniformed personnel, giving an estimated range of 
80.000.000 (27 bits) so using 32 bits gives us some wiggle room.

The Employee ID can be built as the sum of the coutry coude left shifted by 32 
bits plus the country-specific serial number, giving 48 bits of data, and 
scrambled in a way that ensures that some of the high order bits are set, 
yielding 15 digit global employee ids, that SQLite stores as 7 byte integers.

Declaring

Create table employee( id integer primary key, ...)

forces SQLite to use this 7 byte value as the rowid, not only in the employee 
table, but also in all the foreign keys that refer to it. Let's assume that 
there are a dozen relations that refer to the employee id. This gives a total 
of 13 * 7 = 91 bytes of storage for storing and referencing employee ids.

Declaring

Create table employee (id integer, ... , primary key (id));

allows SQLite to use (and reuse) the rowid. Even if you store 1 million active 
records (about twice the size of the US Postal Service), letting SQLite have 
cutody of the rowid uses only up to 3 bytes for a rowid. The total is then 7 + 
12*3 = 43 bytes of storage for storing the employee id and the rowid 
references, which translates to over 50% savings.

The smaller size of rowids in the internal btree structures should also just 
about cancel out the need to keep an index that translates employee ids to 
rowids. Purging records and then performing a vaccum allows SQLite to renumber 
the rows using the smallest possible numbers.

So, not assigning an external meaning to the rowid (and forcing the inner 
workings to comply) can, at least in some cases, actually save space.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Montag, 08. Jänner 2018 16:59
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Gunter, doing it your way would obviously take up more space so what gains 
would make up for that? Faster inserts maybe?



I tried inserting a record in Tbl with an ID 1 less than the min existing ID 
and then did the same with an ID of 1 more than the existing max ID. There was 
little time difference between the two insertions although neither were exactly 
lightning fast.




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Monday, January 8, 2018 1:19:00 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list 
>Betreff: [EXTERNAL] Re: [sqlite] difference between 'ID IS NULL' and 'ID = 
>NULL'
>
>However, I’m still confused. Reading this https://sqlite.org/queryplanner.html 
>suggests the table is stored in RowID order. So what happens if I insert a 
>>record into Tbl with a lower ID than the existing 2.4 million Ids?

IMHO you should NEVER set the rowid (INTEGER PRIMARY KEY) field yourself, only 
copy it's value to the foreign key field of a referring row and within a 
transaction. Using the internal rowid in a foreign key for double lookup speed 
is the intended use.

SQLite "usually" 

Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

[Hick Gunter]

Lets take a simple example of an employee table and couple of requirements:

x) Ids shall be non-reusable
x) Ids shall be indicative of the country the employee is located in at the 
time of hiring
x) Ids shall be assigned in a way that employee counts and seniority may not be 
simply deducted
x) Ids shall be usable for 100 years' worth of business

Looking at country numbering schemes, the UN currently uses 3 digit numbers (10 
bits), using 16 bits gives some wiggle room.
Looking at the largest employers, the US DoD currently employs 3.2 million, at 
a turnover rate of 25% for uniformed personnel, giving an estimated range of 
80.000.000 (27 bits) so using 32 bits gives us some wiggle room.

The Employee ID can be built as the sum of the coutry coude left shifted by 32 
bits plus the country-specific serial number, giving 48 bits of data, and 
scrambled in a way that ensures that some of the high order bits are set, 
yielding 15 digit global employee ids, that SQLite stores as 7 byte integers.

Declaring

Create table employee( id integer primary key, ...)

forces SQLite to use this 7 byte value as the rowid, not only in the employee 
table, but also in all the foreign keys that refer to it. Let's assume that 
there are a dozen relations that refer to the employee id. This gives a total 
of 13 * 7 = 91 bytes of storage for storing and referencing employee ids.

Declaring

Create table employee (id integer, ... , primary key (id));

allows SQLite to use (and reuse) the rowid. Even if you store 1 million active 
records (about twice the size of the US Postal Service), letting SQLite have 
cutody of the rowid uses only up to 3 bytes for a rowid. The total is then 7 + 
12*3 = 43 bytes of storage for storing the employee id and the rowid 
references, which translates to over 50% savings.

The smaller size of rowids in the internal btree structures should also just 
about cancel out the need to keep an index that translates employee ids to 
rowids. Purging records and then performing a vaccum allows SQLite to renumber 
the rows using the smallest possible numbers.

So, not assigning an external meaning to the rowid (and forcing the inner 
workings to comply) can, at least in some cases, actually save space.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Montag, 08. Jänner 2018 16:59
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Gunter, doing it your way would obviously take up more space so what gains 
would make up for that? Faster inserts maybe?



I tried inserting a record in Tbl with an ID 1 less than the min existing ID 
and then did the same with an ID of 1 more than the existing max ID. There was 
little time difference between the two insertions although neither were exactly 
lightning fast.




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Monday, January 8, 2018 1:19:00 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list 
>Betreff: [EXTERNAL] Re: [sqlite] difference between 'ID IS NULL' and 'ID = 
>NULL'
>
>However, I’m still confused. Reading this https://sqlite.org/queryplanner.html 
>suggests the table is stored in RowID order. So what happens if I insert a 
>>record into Tbl with a lower ID than the existing 2.4 million Ids?

IMHO you should NEVER set the rowid (INTEGER PRIMARY KEY) field yourself, only 
copy it's value to the foreign key field of a referring row and within a 
transaction. Using the internal rowid in a foreign key for double lookup speed 
is the intended use.

SQLite "usually" uses 1 more than the highest rowid currently in use; if the 
maximum possible rowid is used, it will select a random rowid, hoping to find a 
free rowid within a limited number of attempts.

SQLite does allow you to modify/set the rowid, but this will fail if the target 
rowid is already in use. If you require custom rowids, these should be kept in 
separate fields. You can still use the SQLite rowid in foreign keys.


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

Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

[x]

Gunter, doing it your way would obviously take up more space so what gains 
would make up for that? Faster inserts maybe?



I tried inserting a record in Tbl with an ID 1 less than the min existing ID 
and then did the same with an ID of 1 more than the existing max ID. There was 
little time difference between the two insertions although neither were exactly 
lightning fast.




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Monday, January 8, 2018 1:19:00 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list 
>Betreff: [EXTERNAL] Re: [sqlite] difference between 'ID IS NULL' and 'ID = 
>NULL'
>
>However, I’m still confused. Reading this https://sqlite.org/queryplanner.html 
>suggests the table is stored in RowID order. So what happens if I insert a 
>>record into Tbl with a lower ID than the existing 2.4 million Ids?

IMHO you should NEVER set the rowid (INTEGER PRIMARY KEY) field yourself, only 
copy it's value to the foreign key field of a referring row and within a 
transaction. Using the internal rowid in a foreign key for double lookup speed 
is the intended use.

SQLite "usually" uses 1 more than the highest rowid currently in use; if the 
maximum possible rowid is used, it will select a random rowid, hoping to find a 
free rowid within a limited number of attempts.

SQLite does allow you to modify/set the rowid, but this will fail if the target 
rowid is already in use. If you require custom rowids, these should be kept in 
separate fields. You can still use the SQLite rowid in foreign keys.


___
 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] Retrieving constraint name

[Cezary H. Noweta]

Hello,

On 2018-01-07 23:35, Igor Korot wrote:

Then maybe it could be considered to be included in the main tree by
simply applying the patch.


I'm afraid that if I change the order of fields in my patch, then it 
will not help. Exchanging the fields in the pragma is trivial. The whole 
patch is trivial also. At least for me, thus, by implication, for the 
team. I think that the reason for not extending the pragma lies elsewhere.


-- best regards

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

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Another reason to do at least the LEFT JOIN + WHERE -> INNER JOIN
optimisation:
If a view is based on a LEFT JOIN, running a WHERE query on it will exhibit
the same poor behavior and here there will be no way to rewrite the query.



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

Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

[Hick Gunter]

>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list 
>Betreff: [EXTERNAL] Re: [sqlite] difference between 'ID IS NULL' and 'ID = 
>NULL'
>
>However, I’m still confused. Reading this https://sqlite.org/queryplanner.html 
>suggests the table is stored in RowID order. So what happens if I insert a 
>>record into Tbl with a lower ID than the existing 2.4 million Ids?

IMHO you should NEVER set the rowid (INTEGER PRIMARY KEY) field yourself, only 
copy it's value to the foreign key field of a referring row and within a 
transaction. Using the internal rowid in a foreign key for double lookup speed 
is the intended use.

SQLite "usually" uses 1 more than the highest rowid currently in use; if the 
maximum possible rowid is used, it will select a random rowid, hoping to find a 
free rowid within a limited number of attempts.

SQLite does allow you to modify/set the rowid, but this will fail if the target 
rowid is already in use. If you require custom rowids, these should be kept in 
separate fields. You can still use the SQLite rowid in foreign keys.


___
 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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

Thanks Ryan and Dominique. The quote “You can think of an SQLite table as 
essentially a btree covering Index by itself with the Key being the Row_ID” 
makes things a lot clearer.



Thanks to everyone for their replies and patience.



Regards



Tom








From: sqlite-users  on behalf of 
Dominique Devienne 
Sent: Monday, January 8, 2018 10:56:55 AM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On Mon, Jan 8, 2018 at 11:39 AM, x  wrote:

> However, I’m still confused. Reading this https://sqlite.org/
> queryplanner.html suggests the table is stored in RowID order. So what
> happens if I insert a record into Tbl with a lower ID than the existing 2.4
> million Ids?
>

It depends if your ID column is an alias for the ROWID special column or
not.

ROWID is the key of the B-tree I believe, so if you insert a row "in the
middle",
many pages have potentially to be rewritten to "rebalance" the B-tree.
While only the "last" page needs updating with AUTO INCREMENT or a ROWID
larger than the previous larger one.

So if ID an alias for ROWID, the insert might be much more expensive.
If ID is not, then the row is simply "appended" at the end, "cheaply".

I could be wrong of course. I'll find out very soon :). --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] difference between 'ID IS NULL' and 'ID = NULL'

[Dominique Devienne]

On Mon, Jan 8, 2018 at 12:33 PM, R Smith  wrote:

> using any other index means a round-trip reading and hitting values in
> THAT index,

then returning and looking up the hit result in the rowid table index,

and then reading the pages(s) from it and extracting the data -

where during a table scan, all this round tripping is skipped.
>

Plus during a table-scan, you're reading the table pages "in-order", and
decoding the page only once,
for all rows on each page. While an index-scan read the index "in-order",
but the table pages "out-of-order",
since most consecutive rows (from the index) end up on different table
pages. The page-cache helps to
avoid too much IO (if big enough), but you must still decode the page for
each row to access one specific record inside it.

Sometimes the full-table-scan is the most efficient indeed. --DD

PS: But also note that a query with an order-by or group-by might still
prefer an index-scan to avoid sorting rows,
  if the index already matches the requested order, offsetting the eventual
higher cost from the index-scan.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[R Smith]

On 2018/01/08 12:39 PM, x wrote:

Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I 
imagined the RowID as being a separate index which is the root of my confusion.


To elaborate a little - We often get people here asking "But why it 
table-scans in stead of using my nice Index?".


This stems from an often-held misconception that Indexes are God-sent 
magic to improve everything. The fact is that Indexes are costly 
mechanisms which allows fast lookup which, only AFTER a certain critical 
size and for specific circumstances, become more efficient than a scan. 
The Query Planner has to do a lot of work to figure out what those 
"critical size and specific circumstances" is for any specific query, 
and it does get real fuzzy.


I think I've heard Richard or Dan explain it as follows (if memory 
serves, someone please point out if I'm mistaken):


You can think of an SQLite table as essentially a btree covering Index 
by itself with the Key being the Row_ID (or more recently, the PK for 
WITHOUT ROWID tables). This is why the rowid (or any column serving as 
an alias to it, or the PK for WITHOUT ROWID tables) cannot have NULL 
values, but any other primary key could (in SQLite).


Being an Index by itself means that a Table-Scan is perhaps not as 
inefficient as one might think and indeed using any other index means a 
round-trip reading and hitting values in THAT index, then returning and 
looking up the hit result in the rowid table index, and then reading the 
pages(s) from it and extracting the data - where during a table scan, 
all this round tripping is skipped.


So unless any prospective candidate Index for any query offers a truly 
magnificent cost advantage, a table scan would probably be more 
efficient, and so be chosen.  This is why running ANALYZE on large 
tables is needed, because it allows the QP to better deduce whether a 
prospective Index might in fact offer such a magnificent cost reduction 
or not. Another way is hinting at the QP (Search "likelihood" in the docs).


This is why a non-rowid-alias Primary Key on a rowid table is also less 
efficient to scan than the table itself (often very non-intuitive) - or 
- why a covering index sometimes gets avoided in a JOIN when it seems to 
contain all needed data to fulfill the join obligation.


Also, often a great index is not used simply because the query planner 
does not know enough about it and its prospective cost to obtain a good 
estimate of its utility, and sometimes what feels intuitively to us as a 
great Index just isn't really. The QP is not infallible, but it is quite 
smart.



Cheers,
Ryan



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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[R Smith]

[Repost: used a wrong e-mail at first, apologies if this comes through 
twice]


On 2018/01/08 12:39 PM, x wrote:

Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I 
imagined the RowID as being a separate index which is the root of my confusion. 
It would explain this


To elaborate a little - We often get people here asking "But why it 
table-scans in stead of using my nice Index?".


This stems from an often-held misconception that Indexes are God-sent 
magic to improve everything. The fact is that Indexes are costly 
mechanisms which allows fast lookup which, only AFTER a certain critical 
size and for specific circumstances, become more efficient than a scan. 
The Query Planner has to do a lot of work to figure out what those 
"critical size and specific circumstances" is for any specific query, 
and it does get real fuzzy.


I think I've heard Richard or Dan explain it as follows (if memory 
serves, someone please point out if I'm mistaken):


You can think of an SQLite table as essentially a btree covering Index 
by itself with the Key being the Row_ID (or more recently, the PK for 
WITHOUT ROWID tables). This is why the rowid (or any column serving as 
an alias to it, or the PK for WITHOUT ROWID tables) cannot have NULL 
values, but any other primary key could (in SQLite).


Being an Index by itself means that a Table-Scan is perhaps not as 
inefficient as one might think and indeed using any other index means a 
round-trip reading and hitting values in THAT index, then returning and 
looking up the hit result in the rowid table index, and then reading the 
pages(s) from it and extracting the data - where during a table scan, 
all this round tripping is skipped.


So unless any prospective candidate Index for any query offers a truly 
magnificent cost advantage, a table scan would probably be more 
efficient, and so be chosen.  This is why running ANALYZE on large 
tables is needed, because it allows the QP to better deduce whether a 
prospective Index might in fact offer such a magnificent cost reduction 
or not. Another way is hinting at the QP (Search "likelihood" in the docs).


This is why a non-rowid-alias Primary Key on a rowid table is also less 
efficient to scan than the table itself (often very non-intuitive) - or 
- why a covering index sometimes gets avoided in a JOIN when it seems to 
contain all needed data to fulfill the join obligation.


Also, often a great index is not used simply because the query planner 
does not know enough about it and its prospective cost to obtain a good 
estimate of its utility, and sometimes what feels intuitively to us as a 
great Index just isn't really. The QP is not infallible, but it is quite 
smart.



Cheers,
Ryan



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

Re: [sqlite] sqlite3_column_name with alias

[Bart Smissaert]

> As you can see

Should read:
As you said

RBS

On Mon, Jan 8, 2018 at 11:17 AM, Bart Smissaert 
wrote:

> > Best is to define type on output
>
> Yes, looks that way. As you can see the alias can help, eg:
> select max(integer_date_column) as integer_date_column
>
> what I also added is coding to pick up the intended formatting from the
> alias, not from the column but by just specifying the output datatype:
> select max(integer_date) as int_date
>
> All this is only needed if sqlite3_column_decltype produces null.
> Looks kind of sorted now, thanks.
>
>
> RBS
>
> On Mon, Jan 8, 2018 at 10:29 AM, R Smith  wrote:
>
>>
>> On 2018/01/08 12:00 AM, Bart Smissaert wrote:
>>
>>> OK, I can see your point and I am sure you are right.
>>> All this has to do with the question (discussed not long ago) how one
>>> should know how the output from a statement should be handled by
>>> an application receiving the data. In my case that application is Excel.
>>> Take for example an integer. Excel need to know if this integer should
>>> be formatted as a plain integer or as an Excel date. When the user moves
>>> data to SQLite (from the sheet or from eg a text file) he/she will tell
>>> how
>>> this data should be stored. In this case the data type will be INTEGER or
>>> INT_DATE. This information will be stored in SQLite_master. The question
>>> is now how to match up the columns of the output with these data types in
>>> SQLite_master. I have a simple system for this with sqlite3_column_type
>>> and sqlite3_column_decltype etc. but this is just not fool proof. It
>>> looks
>>> I will need to think up some more elaborate system. One option is that
>>> the
>>> user
>>> just tells (along with the statement) what the output formatting should
>>> be,
>>> but that would be cumbersome.
>>>
>>
>> Yeah, that is a tough one.
>> Even in other databases you won't be able to achieve this. "Type" is used
>> mostly as an internal specification and sometimes output formatter.
>> Preserving the original column type through query abstraction to the output
>> is simply not possible, especially if that type is homemade.  You are
>> interested in an output type, it should be specified within the output.
>>
>> Best is to define type on output. You might decrease cumbersomeness a lot
>> if the list of types is small, using perhaps just a one or two character
>> type identifier, like this:
>>
>> SELECT  foo AS Col1_i,   bar AS Col2_d etc.
>> (Where i denotes INT and d is INT-DATE etc.)
>>
>> If you expect the list of types to grow in future, pick slightly longer
>> identifiers.
>>
>> This of course would not be feasible if you (as in your collective devs)
>> do not control the resulting queries, like if users can make queries on the
>> fly
>>
>> Another method is keeping a table with names and types that the
>> application can read. Add to it all the names used for output and the types
>> implied. Say it contains the name Col1 with Type INT then whenever your
>> application reads a query where the column name is Col1 (via origin or
>> alias) then it knows that is an INT. This is the saddest method and should
>> be avoided if possible.
>>
>> Good luck!
>>
>> 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] sqlite3_column_name with alias

[Bart Smissaert]

> Best is to define type on output

Yes, looks that way. As you can see the alias can help, eg:
select max(integer_date_column) as integer_date_column

what I also added is coding to pick up the intended formatting from the
alias, not from the column but by just specifying the output datatype:
select max(integer_date) as int_date

All this is only needed if sqlite3_column_decltype produces null.
Looks kind of sorted now, thanks.


RBS

On Mon, Jan 8, 2018 at 10:29 AM, R Smith  wrote:

>
> On 2018/01/08 12:00 AM, Bart Smissaert wrote:
>
>> OK, I can see your point and I am sure you are right.
>> All this has to do with the question (discussed not long ago) how one
>> should know how the output from a statement should be handled by
>> an application receiving the data. In my case that application is Excel.
>> Take for example an integer. Excel need to know if this integer should
>> be formatted as a plain integer or as an Excel date. When the user moves
>> data to SQLite (from the sheet or from eg a text file) he/she will tell
>> how
>> this data should be stored. In this case the data type will be INTEGER or
>> INT_DATE. This information will be stored in SQLite_master. The question
>> is now how to match up the columns of the output with these data types in
>> SQLite_master. I have a simple system for this with sqlite3_column_type
>> and sqlite3_column_decltype etc. but this is just not fool proof. It looks
>> I will need to think up some more elaborate system. One option is that the
>> user
>> just tells (along with the statement) what the output formatting should
>> be,
>> but that would be cumbersome.
>>
>
> Yeah, that is a tough one.
> Even in other databases you won't be able to achieve this. "Type" is used
> mostly as an internal specification and sometimes output formatter.
> Preserving the original column type through query abstraction to the output
> is simply not possible, especially if that type is homemade.  You are
> interested in an output type, it should be specified within the output.
>
> Best is to define type on output. You might decrease cumbersomeness a lot
> if the list of types is small, using perhaps just a one or two character
> type identifier, like this:
>
> SELECT  foo AS Col1_i,   bar AS Col2_d etc.
> (Where i denotes INT and d is INT-DATE etc.)
>
> If you expect the list of types to grow in future, pick slightly longer
> identifiers.
>
> This of course would not be feasible if you (as in your collective devs)
> do not control the resulting queries, like if users can make queries on the
> fly
>
> Another method is keeping a table with names and types that the
> application can read. Add to it all the names used for output and the types
> implied. Say it contains the name Col1 with Type INT then whenever your
> application reads a query where the column name is Col1 (via origin or
> alias) then it knows that is an INT. This is the saddest method and should
> be avoided if possible.
>
> Good luck!
>
> 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] difference between 'ID IS NULL' and 'ID = NULL'

[Dominique Devienne]

On Mon, Jan 8, 2018 at 11:39 AM, x  wrote:

> However, I’m still confused. Reading this https://sqlite.org/
> queryplanner.html suggests the table is stored in RowID order. So what
> happens if I insert a record into Tbl with a lower ID than the existing 2.4
> million Ids?
>

It depends if your ID column is an alias for the ROWID special column or
not.

ROWID is the key of the B-tree I believe, so if you insert a row "in the
middle",
many pages have potentially to be rewritten to "rebalance" the B-tree.
While only the "last" page needs updating with AUTO INCREMENT or a ROWID
larger than the previous larger one.

So if ID an alias for ROWID, the insert might be much more expensive.
If ID is not, then the row is simply "appended" at the end, "cheaply".

I could be wrong of course. I'll find out very soon :). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[x]

Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I 
imagined the RowID as being a separate index which is the root of my confusion. 
It would explain this



sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL ORDER BY ID;

0|0|0|SCAN TABLE TBL



contrasted with



sqlite> EXPLAIN QUERY PLAN SELECT X FROM TBL ORDER BY X;

0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX



However, I’m still confused. Reading this https://sqlite.org/queryplanner.html 
suggests the table is stored in RowID order. So what happens if I insert a 
record into Tbl with a lower ID than the existing 2.4 million Ids?






From: sqlite-users  on behalf of 
Scott Robison 
Sent: Sunday, January 7, 2018 7:30:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Integer primary key is by definition not null, so looking for a null value
on an index can't work. I guess there exists an optimization opportunity to
just return an emotional set, though it seems easier to not specify an
impossible condition.

As to why it does a table scan, the primary key isn't a separate index. The
rowid primary key is part of the table itself.

On Jan 7, 2018 11:22 AM, "Luuk"  wrote:

> On 07-01-18 19:09, x wrote:
> >> Because reading the whole record (all 3 fields) is more expensive than
> >> just reading the index which has all the info you need to give a correct
> >> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
> > Yes, but the covering index has 2 fields (X & ID). The pk has only 1
> field (ID) so why not use that, particularly in the case where ‘ORDER BY
> ID’ was included in the query?
> >
> >
> sorry, i do not know
> ___
> 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_column_name with alias

[R Smith]

On 2018/01/08 12:00 AM, Bart Smissaert wrote:

OK, I can see your point and I am sure you are right.
All this has to do with the question (discussed not long ago) how one
should know how the output from a statement should be handled by
an application receiving the data. In my case that application is Excel.
Take for example an integer. Excel need to know if this integer should
be formatted as a plain integer or as an Excel date. When the user moves
data to SQLite (from the sheet or from eg a text file) he/she will tell how
this data should be stored. In this case the data type will be INTEGER or
INT_DATE. This information will be stored in SQLite_master. The question
is now how to match up the columns of the output with these data types in
SQLite_master. I have a simple system for this with sqlite3_column_type
and sqlite3_column_decltype etc. but this is just not fool proof. It looks
I will need to think up some more elaborate system. One option is that the
user
just tells (along with the statement) what the output formatting should be,
but that would be cumbersome.


Yeah, that is a tough one.
Even in other databases you won't be able to achieve this. "Type" is 
used mostly as an internal specification and sometimes output 
formatter.  Preserving the original column type through query 
abstraction to the output is simply not possible, especially if that 
type is homemade.  You are interested in an output type, it should be 
specified within the output.


Best is to define type on output. You might decrease cumbersomeness a 
lot if the list of types is small, using perhaps just a one or two 
character type identifier, like this:


SELECT  foo AS Col1_i,   bar AS Col2_d etc.
(Where i denotes INT and d is INT-DATE etc.)

If you expect the list of types to grow in future, pick slightly longer 
identifiers.


This of course would not be feasible if you (as in your collective devs) 
do not control the resulting queries, like if users can make queries on 
the fly


Another method is keeping a table with names and types that the 
application can read. Add to it all the names used for output and the 
types implied. Say it contains the name Col1 with Type INT then whenever 
your application reads a query where the column name is Col1 (via origin 
or alias) then it knows that is an INT. This is the saddest method and 
should be avoided if possible.


Good luck!
Ryan

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

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[R Smith]

On 2018/01/08 11:17 AM, Shane Dev wrote:


P.S one reason I am using SQL instead the main application to perform such
calculations is precisely to avoid using variables (and hence the evils of
mutable state). Why do you say it is more efficient?


Because it is much more efficient, memory used in stead of computations 
through the DB engine. It's even vastly more efficient when you do these 
things in your programming language (Zero parsing to start with)...


That however doesn't mean you should only do it this way. There are 
obviously merits (such as mutable state evility, if there is such a 
word) to using a less efficient method.


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

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[Shane Dev]

On 8 January 2018 at 09:19, petern  wrote:

> Your inner CTE will have to examine every generated row and count only
> matches toward "running_num".
>
>
Good idea, that works -

sqlite> with r(num, rand, running_num) as (select 1,
cast(round(abs(random())/9223372036854775808) as int), 1 union all select
num+1, cast(round(abs(random())/9223372036854775808) as int), case rand
when 1 then running_num+1 else running_num end from r) select running_num,
num from r where rand=1 limit 3;
running_num num
1   1
2   2
3   6
sqlite>

P.S one reason I am using SQL instead the main application to perform such
calculations is precisely to avoid using variables (and hence the evils of
mutable state). Why do you say it is more efficient?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

[petern]

Your inner CTE will have to examine every generated row and count only
matches toward "running_num".  You'll also need another column like
"last_running_num" with a referencing CASE statement in the "running_num"
column to condition emitting, for example, a non-null "running_num"...

Or you can simply and more efficiently use a local variable extension like
auxint.c on your existing VIEW (without the LIMIT) like so:

sqlite> .load auxint
sqlite> SELECT auxint('id',1)running_num,num FROM vrand limit 10;
running_num,num
1,1
2,2
3,3
4,4
5,5
6,9
7,11
8,14
9,15
10,16

The ~20 lines of code for auxint.c  are here:

https://www.mail-archive.com/sqlite-users@mailinglists.sqlit
e.org/msg107018.html




On Sun, Jan 7, 2018 at 10:37 PM, Shane Dev  wrote:

> Hello,
>
> The view VRAND below generates a series of 3 randomly chosen integers -
>
> CREATE VIEW vrand as with r(num, rand) as (
> select 1, cast(round(abs(random())/9223372036854775808) as int)
> union all
> select num+1, cast(round(abs(random())/9223372036854775808) as int) from
> r)
> select num from r where rand=1 limit 3;
>
> sqlite> select * from vrand;
> num
> 1
> 2
> 4
>
> sqlite> select count(*) from vrand;
> count(*)
> 3
>
> Now I would like to add a "running number" column with a result set like -
>
> running_num, num
> 1, 1
> 2, 2
> 3, 4
>
> However, the follow statement seems to trigger an infinite loop -
>
> select (select count(*) from vrand where num <= v.num), num from vrand as
> v;
>
> How can a running number column be added to this view?
> ___
> 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