Angel \”Java\” Lopez on Blog

November 28, 2009

Generating a model from the Database using AjGenesis

Filed under: AjGenesis, Code Generation, Software Development — ajlopez @ 10:46 pm

AjGenesis, my code generation open source project, use tasks, templates and a free model, to generate text artifacts, usually source code. Most of the examples use models serialized as XML or text files. But you can use anything as the initial input. Even a model could be the output of this process. Month ago, I wrote about generating the model from the database. In my opinion, the database is not the more expressive model to use as starting point, but it is an ubiquitous one (you should admit: I’m improving my English vocabulary…;-)

In my current agile project, the team took the database as the base model. Actually, we are using a more abstract model, but the database was a good asset to use as first model.

So, I back to play with my old example, and improved it. You can download the current example from my Skydrive DatabaseExample01.zip. (the code in flux is in the code repository at Codeplex, under examples\DatabaseExamples).

After download and expand it, you have the folders:

The content of Projects\Northwind\Metadata.xml:

<Metadata>
  <Project>
    <Name>Northwind</Name>
  </Project>
  <Database>
    <Name>Northwind</Name>
    <ConnectionString>server=.\SQLEXPRESS;database=Northwind;Integrated Security=true</ConnectionString>
  </Database>
</Metadata>

It describes the connection string to use to connect to example database. It use Northwind database, in SQL Server (full or express). If you don’t have Northwind, the creation scripts are in Sql folder.

To create the project and entities (the model to generate), run:

MakeModelFromDatabase.cmd

AjGenesis (compiled in Bin folder) begin to work:

The executed commad contains:

Bin\AjGenesis.Console Projects\Northwind\Metadata.xml Tasks\DatabaseProcess.ajg

This command loads Metadata.xml as model in memory, and executes the AjBasic task DatabaseProcess.ajg. This tasks use Information Schema views to obtain information about database structure (using information schema views opens the possibility of database independence).

The task creates Projects\Northwind\Project.xml file:

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
<Project>
  <Name>Northwind</Name>
  <Model>
    <Entities>    
      <Entity Source="Entities/Customer.xml"/>    
      <Entity Source="Entities/Shipper.xml"/>    
      <Entity Source="Entities/Supplier.xml"/>    
      <Entity Source="Entities/Order.xml"/>    
      <Entity Source="Entities/Product.xml"/>    
      <Entity Source="Entities/OrderDetail.xml"/>    
      <Entity Source="Entities/CustomerCustomerDemo.xml"/>    
      <Entity Source="Entities/CustomerDemographic.xml"/>    
      <Entity Source="Entities/Region.xml"/>    
      <Entity Source="Entities/Territory.xml"/>    
      <Entity Source="Entities/EmployeeTerritory.xml"/>    
      <Entity Source="Entities/Employee.xml"/>    
      <Entity Source="Entities/Category.xml"/>
    </Entities>
  </Model>
</Project>

The project and entities are similar to the ones I used in Application generationg using AjGenesis (but there are not the same). Part of the generated file Projects\Northwind\Entities\Customer.xml:

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
<Entity>
  <Name>Customer</Name>
  <Description>Customer</Description>
  <SetName>Customers</Name>
  <Descriptor>Customer</Descriptor>
  <SetDescriptor>Customers</SetDescriptor>
  <SqlCatalog>Northwind</SqlCatalog>
  <SqlSchema>dbo</SqlSchema>
  <SqlName>Customers</SqlName>
  <Properties>
    <Property>
      <Name>CustomerID</Name>
      <Description>CustomerID</Description>
      <SqlName>CustomerID</SqlName>
      <SqlLength>5</SqlLength>
      <IsKey>True</IsKey>
      <SqlType>nchar</SqlType>
      <SystemType>String</SystemType>
      <IsNullable>False</IsNullable>
    </Property>
....
  </Properties>
</Entity>

The main logic resides in Tasks\DatabaseProcess.ajg, written in AjBasic dynamic language, fragment:

cmd = new System.Data.SqlClient.SqlCommand()
cmd.Connection = conn
cmd.CommandText = "select * from Information_Schema.Tables where Table_Type = 'BASE TABLE'"
conn.Open()
PrintLine "Reader"
dr = cmd.ExecuteReader()
Tables = CreateList()
while dr.Read()
  PrintLine "Table " & dr.Item("Table_Name") & ": " & dr.Item("Table_Type")
  Table = CreateObject()
  Table.SqlCatalog = dr.Item("Table_Catalog")
  Table.SqlSchema = dr.Item("Table_Schema")
  Table.SqlName = dr.Item("Table_Name")
  Table.Name = Table.SqlName.Replace(" ","")
  
  if IsPlural(Table.Name) then
    Table.SetName = Table.Name
    Table.Name = ToSingular(Table.Name)
  else
    Table.SetName = ToPlural(Table.Name)
  end if
  
  Table.Descriptor = Table.Name
  Table.SetDescriptor = Table.SetName
  
  Table.Description = Table.Name
  
  Tables.Add(Table)
end while
dr.Close()

Possible improvements

This examples is a “proof of concept”. To be used in a more general way, it should be improved. Some points to work:

- Use the generated model to generate a working application, scaffolding or not.

- Support of more meta data obtained via Information Schema

- Try another databases, try a real example

- Relation treatment: detecting cascade and other actions

Another posts about initial models for AjGenesis:

Another model for AjGenesis

Textual model for code generation in AjGenesis

AjGenesis: Generating the model from assemblies

AjGenesis: Generating the model from the database

Angel “Java” Lopez

http://www.ajlopez.com

http://twitter.com/ajlopez

3 Comments »

  1. [...] wrote about some examples at: Generating a Model From the Database Using AjGenesis and AjGenesis: Generating the Model from the [...]

    Pingback by Models for Code Generation in AjGenesis « Angel “Java” Lopez on Blog — February 27, 2010 @ 12:45 pm

  2. [...] algún ejemplo en: Generating a Model From the Database Using AjGenesis (Generando un modelo desde la base de datos usando AjGenesis) y en AjGenesis: Generating the Model [...]

    Pingback by Modelos para Generación de Código en AjGenesis - Angel "Java" Lopez — March 12, 2010 @ 10:02 am

  3. [...] – Generating the model from the database Generating a Model from the Database using AjGenesis Models for Code Generation in [...]

    Pingback by Code generation with AjGenesis in a Real Project « Angel “Java” Lopez on Blog — May 27, 2010 @ 12:50 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 57 other followers

%d bloggers like this: