/lib/dbstruct/SpotStruct_pgsql.php

<?php

class SpotStruct_pgsql extends SpotStruct_abs {



	/*

	 * Optimize / analyze (database specific) a number of hightraffic

	 * tables.

	 * This function does not modify any schema or data

	 */

	function analyze() { 

		$this->_dbcon->rawExec("VACUUM ANALYZE spots");

		$this->_dbcon->rawExec("VACUUM ANALYZE spotsfull");

		$this->_dbcon->rawExec("VACUUM ANALYZE commentsxover");

		$this->_dbcon->rawExec("VACUUM ANALYZE commentsfull");

		$this->_dbcon->rawExec("VACUUM ANALYZE sessions");

		$this->_dbcon->rawExec("VACUUM ANALYZE filters");

		$this->_dbcon->rawExec("VACUUM ANALYZE spotteridblacklist");

		$this->_dbcon->rawExec("VACUUM ANALYZE filtercounts");

		$this->_dbcon->rawExec("VACUUM ANALYZE spotstatelist");
		$this->_dbcon->rawExec("VACUUM ANALYZE users");
		$this->_dbcon->rawExec("VACUUM ANALYZE cache");

	} # analyze

	

	/*

	 * Converts a 'spotweb' internal datatype to a 

	 * database specific datatype

	 */

	function swDtToNative($colType) {

		switch(strtoupper($colType)) {

			case 'INTEGER'				: $colType = 'integer'; break;

			case 'UNSIGNED INTEGER'		: $colType = 'bigint'; break;

			case 'BIGINTEGER'			: $colType = 'bigint'; break;

			case 'UNSIGNED BIGINTEGER'	: $colType = 'bigint'; break;

			case 'BOOLEAN'				: $colType = 'boolean'; break;

			case 'MEDIUMBLOB'			: $colType = 'bytea'; break;

		} # switch

		

		return $colType;

	} # swDtToNative 



	/*

	 * Converts a database native datatype to a spotweb native

	 * datatype

	 */

	function nativeDtToSw($colInfo) {

		switch(strtolower($colInfo)) {

			case 'integer'				: $colInfo = 'INTEGER'; break;

			case 'bigint'				: $colInfo = 'BIGINTEGER'; break;

			case 'boolean'				: $colInfo = 'BOOLEAN'; break;

			case 'bytea'				: $colInfo = 'MEDIUMBLOB'; break;

		} # switch

		

		return $colInfo;

	} # nativeDtToSw 

	

	/* checks if an index exists */

	function indexExists($idxname, $tablename) {

		$q = $this->_dbcon->arrayQuery("SELECT indexname FROM pg_indexes WHERE schemaname = CURRENT_SCHEMA() AND tablename = '%s' AND indexname = '%s'",
				Array($tablename, $idxname));
		return !empty($q);

	} # indexExists



	/* checks if a column exists */

	function columnExists($tablename, $colname) {

		$q = $this->_dbcon->arrayQuery("SELECT column_name FROM information_schema.columns 
											WHERE table_schema = CURRENT_SCHEMA() AND table_name = '%s' AND column_name = '%s'",
									Array($tablename, $colname));
		return !empty($q);

	} # columnExists



	/* checks if a fts text index exists */

	function ftsExists($ftsname, $tablename, $colList) {

		foreach($colList as $num => $col) {

			$indexInfo = $this->getIndexInfo($ftsname . '_' . $num, $tablename);

			

			if ((empty($indexInfo)) || (strtolower($indexInfo[0]['column_name']) != strtolower($col))) {

				return false;

			} # if

		} # foreach

		

		return true;

	} # ftsExists

			

	/* creates a full text index */

	function createFts($ftsname, $tablename, $colList) {

		foreach($colList as $num => $col) {

			$indexInfo = $this->getIndexInfo($ftsname . '_' . $num, $tablename);

			

			if ((empty($indexInfo)) || (strtolower($indexInfo[0]['column_name']) != strtolower($col))) {

				$this->dropIndex($ftsname . '_' . $num, $tablename);

				$this->addIndex($ftsname . '_' . $num, 'FULLTEXT', $tablename, array($col));

			} # if

		} # foreach

	} # createFts

	

	/* drops a fulltext index */

	function dropFts($ftsname, $tablename, $colList) {

		foreach($colList as $num => $col) {

			$this->dropIndex($ftsname . '_' . $num, $tablename);

		} # foreach

	} # dropFts

	

	/* returns FTS info  */

