My current project is a database for work. To save myself future headaches, I've been sticking to the code it once principle. Specifically, I've been coding table and class information in YAML, then defining templates (using the Template Toolkit, of course) to produce SQL code, Perl modules, and (ta da!) a Makefile.
With lots of Perl glue.
And now I've got the biggest headache of them all.
Moral: trying to save myself headaches later is pointless if my brain explodes in the process!
Update: Happily, the headache is quickly fading. I added a target in my makefile spec to produce a (longish) SQL command file that populates the database in one transaction, and the resulting file is looking good. It's not running to completion, because my data has a few hiccups in it that my data preparation scripts didn't catch, but that's easy enough to fix.
Moral #2: When trying to save yourself headaches later, don't try to save them all at once. One aspirin at a time, my friend!
If it is a mess, at least you'll have learned a lot about just how far to go with this kind of thing.
Sure, your brain may hurt now, but no pain, no gain. On the other hand, you have failed to save yourself headaches...
Look at it this way, if it is a failure, and your brain does explode, at least it will be a spectacular failure. I'd rather be a spectacular failure than a boring success!
(when did I start sounding like Stuart Smalley?)
Re:Stick it out...
nkuitse on 2003-06-27T23:31:34
It actually sounds pretty cool.
Thanks for the encouraging words. My brain is much better now. Lying in the grass with my eyes closed helped a lot.:-)
I wouldn't say it's a mess, just big and I tend to work on several parts of it at once. When I'm in the groove this can work very well (until my brain conks out); at other times, it results in a certain amount of wheel-spinning...
Does this allow you to describe a table structure and dump out SQL statements to create it in various databases? That'd surely be useful!
Re:CREATE TABLE abstraction?
autarch on 2003-06-27T23:26:47
Gee, you mean like Alzabo?Re:CREATE TABLE abstraction?
chromatic on 2003-06-27T23:42:01
Now that you mention it, Alzabo::Create::Schema looks extremely useful. Darn you for making my life easier yet again!
Re:CREATE TABLE abstraction?
nkuitse on 2003-06-27T23:27:44
Does this allow you to describe a table structure and dump out SQL statements to create it in various databases?
Maybe. I'm only targeting PosgretSQL at the moment, but it should be easy to target other RDBMSes too, as this would mostly* entail making a few new SQL templates.
* There are bound to be some PostgreSQLisms outside of the templates; hopefully, not many.
Here's a much-abridged example of a table definition:
tables/Orders/definition.ymlThe singular element is used to form the name of the class whose instances represent rows from the table. The reverse stuff will result in a method Snoof::Object::Book::orders that returns a list of orders whose book_ids match the id of the book in question. (Snoof is the name of the project.)--- #YAML:1.0
name: Orders
singular: Order
description: Book orders placed for the library
type: data
null: '--undef--'
sequences:
- name: &1 order_id_seq
columns:
- name: id
type: int
description: The unique ID for this order
pkey: 1
unique: 1
nullable: 0
sequence: *1
- name: book_id
type: int
description: The book we're ordering
nullable: 0
foreign_key:
table: Books
column: id
reverse:
name: orders
multiple: 1
on_delete: CASCADE
on_update: CASCADE
- name: type_id
type: int
description: The type of order (one-shot, blanket, etc.)
nullable: 0
foreign_key:
table: OrderTypes
column: id
on_delete: CASCADE
on_update: CASCADE
indexes:
- name: order_book_idx
over: book_id
unique: 1
- name: order_type_idx
over: type_id
unique: 0
The Makefile is created from the table definitions and a makefile spec, which I won't list here. It was a pain to set up but much easier than writing and maintaining a (very long and repetitive) Makefile manually would have been!
All SQL code is created from the table definitions using a template for each statement (CREATE TABLE, SELECT, UPDATE, etc.; I haven't written them all yet). Here's an abridged example (partly outdated because I broke out the column constraints into individual elements):
tt2/create.sql.tt2The Perl modules implementing the business objects (Books, Orders, etc.) are also generated from templates.[% FOREACH seq = table.sequences -%]
CREATE SEQUENCE [% seq.name %];
[% END -%]
CREATE TABLE [% table.name %] (
[% FOREACH col = table.columns %]
/*
[% col.description %]
*/
[% col.name %]
[% col.type %][% IF col.constraints %] [% col.constraints.join(' ') %][% END -%]
[%- UNLESS loop.last %],[% END %]
[% END %]
);
[% FOREACH index = table.indexes -%]
CREATE [% GET 'UNIQUE ' IF index.unique %]INDEX [% index.name %]
ON [% table.name %] ( [% index.over.join(', ') %] );
[% END -%]
Later, I may generate HTML docs from the table definitions and (you guessed it!) some more templates. That way 6 months from now when I've totally forgotten the design, I won't have to plow through source code and make my head explode again.:-)
For good measure, the data files (created from an earlier project) are kept in YAML and used to create tab-delimited files suitable for importing via SQL COPY. No templates involved (whew!), just some simple Perl code (plus the table definitions).Re:CREATE TABLE abstraction?
TeeJay on 2003-07-04T11:48:02
You could probably try and use some of the code from tedia2sql - it generates loads of code for oracle, postgres, etc - most of the big databases.And you can create it all from dia which is nice - should be easy to create your other stuff from dia too.
I am currently working on a new project to combine schema and code creation so that you can create your databases, your queries (all kept in a nice phrasebook natch), and documentation from a couple of perl scripts.
Re:CREATE TABLE abstraction?
nkuitse on 2003-07-05T02:41:24
Thanks for the tip. I've gotten over the hump now, I think, so I probably won't use tedia2sql, but it's always good to know about the alternatives...
