src/norm/sqlite

    Dark Mode
Search:
Group by:

Types

NotFoundError = object of KeyError
RollbackError = object of CatchableError

Raised when transaction is manually rollbacked.

Do not raise manually, use rollback proc.

Consts

dbHostEnv = "DB_HOST"

Procs

proc count(dbConn; T: typedesc[Model]; col = "*"; dist = false; cond = "1";
           params: varargs[DbValue, dbValue]): int64

Count rows matching condition without fetching them.

To count rows with non-NULL values in a particular column, pass the column name in col param.

To count only unique column values, use dist = true (stands for “distinct.”) 

proc createSchema[T: Model](dbConn; obj: T)
Dummy proc for API consistency. 
proc createTables[T: Model](dbConn; obj: T)
Create tables for Model and its Model fields. 
proc delete[T: Model](dbConn; obj: var T)
Delete rows for Model instance and its Model fields. 
proc delete[T: Model](dbConn; objs: var openArray[T])
Delete rows for each Model instance in open array. 
proc dropDb() {....raises: [OSError], tags: [WriteDirEffect, ReadEnvEffect].}
Remove the DB file defined in environment variable. 
proc exists(dbConn; T: typedesc[Model]; cond = "1";
            params: varargs[DbValue, dbValue]): bool
Check if a row exists in the table. 
proc getDb(): DbConn {....raises: [DbError], tags: [DbEffect, ReadEnvEffect,
    ReadDbEffect, WriteDbEffect].}
Create a DbConn from DB_HOST environment variable. 
proc insert[T: Model](dbConn; obj: var T; force = false;
                      conflictPolicy = cpRaise)

Insert rows for Model instance and its Model fields, updating their id fields.

By default, if the inserted object's id is not 0, the object is considered already inserted and is not inserted again. You can force new insertion with force = true.

conflictPolicy determines how the proc reacts to insertion conflicts. cpRaise means raise a DbError, cpIgnore means ignore the conflict and do not insert the conflicting row, cpReplace means overwrite the older row with the newer one. 

proc insert[T: Model](dbConn; objs: var openArray[T]; force = false;
                      conflictPolicy = cpRaise)
Insert rows for each Model instance in open array. 
proc rawSelect[T: ref object](dbConn; qry: string; obj: var T;
                              params: varargs[DbValue, dbValue]) {.
    ...raises: {ValueError, DbError, LoggingError}.}

Populate a ref object instance obj and its ref object fields from DB.

qry is the raw sql query whose contents are to be parsed into obj. The columns on qry must be in the same order as the fields on obj. Raises a NotFoundError if the query returns nothing. 

proc rawSelect[T: ref object](dbConn; qry: string; objs: var seq[T];
                              params: varargs[DbValue, dbValue]) {.
    ...raises: {ValueError, DbError, LoggingError}.}

Populate a sequence of ref object instances from DB.

qry is the raw sql query whose contents are to be parsed into objs.

The columns on qry must be in the same order as the fields on objs. objs must have at least one item. 

proc rollback() {....raises: RollbackError, tags: [].}
Rollback transaction by raising RollbackError. 
proc select[T: Model](dbConn; obj: var T; cond: string;
                      params: varargs[DbValue, dbValue]) {.
    ...raises: {NotFoundError, ValueError, DbError, LoggingError}.}

Populate a Model instance and its Model fields from DB.

cond is condition for WHERE clause but with extra features:

  • use ? placeholders and put the actual values in params
  • use table, col, and fCol procs instead of hardcoded table and column names
proc select[T: Model](dbConn; objs: var seq[T]; cond: string;
                      params: varargs[DbValue, dbValue]) {.
    ...raises: {ValueError, DbError, LoggingError}.}

Populate a sequence of Model instances from DB.

objs must have at least one item. 

proc selectAll[T: Model](dbConn; objs: var seq[T])

Populate a sequence of Model instances from DB, fetching all rows in the matching table.

objs must have at least one item.

Warning: this is a dangerous operation because you don't control how many rows will be fetched. 

proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn;
    queryStartEntries: seq[M1]; joinModelEntries: var seq[J];
    queryEndEntries: var Table[int64, seq[M2]])
A convenience proc. Fetches the many-to-many relationship for all members of queryStartEntries and stores them in the table of queryEndEntries. There, all entries connected to a given member of queryStartEntry are mapped to that members id. Requires to also be passed the model connecting the many-to-many relationship via `joinModelEntriesin order to fetch the relationship. The fields on joinModelEntries to use for these queries are inferred. Will only compile if the joinModel has exactly one field pointing to the table of queryStartEntry as well as exactly one field pointing to the table of queryEndEntries. Specify the parameters fkColumnFromJoinToManyStart and fkColumnFromJoinToManyEnd if that is not the case. 
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn;
    queryStartEntries: seq[M1]; joinModelEntries: var seq[J];
    queryEndEntries: var Table[int64, seq[M2]];
    fkColumnFromJoinToManyStart: static string;
    fkColumnFromJoinToManyEnd: static string)
Fetches the many-to-many relationship for all members of queryStartEntries and stores them in the table of queryEndEntries. There, all entries connected to a given member of queryStartEntry are mapped to that members id. Requires to also be passed the model connecting the many-to-many relationship via `joinModelEntriesin order to fetch the relationship, and the name of its fields that point to the model of queryStartEntries (fkColumnFromJoinToManyStart) and queryEndEntries (fkColumnFromJoinToManyEnd). Will not compile if the specified fields on the joinModel do not properly point to the models of queryStartEntry and queryEndEntries. 
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn;
    queryStartEntry: M1; joinModelEntries: var seq[J];
    queryEndEntries: var seq[M2])