	function getFtsInfo($ftsname, $tablename, $colList) {

		$ftsList = array();

		

		foreach($colList as $num => $col) {

			$tmpIndex = $this->getIndexInfo($ftsname . '_' . $num, $tablename);

			

			if (!empty($tmpIndex)) {

				$ftsList[] = $tmpIndex[0];

			} # if

		} # foreach

		

		return $ftsList;

	} # getFtsInfo



	/*

	 * Adds an index, but first checks if the index doesn't

	 * exist already.

	 *

	 * $idxType can be either 'UNIQUE', '' or 'FULLTEXT'

	 */

	function addIndex($idxname, $idxType, $tablename, $colList) {

		if (!$this->indexExists($idxname, $tablename)) {
			switch($idxType) {
				case 'UNIQUE': {
					$this->_dbcon->rawExec("CREATE UNIQUE INDEX " . $idxname . " ON " . $tablename . "(" . implode(",", $colList) . ")");
					break;
				} # case
				
				case 'FULLTEXT' : {
					$this->_dbcon->rawExec("CREATE INDEX " . $idxname . " ON " . $tablename . " USING gin(to_tsvector('dutch', " . implode(",", $colList) . "))");
					break;
				} # case
				
				default	: {
					$this->_dbcon->rawExec("CREATE INDEX " . $idxname . " ON " . $tablename . "(" . implode(",", $colList) . ")");
				} # default
			} # switch

		} # if

	} # addIndex



	/* drops an index if it exists */

	function dropIndex($idxname, $tablename) {

		/*

		 * Make sure the table exists, else this will return an error

		 * and return a fatal

		 */

		if (!$this->tableExists($tablename)) {

			return ;

		} # if

		

		if ($this->indexExists($idxname, $tablename)) {

			$this->_dbcon->rawExec("DROP INDEX " . $idxname);

		} # if

	} # dropIndex

	

	/* adds a column if the column doesn't exist yet */

	function addColumn($colName, $tablename, $colType, $colDefault, $notNull, $collation) {

		if (!$this->columnExists($tablename, $colName)) {

			# set the DEFAULT value

			if (strlen($colDefault) != 0) {

				$colDefault = 'DEFAULT ' . $colDefault;

			} # if



			# Convert the column type to a type we use in PostgreSQL

			$colType = $this->swDtToNative($colType);



			/*

			 * Only pgsql 9.1 (only just released) supports per-column collation, so for now

			 * we ignore this 

			 */

			switch(strtolower($collation)) {

				case 'utf8'			: 

				case 'ascii'		: 

				case 'ascii_bin'	: 

				case ''			: $colSetting = ''; break;

				default			: throw new Exception("Invalid collation setting");

			} # switch

			

			# and define the 'NOT NULL' part

			switch($notNull) {

				case true		: $nullStr = 'NOT NULL'; break;

				default			: $nullStr = '';

			} # switch

			

			$this->_dbcon->rawExec("ALTER TABLE " . $tablename . 

						" ADD COLUMN " . $colName . " " . $colType . " " . $colSetting . " " . $colDefault . " " . $nullStr);

		} # if

	} # addColumn

	

	/* alters a column - does not check if the column doesn't adhere to the given definition */

	function modifyColumn($colName, $tablename, $colType, $colDefault, $notNull, $collation, $what) {

		# set the DEFAULT value

		if (strlen($colDefault) != 0) {

			$colDefault = 'DEFAULT ' . $colDefault;

		} # if



		# Convert the column type to a type we use in PostgreSQL

		$colType = $this->swDtToNative($colType);



		/*

		 * Only pgsql 9.1 (only just released) supports per-column collation, so for now

		 * we ignore this 

		 */

		switch(strtolower($collation)) {
			case 'utf8'			: 

			case 'ascii'		: 

			case 'ascii_bin'	: 

			case ''			: $colSetting = ''; break;
			default			: throw new Exception("Invalid collation setting");
		} # switch
		

		# and define the 'NOT NULL' part

		switch($notNull) {

			case true		: $nullStr = 'NOT NULL'; break;

			default			: $nullStr = '';

		} # switch

		
		# Alter the column type

		$this->_dbcon->rawExec("ALTER TABLE " . $tablename . " ALTER COLUMN " . $colName . " TYPE " . $colType);
		
		# Change the default value (if one set, else drop it)
		if (strlen($colDefault) > 0) {
			$this->_dbcon->rawExec("ALTER TABLE " . $tablename . " ALTER COLUMN " . $colName . " SET " . $colDefault);
		} else {
			$this->_dbcon->rawExec("ALTER TABLE " . $tablename . " ALTER COLUMN " . $colName . " DROP DEFAULT");
		} # if
		
		# and changes the null/not-null constraint
		if (strlen($notNull) > 0) {
			$this->_dbcon->rawExec("ALTER TABLE " . $tablename . " ALTER COLUMN " . $colName . " SET NOT NULL");
		} else {
			$this->_dbcon->rawExec("ALTER TABLE " . $tablename . " ALTER COLUMN " . $colName . " DROP NOT NULL");
		} # if
	} # modifyColumn



