[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

That's why I think that expose a basic SQL ANSI catalog would make this kind
of work and others a lot easier.  

?  
>  Fri Aug 21 2015 22:13:00 CEST from "Simon Slavin"  
>Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>dependencies ?
>
>  On 21 Aug 2015, at 9:07pm, Scott Hess  wrote:
> 
>  
>>That said, writing code to do this manually has potential to be error
>> prone. It might make sense under an ENABLE flag. It feels like an obvious
>> thing to have, not something out-of-scope like embedding a new language or
>> support for a new storage methodology.
>> 

>  To do it correctly you'd have to identify all table names used by each
>trigger and view. Which means you have to parse the VIEWs and TRIGGERs and
>look in all the positions where you'd expect to find a table name.
> 
> I don't know whether the existing SQLite statement parser could be used for
>this but it does seem to be a complicated task.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] BUG: sqlite 3.8.11.x left join

[Richard Hipp]

The on-line fix
(https://www.sqlite.org/src/artifact/24323faac?ln=3785) together with
commentary and test cases is now on trunk
(https://www.sqlite.org/src/info/351bc22fa9b5a2e5}.

On 8/21/15, Richard Hipp  wrote:
> Thanks for the test case.  The error seems to have been introduced by
> check-in https://www.sqlite.org/src/info/6df18e949d367629 which does
> some aggressive transformations on nested queries for improved
> performance.
>
> On 8/21/15, Mark Brand  wrote:
>> Hi,
>>
>> For the query below, versions 3.8.11.0 and 3.8.11.1 return only 1 row.
>> For some reason, the LEFT JOIN seems to behave like a JOIN.
>>
>> Older versions (tested 3.8.7.4 and 3.8.10.2) correctly return 2 rows.
>>
>> SELECT *
>> FROM (
>>  SELECT 'apple' fruit
>>  UNION ALL SELECT 'banana'
>> ) a
>> JOIN (
>>  SELECT 'apple' fruit
>>  UNION ALL SELECT 'banana'
>> ) b ON a.fruit=b.fruit
>> LEFT JOIN (
>>  SELECT 1 isyellow
>> ) c ON b.fruit='banana'
>> ;
>>
>> regards,
>>
>> Mark
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] design problem involving trigger

[Will Parsons]

On 21 Aug 2015, Scott Robison wrote:
> Another consideration: it is possible that two different anonymous books
> are indeed different books. It is also possible a given "author" releases a
> new rewritten book with the same name. I'm thinking of the Hardy Boys
> series in particular from my personal experience. Not a big deal, but
> thought I'd share it.

Yes, it's a theoretical possibility, but in that case I'd have to
force some difference into the title field to make a distinction.

-- 
Will

[sqlite] design problem involving trigger

[Will Parsons]

On 21 Aug 2015, R.Smith wrote:
>
>
> On 2015-08-21 04:47 AM, Will Parsons wrote:
>> I'm working on a program that involves a catalogue of books.  Part of
>> the database schema looks like this:
>>
>> create table Books(id integer primary key,
>> title text collate nocase not null,
>> author references Authors(id),
>> ...
>> unique(title, author));
>>
>> create table Authors(id integer primary key,
>>   name text unique not null check(name <> ''));
>>
>> The idea here is that the title+author of a book must be unique, but a
>> book may not necessarily have an author associated with it.  But, the
>> schema fragment as I have presented it does not disallow entering the
>> same title with a null author multiple times, which is clearly
>> undesirable.
>>
>> In thinking about how to avoid this, one possibility that has occurred
>> to me is to add an entry to the Authors table with id=0, name=NULL, and
>> to modify the Books table to be:
>>
>> create table Books(id integer primary key,
>> title text collate nocase not null,
>> author references Authors(id) not null,
>> ...
>> unique(title, author));
>>
>> With this, entries in the Books table that presently have the author
>> field set to NUll would instead have author=0.
>>
>> What I would like to have is a trigger that when an attempt is made to
>> enter a new record into the Books table with a NULL author field, is
>> to force the author field to 0 instead.  I can't see how to do this
>> with a "before insert" trigger, though.  Perhaps I'm approaching this
>> the wrong way; in any case I'd appreciate advice.
>>
>
> Nothing wrong with your approach, simply declare the Author to be NOT 
> NULL and if it is possible to have non-Authors you could do what you 
> suggest, add a 0-Author to the parent table, or, you could use a partial 
> Index (available after SQLite 3.7 only I think) which would allow you to 
> have NULLS but still maintain a strict relation, something like this:
>
> create table Books(id integer primary key,
> title text collate nocase not null,
> author int not null references Authors(id),
> ...
> );
>
> create unique index uBookAuth on Books(title,author) where author is not null;
>
> create table Authors(id integer primary key,
>   name text unique not null check(name <> ''));

I guess I don't understand how having a partial index where author is
not null prevents adding two entries with the same title and a null
author.  How is this different from what I have now with the
"unique(title, author)" specification?

-- 
Will

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Simon Slavin]

On 21 Aug 2015, at 9:07pm, Scott Hess  wrote:

> That said, writing code to do this manually has potential to be error
> prone.  It might make sense under an ENABLE flag.  It feels like an obvious
> thing to have, not something out-of-scope like embedding a new language or
> support for a new storage methodology.

To do it correctly you'd have to identify all table names used by each trigger 
and view.  Which means you have to parse the VIEWs and TRIGGERs and look in all 
the positions where you'd expect to find a table name.

I don't know whether the existing SQLite statement parser could be used for 
this but it does seem to be a complicated task.

Simon.

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Simon Slavin]

On 21 Aug 2015, at 8:13pm, Scott Hess  wrote:

> Since renameTriggerFunc()
> follows renameParentFunc(), my guess is that triggers are also handled. 

