godbi

package
v1.4.2 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Dec 4, 2025 License: LGPL-2.1 Imports: 18 Imported by: 0

README

godbi, the main package of Genelet Molecule

molecule acts on whole database across all tables in database:

  • a database table and its associated actions build up an atom.
  • relationship between tables, usually described in JSON, consists of logic operator, data filter, action trigger or SQL foreign-key constraint etc.
  • atoms and relationship build up a molecule.
  • we can run REST actions on single table, and complex database tasks like those in gRPC and GraphQL across multiple related tables in molecule.

For example, assuming we have created a Postgres database to represent gRPC protocol buffer, in which tables represent sub messages and relationships between sub messages etc. (for purposes such as real-time statistics of sub messages). When a protobuf message is received in gRPC call, we can use molecule to write it to multiple tables. Or retrieve the original protobuf message back by single read.

Beneath the surface, molecule will handle detailed reads and writes with given logic.

Check godoc for package details:

GoDoc

This package is fully tested for PostgreSQL, MySQL and SQLite.

Please also check the READMEs for two sub packages:
  • To auto generate a simple molecule from database: rdb. This is a good starting point for first-time users.
  • Instead of JSON, Molecule's meta could be prepresented by protobuf: gometa



Chapter 1. INSTALLATION

To install:

$ go get -u github.com/genelet/molecule

To use molecule, in your GO program:

import ("github.com/genelet/molecule/godbi")

You may also encode and process molecule in protocol buffer, which we have named Graph. To use Graph:

import ("github.com/genelet/molecule/gometa")
1.1) Termilogy
  • table: a database table;
  • action: a SELECT or DO database action;
  • atom: a table with actions (or node in graph);
  • connection: a relationship between atoms (or edge);
  • molecule: whole set of atoms which act with each other in relationship (or graph);
  • RDB: relationational database system.
1.2) Arguments

The following names in functions are defined to be:

Name Type IN/OUT Meaning
args ...any IN arguments
ARGS map[string]any IN input data
extra ...map[string]any IN WHERE constraints
lists []map[string]any OUT output data
1.3) Three Levels of Usages:
  • Basic Usage: run raw SQL statements using DBI struct;
  • Atom Usage: run actions on single table; 6 RESTful actions are pre-defined in this package;
  • Molecule Usage: run GraphQL/gRPC actions of multiple relational atoms.



Chapter 2. BASIC USAGE

In this example, we create table letters with 3 rows, then search and put the data into lists.

Click for Basic Usage Sample 

package main

import (
    "database/sql"
    "fmt"
    "os"

    "github.com/genelet/molecule/godbi"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    dbUser := os.Getenv("DBUSER")
    dbPass := os.Getenv("DBPASS")
    dbName := os.Getenv("DBNAME")
    db, err := sql.Open("mysql", dbUser + ":" + dbPass + "@/" + dbName)
    if err != nil { panic(err) }
    defer db.Close()

    dbi := &godbi.DBI{DB:db}

    // create a new table and insert 3 rows
    //
    _, err = db.Exec(`CREATE TABLE letters (
        id int auto_increment primary key,
        x varchar(1))`)
    if err != nil { panic(err) }
    _, err = db.Exec(`insert into letters (x) values ('m')`)
    if err != nil { panic(err) }
    _, err = db.Exec(`insert into letters (x) values ('n')`)
    if err != nil { panic(err) }
    _, err = db.Exec(`insert into letters (x) values ('p')`)
    if err != nil { panic(err) }

    // select all data from the table using Select
    //
    lists := make([]any, 0)
    err = dbi.Select(&lists, "SELECT id, x FROM letters")
    if err != nil { panic(err) }

    fmt.Printf("%v\n", lists)

    dbi.Exec(`DROP TABLE IF EXISTS letters`)
    db.Close()
}

Running it will output

[map[id:1 x:m] map[id:2 x:n] map[id:3 x:p]]

Here are frequently used functions in the DBI usage. For detailed list, please check the document.

2.1) DBI

The DBI type embeds the standard SQL handle. The extra LastID field is used to stored auto-generated series number, if the database supports it.

package molecule

type DBI struct {
    *sql.DB          
}
2.2) DoSQL

The same as DB's Exec, except it returns error.

func (*DBI) DoSQL(query string, args ...any) (sql.Result, error)
2.3) TxSQL

The same as DoSQL but using transaction.

func (*DBI) TxSQL(query string, args ...any) (sql.Result, error)
2.4) Select

Return query data into lists, with data types determined dynamically.

Select(lists *[]any, query string, args ...any) error
2.5) SelectSQL

The same as Select but using pre-defined labels and types.

func (*DBI) SelectSQL(lists *[]map[string]any, labels []any, query string, args ...any) error
Click for Example

The following example assigns key names TS, id, Name, Length, Flag and fv, of data types string, int, string, int8, bool and float32, to the returned rows:

lists := make([]map[string]any)
err = molecule.SelectSQL(
    &lists, 
    `SELECT ts, id, name, len, flag, fv FROM mytable WHERE id=?`,
    []any{[2]string{"TS","string"], [2]string{"id","int"], [2]string{"Name","string"], [2]string{"Length","int8"], [2]string{"Flag","bool"], [2]string{"fv","float32"]},
    1234)

It outputs:

    {"TS":"2019-12-15 01:01:01", "id":1234, "Name":"company", "Length":30, "Flag":true, "fv":789.123},
2.6) GetSQL

If there is only one row returned, use this function to get a map.

func (*DBI) GetSQL(res map[string]any, query string, labels []any, args ...any) error



Chapter 3. ATOM USAGE

In the following example, we define table, columns and actions in JSON, and run REST actions on the table.

Click for Atom Usage Sample: Default and Customized Actions 
package main

import (
    "context"
    "database/sql"
    "fmt"
    "os"

    "github.com/genelet/molecule/godbi"
    _ "github.com/go-sql-driver/mysql"
)