	/* drops a column */

	function dropColumn($colName, $tablename) {

		if ($this->columnExists($tablename, $colName)) {

			$this->_dbcon->rawExec("ALTER TABLE " . $tablename . " DROP COLUMN " . $colName);

		} # if

	} # dropColumn



	/* checks if a table exists */

	function tableExists($tablename) {

		$q = $this->_dbcon->arrayQuery("SELECT tablename FROM pg_tables WHERE schemaname = CURRENT_SCHEMA() AND (tablename = '%s')", array($tablename));
		return !empty($q);

	} # tableExists



	/* creates an empty table with only an ID field. Collation should be either UTF8 or ASCII */

	function createTable($tablename, $collation) {

		if (!$this->tableExists($tablename)) {

			/*

			 * Only pgsql 9.1 (only just released) supports per-column collation, so for now

			 * we ignore this 

			 */

			switch(strtolower($collation)) {
				case 'utf8'		: 
				case 'ascii'	: 
				case ''			: $colSetting = ''; break;
				default			: throw new Exception("Invalid collation setting");
			} # switch
		

			$this->_dbcon->rawExec("CREATE TABLE " . $tablename . " (id SERIAL PRIMARY KEY) " . $colSetting);

		} # if

	} # createTable

	

	/* drop a table */

	function dropTable($tablename) {

		if ($this->tableExists($tablename)) {

			$this->_dbcon->rawExec("DROP TABLE " . $tablename);

		} # if

	} # dropTable

	

	/* dummy - postgresql doesn't know storage engines of course */

	function alterStorageEngine($tablename, $engine) {
		return false;

	} # alterStorageEngine

	

	/* rename a table */

	function renameTable($tablename, $newTableName) {

		$this->_dbcon->rawExec("ALTER TABLE " . $tablename . " RENAME TO " . $newTableName);

	} # renameTable



	/* drop a foreign key constraint */

	function dropForeignKey($tablename, $colname, $reftable, $refcolumn, $action) {
		/* SQL from http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys */

		$q = $this->_dbcon->arrayQuery("SELECT
											tc.constraint_name AS CONSTRAINT_NAME,
											tc.table_name AS TABLE_NAME,
											tc.constraint_schema AS TABLE_SCHEMA,
											kcu.column_name AS COLUMN_NAME,
											ccu.table_name AS REFERENCED_TABLE_NAME,
											ccu.column_name AS REFERENCED_COLUMN_NAME
										FROM
											information_schema.table_constraints AS tc
											JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
											JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
										WHERE constraint_type = 'FOREIGN KEY' 
										  AND tc.TABLE_SCHEMA = CURRENT_SCHEMA()
										  AND tc.TABLE_NAME = '%s'
										  AND kcu.COLUMN_NAME = '%s'
										  AND ccu.table_name = '%s'
										  AND ccu.column_name = '%s'",
								Array($tablename, $colname, $reftable, $refcolumn));
		if (!empty($q)) {

			foreach($q as $res) {

				$this->_dbcon->rawExec("ALTER TABLE " . $tablename . " DROP CONSTRAINT " . $res['constraint_name']);

			} # foreach

		} # if

	} # dropForeignKey



	/* create a foreign key constraint */

