Database::checkQueryError() - Code Metrics - andygrunwald/Gerrie - Measure and Improve Code Quality continuously with Scrutinizer

Database::checkQueryError() B
last analyzed 2016-01-20 19:15 UTC

↳ Parent: Database

Complexity

Conditions	6
Paths	5

Size

Total Lines	24
Code Lines	13

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	42

Metric	Value
dl	0
loc	24
ccs	0
cts	13
cp	0
rs	8.5125
cc	6
eloc	13
nc	5
nop	3
crap	42

<?php
/**
 * This file is part of the Gerrie package.
 *
 * (c) Andreas Grunwald <[email protected]>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace Gerrie\Component\Database;

class Database
{

    /**
     * Database handle
     *
     * @var null|\PDO
     */
    protected $handle = null;

    /**
     * Database credentials
     *
     * @var array
     */
    private $config = [];

    /**
     * Table constants
     *
     * @var string
     */
    const TABLE_SERVER = 'gerrie_server';
    const TABLE_PROJECT = 'gerrie_project';
    const TABLE_BRANCH = 'gerrie_branch';
    const TABLE_CHANGESET = 'gerrie_changeset';
    const TABLE_PERSON = 'gerrie_person';
    const TABLE_EMAIL = 'gerrie_email';
    const TABLE_PATCHSET = 'gerrie_patchset';
    const TABLE_FILES = 'gerrie_files';
    const TABLE_APPROVAL = 'gerrie_approval';
    const TABLE_COMMENT = 'gerrie_comment';
    const TABLE_STATUS = 'gerrie_changeset_status';
    const TABLE_FILEACTION = 'gerrie_file_action';
    const TABLE_TRACKING_ID = 'gerrie_tracking_ids';
    const TABLE_TRACKING_SYSTEM = 'gerrie_tracking_system';
    const TABLE_SUBMIT_RECORDS = 'gerrie_submit_records';
    const TABLE_SUBMIT_RECORD_LABELS = 'gerrie_submit_record_labels';
    const TABLE_FILE_COMMENTS = 'gerrie_file_comments';
    const TABLE_TMP_DEPENDS_NEEDED = 'gerrie_tmp_depends_needed';
    const TABLE_CHANGESET_NEEDEDBY = 'gerrie_changeset_neededby';

    /**
     * Field value constants for:
     *    Table gerrie_tmp_depends_needed
     *    Field: status
     *
     * Records source is the 'dependsOn' property of a changeset
     *
     * @var int
     */
    const TMP_DEPENDS_NEEDED_STATUS_DEPENDSON = 1;

    /**
     * Field value constants for:
     *    Table gerrie_tmp_depends_needed
     *    Field: status
     *
     * Records source is the 'neededBy' property of a changeset
     *
     * @var int
     */
    const TMP_DEPENDS_NEEDED_STATUS_NEEDEDBY = 2;

    /**
     * MySQL table definition for needed database tables
     *
     * @var array
     */
    protected $tableDefinition = [
        // @todo add unique stuff
        'gerrie_server' => "
			CREATE TABLE `gerrie_server` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`name` varchar(255) NOT NULL DEFAULT '',
				`host` varchar(255) NOT NULL DEFAULT '',
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_project' => "
			CREATE TABLE `gerrie_project` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`server_id` int(11) unsigned NOT NULL,
				`identifier` varchar(255) NOT NULL DEFAULT '',
				`name` varchar(255) NOT NULL DEFAULT '',
				`description` TEXT NOT NULL,
				`kind` varchar(255) NOT NULL DEFAULT '',
				`state` varchar(255) NOT NULL DEFAULT '',
				`parent` int(11) unsigned NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_branch' => "
			CREATE TABLE `gerrie_branch` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`name` varchar(255) NOT NULL DEFAULT '',
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        // @todo was ist id für ein string? SHA1 + Ein Zeichen? Wenn Ja, Feld auf 41 Zeichen begrenzen
        // @todo prüfen ob sortKey immer eine feste länge hat
        'gerrie_changeset' => "
			CREATE TABLE `gerrie_changeset` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`project` int(11) unsigned NOT NULL DEFAULT 0,
				`branch` int(11) unsigned NOT NULL DEFAULT 0,
				`topic` varchar(255) NOT NULL DEFAULT '',
				`identifier` varchar(100) NOT NULL DEFAULT '',
				`number` int(11) unsigned NOT NULL DEFAULT 0,
				`subject` varchar(255) NOT NULL DEFAULT '',
				`owner` int(11) unsigned NOT NULL DEFAULT 0,
				`url` varchar(255) NOT NULL DEFAULT '',
				`commit_message` MEDIUMTEXT NOT NULL,
				`created_on` int(11) unsigned NOT NULL DEFAULT 0,
				`last_updated` int(11) unsigned NOT NULL DEFAULT 0,
				`sort_key` varchar(255) NOT NULL DEFAULT '',
				`open` int(11) unsigned NOT NULL DEFAULT 0,
				`status` int(11) unsigned NOT NULL DEFAULT 0,
				`current_patchset` int(11) unsigned NOT NULL DEFAULT 0,
				`depends_on` int(11) unsigned NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_person' => "
			CREATE TABLE `gerrie_person` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`name` varchar(255) NOT NULL DEFAULT '',
				`username` varchar(255) NOT NULL DEFAULT '',
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_email' => "
			CREATE TABLE `gerrie_email` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`person` int(11) unsigned NOT NULL DEFAULT 0,
				`email` varchar(255) NOT NULL DEFAULT '',
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_patchset' => "
			CREATE TABLE `gerrie_patchset` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`changeset` int(11) unsigned NOT NULL DEFAULT 0,
				`number` int(11) unsigned NOT NULL DEFAULT 0,
				`revision` varchar(255) NOT NULL DEFAULT '',
				`ref` varchar(255) NOT NULL DEFAULT '',
				`uploader` int(11) unsigned NOT NULL DEFAULT 0,
				`author` int(11) unsigned NOT NULL DEFAULT 0,
				`size_insertions` int(11) NOT NULL DEFAULT 0,
				`size_deletions` int(11) NOT NULL DEFAULT 0,
				`is_draft` TINYINT unsigned NOT NULL DEFAULT 0,
				`created_on` int(11) unsigned NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        // @todo Feld type auslagern
        'gerrie_files' => "
			CREATE TABLE `gerrie_files` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`patchset` int(11) unsigned NOT NULL DEFAULT 0,
				`file` varchar(255) NOT NULL DEFAULT '',
				`file_old` varchar(255) NOT NULL DEFAULT '',
				`type` int(11) unsigned NOT NULL DEFAULT 0,
				`insertions` int(11) unsigned NOT NULL DEFAULT 0,
				`deletions` int(11) NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        // @todo feld type und description soviel speicherplatz?
        'gerrie_approval' => "
			CREATE TABLE `gerrie_approval` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`patchset` int(11) unsigned NOT NULL DEFAULT 0,
				`type` varchar(255) NOT NULL DEFAULT '',
				`description` varchar(255) NOT NULL DEFAULT '',
				`value` int(11) signed NOT NULL DEFAULT 0,
				`granted_on` int(11) unsigned NOT NULL DEFAULT 0,
				`by` int(11) unsigned NOT NULL DEFAULT 0,
				`voted_earlier` int(1) NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_comment' => "
			CREATE TABLE `gerrie_comment` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`changeset` int(11) unsigned NOT NULL DEFAULT 0,
				`timestamp` int(11) unsigned NOT NULL DEFAULT 0,
				`reviewer` int(11) unsigned NOT NULL DEFAULT 0,
				`message` text NOT NULL,
				`number` int(11) unsigned NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_changeset_status' => "
			CREATE TABLE `gerrie_changeset_status` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`name` varchar(255) NOT NULL DEFAULT '',
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_file_action' => "
			CREATE TABLE `gerrie_file_action` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`name` varchar(255) NOT NULL DEFAULT '',
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        // @todo is number really a number?
        'gerrie_tracking_ids' => "
			CREATE TABLE `gerrie_tracking_ids` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`changeset` int(11) unsigned NOT NULL DEFAULT 0,
				`system` int(11) unsigned NOT NULL DEFAULT 0,
				`number` varchar(255) NOT NULL DEFAULT '',
				`referenced_earlier` int(1) NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_tracking_system' => "
			CREATE TABLE `gerrie_tracking_system` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`name` varchar(255) NOT NULL DEFAULT '',
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        // @todo refactor status = int
        'gerrie_submit_records' => "
			CREATE TABLE `gerrie_submit_records` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`changeset` int(11) unsigned NOT NULL DEFAULT 0,
				`status` varchar(255) NOT NULL DEFAULT '',
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`),
				UNIQUE KEY `changeset` (`changeset`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_submit_record_labels' => "
			CREATE TABLE `gerrie_submit_record_labels` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`submit_record` int(11) unsigned NOT NULL DEFAULT 0,
				`label` varchar(255) NOT NULL DEFAULT '',
				`status` varchar(255) NOT NULL DEFAULT '',
				`by` int(11) unsigned NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`),
				UNIQUE KEY `label_per_record` (`submit_record`, `label`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_file_comments' => "
			CREATE TABLE `gerrie_file_comments` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`patchset` int(11) unsigned NOT NULL DEFAULT 0,
				`file` int(11) unsigned NOT NULL DEFAULT 0,
				`line` int(11) unsigned NOT NULL DEFAULT 0,
				`reviewer` int(11) unsigned NOT NULL DEFAULT 0,
				`message` text NOT NULL,
				`message_crc32` int(11) unsigned NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        // Temp table for 'dependsOn' and 'neededBy'
        // Status: 1 => dependsOn, 2 => neededBy
        'gerrie_tmp_depends_needed' => "
			CREATE TABLE `gerrie_tmp_depends_needed` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`changeset` int(11) unsigned NOT NULL DEFAULT 0,
				`identifier` varchar(100) NOT NULL DEFAULT '',
				`number` int(11) unsigned NOT NULL DEFAULT 0,
				`revision` varchar(255) NOT NULL DEFAULT '',
				`ref` varchar(255) NOT NULL DEFAULT '',
				`is_current_patchset` int(1) unsigned NOT NULL DEFAULT 0,
				`status` int(1) unsigned NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
        'gerrie_changeset_neededby' => "
			CREATE TABLE `gerrie_changeset_neededby` (
				`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`changeset` int(11) unsigned NOT NULL DEFAULT 0,
				`needed_by` int(11) unsigned NOT NULL DEFAULT 0,
				`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
				`crdate` int(11) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY (`id`),
				UNIQUE KEY `changeset_needed` (`changeset`,`needed_by`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
    ];

    /**
     * Constructor
     *
     * @param array $config
     * @param bool $connectImmediately
     */
    public function __construct(array $config, $connectImmediately = true)
    {
        $this->setConfig($config);

        if ($connectImmediately === true) {
            $this->connect($config);
        }
    }

    public function connect($config)
    {
        // Build the port part of DSN
        $portPart = (isset($config['Port']) === true) ? intval($config['Port']) : null;
        if ($portPart > 0) {
            $portPart = 'port=' . $portPart . ';';
        }

        $dsn = 'mysql:host=' . $config['Host'] . ';' . $portPart . 'dbname=' . $config['Name'];

        $this->handle = new \PDO($dsn, $config['Username'], $config['Password']);
    }

    public function getDatabaseConnection()
    {
        return $this->handle;
    }

    public function getTableDefinition()
    {
        return $this->tableDefinition;
    }

    public function checkQueryError(\PDOStatement $statement, $lastQueryResult, $prepareSet = array())
    {
        if ($lastQueryResult === true) {
            return $statement;
        }

        // MySQL server has gone away ... automatic reconnect
        // The connection can be lost if the mysql.connection_timeout or default_socket_timeout is to low.
        // Here we try to reconnect to the database and execute the statement again
        $errorInfo = $statement->errorInfo();
        if ($errorInfo[1] && $errorInfo[1] == 2006) {
            $this->connect($this->getConfig());
            if (count($prepareSet) > 0) {
                $statement->execute($prepareSet);
            } else {
                $statement->execute();
            }

        } elseif ($errorInfo[1]) {
            throw new \Exception($errorInfo[2] . ' (' . $errorInfo[1] . ')', 1367873943);
        }

        return $statement;
    }

    /**
     * Prepared the needed data / structures for prepared statements for our UPDATE queries.
     *
     * After this we get two structures:
     * 1. ($updateSet): array(0 => 'MySQLfield1 = :MySQLfield1', 1 => 'MySQLfield2 = :MySQLfield2', ...);
     * 2. ($prepareSet): array(':MySQLfield1' => 'valueToUpdate1', ':MySQLfield2' => 'valueToUpdate1', ...);
     *
     * @see $this->updateRecord
     * @see $this->updateRecords
     *
     * @param array $data Data to update with fields as keys and related values as values
     * @return array
     * @throws \Exception
     */
    protected function prepareUpdateData(array $data)
    {
        $updateSet = array();
        $prepareSet = array();
        foreach ($data as $key => $value) {
            $updateSet[] = '`' . $key . '` = :' . $key;
            $prepareSet[':' . $key] = $value;
        }

        if (count($updateSet) == 0 || count($prepareSet) == 0) {
            throw new \Exception('Missing data for update query', 1363894675);
        }

        $updateSet[] = '`tstamp` = :tstamp';
        $prepareSet[':tstamp'] = time();

        return array($updateSet, $prepareSet);
    }

    /**
     * Updates all records which matches the $where statement in the given $table with the given $data via prepared statements.
     *
     * @param string $table Table to update
     * @param array $data New data
     * @param string $where Where statement
     * @return int
     */
    public function updateRecords($table, array $data, $where)
    {
        $dbHandle = $this->getDatabaseConnection();
        list($updateSet, $prepareSet) = $this->prepareUpdateData($data);

        $query = 'UPDATE ' . $table . '
                  SET ' . implode(', ', $updateSet) . '
                  WHERE ' . $where;

        $statement = $dbHandle->prepare($query);
        $executeResult = $statement->execute($prepareSet);

        $statement = $this->checkQueryError($statement, $executeResult, $prepareSet);
        return $statement->rowCount();
    }

    /**
     * Updates a single record (given $id) in the given $table with the given $data via prepared statements.
     *
     * @param string $table Table to update
     * @param array $data New data
     * @param int $id ID of record to update
     * @return int
     */
    public function updateRecord($table, array $data, $id)
    {
        $dbHandle = $this->getDatabaseConnection();
        list($updateSet, $prepareSet) = $this->prepareUpdateData($data);

        $prepareSet[':id'] = $id;

        $query = 'UPDATE ' . $table . '
                  SET ' . implode(', ', $updateSet) . '
                  WHERE `id` = :id';

        $statement = $dbHandle->prepare($query);
        $executeResult = $statement->execute($prepareSet);

        $statement = $this->checkQueryError($statement, $executeResult, $prepareSet);
        return $statement->rowCount();
    }

    /**
     * Inserts a single record (given $data) in the given $table via prepared statements.
     *
     * @param string $table Table to insert
     * @param array $data Data to insert
     * @throws \Exception
     * @return int Last inserted id
     */
    public function insertRecord($table, array $data)
    {
        $dbHandle = $this->getDatabaseConnection();

        $fieldSet = array_keys($data);

        // Prepare sets
        $valueSet = array();
        foreach ($data as $key => $value) {
            $valueSet[':' . $key] = $value;
        }

        if (count($fieldSet) == 0 || count($valueSet) == 0) {
            throw new \Exception('Missing data for insert query', 1363894664);
        }

        $fieldSet[] = 'tstamp';
        $valueSet[':tstamp'] = time();

        $fieldSet[] = 'crdate';
        $valueSet[':crdate'] = time();

        $query = 'INSERT INTO ' . $table . ' (`' . implode('`,`', $fieldSet) . '`)
                  VALUES (' . implode(', ', array_keys($valueSet)) . ')';

        $statement = $dbHandle->prepare($query);
        $executeResult = $statement->execute($valueSet);

        $this->checkQueryError($statement, $executeResult, $valueSet);
        return $dbHandle->lastInsertId();
    }

    /**
     * Sets the database configuration
     *
     * @param array $config
     * @return void
     */
    public function setConfig(array $config)
    {
        $this->config = $config;
    }

    /**
     * Returns the database configuration
     *
     * @return array
     */
    public function getConfig()
    {
        return $this->config;
    }
}

1		<?php
2		/**
3		* This file is part of the Gerrie package.
4		*
5		* (c) Andreas Grunwald <[email protected]>
6		*
7		* For the full copyright and license information, please view the LICENSE
8		* file that was distributed with this source code.
9		*/
10
11		namespace Gerrie\Component\Database;
12
13		class Database
14		{
15
16		/**
17		* Database handle
18		*
19		* @var null\|\PDO
20		*/
21		protected $handle = null;
22
23		/**
24		* Database credentials
25		*
26		* @var array
27		*/
28		private $config = [];
29
30		/**
31		* Table constants
32		*
33		* @var string
34		*/
35		const TABLE_SERVER = 'gerrie_server';
36		const TABLE_PROJECT = 'gerrie_project';
37		const TABLE_BRANCH = 'gerrie_branch';
38		const TABLE_CHANGESET = 'gerrie_changeset';
39		const TABLE_PERSON = 'gerrie_person';
40		const TABLE_EMAIL = 'gerrie_email';
41		const TABLE_PATCHSET = 'gerrie_patchset';
42		const TABLE_FILES = 'gerrie_files';
43		const TABLE_APPROVAL = 'gerrie_approval';
44		const TABLE_COMMENT = 'gerrie_comment';
45		const TABLE_STATUS = 'gerrie_changeset_status';
46		const TABLE_FILEACTION = 'gerrie_file_action';
47		const TABLE_TRACKING_ID = 'gerrie_tracking_ids';
48		const TABLE_TRACKING_SYSTEM = 'gerrie_tracking_system';
49		const TABLE_SUBMIT_RECORDS = 'gerrie_submit_records';
50		const TABLE_SUBMIT_RECORD_LABELS = 'gerrie_submit_record_labels';
51		const TABLE_FILE_COMMENTS = 'gerrie_file_comments';
52		const TABLE_TMP_DEPENDS_NEEDED = 'gerrie_tmp_depends_needed';
53		const TABLE_CHANGESET_NEEDEDBY = 'gerrie_changeset_neededby';
54
55		/**
56		* Field value constants for:
57		* Table gerrie_tmp_depends_needed
58		* Field: status
59		*
60		* Records source is the 'dependsOn' property of a changeset
61		*
62		* @var int
63		*/
64		const TMP_DEPENDS_NEEDED_STATUS_DEPENDSON = 1;
65
66		/**
67		* Field value constants for:
68		* Table gerrie_tmp_depends_needed
69		* Field: status
70		*
71		* Records source is the 'neededBy' property of a changeset
72		*
73		* @var int
74		*/
75		const TMP_DEPENDS_NEEDED_STATUS_NEEDEDBY = 2;
76
77		/**
78		* MySQL table definition for needed database tables
79		*
80		* @var array
81		*/
82		protected $tableDefinition = [
83		// @todo add unique stuff
84		'gerrie_server' => "
85		CREATE TABLE `gerrie_server` (
86		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
87		`name` varchar(255) NOT NULL DEFAULT '',
88		`host` varchar(255) NOT NULL DEFAULT '',
89		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
90		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
91		PRIMARY KEY (`id`)
92		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
93		'gerrie_project' => "
94		CREATE TABLE `gerrie_project` (
95		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
96		`server_id` int(11) unsigned NOT NULL,
97		`identifier` varchar(255) NOT NULL DEFAULT '',
98		`name` varchar(255) NOT NULL DEFAULT '',
99		`description` TEXT NOT NULL,
100		`kind` varchar(255) NOT NULL DEFAULT '',
101		`state` varchar(255) NOT NULL DEFAULT '',
102		`parent` int(11) unsigned NOT NULL DEFAULT 0,
103		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
104		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
105		PRIMARY KEY (`id`)
106		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
107		'gerrie_branch' => "
108		CREATE TABLE `gerrie_branch` (
109		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
110		`name` varchar(255) NOT NULL DEFAULT '',
111		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
112		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
113		PRIMARY KEY (`id`)
114		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
115		// @todo was ist id für ein string? SHA1 + Ein Zeichen? Wenn Ja, Feld auf 41 Zeichen begrenzen
116		// @todo prüfen ob sortKey immer eine feste länge hat
117		'gerrie_changeset' => "
118		CREATE TABLE `gerrie_changeset` (
119		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
120		`project` int(11) unsigned NOT NULL DEFAULT 0,
121		`branch` int(11) unsigned NOT NULL DEFAULT 0,
122		`topic` varchar(255) NOT NULL DEFAULT '',
123		`identifier` varchar(100) NOT NULL DEFAULT '',
124		`number` int(11) unsigned NOT NULL DEFAULT 0,
125		`subject` varchar(255) NOT NULL DEFAULT '',
126		`owner` int(11) unsigned NOT NULL DEFAULT 0,
127		`url` varchar(255) NOT NULL DEFAULT '',
128		`commit_message` MEDIUMTEXT NOT NULL,
129		`created_on` int(11) unsigned NOT NULL DEFAULT 0,
130		`last_updated` int(11) unsigned NOT NULL DEFAULT 0,
131		`sort_key` varchar(255) NOT NULL DEFAULT '',
132		`open` int(11) unsigned NOT NULL DEFAULT 0,
133		`status` int(11) unsigned NOT NULL DEFAULT 0,
134		`current_patchset` int(11) unsigned NOT NULL DEFAULT 0,
135		`depends_on` int(11) unsigned NOT NULL DEFAULT 0,
136		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
137		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
138		PRIMARY KEY (`id`)
139		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
140		'gerrie_person' => "
141		CREATE TABLE `gerrie_person` (
142		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
143		`name` varchar(255) NOT NULL DEFAULT '',
144		`username` varchar(255) NOT NULL DEFAULT '',
145		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
146		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
147		PRIMARY KEY (`id`)
148		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
149		'gerrie_email' => "
150		CREATE TABLE `gerrie_email` (
151		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
152		`person` int(11) unsigned NOT NULL DEFAULT 0,
153		`email` varchar(255) NOT NULL DEFAULT '',
154		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
155		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
156		PRIMARY KEY (`id`)
157		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
158		'gerrie_patchset' => "
159		CREATE TABLE `gerrie_patchset` (
160		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
161		`changeset` int(11) unsigned NOT NULL DEFAULT 0,
162		`number` int(11) unsigned NOT NULL DEFAULT 0,
163		`revision` varchar(255) NOT NULL DEFAULT '',
164		`ref` varchar(255) NOT NULL DEFAULT '',
165		`uploader` int(11) unsigned NOT NULL DEFAULT 0,
166		`author` int(11) unsigned NOT NULL DEFAULT 0,
167		`size_insertions` int(11) NOT NULL DEFAULT 0,
168		`size_deletions` int(11) NOT NULL DEFAULT 0,
169		`is_draft` TINYINT unsigned NOT NULL DEFAULT 0,
170		`created_on` int(11) unsigned NOT NULL DEFAULT 0,
171		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
172		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
173		PRIMARY KEY (`id`)
174		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
175		// @todo Feld type auslagern
176		'gerrie_files' => "
177		CREATE TABLE `gerrie_files` (
178		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
179		`patchset` int(11) unsigned NOT NULL DEFAULT 0,
180		`file` varchar(255) NOT NULL DEFAULT '',
181		`file_old` varchar(255) NOT NULL DEFAULT '',
182		`type` int(11) unsigned NOT NULL DEFAULT 0,
183		`insertions` int(11) unsigned NOT NULL DEFAULT 0,
184		`deletions` int(11) NOT NULL DEFAULT 0,
185		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
186		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
187		PRIMARY KEY (`id`)
188		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
189		// @todo feld type und description soviel speicherplatz?
190		'gerrie_approval' => "
191		CREATE TABLE `gerrie_approval` (
192		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
193		`patchset` int(11) unsigned NOT NULL DEFAULT 0,
194		`type` varchar(255) NOT NULL DEFAULT '',
195		`description` varchar(255) NOT NULL DEFAULT '',
196		`value` int(11) signed NOT NULL DEFAULT 0,
197		`granted_on` int(11) unsigned NOT NULL DEFAULT 0,
198		`by` int(11) unsigned NOT NULL DEFAULT 0,
199		`voted_earlier` int(1) NOT NULL DEFAULT 0,
200		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
201		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
202		PRIMARY KEY (`id`)
203		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
204		'gerrie_comment' => "
205		CREATE TABLE `gerrie_comment` (
206		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
207		`changeset` int(11) unsigned NOT NULL DEFAULT 0,
208		`timestamp` int(11) unsigned NOT NULL DEFAULT 0,
209		`reviewer` int(11) unsigned NOT NULL DEFAULT 0,
210		`message` text NOT NULL,
211		`number` int(11) unsigned NOT NULL DEFAULT 0,
212		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
213		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
214		PRIMARY KEY (`id`)
215		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
216		'gerrie_changeset_status' => "
217		CREATE TABLE `gerrie_changeset_status` (
218		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
219		`name` varchar(255) NOT NULL DEFAULT '',
220		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
221		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
222		PRIMARY KEY (`id`)
223		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
224		'gerrie_file_action' => "
225		CREATE TABLE `gerrie_file_action` (
226		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
227		`name` varchar(255) NOT NULL DEFAULT '',
228		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
229		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
230		PRIMARY KEY (`id`)
231		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
232		// @todo is number really a number?
233		'gerrie_tracking_ids' => "
234		CREATE TABLE `gerrie_tracking_ids` (
235		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
236		`changeset` int(11) unsigned NOT NULL DEFAULT 0,
237		`system` int(11) unsigned NOT NULL DEFAULT 0,
238		`number` varchar(255) NOT NULL DEFAULT '',
239		`referenced_earlier` int(1) NOT NULL DEFAULT 0,
240		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
241		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
242		PRIMARY KEY (`id`)
243		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
244		'gerrie_tracking_system' => "
245		CREATE TABLE `gerrie_tracking_system` (
246		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
247		`name` varchar(255) NOT NULL DEFAULT '',
248		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
249		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
250		PRIMARY KEY (`id`)
251		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
252		// @todo refactor status = int
253		'gerrie_submit_records' => "
254		CREATE TABLE `gerrie_submit_records` (
255		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
256		`changeset` int(11) unsigned NOT NULL DEFAULT 0,
257		`status` varchar(255) NOT NULL DEFAULT '',
258		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
259		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
260		PRIMARY KEY (`id`),
261		UNIQUE KEY `changeset` (`changeset`)
262		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
263		'gerrie_submit_record_labels' => "
264		CREATE TABLE `gerrie_submit_record_labels` (
265		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
266		`submit_record` int(11) unsigned NOT NULL DEFAULT 0,
267		`label` varchar(255) NOT NULL DEFAULT '',
268		`status` varchar(255) NOT NULL DEFAULT '',
269		`by` int(11) unsigned NOT NULL DEFAULT 0,
270		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
271		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
272		PRIMARY KEY (`id`),
273		UNIQUE KEY `label_per_record` (`submit_record`, `label`)
274		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
275		'gerrie_file_comments' => "
276		CREATE TABLE `gerrie_file_comments` (
277		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
278		`patchset` int(11) unsigned NOT NULL DEFAULT 0,
279		`file` int(11) unsigned NOT NULL DEFAULT 0,
280		`line` int(11) unsigned NOT NULL DEFAULT 0,
281		`reviewer` int(11) unsigned NOT NULL DEFAULT 0,
282		`message` text NOT NULL,
283		`message_crc32` int(11) unsigned NOT NULL DEFAULT 0,
284		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
285		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
286		PRIMARY KEY (`id`)
287		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
288		// Temp table for 'dependsOn' and 'neededBy'
289		// Status: 1 => dependsOn, 2 => neededBy
290		'gerrie_tmp_depends_needed' => "
291		CREATE TABLE `gerrie_tmp_depends_needed` (
292		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
293		`changeset` int(11) unsigned NOT NULL DEFAULT 0,
294		`identifier` varchar(100) NOT NULL DEFAULT '',
295		`number` int(11) unsigned NOT NULL DEFAULT 0,
296		`revision` varchar(255) NOT NULL DEFAULT '',
297		`ref` varchar(255) NOT NULL DEFAULT '',
298		`is_current_patchset` int(1) unsigned NOT NULL DEFAULT 0,
299		`status` int(1) unsigned NOT NULL DEFAULT 0,
300		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
301		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
302		PRIMARY KEY (`id`)
303		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
304		'gerrie_changeset_neededby' => "
305		CREATE TABLE `gerrie_changeset_neededby` (
306		`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
307		`changeset` int(11) unsigned NOT NULL DEFAULT 0,
308		`needed_by` int(11) unsigned NOT NULL DEFAULT 0,
309		`tstamp` int(11) unsigned NOT NULL DEFAULT 0,
310		`crdate` int(11) unsigned NOT NULL DEFAULT 0,
311		PRIMARY KEY (`id`),
312		UNIQUE KEY `changeset_needed` (`changeset`,`needed_by`)
313		) ENGINE=InnoDB DEFAULT CHARSET=utf8;",
314		];
315
316		/**
317		* Constructor
318		*
319		* @param array $config
320		* @param bool $connectImmediately
321		*/
322	7	public function __construct(array $config, $connectImmediately = true)
323		{
324	7	$this->setConfig($config);
325
326	7	if ($connectImmediately === true) {
327	5	$this->connect($config);
328	5	}
329	7	}
330
331		public function connect($config)
332		{
333		// Build the port part of DSN
334		$portPart = (isset($config['Port']) === true) ? intval($config['Port']) : null;
335		if ($portPart > 0) {
336		$portPart = 'port=' . $portPart . ';';
337		}
338
339		$dsn = 'mysql:host=' . $config['Host'] . ';' . $portPart . 'dbname=' . $config['Name'];
340
341		$this->handle = new \PDO($dsn, $config['Username'], $config['Password']);
342		}
343
344		public function getDatabaseConnection()
345		{
346		return $this->handle;
347		}
348
349	1	public function getTableDefinition()
350		{
351	1	return $this->tableDefinition;
352		}
353
354		public function checkQueryError(\PDOStatement $statement, $lastQueryResult, $prepareSet = array())
355		{
356		if ($lastQueryResult === true) {
357		return $statement;
358		}
359
360		// MySQL server has gone away ... automatic reconnect
361		// The connection can be lost if the mysql.connection_timeout or default_socket_timeout is to low.
362		// Here we try to reconnect to the database and execute the statement again
363		$errorInfo = $statement->errorInfo();
364		if ($errorInfo[1] && $errorInfo[1] == 2006) {
365		$this->connect($this->getConfig());
366		if (count($prepareSet) > 0) {
367		$statement->execute($prepareSet);
368		} else {
369		$statement->execute();
370		}
371
372		} elseif ($errorInfo[1]) {
373		throw new \Exception($errorInfo[2] . ' (' . $errorInfo[1] . ')', 1367873943);
374		}
375
376		return $statement;
377		}
378
379		/**
380		* Prepared the needed data / structures for prepared statements for our UPDATE queries.
381		*
382		* After this we get two structures:
383		* 1. ($updateSet): array(0 => 'MySQLfield1 = :MySQLfield1', 1 => 'MySQLfield2 = :MySQLfield2', ...);
384		* 2. ($prepareSet): array(':MySQLfield1' => 'valueToUpdate1', ':MySQLfield2' => 'valueToUpdate1', ...);
385		*
386		* @see $this->updateRecord
387		* @see $this->updateRecords
388		*
389		* @param array $data Data to update with fields as keys and related values as values
390		* @return array
391		* @throws \Exception
392		*/
393		protected function prepareUpdateData(array $data)
394		{
395		$updateSet = array();
396		$prepareSet = array();
397		foreach ($data as $key => $value) {
398		$updateSet[] = '`' . $key . '` = :' . $key;
399		$prepareSet[':' . $key] = $value;
400		}
401
402		if (count($updateSet) == 0 \|\| count($prepareSet) == 0) {
403		throw new \Exception('Missing data for update query', 1363894675);
404		}
405
406		$updateSet[] = '`tstamp` = :tstamp';
407		$prepareSet[':tstamp'] = time();
408
409		return array($updateSet, $prepareSet);
410		}
411
412		/**
413		* Updates all records which matches the $where statement in the given $table with the given $data via prepared statements.
414		*
415		* @param string $table Table to update
416		* @param array $data New data
417		* @param string $where Where statement
418		* @return int
419		*/
420		public function updateRecords($table, array $data, $where)
421		{
422		$dbHandle = $this->getDatabaseConnection();
423		list($updateSet, $prepareSet) = $this->prepareUpdateData($data);
424
425		$query = 'UPDATE ' . $table . '
426		SET ' . implode(', ', $updateSet) . '
427		WHERE ' . $where;
428
429		$statement = $dbHandle->prepare($query);
430		$executeResult = $statement->execute($prepareSet);
431
432		$statement = $this->checkQueryError($statement, $executeResult, $prepareSet);
433		return $statement->rowCount();
434		}
435
436		/**
437		* Updates a single record (given $id) in the given $table with the given $data via prepared statements.
438		*
439		* @param string $table Table to update
440		* @param array $data New data
441		* @param int $id ID of record to update
442		* @return int
443		*/
444		public function updateRecord($table, array $data, $id)
445		{
446		$dbHandle = $this->getDatabaseConnection();
447		list($updateSet, $prepareSet) = $this->prepareUpdateData($data);
448
449		$prepareSet[':id'] = $id;
450
451		$query = 'UPDATE ' . $table . '
452		SET ' . implode(', ', $updateSet) . '
453		WHERE `id` = :id';
454
455		$statement = $dbHandle->prepare($query);
456		$executeResult = $statement->execute($prepareSet);
457
458		$statement = $this->checkQueryError($statement, $executeResult, $prepareSet);
459		return $statement->rowCount();
460		}
461
462		/**
463		* Inserts a single record (given $data) in the given $table via prepared statements.
464		*
465		* @param string $table Table to insert
466		* @param array $data Data to insert
467		* @throws \Exception
468		* @return int Last inserted id
469		*/
470		public function insertRecord($table, array $data)
471		{
472		$dbHandle = $this->getDatabaseConnection();
473
474		$fieldSet = array_keys($data);
475
476		// Prepare sets
477		$valueSet = array();
478		foreach ($data as $key => $value) {
479		$valueSet[':' . $key] = $value;
480		}
481
482		if (count($fieldSet) == 0 \|\| count($valueSet) == 0) {
483		throw new \Exception('Missing data for insert query', 1363894664);
484		}
485
486		$fieldSet[] = 'tstamp';
487		$valueSet[':tstamp'] = time();
488
489		$fieldSet[] = 'crdate';
490		$valueSet[':crdate'] = time();
491
492		$query = 'INSERT INTO ' . $table . ' (`' . implode('`,`', $fieldSet) . '`)
493		VALUES (' . implode(', ', array_keys($valueSet)) . ')';
494
495		$statement = $dbHandle->prepare($query);
496		$executeResult = $statement->execute($valueSet);
497
498		$this->checkQueryError($statement, $executeResult, $valueSet);
499		return $dbHandle->lastInsertId();
500		}
501
502		/**
503		* Sets the database configuration
504		*
505		* @param array $config
506		* @return void
507		*/
508	2	public function setConfig(array $config)
509		{
510	2	$this->config = $config;
511	2	}
512
513		/**
514		* Returns the database configuration
515		*
516		* @return array
517		*/
518	1	public function getConfig()
519		{
520	1	return $this->config;
521		}
522		}

andygrunwald / Gerrie

GitHub Access Token became invalid

Database::checkQueryError() B last analyzed 2016-01-20 19:15 UTC

Complexity

Size

Duplication

Code Coverage

Duplication Side-by-Side

Filter issues like

Database::checkQueryError() B
last analyzed 2016-01-20 19:15 UTC