After column add, what should be done to update the schema?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

After column add, what should be done to update the schema?

Andy KU7T
Hi,

I use a simple ALTER TABLE ADD COLUMN statement. However, when I use the Sqlite Expert, the DDL is not reflected. Even though the column is created. I read somewhere that the DDL is really just a copy of the text when teh table was created. Is there a way to refresh this?

Thanks,
Andy

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

Re: After column add, what should be done to update the schema?

Jean-Christophe Deschamps-3
Bonjour,

>I use a simple ALTER TABLE ADD COLUMN statement. However, when I use
>the Sqlite Expert, the DDL is not reflected. Even though the column is
>created. I read somewhere that the DDL is really just a copy of the
>text when teh table was created. Is there a way to refresh this?

I can't reproduce the issue with neither Expert v3.5.96 nor v5.3.5.472.
Post a short reproducer.

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

Re: After column add, what should be done to update the schema?

Keith Medcalf
In reply to this post by Andy KU7T

Probably a bug.  SQLiteExpert does not even run on my computer.  It just farts in the wind and does not even bother to log or show an error message.  Maybe it tries to access some internal Microsoft Spying mechanism that I have disabled.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Andy KU7T
>Sent: Thursday, 27 February, 2020 17:03
>To: SQLite mailing list <[hidden email]>
>Subject: [sqlite] After column add, what should be done to update the
>schema?
>
>Hi,
>
>I use a simple ALTER TABLE ADD COLUMN statement. However, when I use the
>Sqlite Expert, the DDL is not reflected. Even though the column is
>created. I read somewhere that the DDL is really just a copy of the text
>when teh table was created. Is there a way to refresh this?
>
>Thanks,
>Andy
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: After column add, what should be done to update the schema?

David Raymond
In reply to this post by Andy KU7T
"I read somewhere that the DDL is really just a copy of the text when teh table was created"
It starts that way, but the ALTER TABLE command will update that string when it runs so that it reflects the new column.

Did you commit your changes after the alter table and before looking for the change in Sqlite Expert?

"However, when I use the Sqlite Expert, the DDL is not reflected. Even though the column is created."
Never having used Sqlite Expert, how are you determining that it was created? Further queries using the column work? Some GUI showing fields has been updated? Some other means?
When you say "the DDL is not reflected" how are you looking at the DDL? Is it some special window in Sqlite Expert? Are you explicitly running a select statement on sqlite_master? Some other method?

If you close Sqlite Expert and re-open it does it show the changes now?

At the moment it kind of sounds like Sqlite Expert is doing some sort of non-expert caching that it isn't updating the way it should.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: After column add, what should be done to update the schema?

Lee Gray-2
In reply to this post by Andy KU7T
I just tried it in v5.3.5.470 and v5.3.5.472 and it worked fine.

From: sqlite-users <[hidden email]> On Behalf Of Andy KU7T
Sent: Thursday, February 27, 2020 6:03 PM
To: SQLite mailing list <[hidden email]>
Subject: [sqlite] After column add, what should be done to update the schema?

Hi,

I use a simple ALTER TABLE ADD COLUMN statement. However, when I use the Sqlite Expert, the DDL is not reflected. Even though the column is created. I read somewhere that the DDL is really just a copy of the text when teh table was created. Is there a way to refresh this?

Thanks,
Andy

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

Re: After column add, what should be done to update the schema?

Anthony DeRobertis
In reply to this post by Andy KU7T
On 2/27/20 7:03 PM, Andy KU7T wrote:
> Hi,
>
> I use a simple ALTER TABLE ADD COLUMN statement. However, when I use the Sqlite Expert, the DDL is not reflected.

One thing to be aware of is that when SQLite adds the column, it often
doesn't format it like you'd expect. For example:

CREATE TABLE a (
     col1 integer not null primary key,
     col2 integer
);

adding a column may well wind up with something like:

CREATE TABLE a (
     col1 integer not null primary key,
     col2 integer, col3 integer
);

... note how it's been tacked on to the same line as col2. That can make
it easy to miss when reading through a pile of SQL.

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