From 7e7e0f730b49ccfe21fd864fa19380d387933f0b Mon Sep 17 00:00:00 2001 From: Will Budic Date: Sat, 18 May 2024 15:33:13 +1000 Subject: [PATCH] Imp. View type CNF data mapping to external data. --- system/modules/CNFParser.pm | 4 +- system/modules/CNFSQL.pm | 6 +++ tests/DatabaseCentralPlugin.pm | 79 ++++++++++++++++++++++++++++------ tests/testSQL.pl | 47 +++++++++++++++++--- 4 files changed, 114 insertions(+), 22 deletions(-) diff --git a/system/modules/CNFParser.pm b/system/modules/CNFParser.pm index 524dc20..ad42b2e 100644 --- a/system/modules/CNFParser.pm +++ b/system/modules/CNFParser.pm @@ -1437,12 +1437,12 @@ sub dumpENV{ } sub SQL { - my $self = shift; + my ($self,$e,$v) = @_; if(!$SQL){##It is late compiled package on demand. my $data = shift; require CNFSQL; $SQL = CNFSQL->new({parser=>$self}); } - $SQL->addStatement(@_) if @_; + $SQL->addStatement($e,$v) if $e; return $SQL; } our $JSON; diff --git a/system/modules/CNFSQL.pm b/system/modules/CNFSQL.pm index e92f83f..d33133d 100644 --- a/system/modules/CNFSQL.pm +++ b/system/modules/CNFSQL.pm @@ -328,6 +328,12 @@ sub createTable { my ($self, $name, $body, $idType) = @_; $tables_data_map{$name} = $1; $body = $2; } + elsif($body =~ s/${CNFMeta::_meta('MAP_CNF_DB_VIEW')}/""/sexi){ + $body =~ m/\s*(\w*)(.*)/gs; + $tables_data_map{$1} = $2; + $tables_id_type{$name} = $idType; + return; + } $tables{$name} = "CREATE TABLE $name(\n$body);"; $tables_id_type{$name} = $idType; } diff --git a/tests/DatabaseCentralPlugin.pm b/tests/DatabaseCentralPlugin.pm index 9906232..661e071 100644 --- a/tests/DatabaseCentralPlugin.pm +++ b/tests/DatabaseCentralPlugin.pm @@ -12,7 +12,7 @@ use Syntax::Keyword::Try; use Clone qw(clone); -my ($isSQLite,%tables)=(0,()); +my ($isSQLite,%tables,$dsn)=(0,()); sub new ($class, $plugin){ my $settings; @@ -54,22 +54,76 @@ sub getConfigFiles($self, $parser, $property){ data=>\@files } } +### +sub connectDB($self,$user,$passw){ + my $datasource = $self->{DBI_SQL_SOURCE}; + die "DBI_SQL_SOURCE not set!" if !$datasource; + my $dbname = $self->{DB}; + die "DB not set!" if !$dbname; + if(!$user and !$passw){ + my $dbcreds = $self->{DB_CREDENTIALS}; + ($user,$passw) = split '/', $dbcreds + } + $isSQLite = $datasource =~ /DBI:SQLite/i; + $dsn = $datasource .'dbname='.$dbname.($isSQLite?".db":""); + return DBI->connect($dsn, $user, $passw, {AutoCommit => 1, RaiseError => 1, PrintError => 0, show_trace=>1}); +} +sub executeStatements($self,$parser,$property){ + my $db; + if($property eq '*'){ + foreach my $key(%{$parser->SQL()}){ + my $sql = $parser->SQL()->{$key}; + next if($key eq 'parser'); + $db = connectDB($self,undef,undef) if !$db; + executePropertyStatement($self,$parser,$db,$key,$sql); + } + }else{ + executePropertyStatement($self,$parser,$db,$property,$parser->SQL()->{$property}); + } +} +sub executePropertyStatement($self,$parser,$db,$key,$sql){ + $db = connectDB($self,undef,undef) if !$db; + my $pst = $parser->SQL()->selectRecords($db,$sql); + if(!$pst){ + $parser->error("Failed to prepare statment -> $sql") + }else{ + my @data; + while(my @row = $pst->fetchrow_array()){ + $data[@data] = \@row; + } + my @spec; + my @hdr =[]; + my $spec = $CNFSQL::tables_data_map{$key}; + if($spec){ + my @cols = $spec =~ m/\s*([^`~]*)[`~]{0,1}\s*/gm;pop @cols;#<-regexp is special must pop last empty element. + @hdr = CNFMeta::_metaTranslateDataHeader($isSQLite,@cols); + } + my $table = { + name=>$key, + header=>\[\@hdr,"","",\@spec], + auto=>0, + data=>\@data + }; + $parser -> data() ->{$key} = \$table if @data; + } +} +sub doStatement($self,$db, $sql) { +try{ + my $pst = $db->prepare($sql); + return if !$pst; + $pst->execute(); + return $pst +}catch{ + PluginException->throw(error=>"

Error->$@


DSN: $dsn sql:$sql
", show_trace=>1); +} +} sub main ($self, $parser, $property) { my $item = $parser->anon($property); die "Property not found [$property]!" if !$item; - my $datasource = $self->{DBI_SQL_SOURCE}; - die "DBI_SQL_SOURCE not set!" if !$item; - my $dbname = $self->{DB}; - die "DB not set!" if !$item; - my $dbcreds = $self->{DB_CREDENTIALS}; - my ($dsn,$db); + my ($db); try{ - my ($u,$p) = split '/', $dbcreds; - $isSQLite = $datasource =~ /DBI:SQLite/i; - $dbname .= '.db' if $isSQLite; - $dsn = $datasource .'dbname='.$dbname; - $db = DBI->connect($dsn, $u, $p, {AutoCommit => 1, RaiseError => 1, PrintError => 0, show_trace=>1}); + $db = connectDB($self,undef,undef); if($isSQLite){ my $pst = $db->prepare("SELECT name FROM sqlite_master WHERE type='table' or type='view';"); die if !$pst; @@ -87,7 +141,6 @@ sub main ($self, $parser, $property) { }catch{ PluginException->throw(error=>"

Error->$@


DSN: $dsn
", show_trace=>1); } - my $ref = ref($item); if($ref eq 'CNFNode'){ my @tables = @{$item -> find('table/*')}; diff --git a/tests/testSQL.pl b/tests/testSQL.pl index a09a2d8..59cefe1 100644 --- a/tests/testSQL.pl +++ b/tests/testSQL.pl @@ -72,7 +72,7 @@ try{ "ID" INTEGER NOT NULL, PRIMARY KEY ("ID" AUTOINCREMENT) >> - < __HAS_HEADER__ + < __HAS_HEADER__ ____AUTONUMBER_ ID`NAME`Gender~ #`Mickey Mouse`rat~ 5`Donald Duck`food~ @@ -81,14 +81,47 @@ try{ )); my $central = $cnf->property('DB_CENTRAL'); my $db = CNFSQL::_connectDB('test','test',$central->{DBI_SQL_SOURCE},$central->{DB}.'.db'); - $sql = $cnf->SQL(); - $sql -> {data } = $sql2->{parser}->data(); + my $sql = $cnf->SQL(); + $sql -> {data } = $sql->{parser}->data(); #$sql -> initDatabase($db,0,{'TBL_A' => ['TBL_A_DATA','name','ID']}); $sql -> initDatabase($db,0); - # - # - $test->done(); - # + + $test->case("Test db view to existing database."); + $cnf = CNFParser->new(undef,{DO_ENABLED=>1,DEBUG=>1,'%LOG'=>{console=>1}}); + $cnf->parse(undef,qq( +< + DB = test_db_central + DB_CREDENTIALS = admin/admin + DBI_SQL_SOURCE = DBI:SQLite: + package : DatabaseCentralPlugin + subroutine : executeStatements + property : VIEW_TBL_A +>> + +# This is the table spec for an unknow to this script physical SQL database table. +# Its slection statment is mapped with __MAP_CNF_DB_VIEW__ VIEW_TBL_A, without this meta instruction +# auto resolwing will not work. +# +< __MAP_CNF_DB_VIEW__ VIEW_TBL_A +ID _INT_`NAME _TEXT_~ +>> + +# It is more readable not to map the above to an CNF instruction. +# The following statment can be both table and a view, which is recomended. +# +<select ID, Name from TBL_A;>> +)); + $central = $cnf->property('DB_CENTRAL'); + my $view = $cnf->data()->{VIEW_TBL_A}; + my @data = @{$$view->{data}}; + $test->evaluate("Mapped view has selected three records?",3,scalar @data); + $test->evaluate("Mapped view has record 2 at index 1?",'Donald Duck', $data[1][1]); #<-view is ID-0, Name is 1. +# +# +$test->done(); +# + + } catch{ $test -> dumpTermination($@); -- 2.34.1