Database
[Data Storage]

Max's database support currently consists of a SQLite ( http://sqlite.org ) extension which is loaded dynamically by Max at launch time. More...

Collaboration diagram for Database:

Typedefs
typedef t_object	t_database
	A database object.
typedef t_object	t_db_result
	A database result object.
typedef t_object	t_db_view
	A database view object.
Functions
BEGIN_USING_C_LINKAGE t_max_err	db_open (t_symbol dbname, const char fullpath, t_database **db)
	Create an instance of a database.
t_max_err	db_close (t_database **db)
	Close an open database.
t_max_err	db_query (t_database db, t_db_result dbresult, const char sql,...)
	Execute a SQL query on the database.
t_max_err	db_query_silent (t_database db, t_db_result dbresult, const char sql,...)
	Execute a SQL query on the database, temporarily overriding the database's error logging attribute.
t_max_err	db_query_getlastinsertid (t_database db, long id)
	Determine the id (key) number for the most recent INSERT query executed on the database.
t_max_err	db_query_table_new (t_database db, const char tablename)
	Create a new table in a database.
t_max_err	db_query_table_addcolumn (t_database db, const char tablename, const char columnname, const char columntype, const char *flags)
	Add a new column to an existing table in a database.
t_max_err	db_transaction_start (t_database *db)
	Begin a database transaction.
t_max_err	db_transaction_end (t_database *db)
	Finalize a database transaction.
t_max_err	db_transaction_flush (t_database *db)
	Force any open transactions to close.
t_max_err	db_view_create (t_database db, const char sql, t_db_view **dbview)
	A database view is a way of looking at a particular set of records in the database.
t_max_err	db_view_remove (t_database db, t_db_view *dbview)
	Remove a database view created using db_view_create().
t_max_err	db_view_getresult (t_db_view dbview, t_db_result *result)
	Fetch the pointer for a t_db_view's query result.
t_max_err	db_view_setquery (t_db_view dbview, char newquery)
	Set the query used by the view.
char **	db_result_nextrecord (t_db_result *result)
	Return the next record from a set of results that you are walking.
void	db_result_reset (t_db_result *result)
	Reset the interface for walking a result's record list to the first record.
void	db_result_clear (t_db_result *result)
	Zero-out a database result.
long	db_result_numrecords (t_db_result *result)
	Return a count of all records in the query result.
long	db_result_numfields (t_db_result *result)
	Return a count of all fields (columns) in the query result.
char *	db_result_fieldname (t_db_result *result, long fieldindex)
	Return the name of a field specified by its index number.
char *	db_result_string (t_db_result *result, long recordindex, long fieldindex)
	Return a single value from a result according to its index and field coordinates.
long	db_result_long (t_db_result *result, long recordindex, long fieldindex)
	Return a single value from a result according to its index and field coordinates.
float	db_result_float (t_db_result *result, long recordindex, long fieldindex)
	Return a single value from a result according to its index and field coordinates.
unsigned long	db_result_datetimeinseconds (t_db_result *result, long recordindex, long fieldindex)
	Return a single value from a result according to its index and field coordinates.
void	db_util_stringtodate (const char string, unsigned long date)
	A utility to convert from a sql datetime string into seconds.
void	db_util_datetostring (const unsigned long date, char *string)
	A utility to convert from seconds into a sql-ready datetime string.

Detailed Description

Max's database support currently consists of a SQLite ( http://sqlite.org ) extension which is loaded dynamically by Max at launch time.

Because it is loaded dynamically, all interfacing with the sqlite object relies on Max's message passing interface, using object_method() and related functions.

For most common database needs, a C-interface is defined in the ext_database.h header file and implemented in the ext_database.c source file. The functions defined in this interface wrap the message passing calls and provide a convenient means by which you can work with databases. ext_database.c is located in the 'common' folder inside of the 'max-includes' folder. If you use any of the functions defined ext_database.h, you will need to add ext_database.c to your project.

Typedef Documentation

typedef t_object t_database

A database object.

Use db_open() and db_close() to create and free database objects.

Definition at line 23 of file ext_database.h.

typedef t_object t_db_result

A database result object.

This is what the database object returns when a query is executed.

