1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
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'];
}
}
|
