Re: [sqlite] Is there a best practice for breaking up a large update?

[Darren Duncan]

You didn't say if the other tasks need write access to the database or if it is 
just read-only.  If the others only need read-only, let them access a copy of 
the database while you make your changes in another copy, then just swap the 
databases when done. -- Darren Duncan


On 2016-10-15 1:21 PM, Kevin O'Gorman wrote:

I'm new to this, and working in Python's sqlite3.  So be patient, and don't
expect me to know too much.  This is also a personal hobby, so there's
nobody else for me to ask.

I've got a database of a some tens of millions of positions in a board
game.  It may be over a billion before I'm done (working in an 11-TB
partition at the moment.) I want to process a major slice of these records,
in order to update other records.  I might want to break the work up
into chunks to allow other access to the database while this is going on.

So I have some questions:
(1) If I do all of my updates to a temporary table, does the database still
get locked?

(2) Is there another way to keep it available?  It happens for this
activity that consistency is not at risk.

(3) If it turns out that I can avoid locking, it there still a performance
reason to break the transaction into chunks, or would I be as well off
doing it as a single transaction (assuming I have disk space for the
journal).

(4) If I break it up into chunks, I can think of several ways to do that
and keep track of what's been done and what has not.  Is there a best
practice for this?

(5) Is there a forum specifically for folks doing Python database
programming?  It occurs to me that much of what I'm asking about is not
specific to SQLite.  But my code, for now at least, is going to be in
Python because it's the easiest way I can see, and my own efforts are the
real bottleneck in making progress.



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

Re: [sqlite] Is there a best practice for breaking up a large update?

[Igor Tandetnik]

On 10/15/2016 4:21 PM, Kevin O'Gorman wrote:

So I have some questions:
(1) If I do all of my updates to a temporary table, does the database still
get locked?


Locked against what? What else are you trying to do with the database 
while the update is in progress?


Look at WAL journal mode: https://www.sqlite.org/wal.html . It allows a 
writing transaction to co-exist with multiple readers. See if this helps.

--
Igor Tandetnik

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Simon Slavin]

On 16 Oct 2016, at 12:32am, Keith Medcalf  wrote:

> The whole reason for using a "manual sortation field" is so that a human can 
> decide what order to display the data in via a "manual" operation.

You answered the question.  To get as close to the OP's requirements as 
possible the order needs to be governed by two fields, an ID field and a 
SortOrder field.  The SortOrder field can default to 1.

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Keith Medcalf]

On Saturday, 15 October, 2016 16:44, Richard Damon , 
wrote:

> Summary of what I have seen:
 
> Schema for table has an auto-increment primary key, and a Date-Time
> field (which has duplicate values)
 
> Records are to be retrieved in Date-Time order, with duplicate values
> needing to be retrieved in a consistent (and perhaps even specified by
> something else)

> The OP was careful to initially insert the records in Date-Time order,
> and records with identical Date-Times in the 'proper' order (so the
> auto-increment field just happens to be the proper total order).

> He then wants to later add a new record, and wants the auto-increment to
> 'automatically' update to keep it in total order.

> It has been pointed out that this is NOT the way an auto-increment field
> is intended to work.
 
> Some people have come out with various ways to implement a sort field
> that leaves gaps to allow at least some level of inserting new records
> between old records.
 
> My thought is that if the first level of sort IS by Date-Time, and then
> by some other condition, then the real solution is to use an ORDER BY
> clause on the Date-Time field and then some other field to implement the
> duplicate value ordering (and likely have a covering index so this ORDER
> BY is efficient). This extra field doesn't need to be globally unique,
> just unique within a given value of Date-Time, so the practical issues
> in processing the primary key are greatly reduced (at minimum, if you
> need to renumber records, there are a lot less that need to be
> renumbered).

