From ef6092ccaaabd274e1c04505cc57ffc829e5627e Mon Sep 17 00:00:00 2001 From: Will Budic Date: Wed, 8 Jan 2020 08:11:52 +1100 Subject: [PATCH] Implemented relese ver. control and logging. --- htdocs/cgi-bin/config.cgi | 3 + htdocs/cgi-bin/login_ctr.cgi | 112 ++++++++++++++++++---- htdocs/cgi-bin/system/modules/Settings.pm | 70 +++++++++++++- 3 files changed, 164 insertions(+), 21 deletions(-) diff --git a/htdocs/cgi-bin/config.cgi b/htdocs/cgi-bin/config.cgi index dc978eb..58601e2 100755 --- a/htdocs/cgi-bin/config.cgi +++ b/htdocs/cgi-bin/config.cgi @@ -176,6 +176,9 @@ while(my @row = $dbs->fetchrow_array()) { my $n = $row[1]; my $v = $row[2]; my $d = $row[3]; + + next if($n =~ m/^\^/); + if($n eq "TIME_ZONE"){ $n = ''.$n.''; if($tz){ diff --git a/htdocs/cgi-bin/login_ctr.cgi b/htdocs/cgi-bin/login_ctr.cgi index 7eaaf16..e0750e0 100755 --- a/htdocs/cgi-bin/login_ctr.cgi +++ b/htdocs/cgi-bin/login_ctr.cgi @@ -31,8 +31,9 @@ my $cookie = $cgi->cookie(CGISESSID => $sid); my $alias = $cgi->param('alias'); my $passw = $cgi->param('passw'); -my $frm; - +my ($debug,$frm) = ""; +#Codebase release version. Release in the created db or existing one can be different, through time. +my $RELEASE = Settings::release(); #This is the OS developer release key, replace on istallation. As it is not secure. my $cipher_key = '95d7a85ba891da'; @@ -81,7 +82,9 @@ print qq(

Welcome to Life Log

$frm

Get latest version of this application here!
); - print $cgi->end_html; + +Settings::printDebugHTML($debug) if (&Settings::debug); +print $cgi->end_html; } @@ -240,15 +243,7 @@ try{ $st = $db->prepare(selSQLTbl('AUTH')); $st->execute(); if(!$st->fetchrow_array()) { - # - # @TODO - # AUTH Action Flags - # 00|DEFAULT`No action idle use.| - # 02|CONF_UPD`Configuration file update with db. - # 03|EMAIL`Issue email.| - # 06|DESTRUCT`Self destruct, remove alias and all data. - # 08|CHNG_PASS`Change password. - # 10|CHNG_ALIAS`Change alias. + my $stmt = qq( CREATE TABLE AUTH( @@ -315,17 +310,46 @@ try{ } else{ - #Has configuration been wiped out? $st = $db->prepare('SELECT count(ID) FROM CONFIG;'); - $st->execute(); - $changed = 1 if($st->fetchrow_array()==0); - + $st->execute(); + if(!$st->fetchrow_array()){ + $changed = 1; + } } #We got an db now, lets get settings from there. Settings::getConfiguration($db); + if(!$changed){ + #Run db fix renum if this is an relese update? Relese in software might not be what is in db, which counts. + #$st = Settings::dbExecute($db, 'SELECT NAME, VALUE FROM CONFIG WHERE NAME == "RELEASE_VER";'); + $st = $db->prepare('SELECT ID, NAME, VALUE FROM CONFIG WHERE NAME IS "RELEASE_VER";'); + $st->execute() or die "

ERROR with->$DBI::errstri

"; + my @pair = $st->fetchrow_array(); + my $cmp = $pair[2] eq $RELEASE; + $debug .= "Upgrade cmp(RELESE_VER:'$pair[2]' eq Settings::release:'$RELEASE') == $cmp"; + #Settings::debug(1); + if(!$cmp){ + Settings::renumerate($db); + #App private inner db properties, start from 200. + #^REL_RENUM is marker that an renumeration is issued during upgrade. + my $pv = obtainProperty($db, '^REL_RENUM'); + if($pv){ + $pv += 1; + } + else{ + $pv = "1"; + } + configProperty($db, 200, '^REL_RENUM',$pv); + configProperty($db, $pair[0], 'RELEASE_VER', $RELEASE); + Settings::toLog($db,&dbTimeStamp, "Upgraded LifeLog from ".$pair[2]." to $RELEASE version, this is the $pv upgrade."); + &populate($db); + } + } + else{ + &populate($db); + } # - &populate($db) if $changed; + $db->disconnect(); # #Still going through checking tables and data, all above as we might have an version update in code. @@ -342,6 +366,56 @@ try{ } } +#TODO move this subroutine to settings. +sub dbTimeStamp { + my $dat = DateTime->now; + $dat -> set_time_zone(Settings::timezone()); + return DateTime::Format::SQLite->format_datetime($dat); +} +#TODO move this subroutine to settings. +sub obtainProperty { + my($db, $name) = @_; + die "Invalid use of subroutine obtainProperty($db)" if(!$db || !$name); + try{ + my $dbs = Settings::dbExecute($db, "SELECT ID, VALUE FROM CONFIG WHERE NAME IS '$name';"); + my @row = $dbs->fetchrow_array(); + if(scalar @row > 0){ + return $row[1]; + } + else{ + return 0; + } + } + catch{ + print $cgi->header; + print "SERVER ERROR[obtainProperty(db, $name)]:".$_; + print $cgi->end_html; + exit; + } +} +#TODO move this subroutine to settings. +sub configProperty { + my($db, $id, $name, $value) = @_; + die "Invalid use of subroutine obtaiconfigPropertynProperty($db)" if(!$db || !$name|| !$value); +try{ + + my $dbs = Settings::dbExecute($db, "SELECT ID, NAME FROM CONFIG WHERE NAME IS '$name';"); + if($dbs->fetchrow_array()){ + Settings::dbExecute($db, "UPDATE CONFIG SET VALUE = '$value' WHERE NAME IS '$name';"); + } + else{ + Settings::dbExecute($db,"INSERT INTO CONFIG (ID, NAME, VALUE) VALUES ($id, '$name', '$value');"); + + } +} + catch{ + print $cgi->header; + print "SERVER ERROR[configProperty(db, $id, $name, $value)]:".$_; + print $cgi->end_html; + exit; + } + +} sub populate { @@ -393,7 +467,7 @@ $err .= "UID{$id} taken by $vars{$id}-> $line\n"; $st->execute(); $inData = 1; if(!$st->fetchrow_array()) { - $insConfig->execute($id,$name,$value,$tick[1]) if(!$st->fetchrow_array()); + $insConfig->execute($id,$name,$value,$tick[1]); } } } @@ -412,7 +486,7 @@ $err .= "Invalid, spec'ed {uid}|{variable}`{description}-> $line\n"; $st->execute(); $inData = 1; if(!$st->fetchrow_array()) { - $insCat->execute($pair[0],$pair[1],$tick[1]) if(!$st->fetchrow_array()); + $insCat->execute($pair[0],$pair[1],$tick[1]); } } else { diff --git a/htdocs/cgi-bin/system/modules/Settings.pm b/htdocs/cgi-bin/system/modules/Settings.pm index 8adaeb5..ee87226 100644 --- a/htdocs/cgi-bin/system/modules/Settings.pm +++ b/htdocs/cgi-bin/system/modules/Settings.pm @@ -8,6 +8,7 @@ use Switch; use DBI; #DEFAULT SETTINGS HERE! +our $RELEASE_VER = '1.7'; our $REC_LIMIT = 25; our $TIME_ZONE = 'Australia/Sydney'; our $LANGUAGE = 'English'; @@ -15,7 +16,6 @@ our $PRC_WIDTH = '60'; our $LOG_PATH = '../../dbLifeLog/'; our $SESSN_EXPR = '+30m'; our $DATE_UNI = '0'; -our $RELEASE_VER = '1.7'; our $AUTHORITY = ''; our $IMG_W_H = '210x120'; our $AUTO_WRD_LMT = 1000; @@ -52,7 +52,7 @@ sub windowRTFSize {return $RTF_SIZE;} sub bgcol {return $BGCOL;} sub css {return $TH_CSS;} -sub debug {return $DEBUG;} +sub debug {my $ret =shift; if($ret){$DEBUG = $ret;}; return $DEBUG;} @@ -104,4 +104,70 @@ sub getTheme { } + +sub renumerate { + my $db = shift; + #Renumerate Log! Copy into temp. table. + my $sql; + my $dbs = dbExecute($db, 'CREATE TABLE life_log_temp_table AS SELECT * FROM LOG;'); + $dbs = dbExecute($db, 'SELECT rowid, DATE FROM LOG WHERE RTF == 1 ORDER BY DATE;'); + #update notes with new log id + while(my @row = $dbs->fetchrow_array()) { + my $sql_date = $row[1]; + #$sql_date =~ s/T/ /; + $sql_date = DateTime::Format::SQLite->parse_datetime($sql_date); + $sql = "SELECT rowid, DATE FROM life_log_temp_table WHERE RTF = 1 AND DATE = '".$sql_date."';"; + $dbs = dbExecute($db, $sql); + my @new = $dbs->fetchrow_array(); + if(scalar @new > 0){ + $db->do("UPDATE NOTES SET LID =". $new[0]." WHERE LID==".$row[0].";"); + } + } + + # Delete Orphaned Notes entries. + $dbs = dbExecute($db, "SELECT LID, LOG.rowid from NOTES LEFT JOIN LOG ON + NOTES.LID = LOG.rowid WHERE LOG.rowid is NULL;"); + while(my @row = $dbs->fetchrow_array()) { + $db->do("DELETE FROM NOTES WHERE LID=$row[0];"); + } + $dbs = dbExecute($db, 'DROP TABLE LOG;'); + $dbs = dbExecute($db, qq(CREATE TABLE LOG ( + ID_CAT TINY NOT NULL, + DATE DATETIME NOT NULL, + LOG VCHAR (128) NOT NULL, + AMOUNT INTEGER, + AFLAG TINY DEFAULT 0, + RTF BOOL DEFAULT 0, + STICKY BOOL DEFAULT 0 + );)); + $dbs = dbExecute($db, 'INSERT INTO LOG (ID_CAT,DATE,LOG,AMOUNT,AFLAG, RTF) + SELECT ID_CAT, DATE, LOG, AMOUNT, AFLAG, RTF + FROM life_log_temp_table ORDER by DATE;'); + $dbs = dbExecute($db, 'DROP TABLE life_log_temp_table;'); +} + +sub dbExecute { + my ($db,$sql) = @_; + my $ret = $db->prepare($sql); + $ret->execute() or die "

ERROR with->$sql

"; + return $ret; +} + +sub printDebugHTML { + my $msg = shift; + print qq(); +} + +sub toLog { + my ($db,$stamp,$log) = @_; + # try { + #Apostrophe in the log value is doubled to avoid SQL errors. + $log =~ s/'/''/g; + $db->do("INSERT INTO LOG (ID_CAT, DATE, LOG) VALUES(6,'$stamp', \"$log\");"); + # } + # catch { + # print "SERVER ERROR toLog(6,$stamp,$log):" . $_; + # } +} + 1; \ No newline at end of file -- 2.34.1