From 3617bd034ac0cfd9265a7a550556a5be7ecb7892 Mon Sep 17 00:00:00 2001 From: wbudic Date: Thu, 5 Aug 2021 20:05:11 +1000 Subject: [PATCH] Started work on bug 35, PG db related. --- Current Development Check List.md | 2 ++ dbLifeLog/main.cnf | 8 +++-- htdocs/cgi-bin/data.cgi | 2 +- htdocs/cgi-bin/login_ctr.cgi | 42 ++++++++++++----------- htdocs/cgi-bin/system/modules/Settings.pm | 5 +-- 5 files changed, 34 insertions(+), 25 deletions(-) diff --git a/Current Development Check List.md b/Current Development Check List.md index e640b19..524369a 100644 --- a/Current Development Check List.md +++ b/Current Development Check List.md @@ -6,6 +6,7 @@ ### New Development +* Bug 35. SQL migration, version update, not working for PG based databases. * JS - Event, on expense sum in log numbers found at beginning of lines. * ✔ Update to CNF v.2.2, branch to $RELEASE_VER = 2.3, Earth Stage initial. * Plugins @@ -206,6 +207,7 @@ between 2018-08-22 04:13:55 **Moon Stable** production release and this ### v. 2.2 Encountered/Fixed +* Bug 35, Migration and version updating SQL is wrong for PG database, it doesn't have rowid's. * Bug 34, DB fix in config, removes associated RTF documents, for some reason. * ✔ Bug 33 Changing session timeout in config to an lib. background unparsable format cause unrecoverable system error. * i.e. Putting +1hr instead of +1h. diff --git a/dbLifeLog/main.cnf b/dbLifeLog/main.cnf index b58940d..2b34180 100644 --- a/dbLifeLog/main.cnf +++ b/dbLifeLog/main.cnf @@ -13,9 +13,9 @@ This is an Open Source License project -> https://choosealicense.com/licenses/is Note Remove -> '!' prefix to tag name, to enable, set, and override any previously set from now on, top to end of file approach. -<>> +<>> # ProgresSQL DB driver. -<>> +<>> # LOG actual variable size limit, - 1024 default, 128 minimum value. <>> @@ -39,6 +39,10 @@ America/Austin=America/Chicago America/Texas=America/Chicago >> +<help.pl +BankingPlugin->banking.pl +>> # @See help at bottom of config for all the following. diff --git a/htdocs/cgi-bin/data.cgi b/htdocs/cgi-bin/data.cgi index f29bb87..a594e67 100755 --- a/htdocs/cgi-bin/data.cgi +++ b/htdocs/cgi-bin/data.cgi @@ -22,7 +22,7 @@ my $dbname = Settings::dbName(); my $human = DateTime::Format::Human::Duration->new(); my $PRC_WIDTH = Settings::pagePrcWidth(); my $DEBUG = Settings::debug(); -my $today = Settings::today(); +my $today = Settings::today(); my $tbl_rc = 0; my $imgw = 210; my $imgh = 120; diff --git a/htdocs/cgi-bin/login_ctr.cgi b/htdocs/cgi-bin/login_ctr.cgi index 3f48209..068cf3b 100755 --- a/htdocs/cgi-bin/login_ctr.cgi +++ b/htdocs/cgi-bin/login_ctr.cgi @@ -103,7 +103,7 @@ try{ "
SERVER ERROR on ".DateTime->now(). "
".$pwd."/$0 -> [\n$err]","\n$dbg
", $cgi->end_html; - }; + } exit; sub processSubmit { @@ -172,7 +172,6 @@ sub checkAutologinSet { #exit; # Autologin credential in file next. if(@cre &&scalar(@cre)>1){ - if($alias && $passw && $alias ne $cre[0]){ # Is this an new alias login attempt? If so, autologin is of the agenda. return; # Note, we do assign entered password even passw as autologin is set. Not entering one bypasses this. } # If stricter access is required set it to zero in main.cnf, or disable in config. @@ -191,7 +190,6 @@ sub checkAutologinSet { } $db -> disconnect(); } - } sub checkPreparePGDB { @@ -236,11 +234,8 @@ sub checkPreparePGDB { return 0; } -sub checkCreateTables { -my ($pst, $sql,$rv, $changed) = 0; -try{ - - +sub checkCreateTables { my ($pst, $sql,$rv, $changed) = 0; + try{ # We live check database for available tables now only once. # If brand new database, this sill returns fine an empty array. my %curr_tables = (); @@ -330,18 +325,27 @@ try{ $db->do('CREATE TABLE life_log_login_ctr_temp_table AS SELECT * FROM LOG;'); my %notes_ids = (); if($hasNotesTbl){ - $pst = Settings::selectRecords($db, 'SELECT rowid, DATE FROM LOG WHERE ID_RTF > 0 ORDER BY DATE;'); + if(Settings::isProgressDB()){ + $sql='SELECT ID, DATE FROM LOG WHERE ID_RTF > 0 ORDER BY DATE;'} + else{$sql='SELECT rowid, DATE FROM LOG WHERE ID_RTF > 0 ORDER BY DATE;'} + $pst = Settings::selectRecords($db, $sql); while(my @row = $pst->fetchrow_array()) { my $sql_date = $row[1];; $sql_date = DateTime::Format::SQLite->parse_datetime($sql_date); - my $pst2 = Settings::selectRecords($db, "SELECT rowid, DATE FROM life_log_login_ctr_temp_table WHERE RTF > 0 AND DATE = '".$sql_date."';"); + if(Settings::isProgressDB()){ + $sql="SELECT ID, DATE FROM life_log_login_ctr_temp_table WHERE RTF > 0 AND DATE = '".$sql_date."';"} + else{$sql="SELECT rowid, DATE FROM life_log_login_ctr_temp_table WHERE RTF > 0 AND DATE = '".$sql_date."';"} + my $pst2 = Settings::selectRecords($db, $sql); my @rec = $pst2->fetchrow_array(); if(@rec){ - $db->do("UPDATE NOTES SET LID =". $rec[0]." WHERE LID ==".$row[0].";"); - $pst2 = Settings::selectRecords($db, "SELECT rowid FROM NOTES WHERE LID == ".$rec[0].";"); + $db->do("UPDATE NOTES SET LID=". $rec[0]." WHERE LID=".$row[0].";"); + if(Settings::isProgressDB()){ + $sql="SELECT LID FROM NOTES WHERE LID = ".$rec[0].";"} + else{$sql="SELECT rowid FROM NOTES WHERE LID = ".$rec[0].";"} + $pst2 = Settings::selectRecords($db, $sql); @rec = $pst2->fetchrow_array(); if(@rec){ - $notes_ids{$sql_date} = $rec[0]; + $notes_ids{$sql_date} = $rec[0]; } } } @@ -369,7 +373,7 @@ try{ foreach my $date (keys %notes_ids){ #next if(ref($notes_ids{$date}) eq 'HASH'); my $nid = $notes_ids{$date}; - my $stmt= "UPDATE LOG SET RTF =". $nid." WHERE DATE == '".$date."';"; + my $stmt= "UPDATE LOG SET RTF =". $nid." WHERE DATE = '".$date."';"; try{ $db->do($stmt); } @@ -395,9 +399,7 @@ try{ my $t ="BYTE"; $t = "SMALLINT" if Settings::isProgressDB(); $db->do("ALTER TABLE LOG ADD COLUMN RTF $t default 0");$changed = 1; } - elsif($SCRIPT_RELEASE > $DB_VERSION){$changed = 1;} - - + elsif($SCRIPT_RELEASE > $DB_VERSION){$changed = 1;} if(!$hasLogTbl) { @@ -539,9 +541,9 @@ try{ #Then we check if we are login in intereactively back. Interective, logout should bring us to the login screen. #Bypassing auto login. So to start maybe working on another database, and a new session. return $cgi->param('autologoff') == 1; -}catch{ - LifeLogException -> throw(error=>"DSN:".Settings::dsn()." Error:".$@."\nLAST_SQL:".$sql,show_trace=>1); -} + }catch{ + LifeLogException -> throw(error=>"DSN:".Settings::dsn()." Error:".$@."\nLAST_SQL:".$sql,show_trace=>1); + } } sub createPageViewExcludeSQL { diff --git a/htdocs/cgi-bin/system/modules/Settings.pm b/htdocs/cgi-bin/system/modules/Settings.pm index bedb1ae..475208b 100644 --- a/htdocs/cgi-bin/system/modules/Settings.pm +++ b/htdocs/cgi-bin/system/modules/Settings.pm @@ -110,6 +110,7 @@ sub release {$RELEASE_VER} sub logPath {$S_ = shift;$LOG_PATH = $S_ if $S_;$LOG_PATH} sub theme {$THEME} sub timezone {$TIME_ZONE} +sub language {$LANGUAGE} sub sessionExprs {$SESSN_EXPR} sub imgWidthHeight {$IMG_W_H} sub pagePrcWidth {$PRC_WIDTH} @@ -507,11 +508,11 @@ sub renumerate { my @new = selectRecords($db, $sql)->fetchrow_array(); if(scalar @new > 0){ try{#can fail here, for various reasons. - $sql="UPDATE NOTES SET LID =". $new[0]." WHERE LID==".$row[0].";"; + $sql="UPDATE NOTES SET LID =". $new[0]." WHERE LID=".$row[0].";"; $db->do($sql); } catch{ - SettingsException->throw(error=>"Database error encountered. sql->$sql", show_trace=>$DEBUG); + SettingsException->throw(error=>"\@Settings::renumerate Database error encountered. sql->$sql", show_trace=>$DEBUG); }; } } -- 2.34.1