The documentation says that statements within TRIGGERs are not changed 
correctly:



Perhaps renameParentFunc() suggests that you can rename a TRIGGER, not that the 
commands within TRIGGERs are changed.

I do not know whether the current version of SQLite correctly changes TRIGGERs 
or not.  Since it would require complete parsing of the action text, I suspect 
not.

Simon.

[sqlite] libtclsqlite3 assistance

[jungle Boogie]

Hi Dr. H,
On 21 August 2015 at 14:23, Richard Hipp  wrote:
> On 8/21/15, jungle Boogie  wrote:
>>
>> Is it possible to compile in libtclsqlite3?
>>
>
> Dunno.  But you can compile the libtclsqlite3.so yourself:
>
> make tclsqlite3.c
> gcc -fPIC -shared -I. -o libtclsqlite3.so tclsqlite3.c


Made it fine but compiling not so much:

/usr/local/bin/gcc48 -fPIC -shared -I. -o libtclsqlite3.so tclsqlite3.c
tclsqlite3.c:162240:17: fatal error: tcl.h: No such file or directory
 #include "tcl.h"
 ^
compilation terminated.

I have tcl.h here:
/usr/local/include/tcl8.6/generic/tcl.h
/usr/local/include/tcl8.6/tcl.h


>
> --
> D. Richard Hipp
> drh at sqlite.org
\
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

Thank you for your attention !  

I'm pointing this here because postgresql do manage this case properly !  

And I'm creating a tool to prototype database applications and I'm using
sqlite as the primary database, when we are prototyping things can change
drastically at any point and if we already have a lot of views/triggers it's
a pain in the ass to fix it (postgresql does it fine).  

Also that we got to this point would be nice if sqlite implemented a basic
SQL ANSI data dictioanry,? I mean sqlite already provide most of the info
for a basic data dictionary but in a non compliant way "PRAGMAS", although is
better than nothing we can not use that info on sql statements like
views/joins.  

Would be nice if sqlite provides virtual ansi views (like postgresql does on
top of it's pg_catalog) for :  

- check_constraints  

?  

- collations  

- columns  

- key_column_usage  

- referential_constraints  

- sequences  

- table_constraints  

- tables  

- triggered_update_columns  

- triggers  

- user_defined_types  

- views


?  

Thanks in advance for your time, attention and great work !  

Cheers !   

?  
>  Fri Aug 21 2015 19:11:03 CEST from "Richard Hipp"  
>Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>dependencies ?
>
>  On 8/21/15, sqlite-mail  wrote:
>  
>>Then do you think this is a bug ?
>> 

>  The word "bug" means it gets the wrong answer.
> 
> The absence of a feature is not a bug, unless that feature is a
> required part of the specification for the program. The ability to
> rename tables and all dependencies is not a required feature in this
> case.
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

Thanks for your attention!  

Only to remark on this I tested on postgresql and somehow it knows how deal
with it ! "so few (none?)"  

Cheers !  

?  
>  Fri Aug 21 2015 19:08:58 CEST from "J Decker"   Subject:
>Re: [sqlite] Is this a bug ? How to rename a table and all dependencies ?
>
>  On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail 
>wrote:
>  
>>Then do you think this is a bug ?
>>  
>>>Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" 
>>> Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>>> dependencies ?

>>  

>  while it may be considered a nice thing; it's not common practice to
> rename tables, so few (none?) sql implementations automatically update
> references to tables when they are renamed. Not a bug; more like out
> of scope.
> 
> It wouldn't know if you were moving a source table for archival
> purposes and going to replace it with another empty one or moving
> because you didn't like your original schema.
> 
>  
>>  
>>>On 21 Aug 2015, at 12:20pm, sqlite-mail  wrote:
>>> 
>>> 
>>>  
Does anybody knows how to rename a table and all it's dependencies in one
 go
 ?
 
 

>>>  Can't be done. Sorry.
>>> 
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> 
>>> 
>>> 
>>> 
>>> 
>>> 
>>> 

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

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



?

[sqlite] BUG: sqlite 3.8.11.x left join

[Richard Hipp]

Thanks for the test case.  The error seems to have been introduced by
check-in https://www.sqlite.org/src/info/6df18e949d367629 which does
some aggressive transformations on nested queries for improved
performance.

On 8/21/15, Mark Brand  wrote:
> Hi,
>
> For the query below, versions 3.8.11.0 and 3.8.11.1 return only 1 row.
> For some reason, the LEFT JOIN seems to behave like a JOIN.
>
> Older versions (tested 3.8.7.4 and 3.8.10.2) correctly return 2 rows.
>
> SELECT *
> FROM (
>  SELECT 'apple' fruit
>  UNION ALL SELECT 'banana'
> ) a
> JOIN (
>  SELECT 'apple' fruit
>  UNION ALL SELECT 'banana'
> ) b ON a.fruit=b.fruit
> LEFT JOIN (
>  SELECT 1 isyellow
> ) c ON b.fruit='banana'
> ;
>
> regards,
>
> Mark
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Simon Slavin]

On 21 Aug 2015, at 7:02pm, sqlite-mail  wrote:

> I'm pointing this here because postgresql do manage this case properly !

If you want postgres, you know where to find it.

Please don't forget that SQLite has to run on your smartphone and your SatNav 
device and your TV.  It has different design objectives to the big 
client/server database systems.

Simon.

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

Then do you think this is a bug ?  
>  Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin"  
>Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>dependencies ?
>
>  On 21 Aug 2015, at 12:20pm, sqlite-mail  wrote:
> 
>  
>>Does anybody knows how to rename a table and all it's dependencies in one
>>go
>> ? 
>> 

>  Can't be done. Sorry.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Simon Slavin]

On 21 Aug 2015, at 12:20pm, sqlite-mail  wrote:

> Does anybody knows how to rename a table and all it's dependencies in one go
> ? 

