Changeset 7804

Show
Ignore:
Timestamp:
06/09/09 05:40:26 (4 years ago)
Author:
xrobau
Message:

Almost finished apart from alter_col, which I'll do now. Yay!

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • modules/branches/2.6/core/agi-bin/sql.php

    r7803 r7804  
    177177    return $dbhandle; 
    178178  } elseif ($this->dbtype == 'sqlite3') { 
    179     // Database is SQLite. It's preferrable to use the inbuilt sqlite_ commands, so 
    180     // check if they exist first. 
    181     if (function_exists('sqlite_open')) { 
    182       $dbhandle = sqlite_open($this->dbfile, 0666, $sqlerr); 
     179    // Database is SQLite. It's preferrable to use the inbuilt PHP5 sqlite3 class, so 
     180    // check if that exist first. Requires PHP 5.3.0 
     181    if (class_exists('SQLite3')) { 
     182      $dbhandle = new SQLite3($this->dbfile, SQLITE3_OPEN_READWRITE); 
    183183      if (!$dbhandle) { 
    184184        $this->errstr = 'SEVERE: AGI could not connect to (native) SQLite Database "'. 
    185           $this->dbfile.'" - '.$sqlerr; 
     185          $this->dbfile.'" - '.$dbhandle->lastErrorMsg; 
    186186        $this->debug($this->errstr, 1); 
    187187        return null; 
    …… 
    197197      // It's not loaded. Load it. 
    198198      dl('sqlite3.so'); 
     199      // That would have crashed PHP if it couldn't load it, so we know it's loaded if 
     200      // it got to here. 
    199201      $this->debug('Loaded', 4); 
    200     } 
     202    }  
    201203    // We now have sqlite3_ functions. Use them! 
    202204    $dbhandle = sqlite3_open($this->dbfile); 
    …… 
    287289      return $sqlresult; 
    288290    case "sqlite": 
    289       $res = sqlite_query($this->dbhandle, $result, SQLITE_BOTH, $errmsg); 
     291      $res = $this->dbhandle->query($result); 
    290292      if (!$res) { 
    291         $this->errstr = "SQLite Error: '$errmsg' with query '$result'"; 
     293        $this->errstr = "SQLite3 Error: '".$this->dbhandle->lastErrorMsg."' with query '$result'"; 
    292294        $this->debug($this->errstr, 1); 
    293295        return false; 
    …… 
    295297      // Loop through the returned result set, loading it into the array to return 
    296298      // to the caller. 
    297       $this->numrows = sqlite_num_rows($res); 
    298299      // Return the correct type. 
    299300      if ($type == "NONE") { 
    300301        return true; 
    301302      } 
     303      $i = 0; 
    302304      if ($type == "NUM") { 
    303         $sqlresult = sqlite_fetch_all($res, SQLITE_NUM); 
    304       } elseif ($type == "ASSOC") { 
    305         $sqlresult = sqlite_fetch_all($res, SQLITE_ASSOC); 
     305        while ($sqlresult[$i++] = $res->fetchArray(SQLITE3_NUM)); 
     306      } elseif ($type = "ASSOC") { 
     307        while ($sqlresult[$i++] = $res->fetchArray(SQLITE3_ASSOC)); 
    306308      } else { 
    307         $sqlresult = sqlite_fetch_all($res, SQLITE_BOTH); 
    308       } 
     309        while ($sqlresult[$i++] = $res->fetchArray(SQLITE3_BOTH)); 
     310      } 
     311      $res->finalize(); 
     312      $this->numrows = $i; 
    309313      return $sqlresult; 
    310314    case "sqlite3": 
    …… 
    322326        $res = sqlite3_exec($this->dbhandle, $result); 
    323327        if (!$res) { 
    324           $this->errstr = $result; 
     328          $this->errstr = sqlite3_error($this->dbhandle); 
    325329          return false; 
    326330        } else { 
    …… 
    355359    case "sqlite": 
    356360    case "sqlite3": 
    357       return $this->sql("ALTER TABLE `$from` RENAME TO `$to`", "NONE", true); 
     361      $this->sql("DROP TABLE `$to`", "NONE", true); 
     362      $sql = "ALTER TABLE `$from` RENAME TO `$to`"; 
     363      if(!$res = $this->sql($sql, "NONE", true)) { 
     364        print "Error in sql `$sql` - ".$this->errstr."\n"; 
     365        return false; 
     366      } else { 
     367        return true; 
     368      } 
    358369    default: 
    359370      $this->debug("SEVERE: Database type '".$this->db."' NOT SUPPORTED (rename_table)", 0); 
    …… 
    396407    // sqlite_master table. I don't know enough about sqlite to be able to fix it. This  
    397408    // works though. 
    398       $res = sqlite3_query($this->dbhandle,  
    399         "select `tbl_name`,`sql` from `sqlite_master` where `tbl_name`='$tablename'"); 
    400       $sqlarr = sqlite3_fetch_array($res); 
    401       print_r($sqlarr); 
     409 
     410      if ($this->db == "sqlite3") { 
     411        $res = sqlite3_query($this->dbhandle,  
     412          "select `tbl_name`,`sql` from `sqlite_master` where `tbl_name`='$tablename'"); 
     413        $sqlarr = sqlite3_fetch_array($res); 
     414        sqlite3_query_close($res); 
     415      } else { 
     416        // We're using the SQLite3 class, which works normally. 
     417        $res = $this->sql("select `tbl_name`,`sql` from `sqlite_master` where `tbl_name`='$tablename'" 
     418          , "ASSOC", true); 
     419        $sqlarr = $res[0]; 
     420      } 
     421 
    402422      $sqlCreate = $sqlarr['sql']; 
    403       $sqlStripped = preg_replace("/^\s+`$colname`.+$/m", "", $sqlCreate); 
    404       print "The col $colname should not be in this: $sqlStripped\n"; 
     423      // This deletes any line that starts with any number of space characters 
     424      // (^\s+), then has a back tick (`), the name ($colname), another tick 
     425      // (`) and anything else (.+) until the end of the line ($) including the 
     426      // new line character (\n), and it needs to be multiline aware (m) 
     427      $sqlStripped = preg_replace("/^\s+`$colname`.+$\n/m", "", $sqlCreate); 
     428      if ($sqlStripped == $sqlCreate) { 
     429        // Nothing to remove. 
     430        $this->debug("Column $colname doesn't exist in table $tablename", 4); 
     431        return true; 
     432      } 
     433 
     434      // Rename table 
     435      $this->rename_table($tablename, "${tablename}_temp"); 
     436 
     437      // Create new table without $colname 
     438      if (!$this->sql($sqlStripped, "NONE", true)) { 
     439        $this->debug("SQL Command Failed: $sqlStripped\n".$this->errstr."\n"); 
     440      } 
     441 
     442      // Split the CREATE command into col names and types 
     443      preg_match_all('/\n\s+(`.+`)\s(.+)/', $sqlStripped, $arrNewTableInfo); 
     444 
     445      // Join the table names together, stripping off the last comma if there is one 
     446      $strAllCols = implode(",", $arrNewTableInfo[1]); 
     447 
     448      // Copy everything from the old table to the new 
     449      $sql = "INSERT INTO `$tablename` SELECT $strAllCols FROM ${tablename}_temp"; 
     450      if (!$this->sql($sql, "NONE", true)) { 
     451        $this->debug("SQL Command Failed: $sqlStripped\n".$this->errstr."\n"); 
     452      } 
     453 
     454      // Delete the old table 
     455      $sql = "DROP TABLE ${tablename}_temp"; 
     456      if (!$this->sql($sql, "NONE", true)) { 
     457        $this->debug("SQL Command Failed: $sqlStripped\n".$this->errstr."\n"); 
     458      } 
    405459      break; 
    406460    default: 
    …… 
    456510      return mysql_real_escape_string($str, $this->dbhandle); 
    457511    case "sqlite": 
    458       return sqlite_escape_string($str); 
    459512    case "sqlite3": 
    460513      // SQLite only needs to care about single ticks - "'". Escape