CLASS_database Class Reference

The class is designed for baseline database development, functionality, and interaction. More...

Inheritance diagram for CLASS_database:

Public Member Functions
virtual	createDBandTables (in obj)
	Abstract method to create a mysql database and tables. The method should be implemented in derived classes according to the database desired. More...
function	open (in obj)
	Open the database associated with the derived class. More...
function	addTableField (in obj, in tableName, in fieldName, in fieldDefinition)
	Adds a table column to the database associated with the instantiated class. More...
function	renameField (in obj, in tableName, in oldFieldName, in newFieldName)
	Rename a table column in the database associated with the instantiated class. More...
function	create_DB (in obj)
	Builds a mysql database and sets up permissions to modify for the designated user. More...
function	dumpTable2Text (in obj, in tableName)
	Dumps the identified table to a text file using the same name and a '.txt' extension. More...
function	dumpTable (in obj, in tableName)
	Performs a system level MySQL dump of the identified table to a file with '.sql' extension. More...
function	importTable (in obj, in sqlDumpFile)
	Performs a system level mysqldump call to import the passed sql dump file. More...
function	addUser (in obj)
	Adds the user specified in the dbStruct instance variable to the the database (also specified in dbStruct) More...
function	createTable (in this, in tableName, in tableStr)
	Creates a database table using the input arguments. More...
function	dropTable (in this, in tableName)
function	selectSome (in this, in tableName, in limit)
function	query (in this, in queryStr, in varargin)
function	getColumnNames (in this, in tableName)

Static Public Member Functions
static function	close ()
	Closes the current MySQL connection. More...
static function	openDB (in dbStruct)
	Helper function for opening the MySQL database using field values provided in dbStruct. More...
static function	grantPrivileges (in dbStruct)
	Adds the user specified in the dbStruct instance variable to the the database (also specified in dbStruct)%> More...
static function	cellstr2csv (in cellOfKeys)
	Place ',' in between cell string entries for mysql select entry. string = cellstr2csv(cellString) More...
static function	cellstr2statcsv (in cellOfFields, in stat)
	Place ',' in between cell string entries for mysql select entry. More...
static function	refactorPatstudykey (in table2Refactor, in field2RefactorWith)
	Refactors a table's patstudykey using another field that is listed in the studyinfo_t table (e.g. patid). More...
static function	updateDBTableFieldValues (in tableName, in setFieldName, in setFieldValues, in whereFieldName, in whereFieldValues)
	MySQL helper function. Updates fields values for the specified table of the currently open database. This is a wrapper for the the mysql call UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]. More...
static function	updateTableEntry (in tableName, in fields, in values, in whereStmt)
	MySQL helper function. Updates fields values for the specified table of the currently open database. This is a wrapper for the the mysql call UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]. More...
static function	deleteTableEntry (in tableName, in whereStmt)
	MySQL helper function. Delete a table entry or entries from the specified table of the current database which match the the where statement provided. This is a wrapper for the the mysql call DELETE FROM table_name WHERE whereStmt. More...
static function	query2file (in q, in filename, in optional_delim)
	Writes information in mym query output q to a file. More...
static function	query2text (in q, in optional_delim)
	Outputs mym query output statment to the console or string output. More...
static function	loadCohortStruct (in struct_filename)
	Retrieves cohort descriptor data as a struct from the .inf filename provided. More...
static function	insertRecordFromStruct (in TableName, in insertStruct)
	Inserts a record into a table of the current, open, database using a struct of field name/value pairings. More...
static function	updateRecordFromStruct (in tableName, in updateStruct, in whereStmt)
	Updates a table record in the current, open, database using a struct of field name/value pairings. More...
static function	loadDatabaseStructFromInf (in inf_filename, in optional_choice)
	Retrieves database access data as a struct from the .inf filename provided. More...
static function	exportQuery2File (in query, in filename, in optional_delim)
	Exports the output of a mysql query to a file. More...
static function	makeWhereInString (in data, in dataType)

Public Attributes
Property	dbStruct
	Structure containing database accessor fields: More...

Detailed Description

The class is designed for baseline database development, functionality, and interaction.

Note

: A MySQL database must be installed on the local host for class instantiations to operate correctly.

Here are a few tips for upgrading MySQL on a Mac