Can't be done.  Sorry.

Simon.

[sqlite] Is this a bug ? Pragmas do not accept qualified names !

[sqlite-mail]

Thank you !  

That's what I want and looking back in the sqlite documentation I can see now
that I was misinterpreting it, in reality I was applying the same principle
used on other places to qualify/prefix tables/views/... objects but with your
help I could realize that pragmas are an exception to the genral rule.  

?  

Again thank you !  

?  
>  Fri Aug 21 2015 16:42:02 CEST from "Scott Hess"  
>Subject: Re: [sqlite] Is this a bug ? Pragmas do not accept qualified names !
>
>  I think you wanted:
> PRAGMA attached_db.table_info(one_table);
> 
> -scott
> 
> On Fri, Aug 21, 2015 at 7:35 AM, sqlite-mail 
> wrote:
> 
>  
>>Hello !
>> 
>> Today I'm working with sqlite3 with attached databases and when I tried to
>> get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas
>> do
>> not work with qualified/prefixed names like:
>> 
>> PRAGMA table_info(attached_db.one_table)
>> 
>> 
>> 
>> Is this a bug ?
>> 
>> Cheers !
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 

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



?

[sqlite] libtclsqlite3 assistance

[Richard Hipp]

On 8/21/15, jungle Boogie  wrote:
>
> Is it possible to compile in libtclsqlite3?
>

Dunno.  But you can compile the libtclsqlite3.so yourself:

make tclsqlite3.c
gcc -fPIC -shared -I. -o libtclsqlite3.so tclsqlite3.c

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Is this a bug ? Pragmas do not accept qualified names !

[sqlite-mail]

Hello !  

Today I'm working with sqlite3 with attached databases and when I tried to
get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas do
not work with qualified/prefixed names like:  

PRAGMA table_info(attached_db.one_table)  

?  

Is this a bug ?  

Cheers !

[sqlite] design problem involving trigger

[Hick Gunter]

Are you looking for "NOT NULL DEFAULT 0"?

-Urspr?ngliche Nachricht-
Von: Will Parsons [mailto:varro at nodomain.invalid]
Gesendet: Freitag, 21. August 2015 04:47
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] design problem involving trigger

I'm working on a program that involves a catalogue of books.  Part of the 
database schema looks like this:

create table Books(id integer primary key,
   title text collate nocase not null,
   author references Authors(id),
   ...
   unique(title, author));

create table Authors(id integer primary key,
 name text unique not null check(name <> ''));

The idea here is that the title+author of a book must be unique, but a book may 
not necessarily have an author associated with it.  But, the schema fragment as 
I have presented it does not disallow entering the same title with a null 
author multiple times, which is clearly undesirable.

In thinking about how to avoid this, one possibility that has occurred to me is 
to add an entry to the Authors table with id=0, name=NULL, and to modify the 
Books table to be:

create table Books(id integer primary key,
   title text collate nocase not null,
   author references Authors(id) not null,
   ...
   unique(title, author));

With this, entries in the Books table that presently have the author field set 
to NUll would instead have author=0.

What I would like to have is a trigger that when an attempt is made to enter a 
new record into the Books table with a NULL author field, is to force the 
author field to 0 instead.  I can't see how to do this with a "before insert" 
trigger, though.  Perhaps I'm approaching this the wrong way; in any case I'd 
appreciate advice.

--
Will

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] When sqlite3_close() returns SQL_BUSY

[Dan Kennedy]