As described the first level sort cannot be by DateTime since the datetime 
field does not contain sortable data (it is neither an offset from some epoch  
nor it is an ISO-8601 formatted string (biggest datepart to littlest datepart 
in fixed width format per part) expressed in UTC.  (datetime stored as 
localtime values are inherently unsortable for any location in which the 
timezone has ever changed offset -- which for most wall-clocks is at least 
twice per year).

If the datetime were stored so as to permit sorting by datetime such that an 
ORDER BY could be used, the whole issue would not exist.  Duplicates could be 
eliminated simply by using greater precision in the datetime data stored.

The whole reason for using a "manual sortation field" is so that a human can 
decide what order to display the data in via a "manual" operation.

> On 10/15/16 3:12 PM, Delvin wrote:
> > Afternoon all,
> >
> > I am replying to this thread because I am a little confused here.  From
> what I have gotten from this thread, someone wants to be able to insert a
> record into a table based on an arbitrary record number (i.e. if a table
> already has records number 1, 2, 3, 4, etc., have the ability to insert a
> new record number 3 and changing the record numbers (old record number) 3,
> 4, etc.
> >
> > My confusing is this - are record numbers arbitrary to start with (since
> the table can be sorted in any manner) and the only reason for the record
> number column is to insure that the primary key is unique.
> >
> > Thanks
> > Delvin
> >
> >
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jens Alfke
> > Sent: Saturday, 15 October, 2016 13:42
> > To: SQLite mailing list 
> > Subject: Re: [sqlite] Inserting a new record (anywhere) into a table of
> ordered records that have an integer auto-increment primary key
> >
> >
> >> On Oct 15, 2016, at 11:12 AM, Keith Medcalf 
> wrote:
> >>
> >>> Is there a way to do this automagically (like a specialized INSERT
> >>> command?) in Sqlite?
> >> Unfortunately no, there is no way to do this on *ANY* database that
> uses the relational database model.
> > There’s no need to dive into database theory! I’ll play devil’s advocate
> and say that this could pretty easily be done in SQLite by writing a
> simple extension function like in inbetween(a, b) that takes two strings
> and returns a string that sorts in between them, as I described
> previously.
> >
> > Then you just make the table’s primary key a string and do
> > INSERT INTO mytable (ID, …) VALUES (inbetween($firstid, $secondid),
> …) where $firstid and $secondid are the the primary keys of the two rows
> you want to insert between.
> >
> > In real life you’d probably just implement inbetween() as part of your
> program instead of as a SQLite function; I just wanted to prove that a
> relational database can in fact do this.
> >
> > —Jens



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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Simon Slavin]

On 15 Oct 2016, at 11:43pm, Richard Damon  wrote:

> My thought is that if the first level of sort IS by Date-Time, and then by 
> some other condition, then the real solution is to use an ORDER BY clause on 
> the Date-Time field and then some other field to implement the duplicate 
> value ordering (and likely have a covering index so this ORDER BY is 
> efficient). This extra field doesn't need to be globally unique, just unique 
> within a given value of Date-Time, so the practical issues in processing the 
> primary key are greatly reduced (at minimum, if you need to renumber records, 
> there are a lot less that need to be renumbered).

Nice summary above that.

The real question is why the OP wants this strange ordering.  The standard sort 
of search on this table would be like this:

SELECT * FROM MyTable ORDER BY Date, ID

Nothing special has to be done for this to do what I think the OP wanted in the 
first place.  New insertions will have ID's greater than the existing ones.  
It'll just work.

In checking field names I just noticed this:

> 10OCT-08:13:47

Just to remind the OP that this is a string of text.  SQLite doesn't have a 
date/time type.  Those strings will not be sorted into date/time order.  To 
make that happen you'd need to use a format more like

20081010:13:45

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Richard Damon]

Summary of what I have seen:

Schema for table has an auto-increment primary key, and a Date-Time 
field (which has duplicate values)


Records are to be retrieved in Date-Time order, with duplicate values 
needing to be retrieved in a consistent (and perhaps even specified by 
something else)


The OP was careful to initially insert the records in Date-Time order, 
and records with identical Date-Times in the 'proper' order (so the 
auto-increment field just happens to be the proper total order).


He then wants to later add a new record, and wants the auto-increment to 
'automatically' update to keep it in total order.


It has been pointed out that this is NOT the way an auto-increment field 
is intended to work.


Some people have come out with various ways to implement a sort field 
that leaves gaps to allow at least some level of inserting new records 
between old records.


My thought is that if the first level of sort IS by Date-Time, and then 
by some other condition, then the real solution is to use an ORDER BY 
clause on the Date-Time field and then some other field to implement the 
duplicate value ordering (and likely have a covering index so this ORDER 
BY is efficient). This extra field doesn't need to be globally unique, 
just unique within a given value of Date-Time, so the practical issues 
in processing the primary key are greatly reduced (at minimum, if you 
need to renumber records, there are a lot less that need to be renumbered).



On 10/15/16 3:12 PM, Delvin wrote:

Afternoon all,

I am replying to this thread because I am a little confused here.  From what I 
have gotten from this thread, someone wants to be able to insert a record into 
a table based on an arbitrary record number (i.e. if a table already has 
records number 1, 2, 3, 4, etc., have the ability to insert a new record number 
3 and changing the record numbers (old record number) 3, 4, etc.

My confusing is this - are record numbers arbitrary to start with (since the 
table can be sorted in any manner) and the only reason for the record number 
column is to insure that the primary key is unique.

Thanks
Delvin


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Saturday, 15 October, 2016 13:42
To: SQLite mailing list 
Subject: Re: [sqlite] Inserting a new record (anywhere) into a table of ordered 
records that have an integer auto-increment primary key



On Oct 15, 2016, at 11:12 AM, Keith Medcalf  wrote:


Is there a way to do this automagically (like a specialized INSERT
command?) in Sqlite?

Unfortunately no, there is no way to do this on *ANY* database that uses the 
relational database model.

There’s no need to dive into database theory! I’ll play devil’s advocate and 
say that this could pretty easily be done in SQLite by writing a simple 
extension function like in inbetween(a, b) that takes two strings and returns a 
string that sorts in between them, as I described previously.

Then you just make the table’s primary key a string and do
INSERT INTO mytable (ID, …) VALUES (inbetween($firstid, $secondid), …) 
where $firstid and $secondid are the the primary keys of the two rows you want 
to insert between.

In real life you’d probably just implement inbetween() as part of your program 
instead of as a SQLite function; I just wanted to prove that a relational 
database can in fact do this.

—Jens

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

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



--
Richard Damon

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

Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)

