Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • 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!

    • 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.yml
      --- #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 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.)

      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.tt2
      [% 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 -%]
      The Perl modules implementing the business objects (Books, Orders, etc.) are also generated from templates.

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

        --

        @JAPH = qw(Hacker Perl Another Just);
        print reverse @JAPH;