On 08/21/2015 12:30 PM, Jeff M wrote:
> Sometimes my iOS app creates an unreasonable number of prepared statements 
> (perhaps 1,000, an app bug that I'm fixing).  These prepared statements are 
> later finalized just prior to doing sqlite3_close(), which sometimes returns 
> SQL_BUSY.  The docs say SQL_BUSY will be returned if I haven't finalized all 
> prepared statements, but I believe I have done so.  My iOS app has only one 
> connection to the DB and I'm doing all this work on the main thread.
>
> 1.  The docs don't say what to do in the case of SQL_BUSY.  Does that mean 
> I've certainly failed to finalize one or more prepared statements, or does 
> SQLite just need more time (in which case can I loop on sqlite3_close() until 
> I get SQLITE_OK)?
>
> 2.  Does SQLite keep a record of prepared statements?  If so, is there a way 
> I can ask SQLite to close them all, or are there any tricks to debugging 
> which statements have not been finalized?

It does. Here:

   https://www.sqlite.org/c3ref/next_stmt.html

If sqlite3_close() returns SQLITE_BUSY, this can be used to find the 
unfinalized statements. Sometimes using sqlite3_sql() on the statement 
handles helps to determine where the leak occurred.

Dan.

[sqlite] design problem involving trigger

[R.Smith]

On 2015-08-21 04:47 AM, Will Parsons wrote:
> I'm working on a program that involves a catalogue of books.  Part of
> the database schema looks like this:
>
> create table Books(id integer primary key,
> title text collate nocase not null,
> author references Authors(id),
> ...
> unique(title, author));
>
> create table Authors(id integer primary key,
>   name text unique not null check(name <> ''));
>
> The idea here is that the title+author of a book must be unique, but a
> book may not necessarily have an author associated with it.  But, the
> schema fragment as I have presented it does not disallow entering the
> same title with a null author multiple times, which is clearly
> undesirable.
>
> In thinking about how to avoid this, one possibility that has occurred
> to me is to add an entry to the Authors table with id=0, name=NULL, and
> to modify the Books table to be:
>
> create table Books(id integer primary key,
> title text collate nocase not null,
> author references Authors(id) not null,
> ...
> unique(title, author));
>
> With this, entries in the Books table that presently have the author
> field set to NUll would instead have author=0.
>
> What I would like to have is a trigger that when an attempt is made to
> enter a new record into the Books table with a NULL author field, is
> to force the author field to 0 instead.  I can't see how to do this
> with a "before insert" trigger, though.  Perhaps I'm approaching this
> the wrong way; in any case I'd appreciate advice.
>

Nothing wrong with your approach, simply declare the Author to be NOT 
NULL and if it is possible to have non-Authors you could do what you 
suggest, add a 0-Author to the parent table, or, you could use a partial 
Index (available after SQLite 3.7 only I think) which would allow you to 
have NULLS but still maintain a strict relation, something like this:

create table Books(id integer primary key,
title text collate nocase not null,
author int not null references Authors(id),
...
);

create unique index uBookAuth on Books(title,author) where author is not null;

create table Authors(id integer primary key,
  name text unique not null check(name <> ''));


Read more at:
http://www.sqlite.org/partialindex.html

[sqlite] libtclsqlite3 assistance

[jungle Boogie]

Hello All,

I'm trying to install this on my freebsd system:
https://github.com/dbohdan/sqawk

Problem is that I compile sqlite from source and keep up to date with
trunk pretty regularly, but sqawk can't compile when I also have
sqlite complied.

Main error from sqawk:
errorInfo: couldn't load file
"/usr/local/lib/tcl8.6/sqlite3/libtclsqlite3.so": Cannot open
"/usr/local/lib/tcl8.6/sqlite3/libtclsqlite3.so"


I have tried ./configure --enable-shared and ./configure
--enable-shared=libtclsqlite3.so in sqlite src but config log always
says no.

configure:9790: checking whether to build shared libraries
configure:9811: result: no


Full config log: http://dpaste.com/1MEDECZ.txt

Is it possible to compile in libtclsqlite3?

If you don't have a freebsd system nearby, you can use Vagrant to spin
a VM up very quickly:
https://atlas.hashicorp.com/freebsd/boxes/FreeBSD-10.2-RELEASE



-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[John McKown]

On Fri, Aug 21, 2015 at 1:02 PM, sqlite-mail 
wrote:

> Thank you for your attention !
>
> I'm pointing this here because postgresql do manage this case properly !
>

?And is significantly larger and harder to install. PostgreSQL is not
"lite"! I know. I use it and love it. ?



>
> And I'm creating a tool to prototype database applications and I'm using
> sqlite as the primary database, when we are prototyping things can change
> drastically at any point and if we already have a lot of views/triggers
> it's
> a pain in the ass to fix it (postgresql does it fine).
>

?Which is why I keep all my definitions in a file. I edit them there, then
use the ".read" in sqlite3 to bring them all in. Granted this doesn't help
if you have a lot of data in the data base. In that case, I don't rename. I
create the new table and populate it with the data in the old table via a
INSERT INTO ... SELECT ... ?



>
> Also that we got to this point would be nice if sqlite implemented a basic
> SQL ANSI data dictioanry,  I mean sqlite already provide most of the info
> for a basic data dictionary but in a non compliant way "PRAGMAS", although
> is
> better than nothing we can not use that info on sql statements like
> views/joins.
>

?OK, implement one. This would be an _excellent_ "add on" product. Write a
program which reads the data base schema. Using that internal schema,
determine how to update all the dependencies. To implement, send all the
required commands to the sqlite3 API. ?

-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

Hello !  

Here I'm fixing some typos and I also tested on postgresql and there all
views are updated properly then I'll say is a bug in sqlite.  

Does anybody knows how to rename a table and all it's dependencies in one go
 ? 

 ? 

 The problem: a database has several tables and views that reference/use
other
 tables something simplified like this: 

  

 CREATE TABLE a(id integer primary key, name text); 

 CREATE TABLE b(id integer primary key, a_id integer references a(id), name
text); 

 CREATE VIEW aview AS select * from a; 

 CREATE VIEW bview AS select b.*, a.name as aname from b left join a on
b.a_id =a.id; 

 === 

 ? 

 Now if we do "alter table a rename to a2;" actually sqlite only rename the
 "a" all foreign key references from "a" to "a2" but leaves all views broken.



 ===schema after "alter table a rename to a2;" 

 CREATE TABLE "a2"(id integer primary key, name text); 

 CREATE TABLE b(id integer primary key, a_id integer references "a2"(id),
name text); 

 CREATE VIEW aview AS select * from a; 

 CREATE VIEW bview AS select b.*, a.name as aname from b left join a on
b.a_id =a.id; 

 === 

 Is this a bug ? 

 Cheers !   

?

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[sqlite-mail]

Hello !  

Does anybody knows how to rename a table and all it's dependencies in one go
?  

?  

The problem: a database has several tables and views that reference/use other
tables something simplified like this:  

  

CREATE TABLE a(id integer primary key, name text);  

CREATE TABLE b(id integer primary key, a_id integer references a(id), name
text);  

CREATE VIEW aview AS select * from a;  

CREATE VIEW bview AS select b.*, a.name from b left join a on b.a_id =aid;  

===  

?  

Now if we do "alter table a rename to a2;" actually sqlite only rename the
"a" all foreign key references from "a" to "a2" but leaves all views broken. 


===schema after "alter table a rename to a2;"  

CREATE TABLE "a2"(id integer primary key, name text);  

CREATE TABLE b(id integer primary key, a_id integer references "a2"(id), name
text);  

CREATE VIEW aview AS select * from a;  

CREATE VIEW bview AS select b.*, a.name from b left join a on b.a_id =aid;  

===  

Is this a bug ?  

Cheers !  

?

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Richard Hipp]

On 8/21/15, sqlite-mail  wrote:
> Then do you think this is a bug ?

The word "bug" means it gets the wrong answer.

The absence of a feature is not a bug, unless that feature is a
required part of the specification for the program.  The ability to
rename tables and all dependencies is not a required feature in this
case.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Scott Hess]