[Simon Slavin]

On 15 Oct 2016, at 7:34pm, Jens Alfke  wrote:

> I do, actually, which is why I asked. One of the columns is a blob holding a 
> JSON document that can be arbitrarily large. It sounds like including this 
> column in the SELECT clause will cause the entire blob to be read from disk? 
> I definitely don’t want that.

When reading a row of the data asked for SQLite reads through the first column 
of the row to the end of the column which is last in the table.  In other words

CREATE TABLE MyTable (a, b, c, d, e, f, g, h);
SELECT c, f FROM MyTable;

means that SQLite has to read through columns from a to f.  It won't fetch the 
data from long TEXT or BLOB values but it needs to know how long they are to 
figure out where the following column's value starts.

So if you don't need column f don't ask for it.  And definitely don't use

SELECT * FROM MyTable;

unless that's what you need.

The additional twist that Dominique mentioned is that in SQLite an index entry 
contains the values it's indexing.  So suppose you have the above table and

CREATE INDEX i1 on MyTable (c, d, e);
SELECT c, d, e, f FROM MyTable ORDER BY c, d;

then if your SELECT ends up using that index the only value SQLite actually 
needs to fetch from the table is the one for column f.  Because it already has 
the values for c, d and e because it just read them from the index.  And if 
SQLite doesn't need to read any values from the table it doesn't bother reading 
the table at all. Which speeds things up a lot.

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

Re: [sqlite] Version 3.15.0 released

[Rich Shepard]

On Fri, 14 Oct 2016, D. Richard Hipp wrote:


SQLite version 3.15.0 is now available on the SQLite website:


  Thank you, Richard and all other devs, for an excellent product. Your
efforts are certainly appreicated even when not publicly expressed.

Carpe weekend,

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

[sqlite] Is there a best practice for breaking up a large update?

[Kevin O'Gorman]

I'm new to this, and working in Python's sqlite3.  So be patient, and don't
expect me to know too much.  This is also a personal hobby, so there's
nobody else for me to ask.

I've got a database of a some tens of millions of positions in a board
game.  It may be over a billion before I'm done (working in an 11-TB
partition at the moment.) I want to process a major slice of these records,
in order to update other records.  I might want to break the work up
into chunks to allow other access to the database while this is going on.

So I have some questions:
(1) If I do all of my updates to a temporary table, does the database still
get locked?

(2) Is there another way to keep it available?  It happens for this
activity that consistency is not at risk.

(3) If it turns out that I can avoid locking, it there still a performance
reason to break the transaction into chunks, or would I be as well off
doing it as a single transaction (assuming I have disk space for the
journal).

(4) If I break it up into chunks, I can think of several ways to do that
and keep track of what's been done and what has not.  Is there a best
practice for this?

(5) Is there a forum specifically for folks doing Python database
programming?  It occurs to me that much of what I'm asking about is not
specific to SQLite.  But my code, for now at least, is going to be in
Python because it's the easiest way I can see, and my own efforts are the
real bottleneck in making progress.

-- 
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)

[Dominique Pellé]

Jens Alfke  wrote:

> In a simple SELECT query of a single table, using the C API,
> is there any difference in performance for requesting more or
> fewer columns of the table in the result? Or is the performance
> penalty only incurred when actually reading the column values?

There can sometimes be a big difference.

The less columns you select, the more chance you have
to be able to use a covering index.  If the query can use
a covering index, it's likely to have a significant speed up.
According to section 8.0 at https://www.sqlite.org/optoverview.html
being able to use a covering index makes the query about
twice as fast.

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

Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)

[Clemens Ladisch]

Jens Alfke wrote:
> What if I’ve enabled memory-mapping? In that case will the register
> merely point to where the blob data is mapped into memory

This would not work because the data might not be in consecutive pages.
(The database file format was not designed for memory mapping.)


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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Luca Olivetti]

El 15/10/16 a les 20:53, Jens Alfke ha escrit:




[*] I have to insert records in the given order but the user may occasionally 
rearrange them.


What happens if the user makes a series of rearrangements that triggers a 
collision? It’s not that far fetched; all I have to do is, one at a time, drag 
ten records to right after one particular record. This is the kind of thing a 
good [read: malicious] tester will quickly figure out :)


I show a message "sorry, not enough internal space to complete the 
operation", but in around 10 years of several of these systems working 
24x7 it never happened.


Bye
--
Luca Olivetti
Wetron Automation Technology http://www.wetron.es/
Tel. +34 93 5883004 (Ext.3010)  Fax +34 93 5883007
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Delvin]

