Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

[James K. Lowden]

On Sat, 23 Jul 2016 01:06:23 +
"Smith, Randall"  wrote:

> Using "REFERENCES main.Symbols" appears to be a syntax error.  

I think you got caught by a special case.  

Any kind of DRI would be hard to enforce across database boundaries.
In general, if we have two databases, there's no requirement
they be used together, no requirement they both be attached.  Although
the DBMS could conceivably implement a rule that says any constraint
referencing a nonexistent (unattached) database yields False, I haven't
used one that works that way.  To a one, they simply prohibit
schema-qualified names in DRI rules.  (I suppose that's one
justification for the use of triggers.)  

--jkl

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

Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

[Simon Slavin]

> On 23 Jul 2016, at 3:33pm, Dan Kennedy  wrote:
> 
>> On 2016/07/23 3:30 PM, Simon Slavin wrote:
>> 
>>> The other way around should fine, though: use a permanent table to ensure 
>>> that only legitimate values appear in a temporary table.
> 
> The trouble is that some other process may come along and modify the 
> permanent table, violating the FK constraint in the temporary schema.

U ... didn't think of that.  Yep, that explains why it works the way it 
does.

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

Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

[Quan Yong Zhai]

You can maintain a local conditional "index" table via temp trigger, as long as 
there's no other processes modify the permanent table.

Create table t(id integer primary key, x);
Create temp table t1(id integer primary key);
Create temp trigger trig_1 after insert on t begin
Insert into t1 select new.id where new.x > 1;
end;

发件人: Smith, Randall
发送时间: ‎2016/‎7/‎23 9:06
收件人: 
sqlite-users@mailinglists.sqlite.org
主题: [sqlite] REFERENCES from TEMPORARY table to main database table?

Hi.

I'm creating a specialized index table to a persistent table that I need to 
speed up a one-time operation.  This seems like a great application for a 
TEMPORARY table in SQLite, so the index will always be reliably cleaned up.

However, I can't find a way to have REFERENCES to the main table appear in the 
temp table.  I want, e.g.

CREATE TEMPORARY TABLE MySpecializedIndex
(
-- etc.
Symbol INTEGER REFERENCES Symbols(rowid)
)

For which I get an operational error "no such table: temp.Symbols".

Using "REFERENCES main.Symbols" appears to be a syntax error.

Is it impossible to have references from temp tables to main tables?  If so, 
aren't TEMPORARY tables largely useless?

Is there another idiom in SQLite for managing tables that are intended to have 
a short life or which should be reliably cleaned up when the DB closes?

Thanks for any information and 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] REFERENCES from TEMPORARY table to main database table?

[Dan Kennedy]

On 07/23/2016 08:49 PM, R Smith wrote:



On 2016/07/23 3:30 PM, Simon Slavin wrote:

On 23 Jul 2016, at 2:24pm, R Smith  wrote:

No, it's not possible to forge permanent references to/from 
temporary tables - it defies the objective of being "temporary".
The other way around should fine, though: use a permanent table to 
ensure that only legitimate values appear in a temporary table.


  I will in fact be surprised to find out you can even reference 
another temp table... perhaps this might be possible since both 
tables will definitely cease to exist upon connection closure.
I don't see why this shouldn't be allowed.  Assuming that the 
'parent' temporary table has the appropriate index.  SQLite doesn't 
support CREATE TEMPORARY UNIQUE INDEX but it should understand that 
an index created on a temporary table is temporary.


Yes, your reasoning feels right, but I'm coming from the idea that 
references (much like Indices and such) are really database objects 
themselves, stand-alone units if you will, and they need to have 
concrete (non-temporary) linking or at least not mixed 
temporary-permanent linking. This is of course not necessarily true, 
and possibly quite different in SQLite (where it might be more like a 
check-constraint or such), so your point may have merit here and 
perhaps allowing referencing FROM a temporary table to a permanent 
table could simply be a minor tweak in some line of code inside SQLite.


The trouble is that some other process may come along and modify the 
permanent table, violating the FK constraint in the temporary schema.


Dan.


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

Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

[R Smith]

On 2016/07/23 3:30 PM, Simon Slavin wrote:

On 23 Jul 2016, at 2:24pm, R Smith  wrote:


No, it's not possible to forge permanent references to/from temporary tables - it defies 
the objective of being "temporary".

The other way around should fine, though: use a permanent table to ensure that 
only legitimate values appear in a temporary table.


  I will in fact be surprised to find out you can even reference another temp 
table... perhaps this might be possible since both tables will definitely cease 
to exist upon connection closure.

I don't see why this shouldn't be allowed.  Assuming that the 'parent' 
temporary table has the appropriate index.  SQLite doesn't support CREATE 
TEMPORARY UNIQUE INDEX but it should understand that an index created on a 
temporary table is temporary.


