From 921298a22767607937dc33ceea31aae3f9c5cde1 Mon Sep 17 00:00:00 2001 From: Will Budicm Date: Fri, 4 Dec 2020 18:41:58 +1100 Subject: [PATCH] Further pg dev. and requirments. --- Current Development Check List.md | 3 ++ Installation_ProgresSQL.txt | 40 ++++++++++++++++------- htdocs/cgi-bin/login_ctr.cgi | 35 +++++++++++--------- htdocs/cgi-bin/system/modules/Settings.pm | 39 +++++++++------------- 4 files changed, 65 insertions(+), 52 deletions(-) diff --git a/Current Development Check List.md b/Current Development Check List.md index e66b8fc..145cc1e 100644 --- a/Current Development Check List.md +++ b/Current Development Check List.md @@ -6,6 +6,9 @@ ### v.2.1 SUN STABLE New Features in Works +* PostgreSQL to be further tested. Implement server managed database. + * On errors sessions appear not to be closed by driver, maybe this is required and they expire? + * Not all sql has been translated or proper database everywhere established. * Session expired should disable the log entry form. * Implement mapped provision of named timezones via main.inf, for towns not available in global list. * Javascript also needs to be updated to translate this properly. diff --git a/Installation_ProgresSQL.txt b/Installation_ProgresSQL.txt index efe2f12..83fbd5a 100644 --- a/Installation_ProgresSQL.txt +++ b/Installation_ProgresSQL.txt @@ -1,8 +1,8 @@ # Notes -ProgresSQL Server is a full database service solution (RDBSM). Handling interaction, like multipe enterprise schemas, databases, users and backups. +PostgreSQL Server is a full database service solution (RDBSM). Handling interaction, like multiple enterprise schemas, databases, users and backups. These all are and can be handled outside the scope of the LifeLog application. -However, it is not required or recomended to have a fully managed database system service, just to use for the LifeLog app. +However, it is not required or recommended to have a fully managed database system service, just to use for the LifeLog app. Modify the following anon driver property tag in dbLifeLog/main.cnf to specify: @@ -10,22 +10,38 @@ Modify the following anon driver property tag in dbLifeLog/main.cnf to specify: The alias is by default assumed the actual database name as well as the user. -Modify the following anon property, to have the SQL Server provide multiple users as different aliases to loging into specifed single database. +Modify the following anon property, to have the SQL Server provide multiple users as different aliases for the loging into a specified single database. < -to something like, to make logins now database users: +to something like, to make logins now for the database users: < -Where 'lifelog' is the database. +Where 'lifelog' is the server managed database. Hope all works for you, and happy logging! -# Install ProgresSQL +# Install ProgreSQL sudo mkdir /usr/include/postgresql -sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" > \ -/etc/apt/sources.list.d/postgresql.list' -sudo apt update -y +## Create the file repository configuration: +sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' + +## Import the repository signing key: +wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - + +## Update the package lists: +sudo apt-get update -y sudo apt upgrade -y -sudo apt install postgresql-12 -y + +## Install the latest version of PostgreSQL. +## If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql': +sudo apt-get -y install postgresql-12 + +## Optional -> Enable port 5432 for PG server for local network on server. +sudo uwf allow 5432/tcp +sudo vim /etc/postgresql/12/main/postgresql.conf to modify--> listen_addresses = '*' +sudo vim pg_hba.conf -> +host all all 0.0.0.0/0 md5 +host all all ::/0 md5 +sudo service postgresql restart ## Install required libpq-dev to compile test perl driver, later. sudo apt install libpq-dev @@ -34,11 +50,11 @@ sudo apt install libpq-dev sudo cpan DBD::Pg; -# To assign default postgresSQL user and db +# To assign default Postgres SQL user and test db sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';" sudo -u postgres psql -c "CREATE DATABASE testdb;" -## Start stop ProgresSQL +## Start stop Postgres SQL sudo service postgresql start ###manually Obtain dep. if encountering lib problems. with i.e.: diff --git a/htdocs/cgi-bin/login_ctr.cgi b/htdocs/cgi-bin/login_ctr.cgi index 58db360..5938a73 100755 --- a/htdocs/cgi-bin/login_ctr.cgi +++ b/htdocs/cgi-bin/login_ctr.cgi @@ -33,10 +33,9 @@ my $BACKUP_ENABLED = 0; my $AUTO_SET_TIMEZONE = 0; try{ - - logout() if($cgi->param('logout')); checkAutologinSet(); - if(&processSubmit==0){ + logout() if($cgi->param('logout')); + if(processSubmit()==0){ print $cgi->header(-expires=>"0s", -charset=>"UTF-8", -cookie=>$cookie); print $cgi->start_html( @@ -157,7 +156,9 @@ sub checkAutologinSet { $passw = $passw; Settings::removeOldSessions(); } + $st->finish(); } + $db -> disconnect(); } } @@ -173,7 +174,7 @@ sub checkPreparePGDB { if($n eq $alias){ $create = 0; last;} } if($create){ - my $db = DBI->connect("dbi:Pg:dbname=postgres"); + my $db = DBI->connect("dbi:Pg:dbname=postgres");#Default expected to exist db is postgres, holding roles. Settings::debug(1); $db->do(qq( CREATE ROLE $alias WITH @@ -197,8 +198,7 @@ sub checkPreparePGDB { CONNECTION LIMIT = -1; )); $db->disconnect(); undef $db; - } - return Settings::connectDB($alias, $passw) if !$db; + } } sub checkCreateTables { @@ -211,7 +211,8 @@ sub checkCreateTables { my %curr_tables = (); if(Settings::isProgressDB()){ - $db = checkPreparePGDB(); + checkPreparePGDB(); + $db = Settings::connectDB($alias, $passw); my @tbls = $db->tables(undef, 'public'); foreach (@tbls){ my $t = uc substr($_,7); @@ -219,7 +220,7 @@ sub checkCreateTables { } } else{ - $db = Settings::connectDB($alias, $passw) if !$db; + $db = Settings::connectDB($alias, $passw); $pst = Settings::selectRecords($db,"SELECT name FROM sqlite_master WHERE type='table' or type='view';"); while(my @r = $pst->fetchrow_array()){ $curr_tables{$r[0]} = 1; @@ -340,17 +341,18 @@ sub checkCreateTables { exit; } - $db->do(&Settings::createLOGStmt); + $db->do(Settings::createLOGStmt()); my $st = $db->prepare('INSERT INTO LOG(ID_CAT,DATE,LOG) VALUES (?,?,?)'); $st->execute( 3, $today, "DB Created!"); - $session->param("cdb", "1"); + $st->finish(); + $session->param("cdb", "1"); } - # From v.1.6 view use server side views, for pages and correct record by ID and PID lookups. + # From v.1.6 view uses server side views, for pages and correct record by ID and PID lookups. # This should make queries faster, less convulsed, and log renumeration less needed for accurate pagination. if(!$curr_tables{'VW_LOG'}) { - $rv = $db->do(Settings::createVW_LOGStmt()); + $db->do(Settings::createViewLOGStmt()); } if(!$curr_tables{'CAT'}) { $db->do(Settings::createCATStmt()); @@ -363,15 +365,16 @@ sub checkCreateTables { #Have cats been wiped out? $changed = 1 if Settings::countRecordsIn($db, 'CAT') == 0; - #TODO Multiple cats per log future table. + #TODO Future table for multiple cats per log if ever required. if(!$curr_tables{'LOGCATSREF'}) { - $db->do(&Settings::createLOGCATSREFStmt); + $db->do(Settings::createLOGCATSREFStmt()); } if(!$curr_tables{'AUTH'}) { - $db->do(&Settings::createAUTHStmt); + $db->do(Settings::createAUTHStmt()); my $st = $db->prepare('INSERT INTO AUTH VALUES (?,?,?,?);'); $st->execute($alias, $passw,"",0); + $st->finish(); } # # Scratch FTS4 implementation if present. @@ -415,7 +418,7 @@ sub checkCreateTables { } Settings::toLog($db, "Log accessed by $alias.") if(Settings::trackLogins()); # - $db->disconnect(); + $db->disconnect(); # #Still going through checking tables and data, all above as we might have an version update in code. #Then we check if we are login in intereactively back. Interective, logout should bring us to the login screen. diff --git a/htdocs/cgi-bin/system/modules/Settings.pm b/htdocs/cgi-bin/system/modules/Settings.pm index 8d7b229..9cc14cf 100644 --- a/htdocs/cgi-bin/system/modules/Settings.pm +++ b/htdocs/cgi-bin/system/modules/Settings.pm @@ -141,14 +141,15 @@ try { print $cgi->redirect("login_ctr.cgi?CGISESSID=$sid"); exit; } - my $ret = connectDB($alias, $pass); - dbSrc($sss->param('db_source')); + ##From here we have data source set, currently Progress DB SQL and SQLite SQL compatible. + dbSrc($sss->param('db_source')); + my $ret = connectDB($alias, $pass); getConfiguration($ret); getTheme(); $sss->expire($SESSN_EXPR); return $ret; -}catch{ +}catch{ SettingsException->throw(error=>$@, show_trace=>$DEBUG); exit; } @@ -193,7 +194,7 @@ sub createCONFIGStmt { if($IS_PG_DB){return qq( CREATE TABLE CONFIG( ID INT NOT NULL UNIQUE GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ), - NAME VARCHAR(16) UNIQUE, + NAME VARCHAR(28) UNIQUE, VALUE VARCHAR(28), DESCRIPTION VARCHAR(128), PRIMARY KEY(ID) @@ -269,7 +270,7 @@ sub selStartOfYear { return "date>=date('now','start of year')" } -sub createVW_LOGStmt { +sub createViewLOGStmt { if($IS_PG_DB){ return qq( CREATE VIEW VW_LOG AS @@ -448,12 +449,12 @@ sub selectRecords { SettingsException->throw("ERROR Argument number is wrong->db is:$db\n", show_trace=>$DEBUG); } try{ - my $pst = $db->prepare($sql) or SettingsException->throw("

