db_init()

Initialize the database. Must be called only once, and before any other function.

db_free()

Unloads the database system, deleting all tables and values and freeing its memory.

db_save(path)

Save the database to file. Note that it's not saved in human readble format.

db_load(path)

Loads the dabase from a file saved with db_save(). This will replace allo existing contents in your database (if any)

db_table_clear(table_name)

Deletes all the records in table, resetting the ids but keeping the table structure.

db_table_column_add(table_name,column_name,type,default)

Adds a column with the specified name at the end of an existing table. type is either "real" or "string", default is the value to be inserted in the newly created cells for the existing records.

db_table_column_names(table_name)

Returns a ds_list of ordered column names for the specified table. The list has to be destroyed after usage.

db_table_column_remove(table_name,column_name)

Removes a column and all its values from a table

db_table_create(name,columns)

Creates a table in the database with the specified columns. If a table already exists with the same name, it is deleted and replaced.
The special column "id" is auto generated and should NOT be passed in the list of columns.
Columns have to be passed as a string of comma separated name:type values, like in "firstname:string,lastname:string,age:real,". A column type can either be "real" or "string".

db_table_delete(table_name)

Deletes a table and all its data, freeing the memory.

db_table_exists(table_name)

Returns true if a table with the provided name exists in the database, false otherwise.

db_table_size(table_name)

Returns the number of records stored in the table.

db_table_load(filename)

Creates a table from a file saved with db_table_save();

db_table_save(table_name,path)

Saves a table and all its contents to a file. This file can be later used with the function db_table_load() to load the table, with the same name, into the database. The file is in non human readable format.

db_table_save_csv(table_name,path,sep)

Saves a table content to a CSV file. Values are separated based on the sep argument. Useful for debugging or manipulating the data in excel or similar programs.

db_table_load_csv(table,path,separator)

Adds the contents of a CSV file to an existing table. Existing table contents are not removed. Note that the CSV file has to be formatted as in the result of db_table_save(): the first row (labels) and first column (id) will be skipped.

db_record_build(table,[name1:value1],[name2:value2],..)

Generates and returns a ds_map representing a record in the specified table. You can optionally specify some predefined data in the form "name:value" where name is a column name in the table.
The generated ds_map can be used to set the record data to be later inserted in the database with the function db_record_save(record_map). Once you save the record this way, the 'id' key of the record maps will be set from -1 to the actual record id.

Note that you are responsible for deleting the ds_map once you don't need it anymore. This will NOT delete the record from the database.

db_record_create(table,values)
db_record_create(table,value1,value2,value3,...)

Inserts a new record in the database table and returns the newly inserted record id.
The values need to be passed in the same order as you defined your table columns, and can either be passed as string of comma separates values, or as script arguments.
If passed as string of comma separated values, CSV format applies, this means you can skip special characters like in the CSV specification.
It is also possible to generate and sanitized CSV formatted string by passing the values to the script csv_compose_line() in a ds_list.

db_record_delete(table_name,id)

Deletes the record with id from the table. Returns false if the record doesn't exist, true otherwise.

db_record_exists(table_name,id)

Returns true if a record with the provided id exists into the table, false otherwise.

db_record_fetch(table,id)

Returns a ds_map representation of the record having the specified id, with all its data saved as values, and the respective columns as keys. If no valid record with id is found, -1 is returned;
The ds_map contents can be altered without affecting the database, until you call db_record_save(record_map), which will write the changes back.

Note that you are responsible for deleting the ds_map once you don't need it anymore. This will NOT delete the record from the database.

db_record_fetch_all(table,ids)

Returns a ds_list of records, each record in the form of a ds_map (as in db_record_fetch).

Deleting the returned list will automatically delete all the ds_maps in it.

db_record_first(table_name,column,value)

Returns the id of the first record having "value" at the specified column. Returns 0 if no record is found.

db_record_get(table_name,id,column)

Returns the value in the specified column of the record with the given id. If there's no record with that id, undefined is returned.
This can be useful if you need a couple of specific values for a record id you already have, but if you need the values of more records and of many fields, a select query should be used instead.

db_record_rnd(table_name,n)

If n is equal to 1, a valid random record id is returned. If n is greater than 1, the function returns a ds_list of n record ids. If no record is found, 0 is returned.
Note that the function may return duplicate ids, if you need a list of unique records ids, use a query instead.

db_record_save(record_map)

Saves a ds_map representation of a record (obtained by one of the following: db_record_build(), db_record_fetch(), db_record_fetch_all()) back to the database.

The function returns true if successful (e.g: returns false if it has an invalid id or the record has been deleted meanwhile).

db_record_update(table_name,id,columns,values)
db_record_update(table_name,id,columns,value1,value2,...)

Updates the values in the specified columns of record id. Returns true if successful, false otherwise. The fields and values parameters are two strings of comma separated values representing column names to update and values to insert respectively. Values can also be passed separately as arguments.

db_query_select(table,fields)

Select queries are used to return a specific set of fields from the table records. Fields need to be passed as a string of comma separated column names (you can pass and empty string to return all the fields)
The return value of db_query_exec() for a select query is either -1 if no record is found, or a ds_grid of values.
If only the id column is specified in the fields list, a ds_list of record ids is returned instead of a ds_grid.
Remember to destroy the returned ds_grid or ds_list after you are finished.

