The upcoming alter table rename column

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

The upcoming alter table rename column

Domingo Alvarez Duarte
Hello Richard !

I'm following the changes you are making to a add "alter table rename
column" capability to sqlite and I think that it's a good moment to add
a new system table for the columns (I know that we can somehow get this
info now with "pragmas") this way we can get/use this info using "SQL"
aka "data dictionary". Probably if sqlite already provided this facility
less low level changes would be needed to this task ("rename column")
and others.

A simplified version of
https://www.postgresql.org/docs/10/static/catalog-pg-attribute.html 
would be nice.

Cheers !

_______________________________________________
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: The upcoming alter table rename column

wmertens
You can, since 3.16, get most pragma results as table-valued functions. I
think what you want is something like

SELECT * FROM pragma_table_info("tableName");

On Wed, Aug 15, 2018 at 8:04 AM Domingo Alvarez Duarte <[hidden email]>
wrote:

> Hello Richard !
>
> I'm following the changes you are making to a add "alter table rename
> column" capability to sqlite and I think that it's a good moment to add
> a new system table for the columns (I know that we can somehow get this
> info now with "pragmas") this way we can get/use this info using "SQL"
> aka "data dictionary". Probably if sqlite already provided this facility
> less low level changes would be needed to this task ("rename column")
> and others.
>
> A simplified version of
> https://www.postgresql.org/docs/10/static/catalog-pg-attribute.html
> would be nice.
>
> Cheers !
>
> _______________________________________________
> 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: The upcoming alter table rename column

Keith Medcalf

single-quotes around the tablename -- it is a string not an identifier ...


