%s |
%s |
MSGS = %s'."\n", print_r($msgs)); foreach($msgs as $msg){ printf('<%s>%s'."\n", $types[$type], $msg, $types[$type]); } //$_SESSION[$type] = array(); } } public function notice($msg=null){ $this->msg('notices', $msg); } public function warning($msg=null){ $this->msg('warnings', $msg); } public function error($msg=null){ $this->msg('errors', $msg); } public function sql($sql=null){ if (!empty($sql)) { $_SESSION['sqls'][] = $sql; } else if (!empty($_SESSION['sqls'])) { printf('
'."\n");
foreach($_SESSION['sqls'] as $sql){
printf('%s'."\n", $sql); } printf(' |
CONNECTION = %s'."\n", print_r($connection)); foreach($connection as $key => $val){ $_SESSION[$key] = $val; } //printf('
SESSION = %s'."\n", print_r($_SESSION));exit(); return(true); } public function getDrivers() { $possible_drivers = array ( 'mysql', 'sqlite', 'cubrid', 'firebird', 'ibm', 'informix', 'mssql', 'oci', 'oci8', 'odbc', 'pqsql', 'snowflake', '4d', ); $available_drivers = \PDO::getAvailableDrivers(); foreach($possible_drivers as $driver) { $drivers[$driver] = in_array($driver, $available_drivers) ? 1 : 0; } //printf('
DRIVERS = %s
RECS = %s'."\n", print_r($recs));exit; return($recs); } public function getDriver(){ $driver = !empty($_SESSION['driver']) ? $_SESSION['driver'] : ''; //$this->debug(__METHOD__, 'driver', $driver, 1); return($driver); } public function getFields($database, $table){ $fields = array(); $this->connect($database); $sql = "SELECT * FROM `$table` LIMIT 1"; $this->sql($sql); $recs = $this->DBO->query($sql); $field = array(); foreach($recs[0] as $key => $val){ $fields[] = $key; } return($fields); } public function getColumns($database, $table){ if ($this->driver == 'sqlite') { $sql = "PRAGMA table_info(`$this->table`);"; $this->sql($sql); $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $columns = array(); //if (!empty($data) || count($data) != 0) { if (is_string($data)) return($data); $columns = array(); foreach($data as $rec) { $column = array(); $column['column'] = $rec['name']; $column['type'] = $rec['type']; $column['key'] = $rec['pk'] == '1' ? 'PRI' : ''; $column['null'] = $rec['notnull'] == '0' ? 'Yes' : 'No'; $columns[] = $column; } } else if ($this->driver == 'mysql') { //$sql = "SHOW COLUMNS FROM `$this->table`"; $sql = "SELECT * FROM information_schema.columns WHERE table_schema = '$this->database' and table_name = '$this->table'"; $this->debug(__METHOD__, 'sql', $sql); $this->sql($sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $columns = array(); //if (!empty($data) || count($data) != 0) { if (is_string($data)) return($data); $columns = array(); foreach($data as $rec) { $column = array(); $column['column'] = $rec['COLUMN_NAME']; $column['type'] = $rec['COLUMN_TYPE']; $types = explode(' ', trim($column['type'])); $column['type'] = $types[0]; //$column['type'] .= !empty($rec['NUMERIC_PRECISION']) ? '('.$rec['NUMERIC_PRECISION'].')' : ''; $column['key'] = $rec['COLUMN_KEY']; $column['extra'] = $rec['EXTRA']; $column['extra'] = str_replace('auto_increment', 'AI', $column['extra']); $column['extra'] = str_replace('DEFAULT_GENERATED', 'DG', $column['extra']); $column['null'] = $rec['IS_NULLABLE'] == 'NO' ? 'No' : 'Yes'; $columns[] = $column; } } else { } return($columns); } public function getColumn($database, $table, $column){ if ($this->driver == 'sqlite') { $sql = "PRAGMA table_info(`$this->table`);"; $this->sql($sql); $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $columns = array(); //if (!empty($data) || count($data) != 0) { if (is_string($data)) return($data); foreach($data as $rec) { $col = array(); $col['column'] = $rec['name']; $col['type'] = $rec['type']; $col['key'] = $rec['pk'] == '1' ? 'PRI' : ''; $col['null'] = $rec['notnull'] == '0' ? 'Yes' : 'No'; $col['default'] = $rec['dflt_value']; if ($col['column'] == $column) { $column = $col; break; } } } else { //$sql = "SHOW COLUMNS FROM `$this->table`"; //$sql = "SELECT * FROM information_schema.columns WHERE table_schema = '$this->database' and table_name = '$this->table'"; $sql = "SELECT * FROM information_schema.columns WHERE `TABLE_SCHEMA` = '$database' AND `TABLE_NAME` = '$table' AND `COLUMN_NAME` = '$column';"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); if (is_string($data)) return($data); $rec = $data[0]; $column = array(); $column['column'] = $rec['COLUMN_NAME']; $column['type'] = $rec['COLUMN_TYPE']; $types = explode(' ', trim($column['type'])); $column['type'] = $types[0]; $column['key'] = $rec['COLUMN_KEY']; $column['attr'] = $rec['COLUMN_TYPE']; $attrs = explode(' ', trim($column['attr'])); $column['attr'] = !empty($types[1]) ? $types[1] : ''; $column['null'] = $rec['IS_NULLABLE'] == 'NO' ? 'No' : 'Yes'; $column['default'] = $rec['COLUMN_DEFAULT']; $column['extra'] = $rec['EXTRA']; $column['collation'] = $rec['COLLATION_NAME']; $column['charset'] = $rec['CHARACTER_SET_NAME']; $column['comment'] = $rec['COLUMN_COMMENT']; } return($column); } public function getIndexes($database, $table){ if ($this->driver == 'sqlite') { $sql = "SELECT * FROM sqlite_master WHERE type = 'index' AND tbl_name = '$table'"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $indexes = array(); foreach($data as $rec) { $index = array(); $index['column'] = $rec['name']; //$index['table'] = $rec['tbl_name']; $index['sql'] = $rec['sql']; $indexes[] = $index; } } else { $sql = "SHOW INDEXES FROM `$table`"; $this->debug(__METHOD__, 'sql', $sql); //$this->sql($sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); if (is_string($data)) return($data); $indexes = array(); foreach($data as $rec) { $index = array(); $index['index'] = $rec['Key_name']; $index['column'] = $rec['Column_name']; //$index['table'] = $rec['Table']; $index['type'] = $rec['Index_type']; $index['uniq'] = $rec['Non_unique'] == '0' ? 'Yes' : 'No'; //$index['packed'] = !empty($rec['Packed']) ? 'Yes' : 'No'; //$index['card'] = $rec['Cardinality']; //$index['coll'] = $rec['Collation']; $index['null'] = !empty($rec['Null']) ? 'Yes' : 'No'; //$index['visible'] = $rec['Visible'] == 'YES' ? 'Yes' : 'No'; //$index['comment'] = $rec['Comment']; $indexes[] = $index; } } return($indexes); } public function getIndex($database, $table, $column){ $this->debug(__METHOD__, 'database', $database); $this->debug(__METHOD__, 'table', $table); $this->debug(__METHOD__, 'column', $column); if ($this->driver == 'sqlite') { $sql = "SELECT * FROM sqlite_master WHERE type = 'index' AND tbl_name = '$table'"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $index = array(); foreach($data as $rec){ if ($rec['name'] == $column) { $index = array(); $index['index'] = $rec['name']; $index['sql'] = $rec['sql']; break; } } } else { $sql = "SHOW INDEXES FROM `$table`"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $index = array(); foreach($data as $rec){ if ($rec['Key_name'] == $column) { $index = array(); $index['index'] = $rec['Key_name']; $index['column'] = $rec['Column_name']; //$index['table'] = $rec['Table']; $index['type'] = $rec['Index_type']; $index['uniq'] = $rec['Non_unique'] == '0' ? 'Yes' : 'No'; $index['packed'] = !empty($rec['Packed']) ? 'Yes' : 'No'; $index['card'] = $rec['Cardinality']; $index['coll'] = $rec['Collation']; $index['null'] = !empty($rec['Null']) ? 'Yes' : 'No'; //$index['visible'] = $rec['Visible'] == 'YES' ? 'Yes' : 'No'; $index['comment'] = $rec['Comment']; break; } } } $this->debug(__METHOD__, 'index', $index); return($index); } public function getConnections(){ $Config = new Config(); $connections = $Config->get('connections'); $this->debug(__METHOD__, 'connections', $connections); return($connections); } public function getRequest($var=null, $val=null) { if (empty($var)) { //printf('
%s |
getResults.database = %s'."\n", print_r($database));exit; if (!empty($database)) { $this->connect($database); } else { $this->connect(); } $this->debug(__METHOD__, 'sql', $sql); $results = $this->DBO->query($sql); $this->debug(__METHOD__, 'results', $results); return($results); } public function getSession(){ $this->driver = !empty($_SESSION['driver']) ? $_SESSION['driver'] : ''; $this->dbpath = !empty($_SESSION['dbpath']) ? $_SESSION['dbpath'] : ''; $this->dbhost = !empty($_SESSION['dbhost']) ? $_SESSION['dbhost'] : ''; $this->dbport = !empty($_SESSION['dbport']) ? $_SESSION['dbport'] : ''; $this->charset = !empty($_SESSION['charset']) ? $_SESSION['charset'] : ''; $this->dbuser = !empty($_SESSION['dbuser']) ? $_SESSION['dbuser'] : ''; $this->dbpass = !empty($_SESSION['dbpass']) ? $_SESSION['dbpass'] : ''; //$this->database = !empty($_SESSION['database']) ? $_SESSION['database'] : ''; $this->available = !empty($_SESSION['available']) ? $_SESSION['available'] : array(); $this->forbidden = !empty($_SESSION['forbidden']) ? $_SESSION['forbidden'] : array(); } public function getStatus($results, $notice, $page, $database=null, $table=null){ /* printf('
RESULTS = %s'."\n",print_r($results)); printf('
NOTICE = %s'."\n",print_r($notice)); printf('
PAGE = %s'."\n",print_r($page)); printf('
DATABASE = %s'."\n",print_r($database)); printf('
TABLE = %s'."\n",print_r($table)); */ if (!is_string($results)) { $this->notice($notice); //$this->debug(__METHOD__, 'SESSION1', $_SESSION); //exit; $this->redirect($page, $database, $table); return(true); } else { $this->error($results); } return(false); } public function getRows($driver, $database, $table){ $this->debug(__METHOD__, 'driver', $driver, 1); $this->debug(__METHOD__, 'database', $database, 1); $this->debug(__METHOD__, 'table', $table, 1); //if ($driver == 'sqlite') { //} else { $sql = "SELECT COUNT(*) AS `count` FROM `$table`"; $this->debug(__METHOD__, 'sql', $sql); //printf('
SQL = %s'."\n",print_r($sql)); $count = $this->DBO->query($sql); //printf('
COUNT = %s'."\n",print_r($count)); $this->debug(__METHOD__, 'count', $count); $rows = !empty($count[0]['count']) ? $count[0]['count'] : 0; $this->debug(__METHOD__, 'rows', $rows); //} return($rows); } public function getTables111($driver, $database){ //$this->debug(__METHOD__, 'driver', $driver, 1); //$this->debug(__METHOD__, 'database', $database, 1); if ($driver == 'mysql') { //$sql = "SELECT table_schema as 'database', COUNT(*) as 'tables'"; //$sql.= " FROM information_schema.tables"; //$sql.= " WHERE table_type = 'BASE TABLE'"; //$sql.= " GROUP BY table_schema"; //$sql.= " ORDER BY tables DESC"; $sql = "SELECT A.TABLE_NAME AS `table`"; $sql.= ", A.TABLE_ROWS AS `rows`"; //$sql = "SELECT *"; $sql.= " FROM information_schema.TABLES A"; $sql.= " WHERE A.TABLE_SCHEMA = '$database'"; //$sql.= " WHERE A.TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys')"; //$sql.= " AND A.TABLE_TYPE != 'VIEW'"; //$sql.= " GROUP BY A.SCHEMA_NAME"; //$sql.= " ORDER BY `$order_by` $order"; //$sql.= " ORDER BY A.TABLE_NAME ASC"; $this->connect($database); //$this->debug(__METHOD__, 'sql', $sql, 1); $tables = $this->DBO->query($sql); //$this->debug(__METHOD__, 'data', $tables, 1); } else if ($driver == 'mysql1') { $this->connect($database); $sql = "SELECT IFNULL(table_schema,'Total') 'Database',TableCount"; $sql.= " FROM information_schema.tables"; $sql.= " WHERE table_schema NOT IN ('information_schema','mysql')"; $sql.= " GROUP BY table_schema WITH ROLLUP) A"; $sql.= " ORDER BY TableCount DESC"; $this->debug(__METHOD__, 'sql', $sql,1); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data,1); } else if ($driver == 'mysql2') { $this->connect($database); $sql = "SELECT * FROM information_schema.tables"; $sql.= " WHERE TABLE_TYPE = 'BASE TABLE'"; if (!empty($database)) { $sql.= " AND TABLE_SCHEMA = '$database';"; } $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $tables = array(); foreach($data as $rec){ $table = array(); $table['table'] = $rec['TABLE_NAME']; $table['database'] = $rec['TABLE_SCHEMA']; $table['created'] = $rec['CREATE_TIME']; $table['engine'] = $rec['ENGINE']; $table['collation'] = $rec['TABLE_COLLATION']; $table['auto_increment'] = $rec['AUTO_INCREMENT']; $table['comment'] = $rec['TABLE_COMMENT']; $tables[] = $table; } } else if ($driver == 'sqlite') { $this->connect($database); $sql = "SELECT * FROM sqlite_master WHERE `type` = 'table' AND name NOT LIKE 'sqlite_%'"; $this->sql($sql); //printf('
SQL = %s'."\n",print_r($sql)); $recs = $this->DBO->query($sql); //printf('
RECS = %s'."\n",print_r($recs)); $tables = array(); foreach($recs as $rec) { if ($rec['name'] == 'sqlite_sequence') continue; $table = array(); $table['table'] = $rec['name']; $table['sql'] = $rec['sql']; $tables[] = $table; } } else { $sql = "SHOW TABLES FROM `$database`"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $tables = array(); foreach($data as $rec){ $this->debug(__METHOD__, 'rec', $rec); $tables[] = $table; } } $this->debug(__METHOD__, 'tables', $tables); return($tables); } public function getTrigger($driver, $database, $trigger){ $this->debug(__METHOD__, 'driver', $driver); $this->debug(__METHOD__, 'database', $database); //$sql = "SHOW TRIGGERS FROM `$database`"; $sql = "SHOW TRIGGERS FROM `$database` WHERE `Trigger` = '$trigger'"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); return($data); } public function getTriggers($driver, $database){ if ($driver == 'sqlite') { $data = array(); } else { $this->debug(__METHOD__, 'driver', $driver); $this->debug(__METHOD__, 'database', $database); $sql = "SHOW TRIGGERS FROM `$database`"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); } return($data); } public function getEvent($driver, $database, $event){ $this->debug(__METHOD__, 'driver', $driver); $this->debug(__METHOD__, 'database', $database); $this->debug(__METHOD__, 'event', $event); //$sql = "SHOW EVENTS"; //$sql = "SHOW EVENTS FROM `$database`"; $sql = "SHOW EVENTS FROM `$database` WHERE `Name` = '$event'"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); return($data); } public function getEvents($driver, $database){ if ($driver == 'sqlite') { $data = array(); } else { $this->debug(__METHOD__, 'driver', $driver); $this->debug(__METHOD__, 'database', $database); //$sql = "SHOW EVENTS FROM `$database`"; $sql = "SHOW EVENTS"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); } return($data); } public function getRoutine($driver, $database, $routine){ $this->debug(__METHOD__, 'driver', $driver); $this->debug(__METHOD__, 'database', $database); $this->debug(__METHOD__, 'routine', $routine); $sql = "SHOW PROCEDURE STATUS WHERE Db = '$database' AND Name = '$routine';"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); return($data); } public function getRoutines($driver, $database){ if ($driver == 'sqlite') { $data = array(); } else { $this->debug(__METHOD__, 'driver', $driver); $this->debug(__METHOD__, 'database', $database); //$sql = "SHOW PROCEDURE STATUS FROM `$database`"; $sql = "SHOW PROCEDURE STATUS WHERE Db = '$database';"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); } return($data); } public function getView($driver, $database, $view){ $this->debug(__METHOD__, 'driver', $driver); $this->debug(__METHOD__, 'database', $database); $this->debug(__METHOD__, 'view', $view); if ($driver == 'sqlite') { //$sql = "SELECT * FROM sqlite_master WHERE `type` = 'view' AND name NOT LIKE 'sqlite_%'"; $sql = "SELECT * FROM sqlite_master WHERE `type` = 'view'"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $record = array(); foreach($data as $rec) { //if ($rec['name'] == 'sqlite_sequence') continue; $record['view'] = $rec['name']; $record['sql'] = $rec['sql']; } } else { /* $sql = "SELECT * FROM information_schema.views"; $sql.= " WHERE TABLE_SCHEMA = '$database'"; $sql.= " AND TABLE_NAME = '$view'"; */ $sql = "SHOW CREATE TABLE `$database`.`$view`"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $rec = $data[0]; $record = array(); $record['view'] = !empty($rec['View']) ? $rec['View'] : ''; $record['sql'] = !empty($rec['Create View']) ? $rec['Create View'] : ''; } $this->debug(__METHOD__, 'record', $record); return($record); } public function getViews111($driver, $database){ $this->debug(__METHOD__, 'driver', $driver, 1); $this->debug(__METHOD__, 'database', $database, 1); if ($driver == 'sqlite') { $sql = "SELECT * FROM sqlite_master WHERE `type` = 'view'"; $this->sql($sql); $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $views = array(); foreach($data as $rec){ if ($rec['name'] == 'sqlite_sequence') continue; $view = array(); $view['view'] = $rec['name']; $view['sql'] = $rec['sql']; $views[] = $view; } } else { //$sql = "SHOW FULL TABLES WHERE Table_Type LIKE 'VIEW';"; $sql = "SELECT * FROM information_schema.tables"; $sql.= " WHERE TABLE_TYPE = 'VIEW'"; $sql.= " AND TABLE_SCHEMA != 'sys'"; if (!empty($database)) { $sql.= " AND TABLE_SCHEMA = '$database'"; } $sql.= " ORDER BY TABLE_NAME ASC"; $this->debug(__METHOD__, 'sql', $sql); $data = $this->DBO->query($sql); $this->debug(__METHOD__, 'data', $data); $views = array(); foreach($data as $rec){ $view = array(); $view['view'] = $rec['TABLE_NAME']; //$view['database'] = $rec['TABLE_SCHEMA']; $view['created'] = $rec['CREATE_TIME']; $view['comment'] = $rec['TABLE_COMMENT']; $views[] = $view; } } $this->debug(__METHOD__, 'views', $views); return($views); } public function createDatabase($database){ $this->connect(); $sql = sprintf("CREATE DATABASE `%s`;", $database); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function copyDatabase($database1, $database2){ $this->debug(__METHOD__, 'database1', $database1); $this->debug(__METHOD__, 'database2', $database2); $tables = $this->getTables($this->driver, $database1); $this->debug(__METHOD__, 'tables', $tables); foreach($tables as $table){ $sql = sprintf("CREATE TABLE %s.%s AS SELECT * FROM %s.%s;", $database2, $table['table'], $database1, $table['table']); $this->sql($sql); $status = $this->DBO->exec($sql); } return(true); } public function cloneDatabase($database1, $database2){ $tables = $this->getTables($this->driver, $database1); foreach($tables as $table){ $sql = sprintf("CREATE TABLE %s.%s LIKE %s.%s;", $database2, $table['table'], $database1, $table['table']); $this->sql($sql); $status = $this->DBO->exec($sql); } return(true); } public function deleteDatabase($database1){ $this->connect(); $sql = sprintf("DROP DATABASE `%s`;", $database1); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function renameDatabase($database1, $database2){ $this->connect(); $sql = sprintf("CREATE DATABASE `%s`;", $database2); $this->debug(__METHOD__, 'sql', $sql); $this->sql($sql); $status = $this->DBO->exec($sql); $this->debug(__METHOD__, 'status', $status); $sql = sprintf("SHOW DATABASES LIKE '%s';", $database2); //$this->sql($sql); $data = $this->DBO->query($sql); if (!count($data)) { $status = "Failed to create new database!"; //$this->error($status); return($status); } $tables = $this->getTables($this->driver, $database1); foreach($tables as $table){ $sql = sprintf("RENAME TABLE %s.%s TO %s.%s;", $database1, $table['table'], $database2, $table['table']); $this->sql($sql); $status = $this->DBO->exec($sql); if (is_string($status)) { return($status); } } $sql = sprintf("DROP DATABASE `%s`;", $database1); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } /* CREATE EVENT IF NOT EXISTS test_event_01 ON SCHEDULE AT CURRENT_TIMESTAMP DO INSERT INTO messages(message,created) VALUES('Test MySQL Event 1',NOW()); */ public function createEvent($driver, $database, $event){ $this->debug(__METHOD__, 'driver', $driver); $this->debug(__METHOD__, 'database', $database); $this->debug(__METHOD__, 'event', $event); if ($this->driver == 'sqlite'){ //$sql = sprintf("CREATE INDEX `%s` ON %s (`%s`);", $index, $table, $column); } else { $sql = sprintf("CREATE EVENT `%s` ON SCHEDULE AT '%s' DO %s;", $event['name'], $event['starts'], $event['definition']); } $this->debug(__METHOD__, 'sql', $sql); $status = $this->DBO->exec($sql); $this->debug(__METHOD__, 'status', $status); return($status); } public function deleteEvent($database, $event){ $sql = sprintf("DROP EVENT `%s`;", $event['name']); $this->debug(__METHOD__, 'sql', $sql); $status = $this->DBO->exec($sql); return($status); } public function renameEvent($database, $event1, $event2){ $status = $this->createEvent($database, $event2); if (is_string($status)) return($status); $status = $this->deleteEvent($database, $event1); return($status); } public function createTable($table){ $sql = sprintf("CREATE TABLE `%s` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(80) NOT NULL,`date` DATE);", $table); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function copyTable($table, $table2){ $sql = sprintf("CREATE TABLE `%s` AS SELECT * FROM `%s`;", $table2, $table); $this->sql($sql); $status = $this->DBO->exec($sql); return(true); } public function cloneTable($table, $table2){ $sql = sprintf("CREATE TABLE `%s` LIKE `%s`;", $table2, $table); $this->sql($sql); $status = $this->DBO->exec($sql); return(true); } public function deleteTable($table){ $sql = sprintf("DROP TABLE `%s`;", $table); $this->debug(__METHOD__, 'sql', $sql); $this->sql($sql); $status = $this->DBO->exec($sql); $this->debug(__METHOD__, 'status', $status); return($status); } public function emptyTable($table){ $sql = sprintf("TRUNCATE TABLE `%s`;", $table); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function renameTable($table, $table2){ $sql = sprintf("RENAME TABLE `%s` TO `%s`;", $table, $table2); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } //CREATE TRIGGER `before_employee_update` BEFORE UPDATE ON `employees` FOR EACH ROW INSERT INTO public function createTrigger($database, $trigger){ $this->debug(__METHOD__, 'database', $database); $this->debug(__METHOD__, 'trigger', $trigger); if ($this->driver == 'sqlite'){ //$sql = sprintf("CREATE INDEX `%s` ON %s (`%s`);", $index, $table, $column); } else { $sql = sprintf("CREATE TRIGGER `%s` %s %s ON `%s` FOR EACH ROW %s;", $trigger['name'], $trigger['timing'], $trigger['event'], $trigger['table'], $trigger['definition']); } $this->debug(__METHOD__, 'sql', $sql); $status = $this->DBO->exec($sql); return($status); } public function deleteTrigger($database, $trigger){ $sql = sprintf("DROP TRIGGER `%s`;", $trigger['name']); $this->debug(__METHOD__, 'sql', $sql); $status = $this->DBO->exec($sql); return($status); } public function renameTrigger($database, $trigger1, $trigger2){ $status = $this->createTrigger($database, $trigger2); if (is_string($status)) return($status); $status = $this->deleteTrigger($database, $trigger1); return($status); } public function createView($view, $select){ $this->debug(__METHOD__, 'view', $view); $sql = sprintf("CREATE"); if ($this->driver == 'mysql') { $sql.= sprintf(" ALGORITHM=UNDEFINED DEFINER=`ronludwig`@`%%` SQL SECURITY DEFINER"); } $sql.= sprintf(" VIEW `%s` AS", $view); $sql.= sprintf(" %s;", $select); $this->debug(__METHOD__, 'sql', $sql); $status = $this->DBO->exec($sql); $this->debug(__METHOD__, 'status', $status); return($status); } public function deleteView($view1){ $this->debug(__METHOD__, 'view1', $view1); $sql = sprintf("DROP VIEW `%s`;", $view1); $this->debug(__METHOD__, 'sql', $sql); $status = $this->DBO->exec($sql); $this->debug(__METHOD__, 'status', $status); return($status); } public function renameView($view1, $view2){ $this->debug(__METHOD__, 'view1', $view1); $this->debug(__METHOD__, 'view2', $view2); if ($this->driver == 'sqlite') { $record = $this->getView($this->driver, $this->database, $view1); $this->debug(__METHOD__, 'RECORD', $record); $sql = $record['sql']; $sql = str_replace($view1, $view2, $sql); $status = $this->createView($view2, $sql); $this->debug(__METHOD__, 'createView_status', $status); if (!is_string($status)) { $status = $this->deleteView($view1); $this->debug(__METHOD__, 'deleteView_status', $status); } } else { $sql = sprintf("RENAME TABLE `%s` TO `%s`;", $view1, $view2); $this->debug(__METHOD__, 'sql', $sql); $status = $this->DBO->exec($sql); $this->debug(__METHOD__, 'status', $status); } return($status); } public function importFile($database){ $this->connect($database); //printf('
DATABASE = %s'."\n", print_r($database)); //printf('
FILES = %s'."\n", print_r($_FILES)); $filename = $_FILES["file"]["name"]; $sql = file_get_contents($_FILES["file"]["tmp_name"]); //printf('
SQL = %s'."\n", print_r($sql)); $status = $this->DBO->exec($sql); //printf('
STATUS = %s'."\n", print_r($status)); return($status); } public function exportFile($dbhost, $database, $table=null){ //$output = fopen("php://output", "w"); /* printf('
DBHOST = %s'."\n", print_r($dbhost)); printf('
DATABASE = %s'."\n", print_r($database)); printf('
TABLE = %s'."\n", print_r($table)); exit; */ $date = date('Y-m-d'); if (!empty($table)) { $filename = sprintf('%s-%s.sql', $table, $date); } else { $filename = sprintf('%s-%s.sql', $database, $date); } header('Content-Type: text/plain; charset=utf-8'); header('Content-Disposition: attachment; filename='.$filename); header('Pragma: no-cache'); header('Expires: 0'); header('Content-Transfer-Encoding: binary'); header('Cache-Control: must-revalidate'); printf('-- DBone SQL Export'."\n"); printf('-- Version: 2.0.1'."\n"); printf('-- Website: http://www.DBone.us'."\n"); printf('--'."\n"); printf('-- Host: %s'."\n", $dbhost); printf('-- Date: %s'."\n", date('Y-m-d')); printf('-- Time: %s'."\n", date('g:i:s')); $this->connect($database); $sql = "SELECT VERSION()"; $data = $this->DBO->query($sql); $server_version = $data[0]['VERSION()']; printf('-- Server version: %s'."\n", $server_version); printf('-- PHP version: %s'."\n", phpversion()); printf("\n"); printf('--'."\n"); printf('-- Database: %s'."\n", $database); printf('--'."\n"); printf("\n"); if (!empty($table)) { $tables = array($table); } else { $tables = $this->getTables($this->driver, $database); } foreach($tables as $table){ $table = $table['table']; printf('--'."\n"); printf('-- Export structure for table: %s'."\n", $table); printf('--'."\n"); printf("\n"); $sql = "SHOW CREATE TABLE `$table`"; $data = $this->DBO->query($sql); $stmt = !empty($data[0]['Create Table']) ? $data[0]['Create Table'] : ''; $stmt = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $stmt); printf('%s;'."\n", $stmt); printf("\n"); printf('--'."\n"); printf('-- Export data for table: %s'."\n", $table); printf('--'."\n"); printf("\n"); //$fields = $this->getFields($database, $table); //$columns = $this->getColumns($database, $table); //printf('
COLUMNS = %s'."\n", print_r($columns)); $data = $this->getData($database, $table); $columns = array_keys($data[0]); $fields = "`".implode("`,`", $columns)."`"; foreach($data as $rec){ foreach($rec as $key => $val){ $rec[$key] = addslashes($val); } $values = "'".implode("','", $rec)."'"; printf('INSERT INTO `%s` (%s) VALUES (%s);'."\n", $table, $fields, $values); } } //fclose($output); return(true); } public function addColumn($database, $table, $column, $type, $oldcol){ $sql = sprintf("ALTER TABLE `%s` ADD COLUMN `%s` %s AFTER `%s`;", $table, $column, $type, $oldcol); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function moveColumn($database, $table, $column, $type, $where){ $sql = sprintf("ALTER TABLE `%s` CHANGE COLUMN `%s` `%s` %s %s;", $table, $column, $column, $type, $where); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function changeColumn($database, $table, $column, $type, $oldcol, $null, $key, $default, $extra){ $sql = sprintf("ALTER TABLE `%s` CHANGE COLUMN `%s` `%s` %s", $table, $oldcol, $column, $type); if ($null == 'NO') { $sql.= sprintf(" NOT NULL"); } if (!empty($default)) { $sql.= sprintf(" DEFAULT %s", $default); } $sql.= ';'; $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function dropColumn($database, $table, $column){ $sql = sprintf("ALTER TABLE `%s` DROP COLUMN `%s`;", $table, $column); $this->debug(__METHOD__, 'sql', $sql); $this->sql($sql); $status = $this->DBO->exec($sql); $this->debug(__METHOD__, 'status', $status); return($status); } public function modifyColumn($database, $table, $column, $type){ $sql = sprintf("ALTER TABLE `%s` MODIFY COLUMN `%s` %s;", $table, $column, $type); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function renameColumn($database, $table, $column1, $column2){ $sql = sprintf("ALTER TABLE `%s` RENAME COLUMN `%s` TO `%s`;", $table, $column1, $column2); $this->debug(__METHOD__, 'sql', $sql); $this->sql($sql); $status = $this->DBO->exec($sql); $this->debug(__METHOD__, 'status', $status); return($status); } //ALTER TABLE `links` ADD UNIQUE(`title`); //alter table Persion add primary key (persionId,Pname,PMID) public function createIndex($database, $table, $column, $type){ if ($this->driver == 'sqlite'){ $sql = sprintf("CREATE INDEX `%s` ON %s (`%s`);", $index, $table, $column); } else { $sql = sprintf("ALTER TABLE `%s` ADD %s (`%s`);", $table, $type, $column); } $status = $this->DBO->exec($sql); return($status); } //ALTER TABLE `links` DROP INDEX `title`; public function deleteIndex($database, $table, $column){ $sql = sprintf("ALTER TABLE `%s` DROP INDEX `%s`;", $table, $column); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } //ALTER TABLE `links` DROP INDEX `title`; public function renameIndex($database, $table, $column, $column2){ $sql = sprintf("ALTER TABLE `%s` RENAME INDEX `%s` TO `%s`;", $table, $column, $column2); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function deleteRecord($database, $table, $oldrec){ $conditions = array(); foreach ($oldrec as $key => $val) { $conditions[] = sprintf("`%s` = '%s'", $key, @addslashes($val)); } $condition = implode(' AND ', $conditions); $sql = sprintf("DELETE FROM `%s` WHERE %s;", $table, $condition); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function insertRecord($database, $table, $record){ $rec = array(); foreach($record as $key => $val) { if (!empty($val)) { $rec[$key] = addslashes($val); } } $fields = sprintf("`%s`", implode("`,`", array_keys($rec))); $values = sprintf("'%s'", implode("','", array_values($rec))); $sql = sprintf("INSERT INTO `%s` (%s) VALUES (%s);", $table, $fields, $values); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function updateRecord($database, $table, $record, $record2){ $sets = array(); foreach ($record2 as $key => $val) { if ($record2[$key] !== $record[$key]) { $sets[] = sprintf("`%s` = '%s'", $key, addslashes($val)); } } if (empty($sets)) { return(0); } $set = implode(',', $sets); $conditions = array(); foreach ($record as $key => $val) { $conditions[] = sprintf("`%s` = '%s'", $key, @addslashes($val)); } $condition = implode(' AND ', $conditions); $sql = sprintf("UPDATE `%s` SET %s WHERE %s;", $table, $set, $condition); $this->sql($sql); $status = $this->DBO->exec($sql); return($status); } public function updateRecord3($database, $table, $record, $oldrec){ $i=0; $sets = array(); foreach($record as $key => $val) { if ($i == 0) { $field = $key; $value = $val; } if (empty($val)) { //$sets[] = sprintf("`%s` = NULL", $key); } else { $sets[] = sprintf("`%s` = '%s'", $key, addslashes($val)); } $i++; } $set = implode(',', $sets); $conditions = array(); foreach ($record as $key => $val) { if (empty($val)) { //$conditions[] = sprintf("`%s` = NULL", $key); } else { $conditions[] = sprintf("`%s` = '%s'", $key, @addslashes($val)); } } $condition = implode(' AND ', $conditions); $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` = '%s';", $table, $set, $field, $value); $this->sql($sql); $affected = $this->DBO->exec($sql); return($affected); } public function updateRecord2($database, $table, $record){ $old_record = $this->row($database, $table, $row); $old_record = $old_record[0]; $i=0; $sets = array(); foreach($new_record as $key => $val) { if ($i == 0) { $field = $key; $value = $val; } if (empty($val)) { //$sets[] = sprintf("`%s` = NULL", $key); } else { $sets[] = sprintf("`%s` = '%s'", $key, addslashes($val)); } $i++; } $set = implode(',', $sets); $conditions = array(); foreach ($old_record as $key => $val) { if (empty($val)) { //$conditions[] = sprintf("`%s` = NULL", $key); } else { $conditions[] = sprintf("`%s` = '%s'", $key, @addslashes($val)); } } $condition = implode(' AND ', $conditions); $sql = sprintf("UPDATE `%s` SET %s WHERE %s;", $table, $set, $condition); $this->sql($sql); $affected = $this->DBO->exec($sql); return($affected); } public function isConnected(){ if (!empty($this->DBO)){ return(true); } return(false); } public function isLoggedIn(){ $driver = !empty($_SESSION['driver']) ? $_SESSION['driver'] : ''; $dbpass = !empty($_SESSION['dbpass']) ? $_SESSION['dbpass'] : ''; if ($driver == 'sqlite'){ return(true); } if (!empty($dbpass)){ return(true); } return(false); } public function isDownloadPage(){ $downloads = array('csv','export'); //printf('
PAGE = %s'."\n", $this->page);exit(); if (in_array($this->page, $downloads)) { return(true); } return(false); } public function isDatabaseSet(){ if (!empty($_SESSION['database'])){ return(true); } return(false); } public function isTableSet(){ if (!empty($_SESSION['table'])){ return(true); } return(false); } public function goback(){ printf(''."\n"); } public function redirect($page, $database='', $table=''){ //$this->debug(__METHOD__, 'page', $page, 1); //$this->debug(__METHOD__, 'database', $database, 1); //$this->debug(__METHOD__, 'table', $table, 1); //exit; if (!empty($table)) { $url = sprintf("./?page=%s&database=%s&table=%s", $page, $database, $table); } else if (!empty($database)) { //$url = sprintf("./?page=tables&database=%s", $database); $url = sprintf("./?page=%s&database=%s", $page, $database); } else { $url = sprintf("./?page=databases"); //$url = sprintf("./?page=login"); } //printf('
URL = %s'."\n", print_r($url)); //header('Location: '.$url); echo ""; } public function redirect3($database, $table){ //printf('
DATABASE = %s'."\n", print_r($database)); //printf('
TABLE = %s'."\n", print_r($table)); if (!empty($table)) { $url = sprintf("./?page=records&database=%s&table=%s", $database, $table); } else if (!empty($database)) { $url = sprintf("./?page=tables&database=%s", $database); } else { $url = sprintf("./?page=databases"); } //printf('
URL = %s'."\n", print_r($url)); //header('Location: '.$url); echo ""; } public function redirect2($url){ header('Location: '.$url); } public function debug($method, $name, $value, $mode=null) { if (!empty($this->debug) || !empty($mode)) { printf('
%s.%s = %s'."\n", $method, $name, print_r($value, 1)); } } public function raw($rows) { printf('
'."\n");
foreach($rows as $row){
printf("%s\n", print_r($row));
}
printf(''."\n");
}
public function showDatabases($data, $order_by, $order) {
//$this->debug(__METHOD__, 'data', $data,1);
//$this->debug(__METHOD__, '$order_by', $order_by,1);
//$this->debug(__METHOD__, '$order', $order,1);
if (!is_array($data) || count($data) == 0) {
printf('| # | '."\n"); $order1 = $order_by == 'database' && $order != 'DESC' ? 'DESC' : 'ASC'; printf('DATABASE | '."\n", $order1); //printf('DATABASE | '."\n", $order1); $order2 = $order_by == 'tables' && $order != 'DESC' ? 'DESC' : 'ASC'; printf('TABLES | '."\n", $order2); //printf('TABLES | '."\n", $order2); printf('
|---|---|---|---|---|
| %s | '."\n", $row); printf('%s | '."\n", $rec['database']); printf('%s | '."\n", $rec['tables']); printf('||
| T O T A L | '."\n"); printf('%s | '."\n", $total); printf('|||
| # | '."\n"); $order1 = $order_by == 'table' && $order != 'DESC' ? 'DESC' : 'ASC'; printf('TABLES | '."\n", $database, $order1); $order2 = $order_by == 'rows' && $order != 'DESC' ? 'DESC' : 'ASC'; printf('ROWS | '."\n", $database, $order2); //$order3 = $order_by == 'size' && $order != 'DESC' ? 'DESC' : 'ASC'; //printf('SIZE | '."\n", $database, $order3); //$order4 = $order_by == 'auto' && $order != 'DESC' ? 'DESC' : 'ASC'; //printf('AUTO | '."\n", $database, $order4); printf('
|---|---|---|---|---|
| %s | '."\n", $row); printf('%s | '."\n", $rec['table']); printf('%s | '."\n", $rec['rows']); //printf('%s | '."\n", $rec['size']); //printf('%s | '."\n", $rec['auto']); printf('
| T O T A L | '."\n"); printf('%s | '."\n", $total); printf('|||
| # | '."\n"); $order1 = $order_by == 'table' && $order != 'DESC' ? 'DESC' : 'ASC'; printf('VIEWS | '."\n", $database, $order1); $order2 = $order_by == 'rows' && $order != 'DESC' ? 'DESC' : 'ASC'; printf('ROWS | '."\n", $database, $order2); printf('||
|---|---|---|---|---|
| %s | '."\n", $row); printf('%s | '."\n", $rec['view']); printf('%s | '."\n", $rec['rows']); //printf('%s | '."\n", $rec['size']); //printf('%s | '."\n", $rec['auto']); printf('
| T O T A L | '."\n"); printf('%s | '."\n", $total); printf('|||
| # | '."\n"); foreach ($rec as $key => $val) { printf('%s | '."\n", $this->database, $this->table, $key, strtoupper($key)); } printf('
|---|---|
| %s | '."\n", $row); } $col++; printf('%s | '."\n", $val); } printf('
| # | '."\n"); foreach ($rec as $key => $val) { $width = $col == 0 ? ' width="100%"' : ''; $col++; printf('|||
|---|---|---|---|
| %s | '."\n", $row); } $col++; if (in_array($key, $columns)) { $totals[$key] += intval($val); printf('%s | '."\n", $val); } else { printf('%s | '."\n", $val); } } printf('|
| '."\n"); //printf(' | T O T A L | '."\n"); foreach ($rec as $key => $val) { if (in_array($key, $columns) && $totals[$key] > 0) { printf('%s'."\n", $totals[$key]); } else { printf(' | '."\n"); } } printf(' |
TOTALS = %s\n",print_r($totals)); } public function show($rows) { //printf("
COUNT = %s\n",print_r(count($rows))); //printf("
SHOW ROWS = %s\n",print_r($rows)); $this->debug(__METHOD__, 'rows', $rows); if(!isset($rows[0])){ $recs = array(); //$recs[] = array('key' => 'value'); foreach($rows as $key => $val){ $recs[] = array('key' => $key, 'value' => $val); } $rows = $recs; } //printf("
NEW ROWS = %s\n",print_r($rows)); //$rows = empty($rows[0]) ? array($rows) : $rows; $bolds = array('bday','ddate','mdate'); $centers = array('age','bdate','bday','byear','lived','mday','mobile','myear','myears','state','year','years'); $rights = array(); $n=0; //printf('
| # | \n"); foreach ($row as $key => $val) { printf("%s | \n", strtoupper($key)); } printf("||||
|---|---|---|---|---|---|
| %s | \n", $n); foreach ($row as $key => $val) { printf("%s | \n", print_r($val)); /* if (in_array($key, $bolds)){ printf('%s | '."\n", $val); } else if (in_array($key, $centers)){ printf('%s | '."\n", $val); } else if (in_array($key, $rights)){ printf('%s | '."\n", $val); } else { printf("%s | \n", print_r($val)); } */ } printf("
%s\n",print_r($row)); printf(''."\n"); } public function showDatabaseMenu($page) { $items = array('database','views','routines','events','triggers'); $this->showMenu($items, $page); } public function showTableMenu($page, $database) { $items = array('table','views'); $this->showMenu($items, $page, $database); } public function showResults($data){ //$this->debug(__METHOD__, 'data', $data); printf('
| # | \n"); foreach ($rec as $key => $val) { printf("%s | \n", strtoupper($key)); } printf("
|---|---|
| %s | \n", $n); foreach ($rec as $key => $val) { printf("%s | \n", print_r($val, 1)); } printf("
ERROR2: %s'."\n", print_r($status)); $this->error(ucwords($status)); session_destroy(); } } else { session_destroy(); } } public function model(){ $this->drivers = $this->getDrivers(); } public function view(){ $this->showHead(); $this->showStatus(); printf('
DATA = %s\n",print_r($data)); } public function view(){ $this->showHead(); $this->showStatus(); $n=0; printf('
DIR = %s'."\n", print_r($this->dbpath)); $files = scandir($this->dbpath); //printf('
FILES = %s'."\n", print_r($files)); $databases = array(); foreach($files as $file) { if (strpos($file, '.') == 0) continue; $database = substr($file, 0, strrpos($file, ".")); //if (in_array($database, $forbidden)) continue; $this->connect($database); $sql = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"; //printf('
SQL = %s'."\n", print_r($sql)); $this->data = $this->DBO->query($sql); //printf('
DATA = %s'."\n", print_r($this->data)); $tables = count($this->data); $rec['database'] = $database; $rec['tables'] = $tables; $databases[] = $rec; } $this->data = $databases; } else { $databases = $this->getDatabases(); $this->debug(__METHOD__, 'databases', $databases); $this->data = array(); foreach($databases as $database) { $databases = $this->getTables(); //if (in_array($database, $this->forbidden)) continue; //$this->connect($database); $sql = "SHOW TABLES FROM `$database`"; $this->debug(__METHOD__, 'SQL', $sql); $tables = $this->DBO->query($sql); $this->debug(__METHOD__, 'TABLES', $tables); exit; $this->data[] = array('database' => $database, 'tables' => count($tables)); } } */ } public function view(){ $next = 'tables'; $column = 'database'; $columns = array('tables'); $this->showHead(); $this->showStatus(); printf('
| '."\n");
printf(''."\n");
printf(' '."\n"); printf(''."\n"); printf(' '."\n"); printf(''."\n"); printf(' '."\n"); printf(''."\n"); printf(' '."\n"); printf(''."\n"); printf(' '."\n"); printf(''."\n"); printf(' '."\n"); printf(''."\n"); printf(' | '."\n");
printf('
DATA = %s\n",print_r($this->data)); } public function view(){ $this->showHead(); $this->showStatus(); printf('
| '."\n"); //printf(''."\n"); printf(''."\n"); printf(''."\n"); //printf(''."\n"); printf(''."\n"); printf(''."\n"); printf(''."\n"); printf(''."\n"); $view = "New_View"; $sql = "SELECT * FROM `test`"; printf(''."\n"); printf(' | '."\n"); printf('
DBO.dbhost = %s'."\n", print_r($dbhost)); $dsn = "$driver:$dbhost/$database.db"; //printf('
DBO.dsn = %s'."\n", print_r($dsn)); } } else { //$dsn = "$driver:host=$dbhost;port=$dbport;"; $dsn = "$driver:host=$dbhost;port=$dbport;charset=$charset;dbname=$database;"; } $options = array( \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC, \PDO::ATTR_EMULATE_PREPARES => false, ); try { //printf('
DBO.dsn = %s'."\n", print_r($dsn)); //printf('
DBO.dbuser = %s'."\n", print_r($dbuser)); //printf('
DBO.dbpass = %s'."\n", print_r($dbpass)); //printf('
DBO.options = %s'."\n", print_r($options)); //$this->PDO = new \PDO($dsn, $dbuser, $dbpass); $this->PDO = new \PDO($dsn, $dbuser, $dbpass, $options); //printf('
DBO.connect.status = OK'."\n"); $status = true; } catch(PDOException $pex) { printf('
DBO.connect.error = %s'."\n", print_r($pex->getMessage())); $status = $pex->errorInfo[2]; } return $status; } function getDatabases222() { try { $sql = "SHOW DATABASES"; //$this->debug(__METHOD__, 'SQL', $sql, 1); $stmt = $this->PDO->query($sql); $databases = $stmt->fetchAll(); //$this->debug(__METHOD__, 'DATABASES', $databases, 1); $this->data = array(); foreach($databases as $arr) { //$this->debug(__METHOD__, 'arr', $arr, 1); $dbname = $arr['Database']; //$this->debug(__METHOD__, 'dbname', $dbname, 1); $sql = "SHOW FULL TABLES FROM `$dbname`"; $sql.= " WHERE `Table_type` != 'VIEW'"; //$this->debug(__METHOD__, 'SQL', $sql, 1); $stmt = $this->PDO->query($sql); $tables = $stmt->fetchAll(); //$this->debug(__METHOD__, 'TABLES', $tables, 1); $data[] = array('database' => $dbname, 'tables' => count($tables)); } } catch(\PDOException $pex) { print "Error: " . $pex->getMessage(); //$status = $pex->errorInfo[2]; $data = $pex->getMessage(); } //$this->debug(__METHOD__, 'DATA', $data, 1); return($data); } function getTables222($database) { try { $sql = "SHOW FULL TABLES FROM `$database`"; $sql.= " WHERE `Table_type` != 'VIEW'"; //$this->debug(__METHOD__, 'SQL', $sql, 1); $stmt = $this->PDO->query($sql); $tables = $stmt->fetchAll(); //$this->debug(__METHOD__, 'TABLES', $tables, 1); $this->data = array(); foreach($tables as $arr) { //$this->debug(__METHOD__, 'arr', $arr, 1); $key = sprintf("Tables_in_%s", $database); $table = $arr[$key]; //$this->debug(__METHOD__, 'table', $table, 1); $sql = "SELECT COUNT(*) AS `records`"; $sql.= " FROM `$table`"; //$this->debug(__METHOD__, 'SQL', $sql, 1); $stmt = $this->PDO->query($sql); $records = $stmt->fetchAll(); //$this->debug(__METHOD__, 'records', $records, 1); $data[] = array('table' => $table, 'rows' => $records[0]['records']); } } catch(\PDOException $pex) { print "Error: " . $pex->getMessage(); //$status = $pex->errorInfo[2]; $data = $pex->getMessage(); } //$this->debug(__METHOD__, 'DATA', $data, 1); return($data); } function getViews222($database) { try { $sql = "SHOW FULL TABLES FROM `$database`"; $sql.= " WHERE `Table_type` = 'VIEW'"; //$this->debug(__METHOD__, 'SQL', $sql, 1); $stmt = $this->PDO->query($sql); $views = $stmt->fetchAll(); //$this->debug(__METHOD__, 'TABLES', $tables, 1); $this->data = array(); foreach($views as $arr) { $this->debug(__METHOD__, 'view', $view, 1); $key = sprintf("Tables_in_%s", $database); $view = $arr[$key]; //$this->debug(__METHOD__, 'table', $table, 1); $sql = "SELECT COUNT(*) AS `records`"; $sql.= " FROM `$view`"; //$this->debug(__METHOD__, 'SQL', $sql, 1); $stmt = $this->PDO->query($sql); $records = $stmt->fetchAll(); $this->debug(__METHOD__, 'records', $records, 1); $data[] = array('table' => $table, 'rows' => $records[0]['records']); } } catch(\PDOException $pex) { print "Error: " . $pex->getMessage(); //$status = $pex->errorInfo[2]; $data = $pex->getMessage(); } $this->debug(__METHOD__, 'DATA', $data, 1); return($data); } function exec($sql) { try { $status = $this->PDO->exec($sql); } catch(\PDOException $pex) { $status = $pex->errorInfo[2]; } return $status; } function execute2($sql) { try { $status = $this->PDO->execute($sql); } catch(\PDOException $pex) { $status = $pex->errorInfo[2]; } return $status; } function getlastId() { try { $lastId = $this->PDO->lastInsertId(); } catch(\PDOException $pex) { $status = $pex->errorInfo[2]; } return $lastId; } function prepare($sql) { try { $stmt = $this->PDO->prepare($sql); return($stmt); } catch(\PDOException $pex) { $status = $pex->errorInfo[2]; return($status); } } function query($sql) { if (empty($this->PDO)) return(false); try { //printf('
SQL = %s'."\n", print_r($sql)); $results = $this->PDO->query($sql); //printf('
RESULTS = %s'."\n", print_r($results)); $rows = $results->fetchAll(); //printf('
ROWS = %s'."\n", print_r($rows)); //$rows = $this->PDO->query($sql, PDO::FETCH_ASSOC); return($rows); } catch(\PDOException $pex) { $status = $pex->errorInfo[2]; return($status); } } public function debug($method, $name, $value, $mode=null) { if (!empty($this->debug) || !empty($mode)) { printf('
%s.%s = %s'."\n", $method, $name, print_r($value, 1)); } } } class MySQL extends DBO { public $PDO; public function connect($dba){ //$this->debug(__METHOD__, 'dba', $dba, 1); $driver = !empty($dba['driver']) ? $dba['driver'] : ''; $dbhost = !empty($dba['dbhost']) ? $dba['dbhost'] : ''; $dbport = !empty($dba['dbport']) ? $dba['dbport'] : ''; $dbchar = !empty($dba['dbchar']) ? $dba['dbchar'] : ''; $dbname = !empty($dba['dbname']) ? $dba['dbname'] : ''; $dbuser = !empty($dba['dbuser']) ? $dba['dbuser'] : ''; $dbpass = !empty($dba['dbpass']) ? $dba['dbpass'] : ''; //$this->debug(__METHOD__, 'driver', $driver, 1); $dsn = "$driver:host=$dbhost;port=$dbport;charset=$dbchar;dbname=$dbname;"; $dbopts = array( \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC, \PDO::ATTR_EMULATE_PREPARES => false, ); try { $this->PDO = new \PDO($dsn, $dbuser, $dbpass, $dbopts); $status = true; } catch(PDOException $pex) { print "Error: " . $pex->getMessage(); //$status = $pex->errorInfo[2]; $status = $pex->getMessage(); } return($status); } public function getDatabases($order_by=null, $order=null){ $order_by = !empty($order_by) ? $order_by : 'database'; $order = !empty($order) ? $order : 'ASC'; $sql = "SELECT A.SCHEMA_NAME AS `database`, COUNT(B.TABLE_NAME) AS `tables`"; $sql.= " FROM information_schema.SCHEMATA A"; $sql.= " LEFT JOIN information_schema.TABLES B"; $sql.= " ON A.SCHEMA_NAME = B.TABLE_SCHEMA"; $sql.= " WHERE A.SCHEMA_NAME NOT IN ('information_schema','mysql','performance_schema','sys')"; $sql.= " AND B.TABLE_TYPE != 'VIEW'"; $sql.= " GROUP BY A.SCHEMA_NAME"; $sql.= " ORDER BY `$order_by` $order"; try { $stmt = $this->PDO->query($sql); $data = $stmt->fetchAll(); } catch(PDOException $pex) { print "Error: " . $pex->getMessage(); $data = $pex->getMessage(); } return($data); } public function getTables($database, $order_by=null, $order=null){ $order_by = !empty($order_by) ? $order_by : 'table'; $order = !empty($order) ? $order : 'ASC'; $sql = "SELECT table_name AS `table`, SUM(table_rows) AS `rows`"; $sql.= " FROM information_schema.tables"; $sql.= " WHERE table_schema = '$database'"; $sql.= " AND table_type != 'VIEW'"; $sql.= " GROUP BY table_name"; $sql.= " ORDER BY `$order_by` $order"; try { $stmt = $this->PDO->query($sql); $data = $stmt->fetchAll(); } catch(PDOException $pex) { print "Error: " . $pex->getMessage(); $data = $pex->getMessage(); } return($data); } function getViews($database) { $data = array(); try { $sql = "SHOW FULL TABLES FROM `$database`"; $sql.= " WHERE `Table_type` = 'VIEW'"; $stmt = $this->PDO->query($sql); $views = $stmt->fetchAll(); foreach($views as $arr) { $key = sprintf("Tables_in_%s", strtolower($database)); $view = $arr[$key]; $sql = "SELECT COUNT(*) AS `records`"; $sql.= " FROM `$view`"; $stmt = $this->PDO->query($sql); $records = $stmt->fetchAll(); $data[] = array('view' => $view, 'rows' => $records[0]['records']); } } catch(\PDOException $pex) { print "Error: " . $pex->getMessage(); $data = $pex->getMessage(); } return($data); } public function getViewsBAD($database, $order_by=null, $order=null){ $order_by = !empty($order_by) ? $order_by : 'view'; $order = !empty($order) ? $order : 'ASC'; $this->debug(__METHOD__, ' database', $database, 1); $sql = "SELECT table_name AS `table`, SUM(table_rows) AS `rows`"; $sql.= " FROM information_schema.tables"; $sql.= " WHERE table_schema = '$database'"; $sql.= " AND table_type = 'VIEW'"; $sql.= " GROUP BY table_name"; //$sql.= " ORDER BY `$order_by` $order"; try { $stmt = $this->PDO->query($sql); $data = $stmt->fetchAll(); } catch(PDOException $pex) { print "Error: " . $pex->getMessage(); //$status = $pex->errorInfo[2]; $data = $pex->getMessage(); } $this->debug(__METHOD__, 'data', $data, 1); return($data); } public function getRecords($database, $table, $order_by=null, $order=null){ $order_by = !empty($order_by) ? $order_by : ''; $order = !empty($order) ? $order : 'ASC'; $sql = "SELECT *"; $sql.= " FROM `$table`"; if (!empty($order_by)) { $sql.= " ORDER BY `$order_by` $order"; } //$this->debug(__METHOD__, 'sql', $sql, 1); try { $stmt = $this->PDO->query($sql); $data = $stmt->fetchAll(); } catch(PDOException $pex) { print "Error: " . $pex->getMessage(); //$status = $pex->errorInfo[2]; $data = $pex->getMessage(); } //$this->debug(__METHOD__, 'data00', $data, 1); return($data); } public function getRecord($database, $table, $key, $val){ $sql = "SELECT *"; $sql.= " FROM `$table`"; $sql.= " WHERE `$key` = '$val'"; //$this->debug(__METHOD__, 'sql', $sql, 1); try { $stmt = $this->PDO->query($sql); $data = $stmt->fetchAll(); } catch(PDOException $pex) { print "Error: " . $pex->getMessage(); //$status = $pex->errorInfo[2]; $data = $pex->getMessage(); } //$this->debug(__METHOD__, 'data00', $data, 1); return($data); } } class SQLite extends DBO { public $PDO, $dbpath, $dbfile; public function connect($dba){ $this->dbpath = !empty($dba['dbpath']) ? $dba['dbpath'] : ''; $this->dbfile = !empty($dba['dbfile']) ? $dba['dbfile'] : ''; if (empty($this->dbfile)) return(true); //if (!is_file($this->dbpath.'/'.$this->dbfile)) return("Invalid DBfile!"); $dsn = "sqlite:$this->dbpath".'/'."$this->dbfile"; //$this->debug(__METHOD__, 'dsn', $dsn, 1); try { $this->PDO = new \PDO($dsn); $status = true; } catch(PDOException $pex) { $status = $pex->errorInfo[2]; //$this->debug(__METHOD__, 'errorinfo', $pex->errorInfo, 1); } return $status; } public function getDatabases($order_by=null, $order=null){ $order_by = !empty($order_by) ? $order_by : 'database'; $order = !empty($order) ? $order : 'ASC'; $data = array(); $exts = array('db', 'sqlite'); $files = scandir($this->dbpath); foreach($files as $file) { if (strpos($file, '.') == 0) continue; $dbname = substr($file, 0, strrpos($file, '.')); $ext = substr(strrchr($file, '.'), 1); if (!in_array($ext, $exts)) continue; $dba = array( 'dbpath' => $this->dbpath, 'dbfile' => $file, ); try { $this->connect($dba); $sql = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';"; $stmt = $this->PDO->query($sql); $recs = $stmt->fetchAll(); $record['database'] = $file; $record['tables'] = count($recs); $data[] = $record; } catch(PDOException $pex) { print "Error: " . $pex->getMessage(); $data = $pex->getMessage(); } } return($data); } public function getTables($database, $order_by=null, $order=null){ $order_by = !empty($order_by) ? $order_by : 'table'; $order = !empty($order) ? $order : 'ASC'; $sql = "SELECT * FROM sqlite_master"; $sql.= " WHERE `type` = 'table'"; $sql.= " AND name NOT LIKE 'sqlite_%'"; $sql.= " ORDER BY `$order_by` $order"; $dba = array( 'dbpath' => $this->dbpath, 'dbfile' => $database, ); //$this->debug(__METHOD__, 'dba', $dba, 1); $this->connect($dba); //$this->debug(__METHOD__, 'sql1', $sql, 1); $stmt = $this->PDO->query($sql); $recs = $stmt->fetchAll(); //$this->debug(__METHOD__, 'recs', $recs, 1); $data = array(); foreach($recs as $rec) { //if ($rec['name'] == 'sqlite_sequence') continue; $table = array(); $table['table'] = $rec['name']; $table['rows'] = $rec['rootpage']; $data[] = $table; } return($data); } public function getRecords($database, $table, $order_by=null, $order=null){ $order_by = !empty($order_by) ? $order_by : ''; $order = !empty($order) ? $order : 'ASC'; $sql = "SELECT *"; $sql.= " FROM `$table`"; if (!empty($order_by)) { $sql.= " ORDER BY `$order_by` $order"; } //$this->debug(__METHOD__, 'sql', $sql, 1); $dba = array( 'dbpath' => $this->dbpath, 'dbfile' => $database, ); $this->connect($dba); $stmt = $this->PDO->query($sql); $recs = $stmt->fetchAll(); try { $stmt = $this->PDO->query($sql); $data = $stmt->fetchAll(); } catch(PDOException $pex) { print "Error: " . $pex->getMessage(); //$status = $pex->errorInfo[2]; $data = $pex->getMessage(); } //$this->debug(__METHOD__, 'data00', $data, 1); return($data); } public function getRecord($database, $table, $key, $val){ $sql = "SELECT *"; $sql.= " FROM `$table`"; $sql.= " WHERE `$key` = '$val'"; //$this->debug(__METHOD__, 'sql', $sql, 1); $dba = array( 'dbpath' => $this->dbpath, 'dbfile' => $database, ); $this->connect($dba); $stmt = $this->PDO->query($sql); $recs = $stmt->fetchAll(); try { $stmt = $this->PDO->query($sql); $data = $stmt->fetchAll(); } catch(PDOException $pex) { print "Error: " . $pex->getMessage(); //$status = $pex->errorInfo[2]; $data = $pex->getMessage(); } //$this->debug(__METHOD__, 'data00', $data, 1); return($data); } } $DBone = new DBone(); $DBone->controller(); $DBone->model(); $DBone->view(); ?>