ERROR with->$sql

", show_trace=>$DEBUG); + my $pst = $db->prepare($sql); $pst->execute(); return 0 if(!$pst); return $pst; }catch{ - SettingsException->throw(error=>"Database error encountered. Settings::selectRecords[$sql]", show_trace=>$DEBUG); + SettingsException->throw(error=>"Database error encountered!\n ERROR->".$@." SQL-> $sql DSN:".$DSN, show_trace=>$DEBUG); }; } @@ -462,16 +463,13 @@ sub getTableColumnNames { if(scalar(@_) < 2){ SettingsException->throw("ERROR Argument number is wrong->db is:$db\n", show_trace=>$DEBUG); } - try{ - my $pst = selectRecords($db, "SELECT name FROM PRAGMA_table_info('$table_name');"); - my @ret = (); - while(my @r = $pst->fetchrow_array()){ - push @ret, $r[0]; - } - return \@ret; - }catch{ - SettingsException->throw(error=>"Database error encountered.", show_trace=>$DEBUG); + + my $pst = selectRecords($db, "SELECT name FROM PRAGMA_table_info('$table_name');"); + my @ret = (); + while(my @r = $pst->fetchrow_array()){ + push @ret, $r[0]; } + } sub printDebugHTML { @@ -484,12 +482,7 @@ sub toLog { my $stamp = getCurrentSQLTimeStamp(); if(!$cat){ my @arr = selectRecords($db,"SELECT ID FROM CAT WHERE NAME LIKE 'System Log' or NAME LIKE 'System';")->fetchrow_array(); - if(@arr){ - $cat = $arr[0]; - } - else{ - $cat = 6; - } + if(@arr){$cat = $arr[0];}else{$cat = 6;} } $log =~ s/'/''/g; $db->do("INSERT INTO LOG (ID_CAT, DATE, LOG) VALUES($cat,'$stamp', '$log');"); @@ -531,8 +524,6 @@ sub removeOldSessions { } } - - sub obtainProperty { my($db, $name) = @_; SettingsException->throw("Invalid use of subroutine obtainProperty($db, $name)", show_trace=>$DEBUG) if(!$db || !$name); -- 2.34.1