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
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...
4
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
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
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
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
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)
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
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
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
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:
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
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.
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
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
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,
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?
> 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
> 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
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
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.
> 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)
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
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
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,
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
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
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
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.
> 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
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
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,
32 matches
Mail list logo