Data Model Code Generation

Don't repeat yourself

Often developing applications we find ourselves writing the same code more than once and often this code regards access to data. If the data are stored on the relational database we need to implement the functions that perform the operations of create, read, update and delete (CRUD) and some other utility functions to retrieve a entity by primary key, count the entities in the table and etc.
We can write the same code for each entity or we can write the code generator that will implement it for us. For this reason code generators can be useful to produce the repetitive code that we should implement by hand, thus eliminating the tedious part of writing code.

Querying MySQL Information_Schema

In order to generate the code that accesses the entities we need to know the structure of these entities.
If the data are stored on the MySQL database we can query the "Information Schema" to know the database structure.
Information_schema contains a set of views that report information on the types of entities defined in the database. The complete description of this schema can be found in the official MySQL documentation.

Get the list of Tables and Views

The view Information_schema.TABLES contains all the tables end views defined in all the database of the MySQL server. 
This snippet of code Go extract the list of MySQL tables.

func readTables(conn *sql.DB, schema *model.DatabaseSchema) {
    q := "SELECT TABLE_NAME FROM information_schema.TABLES "
    q += "Where TABLE_SCHEMA=?"
    q += " AND TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME"
    rows, err := conn.Query(q, schema.SchemaName)
    if err != nil {
        log.Fatal(err)
    }
    for rows.Next() {
        table := &model.Table{}
        err := rows.Scan(&table.TableName)
        if err != nil {
            log.Fatal(err)
        }
        schema.Tables = append(schema.Tables, table)
        log.Printf("Examining table %s\r\n", table.TableName)
        readColums(conn, schema, table.TableName, &table.Columns)
        for _, col := range table.Columns {
            if col.IsPrimaryKey {
                table.PrimaryKeys = append(table.PrimaryKeys, col)
            } else {
                table.OtherColumns = append(table.OtherColumns, col)
            }
        }
    }
}


Get the list of Columns

The list of columns in a table can be achieved by querying the view Information_schema.COLUMNS.

func readColums(conn *sql.DB, schema *model.DatabaseSchema, 
                tableName string, colums *[]*model.Column) {
  q := "SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, 
  q += " CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, "
  q += " COLUMN_TYPE, COLUMN_KEY, EXTRA"
  q += " FROM information_schema.COLUMNS "
  q+="WHERE TABLE_SCHEMA=? AND TABLE_NAME=? ORDER BY ORDINAL_POSITION"
  rows, err := conn.Query(q, schema.SchemaName, tableName)
  if err != nil {
      log.Fatal(err)
  }
  for rows.Next() {
      column := &model.Column{}
      nullable := "NO"
      columnKey, extra := "", ""
      err := rows.Scan(&column.ColumnName, &column.ColumnName, 
        &nullable, &column.DataType, 
        &column.CharacterMaximumLength, &column.NumericPrecision, 
        &column.NumericScale, &column.ColumnType, &columnKey, &extra)
      if err != nil {
          log.Fatal(err)
      }
      //log.Printf("Examining column %s\r\n", column.ColumnName)
      if "NO" == nullable {
          column.IsNullable = false
      } else {
          column.IsNullable = true
      }
      if "PRI" == columnKey {
          column.IsPrimaryKey = true
      }
      if "UNI" == columnKey {
          column.IsUnique = true
      }
      if "auto_increment" == extra {
          column.IsAutoIncrement = true
      }
      *colums = append(*colums, column)
  }
}


Create the model structs using template

Before we can generate the code that performs the CRUD operations on entities, we want to produce the code of the structures that will contain the data.
First we define objects that describe the package and structs and we map the database schema on these structs.

   
func ProduceModelPackage(config *model.Configuration, 
        schema *model.DatabaseSchema) (pkg *model.ModelPackage) {    
  pkg = &model.ModelPackage{PackageName: "model", 
               BasePackage: config.BasePackage}
  for _, table := range schema.Tables {
     mt:=&model.ModelType{TypeName: getModelTypeName(table.TableName), 
            PackageName: "model"}
     pkg.ModelTypes = append(pkg.ModelTypes, mt)
     for _, column := range table.Columns {
         field := &model.ModelField{FieldName: getModelFieldName(
                      column.ColumnName), 
                      FieldType: getModelFieldType(pkg, column), 
                      FieldMetadata: getFieldMetadata(pkg, column)}
          if column.IsPrimaryKey {
              field.IsPK = true
              mt.PKFields = append(mt.PKFields, field)
          } else {
              mt.OtherFields = append(mt.OtherFields, field)
          }
          if column.IsAutoIncrement {
              field.IsAutoInc = true
          }
          mt.Fields = append(mt.Fields, field)
      }
  }
  for _, view := range schema.Views {
      mt := &model.ModelType{TypeName: getModelTypeName(view.ViewName), 
                          PackageName: "model"}
      pkg.ViewModelTypes = append(pkg.ViewModelTypes, mt)
      for _, column := range view.Columns {
        field := &model.ModelField{FieldName: getModelFieldName
                 (column.ColumnName),
                 FieldType: getModelFieldType(pkg, column), 
                 FieldMetadata: getFieldMetadata(pkg, column)}
         mt.Fields = append(mt.Fields, field)
      }
  }
  return pkg
}

Now we are using a template to create the structures that contain the data of the tables:

package {{.PackageName}}

{{if .HasImports}}
{{range .ImportPackages}}import "{{.}}"
{{end}}{{end}}
{{range .ModelTypes}}
// Data transfer object for {{.TypeName}}
type {{.TypeName}} struct {
    {{range .Fields}}{{.FieldName}} {{.FieldType}} `{{.FieldMetadata}}`
    {{end}}
}
{{end}}
{{range .ViewModelTypes}}
// Data transfer object for view {{.TypeName}}
type {{.TypeName}} struct {
    {{range .Fields}}{{.FieldName}} {{.FieldType}} `{{.FieldMetadata}}`
    {{end}}
}

{{end}}

 

Next step

Now that we got the structures that are going to contain the data we can proceed to create objects that access data (DAO), but this step will be treated in the next blog post.

All code snippets of this article have been taken from the DinGo project, an open source code generator for Go available on GitHub.





Commenti

Post popolari in questo blog

OAuth 2.0 Server & Authorization Middleware for Gin-Gonic

From the database schema to RESTful API with DinGo

Data Model Generation for PostgreSQL