type SQL struct {
    godbi.Action
    Statement string   `json:"statement"`
}

func (s *SQL) RunAction(db *sql.DB, t *godbi.Table, ARGS map[string]any, extra ...map[string]any) ([]any, error) {
    return s.RunActionContext(context.Background(), db, t, ARGS, extra...)
}

func (s *SQL) RunActionContext(ctx context.Context, db *sql.DB, t *godbi.Table, ARGS map[string]any, extra ...map[string]any) ([]any, error) {
    lists := make([]any, 0)
    dbi := &godbi.DBI{DB: db}
    err := dbi.SelectContext(ctx, &lists, s.Statement, ARGS["marker"])
    return lists, err
}

func main() {
    dbUser := os.Getenv("DBUSER")
    dbPass := os.Getenv("DBPASS")
    dbName := os.Getenv("DBNAME")
    db, err := sql.Open("mysql", dbUser + ":" + dbPass + "@/" + dbName)
    if err != nil { panic(err) }
    defer db.Close()

    db.Exec(`CREATE TABLE testing (
        id int auto_increment,
        x varchar(255),
        y varchar(255),
        z varchar(255),
        primary key (id))`)
    table := &godbi.Table{
        TableName: "testing",
        Pks:[]string{"id"},
        IDAuto:"id",
        Columns: []*godbi.Col{
            &godbi.Col{ColumnName:"x",  Label:"x",  TypeName:"string", Notnull:true},
            &godbi.Col{ColumnName:"y",  Label:"y",  TypeName:"string", Notnull:true},
            &godbi.Col{ColumnName:"z",  Label:"z",  TypeName:"string"},
            &godbi.Col{ColumnName:"id", Label:"id", TypeName:"int", Auto:true},
        },
    }

    insert := &godbi.Insert{Action:godbi.Action{ActionName: "insert"}}
    topics := &godbi.Topics{Action:godbi.Action{ActionName: "topics"}}
    update := &godbi.Update{Action:godbi.Action{ActionName: "update"}}
    edit   := &godbi.Edit{Action:godbi.Action{ActionName: "edit"}}
    // a custom action
    sql   := &SQL{Action:godbi.Action{ActionName: "sql"}, Statement:"SELECT z FROM testing WHERE id=?"}

    args := map[string]any{"x":"a","y":"b","z":"c"}
    lists, err := insert.RunAction(db, table, args)
    if err != nil { panic(err) }
    fmt.Printf("Step 1: %v\n", lists)

    args = map[string]any{"x":"c","y":"d","z":"c"}
    lists, err = insert.RunAction(db, table, args)
    if err != nil { panic(err) }
    fmt.Printf("Step 2: %v\n", lists)

    lists, err = topics.RunAction(db, table, nil)
    fmt.Printf("Step 3: %v\n", lists)

    args = map[string]any{"id":2,"x":"c","y":"z"}
    lists, err = update.RunAction(db, table, args)
    if err != nil { panic(err) }
    fmt.Printf("Step 4: %v\n", lists)

    args = map[string]any{"id":2}
    lists, err = edit.RunAction(db, table, args)
    fmt.Printf("Step 5: %v\n", lists)

    args = map[string]any{"marker":1}
    lists, err = sql.RunAction(db, table, args)
    fmt.Printf("Step 6: %v\n", lists)

    db.Exec(`DROP TABLE IF EXISTS testing`)
    os.Exit(0)
}

Running the program will result in

Step 1: [map[id:1 x:a y:b z:c]]
Step 2: [map[id:2 x:c y:d z:c]]
Step 3: [map[id:1 x:a y:b z:c] map[id:2 x:c y:d z:c]]
Step 4: [map[x:c y:z]]
Step 5: [map[id:2 x:c y:z z:c]]
Step 6: [map[z:c]]
Click for Atom Usage Sample: Atom 
package main

import (
    "database/sql"
    "fmt"
    "os"

    "github.com/genelet/molecule/godbi"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    dbUser := os.Getenv("DBUSER")
    dbPass := os.Getenv("DBPASS")
    dbName := os.Getenv("DBNAME")
    db, err := sql.Open("mysql", dbUser + ":" + dbPass + "@/" + dbName)
    if err != nil { panic(err) }
    defer db.Close()

    db.Exec(`CREATE TABLE m_a (
        id int auto_increment not null primary key,
        x varchar(8), y varchar(8), z varchar(8))`)

    str := `{
    "tableName":"m_a",
    "pks":["id"],
    "idAuto":"id",
    "columns": [
        {"columnName":"x", "label":"x", "typeName":"string", "notnull":true },
        {"columnName":"y", "label":"y", "typeName":"string", "notnull":true },
        {"columnName":"z", "label":"z", "typeName":"string" },
        {"columnName":"id", "label":"id", "typeName":"int", "auto":true }
    ],
    "uniques":["x","y"],
    "actions": [
        { "actionName": "insert" },
        { "actionName": "insupd" },
        { "actionName": "delete" },
        { "actionName": "topics" },
        { "actionName": "edit" }
    ]}`
    atom, err := godbi.NewAtomJson([]byte(str))
    if err != nil { panic(err) }

    var lists []any
    // the 1st web requests is assumed to create id=1 to the m_a table
    //
    args := map[string]any{"x": "a1234567", "y": "b1234567", "z": "temp"}
    lists, err = atom.RunAtom(db, "insert", args)
    if err != nil { panic(err) }

    // the 2nd request just updates, becaues [x,y] is defined to the unique
    //
    args = map[string]any{"x": "a1234567", "y": "b1234567", "z": "zzzzz"}
    lists, err = atom.RunAtom(db, "insupd", args)
    if err != nil { panic(err) }

    // the 3rd request creates id=2
    //
    args = map[string]any{"x": "c1234567", "y": "d1234567", "z": "e1234"}
    lists, err = atom.RunAtom(db, "insert", args)
    if err != nil { panic(err) }

    // the 4th request creates id=3
    //
    args = map[string]any{"x": "e1234567", "y": "f1234567", "z": "e1234"}
    lists, err = atom.RunAtom(db, "insupd", args)
    if err != nil { panic(err) }

    // GET all
    args = map[string]any{}
    lists, err = atom.RunAtom(db, "topics", args)
    if err != nil { panic(err) }
    fmt.Printf("Step 1: %v\n", lists)

    // GET one
    args = map[string]any{"id": 1}
    lists, err = atom.RunAtom(db, "edit", args)
    if err != nil { panic(err) }
    fmt.Printf("Step 2: %v\n", lists)

    // DELETE
    args = map[string]any{"id": 1}
    lists, err = atom.RunAtom(db, "delete", args)
    if err != nil { panic(err) }

    // GET all
    args = map[string]any{}
    lists, err = atom.RunAtom(db, "topics", args)
    if err != nil { panic(err) }
    fmt.Printf("Step 3: %v\n", lists)

    db.Exec(`drop table if exists m_a`)
}

