Generating a model from the Database using AjGenesis

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