• Turn off MySQL server and install new MySQL (which you should have downloaded from apache's website)
• After installation, copy the database files from the old mysql/data directory to the new mysql/data directory (i.e. /usr/local/mysql_blah_blah_old_Version/data/[database_name] to /usr/local/mysql/data/ folder.
• Open a terminal window in Mac and change directory to the mysql data folder like so:
• cd /usr/local/mysql/data
• Then for each databasse that you transferred over, give _mysql permission to access it (and not just yourself) If you transferred a database named 'CManager_DB' for example then you would type this into the terminal:
• sudo chown _mysql CManager_DB
• Do this for each database file that you copied over.
• Grant user privileges to these databases (that you just moved) again. These are lost in the upgrade. In MySQL use a "Grant all on [database name].* to [username] identified by [password]. Otherwise, from MATLAB, run the CLASS_database instance method 'addUser()'. Copy all innodb tables from the old mysql/data directory to the new one (e.g. ib_logfile0, ib_logfile1, and ibdata). From the terminal run, mysql_upgrade

Copyright Hyatt Moore IV, 2011,2012,2013,2014,2015

Member Function Documentation

function CLASS_database::addTableField	(	in	obj,
		in	tableName,
		in	fieldName,
		in	fieldDefinition
	)

Adds a table column to the database associated with the instantiated class.

Parameters

obj	CLASS_database derivded instance.
obj	tableName MySQL table to add a column to
obj	fieldName Name of the column to add to tableName
obj	fieldDefintion Definition of column being added.

Note

For example:

obj = CLASS_CManager_database();

obj.addTableField('cohortinfo_t','dockingFolder','varchar(128)');

function CLASS_database::addUser

(

in

obj

)

Adds the user specified in the dbStruct instance variable to the the database (also specified in dbStruct)

Parameters

obj	Instance of CLASS_database

static function CLASS_database::cellstr2csv ( in  cellOfKeys )

static

Place ',' in between cell string entries for mysql select entry. string = cellstr2csv(cellString)

Parameters

Cell	string of fields to select.

Return values

String

Note

example: cellString = {'A0001'; 'A0003'; 'A0008'};

selectStr = makeSelectKeysString(cellString)

ans = A0001,A0003,A0008

static function CLASS_database::cellstr2statcsv ( in  cellOfFields, in  stat  )

static

Place ',' in between cell string entries for mysql select entry.

Parameters

Cell	string of fields to select.
Optional	mysql grouping command (default is 'mean').

Return values

String

Note

example: cellString = {'A0001'; 'A0003'; 'A0008'};

selectStr = cellstr2statcsv(cellString)

ans = mean(A0001) AS A0001, mean(A0003) AS A0003, mean(A0008) AS A0008

static function CLASS_database::close ( )

static

Closes the current MySQL connection.

function CLASS_database::create_DB

(

in

obj

)

Builds a mysql database and sets up permissions to modify for the designated user.

Parameters

obj	Instance of CLASS_database

virtual CLASS_database::createDBandTables ( in  obj )

virtual

Abstract method to create a mysql database and tables. The method should be implemented in derived classes according to the database desired.

Parameters

obj	CLASS_database derived instance

function CLASS_database::createTable	(	in	this,
		in	tableName,
		in	tableStr
	)

Creates a database table using the input arguments.

Parameters

this	Instance of CLASS_database
Name	of the table to create (string)

Note

Table is dropped first if it exists.

Parameters

Column

definitions for the table.

Note

Format needs to work as follows: sprintf('CREATE TABLE IF NOT EXISTS s (s)',tableName,tableStr);

static function CLASS_database::deleteTableEntry ( in  tableName, in  whereStmt  )

static

MySQL helper function. Delete a table entry or entries from the specified table of the current database which match the the where statement provided. This is a wrapper for the the mysql call DELETE FROM table_name WHERE whereStmt.

Parameters

tableName	Name of the table to updated (string)
whereStmt	The 'where' clause (sans 'where'), which must be included (string)

Note

executes the mysql statement DELETE FROM tableName WHERE whereStmt Example: x = CLASS_CManager_database x.open(); x.deleteTableEntry('filestudyinfo_t','cohortID=1 and src_psg_filename="A0097_4 174733.EDF"'); results in the following mysql statement:

• delete from filestudyinfo_t where cohortID=1 and

function CLASS_database::dropTable	(	in	this,
		in	tableName
	)

function CLASS_database::dumpTable	(	in	obj,
		in	tableName
	)

Performs a system level MySQL dump of the identified table to a file with '.sql' extension.

Parameters

obj	Instance of CLASS_database
tableName	Name of the table (as a string) which will be dumped as a MySQL file dump.

function CLASS_database::dumpTable2Text	(	in	obj,
		in	tableName
	)

Dumps the identified table to a text file using the same name and a '.txt' extension.

Parameters

obj	Instance of CLASS_database
tableName	Name of the table (as a string) which will be dumped as a tab-delimited text file with one record per row.

static function CLASS_database::exportQuery2File ( in  query, in  filename, in  optional_delim  )

static

Exports the output of a mysql query to a file.

Parameters

query	A mysql query (string)
filename	The filename to save the MySQL results to (string).
optional_delim	Delimeter to separate each row's results by (optional). For example, ',' would separate using a comma. The default is to use a tab-delimiter (i.e. '').

function CLASS_database::getColumnNames	(	in	this,
		in	tableName
	)

static function CLASS_database::grantPrivileges ( in  dbStruct )

static

Adds the user specified in the dbStruct instance variable to the the database (also specified in dbStruct)%>

Parameters

dbStruct

A structure containing database accessor fields: name Name of the database to use (string) user Database user (string) password Password for user (string)

function CLASS_database::importTable	(	in	obj,
		in	sqlDumpFile
	)

Performs a system level mysqldump call to import the passed sql dump file.

Parameters

obj	Instance of CLASS_database
sqlDumFile	File name of the mysql dump to import (i.e. a .sql file)

static function CLASS_database::insertRecordFromStruct ( in  TableName, in  insertStruct  )

static

Inserts a record into a table of the current, open, database using a struct of field name/value pairings.

Parameters

Name	of the table to insert record into (string).
Struct	of column names and column values to insert into the specified table.

Note

Numeric values are formatted to 3 significant digits (%0.3g).

• A struct field name of 'datetimefirstadded' is given the value 'now()'.
• String values that contain double quotes inside the string, should not start and end with double quotes. @ Okay examples
• insertStruct.name = '"John"';
• insertStruct.name = 'John';
• insertStruct.name = 'John "Little mike" Doe';
• insertStruct.name = '"John \"Little mike" Doe'"; @ Not Okay example (results in mysql error)
• insertStruct.name = '"John "Little mike" Doe"';

Example:

insertStruct.name = 'Korea';

insertStruct.projectname = 'Narcolepsy';

insertStruct.location = 'Korea';

insertStruct.src_psg_extension = '.ewp';

insertStruct.is_docking_folder_grouped = 1;

insertStruct.src_foldername = '/Users/hyatt4/Documents/Sleep Project/Data/Korea';

insertStruct.src_foldertype = 'tier';

insertStruct.docking_foldername = '/Users/hyatt4/Documents/Sleep Project/Data/dock/WSC';

obj = CLASS_CManager_database();

obj.open();

obj.insertRecordFromStruct('cohortinfo_t',insertStruct);

static function CLASS_database::loadCohortStruct ( in  struct_filename )

static

Retrieves cohort descriptor data as a struct from the .inf filename provided.

Parameters

inf_filename

Full filename (i.e. path included) of either a text file containing cohort descriptor data as tab-delimited entries or an XML formatted file (with .xml extension).

Return values

cohortSstruct

A structure containing file value pairings For example, database accessor fields for a database.inf file would be: name Name of the database to use (string) user Database user (string) password Password for user (string)

static function CLASS_database::loadDatabaseStructFromInf ( in  inf_filename, in  optional_choice  )

static

Retrieves database access data as a struct from the .inf filename provided.

Parameters

inf_filename	Full filename (i.e. path included) of text file containing database accessor information 'name', 'user', 'password' as tab-delimited entries.
optional_choice	Optional index that can be provided to return the specified database preference when multiple database entries are present in the supplied inf_filename (integer)

Return values

database_struct

A structure containing database accessor fields: name Name of the database to use (string) user Database user (string) password Password for user (string)

static function CLASS_database::makeWhereInString ( in  data, in  dataType  )

static

function CLASS_database::open

(

in

obj

)

Open the database associated with the derived class.

Parameters

obj	CLASS_database derivded instance.

static function CLASS_database::openDB ( in  dbStruct )

static

Helper function for opening the MySQL database using field values provided in dbStruct.

Parameters

dbStruct

A structure containing database accessor fields: name Name of the database to use (string) user Database user (string) password Password for user (string)

function CLASS_database::query	(	in	this,
		in	queryStr,
		in	varargin
	)

static function CLASS_database::query2file ( in  q, in  filename, in  optional_delim  )

static

Writes information in mym query output q to a file.

Parameters

q	The mym query result to be written to file
filename	Name of the file to store data to (will be created if it does not already exist, or overwrite existing contents
optional_delim	Optional string delimiter to separate output fields The default is tab delimited (i.e. '')

Note

set optional_delim to ',' for comma separated values.

static function CLASS_database::query2text ( in  q, in  optional_delim  )

static

Outputs mym query output statment to the console or string output.

Parameters

q	The mym query result to be displayed
optional_delim	Optional string delimiter to separate output fields The default is tab delimited (i.e. '')

Note

set optional_delim to ',' for comma separated values.

Return values

strout

Stores the output string when provided.

static function CLASS_database::refactorPatstudykey ( in  table2Refactor, in  field2RefactorWith  )

static

Refactors a table's patstudykey using another field that is listed in the studyinfo_t table (e.g. patid).

Parameters

obj	Instance of CLASS_database
table2refactor	Table name whose patstudykey is to be refactored
fieldToRefactorAgainst	The field which is used as an alternate key into the studyinfo_t which also identifies a uninque record in the table2refactor table.

Note

patid is the default value for field2RefactorAgainst

A connection must already exist and be open before refactorPatstudykey is invoked.

function CLASS_database::renameField	(	in	obj,
		in	tableName,
		in	oldFieldName,
		in	newFieldName
	)

Rename a table column in the database associated with the instantiated class.

Parameters

obj	CLASS_database derivded instance.
obj	tableName MySQL table to add a column to
obj	oldFieldName Name of the column to change from.
obj	newFieldName New name to change the column to.

Note

For example:

obj = CLASS_CManager_database();

obj.renameField('cohortinfo_t','dockingFolder','docking_foldername');

function CLASS_database::selectSome	(	in	this,
		in	tableName,
		in	limit
	)

static function CLASS_database::updateDBTableFieldValues ( in  tableName, in  setFieldName, in  setFieldValues, in  whereFieldName, in  whereFieldValues  )

static

MySQL helper function. Updates fields values for the specified table of the currently open database. This is a wrapper for the the mysql call UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause].

Parameters

tableName	Name of the table to updated (string)
setFieldName	(string)
setFieldValues	(array float)
whereFieldName	(string)
whereFieldValues	(integer)

Note

executes the mysql statement UPDATE tableName SET setFieldName=setFieldValues(k) WHERE whereFieldName=whereFieldValues

static function CLASS_database::updateRecordFromStruct ( in  tableName, in  updateStruct, in  whereStmt  )

static

Updates a table record in the current, open, database using a struct of field name/value pairings.

Parameters

Name	of the table to insert record into (string).
Struct	of column names and column values to update in the table.
A	where statement (sans "where") to locate the record to update.

Note

Numeric values are formatted to 3 significant digits (%0.3g).

• A struct field name of 'datetimefirstadded' is given the value 'now()'.
• String values should include double quotes (e.g. insertStruct.name = '"John"';

Example:

updateStruct.name = 'Korea';

updateStruct.projectname = 'Narcolepsy';

updateStruct.location = 'Korea';

updateStruct.src_psg_extension = '.ewp';

updateStruct.is_docking_folder_grouped = 1;

updateStruct.src_foldername = '/Users/hyatt4/Documents/Sleep Project/Data/Korea';

updateStruct.src_foldertype = 'tier';

updateStruct.docking_foldername = '/Users/hyatt4/Documents/Sleep Project/Data/dock/WSC';

whereStmt = 'cohortid = 0';

obj = CLASS_CManager_database();

obj.open();

obj.updateRecordFromStruct('cohortinfo_t',updateStruct,whereStmt);

static function CLASS_database::updateTableEntry ( in  tableName, in  fields, in  values, in  whereStmt  )

static

MySQL helper function. Updates fields values for the specified table of the currently open database. This is a wrapper for the the mysql call UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause].

Parameters

tableName	Name of the table to updated (string)
fields	Cell of column label(s) (strings)
values	Cell of column values that correspond to fields' column labeling.
whereStmt	The 'where' clause (sans 'where'), which must be included (string)

Note

executes the mysql statement UPDATE tableName SET fields{1}=values{1}[, fields{k}=values{k}] WHERE whereStmt Example: x = CLASS_CManager_database x.open(); x.updateTableEntry('cohortinfo_t',{'dockingFolder','src_foldertype'},{'"/Volumes/BUFFALO 500/dock"','"flat"'},'cohortID=1');

• x.updateTable('cohortinfo_t','src_foldername','"/Volumes/BUFFALO 500/WSC"','cohortID=1'); results in the following mysql statement:
• update cohortinfo_t set src_foldername="/Volumes/BUFFALO 500/WSC" WHERE cohortID=1 and src_psg_filename="A0097_4 174733.EDF"')

Member Data Documentation

Property CLASS_database::dbStruct

Structure containing database accessor fields:

• name Name of the database to use (string)
• user Database user (string)
• password Password for user (string)

---

The documentation for this class was generated from the following file:

• c_version_comments/CLASS_database.cpp