Afternoon all,

I am replying to this thread because I am a little confused here.  From what I 
have gotten from this thread, someone wants to be able to insert a record into 
a table based on an arbitrary record number (i.e. if a table already has 
records number 1, 2, 3, 4, etc., have the ability to insert a new record number 
3 and changing the record numbers (old record number) 3, 4, etc.

My confusing is this - are record numbers arbitrary to start with (since the 
table can be sorted in any manner) and the only reason for the record number 
column is to insure that the primary key is unique.

Thanks
Delvin


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Saturday, 15 October, 2016 13:42
To: SQLite mailing list 
Subject: Re: [sqlite] Inserting a new record (anywhere) into a table of ordered 
records that have an integer auto-increment primary key


> On Oct 15, 2016, at 11:12 AM, Keith Medcalf  wrote:
> 
>> Is there a way to do this automagically (like a specialized INSERT
>> command?) in Sqlite?
> 
> Unfortunately no, there is no way to do this on *ANY* database that uses the 
> relational database model.

There’s no need to dive into database theory! I’ll play devil’s advocate and 
say that this could pretty easily be done in SQLite by writing a simple 
extension function like in inbetween(a, b) that takes two strings and returns a 
string that sorts in between them, as I described previously.

Then you just make the table’s primary key a string and do
INSERT INTO mytable (ID, …) VALUES (inbetween($firstid, $secondid), …) 
where $firstid and $secondid are the the primary keys of the two rows you want 
to insert between.

In real life you’d probably just implement inbetween() as part of your program 
instead of as a SQLite function; I just wanted to prove that a relational 
database can in fact do this.

—Jens

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

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

[sqlite] a small defrag tool for SQLite database file

[Quan Yong Zhai]

Hi guys,
I have a modified version of the “scrub” tool, the origin one found in  SQLite 
source tree /ext/misc/scrub.c
It’s a defrag tool for SQLite database file,  implement a function drop out all 
freelist-pages and rearrange
the other useful databases pages. And it’s blazing faster than ‘Vacuum’.

https://github.com/nanazhai/misc

Hope you like it.

Sent from Mail for Windows 10
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[J Decker]

On Sat, Oct 15, 2016 at 11:58 AM, Keith Medcalf  wrote:

>
> I'd just sort the data into the order I wanted the result set presented in
> using an ORDER BY clause on the SELECT which retrieves the data ...
>

One other thought I saw someone else propose a while ago... treat the
number as floating piont, and you can insert at (prior+current)/2 ... 1.5,
1.25, 1.125 etc


>
> > > On Oct 15, 2016, at 11:12 AM, Keith Medcalf 
> wrote:
> > >
> > >> Is there a way to do this automagically (like a specialized INSERT
> > >> command?) in Sqlite?
> > >
> > > Unfortunately no, there is no way to do this on *ANY* database that
> uses
> > the relational database model.
> >
> > There’s no need to dive into database theory! I’ll play devil’s advocate
> > and say that this could pretty easily be done in SQLite by writing a
> > simple extension function like in inbetween(a, b) that takes two strings
> > and returns a string that sorts in between them, as I described
> > previously.
> >
> > Then you just make the table’s primary key a string and do
> >   INSERT INTO mytable (ID, …) VALUES (inbetween($firstid, $secondid),
> > …)
> > where $firstid and $secondid are the the primary keys of the two rows you
> > want to insert between.
> >
> > In real life you’d probably just implement inbetween() as part of your
> > program instead of as a SQLite function; I just wanted to prove that a
> > relational database can in fact do this.
> >
> > —Jens
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[J Decker]

On Sat, Oct 15, 2016 at 10:18 AM, Stephen Chrzanowski 
wrote:

> What you want to do is called using a linked list.  Each record knows what
> its previous record is.
>
> SQLite doesn't exactly have that capability directly.  No SQL engine that I
> know of has the capability.  Each row is unaware of any other row in that
> table.  That row is a unique entity to itself, oblivious to anything
> outside itself.  To answer your question, no, there is no SQL-FU that'll do
> what you want to do.
>
>
CTEs can handle returning linked lists as a normal recordset



> To get what you want done, you must rely on your application to handle
> this.
>
> Back in the day, when using "recordset"s and "collections", we had to rely
> on extra variables to associate what the previous and next records in our
> list were supposed to be.  So our recordset would have an extra integer to
> point to a memory location to where the next record was in memory.
>
> When I'd so this, since I knew what record I wanted to insert my new record
> after, I'd set a temporary variable to point to the old record I'd want to
> insert after, retrieve what that old records "next record" pointer is, set
> the old records "next record" to my the new record, then set my new records
> "next record" to what the old record "next record" was.
>
> You might have to do the same with this.  You know you want to insert
> something between ID 2 and 3.  So if your table had a reference field to
> what its next record was, you'd
> - retrieve row 2 to get its "next record" ID,
> - do the insert of your new data and set its "NextID" to some never to be
> used number (Like -1?)
> - figure out what record ID you put in (In this example, it'd be 6), then
> update row 2 to set its next value to 6.
>
> Then, theoretically in my head it works, when you make your call to
> retrieve your sorted list, you sort by your "NextID" field, not ID.
> Something like "order by NextID=-1,NextID".
>
>
> On Fri, Oct 14, 2016 at 9:29 AM, Thom Wharton <
> twhar...@northpointdefense.com> wrote:
>
> > Hello,
> >
> > I have a table of records in a Sqlite DB.  It contains 5 records. Each
> > record has a time-stamp which is not guaranteed to be unique.  To
> preserve
> > order (which is important in my project), I've given the table an integer
> > primary key (called ID) that is auto-increment.  Let's say I have the
> > following table...
> >
> >
> > IDDate   Type Size  Data
> >
> > 110OCT-08:13:47  Ether28sddsgsd...
> >
> > 210OCT-08:13:52  Ether77fdasfdsdsddssdg...
> >
> > 310OCT-08:13:52  Ether44zeasfkkfa...
> >
> > 410OCT-08:13:57  Ether33dartdg...
> >
> > 510OCT-08:14:03  Ether51afafsfafa...
> >
> >
> > I want to be able to programmatically insert a new record anywhere in
> that
> > table.  Let's suppose I want to create a new record between the records
> > whose ID are 2 and 3.  This new record would need to take the ID of 3,
> and
> > all subsequent records would need to have their primary keys updated.
> >
> > Is there a way to do this automagically (like a specialized INSERT
> > command?) in Sqlite?
> >
> > Thanks,
> >
> > Thom Wharton
> >
> >
> >
> > ___
> > 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] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Keith Medcalf]

