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
