5 require_once 'Pman.php';
6 class Pman_Core_UpdateDatabase extends Pman
9 static $cli_desc = "Update SQL - Beta";
17 $ff = HTML_FlexyFramework::get();
18 if (!empty($ff->cli)) {
23 parent::getAuth(); // load company!
24 $au = $this->getAuthUser();
25 if (!$au || $au->company()->comptype != 'OWNER') {
26 $this->jerr("Not authenticated", array('authFailure' => true));
28 $this->authUser = $au;
41 * imports SQL files from all DataObjects directories....
43 * except any matching /migrate/
48 $ff = HTML_Flexyframework::get();
50 $url = parse_url($ff->DB_DataObject['database']);
52 $this->{'import' . $url['scheme']}($url);
55 function importmysql($url)
58 // hide stuff for web..
60 require_once 'System.php';
61 $cat = System::which('cat');
62 $mysql = System::which('mysql');
64 $ar = $this->modulesList();
68 ' -h ' . $url['host'] .
69 ' -u' . escapeshellarg($url['user']) .
70 (!empty($url['pass']) ? ' -p' . escapeshellarg($url['pass']) : '') .
71 ' ' . basename($url['path']);
72 echo $mysql_cmd . "\n" ;
79 $fd = $this->rootDir. "/Pman/$m/DataObjects";
81 foreach(glob($fd.'/*.sql') as $fn) {
84 if (preg_match('/migrate/i', basename($fn))) { // skip migration scripts at present..
87 // .my.sql but not .pg.sql
88 if (preg_match('/#.[a-z]{2}\.sql#i', basename($bfn))
89 && !preg_match('/#\.my\.sql#i', basename($bfn))
90 ) { // skip migration scripts at present..
93 $cmd = "$mysql_cmd -f < " . escapeshellarg($fn) ;
95 echo $cmd. ($this->cli ? "\n" : "<BR>\n");
107 * postgresql import..
109 function importpgsql($url)
112 // hide stuff for web..
114 require_once 'System.php';
115 $cat = System::which('cat');
116 $psql = System::which('psql');
118 $ar = $this->modulesList();
119 if (!empty($url['pass'])) {
120 putenv("PGPASSWORD=". $url['pass']);
124 ' -h ' . $url['host'] .
125 ' -U' . escapeshellarg($url['user']) .
126 ' ' . basename($url['path']);
127 echo $psql_cmd . "\n" ;
134 $fd = $this->rootDir. "/Pman/$m/DataObjects";
136 foreach(glob($fd.'/*.sql') as $bfn) {
139 if (preg_match('/migrate/i', basename($bfn))) { // skip migration scripts at present..
142 if (preg_match('#\.[a-z]{2}\.sql#i', basename($bfn))
143 && !preg_match('#\.pg\.sql#i', basename($bfn))
144 ) { // skip migration scripts at present..
147 // files ending in .pg.sql are native postgres files..
148 $fn = preg_match('#\.pg\.sql$#', basename($bfn)) ? false : $this->convertToPG($bfn);
150 $cmd = "$psql_cmd -f " . escapeshellarg($fn ? $fn : $bfn) . ' 2>&1' ;
152 echo "$bfn: $cmd ". ($this->cli ? "\n" : "<BR>\n");
165 * simple regex based convert mysql to pgsql...
167 function convertToPG($src)
169 $fn = $this->tempName('sql');
171 $ret = array( ); // pad it a bit.
172 $extra = array("", "" );
175 foreach(file($src) as $l) {
178 if (!strlen($l) || $l[0] == '#') {
182 if (preg_match('#create\s+table\s+([a-z0-9_]+)#i', $l, $m)) {
184 // $extra[] = "drop table {$tbl};";
187 if ($tbl && preg_match('#auto_increment#i', $l, $m)) {
188 $l = preg_replace('#auto_increment#i', "default nextval('{$tbl}_seq')", $l);
189 $extra[] = "create sequence {$tbl}_seq;";
193 if (preg_match('#alter\s+table\s+([a-z0-9_]+)\s+add\s+index\s+([^(]+)(.*)$#i', $l, $m)) {
194 $l = "CREATE INDEX {$m[1]}_{$m[2]} ON {$m[1]} {$m[3]}";
196 // ALTER TABLE core_event_audit ADD INDEX looku
200 $l = preg_replace('#int\([0-9]+\)#i', 'INT', $l);
202 $l = preg_replace('# datetime #i', ' TIMESTAMP WITHOUT TIME ZONE ', $l);
203 $l = preg_replace('# blob #i', ' TEXT ', $l);
204 $l = preg_replace('# longtext #i', ' TEXT ', $l);
205 //$l = preg_match('#int\([0-9]+\)#i', 'INT', $l);
216 $ret = array_merge($extra,$ret);
218 //echo implode("\n", $ret); //exit;
219 file_put_contents($fn, implode("\n", $ret));