qbuilder/addon/classes/class.sql.inc

<?php
   class sqlConnection
   {
      var $sql_query    = array();
      var $error        = array(" ");
      var $conn         = "";

      var $dbs_type     = "MySQL";
      var $dbs_hostname = "localhost";
      var $dbs_username = "root";
      var $dbs_password = "";

      var $dbs_database = "";
      var $dbs_backup   = "";

      var $dbs_error    = "";

      var $error_log    = true;
      var $error_table  = "fehler_sql";


      function getmicrotime ()
      {
         list($usec, $sec) = explode(" ",microtime());
         return ((float)$usec + (float)$sec);
      }

      function sql_connect ()
      {
         switch ($this->dbs_type)
         {
            case "MySQL":
               if (!@$this->conn = mysql_connect($this->dbs_hostname, $this->dbs_username, $this->dbs_password))
               {
                  die($this->dbs_error);
               }
               mysql_select_db($this->dbs_database, $this->conn) or die(mysql_error());
               break;

            case "MSSQL":
               if (!@$this->conn = mssql_connect($this->dbs_hostname, $this->dbs_username, $this->dbs_password))
               {
                  die($this->dbs_error);
               }
               mssql_select_db($this->dbs_database, $this->conn) or die(mssql_get_last_message());
               break;
         }
      }

      function sql_exec ($sql)
      {
         switch ($this->dbs_type)
         {
            case "MySQL":
               $time_start = $this->getmicrotime();

               $erg = mysql_query($sql, $this->conn);

               $time_end = $this->getmicrotime();
               $time = $time_end - $time_start;

               $error = mysql_error($this->conn);
               $errno = mysql_errno($this->conn);

               if ("" == $errno) $errno = 0;

               if ($erg && 0 == $errno) $zeilen = @mysql_num_rows($erg)+0; else $zeilen = -1;

               array_push($this->sql_query,array($sql,$zeilen,mysql_affected_rows($this->conn),$errno,$error,$time));
               array_push($this->error,$error);
               break;

            case "MSSQL":
               $time_start = $this->getmicrotime();

               $erg = @mssql_query($sql,$this->conn);

               $time_end = $this->getmicrotime();
               $time = $time_end - $time_start;

               $error = mssql_get_last_message();
               if (strpos($error,"Datenbankkontext"))
               {
                  $error = " ";
               }

               if ("" == $error || in_array($error,$this->error))
               {
                  $zeilen = @mssql_num_rows($erg) + 0;
                  $errno = 0;
                  $error = "";
               }
                else
               {
                  $zeilen = -1;
                  $errno = 1;
               }

               array_push($this->sql_query,array($sql,$zeilen,mssql_rows_affected($this->conn),$errno,$error,$time));
               array_push($this->error,$error);
               break;
         }
         if ($this->error_log && $errno != 0)
         {
            $sql2 = "INSERT INTO {$this->error_table} VALUES ('{$errno}',\"{$error}\",\"{$sql}\")";
            switch ($this->dbs_type)
            {
               case "MySQL":
                  $erg2 = mysql_query($sql2,$this->conn);
                  break;
               case "MSSQL":
                  $erg2 = mssql_query($sql2,$this->conn);
                  break;
            }
         }
         return $erg;
      }

      function sql_fetch_array ($erg)
      {
         switch ($this->dbs_type)
         {
            case "MySQL":
               $value = mysql_fetch_array($erg);
               break;

            case "MSSQL":
               $value = mssql_fetch_array($erg);
               break;
         }
         return $value;
      }

      function sql_fetch_assoc ($erg)
      {
         if ($erg)
         {
            switch ($this->dbs_type)
            {
               case "MySQL":
                  $value = mysql_fetch_assoc($erg);
                  break;

               case "MSSQL":
                  $value = mssql_fetch_assoc($erg);
                  break;
            }
            return $value;
         }
          else
         {
            return false;
         }
      }

      function sql_fetch_row ($erg)
      {
         if ($erg)
         {
            switch ($this->dbs_type)
            {
               case "MySQL":
                  $value = mysql_fetch_row($erg);
                  break;

               case "MSSQL":
                  $value = mssql_fetch_row($erg);
                  break;
            }
            return $value;
         }
          else
         {
            return false;
         }
      }

      function sql_result ($erg,$row,$col)
      {
         if ($erg)
         {
            switch ($this->dbs_type)
            {
               case "MySQL":
                  $value = mysql_result($erg,$row,$col);
                  break;

               case "MSSQL":
                  $value = mssql_result($erg,$row,$col);
                  break;
            }
            return $value;
         }
          else
         {
            return false;
         }
      }

      function sql_num_rows ($erg)
      {
         if ($erg)
         {
            switch ($this->dbs_type)
            {
               case "MySQL":
                  $value = mysql_num_rows($erg);
                  break;

               case "MSSQL":
                  $value = mssql_num_rows($erg);
                  break;
            }
            return $value;
         }
          else
         {
            return 0;
         }
      }

      function sql_affected_rows ()
      {
         if ($erg)
         {
            switch ($this->dbs_type)
            {
               case "MySQL":
                  $value = mysql_affected_rows($this->conn);
                  break;

               case "MSSQL":
                  $value = mssql_rows_affected($this->conn);
                  break;
            }
            return $value;
         }
          else
         {
            return 0;
         }
      }

      function sql_insert_id ()
      {
         switch ($this->dbs_type)
         {
            case "MySQL":
               $value = mysql_insert_id($this->conn);
               break;

            case "MSSQL":
               $erg = @mssql_query("SELECT MAX(id) FROM {$this->table}",$this->conn);
               $value = mssql_result($this->conn,0,0);
               break;
         }
         return $value;
      }

      function sql_error ()
      {
         $t_query = end($this->sql_query);
         return $t_query[4];
      }

      function sql_errno ()
      {
         $t_query = end($this->sql_query);
         return $t_query[3];
      }

      function sql_fields ($id)
      {
         $id -= 1;
         $sql = $this->sql_query[$id][0];
         $erg = mysql_query($sql);

         $i = 0;
         while ($i < mysql_num_fields($erg))
         {
            $fields[$i] = mysql_fetch_field($erg, $i);
            $fieldset[$fields[$i]->table][$fields[$i]->name] = $i;
            $i++;
         }

         foreach ($fieldset as $t_table => $t_fields)
         {
            $sql2 = "SHOW COLUMNS FROM {$t_table}";
            $erg2 = mysql_query($sql2);

            while ($k = mysql_fetch_assoc($erg2))
            {
               if (isset($t_fields[$k['Field']]))
               {
                  $k['id'] = $fieldset[$t_table][$k['Field']];
                  if ("" != $k['Default'])
                  {
                     $t_default = "DEFAULT '{$k['Default']}'";
                  }
                   else
                  {
                     $t_default = "";
                  }

                  if ("" == $k['Null']) $k['Null'] = "NOT NULL";

                  $k['Row'] = "`{$k['Field']}` {$k['Type']} {$k['Null']} {$t_default}";

                  $fieldset[$t_table][$k['Field']] = $k;
               }
            }
         }

         $this->sql_fields_anzeigen ($fieldset,$sql);

         return ($fieldset);
      }

      function sql_fields_anzeigen ($fieldset,$insert_query)
      {
         $number = rand(10000,99999);

         $create_query = "CREATE TABLE `temp_{$number}` (\n";

         foreach ($fieldset as $t_table => $t_fields)
         {
            foreach ($t_fields as $t_field)
            {
               $rows[$t_field['id']] = $t_field['Row'];
            }
         }

         $create_query .= implode(",\n",$rows) . "\n";
         $create_query .= ") TYPE=MyISAM ;";

         $insert_query = "INSERT INTO `temp_{$number}`\n" . $insert_query;

         echo "<div><pre>" . $create_query . "\n\n" . $insert_query . "\n\n</pre></div>";
      }

      function sqlbox_anzeigen ()
      {
         array_shift($this->error);
         $this->error = array_filter($this->error);
         $sqlbox = "<b>" . count($this->error) . "</b><br>";

         foreach ($this->sql_query as $id => $info)
         {
            $sqlbox .= "#" . ($id+1) . ":<br>";

            $info[0] = nl2br($info[0]);

            if (0 == $info[3])
            {
               $sqlbox .= " <font style='color:#000080;'>{$info[0]};</font><br>";
               if (0 < $info[1])
               {
                  $sqlbox .= " Datens&auml;tze: " . $info[1] . "<br>";
               }
                elseif (0 < $info[2])
               {
                  $sqlbox .= " Betroffene Datens&auml;tze: " . $info[2] . "<br>";
               }
            }
             else
            {
               $sqlbox .= " <font style='color:#800000;'>{$info[0]};</font><br>";
               $sqlbox .= " <b>" . $info[3] . "</b>: " . $info[4] . "<br>";
            }
            $sqlbox .= " Dauer: " . $info[5] . "<br><br>\n";
         }

         include (TEMPLATE_DIR."template.sql.inc");
      }
   }
?>