Database metadata: what changed
Revision 2 to 3. Removed lines have a minus sign; added lines have a plus sign.
| Old | New | Line | |
|---|---|---|---|
| 1 | 1 | Database metadata is data about a database. In this article, we also cover information about the database connection/server/client. | |
| 2 | 2 | ||
| 3 | 3 | ## SQLDB | |
| 4 | 4 | ||
| 5 | 5 | ### Server & client information | |
| 6 | 6 | ||
| 7 | 7 | In FPC development versions as of November 2012, you can use *GetConnectionInfo*. See [GetConnectionInfo documentation](http://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnection.getconnectioninfo.html) Clarification: | |
| 8 | 8 | ||
| 9 | 9 | - *citServerVersion*: the server version is returned in the form of a string formatted as a number (e.g. 0205 representing 2.5) | |
| 10 | 10 | ||
| 11 | 11 | if the T*Connection in question does not support (part of) this, the functions will return empty values. | |
| 12 | 12 | ||
| 13 | 13 | #### Firebird/Interbase specifics | |
| 14 | 14 | ||
| 15 | 15 | - *citServerVersion* returns major and minor numbers only for Firebird (e.g. 0205 for version 2.5.1); it returns 3 numbers for Interbase | |
| 16 | 16 | - *citClientVersion* is not supported and probably will never be supported for Firebird on Linux/*Nix due to missing functionality | |
| 17 | 17 | ||
| 18 | 18 | In addition to the functions above, as of December 2012, the Firebird connector provides the *GetODS* function that shows the ODS major version of the database you are connected to. This ODS version determines what features are available for use - as you can e.g. use a database created with Firebird 2.1 with a Firebird 2.5 server, but the features will be limited to 2.1 functionality. | |
| 19 | 19 | ||
| 20 | 20 | ### Schema information | |
| 21 | 21 | ||
| 22 | 22 | Also known as a database's DDL (Data Definition Language), a schema is the representation of the tables, views, procedures and other objects that make up a database. | |
| 23 | 23 | ||
| 24 | 24 | See [Retrieving Schema Information documentation](http://www.freepascal.org/docs-html/fcl/sqldb/retrievingschemainformation.html) | |
| 25 | 25 | ||
| 26 | 26 | *GetSchemaInfoSQL* is not implemented equally for every connector; the Firebird connector currently has most of the functionality implemented; the others (e.g. MS SQL, Sybase, Oracle) have less functionality. Patches are welcome. | |
| 27 | 27 | ||
| 28 | 28 | #### Proposal for extension/uniformization | |
| 29 | 29 | ||
| 30 | 30 | **Note: there were discussions/a proposal on how to harmonize and extend metadata retrieval for SQLDB on the FPC mailing list. Please update the below with all results of that discussion:** | |
| 31 | 31 | ||
| 32 | 32 | Suggest converging to SQL standard INFORMATION_SCHEMA column naming in existing GetSchemaInfoSQL. So for: | |
| 33 | 33 | ||
| 34 | 34 | - stTables use INFORMATION_SCHEMA.TABLES with column naming: | |
| 35 | 35 | ||
| 36 | - | ||
| 36 | + | ```text | |
| 37 | + | TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE ('BASE TABLE', 'VIEW', 'GLOBAL TEMPORARY', 'LOCAL TEMPORARY'), ... | |
| 38 | + | ``` | |
| 37 | 39 | ||
| 38 | 40 | (for databases which do not implement INFORMATION_SCHEMA views leave queries to system tables but use above mentioned column naming) | |
| 39 | 41 | ||
| 40 | 42 | - stColumns use INFORMATION_SCHEMA.COLUMNS with column naming: | |
| 41 | 43 | ||
| 42 | - | ||
| 44 | + | ```text | |
| 45 | + | TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, ..., CHARACTER_SET_NAME, COLLATION_NAME, DOMAIN_NAME, ..., IS_IDENTITY, IS_GENERATED | |
| 46 | + | ``` | |
| 43 | 47 | ||
| 44 | 48 | - stProcedures use INFORMATION_SCHEMA.ROUTINES with column naming: | |
| 45 | 49 | ||
| 46 | - | ||
| 50 | + | ``` pascal | |
| 51 | + | SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE ('PROCEDURE', 'FUNCTION',...), ... | |
| 52 | + | ``` | |
| 47 | 53 | ||
| 48 | 54 | Note: On other side Delphi uses slightly different column naming, described in [The_Structure_of_Metadata_Datasets](http://docwiki.embarcadero.com/RADStudio/XE3/en/The_Structure_of_Metadata_Datasets) But tests with Delphi XE MySQL and Interbase DBX drivers shows results which do not match this specification (f.e. for stTables these columns are returned: CatalogName, SchemaName, TableName, TableType (without "_") and without RECNO column) and for **ADO OpenSchema** method these columns are returned: | |
| 49 | 55 | ||
| 50 | - | ||
| 51 | - | ||
| 52 | - | ||
| 53 | - | ||
| 54 | - | ||
| 56 | + | ``` pascal | |
| 57 | + | siTables (adSchemaTables): TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE(widestring) | |
| 58 | + | siColumns (adSchemaColumns): TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, ..., CHARACTER_SET_NAME, COLLATION_NAME, DOMAIN_NAME, ..., IS_COMPUTED | |
| 59 | + | siIndexes (adSchemaIndexes): TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, INDEX_CATALOG, INDEX_SCHEMA, INDEX_NAME, PRIMARY_KEY(boolean), UNIQUE(boolean), CLUSTERED(boolean), TYPE(word), FILL_FACTOR, ..., ORDINAL_POSITION, COLUMN_NAME, ... (one row for every column in index) | |
| 60 | + | siProcedures (adSchemaProcedures): PROCEDURE_CATALOG, PROCEDURE_SCHEMA, PROCEDURE_NAME, PROCEDURE_TYPE(Smallint), ... | |
| 61 | + | siSchemata (adSchemaSchemata): CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER, ... | |
| 62 | + | ``` | |
| 55 | 63 | ||
| 56 | 64 | #### Lazarus TSQLQuery metadata tool | |
| 57 | 65 | ||
| 58 | 66 | *GetSchemaInfoSQL* is used in Lazarus: in design mode, if you use the Object Inspector to edit the SQL property of a *TSQLQuery* where the database connection is connected, you can not only edit the text of the SQL query in the SQL Code tab, but also retrieve information in the Metadata tab by filling in some details and pressing on the Play/Go key: | |
| 59 | 67 | ||
| 60 | 68 |  | |
| 61 | 69 | ||
| 62 | 70 | ### Data dictionary schema information | |
| 63 | 71 | ||
| 64 | 72 | There is a separate FPC data dictionary package that has support for schema information. This package is used in the [[lazdatadesktop]] project supplied with Lazarus. | |
| 65 | 73 | ||
| 66 | 74 | ## Database specific information | |
| 67 | 75 | ||
| 68 | 76 | Many databases have either | |
| 69 | 77 | ||
| 70 | 78 | - system tables/views (which you can select from) or | |
| 71 | 79 | - stored procedures | |
| 72 | 80 | ||
| 73 | 81 | that show information about database objects as well as server version etc. | |
| 74 | 82 | ||
| 75 | 83 | Examples are the @@VERSION variables in MS SQL Server and Sybase servers. | |
| 76 | 84 | ||
| 77 | 85 | If your database connection library does not support other means of getting this information, you can use this (and submit a patch/improvement request on the relevant bug tracker for incorporation into SQLDB/Zeos etc) | |
| 78 | 86 | ||
| 79 | 87 | ## See also | |
| 80 | 88 | ||
| 81 | 89 | - [[Working With TSQLQuery]] More information about queries | |
| 82 | 90 | ||
| 83 | 91 | --- | |
| 84 | 92 | *From the Free Pascal wiki, page "Database metadata", as it stood on 2022-10-03 after a change by Slawek, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).* | |
| 85 | 93 | Category: Databases | |
| 86 | 94 | Category: FPC | |
| 87 | 95 | Category: Lazarus |