On Fri, Aug 21, 2015 at 1:02 PM, Simon Slavin  wrote:

> On 21 Aug 2015, at 8:13pm, Scott Hess  wrote:
> > Since renameTriggerFunc()
> > follows renameParentFunc(), my guess is that triggers are also handled.
>
> The documentation says that statements within TRIGGERs are not changed
> correctly:
>
> 
>
> Perhaps renameParentFunc() suggests that you can rename a TRIGGER, not
> that the commands within TRIGGERs are changed.
>
> I do not know whether the current version of SQLite correctly changes
> TRIGGERs or not.  Since it would require complete parsing of the action
> text, I suspect not.


Hmm.  My impression of both triggers and views is that they don't really
have independent existence, instead they are inlined into relevant
statements.  So that would make sense as an omission.

That said, writing code to do this manually has potential to be error
prone.  It might make sense under an ENABLE flag.  It feels like an obvious
thing to have, not something out-of-scope like embedding a new language or
support for a new storage methodology.

-scott

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Scott Hess]

On Fri, Aug 21, 2015 at 11:06 AM, Simon Slavin  wrote:

> On 21 Aug 2015, at 7:02pm, sqlite-mail  wrote:
> > I'm pointing this here because postgresql do manage this case properly !
>
> If you want postgres, you know where to find it.
>
> Please don't forget that SQLite has to run on your smartphone and your
> SatNav device and your TV.  It has different design objectives to the big
> client/server database systems.


SQLite already handles this correctly for index definitions.  It also
handles it correctly for foreign keys IFF you have run "PRAGMA foreign_keys
= ON;" implemented by renameParentFunc().  Since renameTriggerFunc()
follows renameParentFunc(), my guess is that triggers are also handled.  So
having views not be handled maybe is just an unintended omission?

-scott

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Scott Doctor]

(oops glitched my send)

Try using a database modeling  program such as

TMS Software's Data Modeler
or
SQL Maestro Group's SQLite Maestro

These handle the changes nicely. You use the programs for 
designing your databases, queries, views and such. It spits out 
SQL to run on your system that handles setting up and changes. 
Or you can connect directly to your SQLite database and it will 
handle making all the changes for you.


Scott Doctor
scott at scottdoctor.com
--

On 8/21/2015 11:54 AM, Scott Doctor wrote:
>
> 
> Scott Doctor
> scott at scottdoctor.com
> --
>
> On 8/21/2015 10:37 AM, sqlite-mail wrote:
>> Thanks for your attention!
>>
>> Only to remark on this I tested on postgresql and somehow it 
>> knows how deal
>> with it ! "so few (none?)"
>>
>> Cheers !
>>
>>>   Fri Aug 21 2015 19:08:58 CEST from "J Decker" 
>>>   Subject:
>>> Re: [sqlite] Is this a bug ? How to rename a table and all 
>>> dependencies ?
>>>
>>>   On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail 
>>> 
>>> wrote:
 Then do you think this is a bug ?
> Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" 
> 
> Subject: Re: [sqlite] Is this a bug ? How to rename a 
> table and all
> dependencies ?
>>>   while it may be considered a nice thing; it's not common 
>>> practice to
>>> rename tables, so few (none?) sql implementations 
>>> automatically update
>>> references to tables when they are renamed. Not a bug; more 
>>> like out
>>> of scope.
>>>
>>> It wouldn't know if you were moving a source table for archival
>>> purposes and going to replace it with another empty one or 
>>> moving
>>> because you didn't like your original schema.
>>>
> On 21 Aug 2015, at 12:20pm, sqlite-mail 
>  wrote:
>
>
>> Does anybody knows how to rename a table and all it's 
>> dependencies in one
>> go
>> ?
>>
>>
>   Can't be done. Sorry.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
>
>
>
>
>
>
>
>
   ___
 sqlite-users mailing list
 sqlite-users at mailinglists.sqlite.org
 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

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

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Scott Doctor]

Scott Doctor
scott at scottdoctor.com
--

On 8/21/2015 10:37 AM, sqlite-mail wrote:
> Thanks for your attention!
>
> Only to remark on this I tested on postgresql and somehow it knows how deal
> with it ! "so few (none?)"
>
> Cheers !
>
> 
>>   Fri Aug 21 2015 19:08:58 CEST from "J Decker"   
>> Subject:
>> Re: [sqlite] Is this a bug ? How to rename a table and all dependencies ?
>>
>>   On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail > dev.dadbiz.es>
>> wrote:
>>   
>>> Then do you think this is a bug ?
>>>   
 Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" 
 Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
 dependencies ?
>>>   
>>   while it may be considered a nice thing; it's not common practice to
>> rename tables, so few (none?) sql implementations automatically update
>> references to tables when they are renamed. Not a bug; more like out
>> of scope.
>>
>> It wouldn't know if you were moving a source table for archival
>> purposes and going to replace it with another empty one or moving
>> because you didn't like your original schema.
>>
>>   
>>>   
 On 21 Aug 2015, at 12:20pm, sqlite-mail  
 wrote:


   
> Does anybody knows how to rename a table and all it's dependencies in one
> go
> ?
>
>
   Can't be done. Sorry.

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







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

[sqlite] When sqlite3_close() returns SQL_BUSY

[R.Smith]

Hi Jeff,