	function addForeignKey($tablename, $colname, $reftable, $refcolumn, $action) {

		/* SQL from http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys */
		$q = $this->_dbcon->arrayQuery("SELECT
											tc.constraint_name AS CONSTRAINT_NAME,
											tc.table_name AS TABLE_NAME,
											tc.constraint_schema AS TABLE_SCHEMA,
											kcu.column_name AS COLUMN_NAME,
											ccu.table_name AS REFERENCED_TABLE_NAME,
											ccu.column_name AS REFERENCED_COLUMN_NAME
										FROM
											information_schema.table_constraints AS tc
											JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
											JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
										WHERE constraint_type = 'FOREIGN KEY' 
										  AND tc.TABLE_SCHEMA = CURRENT_SCHEMA()

										  AND tc.TABLE_NAME = '%s'

										  AND kcu.COLUMN_NAME = '%s'

										  AND ccu.table_name = '%s'

										  AND ccu.column_name = '%s'",

								Array($tablename, $colname, $reftable, $refcolumn));
		if (empty($q)) {

			$this->_dbcon->rawExec("ALTER TABLE " . $tablename . " ADD FOREIGN KEY (" . $colname . ") 

										REFERENCES " . $reftable . " (" . $refcolumn . ") " . $action);

		} # if

	} # addForeignKey



	/* Returns in a fixed format, column information */

	function getColumnInfo($tablename, $colname) {

		$q = $this->_dbcon->arrayQuery("SELECT column_name AS \"COLUMN_NAME\",

											   column_default AS \"COLUMN_DEFAULT\", 

											   is_nullable AS \"IS_NULLABLE\", 

											   data_type AS \"DATA_TYPE\", 

											   numeric_precision AS \"NUMERIC_PRECISION\", 

											   CASE 

													WHEN (data_type = 'character varying') THEN 'varchar(' || character_maximum_length || ')' 

													WHEN (data_type = 'integer') THEN 'integer' 

													WHEN (data_type = 'bigint') THEN 'bigint' 

													WHEN (data_type = 'boolean') THEN 'boolean' 

													WHEN (data_type = 'text') THEN 'text'

													WHEN (data_type = 'bytea') THEN 'bytea'

											   END as \"COLUMN_TYPE\",

   											   character_set_name AS \"CHARACTER_SET_NAME\", 

											   collation_name AS \"COLLATION_NAME\"

										FROM information_schema.COLUMNS 

										WHERE TABLE_SCHEMA = CURRENT_SCHEMA() 

										  AND TABLE_NAME = '%s'

										  AND COLUMN_NAME = '%s'",

							Array($tablename, $colname));

		if (!empty($q)) {

			$q = $q[0];



			$q['NOTNULL'] = ($q['IS_NULLABLE'] != 'YES');

			

			# a default value has to given, so make it compareable to what we define

			if ((strlen($q['COLUMN_DEFAULT']) == 0) && (is_string($q['COLUMN_DEFAULT']))) {	

				$q['COLUMN_DEFAULT'] = "''";

			} # if



			/*

			 * PostgreSQL per default explicitly typecasts the value, but

			 * we cannot do this, so we strip the default value of its typecast

			 */

			if (strpos($q['COLUMN_DEFAULT'], ':') !== false) {

				$elems = explode(':', $q['COLUMN_DEFAULT']);

				

				$q['COLUMN_DEFAULT'] = $elems[0];

			} # if

		} # if

		

		return $q;

	} # getColumnInfo

	

	/* Returns in a fixed format, index information */

	function getIndexInfo($idxname, $tablename) {
		$q = $this->_dbcon->arrayQuery("SELECT * 

										FROM pg_indexes 

										WHERE schemaname = CURRENT_SCHEMA()

										  AND tablename = '%s'

										  AND indexname = '%s'", Array($tablename, $idxname));

		if (empty($q)) {

			return array();

		} # if

		

		# a index name has to be unique

		$q = $q[0];

											

		# is the index marked as unique

		$tmpAr = explode(" ", $q['indexdef']);

		$isNotUnique = (strtolower($tmpAr[1]) != 'unique');



		# retrieve the column list and seperate the column definition per comma

		preg_match_all("/\((.*)\)/", $q['indexdef'], $tmpAr);

		

		$colList = explode(",", $tmpAr[1][0]);

		$colList = array_map('trim', $colList);

		

		# gin indexes (fulltext search) only have 1 column, so we excempt them

		$idxInfo = array();

		if (stripos($tmpAr[1][0], 'to_tsvector') === false) {

			for($i = 0; $i < count($colList); $i++) {

				$idxInfo[] = array('column_name' => $colList[$i],

								   'non_unique' => (int) $isNotUnique,

								   'index_type' => 'BTREE'

							);

			} # foreach

		} else {

			# extract the column name

			preg_match_all("/\((.*)\)/U", $colList[1], $tmpAr);

			

			# and create the index info

			$idxInfo[] = array('column_name' => $tmpAr[1][0],

							   'non_unique' => (int) $isNotUnique,

							   'index_type' => 'FULLTEXT');

		} # else



		return $idxInfo;

	} # getIndexInfo

	

} # class