Running the program will result in

Step 1: [map[id:1 x:a1234567 y:b1234567 z:zzzzz] map[id:2 x:c1234567 y:d1234567 z:e1234] map[id:3 x:e1234567 y:f1234567 z:e1234]]
Step 2: [map[id:1 x:a1234567 y:b1234567 z:zzzzz]]
Step 3: [map[id:2 x:c1234567 y:d1234567 z:e1234] map[id:3 x:e1234567 y:f1234567 z:e1234]]

Here are frequently used data types and functions in the atom usage.


3.1) Col

Define a table column type:

type Col struct {
    ColumnName string  `json:"columnName" hcl:"columnName"`
    TypeName string    `json:"typeName" hcl:"typeName"`
    Label string       `json:"label" hcl:"label"`
    Notnull bool       `json:"notnull" hcl:"notnull"`
    Auto bool          `json:"auto" hcl:"auto"`
    // true for a one-to-may recurse column
    Recurse bool       `json:"recurse,omitempty" hcl:"recurse,optional"`
}

where ColumnName is the column name. TypeName is column's type. Lable is the label for the column. Notnull marks if the column can't be null. Auto means if the column can be automatically assigned with value e.g. timestamp, auto id etc. And Recurse means it recursively references to table's primary key in one-to-many relation.

3.2) Fk

SQL's foreign key. It's a relationship between 2 atoms:

type Fk struct {
    FkTable  string    `json:"fkTable" hcl:"fkTable"`
    FkColumn string    `json:"fkColumn" hcl:"fkColumn"`
    Column   string    `json:"column" hcl:"column"`
}

where FkTable means a foreign table, FkColumn foreign table's column, and Column the column in the current table.

  • Foreign key is defined to be a single column in this package.
  • Foreign key can be defined even if there is no native SQL foreign key, like NoSQL or time-series database.
  • Foreign key is only used in action Delecs.
3.3) Table

Table describes a database table.

type Table struct {
    TableName string   `json:"tableName" hcl:"tableName"`
    Columns   []*Col   `json:"columns" hcl:"columns"`
    Pks       []string `json:"pks,omitempty" hcl:"pks,optional"`
    IDAuto    string   `json:"idAuto,omitempty" hcl:"idAuto,optional"`
    Fks       []*Fk    `json:"fks,omitempty" hcl:"fks,optional"`
    Uniques   []string `json:"uniques,omitempty" hcl:"uniques,optional"`
}

where TableName is the table name. Columns are all columns. Pks is the primary key. IDAuto is the auto ID. Fks is a list of foreign-key relationships. And Uniques is the combination of columns uniquely defining the row.

3.4) Connection

connection is associated with specific table. It defines relationship to another table for how data are passed under a specific action.

type Connection struct {
    TableName   string            `json:"tableName" hcl:"tableName,label"`
    ActionName  string            `json:"actionName" hcl:"actionName,label"`
    RelateArgs  map[string]string `json:"relateArgs,omitempty" hcl:"relateArgs"`
    RelateExtra map[string]string `json:"relateExtra,omitempty" hcl:"relateExtra"`
    Dimension  ConnectType        `json:"dimension,omitempty" hcl:"dimension,label"`
    Marker     string             `json:"marker,omitempty" hcl:"marker,label"`
}

where TableName is the database table name. ActionName is the action name. RelateArgs is a filter, that maps an output data from this table to the input data of the next table. RelateExtra is for where constraint. Dimension is a relation type. And Marker is a string marker. For an input action like insert and insupd, Marker will be used as key to reference row data; and for an output action like topics, it stores the row under the marker.

3.5) Action

Action defines an action, such as CRUD, on a table. It implements interface Capability.

type Action struct {
    ActionName string `json:"actionName,omitempty" hcl:"actionName,optional"`
    Prepares  []*Connection `json:"prepares,omitempty" hcl:"prepares,block"`
    Nextpages []*Connection `json:"nextpages,omitempty" hcl:"nextpages,block"`
    IsDo      bool          `json:"-" hcl:"-"`
}

where Prepares is a list of actions to run before the current action, and Nextpages actions to follow. We can think Prepares as pre triggers and Nextpages as post triggers in standard SQL. IsDo indicates if it is associated with SQL's DO query.

3.6) RunActionContext

This is the main function in Capability. It takes input data ARGS and optional constraint extra, and run. The output is a slice of interface, and an optional error.

RunActionContext(ctx context.Context, db *sql.DB, t *Table, ARGS map[string]any, extra ...map[string]any) ([]any, error)
3.7) Atom

An atom is made of a table and its pre-defined actions.

