1<?php
2/**
3 * src/container/oracle.class.php
4 *
5 * Copyright © 2006 Stephane Gully <stephane.gully@gmail.com>
6 * Modifications by Golemwashere
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
23/*
24Oracle specific parameters:
25$params["container_type"] = "oracle";
26$params["container_cfg_oracle_host"] = "localhost";
27$params["container_cfg_oracle_port"] = 1521;
28$params["container_cfg_oracle_database"] = "XE";
29$params["container_cfg_oracle_table"] = "phpfreechat";
30$params["container_cfg_oracle_username"] = "orauser";
31$params["container_cfg_oracle_password"] = "orapw";
32
33*/
34
35
36require_once dirname(__FILE__)."/../pfccontainer.class.php";
37
38// include pear DB classes
39require_once 'DB.php';
40
41/**
42 * pfcContainer_Oracle is a concret container which store data into Oracle database
43 *
44 *
45 * @author Golemwashere
46 * @author Stephane Gully <stephane.gully@gmail.com>
47 * @author HenkBB
48 */
49class pfcContainer_Oracle extends pfcContainerInterface
50{
51  var $_db = null;
52  var $_sql_create_table = "
53  CREATE TABLE phpfreechat (
54  server varchar2(200) NOT NULL default '',
55  groupg varchar2(200) NOT NULL default '',
56  subgroup varchar2(200) NOT NULL default '',
57  leaf varchar2(200) NOT NULL default '',
58  leafvalue varchar2(4000) NOT NULL,
59  timestampg number(20) NOT NULL default 0,
60);
61
62  PRIMARY KEY  (server,groupg,subgroup,leaf);
63  INDEX (server,group,subgroupg,timestampg);
64  CREATE SEQUENCE phpfreechat_leafvalue_seq
65
66  ";
67
68
69  function pfcContainer_Oracle()
70  {
71    pfcContainerInterface::pfcContainerInterface();
72  }
73
74  function getDefaultConfig()
75  {
76    $cfg = pfcContainerInterface::getDefaultConfig();
77    $cfg["oracle_host"] = 'localhost';
78    $cfg["oracle_port"] = 1521;
79    $cfg["oracle_database"] = 'XE';
80    $cfg["oracle_table"]    = 'phpfreechat';
81    $cfg["oracle_username"] = 'phpfreechatuser';
82    $cfg["oracle_password"] = 'freechatpass';
83    return $cfg;
84  }
85
86  function init(&$c)
87  {
88
89    $errors = pfcContainerInterface::init($c);
90
91    // connect to the db
92    $db = $this->_connect($c);
93    if ($db === FALSE)
94    {
95      $errors[] = _pfc("DB container: connect error");
96      return $errors;
97    }
98
99    // create the db if it doesn't exists
100    // golemwashere: commented out this part for now, DB must be manually created
101    /*
102    $db_exists = false;
103    $db_list = mysql_list_dbs($db);
104    while (!$db_exists && $row = mysql_fetch_object($db_list))
105      $db_exists = ($c->container_cfg_mysql_database == $row->Database);
106    if (!$db_exists)
107    {
108      $query = 'CREATE DATABASE '.$c->container_cfg_mysql_database;
109      $result = mysql_query($query, $db);
110      if ($result === FALSE)
111      {
112        $errors[] = _pfc("Mysql container: create database error '%s'",mysql_error($db));
113        return $errors;
114      }
115      mysql_select_db($c->container_cfg_mysql_database, $db);
116    }
117
118    // create the table if it doesn't exists
119    $query = $this->_sql_create_table;
120    $query = str_replace('%engine%',              $c->container_cfg_mysql_engine,$query);
121    $query = str_replace('%table%',               $c->container_cfg_mysql_table,$query);
122    $query = str_replace('%fieldtype_server%',    $c->container_cfg_mysql_fieldtype_server,$query);
123    $query = str_replace('%fieldtype_group%',     $c->container_cfg_mysql_fieldtype_group,$query);
124    $query = str_replace('%fieldtype_subgroup%',  $c->container_cfg_mysql_fieldtype_subgroup,$query);
125    $query = str_replace('%fieldtype_leaf%',      $c->container_cfg_mysql_fieldtype_leaf,$query);
126    $query = str_replace('%fieldtype_leafvalue%', $c->container_cfg_mysql_fieldtype_leafvalue,$query);
127    $query = str_replace('%fieldtype_timestamp%', $c->container_cfg_mysql_fieldtype_timestamp,$query);
128    $result = mysql_query($query, $db);
129    if ($result === FALSE)
130    {
131      $errors[] = _pfc("Mysql container: create table error '%s'",mysql_error($db));
132      return $errors;
133    }
134    return $errors;
135    */
136
137  }
138
139  function _connect($c = null)
140  {
141    if (!$this->_db)
142    {
143      if ($c == null) $c =& pfcGlobalConfig::Instance();
144
145      $dsn = array(
146    'phptype'  => 'oci8',
147    'username' => $c->container_cfg_oracle_username,
148    'password' => $c->container_cfg_oracle_password,
149    'hostspec' => '//'.$c->container_cfg_oracle_host.':'.$c->container_cfg_oracle_port.'/'.$c->container_cfg_oracle_database
150     );
151
152$this->_db = DB::connect($dsn);
153if (DB::isError($this->_db))
154{
155 echo 'Cannot connect to database: ' . $this->_db->getMessage();
156}
157
158
159
160    }
161
162
163
164    return $this->_db;
165  }
166
167  function setMeta($group, $subgroup, $leaf, $leafvalue = NULL)
168  {
169    $c =& pfcGlobalConfig::Instance();
170
171    $server = $c->serverid;
172    $db = $this->_connect();
173
174    if ($leafvalue == NULL){$leafvalue=" ";};
175    # clean leafvalue:
176    $leafvalue=str_replace("'", "''", $leafvalue);
177    # GOLEMQUERY #1
178    $sql_count = "SELECT COUNT(*) AS C FROM ".$c->container_cfg_oracle_table." WHERE server='$server' AND groupg='$group' AND subgroup='$subgroup' AND leaf='$leaf' and rownum <= 1";
179    # GOLEMQUERY #2
180    $sql_insert="INSERT INTO ".$c->container_cfg_oracle_table." (server, groupg, subgroup, leaf, leafvalue, timestampg) VALUES('$server', '$group', '$subgroup', '$leaf', '$leafvalue', trunc((to_number(cast((systimestamp AT TIME ZONE 'GMT') as date)-cast(TO_TIMESTAMP_TZ ('01-01-1970 00:00:00 GMT', 'DD-MM-YYYY HH24:MI:SS TZR') as date))*86400)))";
181    # mysql was:
182    #$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'";
183    # GOLEMQUERY #3
184    $sql_update="UPDATE ".$c->container_cfg_oracle_table." SET leafvalue='$leafvalue', timestampg= trunc((to_number(cast((systimestamp AT TIME ZONE 'GMT') as date)-cast(TO_TIMESTAMP_TZ ('01-01-1970 00:00:00 GMT', 'DD-MM-YYYY HH24:MI:SS TZR') as date))*86400)) WHERE  server='$server' AND groupg='$group' AND subgroup='$subgroup' AND leaf='$leaf'";
185
186    if (DEBUGSQL) error_log("sql_count $sql_count");
187    $res = $this->_db->query($sql_count);
188    if (DB::isError($res))
189    {
190 			error_log("sql_count error $sql_count " . $res->getMessage());
191    }
192
193
194    $row = $res->fetchRow(DB_FETCHMODE_ASSOC);
195
196/* mysql was:
197    $res = mysql_query($sql_count, $db);
198    $row = mysql_fetch_array($res, MYSQL_ASSOC);
199*/
200
201    if( $row['C'] == 0 )
202    {
203      $res=$this->_db->query($sql_insert);
204      if (DB::isError($res)) {  error_log("sql insert error: $sql_insert " . $res->getMessage()); }
205      if (DEBUGSQL) error_log("sql_insert: $sql_insert");
206      return 0; // value created
207    }
208    else
209    {
210      if ($sql_update != "")
211      {
212        $res=$this->_db->query($sql_update);
213              if (DB::isError($res))
214										{  error_log("sql update error: $sql_update " . $res->getMessage()); }
215       if (DEBUGSQL) error_log("sql_update $sql_update");
216      }
217      return 1; // value overwritten
218    }
219  }
220
221
222  function getMeta($group, $subgroup = null, $leaf = null, $withleafvalue = false)
223  {
224    $c =& pfcGlobalConfig::Instance();
225
226    $ret = array();
227    $ret["timestamp"] = array();
228    $ret["value"]     = array();
229
230    $server = $c->serverid;
231    $db = $this->_connect();
232
233    $sql_where = "";
234    $sql_group_by = "";
235    $value = "leafvalue";
236
237    if ($group != NULL)
238    {
239      $sql_where   .= " AND groupg='$group'";
240      $value        = "subgroup";
241      #$sql_group_by = "GROUP BY '$value'";
242      $sql_group_by = "GROUP BY $value";
243    }
244
245    if ($subgroup != NULL)
246    {
247      $sql_where   .= " AND subgroup='$subgroup'";
248      $value        = "leaf";
249      $sql_group_by = "";
250    }
251
252    if ($leaf != NULL)
253    {
254      $sql_where   .= " AND leaf='$leaf'";
255      $value        = "leafvalue";
256      $sql_group_by = "";
257    }
258
259    # GOLEMQUERY #4
260    $sql_select="SELECT $value, timestampg FROM ".$c->container_cfg_oracle_table." WHERE server='$server' $sql_where $sql_group_by ORDER BY timestampg";
261    if ($sql_select != "")
262    {
263      $thisresult = $this->_db->query($sql_select);
264          if (DEBUGSQL) error_log("sql_select: $sql_select");
265          if (DB::isError($thisresult))   {  error_log("sql_select error $sql_select " . $thisresult->getMessage());         }
266
267
268      #if (mysql_num_rows($thisresult))
269      $this->_db->setOption('portability', DB_PORTABILITY_NUMROWS);
270
271      #error_log("numrows $numrows");
272
273      if ($thisresult->numRows())
274      {
275        #while ($regel = mysql_fetch_array($thisresult))
276        while ($regel = $thisresult->fetchRow(DB_FETCHMODE_ASSOC))
277        {
278          $ret["timestamp"][] = $regel["TIMESTAMPG"];
279          if ($value == "leafvalue")
280          {
281            if ($withleafvalue)
282              $ret["value"][]     = $regel[strtoupper($value)];
283            else
284              $ret["value"][]     = NULL;
285          }
286          else
287            $ret["value"][] = $regel[strtoupper($value)];
288        }
289
290      }
291      else
292        return $ret;
293    }
294    return $ret;
295  }
296
297
298  function incMeta($group, $subgroup, $leaf)
299  {
300    $c =& pfcGlobalConfig::Instance();
301
302    $server = $c->serverid;
303    $db = $this->_connect();
304    $time = time();
305
306    // search for the existing leafvalue
307    # GOLEMQUERY #5
308    $sql_count = "SELECT COUNT(*) AS C FROM ".$c->container_cfg_oracle_table." WHERE server='$server' AND groupg='$group' AND subgroup='$subgroup' AND leaf='$leaf' and rownum <= 1";
309    $res = $this->_db->query($sql_count);
310    if (DB::isError($res))   {  error_log("sql_count error $sql_count " . $res->getMessage());         }
311    if (DEBUGSQL) error_log("sql select $sql_count");
312    $row = $res->fetchRow(DB_FETCHMODE_ASSOC);
313    #$res = mysql_query($sql_count, $db);
314    #$row = mysql_fetch_array($res, MYSQL_ASSOC);
315    if( $row['C'] == 0 )
316    {
317      $leafvalue = 1;
318      #$sql_insert="REPLACE INTO ".$c->container_cfg_mysql_table." (`server`, `group`, `subgroup`, `leaf`, `leafvalue`, `timestamp`) VALUES('$server', '$group', '$subgroup', '$leaf', '".$leafvalue."', '".$time."')";
319      # GOLEMQUERY # 6
320      $sql_insert="INSERT INTO ".$c->container_cfg_oracle_table." (server, groupg, subgroup, leaf, leafvalue, timestampg) VALUES('$server', '$group', '$subgroup', '$leaf','$leafvalue', trunc((to_number(cast((systimestamp AT TIME ZONE 'GMT') as date)-cast(TO_TIMESTAMP_TZ ('01-01-1970 00:00:00 GMT', 'DD-MM-YYYY HH24:MI:SS TZR') as date))*86400)))";
321
322      #mysql_query($sql_insert, $db);
323      $res=$this->_db->query($sql_insert);
324      if (DB::isError($res)){ error_log("sql insert error $sql_insert " . $res->getMessage()); }
325     if (DEBUGSQL) error_log("sql_insert $sql_insert");
326    }
327    else
328    {
329      # mysql was:
330      #$sql_update="UPDATE ".$c->container_cfg_mysql_table." SET leafvalue= LAST_INSERT_ID( leafvalue + 1 ), `timestamp`='".$time."' WHERE  server='$server' AND groupg='$group' AND subgroup='$subgroup' AND leaf='$leaf'";
331      # GOLEMQUERY #7
332      # test using sequence nextval
333      $sql_update="UPDATE ".$c->container_cfg_oracle_table." SET leafvalue= phpfreechat_leafvalue_seq.NEXTVAL, timestampg=trunc((to_number(cast((systimestamp AT TIME ZONE 'GMT') as date)-cast(TO_TIMESTAMP_TZ ('01-01-1970 00:00:00 GMT', 'DD-MM-YYYY HH24:MI:SS TZR') as date))*86400)) WHERE  server='$server' AND groupg='$group' AND subgroup='$subgroup' AND leaf='$leaf'";
334
335      $res=$this->_db->query($sql_update);
336      if (DB::isError($res)){ error_log("problema update: $sql_update " . $res->getMessage()); }
337	if (DEBUGSQL) error_log("sql_update $sql_update");
338      #
339      # GOLEMQUERY #8
340      # test using sequence currval
341      $sql_last="SELECT phpfreechat_leafvalue_seq.currVAL as lastleaf FROM dual";
342      $res = $this->_db->query($sql_last);
343      if (DB::isError($res))   {  error_log("error in SELECT lastleaf $sql_last" . $res->getMessage());         }
344       if (DEBUGSQL) error_log("select: SELECT phpfreechat_leafvalue_seq.currVAL as lastleaf FROM dual");
345      #$row = mysql_fetch_array($res, MYSQL_ASSOC);
346      $row = $res->fetchRow(DB_FETCHMODE_ASSOC);
347      $leafvalue = $row['LASTLEAF'];
348    }
349
350    $ret["value"][]     = $leafvalue;
351    $ret["timestamp"][] = $time;
352
353    return $ret;
354  }
355
356
357  function rmMeta($group, $subgroup = null, $leaf = null)
358  {
359    $c =& pfcGlobalConfig::Instance();
360
361    $server = $c->serverid;
362    $db = $this->_connect();
363    # GOLEMQUERY #9
364    $sql_delete = "DELETE FROM ".$c->container_cfg_oracle_table." WHERE server='$server'";
365
366    if($group != NULL)
367      $sql_delete .= " AND groupg='$group'";
368
369    if($subgroup != NULL)
370      $sql_delete .= " AND subgroup='$subgroup'";
371
372    if ($leaf != NULL)
373      $sql_delete .= " AND leaf='$leaf'";
374
375    #mysql_query($sql_delete, $db);
376    $res=$this->_db->query($sql_delete);
377    if (DB::isError($res))
378          { error_log('sql_delete $sql_delete ' . $res->getMessage()); }
379
380    if (DEBUGSQL) error_log("sql_delete $sql_delete");
381
382    return true;
383  }
384
385  function encode($str)
386  {
387    return $str;
388    //return addslashes(urlencode($str));
389  }
390
391  function decode($str)
392  {
393  	return $str;
394    //return urldecode(stripslashes($str));
395  }
396
397
398
399}
400
401?>
402