---
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 [mailto:sqlite-users-
>[hidden email]] On Behalf Of Wout Mertens
>Sent: Wednesday, 15 August, 2018 00:33
>To: SQLite mailing list
>Subject: Re: [sqlite] The upcoming alter table rename column
>
>You can, since 3.16, get most pragma results as table-valued
>functions. I
>think what you want is something like
>
>SELECT * FROM pragma_table_info("tableName");
>
>On Wed, Aug 15, 2018 at 8:04 AM Domingo Alvarez Duarte
><[hidden email]>
>wrote:
>
>> Hello Richard !
>>
>> I'm following the changes you are making to a add "alter table
>rename
>> column" capability to sqlite and I think that it's a good moment to
>add
>> a new system table for the columns (I know that we can somehow get
>this
>> info now with "pragmas") this way we can get/use this info using
>"SQL"
>> aka "data dictionary". Probably if sqlite already provided this
>facility
>> less low level changes would be needed to this task ("rename
>column")
>> and others.
>>
>> A simplified version of
>> https://www.postgresql.org/docs/10/static/catalog-pg-attribute.html
>> would be nice.
>>
>> Cheers !
>>
>> _______________________________________________
>> 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



_______________________________________________
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: The upcoming alter table rename column

R Smith-2
In reply to this post by Domingo Alvarez Duarte
On 2018/08/15 8:03 AM, Domingo Alvarez Duarte wrote:

> Hello Richard !
>
> I'm following the changes you are making to a add "alter table rename
> column" capability to sqlite and I think that it's a good moment to
> add a new system table for the columns (I know that we can somehow get
> this info now with "pragmas") this way we can get/use this info using
> "SQL" aka "data dictionary". Probably if sqlite already provided this
> facility less low level changes would be needed to this task ("rename
> column") and others.
>
> A simplified version of
> https://www.postgresql.org/docs/10/static/catalog-pg-attribute.html 
> would be nice.

This has been existing for quite a while in SQLite, and not only this,
but quite a few schema enumeration functions via the
table-valued-function form of the pragmas.

Someone posted on this very forum (I believe it was Keith) some nice
views to get schema information much like other DB systems, and i have
shamelessly plagiarised it, made some improvements (for my needs) and
since then I automatically add it to any new DB.

Here is the script, hope it helps you too:

DROP VIEW SysIndexColumns;
DROP VIEW SysIndexes;
DROP VIEW SysColumns;
DROP VIEW SysObjects;

CREATE VIEW SysObjects AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE
   FROM (SELECT type AS ObjectType, name AS ObjectName
           FROM sqlite_master
          WHERE type IN ('table', 'view', 'index')
        )
;

CREATE VIEW SysColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, ColumnID
COLLATE NOCASE,
        ColumnName COLLATE NOCASE, Type COLLATE NOCASE, Affinity COLLATE
NOCASE,
        IsNotNull, DefaultValue, IsPrimaryKey
   FROM (SELECT ObjectType, ObjectName, cid AS ColumnID, name AS
ColumnName, type AS Type,
                CASE
                  WHEN trim(type) = '' THEN 'Blob'
                  WHEN instr(UPPER(type),'INT' )>0 THEN 'Integer'
                  WHEN instr(UPPER(type),'CLOB')>0 THEN 'Text'
                  WHEN instr(UPPER(type),'CHAR')>0 THEN 'Text'
                  WHEN instr(UPPER(type),'TEXT')>0 THEN 'Text'
                  WHEN instr(UPPER(type),'BLOB')>0 THEN 'Blob'
                  WHEN instr(UPPER(type),'REAL')>0 THEN 'Real'
                  WHEN instr(UPPER(type),'FLOA')>0 THEN 'Real'
                  WHEN instr(UPPER(type),'DOUB')>0 THEN 'Real'
                  ELSE 'Numeric'
                END AS Affinity,
                "notnull" AS IsNotNull, dflt_value as DefaultValue, pk
AS IsPrimaryKey
           FROM SysObjects
           JOIN pragma_table_info(ObjectName)
        )
;

CREATE VIEW SysIndexes AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
        IndexID, IsUniqueIndex COLLATE NOCASE, IndexOrigin COLLATE
NOCASE, isPartialIndex
   FROM (SELECT ObjectType,ObjectName,name AS IndexName, seq AS IndexID,
                "unique" AS isUnique, origin AS IndexOrigin, partial AS
isPartialIndex
           FROM SysObjects
           JOIN pragma_index_list(ObjectName)
        )
;

CREATE VIEW SysIndexColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
        IndexColumnSequence, ColumnID, ColumnName COLLATE NOCASE,
        isDescendingOrder, Collation, isPartOfKey
   FROM (SELECT ObjectType, ObjectName, IndexName, seqno AS
IndexColumnSequence, cid AS ColumnID,
                name AS ColumnName, "desc" AS isDescendingOrder, coll AS
Collation, key AS isPartOfKey
           FROM SysIndexes
           JOIN pragma_index_xinfo(IndexName)
        )
;





_______________________________________________
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: The upcoming alter table rename column

Petite Abeille-2


> On Aug 15, 2018, at 11:55 AM, R Smith <[hidden email]> wrote:
>
> This has been existing for quite a while in SQLite, and not only this, but quite a few schema enumeration functions via the table-valued-function form of the pragmas.

FWIW, information_schema would be the relevant ANSI-standard in that matter:

https://en.wikipedia.org/wiki/Information_schema

The following data dictionaries can be implemented in a pretty straightforward way in sqlite:

information_schema.catalog_name
information_schema.schemata
information_schema.tables
information_schema.columns
information_schema.table_constraints
information_schema.referential_constraints
information_schema.key_column_usage

YMMV.



_______________________________________________
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: The upcoming alter table rename column

R Smith-2
In reply to this post by Domingo Alvarez Duarte
In case anyone followed this topic, it was noted to me off-list that I
included a mistake in the SQL posted, which might confuse people who try it!

Please see below the corrected version.
Cheers!


--  Schema Info Views
--
-- This is a set of views that supply queryable Schema information for
SQLite DBs in
--   table format.
--

DROP VIEW SysIndexColumns;
DROP VIEW SysIndexes;
DROP VIEW SysColumns;
DROP VIEW SysObjects;

CREATE VIEW SysObjects AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE
   FROM (SELECT type AS ObjectType, name AS ObjectName
           FROM sqlite_master
          WHERE type IN ('table', 'view', 'index')
        )
;

CREATE VIEW SysColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, ColumnID
COLLATE NOCASE,
        ColumnName COLLATE NOCASE, Type COLLATE NOCASE, Affinity COLLATE
NOCASE,
        IsNotNull, DefaultValue, IsPrimaryKey
   FROM (SELECT ObjectType, ObjectName, cid AS ColumnID, name AS
ColumnName, type AS Type,
                CASE
                  WHEN trim(type) = '' THEN 'Blob'
                  WHEN instr(UPPER(type),'INT' )>0 THEN 'Integer'
                  WHEN instr(UPPER(type),'CLOB')>0 THEN 'Text'
                  WHEN instr(UPPER(type),'CHAR')>0 THEN 'Text'
                  WHEN instr(UPPER(type),'TEXT')>0 THEN 'Text'
                  WHEN instr(UPPER(type),'BLOB')>0 THEN 'Blob'
                  WHEN instr(UPPER(type),'REAL')>0 THEN 'Real'
                  WHEN instr(UPPER(type),'FLOA')>0 THEN 'Real'
                  WHEN instr(UPPER(type),'DOUB')>0 THEN 'Real'
                  ELSE 'Numeric'
                END AS Affinity,
                "notnull" AS IsNotNull, dflt_value as DefaultValue, pk
AS IsPrimaryKey
           FROM SysObjects
           JOIN pragma_table_info(ObjectName)
        )
;

CREATE VIEW SysIndexes AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
        IndexID, IsUnique COLLATE NOCASE, IndexOrigin COLLATE NOCASE,
isPartialIndex
   FROM (SELECT ObjectType,ObjectName,name AS IndexName, seq AS IndexID,
                "unique" AS isUnique, origin AS IndexOrigin, partial AS
isPartialIndex
           FROM SysObjects
           JOIN pragma_index_list(ObjectName)
        )
;

CREATE VIEW SysIndexColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
        IndexColumnSequence, ColumnID, ColumnName COLLATE NOCASE,
        isDescendingOrder, Collation, isPartOfKey
   FROM (SELECT ObjectType, ObjectName, IndexName, seqno AS
IndexColumnSequence, cid AS ColumnID,
                name AS ColumnName, "desc" AS isDescendingOrder, coll AS
Collation, key AS isPartOfKey
           FROM SysIndexes
           JOIN pragma_index_xinfo(IndexName)
        )
;




_______________________________________________
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: The upcoming alter table rename column

Keith Medcalf
In reply to this post by R Smith-2

;)

And I am stealing it back ... I like your changes that show the computed column affinity!

---
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 [mailto:sqlite-users-
>[hidden email]] On Behalf Of R Smith
>Sent: Wednesday, 15 August, 2018 03:55
>To: [hidden email]
>Subject: Re: [sqlite] The upcoming alter table rename column
>
>On 2018/08/15 8:03 AM, Domingo Alvarez Duarte wrote:
>> Hello Richard !
>>
>> I'm following the changes you are making to a add "alter table
>rename
>> column" capability to sqlite and I think that it's a good moment to
>> add a new system table for the columns (I know that we can somehow
>get
>> this info now with "pragmas") this way we can get/use this info
>using
>> "SQL" aka "data dictionary". Probably if sqlite already provided
>this
>> facility less low level changes would be needed to this task
>("rename
>> column") and others.
>>
>> A simplified version of
>> https://www.postgresql.org/docs/10/static/catalog-pg-attribute.html
>> would be nice.
>
>This has been existing for quite a while in SQLite, and not only
>this,
>but quite a few schema enumeration functions via the
>table-valued-function form of the pragmas.
>
>Someone posted on this very forum (I believe it was Keith) some nice
>views to get schema information much like other DB systems, and i
>have
>shamelessly plagiarised it, made some improvements (for my needs) and
>since then I automatically add it to any new DB.
>
>Here is the script, hope it helps you too:
>
>DROP VIEW SysIndexColumns;
>DROP VIEW SysIndexes;
>DROP VIEW SysColumns;
>DROP VIEW SysObjects;
>
>CREATE VIEW SysObjects AS
>SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE
>   FROM (SELECT type AS ObjectType, name AS ObjectName
>           FROM sqlite_master
>          WHERE type IN ('table', 'view', 'index')
>        )
>;
>
>CREATE VIEW SysColumns AS
>SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, ColumnID
>COLLATE NOCASE,
>        ColumnName COLLATE NOCASE, Type COLLATE NOCASE, Affinity
>COLLATE
>NOCASE,
>        IsNotNull, DefaultValue, IsPrimaryKey
>   FROM (SELECT ObjectType, ObjectName, cid AS ColumnID, name AS
>ColumnName, type AS Type,
>                CASE
>                  WHEN trim(type) = '' THEN 'Blob'
>                  WHEN instr(UPPER(type),'INT' )>0 THEN 'Integer'
>                  WHEN instr(UPPER(type),'CLOB')>0 THEN 'Text'
>                  WHEN instr(UPPER(type),'CHAR')>0 THEN 'Text'
>                  WHEN instr(UPPER(type),'TEXT')>0 THEN 'Text'
>                  WHEN instr(UPPER(type),'BLOB')>0 THEN 'Blob'
>                  WHEN instr(UPPER(type),'REAL')>0 THEN 'Real'
>                  WHEN instr(UPPER(type),'FLOA')>0 THEN 'Real'
>                  WHEN instr(UPPER(type),'DOUB')>0 THEN 'Real'
>                  ELSE 'Numeric'
>                END AS Affinity,
>                "notnull" AS IsNotNull, dflt_value as DefaultValue,
>pk
>AS IsPrimaryKey
>           FROM SysObjects
>           JOIN pragma_table_info(ObjectName)
>        )
>;
>
>CREATE VIEW SysIndexes AS
>SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE,
>IndexName
>COLLATE NOCASE,
>        IndexID, IsUniqueIndex COLLATE NOCASE, IndexOrigin COLLATE
>NOCASE, isPartialIndex
>   FROM (SELECT ObjectType,ObjectName,name AS IndexName, seq AS
>IndexID,
>                "unique" AS isUnique, origin AS IndexOrigin, partial
>AS
>isPartialIndex
>           FROM SysObjects
>           JOIN pragma_index_list(ObjectName)
>        )
>;
>
>CREATE VIEW SysIndexColumns AS
>SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE,
>IndexName
>COLLATE NOCASE,
>        IndexColumnSequence, ColumnID, ColumnName COLLATE NOCASE,
>        isDescendingOrder, Collation, isPartOfKey
>   FROM (SELECT ObjectType, ObjectName, IndexName, seqno AS
>IndexColumnSequence, cid AS ColumnID,
>                name AS ColumnName, "desc" AS isDescendingOrder, coll
>AS
>Collation, key AS isPartOfKey
>           FROM SysIndexes
>           JOIN pragma_index_xinfo(IndexName)
>        )
>;
>
>
>
>
>
>_______________________________________________
>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
dmp
Reply | Threaded
Open this post in threaded view
|

Re: The upcoming alter table rename column

dmp
In reply to this post by Domingo Alvarez Duarte
Still doesn't work when saving directly to file. Nice
though. Modified version follows. Thanks.

danap.

Fixes/Adds:

1. Header comment added --.
2. Added drop IF EXISTS.
3. Each CREATE VIEW added space after AS.
4. Placed end of statement semicolon directly after parenthesis.
5. Has only \n for end of lines.


--  Schema Info Views
--
-- This is a set of views that supply queryable Schema information for
-- SQLite DBs in
-- table format.
--

DROP VIEW IF EXISTS SysIndexColumns;
DROP VIEW IF EXISTS SysIndexes;
DROP VIEW IF EXISTS SysColumns;
DROP VIEW IF EXISTS SysObjects;

CREATE VIEW SysObjects AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE
   FROM (SELECT type AS ObjectType, name AS ObjectName
           FROM sqlite_master
          WHERE type IN ('table', 'view', 'index')
        );

CREATE VIEW SysColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, ColumnID
COLLATE NOCASE,
        ColumnName COLLATE NOCASE, Type COLLATE NOCASE, Affinity COLLATE
NOCASE,
        IsNotNull, DefaultValue, IsPrimaryKey
   FROM (SELECT ObjectType, ObjectName, cid AS ColumnID, name AS
ColumnName, type AS Type,
                CASE
                  WHEN trim(type) = '' THEN 'Blob'
                  WHEN instr(UPPER(type),'INT' )>0 THEN 'Integer'
                  WHEN instr(UPPER(type),'CLOB')>0 THEN 'Text'
                  WHEN instr(UPPER(type),'CHAR')>0 THEN 'Text'
                  WHEN instr(UPPER(type),'TEXT')>0 THEN 'Text'
                  WHEN instr(UPPER(type),'BLOB')>0 THEN 'Blob'
                  WHEN instr(UPPER(type),'REAL')>0 THEN 'Real'
                  WHEN instr(UPPER(type),'FLOA')>0 THEN 'Real'
                  WHEN instr(UPPER(type),'DOUB')>0 THEN 'Real'
                  ELSE 'Numeric'
                END AS Affinity,
                "notnull" AS IsNotNull, dflt_value as DefaultValue, pk
AS IsPrimaryKey
           FROM SysObjects
           JOIN pragma_table_info(ObjectName)
        );

CREATE VIEW SysIndexes AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
        IndexID, IsUnique COLLATE NOCASE, IndexOrigin COLLATE NOCASE,
isPartialIndex
   FROM (SELECT ObjectType,ObjectName,name AS IndexName, seq AS IndexID,
                "unique" AS isUnique, origin AS IndexOrigin, partial AS
isPartialIndex
           FROM SysObjects
           JOIN pragma_index_list(ObjectName)
        );

CREATE VIEW SysIndexColumns AS
SELECT ObjectType COLLATE NOCASE, ObjectName COLLATE NOCASE, IndexName
COLLATE NOCASE,
        IndexColumnSequence, ColumnID, ColumnName COLLATE NOCASE,
        isDescendingOrder, Collation, isPartOfKey
   FROM (SELECT ObjectType, ObjectName, IndexName, seqno AS
IndexColumnSequence, cid AS ColumnID,
                name AS ColumnName, "desc" AS isDescendingOrder, coll AS
Collation, key AS isPartOfKey
           FROM SysIndexes
           JOIN pragma_index_xinfo(IndexName)
        );

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