I'd just sort the data into the order I wanted the result set presented in 
using an ORDER BY clause on the SELECT which retrieves the data ...

> > On Oct 15, 2016, at 11:12 AM, Keith Medcalf  wrote:
> >
> >> Is there a way to do this automagically (like a specialized INSERT
> >> command?) in Sqlite?
> >
> > Unfortunately no, there is no way to do this on *ANY* database that uses
> the relational database model.
> 
> There’s no need to dive into database theory! I’ll play devil’s advocate
> and say that this could pretty easily be done in SQLite by writing a
> simple extension function like in inbetween(a, b) that takes two strings
> and returns a string that sorts in between them, as I described
> previously.
> 
> Then you just make the table’s primary key a string and do
>   INSERT INTO mytable (ID, …) VALUES (inbetween($firstid, $secondid),
> …)
> where $firstid and $secondid are the the primary keys of the two rows you
> want to insert between.
> 
> In real life you’d probably just implement inbetween() as part of your
> program instead of as a SQLite function; I just wanted to prove that a
> relational database can in fact do this.
> 
> —Jens




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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Jens Alfke]

> On Oct 15, 2016, at 11:38 AM, Luca Olivetti  wrote:
> 
> I'm aware of the limitation but for my application[*] it is perfectly fine, 
> maybe it is also ok for the OP, maybe it isn't.

OK, but if you’re proposing a solution that you know has major limitations, I 
think it’s a good idea to disclose them, since the person taking the advice 
might not be aware of them. (I’ve been burned by this in the past.)

> [*] I have to insert records in the given order but the user may occasionally 
> rearrange them.

What happens if the user makes a series of rearrangements that triggers a 
collision? It’s not that far fetched; all I have to do is, one at a time, drag 
ten records to right after one particular record. This is the kind of thing a 
good [read: malicious] tester will quickly figure out :)

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Jens Alfke]

> On Oct 15, 2016, at 11:12 AM, Keith Medcalf  wrote:
> 
>> Is there a way to do this automagically (like a specialized INSERT
>> command?) in Sqlite?
> 
> Unfortunately no, there is no way to do this on *ANY* database that uses the 
> relational database model.

There’s no need to dive into database theory! I’ll play devil’s advocate and 
say that this could pretty easily be done in SQLite by writing a simple 
extension function like in inbetween(a, b) that takes two strings and returns a 
string that sorts in between them, as I described previously.

Then you just make the table’s primary key a string and do
INSERT INTO mytable (ID, …) VALUES (inbetween($firstid, $secondid), …)
where $firstid and $secondid are the the primary keys of the two rows you want 
to insert between.

In real life you’d probably just implement inbetween() as part of your program 
instead of as a SQLite function; I just wanted to prove that a relational 
database can in fact do this.

—Jens

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Luca Olivetti]

El 15/10/16 a les 19:49, Jens Alfke ha escrit:



On Oct 15, 2016, at 7:42 AM, Luca Olivetti  wrote:

Instead of an autoincrement you could increment it manually in, say, 1000 
increments.
Then, when you have to insert something between 1000 and 2000 you just use 1500.


Unfortunately this breaks down after log2(1000) ≈ 10 insertions, when you may 
have to insert in between two consecutive IDs like 1324 and 1325.


