Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[dmp]

> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[James K. Lowden]

On Thu, 5 Jan 2017 19:26:26 +
"Smith, Randall"  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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 d...@sqlite.org

==


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

On 6 Jan 2017, at 10:10am, Domingo Alvarez Duarte  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
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] Quest for "scratch table" implementation in SQLite.

[Simon Slavin]

On 6 Jan 2017, at 10:10am, Domingo Alvarez Duarte  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:


On 5 Jan 2017, at 7:26pm, Smith, Randall  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
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] Quest for "scratch table" implementation in SQLite.

[Dominique Devienne]

On Thu, Jan 5, 2017 at 11:40 PM, Simon Slavin  wrote:

> On 5 Jan 2017, at 7:26pm, Smith, Randall  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[Simon Slavin]

On 5 Jan 2017, at 7:26pm, Smith, Randall  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[Donald Griggs]

On Thu, Jan 5, 2017 at 2:26 PM, Smith, Randall 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[David Raymond]

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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Smith, Randall
Sent: Thursday, January 05, 2017 2:26 PM
To: sqlite-users@mailinglists.sqlite.org
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
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] 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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Smith, Randall
Sent: Thursday, January 05, 2017 2:26 PM
To: sqlite-users@mailinglists.sqlite.org
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
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] Quest for "scratch table" implementation in SQLite.

[Smith, Randall]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users