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"...








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 operatio...








Continua a leggere










OVO Key/Value Storage





















  Yet another Key/Value Storage ?  I started developing OVO , my own implementation of a Key/Value Storage, six month ago because I wanted to test  developing a project  using  the  Go language. In the past , I had already  worked  on a similar project , GetCache , but  it is developed  with  .Net framework .  I don't think  that  the world  lacks  the  Key / Value  Storage , but  for me  it  is treated  to  a great gym  to  train my  Go . I have r ealized that this project with the Go language is extraordinarily simple and so I would like to share this experience.     What is OVO  Initially, the project  aimed to  create  a distributed cache , like  Memcached , that would allow to store  objects  and   JSON   structures  quickly  by spreading the load  across multiple servers , and could  run on any  kind of  Linux , Windows , iOS machine . Each  cached object  can have  a time-to-live property ( TTL)  set  so  that  the removal  is done  automaticall...








Continua a leggere