trunk/include/xx_pgsql.php

<?php

# convert calls to xx_ SQL to PostgreSQL

# this is ugly cludge. Since MySQL calls xx_connect and expects to
# see database connection and after that calls xx_select_db to select
# which database to use, we ignore that alltogether. Insted, we are using
# well-known global variables to make correct connection to database, and
# ignore xx_select_db.

function XXX_debug ($string = "XXX_debug called with empty string") {
        error_log($string, 0);
}

function xx_connect ( $db_srv, $db_usr, $db_pwd ) {
        #$info = "host=$db_srv dbname=$db_nam user=$db_usr password=$db_pwd";
        $info = "dbname=libdata user=dpavlin";
        $GLOBALS['pg_dbh'] = pg_connect($info);
        return $GLOBALS['pg_dbh'];
}

function xx_select_db ( $db_nam, $con ) {
        return 1;
}


function xx_errno() {
        # return 0 on success
        $status = pg_result_status($GLOBALS['pg_dbh']);
        if (
                $status == PGSQL_EMPTY_QUERY ||
                $status == PGSQL_COMMAND_OK ||
                $status == PGSQL_TUPLES_OK ||
                $status == PGSQL_COPY_TO ||
                $status == PGSQL_COPY_FROM ||
                $status == PGSQL_NONFATAL_ERROR
        ) {
                return 0;
        } else {
                return $status;
        }
}

function xx_fetch_array ($rs) {
        XXX_debug("xx_fetch_array row: ".$GLOBALS["xx_element_nr"]);
        $arr = @pg_fetch_array($rs, $GLOBALS["xx_element_nr"]);
        $GLOBALS["xx_element_nr"]++;
        return $arr;
}


function xx_insert_id($con = 0) {
        if ($con == 0) {
                $con = $GLOBALS['pg_dbh'];
        }
        # this depends on premise that autoincrement field in tables
        # is always first one. If would be better to search for field
        # with sequence and return that, but it's too much work, and
        # this seem to work most of the time
        $oid = $GLOBALS["pg_last_oid"];
        preg_match('/\s*insert\s+into\s+(\S+)\s+/is', $GLOBALS["pg_last_sql"], $table);
        if ($table) {
                $result = pg_query($con, "SELECT * FROM $table[1] where oid=$oid");
                $arr = pg_fetch_array($result, 0, PGSQL_NUM);
                XXX_debug("xx_insert_id (table: $table, oid: $oid): ".$arr[0]);
                return $arr[0];
        } else {
                print "WARNING: xx_insert_id emulation failed! Last SQL query was '".$GLOBALS["pg_last_sql"]."' and I can't extract table name from that query. If I could, I would try to find tuple with oid $oid in it!";
                exit;
        }
}

function xx_num_rows($rs) {
        $num = pg_num_rows($rs);
        if (!isset($num)) {
                $num = pg_affected_rows($rs);
        }
        if (!isset($num)) {
                $num = 0;
        }
        XXX_debug("xx_num_rows: $num");
        return $num;
}

function xx_query($sql, $con = 0) {

        if ($con == 0) {
                $con = $GLOBALS['pg_dbh'];
                XXX_debug("xx_query (fixed conn): $sql");
        } else {
                XXX_debug("xx_query (conn: $con): $sql");
        }

        # PostgreSQL MVC obsoletes usage of lock/unlock in mysql terms
        if (preg_match('/^\s*lock/is', $sql) || preg_match('/^\s*unlock/is', $sql)) {
                return 1;
        }

        # fix Mysql contact function into something SQL92 compliant
        $sql = preg_replace('/concat\(([^\,]+)\s*,\s*([^\)]+)\)/is', '\\1 || \\2', $sql);

        # convert Mysql password function to md5
        $sql = preg_replace('/password\(([^\)]+)\)/is', 'md5(\\1)', $sql);

        # fix update statements to remove table name (which would be understood as
        # schama in PostgreSQL
        preg_match('/^\s*update\s+(\S+)\s+set/is', $sql, $table);
        if ($table) {
                $sql = preg_replace('/\s+'.$table[1].'\.(\w+)/is', ' \\1', $sql);
        }

        $sql = preg_replace('/password\(([^\)]+)\)/is', 'md5(\\1)', $sql);

        $GLOBALS["xx_element_nr"] = 0;
        $GLOBALS["pg_last_sql"] = $sql;

        XXX_debug("xx_query [transformed]: $sql");

        $ret = pg_query($con, $sql);

        $GLOBALS["pg_last_oid"] = pg_last_oid($ret);
        if ($ret) {
                return $ret;
        } else {
                print "WARNING: error executing SQL query '$sql'.";
        }
}

function xx_data_seek($rs, $element_nr) {
        $GLOBALS["xx_element_nr"] = $element_nr;
}

function xx_error() {
        return pg_result_error($GLOBALS['pg_dbh']);
}

