skeleton commit; based on an anom project

1 files changed, 172 insertions, 0 deletions

diff --git a/public/app/models/Jorge.php b/public/app/models/Jorge.php
new file mode 100644
index 0000000..5d79007
--- /dev/null
+++ b/public/app/models/Jorge.php
@@ -0,0 +1,172 @@
+<?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'] = [
+
+            'lesson' => [
+                'course' => 'course.id = lesson.course_id',
+                'lesson_media' => 'lesson_media.lesson_id = lesson.id',
+                'lesson_privilege' => 'lesson_privilege.lesson_id = lesson.id'
+            ],
+
+            'page' => [
+                'page_media' => 'page_media.page_id = page.id'
+            ],
+
+            'user' => [
+                'user_privilege' => 'user_privilege.user_id = user.id'
+            ],
+
+            'privilege' => [
+                'user_privilege' => 'user_privilege.privilege_id = privilege.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', '(', ')', 'CONSTRAINT', 'UNIQUE'];    // 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 sub-parenthesis
+            // ex: 'decimal(18, 2)' turns to 'decimal(18: 2)'
+            // or: 'PRIMARY KEY (id1, id2)' turns to 'PRIMARY KEY (id1: id2)'
+            $sentences = preg_replace(
+                '/\\(([0-9a-zA-Z_`]*)[ ,]([0-9a-zA-Z_` ]*)\\)/',
+                '($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