Quest for "scratch table" implementation in SQLite.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Quest for "scratch table" implementation in SQLite.

Randall Smith
I'm still trying to figure out a graceful way to implement a "scratch" database table that has the following properties:

        o Can be created on demand while a database connection is open.
        o Can include references to other material in the database to ensure integrity.
        o Will be automatically deleted when the database is closed.

TEMPORARY tables can't do the second bullet, as I understand it.

Right now I have application code that manages such tables, but it seems clunky and error-prone.  Am I missing any SQLite feature that would let me do this more easily?

Thanks for any suggestions.

Randall.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quest for "scratch table" implementation in SQLite.

David Raymond
Probably not the most elegant solution, but with enough triggers you can simulate close approximations to foreign keys.

create table permTable (pk integer primary key, t text);

create temp table tempTable (pk int /*references permTable on update cascade on delete set null*/);

create temp trigger trg_insert_check
before insert on tempTable
when new.pk is not null and not exists (select 1 from permTable where pk = new.pk)
begin select raise(abort, 'Key constraint 1'); end;

create temp trigger trg_update_check
before update of pk on tempTable
when new.pk is not null and not exists (select 1 from permTable where pk = new.pk)
begin select raise(abort, 'Key constraint 2'); end;

create temp trigger trg_update_cascade
after update of pk on permTable
begin update tempTable set pk = new.pk where pk = old.pk; end;

create temp trigger trg_delete_null
after delete on permTable
begin update tempTable set pk = null where pk = old.pk; end;


And to test it:
insert into permTable values (1, 'one'), (2, 'two');
insert into tempTable values (3);
insert into tempTable values (2);
select * from tempTable;
update tempTable set pk = 3 where pk = 2;
update permTable set pk = 3 where pk = 2;
select * from tempTable;
delete from permTable where pk = 3;
select * from tempTable;



sqlite> insert into permTable values (1, 'one'), (2, 'two');

sqlite> insert into tempTable values (3);
Error: Key constraint 1

sqlite> insert into tempTable values (2);

sqlite> select * from tempTable;
pk
2

sqlite> update tempTable set pk = 3 where pk = 2;
Error: Key constraint 2

sqlite> update permTable set pk = 3 where pk = 2;

sqlite> select * from tempTable;
pk
3
sqlite> delete from permTable where pk = 3;

sqlite> select * from tempTable;
pk
NULL


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Smith, Randall
Sent: Thursday, January 05, 2017 2:26 PM
To: [hidden email]
Subject: [sqlite] Quest for "scratch table" implementation in SQLite.

I'm still trying to figure out a graceful way to implement a "scratch" database table that has the following properties:

        o Can be created on demand while a database connection is open.
        o Can include references to other material in the database to ensure integrity.
        o Will be automatically deleted when the database is closed.

TEMPORARY tables can't do the second bullet, as I understand it.

Right now I have application code that manages such tables, but it seems clunky and error-prone.  Am I missing any SQLite feature that would let me do this more easily?

Thanks for any suggestions.

Randall.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quest for "scratch table" implementation in SQLite.

David Raymond
In reply to this post by Randall Smith
The usual caveats apply of course
-Temporary tables and triggers are only visible by your own connection, so if anyone else came in and made updates then everything would get thrown out of synch and become dangerous. If it's all within a single transaction then it should be ok.

-Since triggers are always for each row, then there would be no such thing as a deferred "temporary fake reference"

-Umm, other limitations that my melted end-of-the-day brain can't think of right at the moment.



-----Original Message-----
From: David Raymond
Sent: Thursday, January 05, 2017 3:19 PM
To: 'SQLite mailing list'
Subject: RE: Quest for "scratch table" implementation in SQLite.

Probably not the most elegant solution, but with enough triggers you can simulate close approximations to foreign keys.

create table permTable (pk integer primary key, t text);

create temp table tempTable (pk int /*references permTable on update cascade on delete set null*/);

create temp trigger trg_insert_check
before insert on tempTable
when new.pk is not null and not exists (select 1 from permTable where pk = new.pk)
begin select raise(abort, 'Key constraint 1'); end;

create temp trigger trg_update_check
before update of pk on tempTable
when new.pk is not null and not exists (select 1 from permTable where pk = new.pk)
begin select raise(abort, 'Key constraint 2'); end;

create temp trigger trg_update_cascade
after update of pk on permTable
begin update tempTable set pk = new.pk where pk = old.pk; end;

create temp trigger trg_delete_null
after delete on permTable
begin update tempTable set pk = null where pk = old.pk; end;


And to test it:
insert into permTable values (1, 'one'), (2, 'two');
insert into tempTable values (3);
insert into tempTable values (2);
select * from tempTable;
update tempTable set pk = 3 where pk = 2;
update permTable set pk = 3 where pk = 2;
select * from tempTable;
delete from permTable where pk = 3;
select * from tempTable;



