· 5 years ago · Mar 12, 2020, 07:34 AM
1<?php
2
3//read schema file
4$filename = "schema2.xsd";
5$fileContents = file_get_contents($filename);
6
7//create database query
8preg_match('/[a-z]+/', $filename, $schema);
9
10$createDatabase = "";
11$createDatabase = "CREATE DATABASE IF NOT EXISTS ";
12$createDatabase .= $schema[0];
13$createDatabase .= ";";
14$createDatabase .= "\n";
15
16//display input schema file
17echo "Input Schema: ";
18echo "<br>";
19
20$lineBreakPattern = "/[\s,]+/";
21$lines = preg_split($lineBreakPattern, $fileContents);
22
23foreach($lines as $line){
24 echo $line . "<br>";
25}
26
27/***/
28$pattern = '/<xs:element\sname=\"[a-zA-Z]+\">/';
29$str = "";
30
31foreach ($lines as $line)
32{
33$str .= $line;
34}
35
36echo "<br><br>";
37echo "**************************************";
38var_dump($str);
39//echo htmlentities($str);
40echo "**************************************";
41echo "<br><br>";
42
43echo preg_match_all($pattern, $str, $tableMatches) ? $tableMatches[1] : 'no match';
44
45echo "FOR CREATE TABLE:";
46print_r($tableMatches);
47/**/
48
49echo "<br>";
50
51//extract table name
52$tablePattern = "/name/";
53$i = 0;
54
55foreach($lines as $line){
56 if($i == 0){
57
58 if(preg_match($tablePattern, $line)){
59 $tableName = $line;
60 $i = 1;
61 }
62 }
63 }
64
65preg_match('/"([^"]+)"/', $tableName, $matches);
66 $table = $matches[1];
67
68 //extract table attributes
69$attrPattern = "/name=\"[a-z]+\"\stype=\"[a-zA-Z]+:[a-zA-Z]+\"/";
70echo preg_match_all($attrPattern, $fileContents, $attr) ? $attr[0] : 'no match';
71
72for($c=0; $c < sizeof($attr,1); $c++)
73{
74 $attributes = $attr[0];
75}
76
77$attributesStr = "";
78
79for($d=0; $d < sizeof($attributes); $d++)
80{
81$attributesStr .= $attributes[$d];
82$attributesStr .= " ";
83}
84
85//extract attribute names and types
86$attrNamePattern = "/name=\"[a-zA-z]+\"/";
87
88for($i=0; $i < strlen($attributesStr); $i++)
89{
90 preg_match_all($attrNamePattern, $attributesStr, $attrNames);
91}
92
93$attrNameType = "";
94
95for($j=0; $j < strlen($attributesStr); $j++)
96{
97 $attrNameType = preg_split("/\s/", $attributesStr);
98}
99
100$attrStr = "";
101$typeStr = "";
102
103for($k=0; $k < count($attrNameType); $k++)
104{
105 if($k % 2 == 0)
106 {
107 $attrStr .= $attrNameType[$k];
108
109 preg_match('/"([^"]+)"/', $attrStr, $cNames);
110
111 $attrNameType[$k] = $cNames[1];
112 $attrStr = "";
113 }
114
115 else
116 {
117 $typeStr .= $attrNameType[$k];
118
119 preg_match('/"([^"]+)"/', $typeStr, $tNames);
120
121 $attrNameType[$k] = $tNames[1];
122 $typeStr = "";
123 }
124}
125
126echo "<br><br>";
127
128echo "Attributes Name and Types Array: ";
129var_dump($attrNameType);
130
131echo "<br><br>";
132
133//replace XML types with SQL types
134for($l=0; $l < count($attrNameType); $l++)
135{
136 if($l % 2 != 0 )
137 {
138 if($attrNameType[$l] == "xs:positiveInteger")
139 $attrNameType[$l] = "INT";
140
141 if($attrNameType[$l] == "xs:decimal")
142 $attrNameType[$l] = "FLOAT";
143
144 if($attrNameType[$l] == "xs:string")
145 $attrNameType[$l] = "VARCHAR(255)";
146
147 if($attrNameType[$l] == "xs:boolean")
148 $attrNameType[$l] = "BOOLEAN";
149
150 if($attrNameType[$l] == "xs:date")
151 $attrNameType[$l] = "DATE";
152
153 if($attrNameType[$l] == "xs:time")
154 $attrNameType[$l] = "TIME";
155 }
156}
157
158echo "Conversion to SQL Types: ";
159var_dump($attrNameType);
160
161//handling required attributes of table
162$reqAttrPattern = '/name=\"[a-z]+\"\stype=\"[a-zA-Z]+:[a-zA-Z]+\"\suse=\"required\"/';
163
164preg_match($reqAttrPattern, $fileContents, $reqAttr);
165
166$reqAttrStr = "";
167
168foreach($reqAttr as $rA)
169{
170 $reqAttrStr .= $rA;
171}
172/**/
173echo "<br><br>";
174echo "reqAttrStr: ";
175var_dump($reqAttrStr);
176echo "<br><br>";
177/** */
178
179$reqAttrSplitterPattern = '/\s/';
180$reqAttrArray = preg_split($reqAttrSplitterPattern,$reqAttrStr);
181
182$unquoteStr = "";
183
184for($n=0; $n < count($reqAttrArray); $n++)
185{
186 $unquoteStr .= $reqAttrArray[$n];
187 preg_match('/"([^"]+)"/', $unquoteStr, $unquote);
188
189 $reqAttrArray[$n] = $unquote[1];
190 $unquoteStr = "";
191}
192
193foreach($reqAttrArray as $rAA)
194{
195if($rAA == "required")
196$attrNameType[count($attrNameType)-2] .= " NOT NULL";
197}
198
199echo "<br><br>";
200
201//making the create query
202$createQuery = "";
203
204echo "CREATE TABLE " . $schema[0] . "." . $table . "(";
205$createQuery .= "CREATE TABLE " . $schema[0] . "." . $table . "(";
206
207echo "<br>";
208$createQuery .= "\n";
209
210for($m=0; $m < count($attrNameType); $m++)
211{
212 if($m % 2 == 0)
213 {
214 echo $attrNameType[$m];
215 $createQuery .= $attrNameType[$m];
216 }
217
218 else
219 {
220 echo " ";
221 $createQuery .= " ";
222
223 if($m != (count($attrNameType)-2))
224 {
225 echo $attrNameType[$m] . ",";
226 $createQuery .= $attrNameType[$m] . ",";
227 }
228 else
229 {
230 echo $attrNameType[$m];
231 $createQuery .= $attrNameType[$m];
232 }
233 echo "<br>";
234 $createQuery .= "\n";
235 }
236}
237
238echo ");";
239$createQuery .= ");";
240
241//writing the SQL schema to file
242$sqlFile = fopen("schema2.sql", "w") or die("Unable to open file!");
243
244fwrite($sqlFile, $createDatabase);
245fwrite($sqlFile, $createQuery);
246
247fclose($sqlFile);
248
249?>