External library or mini-language for reading database schema?

Question

I am considering writing a simple database application for my wife, but I'm hung up on good programming practice, and want to have a human readable source for storing the database schema.

Only, I don't know of any tools for the job, and I can't believe that they don't exist---probably I just don't know what to ask google, but I'm not finding them.

So, what libraries or other tools out there to support reading database schema from plain text files.

---

To invenetix's question: I expect to generate the database creation code at build-time or to include the schema description in the release and build the database from the description at run-time. Draemon's suggestion would be fine, but I don't want to be locked into a predetermined RDBMS if I can help it.

Perhaps I should say that I've used databases in programming projects before, but never been responsible for the creation code.

Possible I'm over engineering the problem at this point.

Answer 1

Just use SQL. SQL is not only a query language but a DDL (data definition language). If, for example you are using mysql; you can design your schema however you want, run mysqldump which will create an SQL file with DDL statements. Tidy up the schema, then you can load the schema any time you like by sourcing the SQL file. Personally, I just write the SQL schema in a text editor and load it into mysql.

This will work for mysql, postgresql, oracle, and probably any other DB.

Answer 2

I'm unable to give comments yet, but if I understand this right, you want an application or library to build a schema dependent on the data provided in the text file?

Could you provide a sample work flow in which you're seeking to accomplish? Which step is the one you're having the issue with?

Answer 3

I think I understand a bit better now.

Eventhough each SQL solution on the market should adhere to some level of the ANSI SQL standards compliance, they'll all provide some sort of custom data type that might not be shared/used/recognized in another SQL server solution.

Like for example, in MySQL 4 you'd have quite a selection to use (DATE, DATETIME, TIME, YEAR, and TIMESTAMP) whereas in MS SQL 2000, you'd only have a couple (DATETIME or SMALLDATETIME). You'd have to ensure which ever schema you'd end up generating is common between the solutions you're willing to support.

MySQL Data Types

MS SQL 2000 Data Types

Does this help?

Answer 4

SQLite doesn't keep its data in text format, but it will probably do what you like.

It's perfect for an embedded database -- there are no servers, no setup, and the database is in a single file that can be managed by your application.

http://www.sqlite.org/

Answer 5

You may want to check out http://www.codesmithtools.com/ . The tool is designed to read the schema of a database and allow you to create code templates based on that schema. It has an abstract library for accessing schema data.