I'm aware of the limitation but for my application[*] it is perfectly 
fine, maybe it is also ok for the OP, maybe it isn't.


[*] I have to insert records in the given order but the user may 
occasionally rearrange them.


Bye
--
Luca Olivetti
Wetron Automation Technology http://www.wetron.es/
Tel. +34 93 5883004 (Ext.3010)  Fax +34 93 5883007
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)

[Jens Alfke]

> On Oct 15, 2016, at 11:10 AM, Clemens Ladisch  wrote:
> 
> In practice, this does not matter unless you have large strings/blobs
> that must be read from overflow pages.

I do, actually, which is why I asked. One of the columns is a blob holding a 
JSON document that can be arbitrarily large. It sounds like including this 
column in the SELECT clause will cause the entire blob to be read from disk? I 
definitely don’t want that.

What if I’ve enabled memory-mapping? In that case will the register merely 
point to where the blob data is mapped into memory, without incurring any I/O 
until I access that memory? (Maybe that’s naïve; I know SQLite isn’t LMDB. I 
don’t have any insight into how it makes use of memory mapping.)

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Keith Medcalf]

On Friday, 14 October, 2016 07:29, Thom Wharton 
 wrote:

> I have a table of records in a Sqlite DB.  It contains 5 records. Each
> record has a time-stamp which is not guaranteed to be unique.  To
> preserve order (which is important in my project), I've given the table
> an integer primary key (called ID) that is auto-increment.  Let's say I
> have the following table...
 
> IDDate   Type Size  Data
> 110OCT-08:13:47  Ether28sddsgsd...
> 210OCT-08:13:52  Ether77fdasfdsdsddssdg...
> 310OCT-08:13:52  Ether44zeasfkkfa...
> 410OCT-08:13:57  Ether33dartdg...
> 510OCT-08:14:03  Ether51afafsfafa...

> I want to be able to programmatically insert a new record anywhere in
> that table.  Let's suppose I want to create a new record between the
> records whose ID are 2 and 3.  This new record would need to take the ID
> of 3, and all subsequent records would need to have their primary keys
> updated.

> Is there a way to do this automagically (like a specialized INSERT
> command?) in Sqlite?

Unfortunately no, there is no way to do this on *ANY* database that uses the 
relational database model.  You see, in the relational database model there is 
no such things as "records".  What you have above (a table) consists of a "bag 
of tuples".  There is no order to them.  They are merely a "bag" (known 
mathematically as a set).  When you issue a query (SELECT) you project sets and 
conditions and obtain a set of data as a result (another bag of data). 

If you wish an order, you tell it what order to put it in (using an ORDER BY 
clause).  Due to the inherently sequential nature of modern computing 
technology most programming languages are unable to deal with the data in its 
native form (a set) and therefore you retrieve and work on them one after each, 
in forward order only, in the order you requested presentation using the ORDER 
BY clause.

The "integer primary key" (whether you use autoincrement of not) is simply a 
unique key in each record that is derived sequentially (unless you provide it) 
based on the previous (or biggest ever previously used, if you use 
autoincrement) number in that field, to uniquely identify the contents of that 
tuple.  It does not contain any useful information which you should be 
manipulating in any way other than using it as a linkage to perhaps other 
related tuples in other tables.

See https://en.wikipedia.org/wiki/Relational_model

> Thanks,
> Thom Wharton




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

Re: [sqlite] Any performance penalty for SELECTing more columns? (C API)

[Clemens Ladisch]

Jens Alfke wrote:
> is there any difference in performance for requesting more or fewer
> columns of the table in the result? Or is the performance penalty only
> incurred when actually reading the column values?

During the sqlite3_step() call, all values in the SELECT clause are
copied into temporary registers.

In practice, this does not matter unless you have large strings/blobs
that must be read from overflow pages.


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

[sqlite] Any performance penalty for SELECTing more columns? (C API)

[Jens Alfke]

In a simple SELECT query of a single table, using the C API, is there any 
difference in performance for requesting more or fewer columns of the table in 
the result? Or is the performance penalty only incurred when actually reading 
the column values?

For example, lets say a table has 26 columns, some of which can be large 
strings/blobs. I prepare two statements:
SELECT a FROM mytable WHERE …
SELECT a,b,c,d,…,z FROM mytable WHERE …
If I run both of those queries, but in my loop I only read the value of ‘a’, by 
calling sqlite3_column_text(stmt, 0), is there any difference in speed?

I am guessing that there isn’t a difference. (I could run tests, but I don’t 
want to be dependent on a detail that might change.) If so, this will make it 
easier to create my queries, since I won’t have to fine-tune their column sets 
based on what columns I need in each different use case.

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Jens Alfke]

> On Oct 15, 2016, at 7:42 AM, Luca Olivetti  wrote:
> 
> Instead of an autoincrement you could increment it manually in, say, 1000 
> increments.
> Then, when you have to insert something between 1000 and 2000 you just use 
> 1500.

