;) 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- >boun...@mailinglists.sqlite.org] On Behalf Of R Smith >Sent: Wednesday, 15 August, 2018 03:55 >To: sqlite-users@mailinglists.sqlite.org >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 >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
