· 8 years ago · May 10, 2018, 08:26 PM
1<?php
2/**
3 * src/container/mysql.class.php
4 *
5 * Copyright © 2006 Stephane Gully <stephane.gully@gmail.com>
6 *
7 * This library is free software; you can redistribute it and/or
8 * modify it under the terms of the GNU Lesser General Public
9 * License as published by the Free Software Foundation; either
10 * version 2.1 of the License, or (at your option) any later version.
11 *
12 * This library is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 * Lesser General Public License for more details.
16 *
17 * You should have received a copy of the GNU Lesser General Public
18 * License along with this library; if not, write to the
19 * Free Software Foundation, 51 Franklin St, Fifth Floor,
20 * Boston, MA 02110-1301 USA
21 */
22
23require_once dirname(__FILE__)."/../pfccontainer.class.php";
24
25/**
26 * pfcContainer_Mysql is a concret container which store data into mysql
27 *
28 * Because of the new storage functions (setMeta, getMeta, rmMeta)
29 * everything can be stored in just one single table.
30 * Using type "HEAP" or "MEMORY" mysql loads this table into memory making it very fast
31 * There is no routine to create the table if it does not exists so you have to create it by hand
32 * Replace the database login info at the top of pfcContainer_mysql class with your own
33 * You also need some config lines in your chat index file:
34 * $params["container_type"] = "mysql";
35 * $params["container_cfg_mysql_host"] = "localhost";
36 * $params["container_cfg_mysql_port"] = 3306;
37 * $params["container_cfg_mysql_database"] = "phpfreechat";
38 * $params["container_cfg_mysql_table"] = "phpfreechat";
39 * $params["container_cfg_mysql_username"] = "root";
40 * $params["container_cfg_mysql_password"] = "";
41 *
42 * Advanced parameters are :
43 * $params["container_cfg_mysql_fieldtype_server"] = 'varchar(32)';
44 * $params["container_cfg_mysql_fieldtype_group"] = 'varchar(64)';
45 * $params["container_cfg_mysql_fieldtype_subgroup"] = 'varchar(64)';
46 * $params["container_cfg_mysql_fieldtype_leaf"] = 'varchar(64)';
47 * $params["container_cfg_mysql_fieldtype_leafvalue"] = 'text';
48 * $params["container_cfg_mysql_fieldtype_timestamp"] = 'int(11)';
49 * $params["container_cfg_mysql_engine"] = 'InnoDB';
50 *
51 *
52 * @author Stephane Gully <stephane.gully@gmail.com>
53 * @author HenkBB
54 */
55class pfcContainer_Mysql extends pfcContainerInterface
56{
57 var $_db = null;
58 var $_sql_create_table = "
59 CREATE TABLE IF NOT EXISTS `%table%` (
60 `server` %fieldtype_server% NOT NULL default '',
61 `group` %fieldtype_group% NOT NULL default '',
62 `subgroup` %fieldtype_subgroup% NOT NULL default '',
63 `leaf` %fieldtype_leaf% NOT NULL default '',
64 `leafvalue` %fieldtype_leafvalue% NOT NULL,
65 `timestamp` %fieldtype_timestamp% NOT NULL default 0,
66 PRIMARY KEY (`server`,`group`,`subgroup`,`leaf`),
67 INDEX (`server`,`group`,`subgroup`,`timestamp`)
68) ENGINE=%engine%;";
69
70 function pfcContainer_Mysql()
71 {
72 pfcContainerInterface::pfcContainerInterface();
73 }
74
75 function getDefaultConfig()
76 {
77 $cfg = pfcContainerInterface::getDefaultConfig();
78 $cfg["mysql_host"] = 'localhost';
79 $cfg["mysql_port"] = 3306;
80 $cfg["mysql_database"] = 'phpfreechat';
81 $cfg["mysql_table"] = 'phpfreechat';
82 $cfg["mysql_username"] = 'root';
83 $cfg["mysql_password"] = '';
84 // advanced parameters (don't touch if you don't know what your are doing)
85 $cfg["mysql_fieldtype_server"] = 'varchar(32)';
86 $cfg["mysql_fieldtype_group"] = 'varchar(64)';
87 $cfg["mysql_fieldtype_subgroup"] = 'varchar(128)';
88 $cfg["mysql_fieldtype_leaf"] = 'varchar(128)';
89 $cfg["mysql_fieldtype_leafvalue"] = 'text';
90 $cfg["mysql_fieldtype_timestamp"] = 'int(11)';
91 $cfg["mysql_engine"] = 'InnoDB';
92 return $cfg;
93 }
94
95 function init(&$c)
96 {
97 $errors = pfcContainerInterface::init($c);
98
99 // connect to the db
100 $db = $this->_connect($c);
101 if ($db === FALSE)
102 {
103 $errors[] = _pfc("Mysql container: connect error");
104 return $errors;
105 }
106
107 // create the db if it doesn't exists
108 $db_exists = false;
109 $db_list = mysql_list_dbs($db);
110 while (!$db_exists && $row = mysql_fetch_object($db_list))
111 $db_exists = ($c->container_cfg_mysql_database == $row->Database);
112 if (!$db_exists)
113 {
114 $query = 'CREATE DATABASE '.$c->container_cfg_mysql_database;
115 $result = mysql_query($query, $db);
116 if ($result === FALSE)
117 {
118 $errors[] = _pfc("Mysql container: create database error '%s'",mysql_error($db));
119 return $errors;
120 }
121 mysql_select_db($c->container_cfg_mysql_database, $db);
122 }
123
124 // create the table if it doesn't exists
125 $query = $this->_sql_create_table;
126 $query = str_replace('%engine%', $c->container_cfg_mysql_engine,$query);
127 $query = str_replace('%table%', $c->container_cfg_mysql_table,$query);
128 $query = str_replace('%fieldtype_server%', $c->container_cfg_mysql_fieldtype_server,$query);
129 $query = str_replace('%fieldtype_group%', $c->container_cfg_mysql_fieldtype_group,$query);
130 $query = str_replace('%fieldtype_subgroup%', $c->container_cfg_mysql_fieldtype_subgroup,$query);
131 $query = str_replace('%fieldtype_leaf%', $c->container_cfg_mysql_fieldtype_leaf,$query);
132 $query = str_replace('%fieldtype_leafvalue%', $c->container_cfg_mysql_fieldtype_leafvalue,$query);
133 $query = str_replace('%fieldtype_timestamp%', $c->container_cfg_mysql_fieldtype_timestamp,$query);
134 $result = mysql_query($query, $db);
135 if ($result === FALSE)
136 {
137 $errors[] = _pfc("Mysql container: create table error '%s'",mysql_error($db));
138 return $errors;
139 }
140 return $errors;
141 }
142
143 function _connect($c = null)
144 {
145 if (!$this->_db)
146 {
147 if ($c == null) $c =& pfcGlobalConfig::Instance();
148 $this->_db = mysql_pconnect($c->container_cfg_mysql_host.':'.$c->container_cfg_mysql_port,
149 $c->container_cfg_mysql_username,
150 $c->container_cfg_mysql_password);
151 mysql_select_db($c->container_cfg_mysql_database, $this->_db);
152 }
153 return $this->_db;
154 }
155
156 function setMeta($group, $subgroup, $leaf, $leafvalue = NULL)
157 {
158 $c =& pfcGlobalConfig::Instance();
159
160 $server = $c->serverid;
161 $db = $this->_connect();
162
163 if ($leafvalue == NULL){$leafvalue="";};
164
165 $sql_count = "SELECT COUNT(*) AS C FROM ".$c->container_cfg_mysql_table." WHERE `server`='$server' AND `group`='$group' AND `subgroup`='$subgroup' AND `leaf`='$leaf' LIMIT 1";
166 $sql_insert="REPLACE INTO ".$c->container_cfg_mysql_table." (`server`, `group`, `subgroup`, `leaf`, `leafvalue`, `timestamp`) VALUES('$server', '$group', '$subgroup', '$leaf', '".addslashes($leafvalue)."', '".time()."')";
167 $sql_update="UPDATE ".$c->container_cfg_mysql_table." SET `leafvalue`='".addslashes($leafvalue)."', `timestamp`='".time()."' WHERE `server`='$server' AND `group`='$group' AND `subgroup`='$subgroup' AND `leaf`='$leaf'";
168
169 $res = mysql_query($sql_count, $db);
170 $row = mysql_fetch_array($res, MYSQL_ASSOC);
171 if( $row['C'] == 0 )
172 {
173 mysql_query($sql_insert, $db);
174 return 0; // value created
175 }
176 else
177 {
178 if ($sql_update != "")
179 {
180 mysql_query($sql_update, $db);
181 }
182 return 1; // value overwritten
183 }
184 }
185
186
187 function getMeta($group, $subgroup = null, $leaf = null, $withleafvalue = false)
188 {
189 $c =& pfcGlobalConfig::Instance();
190
191 $ret = array();
192 $ret["timestamp"] = array();
193 $ret["value"] = array();
194
195 $server = $c->serverid;
196 $db = $this->_connect();
197
198 $sql_where = "";
199 $sql_group_by = "";
200 $value = "leafvalue";
201
202 if ($group != NULL)
203 {
204 $sql_where .= " AND `group`='$group'";
205 $value = "subgroup";
206 $sql_group_by = "GROUP BY `$value`";
207 }
208
209 if ($subgroup != NULL)
210 {
211 $sql_where .= " AND `subgroup`='$subgroup'";
212 $value = "leaf";
213 $sql_group_by = "";
214 }
215
216 if ($leaf != NULL)
217 {
218 $sql_where .= " AND `leaf`='$leaf'";
219 $value = "leafvalue";
220 $sql_group_by = "";
221 }
222
223 $sql_select="SELECT `$value`, `timestamp` FROM ".$c->container_cfg_mysql_table." WHERE `server`='$server' $sql_where $sql_group_by ORDER BY timestamp";
224 if ($sql_select != "")
225 {
226 $thisresult = mysql_query($sql_select, $db);
227 if (mysql_num_rows($thisresult))
228 {
229 while ($regel = mysql_fetch_array($thisresult))
230 {
231 $ret["timestamp"][] = $regel["timestamp"];
232 if ($value == "leafvalue")
233 {
234 if ($withleafvalue)
235 $ret["value"][] = $regel[$value];
236 else
237 $ret["value"][] = NULL;
238 }
239 else
240 $ret["value"][] = $regel[$value];
241 }
242
243 }
244 else
245 return $ret;
246 }
247 return $ret;
248 }
249
250
251 function incMeta($group, $subgroup, $leaf)
252 {
253 $c =& pfcGlobalConfig::Instance();
254
255 $server = $c->serverid;
256 $db = $this->_connect();
257 $time = time();
258
259 // search for the existing leafvalue
260 $sql_count = "SELECT COUNT(*) AS C FROM ".$c->container_cfg_mysql_table." WHERE `server`='$server' AND `group`='$group' AND `subgroup`='$subgroup' AND `leaf`='$leaf' LIMIT 1";
261 $res = mysql_query($sql_count, $db);
262 $row = mysql_fetch_array($res, MYSQL_ASSOC);
263 if( $row['C'] == 0 )
264 {
265 $leafvalue = 1;
266 $sql_insert="REPLACE INTO ".$c->container_cfg_mysql_table." (`server`, `group`, `subgroup`, `leaf`, `leafvalue`, `timestamp`) VALUES('$server', '$group', '$subgroup', '$leaf', '".$leafvalue."', '".$time."')";
267 mysql_query($sql_insert, $db);
268 }
269 else
270 {
271 $sql_update="UPDATE ".$c->container_cfg_mysql_table." SET `leafvalue`= LAST_INSERT_ID( leafvalue + 1 ), `timestamp`='".$time."' WHERE `server`='$server' AND `group`='$group' AND `subgroup`='$subgroup' AND `leaf`='$leaf'";
272 mysql_query($sql_update, $db);
273 $res = mysql_query('SELECT LAST_INSERT_ID();', $db);
274 $row = mysql_fetch_array($res, MYSQL_ASSOC);
275 $leafvalue = $row['LAST_INSERT_ID()'];
276 }
277
278 $ret["value"][] = $leafvalue;
279 $ret["timestamp"][] = $time;
280
281 return $ret;
282 }
283
284
285 function rmMeta($group, $subgroup = null, $leaf = null)
286 {
287 $c =& pfcGlobalConfig::Instance();
288
289 $server = $c->serverid;
290 $db = $this->_connect();
291
292 $sql_delete = "DELETE FROM ".$c->container_cfg_mysql_table." WHERE `server`='$server'";
293
294 if($group != NULL)
295 $sql_delete .= " AND `group`='$group'";
296
297 if($subgroup != NULL)
298 $sql_delete .= " AND `subgroup`='$subgroup'";
299
300 if ($leaf != NULL)
301 $sql_delete .= " AND `leaf`='$leaf'";
302
303 mysql_query($sql_delete, $db);
304 return true;
305 }
306
307 function encode($str)
308 {
309 return addslashes(urlencode($str));
310 }
311
312 function decode($str)
313 {
314 return urldecode(stripslashes($str));
315 }
316
317}
318
319?>