?>
1	dpavlin	39	<?php
2
3			# convert calls to xx_ SQL to PostgreSQL
4
5			# this is ugly cludge. Since MySQL calls xx_connect and expects to
6			# see database connection and after that calls xx_select_db to select
7			# which database to use, we ignore that alltogether. Insted, we are using
8			# well-known global variables to make correct connection to database, and
9			# ignore xx_select_db.
10
11			function XXX_debug ($string = "XXX_debug called with empty string") {
12			error_log($string, 0);
13			}
14
15			function xx_connect ( $db_srv, $db_usr, $db_pwd ) {
16			#$info = "host=$db_srv dbname=$db_nam user=$db_usr password=$db_pwd";
17			$info = "dbname=libdata user=dpavlin";
18			$GLOBALS['pg_dbh'] = pg_connect($info);
19			return $GLOBALS['pg_dbh'];
20			}
21
22			function xx_select_db ( $db_nam, $con ) {
23			return 1;
24			}
25
26
27			function xx_errno() {
28			# return 0 on success
29			$status = pg_result_status($GLOBALS['pg_dbh']);
30			if (
31			$status == PGSQL_EMPTY_QUERY \|\|
32			$status == PGSQL_COMMAND_OK \|\|
33			$status == PGSQL_TUPLES_OK \|\|
34			$status == PGSQL_COPY_TO \|\|
35			$status == PGSQL_COPY_FROM \|\|
36			$status == PGSQL_NONFATAL_ERROR
37			) {
38			return 0;
39			} else {
40			return $status;
41			}
42			}
43
44			function xx_fetch_array ($rs) {
45			XXX_debug("xx_fetch_array row: ".$GLOBALS["xx_element_nr"]);
46			$arr = @pg_fetch_array($rs, $GLOBALS["xx_element_nr"]);
47			$GLOBALS["xx_element_nr"]++;
48			return $arr;
49			}
50
51
52			function xx_insert_id($con = 0) {
53			if ($con == 0) {
54			$con = $GLOBALS['pg_dbh'];
55			}
56			# this depends on premise that autoincrement field in tables
57			# is always first one. If would be better to search for field
58			# with sequence and return that, but it's too much work, and
59			# this seem to work most of the time
60			$oid = $GLOBALS["pg_last_oid"];
61			preg_match('/\s*insert\s+into\s+(\S+)\s+/is', $GLOBALS["pg_last_sql"], $table);
62			if ($table) {
63			$result = pg_query($con, "SELECT * FROM $table[1] where oid=$oid");
64			$arr = pg_fetch_array($result, 0, PGSQL_NUM);
65			XXX_debug("xx_insert_id (table: $table, oid: $oid): ".$arr[0]);
66			return $arr[0];
67			} else {
68			print "WARNING: xx_insert_id emulation failed! Last SQL query was '".$GLOBALS["pg_last_sql"]."' and I can't extract table name from that query. If I could, I would try to find tuple with oid $oid in it!";
69			exit;
70			}
71			}
72
73			function xx_num_rows($rs) {
74			$num = pg_num_rows($rs);
75			if (!isset($num)) {
76			$num = pg_affected_rows($rs);
77			}
78			if (!isset($num)) {
79			$num = 0;
80			}
81			XXX_debug("xx_num_rows: $num");
82			return $num;
83			}
84
85			function xx_query($sql, $con = 0) {
86
87			if ($con == 0) {
88			$con = $GLOBALS['pg_dbh'];
89			XXX_debug("xx_query (fixed conn): $sql");
90			} else {
91			XXX_debug("xx_query (conn: $con): $sql");
92			}
93
94			# PostgreSQL MVC obsoletes usage of lock/unlock in mysql terms
95			if (preg_match('/^\slock/is', $sql) \|\| preg_match('/^\sunlock/is', $sql)) {
96			return 1;
97			}
98
99			# fix Mysql contact function into something SQL92 compliant
100			$sql = preg_replace('/concat\(([^\,]+)\s,\s([^\)]+)\)/is', '\\1 \|\| \\2', $sql);
101
102			# convert Mysql password function to md5
103			$sql = preg_replace('/password\(([^\)]+)\)/is', 'md5(\\1)', $sql);
104
105			# fix update statements to remove table name (which would be understood as
106			# schama in PostgreSQL
107			preg_match('/^\s*update\s+(\S+)\s+set/is', $sql, $table);
108			if ($table) {
109			$sql = preg_replace('/\s+'.$table[1].'\.(\w+)/is', ' \\1', $sql);
110			}
111
112			$sql = preg_replace('/password\(([^\)]+)\)/is', 'md5(\\1)', $sql);
113
114			$GLOBALS["xx_element_nr"] = 0;
115			$GLOBALS["pg_last_sql"] = $sql;
116
117			XXX_debug("xx_query [transformed]: $sql");
118
119			$ret = pg_query($con, $sql);
120
121			$GLOBALS["pg_last_oid"] = pg_last_oid($ret);
122			if ($ret) {
123			return $ret;
124			} else {
125			print "WARNING: error executing SQL query '$sql'.";
126			}
127			}
128
129			function xx_data_seek($rs, $element_nr) {
130			$GLOBALS["xx_element_nr"] = $element_nr;
131			}
132
133			function xx_error() {
134			return pg_result_error($GLOBALS['pg_dbh']);
135			}
136
137			?>