sqlite> insert into permTable values (1, 'one'), (2, 'two');

sqlite> insert into tempTable values (3);
Error: Key constraint 1

sqlite> insert into tempTable values (2);

sqlite> select * from tempTable;
pk
2

sqlite> update tempTable set pk = 3 where pk = 2;
Error: Key constraint 2

sqlite> update permTable set pk = 3 where pk = 2;

sqlite> select * from tempTable;
pk
3
sqlite> delete from permTable where pk = 3;

sqlite> select * from tempTable;
pk
NULL


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Smith, Randall
Sent: Thursday, January 05, 2017 2:26 PM
To: [hidden email]
Subject: [sqlite] Quest for "scratch table" implementation in SQLite.

I'm still trying to figure out a graceful way to implement a "scratch" database table that has the following properties:

        o Can be created on demand while a database connection is open.
        o Can include references to other material in the database to ensure integrity.
        o Will be automatically deleted when the database is closed.

TEMPORARY tables can't do the second bullet, as I understand it.

Right now I have application code that manages such tables, but it seems clunky and error-prone.  Am I missing any SQLite feature that would let me do this more easily?

Thanks for any suggestions.

Randall.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quest for "scratch table" implementation in SQLite.

Donald Griggs
In reply to this post by Randall Smith
On Thu, Jan 5, 2017 at 2:26 PM, Smith, Randall <[hidden email]>
wrote:

> "I'm still trying to figure out a graceful way..."


I may well just be missing something important, but would the following not
be simple, unclunky, and not highly error-prone?

1) Before your program closes:
    DROP TABLE scratchy-one;
    DROP TABLE scratchy-two;   -- etc.

2) When your program begins:
     DROP TABLE IF EXISTS scratchy-one;
     DROP TABLE IF EXISTS scratchy-two;   -- etc.  Just in case your
program was previously interrupted and didn't drop them.

And 1) is even optional!  (at least for debugging)

Also -- do the "big name" databases handle this much better, and if so, is
that feature used widely?   (I know that, for example, Postgres disallows
foreign keys on temporary tables for the same reasons sqlite does.)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quest for "scratch table" implementation in SQLite.

Simon Slavin-3
In reply to this post by Randall Smith

On 5 Jan 2017, at 7:26pm, Smith, Randall <[hidden email]> wrote:

> I'm still trying to figure out a graceful way to implement a "scratch" database table that has the following properties:
>
>        o Can be created on demand while a database connection is open.
>        o Can include references to other material in the database to ensure integrity.
>        o Will be automatically deleted when the database is closed.

It’s not possible to combine the second and third requirements.  SQLite is designed as a multi-process multi-user database.  It would not be possible for one connection to figure out whether your temporary database should exist or not.

> TEMPORARY tables can't do the second bullet, as I understand it.

Correct.

I suggest that you just use a conventional table in the same database, creating it and deleting it when your program thinks it appropriate.

[later]

Yeah, what Donald Griggs wrote.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quest for "scratch table" implementation in SQLite.

Dominique Devienne
On Thu, Jan 5, 2017 at 11:40 PM, Simon Slavin <[hidden email]> wrote:

> On 5 Jan 2017, at 7:26pm, Smith, Randall <[hidden email]> wrote:
> > I'm still trying to figure out a graceful way to implement a "scratch"
> database table that has the following properties:
> >
> >        o Can be created on demand while a database connection is open.
> >        o Can include references to other material in the database to
> ensure integrity.
> >        o Will be automatically deleted when the database is closed.
>
> It’s not possible to combine the second and third requirements.  SQLite is
> designed as a multi-process multi-user database.
>

Not quite though. SQLite does support multi-process multi-user databases of
course,
but one of its primary use case is as an application format too, and often
in that
configuration, there's 1 process and 1 user. Or during an EXCLUSIVE
transaction too.

FWIW. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quest for "scratch table" implementation in SQLite.

Domingo Alvarez Duarte
Hello !

I'm also looking for a way to achieve something similar but using
attached databases, I mean:

Open a local database that automatically attach other databases and have
views/triggers that use/apply to the attached databases.

Why I want this ?

To overcome sqlite3 limitations with one writer per database and
difficulty to manage multi million records tables spreading then in
individual databases.

Something that the "temp" database can already partially do without
persistence.

Cheers !


On 06/01/17 06:09, Dominique Devienne wrote:

> On Thu, Jan 5, 2017 at 11:40 PM, Simon Slavin <[hidden email]> wrote:
>
>> On 5 Jan 2017, at 7:26pm, Smith, Randall <[hidden email]> wrote:
>>> I'm still trying to figure out a graceful way to implement a "scratch"
>> database table that has the following properties:
>>>         o Can be created on demand while a database connection is open.
>>>         o Can include references to other material in the database to
>> ensure integrity.
>>>         o Will be automatically deleted when the database is closed.
>> It’s not possible to combine the second and third requirements.  SQLite is
>> designed as a multi-process multi-user database.
>>
> Not quite though. SQLite does support multi-process multi-user databases of
> course,
> but one of its primary use case is as an application format too, and often
> in that
> configuration, there's 1 process and 1 user. Or during an EXCLUSIVE
> transaction too.
>
> FWIW. --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quest for "scratch table" implementation in SQLite.

