mssql.php

<?php
/*
PHP REST SQL: A HTTP REST interface to relational databases
written in PHP

mssql.php :: Mssql database adapter
Copyright (C) 2008 Lee Smith <lee@aionex.com>

based on MySQL adapter mysql.php by Paul James
Copyright (C) 2004 Paul James <paul@peej.co.uk>

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

/* $id$ */

/**
 * PHP REST Mssql class
 * Mssql connection class.
 */
class mssql {
    
	/**
	 * @var int
	 */
	var $lastInsertPKeys;
	
	/**
	 * @var resource
	 */
    var $lastQueryResultResource;
    
    /**
     * @var resource Database resource
     */
    var $db;
    
    /**
     * Connect to the database.
     * @param str[] config
     */
    function connect($config) {
		
		$connString = sprintf(
			'host=%s dbname=%s user=%s password=%s',
			$config['server'],
			$config['database'],
			$config['username'],
			$config['password']
		);
		
		if ($this->db = mssql_pconnect($config['server'], $config['username'], $config['password'])) {
		    mssql_select_db($config['database']);
		    return TRUE;
	    }
		return FALSE;
    }

    /**
     * Close the database connection.
     */
    function close() {
        mssql_close($this->db);
    }
    
    /**
     * Get the columns in a table.
     * @param str table
     * @return resource A resultset resource
     */
    function getColumns($table) {
    	$qs = sprintf('SELECT * FROM information_schema.columns WHERE table_name =\'%s\'', $table);
		return mssql_query($qs, $this->db);
    }
    
    /**
     * Get a row from a table.
     * @param str table
     * @param str where
     * @return resource A resultset resource
     */
    function getRow($table, $where) {
        $result = mssql_query(sprintf('SELECT * FROM %s WHERE %s', $table, $where));   
    	if ($result) {
	        $this->lastQueryResultResource = $result;
	    }
        return $result;
    }
    
    /**
     * Get the rows in a table.
     * @param str primary The names of the primary columns to return
     * @param str table
     * @return resource A resultset resource
     */
    function getTable($primary, $table) {
        $result = mssql_query(sprintf('SELECT %s FROM %s', $primary, $table));  
        if ($result) {
            $this->lastQueryResultResource = $result;
        }
        return $result;        
    }

    /**
     * Get the tables in a database.
     * @return resource A resultset resource
     */
    function getDatabase() {
        return mssql_query('SELECT table_name FROM information_schema.tables');   
    }
	
    /**
     * Get the primary keys for the request table.
     * @return str[] The primary key field names
     */
    function getPrimaryKeys($table) {
      $primary = NULL;
      $query = sprintf("SELECT [name]
  FROM syscolumns 
	WHERE [id] IN (SELECT [id] 
                  FROM sysobjects 
		       WHERE [name] = '%s')
	AND colid IN (SELECT SIK.colid 
                   FROM sysindexkeys SIK 
                   JOIN sysobjects SO ON SIK.[id] = SO.[id]  
                  WHERE SIK.indid = 1
		      AND SO.[name] = '%s')", $table, $table);


      $result = mssql_query($query);
      //      while($row = mssql_fetch_assoc($result))
      $row = mssql_fetch_assoc($result);
	{
	  if($row)
	    $primary[] = $row['name'];
	}
      return $primary;
    }
	
    /**
     * Update a row.
     * @param str table
     * @param str values
     * @param str where
     * @return bool
     */
    function updateRow($table, $values, $where) {
        # translate from MySQL syntax :)
        $values = preg_replace('/"/','\'',$values);
        $values = preg_replace('/`/','"',$values); 
        $qs = sprintf('UPDATE %s SET %s WHERE %s', $table, $values, $where);
        $result = mssql_query($qs);       
        if ($result) {
            $this->lastQueryResultResource = $result;
        }
        return $result;
    }
    
    /**
     * Insert a new row.
     * @param str table
     * @param str names
     * @param str values
     * @return bool
     */
    function insertRow($table, $names, $values) {
        # translate from MySQL syntax
		$names = preg_replace('/`/', '"', $names); #backticks r so MySQL-ish! ;)
        $values = preg_replace('/"/', '\'', $values);
        $pkeys = join(', ', $this->getPrimaryKeys($table));
        
        $qs = sprintf(
			'INSERT INTO $table ("%s") VALUES ("%s") RETURNING (%s)',
			$names,
			$values,
			$pkeys
		);
        $result = mssql_query($qs); #or die(pg_last_error());
		
        $lastInsertPKeys = mssql_fetch_row($result);
        $this->lastInsertPKeys = $lastInsertPKeys;
		
        if ($result) {
            $this->lastQueryResultResource = $result;
        }
        return $result;
    }
    
    /**
     * Get the columns in a table.
     * @param str table
     * @return resource A resultset resource
     */
    function deleteRow($table, $where) {
        $result = mssql_query(sprintf('DELETE FROM %s WHERE %s', $table, $where));   
        if ($result) {
            $this->lastQueryResultResource = $result;
        }
        return $result;
    }
    
    /**
     * Escape a string to be part of the database query.
     * @param str string The string to escape
     * @return str The escaped string
     */
    function escape($string) {
        return mysql_escape_string($string);
    }
    
    /**
     * Fetch a row from a query resultset.
     * @param resource resource A resultset resource
     * @return str[] An array of the fields and values from the next row in the resultset
     */
    function row($resource) {
        return mssql_fetch_assoc($resource);
    }

    /**
     * The number of rows in a resultset.
     * @param resource resource A resultset resource
     * @return int The number of rows
     */
    function numRows($resource) {
        return mssql_num_rows($resource);
    }

    /**
     * The number of rows affected by a query.
     * @return int The number of rows
     */
    function numAffected() {
        return mssql_rows_affected($this->lastQueryResultResource);
    }
    
    /**
     * Get the ID of the last inserted record. 
     * @return int The last insert ID ('a/b' in case of multi-field primary key)
     */
    function lastInsertId() {
        return join('/', $this->lastInsertPKeys);
    }
    
}
?>