On 2015-08-21 07:30 AM, Jeff M wrote:
> Sometimes my iOS app creates an unreasonable number of prepared statements 
> (perhaps 1,000, an app bug that I'm fixing).  These prepared statements are 
> later finalized just prior to doing sqlite3_close(), which sometimes returns 
> SQL_BUSY.  The docs say SQL_BUSY will be returned if I haven't finalized all 
> prepared statements, but I believe I have done so.  My iOS app has only one 
> connection to the DB and I'm doing all this work on the main thread.
>
> 1.  The docs don't say what to do in the case of SQL_BUSY.  Does that mean 
> I've certainly failed to finalize one or more prepared statements, or does 
> SQLite just need more time (in which case can I loop on sqlite3_close() until 
> I get SQLITE_OK)?

SQL_BUSY does not mean anything bad except that you are trying to do 
some work on a query (read: prepared statement) while another is still 
not done with its duties. These duties may in your case simply mean that 
the "closing" of a previous prepared statement is still under way, so 
yes, it just needs a moment. You can wait a moment and try again.

I will mention (as Simon is likely to point out soon!) that the good 
news is: SQLite will do this waiting-and-retrying for you if you simply 
set a suitable time-out, perhaps in the order of a minute or more, using 
the pragma:

http://www.sqlite.org/pragma.html#pragma_busy_timeout

or, if you prefer using the C-interface:
http://www.sqlite.org/c3ref/busy_timeout.html

The idea with quite a long time-out is that in the unlikely event a busy 
signal is reached after that much waiting, you can be certain of a lock 
race condition or something else being wrong and probably go into 
recovery/safety mode. Don't make it too long since an impatient user 
will just kill your process.

> 2.  Does SQLite keep a record of prepared statements?  If so, is there a way 
> I can ask SQLite to close them all, or are there any tricks to debugging 
> which statements have not been finalized?

Closing the connection will release the resources (such as lingering 
prepared statements), but there it is best to control it yourself. For a 
C-interface to find them, see here:
https://www.sqlite.org/c3ref/next_stmt.html


Hope that helps!
Ryan

[sqlite] Compile warnings

[Bernhard Schommer]

You are right that the warning is wrong in the case that both are zero, I'm
not sure if this is still the case in newer gcc versions
since I only tried 4.8.*. I'm okay with keeping my local patch to disable
the warning in my case and I can fully understand that
adding a not necessary test to remove a false warning would harm
performance.

2015-08-20 19:05 GMT+02:00 Scott Robison :

> On Thu, Aug 20, 2015 at 10:46 AM, Scott Doctor 
> wrote:
>
> > My opinion is to keep it portable. The warning is there for a reason. you
> > are worried about performance yet are calling a function that does
> nothing,
> > which will take more memory cycles than a simple check for zero.
> >
>
> I didn't say don't make a change. I'm observing there are things to
> consider. In particular the optimization strategies that the SQLite team
> have pursued for several years now have been *tiny* little optimizations
> that would never be justifiable on their own but that really add up,
> particularly for small devices probably running on batteries.
>
>
> > Trying to memset a zero length is a bug, not the warning. Add an if
> > statement around it. If the variable is local, it will probably be
> > optimized as a register variable and a zero check of a register is a
> single
> > op-code.
> >
>
> Trying to memset a zero length buffer is not a bug if the length of the
> sequence of bytes that needs to be set is zero. C89 in particular does not
> disallow this use.
>
>
> > the problem with disabling warnings is that even if this instance is not
> > an error, some other part of the code may end up with the same situation
> > but is an error in the coding. I would prefer code that can be compiled
> > with all warnings turned on that gives no warnings than have a potential
> > problem because of a glitch in the code.
> >
>
> I don't think anyone called for disabling the warning. I think it should
> just be ignored in this case, just as I ignore warnings that are generated
> by Visual C++ when I compile SQLite there.
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[J Decker]

On Fri, Aug 21, 2015 at 10:04 AM, sqlite-mail  
wrote:
> Then do you think this is a bug ?
>>  Fri Aug 21 2015 18:57:33 CEST from "Simon Slavin" 
>>Subject: Re: [sqlite] Is this a bug ? How to rename a table and all
>>dependencies ?
while it may be considered a nice thing; it's not common practice to
rename tables, so few (none?) sql implementations automatically update
references to tables when they are renamed.  Not a bug; more like out
of scope.

It wouldn't know if you were moving a source table for archival
purposes and going to replace it with another empty one or moving
because you didn't like your original schema.
>>
>>  On 21 Aug 2015, at 12:20pm, sqlite-mail  
>> wrote:
>>
>>
>>>Does anybody knows how to rename a table and all it's dependencies in one
>>>go
>>> ?
>>>
>
>>  Can't be done. Sorry.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compile warnings

[David Bennett]

Addressing only standards compliance, the C99 (n1256) standard says as
follows.

7.21.1 /2
Where an argument declared as size_t n specifies the length of the array for
a
function, n can have the value zero on a call to that function. Unless
explicitly stated
otherwise in the description of a particular function in this subclause,
pointer arguments
on such a call shall still have valid values, as described in 7.1.4. On such
a call, a
function that locates a character finds no occurrence, a function that
compares two
character sequences returns zero, and a function that copies characters
copies zero
characters.

Later versions of the standard contain similar wording.

A zero value for the third argument of memset() is standards compliant. Any
warning by any compiler is for the convenience of developers and may be
safely disabled or ignored while remaining standards compliant.

In my opinion, disabling or simply ignoring the warning are both legitimate
choices. Modifying the code to suppress the warning is NOT.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott
Robison
Sent: Friday, 21 August 2015 3:05 AM
To: General Discussion of SQLite Database

Subject: Re: [sqlite] Compile warnings

On Thu, Aug 20, 2015 at 10:46 AM, Scott Doctor 
wrote:

> My opinion is to keep it portable. The warning is there for a reason. 
> you are worried about performance yet are calling a function that does 
> nothing, which will take more memory cycles than a simple check for zero.
>

I didn't say don't make a change. I'm observing there are things to
consider. In particular the optimization strategies that the SQLite team
have pursued for several years now have been *tiny* little optimizations
that would never be justifiable on their own but that really add up,
particularly for small devices probably running on batteries.


> Trying to memset a zero length is a bug, not the warning. Add an if 
> statement around it. If the variable is local, it will probably be 
> optimized as a register variable and a zero check of a register is a 
> single op-code.
>

Trying to memset a zero length buffer is not a bug if the length of the
sequence of bytes that needs to be set is zero. C89 in particular does not
disallow this use.


> the problem with disabling warnings is that even if this instance is 
> not an error, some other part of the code may end up with the same 
> situation but is an error in the coding. I would prefer code that can 
> be compiled with all warnings turned on that gives no warnings than 
> have a potential problem because of a glitch in the code.
>

I don't think anyone called for disabling the warning. I think it should
just be ignored in this case, just as I ignore warnings that are generated
by Visual C++ when I compile SQLite there.

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

[sqlite] Compile warnings

[Scott Robison]

And C89 doesn't have the valid pointer requirement
On Aug 21, 2015 7:03 AM, "David Bennett"  wrote:

> Addressing only standards compliance, the C99 (n1256) standard says as
> follows.
>
> 7.21.1 /2
> Where an argument declared as size_t n specifies the length of the array
> for
> a
> function, n can have the value zero on a call to that function. Unless
> explicitly stated
> otherwise in the description of a particular function in this subclause,
> pointer arguments
> on such a call shall still have valid values, as described in 7.1.4. On
> such
> a call, a
> function that locates a character finds no occurrence, a function that
> compares two
> character sequences returns zero, and a function that copies characters
> copies zero
> characters.
>
> Later versions of the standard contain similar wording.
>
> A zero value for the third argument of memset() is standards compliant. Any
> warning by any compiler is for the convenience of developers and may be
> safely disabled or ignored while remaining standards compliant.
>
> In my opinion, disabling or simply ignoring the warning are both legitimate
> choices. Modifying the code to suppress the warning is NOT.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott
> Robison
> Sent: Friday, 21 August 2015 3:05 AM
> To: General Discussion of SQLite Database
> 
> Subject: Re: [sqlite] Compile warnings
>
> On Thu, Aug 20, 2015 at 10:46 AM, Scott Doctor 
> wrote:
>
> > My opinion is to keep it portable. The warning is there for a reason.
> > you are worried about performance yet are calling a function that does
> > nothing, which will take more memory cycles than a simple check for zero.
> >
>
> I didn't say don't make a change. I'm observing there are things to
> consider. In particular the optimization strategies that the SQLite team
> have pursued for several years now have been *tiny* little optimizations
> that would never be justifiable on their own but that really add up,
> particularly for small devices probably running on batteries.
>
>
> > Trying to memset a zero length is a bug, not the warning. Add an if
> > statement around it. If the variable is local, it will probably be
> > optimized as a register variable and a zero check of a register is a
> > single op-code.
> >
>
> Trying to memset a zero length buffer is not a bug if the length of the
> sequence of bytes that needs to be set is zero. C89 in particular does not
> disallow this use.
>
>
> > the problem with disabling warnings is that even if this instance is
> > not an error, some other part of the code may end up with the same
> > situation but is an error in the coding. I would prefer code that can
> > be compiled with all warnings turned on that gives no warnings than
> > have a potential problem because of a glitch in the code.
> >
>
> I don't think anyone called for disabling the warning. I think it should
> just be ignored in this case, just as I ignore warnings that are generated
> by Visual C++ when I compile SQLite there.
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] design problem involving trigger

