I want to store the 'structure' of an Database.
Currently i am working on Oracle, but as soon as i finish with it, I have to move on to
MySQL and MS SQL Server. So, I need a common datatype (might be a custom datatype) to store the structure of all these 3 types of database.
Let me explain in detail:
First, I would provide a schema name and credentials to my app. The app will then list out all of the tablespaces, datafiles, tables, columns ect. (ie, the details of the DB STRUCTURE, not the data inside the tables & columns) that are present in the said schema.
What I want to do next is to store this 'DB STRUCTURE' output in an object/variable in memory, in such a way that preserves their relation to each other. I mean,
If table1 and table2 exists inside tablespace_01, and table3 inside tablespace_02
then I would like to store names "tablespace_01", "tablespace_02", "table1", "table2", "table3" in such a way that if I choose any one of these string, then I must be easily able to know what type of thing it is (
is it a tablespace name, datafile name, a table name or a column name?) and also its parents/children (
if it is a table name, then which tablespace does it belong to? what columns come under it? if it is a column, then which table does it belong to?)
Should I go for a struct for storing the column name and its attributes, and then
**psuedocode**
struct Column
{
CString columnName;
CString dataType;
int dataLength;
CString defaultValue; BOOL isUnique;
BOOL isPrimary; }
vector<column> Table;
vector<Table> Tablespace;
vector<Tablespace> Schema;
OR should I do struct for all of them?
Or is their any better idea?
Requirements from whatever datatype {vector, struct or anything else that i end up using}:
this stored structure will be used to compare current structure of the schema to some previous structure of schema to check if there was any sort of structural change... such as if somebody dropped a column or changed it datatype or length, or might be somebody added a new table, or a new datafile was added to a particular tablespace...