type Atom struct {
 Table
 Actions []Capability `json:"actions,omitempty" hcl:"actions,optional"`
}
3.8) RunAtomContext

This is the main function for action. It takes input data ARGS and optional constraint extra, and acts. The output is a slice of interface, and an optional error.

RunActomContext(ctx context.Context, db *sql.DB, action string, ARGS any, extra ...map[string]any) ([]any, error)



Chapter 4. RESTFUL ACTIONS

molecule has defined the following 7 RESTful actions. Most RDB tasks can be fulfilled using these actions.

4.1) Insert

Add a new row into table.

type Insert struct {
    Action
}
4.2) Update

Update a row by primary key.

type Update struct {
    Action
    Empties  []string  `json:"empties,omitempty" hcl:"empties,optional"`
}

where Empties defines columns which will be forced to be empty or null if having no input data.

4.3) Insupd

If insert a new row if the row is unique, otherwise update.

type Insupd struct {
    Action
}
4.4) Edit

Query one row by primary key. We can dynamically pass the field names as concated value in ARGS[FIELDS]. Joints is provided optionally so a single Edit could run a more sophisticated JOIN statement.

type Edit struct {
    Action
    Joins    []*Joint   `json:"joins,omitempty" hcl:"join,block"`
    FIELDS   string    `json:"fields,omitempty" hcl:"fields"`
}
4.5) Topics
type Topics struct {
    Action
    Joints []*Joint    `json:"joints,omitempty" hcl:"joints,block"`
    FIELDS string      `json:"fields,omitempty" hcl:"fields"`

    Totalforce  int    `json:"totalforce,omitempty" hcl:"totalforce,optional"`
    MAXPAGENO   string `json:"maxpageno,omitempty" hcl:"maxpageno,optional"`
    TOTALNO     string `json:"totalno,omitempty" hcl:"totalno,optional"`
    PAGESIZE    string `json:"pagesize,omitempty" hcl:"pagesize,optional"`
    PAGENO      string `json:"pageno,omitempty" hcl:"pageno,optional"`
    SORTBY      string `json:"sortby,omitempty" hcl:"sortby,optional"`
    SORTREVERSE string `json:"sortreverse,omitempty" hcl:"sortreverse,optional"`
}

where pagination is defined by:

Field Default Meaning in Input Data ARGS
MAXPAGENO "maxpageno" how many pages in total
TOTALNO "totalno" how many records in total
PAGESIZE "pagesize" how many record in each page
PAGENO "pageno" return only data of the specific page
SORTBY "sortby" sort the returned data by this
SORTREVERSE "sortreverse" 1 to return the data in reverse

and Totalforce is: 0 for not calculating total number of records; -1 for calculating; and 1 for optionally calculating. In the last case, if there is no input data for PAGESIZE or PAGENO, there is no pagination information.

4.6) Delete

Delete a row by primary key.

type Delete struct {
    Action
}
4.7) Delecs

Get all keys and foreign keys for a row. In molecule, when to delete a row in this table, deletions could be triggered in other tables. We always run Delecs before Delete so as to get keys ready for the related tables.

type Delecs struct {
    Action
}



Chapter 5. MOLECULE USAGE

Molecule is a collection of all atoms in the database and implements the Run function to react.

type Molecule struct {
    Atoms []*Atom `json:"atoms" hcl:"atoms"`
    DatabaseName string `json:"databaseName" hcl:"databaseName"`
    DBDriver DBType `json:"dbDriver" hcl:"dbDriver"`
	Stopper
}

type Stopper interface {
	Sign(tableObj *Table, item any) bool
}

where DBDriver is one of database drive defined:

    SQLDefault DBType = iota
    SQLRaw
    SQLite
    MySQL
    Postgres
    TSMillisecond
    TSMicrosecond

Stopper stops molecule's chain actions at an earlier stage defined by Sign is true.

Click for Molecule Usage Sample 

package main

