Database

z3n7 has two ways to work with a database. Both speak PostgreSQL and SQLite (SQLite through the SQLite3 ODBC driver, which must be installed).

The Db class#

Db takes its connection settings explicitly or from the project:

// explicit settings
var db = new Db("SQLite", sqLitePath: @"C:\data\accounts.db", defaultTable: "accounts");

// from the project: DBmode, DBsqltPath and the global sqlPg* variables
var db = new Db(project);

string balance = db.Get("balance", id: 5);            // one row by id
var row = db.GetColumns("login, proxy", id: 5);       // column → value
db.Upd("status = 'ok'", id: 5);

Every statement opens its own connection. A SELECT returns rows joined by · and columns joined by ¦.

The project.Db* helpers#

Extension methods such as DbGet, DbUpd, DbQ use the database named by the dbSource variable (project variable, else global variable):

• a value starting with Host= is a PostgreSQL connection string;
• anything else selects SQLite; DbQ then opens the file passed in its sqLitePath argument, and the Db* helpers do not pass one.

They work on the current account's row unless told otherwise: the table defaults to the project table (__{project name}) and the row to id = acc0.

project.Var("acc0", "7");
string login = project.DbGet("login");                 // __myproject, id = 7
project.DbUpd("status = 'done'");                      // same row
project.DbDone("daily", cooldownMin: 60);              // cooldown timestamp
var proxy = project.DbGet("proxy", "_instance");       // another table, same account

Preparing a table#

// id + the listed columns + one column per item of cfgToDo
project.PrepareProjectTable(new List<string> { "login", "status", "daily" });
project.AddRange(project.Var("projectTable"));         // rows 1..rangeEnd

PrepareProjectTable creates the table if needed and adds missing columns; prune drops extra ones, rearrange reorders them.

Conventions#

• id is the account number.
• Values given as id or inside where are inserted into SQL as written; quote text yourself.
• In account selection conditions the word NOW becomes the current time as yyyy-MM-ddTHH:mm:ss (see 06. Account workflow).
• Tables used by the library itself: _instance (proxy, cookies, WebGL per account), _api (credentials of external services by id), _processes (ProcessManager).

See also: Database, Db API.