instapage / models / InstapageCmsPluginDBModel.php

InstapageCmsPluginDBModel.php in Instapage Plugin trunk, at models/InstapageCmsPluginDBModel.php

249 lines 7.3 KB

Raw Download Zip

1	<?php
2
3	/**
4	* Class responsible for communication with DB.
5	*/
6	class InstapageCmsPluginDBModel {
7
8	/**
9	* @var object Class instance.
10	*/
11	private static $dbModel = null;
12
13	/**
14	* @var string DB prefix.
15	*/
16	public $prefix = null;
17
18	/**
19	* @var string Charset collatation.
20	*/
21	public $charsetCollate = null;
22
23	/**
24	* @var string Options table name.
25	*/
26	public $optionsTable = null;
27
28	/**
29	* @var string Pages table name.
30	*/
31	public $pagesTable = null;
32
33	/**
34	* @var Debug log table name.
35	*/
36	public $debugTable = null;
37
38	/**
39	* Class constructor. Sets the properties based on current CMS settings.
40	*/
41	function __construct() {
42	$this->prefix = InstapageCmsPluginConnector::getSelectedConnector()->getDBPrefix();
43	$this->charsetCollate = InstapageCmsPluginConnector::getSelectedConnector()->getCharsetCollate();
44	$this->optionsTable = $this->prefix . 'instapage_options';
45	$this->pagesTable = $this->prefix . 'instapage_pages';
46	$this->debugTable = $this->prefix . 'instapage_debug';
47	}
48
49	/**
50	* Gets the class instance.
51	*
52	* @return object Class instance.
53	*/
54	public static function getInstance() {
55	if (self::$dbModel === null) {
56	self::$dbModel = new InstapageCmsPluginDBModel();
57	}
58
59	return self::$dbModel;
60	}
61
62	/**
63	* Executes a SQL query.
64	*
65	* @param string $sql SQL to execute. %s can be used to output pre-formatted values. Values for %s can be passed as arguments for this function.
66	* @return bool True if the query is successful. DB error is logged and false if returned otherwise.
67	*/
68	public function query($sql) {
69	$args = func_get_args();
70	array_shift($args);
71
72	if (isset($args[0]) && is_array($args[0]) ) {
73	$args = $args[0];
74	}
75
76	return InstapageCmsPluginConnector::getSelectedConnector()->query($sql, $args);
77	}
78
79	/**
80	* Gets the last ID of an insert query.
81	*
82	* @return integer\|boolean Last insert ID of false on error.
83	*/
84	public function lastInsertId() {
85	return InstapageCmsPluginConnector::getSelectedConnector()->lastInsertId();
86	}
87
88	/**
89	* Executes the query and returns the first row.
90	*
91	* @param string $sql SQL to execute. %s can be used to output pre-formatted values. Values for %s can be passed as arguments for this function.
92	* @return mixed first row of results of the query.
93	*/
94	public function getRow($sql) {
95	$args = func_get_args();
96	array_shift($args);
97
98	if (isset($args[0]) && is_array($args[0]) ) {
99	$args = $args[0];
100	}
101
102	return InstapageCmsPluginConnector::getSelectedConnector()->getRow($sql, $args);
103	}
104
105	/**
106	* Executes the query and returns a list of results.
107	*
108	* @param string $sql SQL to execute. %s can be used to output pre-formatted values. Values for %s can be passed as arguments for this function.
109	* @return mixed first row of results of the query.
110	*/
111	public static function getResults($sql) {
112	$args = func_get_args();
113	array_shift($args);
114
115	if (isset($args[0]) && is_array($args[0]) ) {
116	$args = $args[0];
117	}
118
119	return InstapageCmsPluginConnector::getSelectedConnector()->getResults($sql, $args);
120	}
121
122	/**
123	* Initiates Instapage plugin's DB structure.
124	*/
125	public function initPluginTables() {
126	$this->initOptionsTable();
127	$this->initPagesTable();
128	$this->initDebugTable();
129	$this->updateDB();
130	}
131
132	/**
133	* Initiates Instapage plugin's DB structure for options table.
134	*/
135	private function initOptionsTable() {
136	$sql = sprintf('SHOW TABLES LIKE \'%s\'', $this->optionsTable);
137	$result = $this->getRow($sql);
138
139	if ($result) {
140	return true;
141	}
142
143	$sql = sprintf('CREATE TABLE IF NOT EXISTS %s(' .
144	'id MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT, ' .
145	'plugin_hash VARCHAR(255) DEFAULT \'\', ' .
146	'api_keys TEXT NULL, ' .
147	'user_name VARCHAR(255) DEFAULT \'\', ' .
148	'config TEXT NULL, ' .
149	'metadata TEXT NULL, ' .
150	'UNIQUE KEY id (id)) %s', $this->optionsTable, $this->charsetCollate);
151
152	$this->query($sql);
153	}
154
155	/**
156	* Initiates Instapage plugin's DB structure for pages table.
157	*/
158	private function initPagesTable() {
159	$sql = sprintf('SHOW TABLES LIKE \'%s\'', $this->pagesTable);
160	$result = $this->getRow($sql);
161
162	if ($result) {
163	return true;
164	}
165
166	$sql = sprintf('CREATE TABLE IF NOT EXISTS %s(' .
167	'id MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT, ' .
168	'instapage_id INT UNSIGNED NOT NULL, ' .
169	'slug VARCHAR(255) DEFAULT \'\' NOT NULL, ' .
170	'type VARCHAR(4) DEFAULT \'page\' NOT NULL, ' .
171	'time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, ' .
172	'stats_cache TEXT NULL, ' .
173	'stats_cache_expires INT UNSIGNED, ' .
174	'enterprise_url VARCHAR(255) DEFAULT \'\' NOT NULL, ' .
175	'UNIQUE KEY id (id)) %s', $this->pagesTable, $this->charsetCollate );
176
177	$this->query($sql);
178	}
179
180	/**
181	* Initiates Instapage plugin's DB structure for debug table.
182	*/
183	private function initDebugTable() {
184	$sql = sprintf('SHOW TABLES LIKE \'%s\'', $this->debugTable);
185	$result = $this->getRow($sql);
186
187	if ($result) {
188	return true;
189	}
190
191	$sql = sprintf('CREATE TABLE IF NOT EXISTS %s(' .
192	'id MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT, ' .
193	'time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, ' .
194	'text TEXT NULL, ' .
195	'caller VARCHAR(255) DEFAULT \'\' NOT NULL, ' .
196	'name VARCHAR(255) DEFAULT \'\' NOT NULL, ' .
197	'UNIQUE KEY id (id)) %s', $this->debugTable, $this->charsetCollate );
198
199	$this->query($sql);
200	}
201
202	/**
203	* Check current DB structure version and updates it if necessary.
204	*/
205	private function updateDB() {
206	$db_version = intval(InstapageCmsPluginHelper::getMetadata('db_version', 0), 10);
207
208	if ($db_version < 300000010) {
209	InstapageCmsPluginHelper::writeDiagnostics($db_version, 'Current db version. Doing update.');
210	$sql = 'SHOW COLUMNS FROM ' . $this->optionsTable . ' LIKE %s';
211	$metadata_exists = $this->getRow($sql, 'metadata');
212	$sql = 'SHOW COLUMNS FROM ' . $this->pagesTable . ' LIKE %s';
213	$enterprise_url_exists = $this->getRow($sql, 'enterprise_url');
214
215	if (!$metadata_exists) {
216	$sql = sprintf('ALTER TABLE %s ADD metadata TEXT NULL', $this->optionsTable);
217	$this->query($sql);
218	}
219
220	if (!$enterprise_url_exists) {
221	$sql = sprintf('ALTER TABLE %s ADD enterprise_url VARCHAR(255) DEFAULT \'\' NOT NULL', $this->pagesTable);
222	$this->query($sql);
223	}
224
225	$sql = sprintf('ALTER TABLE %s MODIFY api_keys TEXT NULL', $this->optionsTable);
226	$this->query($sql);
227	$sql = sprintf('ALTER TABLE %s MODIFY config TEXT NULL', $this->optionsTable);
228	$this->query($sql);
229	$sql = sprintf('ALTER TABLE %s MODIFY slug VARCHAR(255) DEFAULT \'\' NOT NULL', $this->pagesTable);
230	$this->query($sql);
231	$sql = sprintf('ALTER TABLE %s MODIFY time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL', $this->pagesTable);
232	$this->query($sql);
233	$sql = sprintf('ALTER TABLE %s MODIFY time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL', $this->debugTable);
234	$this->query($sql);
235
236	InstapageCmsPluginHelper::updateMetadata('db_version', 300000010);
237	}
238	}
239
240	/**
241	* Initiates Instapage plugin's DB tables.
242	*/
243	public function removePluginTables() {
244	$this->query('DROP TABLE IF EXISTS ' . $this->optionsTable);
245	$this->query('DROP TABLE IF EXISTS ' . $this->pagesTable);
246	$this->query('DROP TABLE IF EXISTS ' . $this->debugTable);
247	}
248	}
249