3 # This program is in the public domain within the United States. Additionally,
4 # we waive copyright and related rights in the work worldwide through the CC0
5 # 1.0 Universal public domain dedication, which can be found at
6 # http://creativecommons.org/publicdomain/zero/1.0/
9 require_once(__DIR__.'/'.'encode.php');
10 require_once(__DIR__.'/'.'format.php');
12 # db_connect() -- connect to a mysql database (automatically, later, when/if needed)
13 # db_connect_now() -- connect to a mysql database immediately
17 # database: the name of the database you want to connect to. Defaults to the
18 # second-to-last part of the domain name. eg for foo.example.com it would be
21 # user: username for connecting to the database. Defaults to
22 # $GLOBALS['db_username'] or (if that's not set) "www".
24 # password: password for connecting to the database. Defaults to
25 # $GLOBALS['db_password'] or (if that's not set "".
29 # the database connection handle. You'll only need this if you want to have
30 # multiple databases open at once.
31 function db_connect() {
32 if (isset($GLOBALS['wfpl_db_handle'])) {
33 mysqli_close($GLOBALS['wfpl_db_handle']);
34 unset($GLOBALS['wfpl_db_handle']);
36 $GLOBALS['wfpl_db_connect_args'] = func_get_args();
38 function db_connect_now($database = 'auto', $user = 'auto', $pass = 'auto', $host = 'localhost', $encoding = 'utf8') {
39 if($database == 'auto') {
40 if(isset($GLOBALS['db_name'])) {
41 $database = $GLOBALS['db_name'];
43 $host = $_SERVER['SERVER_NAME'];
44 $host = explode('.', $host);
46 $database = array_pop($host);
52 if(isset($GLOBALS['db_username'])) {
53 $user = $GLOBALS['db_username'];
60 if(isset($GLOBALS['db_password'])) {
61 $pass = $GLOBALS['db_password'];
67 $GLOBALS['wfpl_db_handle'] = @mysqli_connect($host, $user, $pass);
68 if(!$GLOBALS['wfpl_db_handle']) {
69 die('Server error: Database connection failed');
70 # to show username and host: mysqli_connect_error()
73 mysqli_set_charset($GLOBALS['wfpl_db_handle'], $encoding);
75 if(!mysqli_select_db($GLOBALS['wfpl_db_handle'], $database)) {
76 die("Couldn not access database \"$database\": " . mysqli_error($GLOBALS['wfpl_db_handle']));
79 return $GLOBALS['wfpl_db_handle'];
81 # for internal use (helps implement the auto-connect feature of db_connect())
82 function _db_connection_needed() {
83 if (isset($GLOBALS['wfpl_db_handle'])) {
86 if (isset($GLOBALS['wfpl_db_connect_args'])) {
87 return call_user_func_array('db_connect_now', $GLOBALS['wfpl_db_connect_args']);
89 die('Error: you must call db_connect() or db_auto_connect() first!');
92 function db_enc_sql($str) {
93 _db_connection_needed();
94 return mysqli_real_escape_string(isset($GLOBALS['wfpl_db_handle']) ? $GLOBALS['wfpl_db_handle'] : null, $str);
97 # Unless you're doing something unusual like an ALTER TABLE don't call this directly
98 function db_send_query($sql) {
99 _db_connection_needed();
100 #echo("Sending query: " . enc_html($sql) . "<br>\n");
101 $result = mysqli_query($GLOBALS['wfpl_db_handle'], $sql);
103 die(enc_html('DATABASE ERROR: ' . mysqli_error($GLOBALS['wfpl_db_handle']) . ' in the following query: ' . $sql));
109 # All select queries use this to generate the where clause, so they can work
110 # like printf. Currently three % codes are supported:
112 # %% put a % in the output
113 # %i put an integer in the output (strips non-numeric digits, and puts in 0 if blank)
114 # %I a list of integers (as %i) separated by commas
115 # %f put a floating point value in the output (strips non-numeric digits, puts in 0.0 if not valid)
116 # %" output double quotes, surrounding the variable which is encoded to be in there.
117 # %s output encoded to be in double quotes, but don't output the quotes
118 # %$ output argument as-is, no encoding. Make sure you quote everything from the user!
120 # complex example: db_get_rows('mytable', 'id', 'where name=%" or company like "%%%s%%"', $name, $company_partial);
122 function db_printf($str) {
123 $args = func_get_args();
124 $args = array_slice($args, 1);
125 return _db_printf($str, $args);
128 # This function does the work, but takes the parameters in an array
129 function _db_printf($str, $args) {
132 $pos = strpos($str, '%');
133 if($pos === false) { # not found
137 # move everything up to (but not including) % to the output
138 $out .= substr($str, 0, $pos);
140 # grab the character after the %
141 $chr = substr($str, $pos + 1, 1);
143 # remove the stuff we've read from input
144 $str = substr($str, $pos + 2);
147 $out .= '"' . db_enc_sql(array_shift($args)) . '"';
148 } elseif($chr == 's') {
149 $out .= db_enc_sql(array_shift($args));
150 } elseif($chr == 'i') {
151 $out .= format_int_0(array_shift($args));
152 } elseif($chr == 'I') {
153 $arg = array_shift($args);
155 foreach ($arg as $int) {
161 $out .= format_int_0($int);
163 } elseif($chr == 'f') {
164 $arg = array_shift($args);
165 if(is_numeric($arg)) {
166 $arg = sprintf("%f", $arg);
168 $arg = format_decimal($arg);
169 if(strlen($arg) < 1) {
173 } elseif($chr == '$') {
174 $out .= array_shift($args);
185 function db_send_get($table, $columns, $where, $args) {
186 $sql = "SELECT $columns FROM $table";
188 $sql .= ' ' . _db_printf($where, $args);
191 return db_send_query($sql);
195 # if no results: returs []
196 function db_get_rows($table, $columns, $where = '') {
197 $args = func_get_args();
198 $args = array_slice($args, 3);
199 $result = db_send_get($table, $columns, $where, $args);
202 while($row = mysqli_fetch_row($result)) {
206 mysqli_free_result($result);
211 # like db_get_rows, but return array of hashes.
212 # if no results: returs []
213 function db_get_assocs($table, $columns, $where = '') {
214 $args = func_get_args();
215 $args = array_slice($args, 3);
216 $result = db_send_get($table, $columns, $where, $args);
219 while($row = mysqli_fetch_assoc($result)) {
223 mysqli_free_result($result);
228 # if no results: returs []
229 function db_get_column($table, $columns, $where = '') {
230 $args = func_get_args();
231 $args = array_slice($args, 3);
232 $result = db_send_get($table, $columns, $where, $args);
235 while($row = mysqli_fetch_row($result)) {
239 mysqli_free_result($result);
244 # returns first matching row
245 # if no results: returns false
246 function db_get_row($table, $columns, $where = '') {
247 $args = func_get_args();
248 $args = array_slice($args, 3);
249 $result = db_send_get($table, $columns, $where, $args);
251 $row = mysqli_fetch_row($result);
253 mysqli_free_result($result);
258 # like db_get_row, but return a hash.
259 # if no results: returns false
260 function db_get_assoc($table, $columns, $where = '') {
261 $args = func_get_args();
262 $args = array_slice($args, 3);
263 $result = db_send_get($table, $columns, $where, $args);
265 $row = mysqli_fetch_assoc($result);
267 mysqli_free_result($result);
272 # if no results: returns false
273 function db_get_value($table, $column, $where = '') {
274 $args = func_get_args();
275 $args = array_slice($args, 3);
276 $result = db_send_get($table, $column, $where, $args);
278 $value = mysqli_fetch_row($result);
279 if($value !== false) {
283 mysqli_free_result($result);
289 function db_count($table, $where = '') {
290 $args = func_get_args();
291 array_splice($args, 1, 0, array('count(*)'));
292 return (int) call_user_func_array('db_get_value', $args);
295 # call either of these ways:
297 # db_insert('people', 'name,company', 'jason', 'widgets ltd');
299 # db_insert('people', 'name,company', array('jason', 'widgets ltd'));
300 function db_insert($table, $columns, $values) {
301 if(!is_array($values)) {
302 $values = func_get_args();
303 $values = array_slice($values, 2);
306 db_insert_ish('INSERT', $table, $columns, $values);
309 # like db_insert() above, but instead of passing columns and data separately,
310 # you can pass one array with the column names as keys and the data as values
311 function db_insert_assoc($table, $data) {
312 $args = func_get_args();
313 $args = array_slice($args, 2);
316 foreach($data as $key => $value) {
320 array_unshift($args, $table, join(',', $columns), $values);
321 call_user_func_array('db_insert', $args);
324 # same as above, except uses the "replace" command instead of "insert"
325 function db_replace($table, $columns, $values) {
326 if(!is_array($values)) {
327 $values = func_get_args();
328 $values = array_slice($values, 2);
331 db_insert_ish('REPLACE', $table, $columns, $values);
334 # return the value mysql made up for the auto_increment field (for the last insert)
335 function db_auto_id() {
336 _db_connection_needed();
337 return mysqli_insert_id($GLOBALS['wfpl_db_handle']);
341 # used to implement db_insert() and db_replace()
342 function db_insert_ish($command, $table, $columns, $values) {
345 foreach($values as $value) {
346 if($sql) $sql .= ',';
347 $sql .= '"' . db_enc_sql($value) . '"';
350 $sql = "$command INTO $table ($columns) values($sql)";
355 # to be consistent with the syntax of the other db functions, $values can be an
356 # array, a single value, or multiple parameters.
358 # as usual the where clause stuff is optional, but it will of course update the
359 # whole table if you leave it off.
363 # # name everybody Bruce
364 # db_update('users', 'name', 'Bruce');
366 # # name user #6 Bruce
367 # db_update('users', 'name', 'Bruce', 'where id=%i', 6);
369 # # update the whole bit for user #6
370 # db_update('users', 'name,email,description', 'Bruce', 'bruce@example.com', 'is a cool guy', 'where id=%i', 6);
372 # # update the whole bit for user #6 (passing data as an array)
373 # $data = array('Bruce', 'bruce@example.com', 'is a cool guy');
374 # db_update('users', 'name,email,description', $data, 'where id=%i', 6);
376 # The prototype is really something like this:
377 # db_update(table, columns, values..., where(optional), where_args...(optional))
378 function db_update($table, $columns, $values) {
379 $args = func_get_args();
380 $args = array_slice($args, 2);
381 $columns = explode(',', $columns);
382 $num_fields = count($columns);
384 if(is_array($values)) {
385 $values = array_values($values);
386 $args = array_slice($args, 1);
388 $values = array_slice($args, 0, $num_fields);
389 $args = array_slice($args, $num_fields);
393 for($i = 0; $i < $num_fields; ++$i) {
397 $sql .= $columns[$i] . ' = "' . db_enc_sql($values[$i]) . '"';
401 $sql = "UPDATE $table SET $sql";
403 # if there's any more arguments
406 $args = array_slice($args, 1);
409 # any left for printf arguments?
411 $sql .= _db_printf($where, $args);
421 # like db_update() above, but instead of passing columns and data separately,
422 # you can pass one array with the column names as keys and the data as values
423 function db_update_assoc($table, $data) {
424 $args = func_get_args();
425 $args = array_slice($args, 2);
428 foreach($data as $key => $value) {
432 array_unshift($args, $values);
433 array_unshift($args, join(',', $columns));
434 array_unshift($args, $table);
435 call_user_func_array('db_update', $args);
438 # pass args for printf-style where clause as usual
439 function db_delete($table, $where = '') {
440 $sql = "DELETE FROM $table";
443 $args = func_get_args();
444 $args = array_slice($args, 2);
446 $sql .= _db_printf($where, $args);
456 define('DB_ORD_MAX', 2000000000);
458 function db_reposition_respace($table, $field, $where = '') {
460 $andand = " && ($where) ";
462 $ids = db_get_column($table, 'id', "where $field != 0 $andand order by $field");
465 # should never happen
468 $inc = floor(DB_ORD_MAX / ($c + 1));
470 $count = count($ids);
471 for ($i = 0; $i < $count; $i += 1000) {
473 $j_max = min($count, $i + 1000);
474 for ($j = $i; $j < $j_max; ++$j) {
476 $values[] = "($id,$ord)";
480 "insert into $table (id,$field) values "
481 . implode(',', $values)
482 . " on duplicate key update $field=VALUES($field)"
488 # this function facilitates letting the user manually sort records (with (int) $field != 0)
490 # When editing a particular row, give the user a pulldown, with 0 -> first, 1 -> second, etc, and pass this integer to db_reposition (3rd parameter). The value "ignored" can be passed, and the row will be given a sort value of 0 and ignored for all sorting.
492 # $pretty is used in error messages to refer to the row, it defaults to whatever you pass for $table.
494 # return value is the "ord" value you should set/insert into your database
496 function db_reposition($table, $row_id, $new_pos, $field = 'ord', $pretty = 'same as $table', $where = '', $renumbered_already = false) {
497 if($pretty == 'same as $table') {
501 $andand = " && ($where) ";
504 if($new_pos === 'ignored') {
509 # strategy: calculate $prev_ord and $next_ord. If there's no space between, renumber and recurse
510 if($new_pos == '0') {
511 $row = db_get_row($table, "id,$field", "where $field != 0 $andand order by $field limit 1");
513 list($first_row_id, $first_row_ord) = $row;
514 if($first_row_id == $row_id) {
516 return $first_row_ord;
518 $next_ord = $first_row_ord;
520 # this is the only row, put it in the middle
521 return '' + floor(DB_ORD_MAX / 2);
526 $new_pos = format_int_0($new_pos);
527 $rows = db_get_rows($table, "id,$field", "where $field != 0 $andand order by $field limit %i,2", $new_pos - 1);
529 message("Sorry, couldn't find the $pretty you asked to put this $pretty after. Putting it first instead.");
530 return db_reposition($table, $row_id, '0', $field, $pretty, $where);
532 list($prev_id, $prev_ord) = $rows[0];
533 if($prev_id == $row_id) {
534 # after self? this shouldn't happen
537 if(count($rows) == 1) {
539 $next_ord = DB_ORD_MAX;
541 list($next_id, $next_ord) = $rows[1];
542 if($next_id == $row_id) {
543 # after prev (already there)
549 if($prev_ord + 1 == $next_ord || $prev_ord == $next_ord) { # the latter should never happen
550 if($renumbered_already) {
551 message("Programmer error in $pretty ordering code. Please tell your website administrator.");
552 return '' . rand(2, DB_ORD_MAX - 2); # reasonably unlikely to be the same as some other ord
554 db_reposition_respace($table, $field, $where);
555 return db_reposition($table, $row_id, $new_pos, $field, $pretty, $where, $renumbered_already = true);
557 return $prev_ord + round(($next_ord - $prev_ord) / 2);
561 # Call this to upgrade your database (using upgrade functions you define.)
563 # You can call this from config.php right after db_connect() to make sure the
564 # database is up to date.
566 # When you want to update your schema, define a new function named
567 # db_upgrade_to_X() where X is the next integer (start at 1).
569 # If there are any page views while your upgrade function is running, they will
570 # stall until the upgrade function completes. This is often better than running
571 # while the databse is in a transitional state, and is way way better than
572 # running the upgrade function multiple times concurrently.
574 # Efficiency: this function is designed to be lean enough that you'd run it on
575 # every page load, so you never forget to upgrade your schema after uploading
576 # code changes. If your schema is up to date, this will only execute one
577 # database query, and that query loads the persistent data store (used by
578 # persistent_get()), so if you use that, you'll need that query to happen
579 # anyway (giving this function a zero-query overhead).
581 function db_upgrade() {
582 if (isset($GLOBALS['wfpl_persistent'])) {
583 $version = persistent_get('wfpl_db_version');
585 # custom version of persistent_init() that creates the table if needed
587 $GLOBALS['wfpl_persistent'] = array();
588 _db_connection_needed();
589 $result = mysqli_query($GLOBALS['wfpl_db_handle'], 'select k,v from wfpl_persistent');
591 while($row = mysqli_fetch_assoc($result)) {
592 $GLOBALS['wfpl_persistent'][$row['k']] = json_decode($row['v'], true);
594 if (isset($GLOBALS['wfpl_persistent']['wfpl_db_version'])) {
595 $version = $GLOBALS['wfpl_persistent']['wfpl_db_version'];
600 db_send_query('create table if not exists wfpl_persistent (k varchar(30) binary not null default "", v mediumblob, primary key (k)) CHARSET=utf8;');
605 if ($version === -1) {
606 db_send_query('create table if not exists wfpl_mutexes (id int unique auto_increment, name varchar(255) binary, expires int(11)) CHARSET=utf8;');
608 # don't save version now in case another thread is doing this too
610 $next = $version + 1;
611 if (function_exists("db_upgrade_to_$next")) {
612 require_once(__DIR__.'/'.'persistent.php');
613 require_once(__DIR__.'/'.'mutex.php');
614 mutex_lock('wfpl_db_upgrade', 20);
615 # check version again, in case another thread upgraded the database
616 # while we waited for a lock just now
617 persistent_invalidate_cache();
618 $version = persistent_get('wfpl_db_version');
619 if ($version === null) {
623 for ($next = $version + 1; function_exists("db_upgrade_to_$next"); ++$next) {
624 call_user_func("db_upgrade_to_$next");
625 persistent_set('wfpl_db_version', $next);
627 mutex_unlock('wfpl_db_upgrade');