<?php

[0] Fix | Delete

/**

[1] Fix | Delete

* Implementation of database access

[2] Fix | Delete

*

[3] Fix | Delete

* @category DMS

[4] Fix | Delete

* @package LetoDMS_Core

[5] Fix | Delete

* @license GPL 2

[6] Fix | Delete

* @version @version@

[7] Fix | Delete

* @author Uwe Steinmann <uwe@steinmann.cx>

[8] Fix | Delete

* @copyright Copyright (C) 2002-2005 Markus Westphal, 2006-2008 Malcolm Cowe,

[9] Fix | Delete

* 2010 Matteo Lucarelli, 2010 Uwe Steinmann

[10] Fix | Delete

* @version Release: @package_version@

[11] Fix | Delete

*/

[12] Fix | Delete

[13] Fix | Delete

/**

[14] Fix | Delete

* Include the adodb database abstraction

[15] Fix | Delete

*/

[16] Fix | Delete

require_once "[[softpath]]/adodb/adodb.inc.php";

[17] Fix | Delete

/** @noinspection PhpUndefinedClassInspection */

[18] Fix | Delete

[19] Fix | Delete

/**

[20] Fix | Delete

* Class to represent the database access for the document management

[21] Fix | Delete

*

[22] Fix | Delete

* @category DMS

[23] Fix | Delete

* @package LetoDMS_Core

[24] Fix | Delete

* @author Markus Westphal, Malcolm Cowe, Matteo Lucarelli, Uwe Steinmann <uwe@steinmann.cx>

[25] Fix | Delete

* @copyright Copyright (C) 2002-2005 Markus Westphal, 2006-2008 Malcolm Cowe, 2010 Matteo Lucarelli, 2010 Uwe Steinmann

[26] Fix | Delete

* @version Release: @package_version@

[27] Fix | Delete

*/

[28] Fix | Delete

