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

[Kees Nuyt]

On Fri, 21 Aug 2015 22:58:51 +0200, "sqlite-mail"
 wrote:

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

That would be nice to have in some development environment (you
could develop one, if nobody else has done it), but in my
opinion it does not belong in the core engine that SQLite is.
It's lite for good reasons.

It even would be fine to me if all ALTER TABLE features would be
removed.

-- 
Regards, 

Kees Nuyt

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

[Tim Streater]

On 22 Aug 2015 at 11:01, Kees Nuyt  wrote: 

> It even would be fine to me if all ALTER TABLE features would be
> removed.

I disagree with this, although I'd be satisfied if there were a separate 
library and API for such things. With an evolving application I need to have 
ALTER TABLE. Even the reduced version we have now is adequate.

--
Cheers  --  Tim

[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] 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] 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] 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 ? 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] 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