diff options
author | David Sommerseth <dazo@users.sourceforge.net> | 2012-10-07 16:50:42 +0200 |
---|---|---|
committer | David Sommerseth <dazo@users.sourceforge.net> | 2012-10-08 02:18:14 +0200 |
commit | 3af4f9b2bacfc8383001bba95c4f3836a6cecca6 (patch) | |
tree | 7138162c2b371f2a1743b9cafc64fb5ed9a3f855 /database/sqlite | |
parent | 79b7a8c5ad5d4de6fc69d71585625b8a74198c47 (diff) | |
download | eurephia-3af4f9b2bacfc8383001bba95c4f3836a6cecca6.tar.gz eurephia-3af4f9b2bacfc8383001bba95c4f3836a6cecca6.tar.xz eurephia-3af4f9b2bacfc8383001bba95c4f3836a6cecca6.zip |
sqlite/admin: Report all timestamp fields with localtime instead of UTC/GMT
Made all SELECT queries which is used for reports to use the new 'locdt' SQL
function on timestamp fields. This converts the UTC/GMT timestamps stored in
the database to the correct timezone of the running admin client.
Signed-off-by: David Sommerseth <dazo@users.sourceforge.net>
Diffstat (limited to 'database/sqlite')
-rw-r--r-- | database/sqlite/administration/attempts.c | 2 | ||||
-rw-r--r-- | database/sqlite/administration/blacklist.c | 2 | ||||
-rw-r--r-- | database/sqlite/administration/certificates.c | 2 | ||||
-rw-r--r-- | database/sqlite/administration/firewalladmin.c | 2 | ||||
-rw-r--r-- | database/sqlite/administration/lastlog.c | 3 | ||||
-rw-r--r-- | database/sqlite/administration/useraccount.c | 14 | ||||
-rw-r--r-- | database/sqlite/administration/usercerts.c | 2 |
7 files changed, 15 insertions, 12 deletions
diff --git a/database/sqlite/administration/attempts.c b/database/sqlite/administration/attempts.c index d296172..c884b4e 100644 --- a/database/sqlite/administration/attempts.c +++ b/database/sqlite/administration/attempts.c @@ -67,7 +67,7 @@ xmlDoc *attempts_list(eurephiaCTX *ctx, eDBfieldMap *fmap) { // Query the database for registered attempts res = sqlite_query_mapped(ctx, SQL_SELECT, "SELECT username, lower(digest), remoteip, attempts," - " registered, last_attempt, atpid" + " locdt(registered), locdt(last_attempt), atpid" " FROM openvpn_attempts", NULL, fmap, "atpid"); if( sqlite_query_status(res) != dbSUCCESS ) { diff --git a/database/sqlite/administration/blacklist.c b/database/sqlite/administration/blacklist.c index 66a5f6f..0662bbc 100644 --- a/database/sqlite/administration/blacklist.c +++ b/database/sqlite/administration/blacklist.c @@ -68,7 +68,7 @@ xmlDoc *blacklist_list(eurephiaCTX *ctx, eDBfieldMap *fmap) { // Query the database for registered attempts res = sqlite_query_mapped(ctx, SQL_SELECT, "SELECT username, lower(digest), remoteip," - " registered, last_accessed, blid" + " locdt(registered), locdt(last_accessed), blid" " FROM openvpn_blacklist", NULL, fmap, "blid"); if( sqlite_query_status(res) != dbSUCCESS ) { diff --git a/database/sqlite/administration/certificates.c b/database/sqlite/administration/certificates.c index 8db12ff..93518b6 100644 --- a/database/sqlite/administration/certificates.c +++ b/database/sqlite/administration/certificates.c @@ -87,7 +87,7 @@ static xmlDoc *certificate_list(eurephiaCTX *ctx, eDBfieldMap *srch_map, const c res = sqlite_query_mapped(ctx, SQL_SELECT, "SELECT depth, lower(digest), common_name, organisation, email, " - " registered, certid" + " locdt(registered), certid" " FROM openvpn_certificates", NULL, srch_map, sortkeys); if( sqlite_query_status(res) != dbSUCCESS ) { eurephia_log(ctx, LOG_ERROR, 0, "Could not query the certificate table"); diff --git a/database/sqlite/administration/firewalladmin.c b/database/sqlite/administration/firewalladmin.c index fd34183..b10c5d0 100644 --- a/database/sqlite/administration/firewalladmin.c +++ b/database/sqlite/administration/firewalladmin.c @@ -81,7 +81,7 @@ xmlDoc *fwadmin_search(eurephiaCTX *ctx, eDBfieldMap *fmap) { "SELECT access_descr, fw_profile, accessprofile, " " uid, username, " " uac.certid, common_name, organisation, " - " email, lower(digest), c.registered, uicid " + " email, lower(digest), locdt(c.registered), uicid " " FROM openvpn_accesses" " LEFT JOIN openvpn_usercerts uac USING (accessprofile)" " LEFT JOIN openvpn_users USING (uid)" diff --git a/database/sqlite/administration/lastlog.c b/database/sqlite/administration/lastlog.c index ff7b479..2fc2904 100644 --- a/database/sqlite/administration/lastlog.c +++ b/database/sqlite/administration/lastlog.c @@ -94,7 +94,8 @@ xmlDoc *eDBadminGetLastlog(eurephiaCTX *ctx, xmlDoc *srch_xml, const char *sortk res = sqlite_query_mapped(ctx, SQL_SELECT, "SELECT llid, ll.certid, protocol, remotehost, remoteport, macaddr," " vpnipaddr, vpnipmask, sessionstatus, sessionkey," - " login, logout, session_duration, session_deleted," + " locdt(login), locdt(logout)," + " session_duration, locdt(session_deleted)," " bytes_sent, bytes_received, uicid, accessprofile," " access_descr, fw_profile, depth, lower(digest)," " common_name, organisation, email, username, ll.uid" diff --git a/database/sqlite/administration/useraccount.c b/database/sqlite/administration/useraccount.c index a989257..31d4af2 100644 --- a/database/sqlite/administration/useraccount.c +++ b/database/sqlite/administration/useraccount.c @@ -105,8 +105,8 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType, // Query the database, find the user defined in the user map uinf = sqlite_query_mapped(ctx, SQL_SELECT, - "SELECT users.username, users.activated, users.deactivated," - " users.last_accessed, users.uid," + "SELECT users.username, locdt(users.activated), locdt(users.deactivated)," + " locdt(users.last_accessed), users.uid," " (bl.username IS NOT NULL), opensess, logincount," " (at.attempts > 0)" " FROM openvpn_users users" @@ -181,7 +181,8 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType, // Extract certificate info qres = sqlite_query(ctx, "SELECT depth, lower(digest), common_name, organisation, email, " - " c.registered, c.certid, uc.accessprofile, access_descr," + " locdt(c.registered), c.certid," + " uc.accessprofile, access_descr," " fw_profile" " FROM openvpn_certificates c" " LEFT JOIN openvpn_usercerts uc ON (c.certid = uc.certid)" @@ -233,7 +234,8 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType, qres = sqlite_query(ctx, "SELECT llid, ll.certid,protocol,remotehost,remoteport,macaddr," " vpnipaddr, vpnipmask, sessionstatus, sessionkey," - " login, logout, session_duration, session_deleted," + " locdt(login), locdt(logout)," + " session_duration, locdt(session_deleted)," " bytes_sent, bytes_received, uicid, accessprofile," " access_descr, fw_profile, depth, lower(digest)," " common_name, organisation, email" @@ -308,7 +310,7 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType, xmlNode *atmpt = NULL; qres = sqlite_query(ctx, - "SELECT attempts, registered, last_attempt, atpid" + "SELECT attempts, locdt(registered), locdt(last_attempt), atpid" " FROM openvpn_attempts " " WHERE username = '%q'", username); @@ -342,7 +344,7 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType, xmlNode *atmpt = NULL; qres = sqlite_query(ctx, - "SELECT registered, last_accessed, blid" + "SELECT locdt(registered), locdt(last_accessed), blid" " FROM openvpn_blacklist " " WHERE username = '%q'", username); diff --git a/database/sqlite/administration/usercerts.c b/database/sqlite/administration/usercerts.c index 8e654ab..c6f368e 100644 --- a/database/sqlite/administration/usercerts.c +++ b/database/sqlite/administration/usercerts.c @@ -81,7 +81,7 @@ xmlDoc *usercerts_search(eurephiaCTX *ctx, eDBfieldMap *where_m, const char *sor } res = sqlite_query_mapped(ctx, SQL_SELECT, - "SELECT uicid, ucs.uid AS uid, certid, ucs.registered AS registered," + "SELECT uicid, ucs.uid AS uid, certid, locdt(ucs.registered) AS registered," " ucs.accessprofile AS accessprofile, access_descr," " username, " " common_name, organisation, email, lower(digest), depth " |