Definition at line 29 of file ext_database.h.

typedef t_object t_db_view

A database view object.

A database view wraps a query and a result for a given database, and is always updated and in-sync with the database.

Definition at line 35 of file ext_database.h.

Function Documentation

t_max_err db_close ( t_database ** db )

Close an open database.

Parameters:

db

The address of the t_database pointer for your database instance. The pointer will be freed and set NULL upon return.

Returns:: An error code.

Definition at line 70 of file ext_database.c.

References MAX_ERR_NONE, and object_free().

Here is the call graph for this function:

BEGIN_USING_C_LINKAGE t_max_err db_open	(	t_symbol *	dbname,
		const char *	fullpath,
		t_database **	db
	)

Create an instance of a database.

Parameters:

	dbname	The name of the database.
	fullpath	If a database with this dbname is not already open, this will specify a full path to the location where the database is stored on disk. If NULL is passed for this argument, the database will reside in memory only. The path should be formatted as a Max style path.
	db	The address of a t_database pointer that will be set to point to the new database instance. If the pointer is not NULL, then it will be treated as a pre-existing database instance and thus will be freed.

Returns:: An error code.

Definition at line 18 of file ext_database.c.

References atom_setlong(), atom_setsym(), gensym(), MAX_ERR_GENERIC, MAX_ERR_NONE, MAX_PATH_CHARS, object_free(), object_new_typed(), path_nameconform(), PATH_TYPE_ABSOLUTE, and strncpy_zero().

Here is the call graph for this function:

t_max_err db_query	(	t_database *	db,
		t_db_result **	dbresult,
		const char *	sql,
			...
	)

Execute a SQL query on the database.

Parameters:

	db	The t_database pointer for your database instance.
	dbresult	The address of a t_db_result pointer. If the pointer is passed-in set to NULL then a new dbresult will be created. If the pointer is not NULL then it is assumed to be a valid dbresult, which will be filled in with the query results. When you are done with the dbresult you should free it with object_free().
	sql	A C-string containing a valid SQL query, possibly with sprintf() formatting codes.
	...	If an sprintf() formatting codes are used in the sql string, these values will be interpolated into the sql string.

Returns:: An error code.

Definition at line 78 of file ext_database.c.

References cpost(), MAX_ERR_GENERIC, and object_method().

Referenced by db_query_silent(), and db_query_table_new().

Here is the call graph for this function:

t_max_err db_query_getlastinsertid	(	t_database *	db,
		long *	id
	)

Determine the id (key) number for the most recent INSERT query executed on the database.

Parameters:

	db	The t_database pointer for your database instance.
	id	The address of a variable to hold the result on return.

Returns:: An error code.

Definition at line 125 of file ext_database.c.

References MAX_ERR_NONE, and object_method().

Here is the call graph for this function:

t_max_err db_query_silent	(	t_database *	db,
		t_db_result **	dbresult,
		const char *	sql,
			...
	)

Execute a SQL query on the database, temporarily overriding the database's error logging attribute.

Parameters:

	db	The t_database pointer for your database instance.
	dbresult	The address of a t_db_result pointer. If the pointer is passed-in set to NULL then a new dbresult will be created. If the pointer is not NULL then it is assumed to be a valid dbresult, which will be filled in with the query results. When you are done with the dbresult you should free it with object_free().
	sql	A C-string containing a valid SQL query, possibly with sprintf() formatting codes.
	...	If an sprintf() formatting codes are used in the sql string, these values will be interpolated into the sql string.

Returns:: An error code.

Definition at line 107 of file ext_database.c.

References db_query(), object_attr_getlong(), and object_attr_setlong().

Referenced by db_query_table_addcolumn().

Here is the call graph for this function:

t_max_err db_query_table_addcolumn	(	t_database *	db,
		const char *	tablename,
		const char *	columnname,
		const char *	columntype,
		const char *	flags
	)

Add a new column to an existing table in a database.

Parameters:

	db	The t_database pointer for your database instance.
	tablename	The name of the table to which the column should be added.
	columnname	The name to use for the new column.
	columntype	The SQL type for the data that will be stored in the column. For example: "INTEGER" or "VARCHAR"
	flags	If you wish to specify any additional information for the column, then pass that here. Otherwise pass NULL.