[Scott Robison]

Another consideration: it is possible that two different anonymous books
are indeed different books. It is also possible a given "author" releases a
new rewritten book with the same name. I'm thinking of the Hardy Boys
series in particular from my personal experience. Not a big deal, but
thought I'd share it.
On Aug 21, 2015 7:29 AM, "Stephen Chrzanowski"  wrote:

> Another option would be to not use NULL but use an empty string.
>
> On Fri, Aug 21, 2015 at 9:17 AM, R.Smith  wrote:
>
> >
> >
> > On 2015-08-21 04:47 AM, Will Parsons wrote:
> >
> >> I'm working on a program that involves a catalogue of books.  Part of
> >> the database schema looks like this:
> >>
> >> create table Books(id integer primary key,
> >> title text collate nocase not null,
> >> author references Authors(id),
> >> ...
> >> unique(title, author));
> >>
> >> create table Authors(id integer primary key,
> >>   name text unique not null check(name <> ''));
> >>
> >> The idea here is that the title+author of a book must be unique, but a
> >> book may not necessarily have an author associated with it.  But, the
> >> schema fragment as I have presented it does not disallow entering the
> >> same title with a null author multiple times, which is clearly
> >> undesirable.
> >>
> >> In thinking about how to avoid this, one possibility that has occurred
> >> to me is to add an entry to the Authors table with id=0, name=NULL, and
> >> to modify the Books table to be:
> >>
> >> create table Books(id integer primary key,
> >> title text collate nocase not null,
> >> author references Authors(id) not null,
> >> ...
> >> unique(title, author));
> >>
> >> With this, entries in the Books table that presently have the author
> >> field set to NUll would instead have author=0.
> >>
> >> What I would like to have is a trigger that when an attempt is made to
> >> enter a new record into the Books table with a NULL author field, is
> >> to force the author field to 0 instead.  I can't see how to do this
> >> with a "before insert" trigger, though.  Perhaps I'm approaching this
> >> the wrong way; in any case I'd appreciate advice.
> >>
> >>
> > Nothing wrong with your approach, simply declare the Author to be NOT
> NULL
> > and if it is possible to have non-Authors you could do what you suggest,
> > add a 0-Author to the parent table, or, you could use a partial Index
> > (available after SQLite 3.7 only I think) which would allow you to have
> > NULLS but still maintain a strict relation, something like this:
> >
> > create table Books(id integer primary key,
> >title text collate nocase not null,
> >author int not null references Authors(id),
> >...
> >);
> >
> > create unique index uBookAuth on Books(title,author) where author is not
> > null;
> >
> > create table Authors(id integer primary key,
> >  name text unique not null check(name <> ''));
> >
> >
> > Read more at:
> > http://www.sqlite.org/partialindex.html
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] design problem involving trigger