import (
    "context"
    "database/sql"
    "fmt"
    "os"

    "github.com/genelet/molecule/godbi"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    dbUser := os.Getenv("DBUSER")
    dbPass := os.Getenv("DBPASS")
    dbName := os.Getenv("DBNAME")
    db, err := sql.Open("mysql", dbUser + ":" + dbPass + "@/" + dbName)
    if err != nil { panic(err) }
    defer db.Close()

    db.Exec(`drop table if exists m_b`)
    db.Exec(`drop table if exists m_a`)
    db.Exec(`CREATE TABLE m_a (
        id int auto_increment not null primary key,
        x varchar(8), y varchar(8), z varchar(8))`)
    db.Exec(`CREATE TABLE m_b (
        tid int auto_increment not null primary key,
        child varchar(8),
        id int)`)

    ctx := context.Background()
    METHODS := map[string]string{"LIST": "topics", "GET": "edit", "POST": "insert", "PUT": "update", "PATCH": "insupd", "DELETE": "delete"}

   molecule, err := godbi.NewMoleculeJson([]byte(`{"atoms":
[{
    "tableName": "m_a",
    "pks": [ "id" ],
    "idAuto": "id",
    "columns": [
        {"columnName":"x", "label":"x", "typeName":"string", "notnull":true },
        {"columnName":"y", "label":"y", "typeName":"string", "notnull":true },
        {"columnName":"z", "label":"z", "typeName":"string" },
        {"columnName":"id", "label":"id", "typeName":"int", "auto":true }
    ],
    "uniques":["x","y"],
    "actions": [{
        "actionName": "insupd",
        "nextpages": [{
            "tableName": "m_b",
            "actionName": "insert",
            "relateArgs": { "id": "id" },
            "marker": "m_b"
        }]
    },{
        "actionName": "insert",
        "nextpages": [{
            "tableName": "m_b",
            "actionName": "insert",
            "relateArgs": { "id": "id" },
            "marker": "m_b"
        }]
    },{
        "actionName": "edit",
        "nextpages": [{
            "tableName": "m_b",
            "actionName": "topics",
            "relateExtra": { "id": "id" }
        }]
    },{
        "actionName": "delete",
        "prepares": [{
            "tableName": "m_b",
            "actionName": "delecs",
            "relateArgs": { "id": "id" }
        }]
    },{
        "actionName": "topics",
        "nextpages": [{
            "tableName": "m_a",
            "actionName": "edit",
            "relateExtra": { "id": "id" }
        }]
    }]
},{
    "tableName": "m_b",
    "pks": [ "tid" ],
    "fks": [{"fkTable":"m_a", "fkColumn":"id", "column":"id"}],
    "idAuto": "tid",
    "columns": [
        {"columnName":"tid", "label":"tid", "typeName":"int", "notnull": true, "auto":true},
        {"columnName":"child", "label":"child", "typeName":"string"},
        {"columnName":"id", "label":"id", "typeName":"int", "notnull": true}
    ],
    "actions": [{
        "actionName": "delecs",
        "nextpages": [{
            "tableName": "m_b",
            "actionName": "delete",
            "relateArgs": { "tid": "tid" }
        }]
    },{
        "actionName": "topics"
    }]
}]
}`))

    var lists []any

    // the 1st web requests creates id=1 to the m_a and m_b tables:
    //
    args := map[string]any{"x": "a1234567", "y": "b1234567", "z": "temp", "child": "john", "m_b": []map[string]any{{"child": "john"}, {"child": "john2"}}}
    lists, err = molecule.RunContext(ctx, db, "m_a", METHODS["PATCH"], &godbi.RunOption{Args: args})
    if err != nil { panic(err) }

    // the 2nd request just updates, becaues [x,y] is unique in m_a.
    // but creates a new record in tb for id=1
   args = map[string]any{"x": "a1234567", "y": "b1234567", "z": "zzzzz", "m_b": map[string]any{"child": "sam"}}
    lists, err = molecule.RunContext(ctx, db, "m_a", METHODS["PATCH"], &godbi.RunOption{Args: args})
    if err != nil { panic(err) }

    // the 3rd request creates id=2
    //
    args = map[string]any{"x": "c1234567", "y": "d1234567", "z": "e1234", "m_b": map[string]any{"child": "mary"}}
    lists, err = molecule.RunContext(ctx, db, "m_a", METHODS["POST"], &godbi.RunOption{Args: args})
    if err != nil { panic(err) }

    // the 4th request creates id=3
    //
    args = map[string]any{"x": "e1234567", "y": "f1234567", "z": "e1234", "m_b": map[string]any{"child": "marcus"}}
    lists, err = molecule.RunContext(ctx, db, "m_a", METHODS["POST"], &godbi.RunOption{Args: args})
    if err != nil { panic(err) }

    // GET all
    args = map[string]any{}
    lists, err = molecule.RunContext(ctx, db, "m_a", METHODS["LIST"], &godbi.RunOption{Args: args})
    if err != nil { panic(err) }
    fmt.Printf("Step 1: %v\n", lists)

    // GET one
    args = map[string]any{"id": 1}
    lists, err = molecule.RunContext(ctx, db, "m_a", METHODS["GET"], &godbi.RunOption{Args: args})
    if err != nil { panic(err) }
    fmt.Printf("Step 2: %v\n", lists)

    // DELETE
    lists, err = molecule.RunContext(ctx, db, "m_a", METHODS["DELETE"], &godbi.RunOption{Args: map[string]any{"id": 1}})
    if err != nil { panic(err) }

    // GET all m_a
    args = map[string]any{}
    lists, err = molecule.RunContext(ctx, db, "m_a", METHODS["LIST"], &godbi.RunOption{Args: args})
    if err != nil { panic(err) }
    fmt.Printf("Step 3: %v\n", lists)

    // GET all m_b
    args = map[string]any{}
    lists, err = molecule.RunContext(ctx, db, "m_b", METHODS["LIST"], &godbi.RunOption{Args: args})
    if err != nil { panic(err) }
    fmt.Printf("Step 4: %v\n", lists)

    db.Exec(`drop table if exists m_a`)
    db.Exec(`drop table if exists m_b`)
}

Running it will result in:

Step 1: [map[id:1 m_a_edit:[map[id:1 m_b_topics:[map[child:john id:1 tid:1] map[child:john2 id:1 tid:2] map[child:sam id:1 tid:3]] x:a1234567 y:b1234567 z:zzzzz]] x:a1234567 y:b1234567 z:zzzzz] map[id:2 m_a_edit:[map[id:2 m_b_topics:[map[child:mary id:2 tid:4]] x:c1234567 y:d1234567 z:e1234]] x:c1234567 y:d1234567 z:e1234] map[id:3 m_a_edit:[map[id:3 m_b_topics:[map[child:marcus id:3 tid:5]] x:e1234567 y:f1234567 z:e1234]] x:e1234567 y:f1234567 z:e1234]]
Step 2: [map[id:1 m_b_topics:[map[child:john id:1 tid:1] map[child:john2 id:1 tid:2] map[child:sam id:1 tid:3]] x:a1234567 y:b1234567 z:zzzzz]]
Step 3: [map[id:2 m_a_edit:[map[id:2 m_b_topics:[map[child:mary id:2 tid:4]] x:c1234567 y:d1234567 z:e1234]] x:c1234567 y:d1234567 z:e1234] map[id:3 m_a_edit:[map[id:3 m_b_topics:[map[child:marcus id:3 tid:5]] x:e1234567 y:f1234567 z:e1234]] x:e1234567 y:f1234567 z:e1234]]
Step 4: [map[child:mary id:2 tid:4] map[child:marcus id:3 tid:5]]


5.1) Construct

Use JSON to build a molecule

func NewMoleculeJSONFile(filename string, cmap ...map[string][]Capability) (*Molecule, error)