Unfortunately this breaks down after log2(1000) ≈ 10 insertions, when you may 
have to insert in between two consecutive IDs like 1324 and 1325. “Fine, then 
I’ll go to floating point.” Unfortunately that only gives you a finite number 
more bits … a dozen or two more insertions before you run out of room in more 
insidious ways due to floating-point roundoff error.

A much better way to create an ordered sequence you can always insert into, is 
to use strings. As a simplified example, initially assign the IDs “A”, “B”, 
“C”, “D”… Then when you need to insert between “C” and “D”, use “CM”. Between 
“C” and “CM” is “CF”. Eventually you may have to insert between “CD” and “CE” … 
use “CDM”. It’s pretty clear that this can go on ‘forever’.

(This works best when you have a finite space you need to subdivide, which 
isn’t true here. So “What comes after “Z”? Well, “ZM”. Obviously this example 
scheme is not optimized for frequent appending to the end! It’s really the same 
problem that comes up with search trees, since this is equivalent to a 26-way 
tree where the nodes are expressed as paths from the root.)

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Stephen Chrzanowski]

What you want to do is called using a linked list.  Each record knows what
its previous record is.

SQLite doesn't exactly have that capability directly.  No SQL engine that I
know of has the capability.  Each row is unaware of any other row in that
table.  That row is a unique entity to itself, oblivious to anything
outside itself.  To answer your question, no, there is no SQL-FU that'll do
what you want to do.

To get what you want done, you must rely on your application to handle this.

Back in the day, when using "recordset"s and "collections", we had to rely
on extra variables to associate what the previous and next records in our
list were supposed to be.  So our recordset would have an extra integer to
point to a memory location to where the next record was in memory.

When I'd so this, since I knew what record I wanted to insert my new record
after, I'd set a temporary variable to point to the old record I'd want to
insert after, retrieve what that old records "next record" pointer is, set
the old records "next record" to my the new record, then set my new records
"next record" to what the old record "next record" was.

You might have to do the same with this.  You know you want to insert
something between ID 2 and 3.  So if your table had a reference field to
what its next record was, you'd
- retrieve row 2 to get its "next record" ID,
- do the insert of your new data and set its "NextID" to some never to be
used number (Like -1?)
- figure out what record ID you put in (In this example, it'd be 6), then
update row 2 to set its next value to 6.

Then, theoretically in my head it works, when you make your call to
retrieve your sorted list, you sort by your "NextID" field, not ID.
Something like "order by NextID=-1,NextID".


On Fri, Oct 14, 2016 at 9:29 AM, Thom Wharton <
twhar...@northpointdefense.com> wrote:

> Hello,
>
> I have a table of records in a Sqlite DB.  It contains 5 records. Each
> record has a time-stamp which is not guaranteed to be unique.  To preserve
> order (which is important in my project), I've given the table an integer
> primary key (called ID) that is auto-increment.  Let's say I have the
> following table...
>
>
> IDDate   Type Size  Data
>
> 110OCT-08:13:47  Ether28sddsgsd...
>
> 210OCT-08:13:52  Ether77fdasfdsdsddssdg...
>
> 310OCT-08:13:52  Ether44zeasfkkfa...
>
> 410OCT-08:13:57  Ether33dartdg...
>
> 510OCT-08:14:03  Ether51afafsfafa...
>
>
> I want to be able to programmatically insert a new record anywhere in that
> table.  Let's suppose I want to create a new record between the records
> whose ID are 2 and 3.  This new record would need to take the ID of 3, and
> all subsequent records would need to have their primary keys updated.
>
> Is there a way to do this automagically (like a specialized INSERT
> command?) in Sqlite?
>
> Thanks,
>
> Thom Wharton
>
>
>
> ___
> 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] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Richard Damon]

On 10/15/16 12:15 PM, Simon Slavin wrote:

On 14 Oct 2016, at 2:29pm, Thom Wharton  wrote:


I want to be able to programmatically insert a new record anywhere in that 
table. Let's suppose I want to create a new record between the records whose ID 
are 2 and 3.  This new record would need to take the ID of 3, and all 
subsequent records would need to have their primary keys updated.

Is there a way to do this automagically (like a specialized INSERT command?) in 
Sqlite?

It's a silly thing to want to do since it makes a nonsense of what IDs are for. 
 I think you need to rethink what you're trying to do by changing existing IDs.

However, if you really need to do it ...

UPDATE MyTable SET ID = ID + 1 WHERE ID >= 3;
INSERT INTO MyTable ...

Simon.

And, if any other table refers to records in that table via that ID 
field, THEY need to be changed too, all in an 'atomic' transaction.



--
Richard Damon

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Simon Slavin]

On 14 Oct 2016, at 2:29pm, Thom Wharton  wrote:

