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











Posta un commento



















Post popolari in questo blog









OAuth 2.0 Server & Authorization Middleware for Gin-Gonic





















 Project OAuth  OAuth is an open source project developed in Go and available on GitHub .  The project offers an Authorization Server and an Authorization Middleware that can be used to make secure access to resources API and provide a simple and efficient implementation of the OAuth 2.0 specification.  Authorization Server and Middleware are both based on Gin-Gonic  and can interoperate with any other middleware available for this framework.      Authorization Server   The Authorization Server is implemented by the struct OAuthBearerServer  that manages two grant types of authorizations: password and client_credentials. This Authorization Server is made to provide authorization token usable for consuming resources API.   Developers need only add their own implementation of CredentialsVerifier  interface to validate the credentials of users or applications that require a token.     package main   import (  "errors"  "time"   "gopkg.in/gin-gonic/gin.v1"  &q








Continua a leggere










From the database schema to RESTful API with DinGo




















Immagine







 In the  previous post  I had started writing about data model and code generation and the post coincided with the boot of an open source project called DinGo  thought to generate the Go code for a Microservice host analysing the schema of a relational database.   It's a very SQL First  approach to write code, so I don't think it should be good for all the projects, but surely there are many applications that can take advantage from this, at least those are using already defined relational databases and want to adopt a Microservice architecture and RESTful API.     What DinGo does  DinGo creates a Microservice application starting from a MySQL database schema. Dingo follows these steps during the generation process:   DinGo reads the information_schema  of the MySQL database  it generates a set of Data Model structs used to map tables into Go structs   it generates the Data Access Objects , these objects provide a set of basic operations (CRUD operations) necessary to manipulat








Continua a leggere










Go text and HTML templates





















 Template packages  Server side languages offers a mechanism for inserting a dynamically generated content into static pages. Go provides a mechanism for substituting the value of variables into text or HTML template.    A Go template is string or file containing text (HTML) and  actions , that are string expressions enclosed in double braces {{ ... }} that trigger some behaviors. Actions are powerful notations for selecting and printing fields, expressing control flow and calling functions.   Go standard library offers two package for managing templates:   The packages share the same interface so the following examples that treat HTML can also be applied to simple text .   text/template , implements data-driven templates for generating textual output  html/template , is the same as text/template but automatically secures HTML output against certain attacks (script injection)    Gin-Gonic Gin, the fast and full-featured web framework for Go that we have already seen in the GIF API post








Continua a leggere