where cmap is for customized actions not in the default list.

5.2) Run action on atom

We can run any action on any atom by names using RunConext. The output is data as a slice of interface, and an optional error.

func (m *Molecule) RunContext(ctx context.Context, db *sql.DB, atom, action string, opt *RunOption) ([]any, error)

Unlike traditional REST, which is limited to a sinlge table and sinle action, RunContext will act on related tables and trigger associated actions.

Please check the full document for usage.

Documentation

Overview

Package godbi provides a generic database interface

Index

Constants

View Source 
const (
	STMT = "stmt"
)

Variables

This section is empty.

Functions

func DevelopLogger added in v1.3.1 

func DevelopLogger(w io.Writer, level ...slog.Level) *slog.Logger

func ProductLogger added in v1.3.1 

func ProductLogger(w io.Writer, level ...slog.Level) *slog.Logger

Types

type Action 

type Action struct {
	ActionName string        `json:"actionName,omitempty" hcl:"actionName,label"`
	Picked     []string      `json:"picked,omitempty" hcl:"picked,optional"`
	Prepares   []*Connection `json:"prepares,omitempty" hcl:"prepares,block"`
	Nextpages  []*Connection `json:"nextpages,omitempty" hcl:"nextpages,block"`
	IsDo       bool          `json:"-" hcl:"-"`
}

Action is the base struct for REST actions. Prepares and Nextpages are edges to other tables before and after the action.

func (*Action) GetBaseAction added in v1.4.0 

func (a *Action) GetBaseAction() *Action

GetBaseAction gets the base action

type Atom 

type Atom struct {
	AtomName string `json:"atomName,omitempty" hcl:"atomName,label"`
	Table
	Actions []Capability `json:"actions,omitempty" hcl:"actions,block"`
	// contains filtered or unexported fields
}

Atom is a table with multiple actions

func (*Atom) GetAction 

func (a *Atom) GetAction(actionName string) Capability

GetAction gets a specific action by name

func (*Atom) MergeCustomActions added in v1.3.0 

func (a *Atom) MergeCustomActions(custom ...Capability)

MergeCustomActions merges custom actions

func (*Atom) RunAtom 

func (a *Atom) RunAtom(db *sql.DB, action string, args any, extra ...map[string]any) ([]any, error)

RunAtom runs an action by name

func (*Atom) RunAtomContext 

func (a *Atom) RunAtomContext(ctx context.Context, db *sql.DB, action string, args any, extra ...map[string]any) ([]any, error)

RunAtomContext runs an action with context by name

func (*Atom) UnmarshalHCL added in v1.3.0 

func (a *Atom) UnmarshalHCL(bs []byte, labels ...string) error

UnmarshalHCL is a HCL unmarshaler

func (*Atom) UnmarshalJSON added in v1.0.4 

func (a *Atom) UnmarshalJSON(bs []byte) error

UnmarshalJSON is a JSON unmarshaler

type Capability 

type Capability interface {
	GetBaseAction() *Action
	// RunActionContext runs the action with context, db, table, and args
	RunActionContext(context.Context, *sql.DB, *Table, map[string]any, ...map[string]any) ([]any, error)
}

Capability is to implement Capability interface

type Col 

type Col struct {
	ColumnName string `json:"columnName" hcl:"columnName,label"`
	TypeName   string `json:"typeName" hcl:"typeName,optional"`
	Label      string `json:"label" hcl:"columnLabel,optional"`
	Notnull    bool   `json:"notnull,omitempty" hcl:"notnull,optional"`
	Constraint bool   `json:"constraint,omitempty" hcl:"constraint,optional"`
	Auto       bool   `json:"auto,omitempty" hcl:"auto,optional"`
	// true for a one-to-may recurse column
	Recurse bool `json:"recurse,omitempty" hcl:"recurse,optional"`
}

Col defines table column in GO struct

type ConnectType 

type ConnectType int
const (
	CONNECTDefault ConnectType = iota
	CONNECTOne
	CONNECTArray
	CONNECTMap
	CONNECTMany
)

type Connection 

type Connection struct {
	// AtomName: the name of the table
	AtomName string `json:"atomName" hcl:"atomName,label"`

	// ActionName: the action on the atom
	ActionName string `json:"actionName" hcl:"actionName,label"`

	// RelateArgs: map current page's columns to nextpage's columns as input
	RelateArgs map[string]string `json:"relateArgs,omitempty" hcl:"relateArgs,optional"`

	// RelateExtra: map current page's columns to nextpage's columns (for Nextpages), or prepared page's columns to current page's columns (for Prepares) as constrains.
	RelateExtra map[string]string `json:"relateExtra,omitempty" hcl:"relateExtra,optional"`

	// Dimension: for nextpage's output format
	Dimension ConnectType `json:"dimension,omitempty" hcl:"dimension,optional"`

	// Marker: for input data, this marks a whole data set for the next or previous object; for output data, this is the key for the next whole data set.
	Marker string `json:"marker,omitempty" hcl:"marker,optional"`
}

Connection describes linked page 1) for Nextpages, it maps item in lists to next ARGS and next Extra 2) for Prepares, it maps current ARGS to the next ARGS and next Extra

func (*Connection) Subname 

func (c *Connection) Subname() string

Subname is the marker string used to store the output

type DBI 

type DBI struct {
	// Embedding the generic database handle.
	*sql.DB
	// contains filtered or unexported fields
}

DBI embeds GO's generic SQL handler and adds few functions for database executions and queries.

func (*DBI) DoSQL 

func (d *DBI) DoSQL(query string, args ...any) (sql.Result, error)

DoSQL executes a SQL the same as DB's Exec, only save the last inserted ID

func (*DBI) DoSQLContext 

func (d *DBI) DoSQLContext(ctx context.Context, query string, args ...any) (sql.Result, error)