Returns:: An error code.

Definition at line 146 of file ext_database.c.

References db_query_silent().

Here is the call graph for this function:

t_max_err db_query_table_new	(	t_database *	db,
		const char *	tablename
	)

Create a new table in a database.

Parameters:

	db	The t_database pointer for your database instance.
	tablename	The name to use for the new table. The new table will be created with one column, which holds the primary key for the table, and is named according the form {tablename}_id.

Returns:: An error code.

Definition at line 132 of file ext_database.c.

References db_query(), and strncpy_zero().

Here is the call graph for this function:

void db_result_clear ( t_db_result * result )

Zero-out a database result.

Parameters:

result

The t_db_result pointer for your query results.

Definition at line 229 of file ext_database.c.

References object_method().

Here is the call graph for this function:

unsigned long db_result_datetimeinseconds	(	t_db_result *	result,
		long	recordindex,
		long	fieldindex
	)

Return a single value from a result according to its index and field coordinates.

The value will be coerced from an expected datetime field into seconds.

Parameters:

	result	The t_db_result pointer for your query results.
	recordindex	The zero-based index number of the record (row) in the result.
	fieldindex	The zero-based index number of the field (column) in the result.

Returns:: The datetime represented in seconds.

Definition at line 280 of file ext_database.c.

References db_result_string(), and db_util_stringtodate().

Here is the call graph for this function:

char* db_result_fieldname	(	t_db_result *	result,
		long	fieldindex
	)

Return the name of a field specified by its index number.

Parameters:

	result	The t_db_result pointer for your query results.
	fieldindex	The zero-based index number of the field (column) in the result.

Returns:: A C-String with the name of the field.

Definition at line 250 of file ext_database.c.

References object_method().

Here is the call graph for this function:

float db_result_float	(	t_db_result *	result,
		long	recordindex,
		long	fieldindex
	)

Return a single value from a result according to its index and field coordinates.

Parameters:

	result	The t_db_result pointer for your query results.
	recordindex	The zero-based index number of the record (row) in the result.
	fieldindex	The zero-based index number of the field (column) in the result.

Returns:: The content of the specified cell from the result scanned out to a float.

Definition at line 270 of file ext_database.c.

References object_method().

Here is the call graph for this function:

long db_result_long	(	t_db_result *	result,
		long	recordindex,
		long	fieldindex
	)

Return a single value from a result according to its index and field coordinates.

Parameters:

	result	The t_db_result pointer for your query results.
	recordindex	The zero-based index number of the record (row) in the result.
	fieldindex	The zero-based index number of the field (column) in the result.

Returns:: The content of the specified cell from the result scanned out to a long int.

Definition at line 260 of file ext_database.c.

References object_method().

Here is the call graph for this function:

char** db_result_nextrecord ( t_db_result * result )

Return the next record from a set of results that you are walking.

When you are returned a result from a query of the database, the result is prepared for walking the results from the beginning. You can also reset the result manually to the beginning of the record list by calling db_result_reset().

Parameters:

result

The t_db_result pointer for your query results.

Returns:: An array of C-Strings with the values for every requested column (field) of a database record. To find out how many columns are represented in the array, use db_result_numfields().

Definition at line 216 of file ext_database.c.

References object_method().

Here is the call graph for this function:

long db_result_numfields ( t_db_result * result )

Return a count of all fields (columns) in the query result.

Parameters:

result

The t_db_result pointer for your query results.

Returns:: The count of fields in the query result.

Definition at line 242 of file ext_database.c.

References object_method().

Here is the call graph for this function:

long db_result_numrecords ( t_db_result * result )

Return a count of all records in the query result.

Parameters:

result

The t_db_result pointer for your query results.

Returns:: The count of records in the query result.

Definition at line 234 of file ext_database.c.

References object_method().

Here is the call graph for this function:

void db_result_reset ( t_db_result * result )

Reset the interface for walking a result's record list to the first record.

Parameters:

result

The t_db_result pointer for your query results.

Definition at line 224 of file ext_database.c.

References object_method().

Here is the call graph for this function:

char* db_result_string	(	t_db_result *	result,
		long	recordindex,
		long	fieldindex
	)