Yes, your reasoning feels right, but I'm coming from the idea that 
references (much like Indices and such) are really database objects 
themselves, stand-alone units if you will, and they need to have 
concrete (non-temporary) linking or at least not mixed 
temporary-permanent linking. This is of course not necessarily true, and 
possibly quite different in SQLite (where it might be more like a 
check-constraint or such), so your point may have merit here and perhaps 
allowing referencing FROM a temporary table to a permanent table could 
simply be a minor tweak in some line of code inside SQLite.


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

Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

[Simon Slavin]

On 23 Jul 2016, at 2:24pm, R Smith  wrote:

> No, it's not possible to forge permanent references to/from temporary tables 
> - it defies the objective of being "temporary".

The other way around should fine, though: use a permanent table to ensure that 
only legitimate values appear in a temporary table.

>  I will in fact be surprised to find out you can even reference another temp 
> table... perhaps this might be possible since both tables will definitely 
> cease to exist upon connection closure.

I don't see why this shouldn't be allowed.  Assuming that the 'parent' 
temporary table has the appropriate index.  SQLite doesn't support CREATE 
TEMPORARY UNIQUE INDEX but it should understand that an index created on a 
temporary table is temporary.

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

Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

[R Smith]

On 2016/07/23 3:06 AM, Smith, Randall wrote:

Hi.

I'm creating a specialized index table to a persistent table that I need to 
speed up a one-time operation.  This seems like a great application for a 
TEMPORARY table in SQLite, so the index will always be reliably cleaned up.

However, I can't find a way to have REFERENCES to the main table appear in the 
temp table.  I want, e.g.

CREATE TEMPORARY TABLE MySpecializedIndex
(
-- etc.
Symbol INTEGER REFERENCES Symbols(rowid)
)

For which I get an operational error "no such table: temp.Symbols".

Using "REFERENCES main.Symbols" appears to be a syntax error.

Is it impossible to have references from temp tables to main tables?  If so, 
aren't TEMPORARY tables largely useless?


That is a very unimportant very non-consequential little detail you are 
basing a very large assumption on. It's akin to asking "Is a car without 
a roof-light largely useless?"


To answer the question though, No, it's not possible to forge permanent 
references to/from temporary tables - it defies the objective of being 
"temporary". I will in fact be surprised to find out you can even 
reference another temp table... perhaps this might be possible since 
both tables will definitely cease to exist upon connection closure.


Either way, your plan of making an index table is, well, why not just 
use an index? It will take the same (if not less) effort to make as a 
temp table, and it can be dropped after without much fuss should you no 
longer need it. You can even make conditional, compound and concatenated 
indices in SQLite if this was your motivation for the table-idea. 
Perhaps the "feature request" I'm seeing in this thread is "Temporary 
Indices"... which the use cases for are minuscule, though the effort to 
implement it and code added overall is probably equally minuscule - so 
it might be a good idea still.


Another option is to just make a normal table to achieve your goal, but 
I see you have already thought of that, hence the following question:



Is there another idiom in SQLite for managing tables that are intended to have 
a short life or which should be reliably cleaned up when the DB closes?


to which the answer is "No - but..."
You could do what needs doing inside a transaction, that will reliably 
ensure the last bit of the transaction (where you get rid of any 
temporary added objects) also completes (or any errors get reverted back 
so the added objects are again removed - either way, no added object 
will remain after). Alternatively, you can issue "DROP TABLE xxx IF 
EXISTS..." as part of your connection start-up scripts to ensure "the 
one that got away" is dealt with.


The only situation I can fathom where this might not work for you is 
where you want to have the table "in-use" by clients for a period of 
varying use (so not a transaction) while the added index table does its 
thing, until the connection is terminated - but if this is the case, I 
can't find an argument for not using a real index, or at a minimum, a 
more permanent table solution - so I don't think this is your use case.


In conclusion - I'm sorry there is no way to do specifically what you 
want, but you are obviously not a beginner at this, so if you share more 
details of what you wish to achieve, some of the people here have 
extensive experience in tuning DB operations via SQLite and someone 
might have already done more-or-less what you are attempting and can 
share possible outcomes of their experiences and tests.



Good luck,
Ryan

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

Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

[Clemens Ladisch]

Smith, Randall wrote:
> Is it impossible to have references from temp tables to main tables?

Yes.

> If so, aren't TEMPORARY tables largely useless?

Only if you want to use foreign key constraints.

> Is there another idiom in SQLite for managing tables that are intended
> to have a short life or which should be reliably cleaned up when the
> DB closes?

You could omit the constraint from the temporary table.


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