DoSQLContext executes a SQL the same as DB's Exec, only save the last inserted ID

func (*DBI) DoSQLs 

func (d *DBI) DoSQLs(query string, args ...[]any) (sql.Result, error)

DoSQLs executes multiple rows using the same prepared statement, The rows are expressed as a slice of slices.

func (*DBI) DoSQLsContext 

func (d *DBI) DoSQLsContext(ctx context.Context, query string, args ...[]any) (sql.Result, error)

DoSQLsContext executes multiple rows using the same prepared statement, The rows are expressed as a slice of slices.

func (*DBI) GetSQL 

func (d *DBI) GetSQL(res map[string]any, query string, labels []any, args ...any) error

GetSQL returns single row 'res'.

func (*DBI) GetSQLContext 

func (d *DBI) GetSQLContext(ctx context.Context, res map[string]any, query string, labels []any, args ...any) error

GetSQLContext returns single row 'res'.

func (*DBI) InsertID 

func (d *DBI) InsertID(query string, args ...any) (int64, error)

InsertID executes a SQL the same as DB's Exec, only save the last inserted ID

func (*DBI) InsertIDContext 

func (d *DBI) InsertIDContext(ctx context.Context, query string, args ...any) (int64, error)

InsertIDContext executes a SQL the same as DB's Exec, only save the last inserted ID

func (*DBI) InsertSerial 

func (d *DBI) InsertSerial(query string, args ...any) (int64, error)

InsertSerial insert a SQL row into Postgres table with Serail , only save the last inserted ID

func (*DBI) InsertSerialContext 

func (d *DBI) InsertSerialContext(ctx context.Context, query string, args ...any) (int64, error)

InsertSerialContext insert a SQL into Postgres table with Serial, only save the last inserted ID

func (*DBI) Select 

func (d *DBI) Select(lists *[]any, query string, args ...any) error

Select returns queried data 'list' as a slice of maps, whose data types are determined dynamically by the generic handle.

func (*DBI) SelectContext 

func (d *DBI) SelectContext(ctx context.Context, lists *[]any, query string, args ...any) error

SelectContext returns queried data 'lists' as a slice of maps, whose data types are determined dynamically by the generic handle.

func (*DBI) SelectSQL 

func (d *DBI) SelectSQL(lists *[]any, query string, labels []any, args ...any) error

SelectSQL returns queried data 'list' as a slice of maps. The map keys and their data types are pre-defined in 'labels', expressed as a slice of interfaces:

  1. when an interface is a string, this is the key name. The data types are determined dynamically by the generic handler.
  2. when an interface is a 2-string slice, the first element is the key and the second the data type in "int64", "int", "string" etc.

func (*DBI) SelectSQLContext 

func (d *DBI) SelectSQLContext(ctx context.Context, lists *[]any, query string, labels []any, args ...any) error

SelectSQLContext returns queried data 'list' as a slice of maps. The map keys and their data types are pre-defined in 'labels', expressed as a slice of interfaces:

  1. when an interface is a string, this is the key name. The data types are determined dynamically by the generic handler.
  2. when an interface is a 2-string slice, the first element is the key and the second the data type in "int64", "int", "string" etc.

func (*DBI) TxSQL 

func (d *DBI) TxSQL(query string, args ...any) (sql.Result, error)

TxSQL is the same as DoSQL, but use transaction

func (*DBI) TxSQLContext 

func (d *DBI) TxSQLContext(ctx context.Context, query string, args ...any) (sql.Result, error)

TxSQLContext is the same as DoSQLContext, but use transaction

type DBType 

type DBType int32
const (
	SQLDefault DBType = iota
	SQLRaw
	SQLite
	MySQL
	Postgres
	TSNano
)

func DBTypeByName 

func DBTypeByName(str string) DBType

DBTypeByName returns the DBType by name

func (DBType) LowerName 

func (d DBType) LowerName() string

LowerName returns the lower case name of the DBType

type Delecs 

type Delecs struct {
	Action
}

Delecs is a special Topics action that returns all foreign keys

func (*Delecs) RunAction 