[Stephen Chrzanowski]

Another option would be to not use NULL but use an empty string.

On Fri, Aug 21, 2015 at 9:17 AM, R.Smith  wrote:

>
>
> On 2015-08-21 04:47 AM, Will Parsons wrote:
>
>> I'm working on a program that involves a catalogue of books.  Part of
>> the database schema looks like this:
>>
>> create table Books(id integer primary key,
>> title text collate nocase not null,
>> author references Authors(id),
>> ...
>> unique(title, author));
>>
>> create table Authors(id integer primary key,
>>   name text unique not null check(name <> ''));
>>
>> The idea here is that the title+author of a book must be unique, but a
>> book may not necessarily have an author associated with it.  But, the
>> schema fragment as I have presented it does not disallow entering the
>> same title with a null author multiple times, which is clearly
>> undesirable.
>>
>> In thinking about how to avoid this, one possibility that has occurred
>> to me is to add an entry to the Authors table with id=0, name=NULL, and
>> to modify the Books table to be:
>>
>> create table Books(id integer primary key,
>> title text collate nocase not null,
>> author references Authors(id) not null,
>> ...
>> unique(title, author));
>>
>> With this, entries in the Books table that presently have the author
>> field set to NUll would instead have author=0.
>>
>> What I would like to have is a trigger that when an attempt is made to
>> enter a new record into the Books table with a NULL author field, is
>> to force the author field to 0 instead.  I can't see how to do this
>> with a "before insert" trigger, though.  Perhaps I'm approaching this
>> the wrong way; in any case I'd appreciate advice.
>>
>>
> Nothing wrong with your approach, simply declare the Author to be NOT NULL
> and if it is possible to have non-Authors you could do what you suggest,
> add a 0-Author to the parent table, or, you could use a partial Index
> (available after SQLite 3.7 only I think) which would allow you to have
> NULLS but still maintain a strict relation, something like this:
>
> create table Books(id integer primary key,
>title text collate nocase not null,
>author int not null references Authors(id),
>...
>);
>
> create unique index uBookAuth on Books(title,author) where author is not
> null;
>
> create table Authors(id integer primary key,
>  name text unique not null check(name <> ''));
>
>
> Read more at:
> http://www.sqlite.org/partialindex.html
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Is this a bug ? Pragmas do not accept qualified names !

[Scott Hess]

I think you wanted:
  PRAGMA attached_db.table_info(one_table);

-scott

On Fri, Aug 21, 2015 at 7:35 AM, sqlite-mail 
wrote:

> Hello !
>
> Today I'm working with sqlite3 with attached databases and when I tried to
> get info about a tbale using "PRAGMA TABLE_INFO" I discovered that pragmas
> do
> not work with qualified/prefixed names like:
>
> PRAGMA table_info(attached_db.one_table)
>
>
>
> Is this a bug ?
>
> Cheers !
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] design problem involving trigger

[Will Parsons]

I'm working on a program that involves a catalogue of books.  Part of
the database schema looks like this:

create table Books(id integer primary key,
   title text collate nocase not null,
   author references Authors(id),
   ...
   unique(title, author));

create table Authors(id integer primary key,
 name text unique not null check(name <> ''));

The idea here is that the title+author of a book must be unique, but a
book may not necessarily have an author associated with it.  But, the
schema fragment as I have presented it does not disallow entering the
same title with a null author multiple times, which is clearly
undesirable.

In thinking about how to avoid this, one possibility that has occurred
to me is to add an entry to the Authors table with id=0, name=NULL, and
to modify the Books table to be:

create table Books(id integer primary key,
   title text collate nocase not null,
   author references Authors(id) not null,
   ...
   unique(title, author));

With this, entries in the Books table that presently have the author
field set to NUll would instead have author=0.

What I would like to have is a trigger that when an attempt is made to
enter a new record into the Books table with a NULL author field, is
to force the author field to 0 instead.  I can't see how to do this
with a "before insert" trigger, though.  Perhaps I'm approaching this
the wrong way; in any case I'd appreciate advice.

-- 
Will

[sqlite] DbFunctions.TruncateTime

[Steffen Mangold]

Hi,

how can I trunc time in EntityFramework?

I tried it this way:

model.Datas
.GroupBy(d => DbFunctions.TruncateTime(d.TimeStamp))
.Select(d => d.Key.Value)
.ToArray();

But get this error:
"SQLite error (1): no such function: TruncateTime"

How else can I use the "date(timestring) function in EntityFramework?


Regards Steffen

[sqlite] When sqlite3_close() returns SQL_BUSY

[Jeff M]

Sometimes my iOS app creates an unreasonable number of prepared statements 
(perhaps 1,000, an app bug that I'm fixing).  These prepared statements are 
later finalized just prior to doing sqlite3_close(), which sometimes returns 
SQL_BUSY.  The docs say SQL_BUSY will be returned if I haven't finalized all 
prepared statements, but I believe I have done so.  My iOS app has only one 
connection to the DB and I'm doing all this work on the main thread.

1.  The docs don't say what to do in the case of SQL_BUSY.  Does that mean I've 
certainly failed to finalize one or more prepared statements, or does SQLite 
just need more time (in which case can I loop on sqlite3_close() until I get 
SQLITE_OK)?

2.  Does SQLite keep a record of prepared statements?  If so, is there a way I 
can ask SQLite to close them all, or are there any tricks to debugging which 
statements have not been finalized?

Jeff