From 7076343338ae3439f3c86f01144818abe8c31978 Mon Sep 17 00:00:00 2001
From: George Halkiadakis <gchalkiadakis@sklavenitis.co.gr>
Date: Sun, 12 Mar 2023 07:16:23 +0200
Subject: add container helpers; constuct public directory-tree

---
 html/app/models/Jorge.php | 169 ++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 169 insertions(+)
 create mode 100644 html/app/models/Jorge.php

(limited to 'html/app/models/Jorge.php')

diff --git a/html/app/models/Jorge.php b/html/app/models/Jorge.php
new file mode 100644
index 0000000..011b8c0
--- /dev/null
+++ b/html/app/models/Jorge.php
@@ -0,0 +1,169 @@
+<?php
+
+namespace app\models;
+
+use \Registry;
+
+/** Jorge
+ * a database reference and adminitration toolkit
+ * ---
+ * The name Jorge comes from Uberto Eco's book: 'il nome della rosa',
+ * (Jorge is the blind monk who supervises the monastery's library ;)
+ */
+class Jorge
+{
+
+    /** tables
+     * array to keep all database-tables' critical information.
+     * @key create (array) includes all 'CREATE TABLE' statements
+     * @key relate (array) includes relations between tables
+     * @key fields (array) includes all important fields (when no select*)
+     * @key filter (array) includes all fields used to filter results
+     */
+    private $tables = [
+        'create' => [],
+        'relate' => [],
+        'fields' => []
+    ];
+
+    public function __construct()
+    {
+        $db = Registry::use('database');
+
+        // get all tables
+        $tableList = $db->runQuery("SHOW TABLES", []);
+        
+        // get all CREATE TABLE statements
+        foreach($tableList as $tableArray) {
+            $table = array_shift($tableArray);
+            $create = $db->runQuery("SHOW CREATE TABLE {$table}", []);
+            $this->tables['create'][$table] = $create[0]['Create Table'];
+        }
+
+        // define relations
+        $this->tables['relate'] = [
+
+            'products' => [
+                'brands' => 'products.BrandID = brands.ID',
+                'products_to_product_categories' => 'products.ID = products_to_product_categories.SimpleProductID',
+                'products_to_images' => 'products.ID = products_to_images.SimpleProductID',
+                'prices' => 'products.SKU = prices.SKU'
+            ],
+
+            'products_to_product_categories' => [
+                'product_categories' => 'product_categories.ID = products_to_product_categories.ProductCategoryID'
+            ],
+
+            'products_to_images' => [
+                'products' => 'products_to_images.SimpleProductID = products.ID',
+                'assets' => 'products_to_images.ImageID = assets.ID'
+            ]
+
+        ];
+
+        $this->extractTableFields();
+
+        // return true;
+    }
+
+
+    /** Create...
+     * one or more database tables
+     * ---
+     * @param $datasource (string|void) : table name or none
+     * if none then all tables will be created
+     * @return (boolean)
+     * 
+     * NOTE:
+     * for safety reasons this method is not executing SQL;
+     * it just echoes the SQL that needs to be executed in 
+     * order to crate all database tables.
+     */
+    public function create($datasource = '')
+    {
+        // if no datasource passed then datasource is all tables
+        if ($datasource == '') {
+            $datasource = array_keys(self::$tables['create']);
+        
+        } else {
+            // if datasource exist, make it an array
+            if (array_key_exists($datasource, self::$tables['create'])) {
+                $datasource = [ $datasource ];
+
+            } else {
+                // table does not exist
+                return fasle;
+            }
+        }
+
+        $sql = "";
+        foreach( $datasource as $table ) {          
+            $sql .= "-- CREATE ". $table .";\n". self::$tables['create'][$table] . "\n\n";
+        }
+
+        return ['sql' => $sql];
+    }
+
+
+    /** Calculate fields of every table
+     * ---
+     * Parse every CREATE SQL statement and extract list of fields;
+     * Algorithm implements linear-parsing (faster than a recursive one)
+     */
+    private function extractTableFields()
+    {
+        // words used by SQL that can not be field names
+        $nonFields = ['PRIMARY', 'KEY', '(', ')'];    // reduced list (used on CREATE)
+
+        foreach( $this->tables['create'] as $table => $sqlCreate ) {
+
+            $fields = [];   // variable to hold the extracted fields
+
+            // get text between first open-parentesis and last close-parentesis
+            // this is waht lies between 'CREATE TABLE table(' and ') [ENGINE whatever]'
+            // (including the patenteses)
+            preg_match_all(
+                "/\((((?>[^()]+)|(?R))*)\)/",
+                str_replace("\n", '', $sqlCreate),
+                $match
+            );
+            // remove 1st+last parentesis
+            $mainDefinitions = substr($match[0][0], 1, -1);
+
+            // replace comma (,) on decimal definitions
+            // example: 'decimal(18, 2)' turns to 'decimal(18: 2)'
+            $sentences = preg_replace(
+                '/\\(([0-9]*)[ ,]([0-9 ]*)\\)/',
+                '($1:$2)',
+                $mainDefinitions,
+                -1
+            );
+
+            // devide the banth of sentences to field definitions
+            $defines = explode(',', $sentences);
+
+            // now each denine holds a full field definition
+            // like: `ID` int(11) NOT NULL AUTO_INCREMENT
+
+            foreach($defines as $def) {
+                // check the first term of each sentence
+                $terms = explode(' ', trim($def,));
+                $term = array_shift($terms);
+                
+                if (!in_array($term, $nonFields)) {
+                    $fields[ str_replace('`', '', $term) ] = implode(' ', $terms);
+                }
+            }
+
+            $this->tables['fields'][$table] = $fields;
+        }
+
+        return;
+    }
+
+    public function databaseDocumentation()
+    {
+        return $this->tables['fields'];
+    }
+    
+}
\ No newline at end of file
-- 
cgit v1.2.3