#11 Create a definition for Database metadata

[Anonymous]

Originally created by: jamieogl...@gmail.com
Originally owned by: jamieogl...@gmail.com

Create a definition for database metadata. The following are required:

A database header, including information such as the DBMS, the name of the database, its server and instance names, a provider name and connection string. The connection string must not contain passwords in clear text.

Table and Query headers. On each of these we need an indicator to distinguish between tables and queries, the object name and its schema, its primary key name and the partitioning column and function name. If there is no primary key, a suitable unique index will be used instead.

For each table or query we need a list of columns, a list of indices a list of foreign key constraints and a list of check constraints.

For each column we need the column name, the database data type name, the ODBC type code, its length and precision, a flag to indicate if null values are allowed, the column sequence number in the table, a flag to indicate if the column is part of the table's primary key, and the column sequence number in the primary key. The columns are saved in the same order as they appear in the metadata. Also needed are the default expressions, calculated expressions, identity or generator definitions and check constraints.

For each index we need indicators for the table's primary key, for unique and clustered indexes, and a list of key columns in key sequence.

For each foreign key constraint we need the foreign table name and an indicator if deletions are cascaded. We also need a list of the key columns in the local and foreign tables.

For each stored procedure we need a header with the schema and procedure names. We also need a list of parameters in sequence.

For each parameter we need its name, its data type (as for columns) and an indicator if it is a result, input, output or input/output parameter. Any characters such as T-SQL's @ preceding the parameter name are stripped off.

[Anonymous]

Originally posted by: jamieogl...@gmail.com

I have created a DatabaseDefinition data definition, and a T-SQL script to extract meta-data from a MS-SqlServer database for this definition.

The data types still need to be defined more precisely and independent of the MS SQL implementation. I am looking at using the ODBC data types, but they are not complete.

Further extensions to the generator data are needed, defining lookup tables and their dynamic inclusion in the data files. I want a set of data types that will allow me to map data from any data source to any programming language.

That is very ambitious, so pragmatically, I will be happy to support MS SQL, MS Access and, say MySql, to start with, and mapping to C#, VB.Net and Java to start with.

Status: Started