A convenience proc. Fetches the many-to-many relationship for the entry queryStartEntry and returns a seq of all entries connected to queryStartEntry in queryEndEntries. Requires to also be passed the model connecting the many-to-many relationship via joinModelEntriesin order to fetch the relationship. The fields on joinModelEntries to use for these queries are inferred. Will only compile if the joinModel has exactly one field pointing to the table of queryStartEntry as well as exactly one field pointing to the table of queryEndEntries. Specify the parameters fkColumnFromJoinToManyStart and fkColumnFromJoinToManyEnd if that is not the case. 
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn;
    queryStartEntry: M1; joinModelEntries: var seq[J];
    queryEndEntries: var seq[M2]; fkColumnFromJoinToManyStart: static string;
    fkColumnFromJoinToManyEnd: static string)
Fetches the many-to-many relationship for the entry queryStartEntry and returns a seq of all entries connected to queryStartEntry in queryEndEntries. Requires to also be passed the model connecting the many-to-many relationship via joinModelEntriesin order to fetch the relationship. Also requires the field on the joinModel that points to the table of queryStartEntry via the parameter fkColumnFromJoinToManyStart. Also requires the field field on the joinModel that points to the table of queryEndEntries via the parameter fkColumnFromJoinToManyEnd. Will not compile if the specified fields on the joinModel do not properly point to the tables of queryStartEntry and queryEndEntries. 
proc selectOneToMany[O: Model; M: Model](dbConn; oneEntries: seq[O];
    relatedEntries: var seq[M])
A convenience proc. Fetches all entries of multiple "many" side from multiple one-to-many relationships between the entries within oneEntries and the model of relatedEntries. This is done with a single query to the database. The field used to fetch the relatedEntries is automatically inferred as long as the relatedEntries model has only one field pointing to the model of oneEntries. Will not compile if relatedEntries has multiple fields that point to the model of oneEntry. Specify the foreignKeyFieldName parameter in such a case. 
proc selectOneToMany[O: Model; M: Model](dbConn; oneEntries: seq[O];
    relatedEntries: var Table[int64, seq[M]]; foreignKeyFieldName: static string)
Fetches all entries of multiple "many" side from multiple one-to-many relationships between the entries within oneEntries and the model of relatedEntries. This is done with a single query to the database. The various many-to-one relationships are split into a table, where the id of each entry in oneEntries is mapped to the entries pointing to it. It is ensured at compile time that the field specified here is a valid foreign key field on oneEntry pointing to the table of the relatedEntries-model. relatedEntries must contain at least 1 entry with a seq that contains a model instance. 
proc selectOneToMany[O: Model; M: Model](dbConn; oneEntry: O;
    relatedEntries: var seq[M])
A convenience proc. Fetches all entries of a "many" side from the single one-to-many relationship between the model of oneEntry and the model of relatedEntries puts them into relatedEntries. The field used to fetch the relatedEntries is automatically inferred as long as the relatedEntries model has only one field pointing to the model of oneEntry. Will not compile if relatedEntries has multiple fields that point to the model of oneEntry. Specify the foreignKeyFieldName parameter in such a case. 
proc selectOneToMany[O: Model; M: Model](dbConn; oneEntry: O;
    relatedEntries: var seq[M]; foreignKeyFieldName: static string)
Fetches all entries of a "many" side from the single one-to-many relationship between the model of oneEntry and the model of relatedEntries and puts them into relatedEntries. It is ensured at compile time that the field specified here is a valid foreign key field on oneEntry pointing to the table of the relatedEntries-model. 
proc sum(dbConn; T: typedesc[Model]; col: string; dist = false; cond = "1";
         params: varargs[DbValue, dbValue]): float

Sum column values matching condition without fetching them.

To sum only unique column values, use dist = true (stands for “distinct.”) 

proc update[T: Model](dbConn; obj: var T)
Update rows for Model instance and its Model fields. 
proc update[T: Model](dbConn; objs: var openArray[T])
Update rows for each Model instance in open array.

Templates

template transaction(dbConn; body: untyped): untyped

Wrap code in DB transaction.

If an exception is raised, the transaction is rollbacked.

To rollback manually, call rollback. 

template withDb(body: untyped): untyped

Wrapper for DB operations.

Creates a DbConn with getDb as db variable, runs your code in a try block, and closes db afterward.


Made with Nim. Generated: 2024-01-31 16:00:03 UTC