/[debian]/quickappoint/branches/upstream/current/lib/db.php
ViewVC logotype

Contents of /quickappoint/branches/upstream/current/lib/db.php

Parent Directory Parent Directory | Revision Log Revision Log


Revision 140 - (show annotations)
Sat Feb 25 17:48:32 2006 UTC (15 years, 5 months ago) by gregoa
File size: 9498 byte(s)
[svn-inject] Installing original source of quickappoint
1 <?php
2 // This file should be called whenever a database connection
3 // and/or database functions are needed.
4
5 // database connection
6 $db_conn = pg_connect(
7 'host=' . $db_options['host'] .
8 ' dbname=' . $db_options['db'] .
9 ' user=' . $db_options['user'] .
10 ' password=' . $db_options['pass']
11 );
12 if (!$db_conn) die("Could not open connection to database.");
13 unset($db_options);
14
15
16
17 // General Postgresql Database related functions
18 // Naming convention
19 // - All postgresql related functions begin with pg_
20 // - Functions that die on an error begin with pg_u_
21 // - Some Functions with a special error handling begin with pg_e_
22
23
24 // Applies the function pg_escape_string on every value of an array.
25 function pg_escape_array($array) {
26 return array_map('pg_escape_string', $array);
27 }
28
29
30 // Applies pg_escape_string on the value
31 // - if the value is NULL or '', it is converted in null
32 // - other values are placed within single quotes.
33 function pg_encode($value) {
34 if (is_null($value) || $value == '') return 'null';
35 return "'" . pg_escape_string($value) . "'";
36 }
37
38
39 // converts 'f', '0', 0, '' and FALSE to FALSE, everything else to TRUE
40 function pg_bool($value) {
41 if ($value == 'f') return FALSE; else return (bool) $value;
42 }
43
44
45 // The function creates a string like key1='value1', key2='value2'
46 // The or key1='value1' and key2='value2'
47 // out of a array like array('key1' => 'value1', 'key2' => 'value2')
48 // On the keys, pg_escape_string is applied,
49 // on the values, pg_enocde is applied.
50 // $glue should be something like ', ' or ' and '
51 function pg_field_value_string($field_value_map, $glue) {
52 $elements = array();
53 foreach($field_value_map as $key => $value)
54 $elements[] = pg_escape_string($key) . '=' . pg_encode($value);
55 return implode($glue, $elements);
56 }
57
58
59 // Connects to a database
60 function pg_u_connect($host, $dbname, $username, $password) {
61 if ($password) $password = " password=$password";
62 if ($host != 'localhost') $host="host=$host "; else $host = '';
63 $connectstring = $host . "dbname=$dbname user=$username" . $password;
64 $db = pg_connect($connectstring);
65 if (!$db) die("Could not connect to database $dbname ($connectstring)");
66 pg_query($db, "set datestyle to 'German'");
67 return $db;
68 }
69
70
71 // same as pg_query but dies if an error occurs with a meaningful errormessage
72 function pg_u_query($db, $sql) {
73 $result = pg_query($db, $sql);
74 if (!$result) die('Could not execute query: ' . $sql);
75 return $result;
76 }
77
78
79 // Returns the database - result of pg_query on success,
80 // otherwise the errormessage as string
81 function pg_e_query($db, $sql) {
82 // Execute SQL
83 $error_reporting = error_reporting(E_ERROR);
84 $result = pg_query($db, $sql);
85 error_reporting($error_reporting);
86
87 // Error handling
88 if (!$result) return "Could not execute query: '$sql', the message was '" . pg_last_error() . "'"; // pg_result_error($result) does not work (always returns an empty string
89 return $result;
90 }
91
92
93 // helper function
94 function pg_u_fetch($dbresult, $fetchfuncname = 'pg_fetch_assoc') {
95 if (!$dbresult) die('No valid result');
96 if (pg_num_rows($dbresult) == 0) return array();
97 while ($row = $fetchfuncname($dbresult)) {$result[] = $row;}
98 return $result;
99 }
100
101
102 // returns the result of the query as an array of associative row-arrays.
103 function pg_u_fetch_assoc($dbresult) {
104 return pg_u_fetch($dbresult, 'pg_fetch_assoc');
105 }
106
107
108 // returns the result of the query as an array of numbered row-arrays.
109 function pg_u_fetch_num($dbresult) {
110 return pg_u_fetch($dbresult, 'pg_fetch_row');
111 }
112
113
114 // same as pg_u_fetch_assoc, but needs an sql statement.
115 function pg_u_query_assoc($db, $sql) {
116 return pg_u_fetch_assoc(pg_u_query($db, $sql));
117 }
118
119
120 // same as pg_u_fetch_num, but needs an sql statement.
121 function pg_u_query_num($db, $sql) {
122 return pg_u_fetch_num(pg_u_query($db, $sql));
123 }
124
125
126 // returns the number of rows that a query of the specified table with the
127 // field_value_map returns. If an error occurs, the function returns the
128 // errormessage as string.
129 function pg_e_rowsaffected($db, $tablename, $field_value_map) {
130 $sql = 'select count(*) from ' . pg_escape_string($tablename);
131 $sql .= ' where ' . pg_field_value_string($field_value_map, ' and ');
132 $result = pg_e_query($db, $sql);
133 if (is_string($result)) return $result; // error
134 return (int) pg_fetch_result($result, 0, 0);
135 }
136
137
138 // This function is similar to the pg_u_rowsaffected function but it
139 // returns TRUE if the result is 1. In all other cases it returns a string
140 // with the description of the "error".
141 function pg_e_onerowaffected($db, $tablename, $field_value_map) {
142 $result = pg_e_rowsaffected($db, $tablename, $field_value_map);
143 if (is_string($result)) return $result; // error
144 if ($result == 0) return 'No row affected';
145 if ($result > 1) return 'More than one row affected';
146 return TRUE;
147 }
148
149
150 // Inserts a new row into the specified table.
151 // On success, the function returns TRUE, otherwise a string with the error message.
152 function pg_e_insert($db, $tablename, $field_value_map) {
153 // Build SQL
154 $tablename = pg_escape_string($tablename);
155 $fields = pg_escape_array(array_keys($field_value_map));
156 $values = array_map('pg_encode', array_values($field_value_map));
157 $fields = implode(', ', $fields);
158 $values = implode(", ", $values);
159 $sql = "insert into $tablename ($fields) values ($values)";
160
161 // Execute SQL
162 $result = pg_e_query($db, $sql);
163 if (is_string($result)) return $result;
164 $num = pg_affected_rows($result);
165 if ($num != 1) return "There were $num rows affected by query: '$sql'";
166 return TRUE;
167 }
168
169
170 // Updates _one_ row in the specified table.
171 // On success, the function returns TRUE, otherwise a string with the error message.
172 // - $db ... The database connection (has to have the suitable rights)
173 // - $tablename ... Name of the table to update
174 // - $field_value_map ... key/value array. The keys are the fieldnames.
175 // Not all fields have to be present
176 // - $key_field_value_map ... key/value array of names of the key - fields
177 // that identify the row unique
178 function pg_e_update($db, $tablename, $field_value_map, $key_field_value_map) {
179 // Only one row?
180 $one_result = pg_e_onerowaffected($db, $tablename, $key_field_value_map);
181 if (is_string($one_result)) return $one_result;
182
183 // Build update query
184 if (count($field_value_map) == 0) return TRUE;
185 $sql = 'update ' . pg_escape_string($tablename);
186 $sql .= ' set ' . pg_field_value_string($field_value_map, ', ');
187 $sql .= ' where ' . pg_field_value_string($key_field_value_map, ' and ');
188
189 // Execute SQL
190 $result = pg_e_query($db, $sql);
191 if (is_string($result)) return $result;
192 return TRUE;
193 }
194
195
196
197 // Deletes _one_ row in the specified table.
198 // On success, the function returns TRUE, otherwise a string with the error message.
199 // - $db ... The database connection (has to have the suitable rights)
200 // - $tablename ... Name of the table where one row should be deleted
201 // - $key_field_value_map ... array of names of keys with their values
202 // fields that identify the row to delete unique
203 function pg_e_delete($db, $tablename, $key_field_value_map) {
204 // Only one row?
205 $one_result = pg_e_onerowaffected($db, $tablename, $key_field_value_map);
206 if (is_string($one_result)) return $one_result;
207
208 // Build update query
209 $sql = 'delete from ' . pg_escape_string($tablename);
210 $sql .= ' where ' . pg_field_value_string($key_field_value_map, ' and ');
211
212 // Execute SQL
213 $result = pg_e_query($db, $sql);
214 if (is_string($result)) return $result;
215 return TRUE;
216 }
217
218
219 // Returns the type of the column as string.
220 // If there is an error (i.e. column does not exist), FALSE is returned.
221 function pg_e_get_columntype($db, $tablename, $columnname) {
222 $tablename = pg_escape_string($tablename);
223 $columnname = pg_escape_string($columnname);
224 $sql = "select udt_name from information_schema.columns where table_name = '$tablename' and column_name = '$columnname'";
225
226 // Execute SQL
227 $result = pg_e_query($db, $sql);
228 if (is_string($result)) return FALSE;
229
230 $r = pg_u_fetch_num($result);
231 if (!$r) return FALSE; // The same: if (count($array) == 0) return FALSE
232 return $r[0][0];
233 }
234
235
236 // Return values:
237 // - TRUE, if the value would be accepted if used in an insert statement
238 // - FALSE if the column type can't be determined
239 // - the column type as string otherwise.
240 function pg_check_columnvalue($db, $tablename, $columnname, $value) {
241 // 1st: determine the column type
242 $columntype = pg_e_get_columntype($db, $tablename, $columnname);
243 if ($columntype === FALSE) return FALSE;
244
245 // "quick hack:"
246 if ($value == '') return TRUE;
247
248 // a select is done to check if the value is convertable into $columntype.
249 $pg_columntype = pg_escape_string($columntype);
250 $value = pg_escape_string($value);
251 $sql = "select $pg_columntype '$value'";
252 $result = pg_e_query($db, $sql);
253 if (is_string($result)) return $columntype;
254
255 return TRUE;
256 }
257
258
259 // Return values:
260 // - TRUE, if the values would be accepted if used in an insert statement
261 // - FALSE, if $db is not ok, the table does not exist or one of the columns doesn't exist.
262 // - a readable error message otherwise.
263 function pg_checkcolumnvalues($db, $tablename, $field_value_map) {
264 $error_msg = array();
265 foreach($field_value_map as $column => $value) {
266 $result = pg_check_columnvalue($db, $tablename, $column, $value);
267 if ($result === FALSE) return FALSE;
268 if ($result === TRUE) continue;
269 $error_msg[] = "'$value' is not of type '$result'";
270 }
271 if (!$error_msg) return TRUE;
272 return "Error(s) in supplied data: " . implode(" / ", $error_msg);
273 }
274
275 ?>

  ViewVC Help
Powered by ViewVC 1.1.26