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..
88 $cmd = "$mysql_cmd -f < " . escapeshellarg($fn) ;
90 echo $cmd. ($this->cli ? "\n" : "<BR>\n");
102 * postgresql import..
104 function importpgsql($url)
107 // hide stuff for web..
109 require_once 'System.php';
110 $cat = System::which('cat');
111 $psql = System::which('psql');
113 $ar = $this->modulesList();
114 if (!empty($url['pass'])) {
115 putenv("PGPASSWORD=". $url['pass']);
119 ' -h ' . $url['host'] .
120 ' -U' . escapeshellarg($url['user']) .
121 ' ' . basename($url['path']);
122 echo $psql_cmd . "\n" ;
129 $fd = $this->rootDir. "/Pman/$m/DataObjects";
131 foreach(glob($fd.'/*.sql') as $fn) {
134 if (preg_match('/migrate/i', basename($fn))) { // skip migration scripts at present..
137 $fn = $this->convertToPG($fn);
139 $cmd = "$psql_cmd -f " . escapeshellarg($fn) ;
141 echo $cmd. ($this->cli ? "\n" : "<BR>\n");
151 * simple regex based convert mysql to pgsql...
153 function convertToPG($src)
155 $fn = $this->tempName('.sql');
157 $ret = array( ); // pad it a bit.
158 $extra = array("", "" );
161 foreach(file($src) as $l) {
164 if (!strlen($l) || $l[0] == '#') {
168 if (preg_match('#create\s+table\s+([a-z0-9_]+)#i', $l, $m)) {
170 // $extra[] = "drop table {$tbl};";
173 if ($tbl && preg_match('#auto_increment#i', $l, $m)) {
174 $l = preg_replace('#auto_increment#i', "default nextval('{$tbl}_seq')", $l);
175 $extra[] = "create sequence {$tbl}_seq;";
179 if (preg_match('#alter\s+table\s+([a-z0-9_]+)\s+add\s+index\s+([^(]+)(.*)$#i', $l, $m)) {
180 $l = "CREATE INDEX {$m[1]}_{$m[2]} ON {$m[1]} {$m[3]}";
182 // ALTER TABLE core_event_audit ADD INDEX looku
186 $l = preg_replace('#int\([0-9]+\)#i', 'INT', $l);
188 $l = preg_replace('# datetime #i', ' TIMESTAMP WITHOUT TIME ZONE ', $l);
189 $l = preg_replace('# blob #i', ' TEXT ', $l);
190 $l = preg_replace('# longtext #i', ' TEXT ', $l);
191 //$l = preg_match('#int\([0-9]+\)#i', 'INT', $l);
202 $ret = array_merge($extra,$ret);
204 echo implode("\n", $ret); //exit;
205 file_put_contents($fn, implode("\n", $ret));