func (d *Delecs) RunAction(db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

func (*Delecs) RunActionContext 

func (d *Delecs) RunActionContext(ctx context.Context, db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

RunActionContext of Delecs is to populate Fks before making delete, so that Fks could be passed to other Prepared delete actions. If there is no Fks, we putput the input, then Delecs does nothing.

type Delete 

type Delete struct {
	Action
}

Delete struct for row deletion by primary key

func (*Delete) RunAction 

func (d *Delete) RunAction(db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

func (*Delete) RunActionContext 

func (d *Delete) RunActionContext(ctx context.Context, db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

type Edit 

type Edit struct {
	Action
	FIELDS string `json:"fields,omitempty" hcl:"fields,optional"`
}

Edit struct for search one specific row by primary key

func (*Edit) RunAction 

func (e *Edit) RunAction(db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

func (*Edit) RunActionContext 

func (e *Edit) RunActionContext(ctx context.Context, db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

type Fk 

type Fk struct {
	// the parent table name
	FkTable string `json:"fkTable" hcl:"fkTable,optional"`
	// the parant table column
	FkColumn string `json:"fkColumn" hcl:"fkColumn,optional"`
	// column of this table
	Column string `json:"column" hcl:"column,optional"`
}

Fk defines foreign key struct

type Insert 

type Insert struct {
	Action
}

Insert struct for table insert

func (*Insert) RunAction 

func (i *Insert) RunAction(db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

RunAction inserts a row using data passed in args.

func (*Insert) RunActionContext 

func (i *Insert) RunActionContext(ctx context.Context, db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

RunActionContext inserts a row using data passed in args.

type Insupd 

type Insupd struct {
	Action
}

Insupd struct for table update, if not existing according to the unique key, do insert

func (*Insupd) RunAction 

func (i *Insupd) RunAction(db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

func (*Insupd) RunActionContext 

func (i *Insupd) RunActionContext(ctx context.Context, db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

type Joint 

type Joint struct {
	TableName string `json:"tableName" hcl:"tableName,label"`
	Alias     string `json:"alias,omitempty" hcl:"alias,optional"`
	JoinType  string `json:"type,omitempty" hcl:"type,optional"`
	JoinUsing string `json:"using,omitempty" hcl:"using,optional"`
	JoinOn    string `json:"on,omitempty" hcl:"on,optional"`
	Sortby    string `json:"sortby,omitempty" hcl:"sortby,optional"`
}

Joint struct describes the joined search

type Molecule 

type Molecule struct {
	Atoms    []*Atom `json:"atoms" hcl:"atoms,block"`
	DBDriver DBType  `json:"dbDriver" hcl:"dbDriver,optional"`
	Stopper
	PreStopper
	// contains filtered or unexported fields
}

Molecule describes all atoms and actions in a database schema

func (*Molecule) GetAtom 

func (m *Molecule) GetAtom(atomName string) *Atom

GetAtom returns the atom by atom name

func (*Molecule) GetLogger added in v1.3.1 

func (m *Molecule) GetLogger() Slogger

GetLogger gets the logger

func (*Molecule) RunContext 

func (m *Molecule) RunContext(ctx context.Context, db *sql.DB, atom, action string, opt *RunOption) ([]any, error)

RunContext runs action by atom and action string names. It returns the searched data and optional error code. atom is the atom name, and action the action name.

func (*Molecule) SetLogger added in v1.3.1 

func (m *Molecule) SetLogger(logger Slogger)

SetLogger sets the logger

type PreStopper added in v1.3.0 

type PreStopper interface {
	Stop(tableObj, childObj *Table) bool
}

type RunOption added in v1.4.0 

type RunOption struct {
	Args        any
	Extra       map[string]any
	GlobalArgs  map[string]any
	GlobalExtra map[string]any
}

RunOption holds the arguments for RunContext

type Slogger added in v1.3.1 

type Slogger interface {
	Debug(string, ...any)
	Info(string, ...any)
	Warn(string, ...any)
	Error(string, ...any)
}

type Stmt added in v1.3.0 

type Stmt struct {
	Action
	StmtContext
	Others map[string]*StmtContext `json:"others,omitempty" hcl:"others,block"`
}

Stmt struct for search one specific row by primary key

func (*Stmt) RunActionContext added in v1.3.0 

func (s *Stmt) RunActionContext(ctx context.Context, db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

type StmtContext added in v1.3.0 

type StmtContext struct {
	Statement string   `json:"statement" hcl:"statement,optional"`
	Labels    []any    `json:"labels,omitempty" hcl:"labels,optional"`
	Pars      []string `json:"pars,omitempty" hcl:"pars,optional"`
}

type Stopper added in v1.0.3 

type Stopper interface {
	PreStopper
	Sign(tableObj *Table, item map[string]any)
}

type Table 

type Table struct {
	TableName string   `json:"tableName" hcl:"tableName,optional"`
	Columns   []*Col   `json:"columns" hcl:"columns,block"`
	Pks       []string `json:"pks,omitempty" hcl:"pks,optional"`
	IDAuto    string   `json:"idAuto,omitempty" hcl:"idAuto,optional"`
	Fks       []*Fk    `json:"fks,omitempty" hcl:"fks,block"`
	Uniques   []string `json:"uniques,omitempty" hcl:"uniques,optional"`
	// contains filtered or unexported fields
}

Table defines a table by name, columns, primary key, foreign keys, auto id and unique columns

func (*Table) GetLogger added in v1.3.1 

func (t *Table) GetLogger() Slogger

GetLogger gets the logger

func (*Table) IsRecursive 

func (t *Table) IsRecursive() bool

IsRecursive indicates if table references to itself in one to multiple relations

func (*Table) RecursiveColumn 

func (t *Table) RecursiveColumn() string

RecursiveColumn returns the name of the resursive column

func (*Table) SetDBDriver 

func (t *Table) SetDBDriver(driver DBType)

SetDBDriver sets the driver type

func (*Table) SetLogger added in v1.3.1 

func (t *Table) SetLogger(logger Slogger)

SetLogger sets the logger

type Topics 

type Topics struct {
	Action
	FIELDS string `json:"fields,omitempty" hcl:"fields,optional"`

	Totalforce  int    `json:"totalforce,omitempty" hcl:"totalforce,optional"`
	MAXPAGENO   string `json:"maxpageno,omitempty" hcl:"maxpageno,optional"`
	TOTALNO     string `json:"totalno,omitempty" hcl:"totalno,optional"`
	PAGESIZE    string `json:"pagesize,omitempty" hcl:"pagesize,optional"`
	PAGENO      string `json:"pageno,omitempty" hcl:"pageno,optional"`
	SORTBY      string `json:"sortby,omitempty" hcl:"sortby,optional"`
	SORTREVERSE string `json:"sortreverse,omitempty" hcl:"sortreverse,optional"`
}

Topics struct for search multiple rows by constraints

func (*Topics) RunAction 

func (t *Topics) RunAction(db *sql.DB, table *Table, args map[string]any, extra ...map[string]any) ([]any, error)

func (*Topics) RunActionContext 

func (t *Topics) RunActionContext(ctx context.Context, db *sql.DB, table *Table, args map[string]any, extra ...map[string]any) ([]any, error)

type Update 

type Update struct {
	Action
	Empties []string `json:"empties,omitempty" hcl:"empties,optional"`
}

Update struct for row update by primary key

func (*Update) RunAction 

func (u *Update) RunAction(db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

func (*Update) RunActionContext 

func (u *Update) RunActionContext(ctx context.Context, db *sql.DB, t *Table, args map[string]any, extra ...map[string]any) ([]any, error)

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.