Return a single value from a result according to its index and field coordinates.

Parameters:

	result	The t_db_result pointer for your query results.
	recordindex	The zero-based index number of the record (row) in the result.
	fieldindex	The zero-based index number of the field (column) in the result.

Returns:: A C-String with the content of the specified cell in the result.

Definition at line 255 of file ext_database.c.

References object_method().

Referenced by db_result_datetimeinseconds().

Here is the call graph for this function:

t_max_err db_transaction_end ( t_database * db )

Finalize a database transaction.

Parameters:

db

The t_database pointer for your database instance.

Returns:: An error code.

Definition at line 160 of file ext_database.c.

References object_method().

Here is the call graph for this function:

t_max_err db_transaction_flush ( t_database * db )

Force any open transactions to close.

Parameters:

db

The t_database pointer for your database instance.

Returns:: An error code.

Definition at line 165 of file ext_database.c.

References object_method().

Here is the call graph for this function:

t_max_err db_transaction_start ( t_database * db )

Begin a database transaction.

When you are working with a file-based database, then the database will not be flushed to disk until db_transacation_end() is called. This means that you can _much_ more efficiently execute a sequence of queries in one transaction rather than independently.

That database object reference counts transactions, so it is possible nest calls to db_transacation_start() and db_transacation_end(). It is important to balance all calls with db_transacation_end() or the database contents will never be flushed to disk.

Parameters:

db

The t_database pointer for your database instance.

Returns:: An error code.

Definition at line 155 of file ext_database.c.

References object_method().

Here is the call graph for this function:

void db_util_datetostring	(	const unsigned long	date,
		char *	string
	)

A utility to convert from seconds into a sql-ready datetime string.

Parameters:

	date	The datetime represented in seconds.
	string	The address of a valid C-string whose contents will be set to a SQL-ready string format upon return.

Definition at line 303 of file ext_database.c.

References t_datetime::day, t_datetime::hour, t_datetime::minute, t_datetime::month, t_datetime::second, systime_secondstodate(), and t_datetime::year.

Here is the call graph for this function:

void db_util_stringtodate	(	const char *	string,
		unsigned long *	date
	)

A utility to convert from a sql datetime string into seconds.

Parameters:

	string	A C-string containing a date and time in SQL format.
	date	The datetime represented in seconds upon return.

Definition at line 293 of file ext_database.c.

References t_datetime::day, t_datetime::hour, t_datetime::minute, t_datetime::month, t_datetime::second, systime_datetoseconds(), and t_datetime::year.

Referenced by db_result_datetimeinseconds().

Here is the call graph for this function:

t_max_err db_view_create	(	t_database *	db,
		const char *	sql,
		t_db_view **	dbview
	)

A database view is a way of looking at a particular set of records in the database.

This particular set of records is defined with a standard SQL query, and the view maintains a copy of the results of the query internally. Any time the database is modified the internal result set is updated, and any objects listening to the view are notified via object_notify().

Parameters:

	db	The t_database pointer for your database instance.
	sql	A SQL query that defines the set of results provided by the view.
	dbview	The address of a NULL t_db_view pointer which will be set with the new view upon return.

Returns:: An error code.

Definition at line 173 of file ext_database.c.

References gensym(), MAX_ERR_GENERIC, MAX_ERR_NONE, and object_method().

Here is the call graph for this function:

t_max_err db_view_getresult	(	t_db_view *	dbview,
		t_db_result **	result
	)

Fetch the pointer for a t_db_view's query result.

Parameters:

	dbview	The t_db_view pointer for your database view instance.
	result	The address of a pointer to a t_db_result object. This pointer will be overwritten with the view's result pointer upon return.

Returns:: An error code.

Definition at line 196 of file ext_database.c.

References gensym(), MAX_ERR_GENERIC, MAX_ERR_NONE, and object_method().

Here is the call graph for this function:

t_max_err db_view_remove	(	t_database *	db,
		t_db_view **	dbview
	)

Remove a database view created using db_view_create().

Parameters:

	db	The t_database pointer for your database instance for which this view was created.
	dbview	The address of the t_db_view pointer for the view. This pointer will be freed and set NULL upon return.