generate-database

generate-database

Introduction

generate-database is a database statement and associated go function generator for Incus and related projects. generate-database utilizes go's code generation directives (//go:generate ...) alongside go's ast and types packages for parsing the syntax tree for go structs and variables. We use generate-database for the majority of our SQL statements and database interactions on the go side for consistency and predictability.

Disclaimer

generate-database is intended for internal use within the Incus code base. There are no guarantees regarding backwards compatibility, API stability, or long-term availability. It may change or be removed at any time without prior notice. Use at your own discretion.

Usage

Initialization

Package global

Once per package, that uses generate-database for generation of database statements and associated go functions, generate-database needs to be invoked using the following go:generate instruction:

//go:generate generate-database db mapper generate

This will initiate a call to generate-database db mapper generate, which will then search for //generate-database:mapper directives in the same file and process those.

The following flags are available:

• --package / -p: Package import paths to search for structs to parse. Defaults to the caller package. Can be used more than once.

File

Generally the first thing we will want to do for any newly generated file is to ensure the file has been cleared of content:

//generate-database:mapper target instances.mapper.go
//generate-database:mapper reset -i -b "//go:build linux && cgo && !agent"

Generation Directive Arguments

The generation directive arguments have the following form:

//generate-database:mapper <command> flags <kind> <args...>

The following flags are available:

• --build / -b: build comment to include (commands: reset)
• --interface / -i: create interface files (commands: reset, method)
• --entity / -e: database entity to generate the method or statement for (commands: stmt, method)

Example:

• //generate-database:mapper stmt -e instance objects table=table_name

The table key can be used to override the generated table name for a specified one.

• //generate-database:mapper method -i -e instance Create references=Config,Device

For some tables (defined below under Additional Information as EntityTable, the references=<ReferenceEntity> key can be provided with the name of a ReferenceTable or MapTable struct. This directive would produce CreateInstance in addition to CreateInstanceConfig and CreateInstanceDevices:

• //generate-database:mapper method -i -e instance_profile Create struct=Instance
• //generate-database:mapper method -i -e instance_profile Create struct=Profile

For some tables (defined below under Additional Information as AssociationTable, method declarations must include a struct=<Entity> to indicate the directionality of the function. An invocation can be called for each direction. This would produce CreateInstanceProfiles and CreateProfileInstances respectively.

SQL Statement Generation

SQL generation supports the following SQL statement types:

Examples

//generate-database:mapper stmt -e instance objects
//generate-database:mapper stmt -e instance objects-by-Name-and-Project
//generate-database:mapper stmt -e instance create
//generate-database:mapper stmt -e instance update
//generate-database:mapper stmt -e instance delete-by-Name-and-Project

Statement Related Go Tags

There are several tags that can be added to fields of a struct that will be parsed by the ast package.

Go Function Generation

Go function generation supports the following types:

//generate-database:mapper method -i -e instance GetMany
//generate-database:mapper method -i -e instance GetOne
//generate-database:mapper method -i -e instance ID
//generate-database:mapper method -i -e instance Exist
//generate-database:mapper method -i -e instance Create
//generate-database:mapper method -i -e instance Update
//generate-database:mapper method -i -e instance DeleteOne-by-Project-and-Name
//generate-database:mapper method -i -e instance DeleteMany-by-Name

Additional Information

All structs should have an ID field, as well as an additional Filter struct prefixed with the original struct name. This should include any fields that should be considered for filtering in WHERE clauses. These fields should be pointers to facilitate omission and inclusion without setting default values.

Example:

type Instance struct {
  ID int
  Name string
  Project string
}

type InstanceFilter struct {
  Name *string
  Project *string
}

generate-database will handle parsing of structs differently based on the composition of the struct in four different ways.

Non-EntityType structs will only support GetMany, Create, Update, and Delete functions.

EntityTable

Most structs will get treated this way, and represent a normal table.

• If a table has an associated table for which a ReferenceTable or MapTable as defined below is applicable, functions specific to this entity can be generated by including a comma separated list to references=<OtherEntity> in the code generation directive for GetMany, Create, or Update directives.

• The Create method directive for EntityTable will expect on the ID and Exist method directives to be present.

• All CREATE, UPDATE, and DELETE statements that include a joined table will expect a var <entity>ID = RegisterStmt('SQL String') to exist for the joining table.

ReferenceTable

A struct that contains a field named ReferenceID will be parsed this way. generate-database will use this struct to generate more abstract SQL statements and functions of the form <parent_table>_<this_table>.

Real world invocation of these statements and functions should be done through an EntityTable method call with the tag references=<ThisStruct>. This EntityTable will replace the <parent_table> above.

Example:

//generate-database:mapper stmt -e device create
//generate-database:mapper method -e device Create

type Device struct {
  ID int
  ReferenceID int
  Name string
  Type string
}

//...
//generate-database:mapper method -e instance Create references=Device
// This will produce a function called `CreateInstanceDevices`.

MapTable

This is a special type of ReferenceTable with fields named Key and Value. On the SQL side, this is treated exactly like a ReferenceTable, but on the go side, the return values will be a map.

Example:

//generate-database:mapper stmt -e config create
//generate-database:mapper method -e config Create

type Config struct {
  ID int
  ReferenceID int
  Key string
  Value string
}

//...
//generate-database:mapper method -e instance Create references=Config
// This will produce a function called `CreateInstanceConfig`, which will return a `map[string]string`.

AssociationTable

This is a special type of table that contains two fields of the form <Entity>ID, where <Entity> corresponds to two other structs present in the same package. This will generate code for compound tables of the form <entity1>_<entity2> that are generally used to associate two tables together by their IDs.

method generation declarations for these statements should include a struct=<Entity> to indicate the directionality of the function. An invocation can be called for each direction.

Example:

//generate-database:mapper method -i -e instance_profile Create struct=Instance
//generate-database:mapper method -i -e instance_profile Create struct=Profile

type InstanceProfile struct {
  InstanceID int
  ProfileID int
}