The function returns a query structure that needs to be passed to db_query_exec() to be executed and retrieve the results, and can be filtered and sorted by using query clauses

db_query_delete(table)

Delete queries are used to delete records from the database. This kind of query is usually associated with some clauses (see clauses section below) in order to restrict the range of the deleted records, otherwise all table records are deleted.
The generated query needs to be executed with db_query_exec(), which will return the actual number of records deleted from the query execution.

db_query_update(table,fields,values)
db_query_update(table,fields,value1,value2,valueN,...)

Update queries are used to update specific fields of one or more records. This kind of query is usually associated with some clauses in order to restrict the range of the updated records, Otherwise all records are affected.
The fields and values parameters are two strings of comma separated values representing column names to update and values to insert respectively.
The generated query needs to be executed with db_query_exec(), which will return the actual number of records affected.

db_query_calc(table or query,operation,field,ignore_limits)

Calc queries are used to perform calculations on the records.
The function accepts one of the following operations (as string): count,sum,min,max,mean .
You have to specify the column to consuder for the calculation in the field parameter (except for count).
The first argument can either be a table name or one of the other queries. In the first case in which case this function returns a regular calc query which can be filtered using clauses, in the second case it returns the result directly using the parameters of the other query (useful for example if you need to find out how many records will be deleted / updated before actually executing the query).
ignore_limits tells the query to ignore db_cl_limit and db_cl_offset clauses on calculations based on existing queries (useful to get the total number of records when doing a pagination).

db_cl_only(query,ids)

This clause selects only the records of the table having the specified ids.
The ids can be passed either as a ds_list of integers, or as a comma separated string.
This clause is extremely helpful when deleting or updating specific records. Note that the same result can be obtained by using the where clause (see below) associated with the list of ids, but this clause accesses the records directly from the index, and is therefore a lot faster.

Passing -1 as ids resets the clause and applied no id filter.

db_cl_where(query,script,fields,args)

This clause filters the records based on the specified conditions.
script is the name of the script used for comparing the values (e.g: db_op_eq tests for equality. See db_op_* scripts).
fields is a string of comma separated column names involved in the comparison.
args is a string of comma separated values to be tested against the fields.

The following comparison scripts are provided with the extension, but if those don't fit your requirements for a specific query, you can always define some new ones (see section condition scripts):

db_op_eq: checks for equality
db_op_neq: checks for difference
db_op_gt: checks for values grater than a number
db_op_lt: checks for values less than a number
db_op_between: checks inclusion of values between two specified numbers

Passing -1 as script resets the clause and no filter is applied.

db_cl_limit(query,limit,offset)

This clause applies a limit and/or an offset to the result set. Apart from limiting the results for select queries, this can also be used on delete and update queries to limit the scope of the operation. You can for instance update/delete only the first N records matching your criteria. Offset instead allows you to ignore the first N results.
Useful for instance for pagination to select specific chunks (pages) of your result set.

Passing 0 as limit and offest arguments resets the clause.

db_cl_sort(query,sort_criteria)

This clause applies an order to the result set. Sorting criteria and order have to be specified as a string in the format: "name:order" where 'name' is a column name, and 'order' can either be 'desc' or 'asc'
Note that this clause is obviously useful when using select queries, but can also be applied to delete and update queries, where it has a meaning only if coupled with the limit clause (you can for instance delete/update only the lightest fruits).

You can also specify two or more sort criteria, in the form "color:asc,name:asc": in this case, all data is sorted by color first, and then for each color the records are sorted by name.

If you pass the string "RAND" as sort_field, results are returned sorted randomly. An empty string as sort_field means no sorting applied.

db_cl_join(query,join_table,foreign_key)

The join clause allows to attach the values of another table to the queried table, based on a foreign key.
This is best explained by a practical example: suppose you have two tables in a trading card game, "cards" and "deck", where "cards" holds information about all the cards present in the game, and "deck" stores the card ids you have collected so far. If you want to get all the cards in your deck, along with all their data, you actually have to involve both tables. You could do that with two queries, but that would be tricky in complex cases, or you could just use a join clause and return the merged results, filtered, and sorted as you prefer.

join_table is the other table to involve, and foreign_key is the name of the column of the query table that holds the record ids of join_table.

IMPORTANT: when using the join clause, all reference to columns in the query and other clauses need to include the table they belong to in the form "table_name.column_name". See the example below.

db_query_exec(query,(keep))

Executes a query. The return value depends on the type of query passed as argument. Insert queries don't need to be executed with this function as they are executed automatically, as well as some calc queries (see queries specification).
The optional keep parameter can be either true or false, when true the query will not be destroyed after the function call. This means that you can actually save a query and execute it as many times as you want while changing the clauses. A query is actually data structure (ds_map), therefore if you decide to keep a query, you then need to destroy it with the function db_query_free or execute it the last time with keep = false.

db_query_free(query)

Deletes a query freeing the memory used (use only if you execute the query with keep == true) or never executing it.

csv_compose_line(list,separator)

Takes a ds_list of values and converts it into a standard CSV string, also escaping special characters.

csv_parse_line(line,separator)

Returns a ds_list of values from a standard CSV line.

string_parse(string,separator,ignore_null)

Splits a string based on a separator character, and returns a ds_list of split strings.
ignore_null remove empty strings from the result. Similar to csv_parse_line, but csv rules for escaping characters don't apply.