/ -> dumptable.php
1 <?php
2
3 // dumptable.php - Creates the php code needed to re-create the table structure
4 // dumptable.php - author: Nico Stuurman<nicost@sourceforge.net>
5
6 /***************************************************************************
7 * Creates the php code needed to re-create the table structure *
8 * Takes 'tablename' as a get variable *
9 * *
10 * Copyright (c) 2002 by Nico Stuurman *
11 * ------------------------------------------------------------------------ *
12 * This program is free software; you can redistribute it and/or modify it *
13 * under the terms of the GNU General Public License as published by the *
14 * Free Software Foundation; either version 2 of the License, or (at your *
15 * option) any later version. *
16 \**************************************************************************/
17
18 require ('include.php');
19
20 printheader($httptitle,false);
21 navbar ($USER['permissions']);
22
23 if (!$USER['permissions'] & $SUPER) {
24 echo "<h3 align='center'>Sorry, this page is not for you.</h3>\n";
25 printfooter($db, $USER);
26 exit();
27 }
28
29
30 $tablename=$HTTP_GET_VARS['tablename'];
31 $tableid=get_cell($db,'tableoftables','id','tablename',$tablename);
32 if (!$tableid) {
33 echo "<h3>This script will create a php file with code that will re-create the table you selected in phplabware. Only the table structure, not its content will be re-created</h3>";
34 $r=$db->Execute('SELECT id,tablename FROM tableoftables');
35 if ($r) {
36 echo "<table align='center'>\n";
37 echo "<tr><td><h3>Select one of the following tables:</h3></td></tr>\n";
38 while ($r && !$r->EOF) {
39 echo "<tr><td><a href='dumptable.php?tablename=".$r->fields[1]."'>".$r->fields[1]."</a></td></tr>\n";
40 $r->MoveNext();
41 }
42 }
43 printfooter($d, $USER);
44 exit();
45 }
46
47 $table_desc=get_cell($db,'tableoftables','table_desc_name','tablename',$tablename);
48 $table_label=get_cell($db,'tableoftables','label','tablename',$tablename);
49 $table_plugin=get_cell($db,'tableoftables','plugin_code','tablename',$tablename);
50
51 // open file to write output to
52 $outfile=$system_settings['tmpdir']."/$tablename.php";
53 $fp=fopen($outfile,'w');
54 if (!$fp) {
55 echo "<h3 align='center'>Failed to open <i>$outfile</i> for output</h3>\n";
56 printfooter($db, $USER);
57 exit();
58 }
59
60 $header="<?php\n\n";
61 fwrite($fp,$header);
62
63 // Check for associated tables:
64 unset($r);
65 $r=$db->Execute("SELECT DISTINCT associated_table FROM $table_desc WHERE associated_column !='' AND datatype='table'");
66 while ($r && !$r->EOF) {
67 $asstable=get_cell($db,'tableoftables','tablename','id',$r->fields[0]);
68 echo "Please make sure that you also export table <i>$asstable</i>, and restore table $asstable before restoring table <i>$table_label</i>, since the latter containes links to the former.<br>";
69 fwrite($fp,'unset($asstableid);'."\n");
70 fwrite ($fp,'$asstableid=get_cell($db,"tableoftables","tablename","tablename","'.$asstable."\");\n");
71 fwrite ($fp,'if (!$asstableid) {
72 echo "Table <i>'.$asstable.'</i> is needed by table '.$tablename.'. Please restore table '.$asstable.' first.<br>";
73 printfooter();
74 exit();
75 }
76 ');
77 $r->MoveNext();
78 }
79
80 fwrite ($fp,'$newtableid=$db->GenID("tableoftables_gen_id_seq");'."\n");
81 fwrite ($fp,'$newtablename='."\"$tablename\";\n");
82 fwrite ($fp,'$newtablelabel='."\"$table_label\";\n");
83 fwrite ($fp,'for ($i=0;$i<$hownew;$i++) {$newtablelabel.="new";}'."\n");
84 fwrite ($fp, 'while (get_cell($db,"tableoftables","id","tablename",$newtablename)) {
85 $newtablename.="n";
86 $hownew++;
87 }
88 for ($i=0;$i<$hownew;$i++)
89 $newtablelabel.="new";
90 $newtableshortname=substr($newtablename,0,3).$newtableid;
91 $newtable_realname=$newtablename."_".$newtableid;
92 $newtable_desc_name=$newtable_realname."_desc";
93 $r=$db->Execute("INSERT INTO tableoftables (id,sortkey,tablename,shortname,display,permission,custom,real_tablename,table_desc_name,label,plugin_code) VALUES (\'$newtableid\',\'0\',\'$newtablename\',\'$newtableshortname\',\'Y\',\'Users\',NULL,\'$newtable_realname\',\'$newtable_desc_name\',\'$newtablelabel\',\''.$table_plugin.'\')");'."\n");
94
95 fwrite ($fp, '$rg=$db->Execute ("SELECT id FROM groups");
96 while (!$rg->EOF) {
97 $groupid=$rg->fields[0];
98 $db->Execute ("INSERT INTO groupxtable_display VALUES ($groupid,$newtableid)");
99 $rg->MoveNext();
100 }
101
102 ');
103
104 fwrite ($fp, 'if ($r) {
105 $rb=$db->Execute("CREATE TABLE $newtable_desc_name (
106 id int NOT NULL,
107 sortkey int,
108 label text,
109 columnname text,
110 display_table char(1),
111 display_record char(1),
112 required char(1),
113 type text,
114 datatype text,
115 associated_table text,
116 associated_column text,
117 associated_local_key text,
118 key_table text,
119 thumb_x_size int,
120 thumb_y_size int,
121 link_first text,
122 link_last text,
123 modifiable char(1) )");
124 if ($rb) {
125 ');
126 $desc_fields="sortkey,label,columnname,display_table,display_record,required,type,datatype,associated_table,associated_column,associated_local_key,key_table,thumb_x_size,thumb_y_size,link_first,link_last,modifiable";
127 $ADODB_FETCH_MODE=ADODB_FETCH_NUM;
128 $s=$db->Execute("SELECT $desc_fields FROM $table_desc");
129 while (!$s->EOF) {
130 fwrite ($fp,'$newid=$db->GenID("$newtable_desc_name"."_id");');
131 fwrite ($fp,'
132 $db->Execute("INSERT INTO $newtable_desc_name VALUES($newid');
133 // rewrite types to standard SQL
134 $value=$s->fields[6];
135 if (substr ($value,0,3)=='int')
136 $s->fields[6]='int';
137 //elseif (substr ($value,0,7)=="varchar")
138 for ($i=0; $i<sizeof($s->fields);$i++) {
139 $value=$s->fields[$i];
140 if (!$value)
141 fwrite ($fp,",NULL");
142 else
143 fwrite ($fp,",'$value'");
144 }
145 fwrite ($fp,')");
146 ');
147 // recreate pull down tables
148 if ($s->fields[7]=='pulldown' || $s->fields[7]=='mpulldown') {
149 fwrite($fp,'$ass_table=$newtable_realname."ass";
150 $id_ass=$db->GenId($ass_table,20);
151 $ass_table.="_$id_ass";
152 $db->Execute("CREATE TABLE $ass_table (
153 id int PRIMARY KEY,
154 sortkey int,
155 type text,
156 typeshort text)");
157 $db->Execute("UPDATE $newtable_desc_name SET associated_table=\'$ass_table\' WHERE id=$newid");
158 ');
159 if ($s->fields[7]=='mpulldown') {
160 fwrite ($fp,'$ask_table=$newtable_realname."ask_".$id_ass;
161 $db->Execute("CREATE TABLE $ask_table (
162 recordid int,
163 typeid int)");
164 $db->Execute("UPDATE $newtable_desc_name SET key_table=\'$ask_table\' WHERE id=$newid");
165 ');
166 }
167 }
168 // destroy links to tables, since those will fail
169 elseif ($s->fields[7]=='table') {
170 $asstable_name=get_cell($db,'tableoftables','tablename','id',$s->fields[8]);
171 $asstable_descname=get_cell($db,'tableoftables','table_desc_name','id',$s->fields[8]);
172 $asscolumnname=get_cell($db,$asstable_descname,'columnname','id',$s->fields[9]);
173 // also do associated local column
174 $asslocalcolumnname=get_cell($db,$table_desc,'columnname','id',$s->fields[10]);
175 // we need to write the whole description table and only then make the association to local columns:
176 if ($asslocalcolumnname) {
177 fwrite($fp,'$lid[]=$newid;
178 ');
179 fwrite($fp,'$lasscolumnname[]="'.$asslocalcolumnname."\";\n");
180 }
181
182 fwrite($fp,'$asstable_id=get_cell($db,"tableoftables","id","tablename","'.$asstable_name."\");\n");
183 fwrite($fp,'$asstable_desc=get_cell($db,"tableoftables","table_desc_name","tablename","'.$asstable_name."\");\n");
184 fwrite($fp,'$asscolumn_id=get_cell($db,"$asstable_desc","id","columnname","'.$asscolumnname."\");\n");
185 fwrite ($fp,'$db->Execute("UPDATE $newtable_desc_name SET associated_table=$asstable_id,associated_column=$asscolumn_id,key_table=NULL WHERE id=$newid");
186 ');
187 }
188 $s->MoveNext();
189 }
190 // if there were associated local columns in the description table, create those link now
191 fwrite ($fp,"\n".'for ($i=0;$i<sizeof($lid);$i++) {'."\n");
192 fwrite ($fp,' // find local associated column'."\n");
193 fwrite ($fp,' $r=$db->Execute("SELECT id FROM $newtable_desc_name WHERE columnname=\'{$lasscolumnname[$i]}\'");'."\n");
194 fwrite ($fp,' $db->Execute("UPDATE $newtable_desc_name SET associated_local_key='.'\'{$r->fields[0]}\' WHERE id=\'{$lid[$i]}\'");'."\n");
195 fwrite ($fp,"}\n");
196
197 fwrite ($fp,'// and finally create the table
198 $rc=$db->Execute(" CREATE TABLE $newtable_realname (id int NOT NULL
199 ');
200 $s->MoveFirst();
201 /*
202 $fieldname=$s->fields[2];
203 $fieldtype=$s->fields[6];
204 if (substr ($fieldtype,0,3)=='int')
205 $fieldtype='int';
206 if ($fieldname=='id')
207 $extra=' NOT NULL';
208 fwrite ($fp," $fieldname $fieldtype $extra");
209 $s->MoveNext();
210 */
211 while (!$s->EOF) {
212 unset ($extra);
213 $fieldname=$s->fields[2];
214 $fieldtype=$s->fields[6];
215 if (substr ($fieldtype,0,3)=='int')
216 $fieldtype='int';
217 if ($fieldname!='id')
218 fwrite ($fp,",\n $fieldname $fieldtype $extra");
219 $s->MoveNext();
220 }
221 fwrite ($fp,' ) ");
222 ');
223
224 $ADODB_FETCH_MODE=ADODB_FETCH_BOTH;
225 fwrite($fp,"
226 }
227 }
228 ");
229
230
231 fwrite ($fp,"?>");
232 fclose($fp);
233
234 echo "<h3>Wrote script to $outfile.</h3>";
235
236 printfooter($db, $USER);
237
238 ?>