What data structure should I use

Question

2.00/5 (1 vote)

See more:

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

C++

**psuedocode**

struct Column
{
     CString columnName;
     CString dataType;
     int dataLength;
     CString defaultValue; // NULL if no default value???
     BOOL isUnique;
     BOOL isPrimary; // Not sure about this
}

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...

Posted 19-Aug-15 22:41pm

princektd

Updated 20-Aug-15 19:50pm

v3

Add a Solution

Comments

Richard MacCutchan 20-Aug-15 5:25am

Why would you do this when the information can be got from the server?

princektd 21-Aug-15 1:44am

The app requirement is for the user to load a DB structure into memory, make changes to it in memory and save the changed structure as an XML file. Then, if they want, they can execute the changes to the DB, or leave the DB as it is... So, whatever changes they want to make to the DB structure should be in memory only without actually affecting the real DB unless they explicitly click the update button. Thus, I need some type of hierarchical data structure that can hold the DB structure information in memory, and allow easy modifications, and finally, write it out to an XML and update the original DB structure.

Richard MacCutchan 21-Aug-15 3:32am

It still seems a waste of time. What you are trying to do is already available from the support tools that come with the database software.

[no name] 20-Aug-15 18:56pm

Have you looked at OCCI?

princektd 21-Aug-15 1:43am

@bling: Thank you for the reply.
OCCI is for oracle only. Sorry for my mistake. I also need to support MySQL and MS SQL Server. I will fix the question now.

princektd 21-Aug-15 1:55am

How about an XML document in memory?

something like this:

<Database type="ORACLE">
<Schema name="">
<Tablespace type="SMALLFILE||BIGFILE">
<Datafile name="" size="" reuse="TRUE||FALSE">
<Autoextend value="ON||OFF">
<Next value=""/>
<Maxsize value=""/>
</Autoextend>
</Datafile>
<Tables>
<Table name="">
<Columns>
<column name="">
<int>
<identity/>
</int>
</column>
<column name="">
<nchar length=""/>
</column>
</Columns>
<PrimaryKey>
<key name="" clustered="TRUE||FALSE">
<column name=""/>
</key>
</PrimaryKey>
<UniqueConstraints>
</UniqueConstraints>
<Indexes>
<index name="" clustered="TRUE||FALSE">
<column name="" sortOrder="ASC||DESC"/>
</index>
</Indexes>
<Relationships>
</Relationships>
</Table>
</Tables>
</Tablespace>
</Schema>
</Database>
<

would such an XML object be faster & easier to use than struct or vector<> ?

[no name] 21-Aug-15 15:05pm

I agree with Richard. Fifteen years ago, I'd recommend DBTools.

http://docs.roguewave.com/legacy-hpp/dbtug/index.html

On Windows, you can use ADO, ODBC, OLE DB or ...

http://sourceforge.net/projects/odbclib/

http://www.sqlapi.com/

http://soci.sourceforge.net/

princektd 23-Aug-15 22:05pm

Thanks for the reply. I think maybe I still have not made myself clear. I have already got all that information into a CRecordset using afxdb.h.
that part is allright. Next, I have already displayed the tablespaces, tables and column info to the user using list controls.

The user may make changes to the data in these lists, ie, he might add a new row to the columns list, in effect, he is adding a new column to some table.
or he might delete a row from the tables list. it means he wants to delete a table.

Now, instead of directly executing these commands on the database one by one, what I want is to cache these things, and write it down in some sort of structure. After some thinking, I feel XML might be a good choice. The reason to cache the changes is that
1) reduce waiting time and confirmation prompts from the user (which will be the case if i execute each change directly to the DB)
2) the user might want to rollback some change. Thus, instead of executing the changes, I have to display the changed structure using some list controls and also output it as XML. Then I display a single confirmation prompt. Once confirmed, all the changes will be executed and there will be no rolling back. In effect, upto the final confirmation, the user has a chance to rollback any changes.

Maybe the requirements may sound odd, but I cannot change them.

After deciding to use XML, I have looked at a few MFC C++ xml classes. The msxml looks OK, but I am not sure about the COM things shown in its example.
I also looked at 2 XML classes from codeproject itself:
http://www.codeproject.com/Articles/24492/CXMLFile-A-Simple-C-XML-Parser and
http://www.codeproject.com/Articles/982/XML-class-for-processing-and-building-simple-XML-d

these might work, but they don't seem to have a remove node function (which will be needed in case the user performs a delete operation.)

Do you have any other ideas of any simple xml class? I need to be able to add nodes, change the attribute values, add or remove attributes to selected nodes,
select nodes programmatically (search for a node), and remove nodes.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)