Re: [PHP4BETA] SQL abstraction

On 30 Jun 1999, Jim Winstead wrote: > Might be useful for ideas. Come again? :-) If you're looking for ideas, mine are listed below, along with an API suggestion. Much of what I suggest would probably be better written in PHP. We should seriously start considering implementing something like CPAN in PHP, so we have defined ways of distributing and installing standard packages. - Stig DATABASE ABSTRACTION LAYER FOR PHP 4: * The abstraction layer should be available both as a class and as functions. The main functionality could be in the functions, while the class is merely a wrapper. * This API is bound to change at time, so we should always define an API version and be both backwards and forwards compatible. * Define a number of "capabilities" that each database extension defines whether or not it features. This help users away from some of the "least common denominator" problems. Examples: if ($db->capableOf("transactions")) { $db->transactionStart(); } if ($db->capableOf("prepare")) { $stmt = $db->prepare('INSERT INTO mytable VALUES(?, ?, ?)'); while (list($id, $data) = each($insertData)) { $res = $db->execute($stmt, &$data); } } else { while (list($id, $data) = each($insertData)) { $res = $db->query("INSERT INTO mytable VALUES($data[0],". "'".$db->escape($data[1])."',$data[2])"); } } Such capabilities should be in from the beginning IMHO. * We need to define an abstract way of specifying a database or data source. We could do that Perl's DBI is doing, or we could invent our own URL method like sql://user:pass@server/database (then we can just use parse_url() on the data source parameter :-). * Errors returned should be available both in their native form, but also as a set of error codes defined by the abstraction layer. We should provide a bunch of constants that each extension should define values for. Some random examples: DB_ERROR_TRUNCATED, DB_ERROR_DIVZERO, DB_ERROR_INVALID_NUMBER, DB_ERROR_INVALID_DATE, DB_ERROR_CONSTRAINT, DB_ERROR_SYNTAX, DB_ERROR_ALREADY_EXISTS, DB_ERROR_NOT_CAPABLE * Portable prepare/execute implementation. This means that your query should not have one syntax for Oracle, but another for Solid. This would require the abstraction layer to parse the query, but I think that's ok as long as we have defined what to look for. We could also implement a poor man's prepare/execute for the databases that don't have it, for people who don't like messing around with magic quotes (I don't.) I've done this for ODBC and OCI8, it's not very hard. * Portable long/binary reading implementation. * Optional transaction support (should be indicated through a capability whether the db supports them). * Each database must define a function [pointer] for quoting string data, so we can do db_escape() instead of knowing what semantics sybase vs. mysql magic quotes have and what to use for our db. * Portable sequences or sequence emulation. This one is tricky. * A nice set of query utility functions. For example: // returns the first column of the first row: $db->do_one("SELECT name FROM table WHERE id = $id"); $db->do_one("SELECT name FROM table WHERE id = ?", $id); // returns a row as an array $db->do_onerow("SELECT * FROM table WHERE id = $id"); // returns a row as an array $db->do_onerowinto("SELECT * FROM table WHERE id = ?", &row, $id); // returns an array of rows with all the result data $db->do_all("SELECT * FROM table"); // returns an array with data from column 0 only: $db->do_col("SELECT name FROM table", 0); // returns an associative array with the first column as the // key and the second column as the value: $db->do_assoc("SELECT id, name FROM table"); Here's a function/method list: is_db_conn($handle) Returns true if $handle is a connection handle from the abstraction layer. is_db_stmt($handle) Returns true if $handle is a statement/result handle from the abstraction layer. db_api_version() $db->apiVersion() Returns the abstraction layer API version as an integer based on date (e.g. 19990820). db_backend() $db->backend() Return the name of the database backend, e.g. "mysql". db_connect($dsn[, $user, $pw]) $db->connect($dsn[, $user, $pw]) db_pconnect($dsn[, $user, $pw]) $db->pconnect($dsn[, $user, $pw]) Connect to $dsn as $user/$pw. The format of $dsn is to be defined. db_capable_of($conn, $capability) $db->capableOf($capability) Tell whether the database behind $conn is capable of something. List of capabilities to be defined. db_prepare($conn, $query) $db->prepare($query) Parse a query and prepare it to be executed. db_execute($stmt, $data...) $db->execute($stmt, $data...) Execute a query with $data as parameters to it. db_query($conn, $query) $db->query($query) Parse and execute a query. db_fetch($stmt) $db->fetch($stmt) Fetch a row of data from the database so it can be read by db_get(). db_fetch_row($stmt) $db->fetchRow($stmt) Fetch a row of data from the database and return it as an array. db_fetch_row_into($stmt, $array) $db->fetchRowInto($stmt, $array) Fetch a row of data from the database and store it in $array. db_get($stmt, $column) $db->get($stmt, $column) Return a single column of data from the currently fetched row. db_num_rows($stmt) $db->numRows($stmt) Return the number of rows, (or false if not known), in $stmt. db_num_cols($stmt) $db->numCols($stmt) Return the number of columns in $stmt. db_free($resource) $db->free($resource) Free memory associated with $resource. db_autocommit($conn[, $onoff]) $db->autocommit([$onoff]) With one arg, return whether autocommit is enabled (for databases that do not support transactions, true will always be returned). With two args, change the autocommit status (or return error if transactions are not supported). db_commit($conn) $db->commit() Commit current transaction, or return error if transactions are not supported. db_rollback($conn) $db->rollback() Roll back current transaction, or return error if transactions are not supported. db_escape($string) $db->escape($string) Escape a string for inserting into a query. db_error_code_native([$conn|$stmt]) $db->errorCodeNative([$stmt]) Return the native code of the last error. db_error_code([$conn|$stmt]) $db->errorCode([$stmt]) Return a portable code for the last error. db_error_msg($conn/$stmt) $db->errorMsg([$stmt]) Return a portable message for the last error. db_sequence_next($conn, $seqname) $db->sequenceNext($seqname) Return the next value in the sequence $seqname. db_long_read_len($stmt[, $length]) $db->longReadLen($stmt[, $length]) With one arg, return the current max size of data to be read with one call. With two args, set the current max size or return an error if the db extension doesn't support changing it. db_binary_mode($stmt[, $status]) $db->binaryMode($stmt[, $status]) With one arg, return the current way of retrieving binary data. With two args, change the current way of retrieving binary data or return an error if the db extension doesn't support changing it. db_disconnect($conn) $db->disconnect() Disconnect, log out. db_do_one($conn, $query[, $data...]) $db->doOne($query[, $data...]) Do a query and return the first column of the first row, or false on error. If $data is specified, prepare/execute will be used, and $data will be passed during execute. db_do_column($conn, $query, $col[, $data...]) $db->doColumn($query, $col[, $data...]) Do a query and return an array indexed 0.. containing column $col from each row of the result. If $data is specified, prepare/execute will be used, and $data will be passed during execute. db_do_row($conn, $query[, $data...]) $db->doRow($query[, $data...]) Do a query and return an array indexed 1.. with the first row of data. If $data is specified, prepare/execute will be used, and $data will be passed during execute. db_do_row_into($conn, $query, $array[, $data...]) $db->doRowInto($query, $array[, $data...]) Do a query and store the first row of data in $array indexed 1... If $data is specified, prepare/execute will be used, and $data will be passed during execute. db_do_all($conn, $query[, $data...]) $db->doAll($query[, $data...]) Do a query and return an array indexed 0.., where the value of each element is an array indexed 1.. with a row of data. If $data is specified, prepare/execute will be used, and $data will be passed during execute. db_do_all_into($conn, $query, $array[, $data...]) $db->doAllInto($query, $array[, $data...]) Do a query and store an array in $array indexed 0.., where the value of each element is an array indexed 1.. with a row of data. If $data is specified, prepare/execute will be used, and $data will be passed during execute. db_do_assoc($conn, $query[, $forcearray[, $data...]]) $db->doAssoc($query[, $forcearray[, $data...]]) Do a query and return an associative array where the key is the first column and the value is the second column of the query. If $data is specified, prepare/execute will be used, and $data will be passed during execute. db_do_assoc_into($conn, $query, $array[, $forcearray[, $data...]]) $db->doAssocInto($query, $array[, $forcearray[, $data...]]) Do a query and store an associative array in $array where the key is the first column and the value is the second column of the query. If the query returned only two columns, the value will be a scalar value. If the query returned three or more columns, or $forcearray is true, the value will be an array indexed 0... If $data is specified, prepare/execute will be used, and $data will be passed during execute.