[aur-dev] [PATCH] Fix DB.class.php to match config
In commit baf8a220, conf/config.proto was changed so that dsn_prefix was changed to backend and this fixes this in web/lib/DB.class.php. Signed-off-by: Mark Weiman <mark.weiman@markzz.com> --- web/lib/DB.class.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/web/lib/DB.class.php b/web/lib/DB.class.php index b538e0d..f6fd7ad 100644 --- a/web/lib/DB.class.php +++ b/web/lib/DB.class.php @@ -17,14 +17,14 @@ class DB { public static function connect() { if (self::$dbh === null) { try { - $dsn_prefix = config_get('database', 'dsn_prefix'); + $backend = config_get('database', 'backend'); $host = config_get('database', 'host'); $socket = config_get('database', 'socket'); $name = config_get('database', 'name'); $user = config_get('database', 'user'); $password = config_get('database', 'password'); - $dsn = $dsn_prefix . + $dsn = $backend . ':host=' . $host . ';unix_socket=' . $socket . ';dbname=' . $name; -- 2.10.2
On Wed, 09 Nov 2016 at 04:23:22, Mark Weiman wrote:
In commit baf8a220, conf/config.proto was changed so that dsn_prefix was changed to backend and this fixes this in web/lib/DB.class.php.
Signed-off-by: Mark Weiman <mark.weiman@markzz.com> --- web/lib/DB.class.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-)
Good catch. The dsn_prefix parameter was indeed replaced by the backend parameter in commit baf8a22 (git-interface: Support SQLite as database backend, 2016-08-03). However, it is more than a simple rename. The new parameter allows for using SQLite instead of MySQL and I doubt that this actually works after your patch; especially since host, socket, user and password can be unset if SQLite is used. So instead of pretending that the variable was renamed, we should either add SQLite support to DB.class.php or simply check whether backend is something other than "mysql" and bail out. Since the main reason for adding SQLite was making the test suite simpler and faster, I am fine with the second option. Opinions?
diff --git a/web/lib/DB.class.php b/web/lib/DB.class.php index b538e0d..f6fd7ad 100644 --- a/web/lib/DB.class.php +++ b/web/lib/DB.class.php @@ -17,14 +17,14 @@ class DB { public static function connect() { if (self::$dbh === null) { try { - $dsn_prefix = config_get('database', 'dsn_prefix'); + $backend = config_get('database', 'backend'); $host = config_get('database', 'host'); $socket = config_get('database', 'socket'); $name = config_get('database', 'name'); $user = config_get('database', 'user'); $password = config_get('database', 'password');
- $dsn = $dsn_prefix . + $dsn = $backend . ':host=' . $host . ';unix_socket=' . $socket . ';dbname=' . $name; -- 2.10.2
On Wed, 2016-11-09 at 19:56 +0100, Lukas Fleischer wrote:
Good catch. The dsn_prefix parameter was indeed replaced by the backend parameter in commit baf8a22 (git-interface: Support SQLite as database backend, 2016-08-03). However, it is more than a simple rename. The new parameter allows for using SQLite instead of MySQL and I doubt that this actually works after your patch; especially since host, socket, user and password can be unset if SQLite is used.
So instead of pretending that the variable was renamed, we should either add SQLite support to DB.class.php or simply check whether backend is something other than "mysql" and bail out. Since the main reason for adding SQLite was making the test suite simpler and faster, I am fine with the second option. Opinions?
Adding support to DB.class.php to work with sqlite shouldn't be too difficult to implement. The only thing that would need to be decided is where in the config should the location of the database be. Perhaps if backend is set to sqlite, have it look for a location setting and ignore the rest? Having this support sqlite would be very convenient as it means when working on the aurweb web interface, I wouldn't need to start a mysql service and could take the sqlite file around with me to make setting up a new instance easier.
On Wed, 09 Nov 2016 at 20:32:18, Mark Weiman wrote:
Adding support to DB.class.php to work with sqlite shouldn't be too difficult to implement. The only thing that would need to be decided is where in the config should the location of the database be. Perhaps if backend is set to sqlite, have it look for a location setting and ignore the rest?
It should be consistent with the way it is implemented in aurweb/db.py, i.e. only the name parameter should matter if backend is "sqlite".
Having this support sqlite would be very convenient as it means when working on the aurweb web interface, I wouldn't need to start a mysql service and could take the sqlite file around with me to make setting up a new instance easier.
Sounds good to me. Thanks for working on this!
UNIX_TIMESTAMP is not part of the SQL standard. Instead, all usage in the web interface is changed to use PHP's time() function. Signed-off-by: Mark Weiman <mark.weiman@markzz.com> --- web/lib/acctfuncs.inc.php | 12 ++++++------ web/lib/aur.inc.php | 4 ++-- web/lib/pkgbasefuncs.inc.php | 14 +++++++------- web/lib/pkgreqfuncs.inc.php | 2 +- 4 files changed, 16 insertions(+), 16 deletions(-) diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php index 172b962..6dcf91d 100644 --- a/web/lib/acctfuncs.inc.php +++ b/web/lib/acctfuncs.inc.php @@ -543,7 +543,7 @@ function try_login() { $new_sid = new_sid(); $q = "INSERT INTO Sessions (UsersID, SessionID, LastUpdateTS)" - ." VALUES (" . $userID . ", '" . $new_sid . "', UNIX_TIMESTAMP())"; + ." VALUES (" . $userID . ", '" . $new_sid . "', " . strval(time()) . ")"; $result = $dbh->exec($q); /* Query will fail if $new_sid is not unique. */ @@ -560,7 +560,7 @@ function try_login() { return array('SID' => $new_sid, 'error' => $login_error); } - $q = "UPDATE Users SET LastLogin = UNIX_TIMESTAMP(), "; + $q = "UPDATE Users SET LastLogin = " . strval(time()) . ", "; $q.= "LastLoginIPAddress = " . $dbh->quote($_SERVER['REMOTE_ADDR']) . " "; $q.= "WHERE ID = $userID"; $dbh->exec($q); @@ -638,7 +638,7 @@ function valid_username($user) { function open_user_proposals($user) { $dbh = DB::connect(); $q = "SELECT * FROM TU_VoteInfo WHERE User = " . $dbh->quote($user) . " "; - $q.= "AND End > UNIX_TIMESTAMP()"; + $q.= "AND End > " . strval(time()); $result = $dbh->query($q); return ($result->fetchColumn() ? true : false); @@ -665,7 +665,7 @@ function add_tu_proposal($agenda, $user, $votelength, $quorum, $submitteruid) { $q = "INSERT INTO TU_VoteInfo (Agenda, User, Submitted, End, Quorum, "; $q.= "SubmitterID, ActiveTUs) VALUES "; $q.= "(" . $dbh->quote($agenda) . ", " . $dbh->quote($user) . ", "; - $q.= "UNIX_TIMESTAMP(), UNIX_TIMESTAMP() + " . $dbh->quote($votelength); + $q.= strval(time()) . ", " . strval(time()) . " + " . $dbh->quote($votelength); $q.= ", " . $dbh->quote($quorum) . ", " . $submitteruid . ", "; $q.= $active_tus . ")"; $result = $dbh->exec($q); @@ -978,7 +978,7 @@ function clear_expired_sessions() { $dbh = DB::connect(); $timeout = config_get_int('options', 'login_timeout'); - $q = "DELETE FROM Sessions WHERE LastUpdateTS < (UNIX_TIMESTAMP() - " . $timeout . ")"; + $q = "DELETE FROM Sessions WHERE LastUpdateTS < (" . strval(time()) . " - " . $timeout . ")"; $dbh->query($q); return; @@ -1086,7 +1086,7 @@ function last_votes_list() { $q = "SELECT UserID, MAX(VoteID) AS LastVote FROM TU_Votes, "; $q .= "TU_VoteInfo, Users WHERE TU_VoteInfo.ID = TU_Votes.VoteID AND "; - $q .= "TU_VoteInfo.End < UNIX_TIMESTAMP() AND "; + $q .= "TU_VoteInfo.End < " . strval(time()) . " AND "; $q .= "Users.ID = TU_Votes.UserID AND (Users.AccountTypeID = 2 OR Users.AccountTypeID = 4) "; $q .= "GROUP BY UserID ORDER BY LastVote DESC, UserName ASC"; $result = $dbh->query($q); diff --git a/web/lib/aur.inc.php b/web/lib/aur.inc.php index 9015ae8..67dd1c1 100644 --- a/web/lib/aur.inc.php +++ b/web/lib/aur.inc.php @@ -38,7 +38,7 @@ function check_sid() { # the visitor is logged in, try and update the session # $dbh = DB::connect(); - $q = "SELECT LastUpdateTS, UNIX_TIMESTAMP() FROM Sessions "; + $q = "SELECT LastUpdateTS, " . strval(time()) . " FROM Sessions "; $q.= "WHERE SessionID = " . $dbh->quote($_COOKIE["AURSID"]); $result = $dbh->query($q); $row = $result->fetch(PDO::FETCH_NUM); @@ -77,7 +77,7 @@ function check_sid() { # This keeps 'remembered' sessions from being # overwritten. if ($last_update < time() + $timeout) { - $q = "UPDATE Sessions SET LastUpdateTS = UNIX_TIMESTAMP() "; + $q = "UPDATE Sessions SET LastUpdateTS = " . strval(time()) . " "; $q.= "WHERE SessionID = " . $dbh->quote($_COOKIE["AURSID"]); $dbh->exec($q); } diff --git a/web/lib/pkgbasefuncs.inc.php b/web/lib/pkgbasefuncs.inc.php index b082784..4f10204 100644 --- a/web/lib/pkgbasefuncs.inc.php +++ b/web/lib/pkgbasefuncs.inc.php @@ -98,7 +98,7 @@ function pkgbase_add_comment($base_id, $uid, $comment) { $q = "INSERT INTO PackageComments "; $q.= "(PackageBaseID, UsersID, Comments, CommentTS) VALUES ("; $q.= intval($base_id) . ", " . $uid . ", "; - $q.= $dbh->quote($comment) . ", UNIX_TIMESTAMP())"; + $q.= $dbh->quote($comment) . ", " . strval(time()) . ")"; $dbh->exec($q); $comment_id = $dbh->lastInsertId(); @@ -144,7 +144,7 @@ function pkgbase_pin_comment($unpin=false) { $dbh = DB::connect(); $q = "UPDATE PackageComments "; if (!$unpin) { - $q.= "SET PinnedTS = UNIX_TIMESTAMP() "; + $q.= "SET PinnedTS = " . strval(time()) . " "; } else { $q.= "SET PinnedTS = 0 "; } @@ -395,7 +395,7 @@ function pkgbase_flag($base_ids, $comment) { $dbh = DB::connect(); $q = "UPDATE PackageBases SET "; - $q.= "OutOfDateTS = UNIX_TIMESTAMP(), FlaggerUID = " . $uid . ", "; + $q.= "OutOfDateTS = " . strval(time()) . ", FlaggerUID = " . $uid . ", "; $q.= "FlaggerComment = " . $dbh->quote($comment) . " "; $q.= "WHERE ID IN (" . implode(",", $base_ids) . ") "; $q.= "AND OutOfDateTS IS NULL"; @@ -749,12 +749,12 @@ function pkgbase_vote ($base_ids, $action=true) { $first = 0; $vote_ids = $pid; if ($action) { - $vote_clauses = "($uid, $pid, UNIX_TIMESTAMP())"; + $vote_clauses = "($uid, $pid, " . strval(time()) . ")"; } } else { $vote_ids .= ", $pid"; if ($action) { - $vote_clauses .= ", ($uid, $pid, UNIX_TIMESTAMP())"; + $vote_clauses .= ", ($uid, $pid, " . strval(time()) . ")"; } } } @@ -972,7 +972,7 @@ function pkgbase_delete_comment($undelete=false) { $q = "UPDATE PackageComments "; $q.= "SET DelUsersID = ".$uid.", "; - $q.= "DelTS = UNIX_TIMESTAMP() "; + $q.= "DelTS = " . strval(time()) . " "; $q.= "WHERE ID = ".intval($comment_id); $dbh->exec($q); return array(true, __("Comment has been deleted.")); @@ -1005,7 +1005,7 @@ function pkgbase_edit_comment($comment) { $q = "UPDATE PackageComments "; $q.= "SET EditedUsersID = ".$uid.", "; $q.= "Comments = ".$dbh->quote($comment).", "; - $q.= "EditedTS = UNIX_TIMESTAMP() "; + $q.= "EditedTS = " . strval(time()) . " "; $q.= "WHERE ID = ".intval($comment_id); $dbh->exec($q); return array(true, __("Comment has been edited.")); diff --git a/web/lib/pkgreqfuncs.inc.php b/web/lib/pkgreqfuncs.inc.php index 8ceac8d..e4556a3 100644 --- a/web/lib/pkgreqfuncs.inc.php +++ b/web/lib/pkgreqfuncs.inc.php @@ -149,7 +149,7 @@ function pkgreq_file($ids, $type, $merge_into, $comments) { $q.= "UsersID, Comments, RequestTS) VALUES (" . $type_id . ", "; $q.= $base_id . ", " . $dbh->quote($pkgbase_name) . ", "; $q.= $dbh->quote($merge_into) . ", " . $uid . ", "; - $q.= $dbh->quote($comments) . ", UNIX_TIMESTAMP())"; + $q.= $dbh->quote($comments) . ", " . strval(time()) . ")"; $dbh->exec($q); $request_id = $dbh->lastInsertId(); -- 2.10.2
In commit baf8a22 (git-interface: Support SQLite as database backend, 2016-08-03), conf/config.proto was changed so that dsn_prefix was changed to backend and this fixes this in web/lib/DB.class.php. Since SQLite's dsn is different, this adds a check of which backend is desired and will quit if MySQL or SQLite are not the backend selected. SQLite2 may be supported, but is untested and will trigger an error if used. Signed-off-by: Mark Weiman <mark.weiman@markzz.com> --- web/lib/DB.class.php | 24 +++++++++++++++++------- 1 file changed, 17 insertions(+), 7 deletions(-) diff --git a/web/lib/DB.class.php b/web/lib/DB.class.php index b538e0d..dfdbbf9 100644 --- a/web/lib/DB.class.php +++ b/web/lib/DB.class.php @@ -17,20 +17,30 @@ class DB { public static function connect() { if (self::$dbh === null) { try { - $dsn_prefix = config_get('database', 'dsn_prefix'); + $backend = config_get('database', 'backend'); $host = config_get('database', 'host'); $socket = config_get('database', 'socket'); $name = config_get('database', 'name'); $user = config_get('database', 'user'); $password = config_get('database', 'password'); - $dsn = $dsn_prefix . - ':host=' . $host . - ';unix_socket=' . $socket . - ';dbname=' . $name; + if ($backend == "mysql") { + $dsn = $backend . + ':host=' . $host . + ';unix_socket=' . $socket . + ';dbname=' . $name; + + self::$dbh = new PDO($dsn, $user, $password); + self::$dbh->exec("SET NAMES 'utf8' COLLATE 'utf8_general_ci';"); + } else if ($backend == "sqlite") { + $dsn = $backend . + ":" . $name; + + self::$dbh = new PDO($dsn, null, null); + } else { + die("Error - " . $backend . " is not supported by aurweb"); + } - self::$dbh = new PDO($dsn, $user, $password); - self::$dbh->exec("SET NAMES 'utf8' COLLATE 'utf8_general_ci';"); } catch (PDOException $e) { die('Error - Could not connect to AUR database'); } -- 2.10.2
On Thu, 10 Nov 2016 at 00:54:38, Mark Weiman wrote:
In commit baf8a22 (git-interface: Support SQLite as database backend, 2016-08-03), conf/config.proto was changed so that dsn_prefix was changed to backend and this fixes this in web/lib/DB.class.php.
Since SQLite's dsn is different, this adds a check of which backend is desired and will quit if MySQL or SQLite are not the backend selected. SQLite2 may be supported, but is untested and will trigger an error if used.
Signed-off-by: Mark Weiman <mark.weiman@markzz.com> --- web/lib/DB.class.php | 24 +++++++++++++++++------- 1 file changed, 17 insertions(+), 7 deletions(-) [...]
Great! I merged this one, as well as the preparatory patch and the quote fix into pu!
participants (2)
-
Lukas Fleischer
-
Mark Weiman