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?>
320