github.com/aacfactory/fns-contrib/databases/sql@v1.2.84/dac/README.md (about)

     1  # DAC
     2  It is a database access layer for sql.
     3  
     4  ## Define
     5  ### Table
     6  Define a struct which implements `dac.Table`.  
     7  Examples:  
     8  User table.
     9  ```go
    10  type User struct {
    11  	Id          string                  `column:"ID,PK"`
    12  	CreateBY    string                  `column:"CREATE_BY,ACB"`
    13  	CreateAT    time.Time               `column:"CREATE_AT,ACT"`
    14  	ModifyBY    string                  `column:"MODIFY_BY,AMB"`
    15  	ModifyAT    time.Time               `column:"MODIFY_AT,AMT"`
    16  	DeleteBY    string                  `column:"DELETE_BY,ADB"`
    17  	DeleteAT    time.Time               `column:"DELETE_AT,ADT"`
    18  	Version     int64                   `column:"VERSION,AOL"`
    19  	Nickname    string                  `column:"NICKNAME"`
    20  	Mobile      string                  `column:"MOBILE"`
    21  	Gender      string                  `column:"GENDER"`
    22  	Birthday    time.Time               `column:"BIRTHDAY"`
    23  	Avatar      sql.NullJson[Avatar]    `column:"AVATAR,json"`
    24  	BD          times.Date              `column:"BD"`
    25  	BT          times.Time              `column:"BT"`
    26  }
    27  
    28  func (row User) TableInfo() dac.TableInfo {
    29  	return dac.Info("USER", dac.Schema("FNS"))
    30  }
    31  ```
    32  User avatar json typed column
    33  ```go
    34  // Avatar
    35  // json column
    36  type Avatar struct {
    37  	Schema      string `json:"schema"`
    38  	Domain      string `json:"domain"`
    39  	Path        string `json:"path"`
    40  	MimeType    string `json:"mimeType"`
    41  	URL         string `json:"url"`
    42  }
    43  ```
    44  Post table.  
    45  Author column is a reference column, which means many post to one user.  
    46  Comments column is a links column, which means one post to many comments.  
    47  Links columns is a virtual column, which means that column is select from another source.
    48  ```go
    49  type Post struct {
    50  	Id          string          `column:"ID,pk"`
    51  	CreateBY    string          `column:"CREATE_BY,ACB"`
    52  	CreateAT    time.Time       `column:"CREATE_AT,act"`
    53  	Version     int64           `column:"VERSION,aol"`
    54  	Author      User            `column:"AUTHOR,ref,Id"`
    55  	Title       string          `column:"TITLE"`
    56  	Content     string          `column:"CONTENT"`
    57  	Comments    []PostComment   `column:"COMMENTS,links,Id+PostId,orders:Id@desc,length:10"`
    58  	Likes       int64           `column:"LIKES,vc,basic,SELECT COUNT(1) FROM \"FNS\".\"POST_LIKE\" WHERE \"POST_ID\" = \"FNS\".\"POST\".\"ID\""`
    59  }
    60  
    61  func (row Post) TableInfo() dac.TableInfo {
    62  	return dac.Info("POST", dac.Schema("FNS"))
    63  }
    64  ```
    65  Comment table.
    66  ```go
    67  type PostComment struct {
    68  	Id       int64      `column:"ID,pk,incr"`
    69  	PostId   string     `column:"POST_ID"`
    70  	User     User       `column:"USER_ID,ref,Id"`
    71  	CreateAT time.Time  `column:"CREATE_AT,act"`
    72  	Content  string     `column:"CONTENT"`
    73  }
    74  
    75  func (row PostComment) TableInfo() dac.TableInfo {
    76  	return dac.Info("POST_COMMENT", dac.Schema("FNS"))
    77  }
    78  ```
    79  Like table.
    80  ```go
    81  type PostLike struct {
    82  	Id     int64  `column:"ID,pk,incr"`
    83  	PostId string `column:"POST_ID"`
    84  	UserId string `column:"USER_ID"`
    85  }
    86  
    87  func (row PostLike) TableInfo() dac.TableInfo {
    88  	return dac.Info("POST_LIKE", dac.Schema("FNS"))
    89  }
    90  ```
    91  ### View
    92  There are two kinds view, one is pure view, another is projection (used for group by).  
    93  Define a struct which implements `dac.View`.
    94  Example:   
    95  Count group genders.
    96  ```go
    97  type UserGenderCount struct {
    98  	Gender string `column:"GENDER"` // group by column
    99  	Count  int64  `column:"ID,vc,agg,COUNT"` // agg column
   100  }
   101  
   102  func (u UserGenderCount) ViewInfo() dac.ViewInfo {
   103  	return dac.TableView(User{}) // projection of User
   104  }
   105  ```
   106  ### Column
   107  Format of `column` tag is `{column name | ident},{kind},{options of kind}`.  
   108  Kinds:
   109  * normal: which is default.
   110  * pk: primary key, when it is increment, then add `incr` option, such as `id,pk,incr`.
   111  * acb: used for `create_by` column, only support `int` or `string` type.
   112  * act: used for `create_at` column, only support `int` or `time` type.
   113  * amb: used for `modify_by` column, only support `int` or `string` type.
   114  * amt: used for `modify_at` column, only support `int` or `time` type.
   115  * adb: used for `delete_by` column, only support `int` or `string` type.
   116  * adt: used for `delete_at` column, only support `int` or `time` type.
   117  * aol: used for `version` column, only support `int` type.
   118  * ref: used for many to one or one to one. first option is `{target struct field name}`.
   119  * link: used for one to one but host table has no target table column. first option is `{host struct field name}+{target struct field name}`.
   120  * links: used for one to many. first option is `{host struct field name}+{target struct field name}`, when use order, then add order option, such as `orders:{field name}{@desc}`. when use limit, then add limit option, such as `length:{size}`.
   121  * virtual: used for add out source column, first option is type of virtual. 
   122    * `basic` type means the column value is basic value. 
   123    * `object` type means the column value is one row which will be encoded by json.
   124    * `array` type means the column value is many rows which will be encoded by json.
   125    * `agg` type means the column value is result of aggregation.
   126  
   127  ### Note
   128  * DON'T use ptr to implement Table or View.
   129  * Anonymous field is supported, but can not be ptr and must be exported.
   130  * When reference is not null, then use value, not use ptr, also as link.
   131  * Element of links slice should be value not ptr.
   132  * `InsertOrUpdate` only used for which table has conflict columns.
   133  * When dialect does not support `returning`, then `InsertMulti` is not fully worked.
   134  
   135  ## Methods
   136  * Insert
   137  * InsertMulti
   138  * InsertOrUpdate
   139  * InsertWhenNotExist
   140  * InsertWhenExist
   141  * Update
   142  * UpdateFields
   143  * Delete
   144  * DeleteByCondition
   145  * Query
   146  * One
   147  * ALL
   148  * Views
   149  * ViewOne
   150  * ViewALL