AjGenesis: Generating the model from the database

My code generation project AjGenesis uses one or many user-defined models to generate text artifacts, using programmable tasks and templates. When I started the project, I read the Kathleen Dollard‘s book Code Generation in Microsoft .NET

One of her recommendations is take metatadata from some resource, like a database. In my opinion, not all needed metadata resides in a database schema, so, from the very beginning of the project, I follow the way of a free user-defined model (I quickly go away from the Dollard’s ideas of using XSLT transformations: I think XSLT is the devil’s work… don’t try it at your home..;-). There are many interesting points her ideas, I should write a post about her book.

But metadata from database is not discarded. It can be used, applying some work. Remember: code generation is a pragmatic solution, don’t ignore useful metadata source.

So, some months ago, I wrote a simple example inside the ones that come with AjGenesis. You can try the example, downloading the last published version of the project:

AjGenesis Versión 0.5

The example is located at examples\LoadDB directory.

It works using the Northwind database that comes as example for MS SQL Server. In the directory example, from the command line, execute

make

This command executes an AjGenesis task:

..\..\bin\AjGenesis.Console Tasks\DbProcess.ajg
pause

It connects with database:

PrintLine "Connection" AssemblyManager.LoadWithPartialName("System.Data") con = new System.Data.SqlClient.SqlConnection() PrintLine "ConnectionString" con.ConnectionString = "server=(local);uid=sa;database=Northwind" PrintLine "Command" cmd = new System.Data.SqlClient.SqlCommand() cmd.Connection = con cmd.CommandText = "select * from Information_Schema.Tables" con.Open()

This code uses the information inside Information_Schema.Tables. Take notice of the use of the new AssemblyManager, an utility in AjGenesis that loads assemblies.

Then, the code obtains the table names:

dr = cmd.ExecuteReader() TableNames = new System.Collections.ArrayList() while dr.Read() PrintLine "Table " & dr.Item("Table_Name") & ": " & dr.Item("Table_Type") TableName = dr.Item("Table_Name") if dr.Item("Table_Type")="BASE TABLE" then TableNames.Add(TableName) end if end while dr.Close()

Once this info was obtained, it gets the columns of the tables, making an in-memory model in a Tables collection:

Tables = new System.Collections.ArrayList() For each TableName in TableNames PrintLine "Loading Table " & TableName Table = new AjGenesis.Models.DynamicModel.DynamicObject() Table.Name = TableName Table.Columns = new System.Collections.ArrayList() cmd.CommandText = "SELECT * from Information_Schema.Columns where Table_name='" & TableName & "'" dr = cmd.ExecuteReader() while dr.Read() ColumnName = dr.Item("Column_Name") PrintLine "Processing Column " & ColumnName & ": " & dr.Item("Data_Type") Column = new AjGenesis.Models.DynamicModel.DynamicObject() Column.Name = ColumnName Column.DataType = dr.Item("Data_Type") if dr.Item("Character_Maximum_Length") then Column.Length = dr.Item("Character_Maximum_Length") Column.IsNumeric = false else if dr.Item("Numeric_Precision") then Column.Length = dr.Item("Numeric_Precision") Column.Scale = dr.Item("Numeric_Scale") Column.IsNumeric = true end if end if Column.SqlName = Column.Name Column.Name = Column.Name.Replace(" ","") Table.Columns.Add(Column) end while dr.Close() Table.SqlName = Table.Name Table.Name = Table.Name.Replace(" ","") Tables.Add(Table) end for

Finally, I use the old trick of visiting the model, applying a template to it to generate other model. This is an interesting idea that deserves more attention: multi-step model generation and transformation. I could inject some expert system to complete the model, and infers any missing part. The model can be a in-memory one, or it can saved in an XML file, to be adapted and improved:

FileManager.CreateDirectory("Model") for each Table in Tables TransformerManager.Transform("Templates\Table.tpl", "Model\${Table.Name}.xml", Environment) end for

A generated model as example:

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?> <Table> <Name>Region</Name> <SqlName>Region</SqlName> <Columns> <Column> <Name>RegionID</Name> <SqlName>RegionID</SqlName> <DataType>int</DataType> <Length></Length> <Scale></Scale> </Column> <Column> <Name>RegionDescription</Name> <SqlName>RegionDescription</SqlName> <DataType>nchar</DataType> <Length>50</Length> <Scale></Scale> </Column> </Columns> </Table>

Now, using AssemblyManager, we can load any .dll assembly, and use it to generate any code. Darío Quintana works with this feature to generate code from compiled code (post in Spanish Generando con AjGenesis desde los assemblies). You can download his utility from

http://code.google.com/p/darioquintana/downloads/detail?name=AjGenesis.FromAssembly.7z

One idea: we can use the Meta library from MyGenerationSoftware to obtain metadata from any supported database. Notably, now this utility has published source code.

Angel “Java” Lopez
http://www.ajlopez.com/en

6 thoughts on “AjGenesis: Generating the model from the database

  1. Pingback: AjGenesis: Generating the model from assemblies « Angel “Java” Lopez on Blog

  2. Pingback: Code Generation for Mere Mortals Framework « Angel “Java” Lopez on Blog

  3. Pingback: Generación de código con AjGenesis para Mere Mortals Framework - Angel "Java" Lopez

  4. Pingback: Generating a model from the Database using AjGenesis « Angel “Java” Lopez on Blog

  5. Pingback: Models for Code Generation in AjGenesis « Angel “Java” Lopez on Blog

  6. Pingback: Code generation with AjGenesis in a Real Project « Angel “Java” Lopez on Blog

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s