> I want to be able to programmatically insert a new record anywhere in that 
> table. Let's suppose I want to create a new record between the records whose 
> ID are 2 and 3.  This new record would need to take the ID of 3, and all 
> subsequent records would need to have their primary keys updated.
> 
> Is there a way to do this automagically (like a specialized INSERT command?) 
> in Sqlite?

It's a silly thing to want to do since it makes a nonsense of what IDs are for. 
 I think you need to rethink what you're trying to do by changing existing IDs.

However, if you really need to do it ...

UPDATE MyTable SET ID = ID + 1 WHERE ID >= 3;
INSERT INTO MyTable ...

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Luca Olivetti]

El 14/10/16 a les 15:29, Thom Wharton ha escrit:

Hello,

I have a table of records in a Sqlite DB.  It contains 5 records. Each
record has a time-stamp which is not guaranteed to be unique.  To
preserve order (which is important in my project), I've given the table
an integer primary key (called ID) that is auto-increment.


Instead of an autoincrement you could increment it manually in, say, 
1000 increments.
Then, when you have to insert something between 1000 and 2000 you just 
use 1500.



Bye.
--
Luca Olivetti
Wetron Automation Technology http://www.wetron.es/
Tel. +34 93 5883004 (Ext.3010)  Fax +34 93 5883007
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Richard Damon]

If you sort rule is by Date (and time) and ID as a tie breaker, then you 
should have your sort key be THAT, not just your ID field (I.e., your 
index/order would be Data, ID not just ID)


An auto-increment primary key is to give every record a unique id to 
refer to it, the only order that it can easily hold is order of creation.


Now you are saying that the new record should be between two records 
with the same time stamp. Now you are saying that while originally you 
said ordered by time stamp, with equal keys having a stable order, to 
them having a specific order (not just needing to be stable). Is your 
time stamp not precise enough and from other data you know better? (if 
so, you need a better time-stamp in the index, perhaps as a supplemental 
field for the fraction of the second.


The Summary, an auto-increment field shouldn't be used to enforce an 
order that comes from something else. It can provide a 'stable' (but 
arbitrary) order and the end of the sort condition, but you should not 
anticipate changing the auto-index values to try and create some other 
order.



On 10/14/16 9:29 AM, Thom Wharton wrote:

Hello,

I have a table of records in a Sqlite DB.  It contains 5 records. Each 
record has a time-stamp which is not guaranteed to be unique.  To 
preserve order (which is important in my project), I've given the 
table an integer primary key (called ID) that is auto-increment.  
Let's say I have the following table...



IDDate   Type Size  Data

110OCT-08:13:47  Ether28sddsgsd...

210OCT-08:13:52  Ether77fdasfdsdsddssdg...

310OCT-08:13:52  Ether44zeasfkkfa...

410OCT-08:13:57  Ether33dartdg...

510OCT-08:14:03  Ether51afafsfafa...


I want to be able to programmatically insert a new record anywhere in 
that table.  Let's suppose I want to create a new record between the 
records whose ID are 2 and 3.  This new record would need to take the 
ID of 3, and all subsequent records would need to have their primary 
keys updated.


Is there a way to do this automagically (like a specialized INSERT 
command?) in Sqlite?


Thanks,

Thom Wharton



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



--
Richard Damon

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

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Igor Tandetnik]

On 10/14/2016 9:29 AM, Thom Wharton wrote:

IDDate   Type Size  Data

110OCT-08:13:47  Ether28sddsgsd...

210OCT-08:13:52  Ether77fdasfdsdsddssdg...

310OCT-08:13:52  Ether44zeasfkkfa...

410OCT-08:13:57  Ether33dartdg...

510OCT-08:14:03  Ether51afafsfafa...


I want to be able to programmatically insert a new record anywhere in
that table.  Let's suppose I want to create a new record between the
records whose ID are 2 and 3.


Why? Is there some indicator you haven't told us about that suggests the 
new record should go between 2 and 3? If so, make that indicator part of 
the row data, and use it in your ordering to break ties between 
timestamps. It makes no sense to use RowId for that.

--
Igor Tandetnik

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

[sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

[Thom Wharton]

Hello,

I have a table of records in a Sqlite DB.  It contains 5 records. Each 
record has a time-stamp which is not guaranteed to be unique.  To 
preserve order (which is important in my project), I've given the table 
an integer primary key (called ID) that is auto-increment.  Let's say I 
have the following table...



IDDate   Type Size  Data

110OCT-08:13:47  Ether28sddsgsd...

210OCT-08:13:52  Ether77fdasfdsdsddssdg...

310OCT-08:13:52  Ether44zeasfkkfa...

410OCT-08:13:57  Ether33dartdg...

510OCT-08:14:03  Ether51afafsfafa...


I want to be able to programmatically insert a new record anywhere in 
that table.  Let's suppose I want to create a new record between the 
records whose ID are 2 and 3.  This new record would need to take the ID 
of 3, and all subsequent records would need to have their primary keys 
updated.


Is there a way to do this automagically (like a specialized INSERT 
command?) in Sqlite?


Thanks,

Thom Wharton



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