class LetoDMS_Core_DatabaseAccess {

[29] Fix | Delete

var $_debug;

[30] Fix | Delete

var $_driver;

[31] Fix | Delete

var $_hostname;

[32] Fix | Delete

var $_database;

[33] Fix | Delete

var $_user;

[34] Fix | Delete

var $_passw;

[35] Fix | Delete

var $_conn;

[36] Fix | Delete

var $_connected;

[37] Fix | Delete

var $_ttreviewid;

[38] Fix | Delete

var $_ttapproveid;

[39] Fix | Delete

var $_ttstatid;

[40] Fix | Delete

var $_ttcontentid;

[41] Fix | Delete

var $_intransaction;

[42] Fix | Delete

[43] Fix | Delete

/*

[44] Fix | Delete

Backup functions

[45] Fix | Delete

*/

[46] Fix | Delete

[47] Fix | Delete

/**

[48] Fix | Delete

* Return list of all database tables

[49] Fix | Delete

*

[50] Fix | Delete

* This function is used to retrieve a list of database tables for backup

[51] Fix | Delete

*

[52] Fix | Delete

* @return array list of table names

[53] Fix | Delete

*/

[54] Fix | Delete

function TableList() {

[55] Fix | Delete

return $this->_conn->MetaTables("TABLES");

[56] Fix | Delete

}

[57] Fix | Delete

[58] Fix | Delete

/**

[59] Fix | Delete

* Constructor of LetoDMS_Core_DatabaseAccess

[60] Fix | Delete

*

[61] Fix | Delete

* Sets all database parameters but does not connect.

[62] Fix | Delete

*

[63] Fix | Delete

* @param string $driver the database type e.g. mysql, sqlite

[64] Fix | Delete

* @param string $hostname host of database server

[65] Fix | Delete

* @param string $user name of user having access to database

[66] Fix | Delete

* @param string $passw password of user

[67] Fix | Delete

* @param bool|string $database name of database

[68] Fix | Delete

*/

[69] Fix | Delete

function __construct($driver, $hostname, $user, $passw, $database = false) {

[70] Fix | Delete

$this->_driver = $driver;

[71] Fix | Delete

$this->_hostname = $hostname;

[72] Fix | Delete

$this->_database = $database;

[73] Fix | Delete

$this->_user = $user;

[74] Fix | Delete

$this->_passw = $passw;

[75] Fix | Delete

$this->_connected = false;

[76] Fix | Delete

$this->_intransaction = 0;

[77] Fix | Delete

// $tt*****id is a hack to ensure that we do not try to create the

[78] Fix | Delete

// temporary table twice during a single connection. Can be fixed by

[79] Fix | Delete

// using Views (MySQL 5.0 onward) instead of temporary tables.

[80] Fix | Delete

// CREATE ... IF NOT EXISTS cannot be used because it has the

[81] Fix | Delete

// unpleasant side-effect of performing the insert again even if the

[82] Fix | Delete

// table already exists.

[83] Fix | Delete

//

[84] Fix | Delete

// See createTemporaryTable() method for implementation.

[85] Fix | Delete

$this->_ttreviewid = false;

[86] Fix | Delete

$this->_ttapproveid = false;

[87] Fix | Delete

$this->_ttstatid = false;

[88] Fix | Delete

$this->_ttcontentid = false;

[89] Fix | Delete

$this->_debug = false;

[90] Fix | Delete

}

[91] Fix | Delete

[92] Fix | Delete

/**

[93] Fix | Delete

* Connect to database

[94] Fix | Delete

*

[95] Fix | Delete

* @return boolean true if connection could be established, otherwise false

[96] Fix | Delete

*/

[97] Fix | Delete

function connect() { /* {{{ */

[98] Fix | Delete

$this->_conn = ADONewConnection($this->_driver);

[99] Fix | Delete

if ($this->_database)

[100] Fix | Delete

$this->_conn->Connect($this->_hostname, $this->_user, $this->_passw, $this->_database);

[101] Fix | Delete

else

[102] Fix | Delete

$this->_conn->Connect($this->_hostname, $this->_user, $this->_passw);

[103] Fix | Delete

[104] Fix | Delete

if (!$this->_conn)

[105] Fix | Delete

return false;

[106] Fix | Delete

[107] Fix | Delete

$this->_conn->SetFetchMode(ADODB_FETCH_ASSOC);

[108] Fix | Delete

$this->_conn->Execute('SET NAMES utf8');

[109] Fix | Delete

$this->_connected = true;

[110] Fix | Delete

return true;

[111] Fix | Delete

} /* }}} */

[112] Fix | Delete

[113] Fix | Delete

/**

[114] Fix | Delete

* Make sure a database connection exisits

[115] Fix | Delete

*

[116] Fix | Delete

* This function checks for a database connection. If it does not exists

[117] Fix | Delete

* it will reconnect.

[118] Fix | Delete

*

[119] Fix | Delete

* @return boolean true if connection is established, otherwise false

[120] Fix | Delete

*/

[121] Fix | Delete

function ensureConnected() { /* {{{ */

[122] Fix | Delete

if (!$this->_connected) return $this->connect();

[123] Fix | Delete

else return true;

[124] Fix | Delete

} /* }}} */

[125] Fix | Delete

[126] Fix | Delete

/**

[127] Fix | Delete

* Sanitize String used in database operations

[128] Fix | Delete

*

[129] Fix | Delete

* @param string $text

[130] Fix | Delete

* @return string sanitized string

[131] Fix | Delete

*/

[132] Fix | Delete

function qstr($text) { /* {{{ */

[133] Fix | Delete

return $this->_conn->qstr($text);

[134] Fix | Delete

} /* }}} */

[135] Fix | Delete

[136] Fix | Delete

[137] Fix | Delete

/**

[138] Fix | Delete

* Execute SQL query and return result

[139] Fix | Delete

*

[140] Fix | Delete

* Call this function only with sql query which return data records.

[141] Fix | Delete

*

[142] Fix | Delete

* @param string $queryStr sql query

[143] Fix | Delete

* @return array|boolean data if query could be executed otherwise false

[144] Fix | Delete

*/

[145] Fix | Delete

function getResultArray($queryStr) { /* {{{ */

[146] Fix | Delete

/** @noinspection PhpUnusedLocalVariableInspection */

[147] Fix | Delete

$resArr = array();

[148] Fix | Delete

[149] Fix | Delete

$res = $this->_conn->Execute($queryStr);

[150] Fix | Delete

if (!$res) {

[151] Fix | Delete

if($this->_debug)

[152] Fix | Delete

echo "error: ".$queryStr."<br />";

[153] Fix | Delete

return false;

[154] Fix | Delete

}

[155] Fix | Delete

$resArr = $res->GetArray();

[156] Fix | Delete

$res->Close();

[157] Fix | Delete

return $resArr;

[158] Fix | Delete

} /* }}} */

[159] Fix | Delete

[160] Fix | Delete

/**

[161] Fix | Delete

* Execute SQL query

[162] Fix | Delete

*

[163] Fix | Delete

* Call this function only with sql query which do not return data records.

[164] Fix | Delete

*

[165] Fix | Delete

* @param string $queryStr sql query

[166] Fix | Delete

* @return bool true if query could be executed otherwise false

[167] Fix | Delete

* @internal param bool $silent not used anymore. This was used when this method

[168] Fix | Delete

* still issued an error message

[169] Fix | Delete

*/

[170] Fix | Delete

function getResult($queryStr) { /* {{{ */

[171] Fix | Delete

$res = $this->_conn->Execute($queryStr);

[172] Fix | Delete

if(!$res) {

[173] Fix | Delete

if($this->_debug)

[174] Fix | Delete

echo "error: ".$queryStr."<br />";

[175] Fix | Delete

}

[176] Fix | Delete

[177] Fix | Delete

return $res;

[178] Fix | Delete

} /* }}} */

[179] Fix | Delete

[180] Fix | Delete

/**

[181] Fix | Delete

* Return the id of the last instert record

[182] Fix | Delete

*

[183] Fix | Delete

* @return integer id used in last autoincrement

[184] Fix | Delete

*/

[185] Fix | Delete

function getInsertID() { /* {{{ */

[186] Fix | Delete

return $this->_conn->Insert_ID();

[187] Fix | Delete

} /* }}} */

[188] Fix | Delete

[189] Fix | Delete

function startTransaction() { /* {{{ */

[190] Fix | Delete

if(!$this->_intransaction) {

[191] Fix | Delete

$this->_conn->BeginTrans();

[192] Fix | Delete

}

[193] Fix | Delete

$this->_intransaction++;

[194] Fix | Delete

} /* }}} */

[195] Fix | Delete

[196] Fix | Delete

function rollbackTransaction() { /* {{{ */

[197] Fix | Delete

if($this->_intransaction == 1) {

[198] Fix | Delete

$this->_conn->RollbackTrans();

[199] Fix | Delete

}

[200] Fix | Delete

$this->_intransaction--;

[201] Fix | Delete

} /* }}} */

[202] Fix | Delete

[203] Fix | Delete

function commitTransaction() { /* {{{ */

[204] Fix | Delete

if($this->_intransaction == 1) {

[205] Fix | Delete

$this->_conn->CommitTrans();

[206] Fix | Delete

}

[207] Fix | Delete

$this->_intransaction--;

[208] Fix | Delete

} /* }}} */

[209] Fix | Delete

[210] Fix | Delete

function getErrorMsg() { /* {{{ */

[211] Fix | Delete

return $this->_conn->ErrorMsg();

[212] Fix | Delete

} /* }}} */

[213] Fix | Delete

[214] Fix | Delete

function getErrorNo() { /* {{{ */

[215] Fix | Delete

return $this->_conn->ErrorNo();

[216] Fix | Delete

} /* }}} */

[217] Fix | Delete

[218] Fix | Delete

/**

[219] Fix | Delete

* Create various temporary tables to speed up and simplify sql queries

[220] Fix | Delete

* @param $tableName

[221] Fix | Delete

* @param bool $override

[222] Fix | Delete

* @return bool

[223] Fix | Delete

*/

[224] Fix | Delete

function createTemporaryTable($tableName, $override=false) { /* {{{ */

[225] Fix | Delete

if (!strcasecmp($tableName, "ttreviewid")) {

[226] Fix | Delete

$queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttreviewid` (PRIMARY KEY (`reviewID`), INDEX (`maxLogID`)) ".

[227] Fix | Delete

"SELECT `tblDocumentReviewLog`.`reviewID`, ".

[228] Fix | Delete

"MAX(`tblDocumentReviewLog`.`reviewLogID`) AS `maxLogID` ".

[229] Fix | Delete

"FROM `tblDocumentReviewLog` ".

[230] Fix | Delete

"GROUP BY `tblDocumentReviewLog`.`reviewID` ".

[231] Fix | Delete

"ORDER BY `tblDocumentReviewLog`.`reviewLogID`";

[232] Fix | Delete

if (!$this->_ttreviewid) {

[233] Fix | Delete

if (!$this->getResult($queryStr))

[234] Fix | Delete

return false;

[235] Fix | Delete

$this->_ttreviewid=true;

[236] Fix | Delete

}

[237] Fix | Delete

else {

[238] Fix | Delete

if (is_bool($override) && $override) {

[239] Fix | Delete

if (!$this->getResult("DELETE FROM `ttreviewid`"))

[240] Fix | Delete

return false;

[241] Fix | Delete

if (!$this->getResult($queryStr))

[242] Fix | Delete

return false;

[243] Fix | Delete

}

[244] Fix | Delete

}

[245] Fix | Delete

return $this->_ttreviewid;

[246] Fix | Delete

}

[247] Fix | Delete

else if (!strcasecmp($tableName, "ttapproveid")) {

[248] Fix | Delete

$queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttapproveid` (PRIMARY KEY (`approveID`), INDEX (`maxLogID`)) ".

[249] Fix | Delete

"SELECT `tblDocumentApproveLog`.`approveID`, ".

[250] Fix | Delete

"MAX(`tblDocumentApproveLog`.`approveLogID`) AS `maxLogID` ".

[251] Fix | Delete

"FROM `tblDocumentApproveLog` ".

[252] Fix | Delete

"GROUP BY `tblDocumentApproveLog`.`approveID` ".

[253] Fix | Delete

"ORDER BY `tblDocumentApproveLog`.`approveLogID`";

[254] Fix | Delete

if (!$this->_ttapproveid) {

[255] Fix | Delete

if (!$this->getResult($queryStr))

[256] Fix | Delete

return false;

[257] Fix | Delete

$this->_ttapproveid=true;

[258] Fix | Delete

}

[259] Fix | Delete

else {

[260] Fix | Delete

if (is_bool($override) && $override) {

[261] Fix | Delete

if (!$this->getResult("DELETE FROM `ttapproveid`"))

[262] Fix | Delete

return false;

[263] Fix | Delete

if (!$this->getResult($queryStr))

[264] Fix | Delete

return false;

[265] Fix | Delete

}

[266] Fix | Delete

}

[267] Fix | Delete

return $this->_ttapproveid;

[268] Fix | Delete

}

[269] Fix | Delete

else if (!strcasecmp($tableName, "ttstatid")) {

[270] Fix | Delete

$queryStr = "CREATE TEMPORARY TABLE IF NOT EXISTS `ttstatid` (PRIMARY KEY (`statusID`), INDEX (`maxLogID`)) ".

[271] Fix | Delete

"SELECT `tblDocumentStatusLog`.`statusID`, ".

[272] Fix | Delete

"MAX(`tblDocumentStatusLog`.`statusLogID`) AS `maxLogID` ".

[273] Fix | Delete

"FROM `tblDocumentStatusLog` ".

[274] Fix | Delete

"GROUP BY `tblDocumentStatusLog`.`statusID` ".

[275] Fix | Delete

"ORDER BY `tblDocumentStatusLog`.`statusLogID`";

[276] Fix | Delete

if (!$this->_ttstatid) {

[277] Fix | Delete

if (!$this->getResult($queryStr))

[278] Fix | Delete

return false;

[279] Fix | Delete

$this->_ttstatid=true;

[280] Fix | Delete

}

[281] Fix | Delete

else {

[282] Fix | Delete

if (is_bool($override) && $override) {

[283] Fix | Delete

if (!$this->getResult("DELETE FROM `ttstatid`"))

[284] Fix | Delete

return false;

[285] Fix | Delete

if (!$this->getResult($queryStr))

[286] Fix | Delete

return false;

[287] Fix | Delete

}

[288] Fix | Delete

}

[289] Fix | Delete

return $this->_ttstatid;

[290] Fix | Delete

}

[291] Fix | Delete

else if (!strcasecmp($tableName, "ttcontentid")) {

[292] Fix | Delete

$queryStr = "CREATE TEMPORARY TABLE `ttcontentid` (PRIMARY KEY (`document`), INDEX (`maxVersion`)) ".

[293] Fix | Delete

"SELECT `tblDocumentContent`.`document`, ".

[294] Fix | Delete

"MAX(`tblDocumentContent`.`version`) AS `maxVersion` ".

[295] Fix | Delete

"FROM `tblDocumentContent` ".

[296] Fix | Delete

"GROUP BY `tblDocumentContent`.`document` ".

[297] Fix | Delete

"ORDER BY `tblDocumentContent`.`document`";

[298] Fix | Delete

if (!$this->_ttcontentid) {

[299] Fix | Delete

if (!$this->getResult($queryStr))

[300] Fix | Delete

return false;

[301] Fix | Delete

$this->_ttcontentid=true;

[302] Fix | Delete

}

[303] Fix | Delete

else {

[304] Fix | Delete

if (is_bool($override) && $override) {

[305] Fix | Delete

if (!$this->getResult("DELETE FROM `ttcontentid`"))

[306] Fix | Delete

return false;

[307] Fix | Delete

if (!$this->getResult($queryStr))

[308] Fix | Delete

return false;

[309] Fix | Delete

}

[310] Fix | Delete

}

[311] Fix | Delete

return $this->_ttcontentid;

[312] Fix | Delete

}

[313] Fix | Delete

return false;

[314] Fix | Delete

} /* }}} */

[315] Fix | Delete

}

[316] Fix | Delete