Simon Slavin-3

On 6 Jan 2017, at 10:10am, Domingo Alvarez Duarte <[hidden email]> wrote:

> Why I want this ?
>
> To overcome sqlite3 limitations with one writer per database and difficulty to manage multi million records tables spreading then in individual databases.

SQLite does not have a limitation for one writer per database.  SQLite does not have any problem with multi-million records in a database.  I have a database with a table which has over a billion rows in it.

You may have other reasons for designing your database structure this way, but you are not doing it because of limitations in SQLite.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quest for "scratch table" implementation in SQLite.

Domingo Alvarez Duarte
Hello Simon !

Thanks for reply !

I already raised this point here before and asked people using sqlite
with multi million records and they said that they use sqlite mostly as
append/read only on this case.

I raised one example when sqlite uses too much memory and even Richard
Hipp recognized it.

I understand that sqlite is a good piece of software and indeed I use it
a lot and care about it, but that doesn't mean it actually doesn't have
limitations and room for improvement, I'm not detracting sqlite I'm
pointing areas where it doesn't perform well.

Cheers !

==

On 10/2/16, Domingo Alvarez Duarte<[hidden email]>  wrote:

> Hello !
>
> I'm still fighting to use sqlite with a 20GB database and now I'm trying
> to update a table with 980M rows but sqlite is eating all my memory
> (2GB) and making blocking my computer (too much swap).
>
> I'm in doubt if sqlite can really manage databases above 1GB.

For some (many) UPDATE statements, SQLite has to make two passes.  On
the first pass, it determines the ROWID of every row in the table that
needs to be changed, and then on the second pass it updates those
rows.

You are trying to updates 900 million rows all in one go, and SQLite
requires 8 bytes for each row, so that is 7.2 GB of RAM right there,
just to hold all of the rowids.

For your specific UPDATE, it seems like SQLite ought to be able to do
it all in one pass, and thus avoid using all that memory to store the
rowids.  This looks like an opportunity to improve the query planner.
-- D. Richard Hipp [hidden email]

==


On 06/01/17 10:00, Simon Slavin wrote:

> On 6 Jan 2017, at 10:10am, Domingo Alvarez Duarte <[hidden email]> wrote:
>
>> Why I want this ?
>>
>> To overcome sqlite3 limitations with one writer per database and difficulty to manage multi million records tables spreading then in individual databases.
> SQLite does not have a limitation for one writer per database.  SQLite does not have any problem with multi-million records in a database.  I have a database with a table which has over a billion rows in it.
>
> You may have other reasons for designing your database structure this way, but you are not doing it because of limitations in SQLite.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Quest for "scratch table" implementation in SQLite.

James K. Lowden
In reply to this post by Randall Smith
On Thu, 5 Jan 2017 19:26:26 +0000
"Smith, Randall" <[hidden email]> wrote:

> * Can be created on demand while a database connection is open.
> * Can include references to other material in the database to
>    ensure integrity.
> * Will be automatically deleted when the database is closed.

There is a conventional approach that would work just as well.  

Instead of creating and dropping the table per session, create it once
as part of the schema.  Obviously, you can set up foreign key
constraints for it.  

Per session, delete all rows at the beginning. If multiple users are
involved, make the user id part of the key, and delete all rows per
user.  If you want to, delete all rows before exiting, too.  But that's
optional; no one can see rows in a database that's not open.  

It may help to remember that relational theory doesn't include any
notion of "connection" to the database.  There are tables and they have
rows. Operations on those tables affect the rows.  Creating the tables,
per se, is outside the theory, as too is the idea of creating them
automatically at time of connection.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dmp
Reply | Threaded
Open this post in threaded view
|

Re: Quest for "scratch table" implementation in SQLite.

dmp
In reply to this post by Randall Smith
> I'm still trying to figure out a graceful way to implement
> a "scratch" database table that has the following properties:

> o Can be created on demand while a database connection is open.
> o Can include references to other material in the database to
>   ensure integrity.
> o Will be automatically deleted when the database is closed.
>
> TEMPORARY tables can't do the second bullet, as I understand it.
>
> Right now I have application code that manages such tables,
> but it seems clunky and error-prone.  Am I missing any SQLite
>  feature that would let me do this more easily?
>
> Thanks for any suggestions.
>
> Randall.

I have a Java program that is used as a plugin for my application
that can before a query on a database that then creates a mem/file
database of the data. Not sure if it would meet point 2. Still
needs some work because it works with other databases also, but
the SQLite to SQLite should be fine.

danap.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users