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
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/ma
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 yo
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);
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,
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 t
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 understa
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
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
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.
>
>
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
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, th
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
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) I
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 diff
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
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/
__
>-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
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: s
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 sca
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 I
[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 el
> 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
> 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:
sele
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
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
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 applicatio
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
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 s
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 sim
30 matches
Mail list logo
