private function createSchema(\Doctrine\DBAL\Connection $connection) { $schema = new \Doctrine\DBAL\Schema\Schema(); $teams = $schema->createTable('teams'); $teams->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true)); $teams->addColumn('uuid', 'binary', array('length' => 128)); $teams->addColumn('created_at', 'datetimetz'); $teams->addColumn('deleted', 'boolean', array('default' => false)); $teams->setPrimaryKey(array('id')); $players = $schema->createTable('players'); $players->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true)); $players->addColumn('uuid', 'binary', array('length' => 128)); $players->addColumn('created_at', 'datetimetz'); $players->addColumn('deleted', 'boolean', array('default' => false)); $players->setPrimaryKey(array('id')); $teamsPlayers = $schema->createTable('teams_players'); $teamsPlayers->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true)); $teamsPlayers->addColumn('uuid', 'binary', array('length' => 128)); $teamsPlayers->addColumn('created_at', 'datetimetz'); $teamsPlayers->addColumn('deleted', 'boolean', array('default' => false)); $teamsPlayers->addColumn('team_uuid', 'integer', array('unsigned' => true)); $teamsPlayers->addColumn('player_uuid', 'integer', array('unsigned' => true)); $teamsPlayers->setPrimaryKey(array('id')); foreach ($schema->toSql($connection->getDatabasePlatform()) as $query) { $connection->executeQuery($query); } }
public function createTable($name) { // remove table $pdo = $this->pdo->query('DROP TABLE IF EXISTS `' . $name . '`;'); $pdo->execute(); // create table $newSchema = new \Doctrine\DBAL\Schema\Schema(); $newTable = $newSchema->createTable($name); $newTable->addColumn('container_id', 'integer'); $newTable->addColumn('context_id', 'integer'); $newTable->addColumn('module_id', 'integer'); $newTable->addColumn('resource_id', 'integer')->setNotnull(false); $newTable->addColumn('by_resource_id', 'integer')->setNotnull(false); $newTable->addColumn('sequence', 'integer')->setNotnull(false); $newTable->addColumn('deleted', 'datetime')->setNotnull(false); $newTable->addColumn('display', 'boolean')->setNotnull(true); $newTable->addColumn('expire_temporary_date', 'datetime')->setNotnull(false); $newTable->addColumn('culture', 'string')->setLength(11); foreach ($this->eavColumns->getColumns() as $column) { if ($this->getTypeToColumn($column['column'])) { $newTable->addColumn($column['identifier'], $this->getTypeToColumn($column['column']))->setNotnull(false); $columnsToFill[] = $column['identifier']; } } $newTable->addForeignKeyConstraint($this->objectManager->getClassMetadata('BigfishEavBundle:Container')->getTablename(), array('container_id'), array('container_id'), array('onDelete' => 'CASCADE')); $newTable->addForeignKeyConstraint($this->objectManager->getClassMetadata('BigfishEavBundle:Module')->getTablename(), array('module_id'), array('module_id'), array('onDelete' => 'CASCADE')); $newTable->addForeignKeyConstraint($this->objectManager->getClassMetadata('BigfishContextBundle:Context')->getTablename(), array('context_id'), array('context_id'), array('onDelete' => 'CASCADE')); $newTable->addForeignKeyConstraint($this->objectManager->getClassMetadata('BigfishResourceBundle:Resource')->getTablename(), array('resource_id'), array('resource_id'), array('onDelete' => 'CASCADE')); $newTable->addForeignKeyConstraint($this->objectManager->getClassMetadata('BigfishResourceBundle:Resource')->getTablename(), array('by_resource_id'), array('resource_id'), array('onDelete' => 'CASCADE')); foreach ($newSchema->toSql($this->objectManager->getConnection()->getDatabasePlatform()) as $l) { $pdo = $this->pdo->prepare($l); $pdo->execute(); } }
/** * @author "Lionel Lecaque, <*****@*****.**>" */ public function initTaoDataBase() { $platform = $this->connection->getDatabasePlatform(); $queries = $this->schema->toSql($platform); foreach ($queries as $query) { $this->connection->executeUpdate($query); } if ($this->dbConfiguration['driver'] == 'pdo_mysql') { $this->createMysqlStatementsIndex(); } }
public function install() { $schema = new \Doctrine\DBAL\Schema\Schema(); $table = $schema->createTable($this->getTableName()); $table->addColumn("id", "integer", array("unsigned" => true, 'autoincrement' => true)); $table->setPrimaryKey(array("id")); $table->addColumn('content', 'text'); $queries = $schema->toSql($this->app['db']->getDatabasePlatform()); $queries = implode("; ", $queries); $this->app['db']->query($queries); return true; }
public function setUp() { parent::setUp(); $platform = $this->_conn->getDatabasePlatform(); if ($platform->getName() == "sqlite") { $this->markTestSkipped('TableGenerator does not work with SQLite'); } try { $schema = new \Doctrine\DBAL\Schema\Schema(); $visitor = new \Doctrine\DBAL\Id\TableGeneratorSchemaVisitor(); $schema->visit($visitor); foreach ($schema->toSql($platform) as $sql) { $this->_conn->exec($sql); } } catch (\Exception $e) { } $this->generator = new TableGenerator($this->_conn); }
public function getConnection() { $params = array('driver' => 'pdo_sqlite', 'memory' => true); $connection = DriverManager::getConnection($params, new Configuration()); $schema = new \Doctrine\DBAL\Schema\Schema(); $table = $schema->createTable('groups'); $table->addColumn('id', 'integer'); $table->addColumn('name', 'string', array('length' => 45)); $table->setPrimaryKey(array('id')); $myTable = $schema->createTable('user'); $myTable->addColumn('id', 'integer', array('unsigned' => true)); $myTable->addColumn('username', 'string', array('length' => 32)); $myTable->addColumn('group_id', 'integer'); $myTable->setPrimaryKey(array('id')); $myTable->addUniqueIndex(array('username')); $myTable->addForeignKeyConstraint($table, array('group_id'), array('id')); foreach ($schema->toSql(new SqlitePlatform()) as $query) { $connection->query($query); } return $connection; }
public function sql() { $functions = "CREATE TABLE `function` (`id`\tINTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT UNIQUE, `type` TEXT, `description` TEXT"; $params = "CREATE TABLE `parameter` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `parameter` TEXT NOT NULL, `type` TEXT NOT NULL, `initializer`\tTEXT, `functionId` INTEGER NOT NULL"; $schema = new \Doctrine\DBAL\Schema\Schema(); $functions = $schema->createTable('function'); $functions->addColumn('id', 'integer', array('autoincrement' => true)); $functions->addColumn('name', 'text'); $functions->addColumn('type', 'text'); $functions->addColumn('description', 'text'); $functions->setPrimaryKey(array('id')); $functions->addUniqueIndex(array('name')); $schema->createSequence('function_seq'); $parameter = $schema->createTable('parameter'); $parameter->addColumn('id', 'integer', array('autoincrement' => true)); $parameter->addColumn('parameter', 'text'); $parameter->addColumn('type', 'text'); $parameter->addColumn('initializer', 'text'); $parameter->setPrimaryKey(array('id')); $schema->createSequence('parameter_seq'); $sql = $schema->toSql(new \Doctrine\DBAL\Platforms\SqlitePlatform()); var_dump($sql); die; }
/** * @dataProvider getConnectionParams */ public function testConnectionException($params) { if ($this->_conn->getDatabasePlatform()->getName() == 'sqlite') { $this->markTestSkipped("Only skipped if platform is not sqlite"); } if ($this->_conn->getDatabasePlatform()->getName() == 'drizzle') { $this->markTestSkipped("Drizzle does not always support authentication"); } if ($this->_conn->getDatabasePlatform()->getName() == 'postgresql' && isset($params['password'])) { $this->markTestSkipped("Does not work on Travis"); } $defaultParams = $this->_conn->getParams(); $params = array_merge($defaultParams, $params); $conn = \Doctrine\DBAL\DriverManager::getConnection($params); $schema = new \Doctrine\DBAL\Schema\Schema(); $table = $schema->createTable("no_connection"); $table->addColumn('id', 'integer'); $this->setExpectedException('Doctrine\\DBAL\\Exception\\ConnectionException'); foreach ($schema->toSql($conn->getDatabasePlatform()) as $sql) { $conn->executeQuery($sql); } }
$saleCommissions->setPrimaryKey(['id']); $saleTable = $pluginSchema->createTable(BuyCoursesPlugin::TABLE_SALE); $saleTable->addColumn('id', \Doctrine\DBAL\Types\Type::INTEGER, ['autoincrement' => true, 'unsigned' => true]); $saleTable->addColumn('reference', \Doctrine\DBAL\Types\Type::STRING); $saleTable->addColumn('date', \Doctrine\DBAL\Types\Type::DATETIME); $saleTable->addColumn('user_id', \Doctrine\DBAL\Types\Type::INTEGER, ['unsigned' => true]); $saleTable->addColumn('product_type', \Doctrine\DBAL\Types\Type::INTEGER); $saleTable->addColumn('product_name', \Doctrine\DBAL\Types\Type::STRING); $saleTable->addColumn('product_id', \Doctrine\DBAL\Types\Type::INTEGER, ['unsigned' => true]); $saleTable->addColumn('price', \Doctrine\DBAL\Types\Type::DECIMAL, ['scale' => 2]); $saleTable->addColumn('currency_id', \Doctrine\DBAL\Types\Type::INTEGER, ['unsigned' => true]); $saleTable->addColumn('status', \Doctrine\DBAL\Types\Type::INTEGER); $saleTable->addColumn('payment_type', \Doctrine\DBAL\Types\Type::INTEGER); $saleTable->setPrimaryKey(['id']); $saleTable->addForeignKeyConstraint($currencyTable, ['currency_id'], ['id'], ['onDelete' => 'CASCADE']); $queries = $pluginSchema->toSql($platform); foreach ($queries as $query) { Database::query($query); } //Insert data $paypalTable = Database::get_main_table(BuyCoursesPlugin::TABLE_PAYPAL); $currencyTable = Database::get_main_table(BuyCoursesPlugin::TABLE_CURRENCY); $itemTable = Database::get_main_table(BuyCoursesPlugin::TABLE_ITEM); $saleTable = Database::get_main_table(BuyCoursesPlugin::TABLE_SALE); $commissionTable = Database::get_main_table(BuyCoursesPlugin::TABLE_COMMISSION); $extraFieldTable = Database::get_main_table(TABLE_EXTRA_FIELD); $paypalExtraField = Database::select("*", $extraFieldTable, ['where' => ['variable = ?' => 'paypal']], 'first'); if (!$paypalExtraField) { Database::insert($extraFieldTable, ['extra_field_type' => 1, 'field_type' => 1, 'variable' => 'paypal', 'display_text' => 'Paypal', 'default_value' => '', 'field_order' => 0, 'visible' => 1, 'changeable' => 1, 'filter' => 0, 'created_at' => getdate()]); } Database::insert($paypalTable, ['username' => '', 'password' => '', 'signature' => '', 'sandbox' => true]);
/** * Install and upgrade script. * * Run this when you run this application first time or when upgrade to a new * version. */ // TODO: use DBAL because AUTOINCREMENT is the syntax in SQLite // and AUTO_INCREMENT in MySQL. if (!isset($app)) { $app = (require __DIR__ . '/app/app.php'); } $schema = new \Doctrine\DBAL\Schema\Schema(); $devicesTable = $schema->createTable("devices"); $devicesTable->addColumn("id", "integer", ["unsigned" => true, 'autoincrement' => true]); $devicesTable->addColumn("name", "string", ["length" => 128]); $devicesTable->addColumn("hash", "string", ["length" => 256]); $devicesTable->setPrimaryKey(array("id")); $devicesTable->addUniqueIndex(array("hash")); $posTable = $schema->createTable("positions"); $posTable->addColumn("id", "integer", ["unsigned" => true, 'autoincrement' => true]); $posTable->addColumn("device_id", "integer", ["unsigned" => true]); $posTable->addColumn("timestamp", "integer", ["unsigned" => true]); $posTable->addColumn("lat", "float", []); $posTable->addColumn("lng", "float", []); $posTable->setPrimaryKey(array("id")); $platform = $app['db']->getDatabasePlatform(); $queries = $schema->toSql($platform); foreach ($queries as $query) { $app['db']->executeQuery($query); }
function repairTables() { $sm = $this->db->getSchemaManager(); $output = array(); $tables = $this->getTables(); // Check the users table.. if (!isset($tables[$this->prefix . "users"])) { $schema = new \Doctrine\DBAL\Schema\Schema(); $myTable = $schema->createTable($this->prefix . "users"); $myTable->addColumn("id", "integer", array("unsigned" => true, 'autoincrement' => true)); $myTable->setPrimaryKey(array("id")); $myTable->addColumn("username", "string", array("length" => 32)); $myTable->addColumn("password", "string", array("length" => 64)); $myTable->addColumn("email", "string", array("length" => 64)); $myTable->addColumn("lastseen", "datetime"); $myTable->addColumn("lastip", "string", array("length" => 32, "default" => "")); $myTable->addColumn("displayname", "string", array("length" => 32)); $myTable->addColumn("userlevel", "string", array("length" => 32)); $myTable->addColumn("enabled", "boolean"); $queries = $schema->toSql($this->db->getDatabasePlatform()); $queries = implode("; ", $queries); $this->db->query($queries); $output[] = "Created table <tt>" . $this->prefix . "users" . "</tt>."; } // Check the taxonomy table.. if (!isset($tables[$this->prefix . "taxonomy"])) { $schema = new \Doctrine\DBAL\Schema\Schema(); $myTable = $schema->createTable($this->prefix . "taxonomy"); $myTable->addColumn("id", "integer", array("unsigned" => true, 'autoincrement' => true)); $myTable->setPrimaryKey(array("id")); $myTable->addColumn("content_id", "integer", array("unsigned" => true)); $myTable->addColumn("contenttype", "string", array("length" => 32)); $myTable->addColumn("taxonomytype", "string", array("length" => 32)); $myTable->addColumn("slug", "string", array("length" => 64)); $myTable->addColumn("name", "string", array("length" => 64, "default" => "")); $queries = $schema->toSql($this->db->getDatabasePlatform()); $queries = implode("; ", $queries); $this->db->query($queries); $output[] = "Created table <tt>" . $this->prefix . "taxonomy" . "</tt>."; } // Check the taxonomy table.. if (!isset($tables[$this->prefix . "log"])) { $schema = new \Doctrine\DBAL\Schema\Schema(); $myTable = $schema->createTable($this->prefix . "log"); $myTable->addColumn("id", "integer", array("unsigned" => true, 'autoincrement' => true)); $myTable->setPrimaryKey(array("id")); $myTable->addColumn("level", "integer", array("unsigned" => true)); $myTable->addColumn("date", "datetime"); $myTable->addColumn("message", "string", array("length" => 1024)); $myTable->addColumn("username", "string", array("length" => 64, "default" => "")); $myTable->addColumn("requesturi", "string", array("length" => 128)); $myTable->addColumn("route", "string", array("length" => 128)); $myTable->addColumn("ip", "string", array("length" => 32, "default" => "")); $myTable->addColumn("file", "string", array("length" => 128, "default" => "")); $myTable->addColumn("line", "integer", array("unsigned" => true)); $myTable->addColumn("contenttype", "string", array("length" => 32)); $myTable->addColumn("content_id", "integer", array("unsigned" => true)); $myTable->addColumn("code", "string", array("length" => 32)); $myTable->addColumn("dump", "string", array("length" => 1024)); $queries = $schema->toSql($this->db->getDatabasePlatform()); $queries = implode("; ", $queries); $this->db->query($queries); $output[] = "Created table <tt>" . $this->prefix . "log" . "</tt>."; } // Now, iterate over the contenttypes, and create the tables if they don't exist. foreach ($this->config['contenttypes'] as $key => $contenttype) { // create the table if necessary.. $tablename = $this->prefix . makeSlug($key); if (!isset($tables[$tablename])) { $schema = new \Doctrine\DBAL\Schema\Schema(); $myTable = $schema->createTable($tablename); $myTable->addColumn("id", "integer", array("unsigned" => true, 'autoincrement' => true)); $myTable->setPrimaryKey(array("id")); $myTable->addColumn("slug", "string", array("length" => 128)); $myTable->addColumn("datecreated", "datetime"); $myTable->addColumn("datechanged", "datetime"); $myTable->addColumn("username", "string", array("length" => 32)); $myTable->addColumn("status", "string", array("length" => 32)); $queries = $schema->toSql($this->db->getDatabasePlatform()); $queries = implode("; ", $queries); $this->db->query($queries); $output[] = "Created table <tt>" . $tablename . "</tt>."; } // Check if all the fields are present in the DB.. foreach ($contenttype['fields'] as $field => $values) { if (!isset($tables[$tablename][$field])) { $myTable = $sm->listTableDetails($tablename); switch ($values['type']) { case 'text': case 'templateselect': case 'image': case 'file': $query = sprintf("ALTER TABLE `%s` ADD `%s` VARCHAR( 256 ) NOT NULL DEFAULT \"\";", $tablename, $field); $this->db->query($query); $output[] = "Added column <tt>" . $field . "</tt> to table <tt>" . $tablename . "</tt>."; break; case 'number': $query = sprintf("ALTER TABLE `%s` ADD `%s` DECIMAL(18,9) NOT NULL DEFAULT 0;", $tablename, $field); $this->db->query($query); $output[] = "Added column <tt>" . $field . "</tt> to table <tt>" . $tablename . "</tt>."; break; case 'html': case 'textarea': case 'video': $query = sprintf("ALTER TABLE `%s` ADD `%s` TEXT NOT NULL DEFAULT \"\";", $tablename, $field); $this->db->query($query); $output[] = "Added column <tt>" . $field . "</tt> to table <tt>" . $tablename . "</tt>."; break; case 'datetime': case 'date': $query = sprintf("ALTER TABLE `%s` ADD `%s` DATETIME", $tablename, $field); $this->db->query($query); $output[] = "Added column <tt>" . $field . "</tt> to table <tt>" . $tablename . "</tt>."; break; case 'slug': case 'id': case 'datecreated': case 'datechanged': case 'username': case 'divider': // These are the default columns. Don't try to add these. break; default: $output[] = "Type <tt>" . $values['type'] . "</tt> is not a correct field type for field <tt>{$field}</tt> in table <tt>{$tablename}</tt>."; } } } } return $output; }
/** * Imports the schema in the database * * @throw \PDOException if schema already imported */ public function importSchema() { $sm = new \Doctrine\DBAL\Schema\Schema(); $user = $sm->createTable("user"); $user->addColumn("id", "integer", array("unsigned" => true, "autoincrement" => true)); $user->addColumn("email", "string", array("length" => 255)); $user->setPrimaryKey(array("id")); $url = $sm->createTable("url"); $url->addColumn("id", "integer", array("unsigned" => true, "autoincrement" => true)); $url->addColumn("user_id", "integer", array("unsigned" => true, "notnull" => false)); $url->addColumn("url", "string", array("length" => 1024)); $url->addColumn("created_at", "datetime"); $url->addForeignKeyConstraint($user, array("user_id"), array("id"), array("onUpdate" => "CASCADE")); $url->setPrimaryKey(array("id")); $redirect = $sm->createTable("redirect"); $redirect->addColumn("id", "integer", array("unsigned" => true, "autoincrement" => true)); $redirect->addColumn("url_id", "integer", array("unsigned" => true)); $redirect->addForeignKeyConstraint($url, array("url_id"), array("id"), array("onUpdate" => "CASCADE")); $redirect->addColumn("created_at", "datetime"); $redirect->setPrimaryKey(array("id")); $queries = $sm->toSql($this->db->getDatabasePlatform()); // get queries to create this schema. foreach ($queries as $query) { $this->db->query($query); } }
$name_table->addColumn("fname", "string", array("length" => 32)); $name_table->addColumn("middle_initial", "string", array("length" => 32)); $name_table->addColumn("lname", "string", array("length" => 32)); $name_table->setPrimaryKey(array("id")); $cities_table = $schema->createTable('world_cities'); $cities_table->addColumn("geonameid", "integer", array("unsigned" => true)); $cities_table->addColumn("name", 'string', array('length' => 200)); $cities_table->addColumn("latitude", "decimal", array()); $cities_table->addColumn("longitude", "decimal", array()); $cities_table->addColumn("country_code", "string", array('length' => 3)); $cities_table->addColumn("time_zone", "string", array('length' => 40)); $country_codes = $schema->createTable('countries'); $country_codes->addColumn("name", "string", array("length" => 200)); $country_codes->addColumn("code", 'string', array('length' => 3)); # get queries to apply $create_schema = $schema->toSql($connection->getDatabasePlatform()); // get queries to create this schema. $drop_schema = $schema->toDropSql($connection->getDatabasePlatform()); // get queries to safely delete this schema. $connection->executeQuery($drop_schema[0]); $connection->executeQuery($drop_schema[1]); $connection->executeQuery($drop_schema[2]); $connection->executeQuery($create_schema[0]); $connection->executeQuery($create_schema[1]); $connection->executeQuery($create_schema[2]); $output->writeLn('Finished Running <info>Database Setup</info>'); }); //--------------------------------------------------------------------- // Build pear // //--------------------------------------------------------------------
/** * @param $tableName */ private function createShema($tableName) { $schema = new \Doctrine\DBAL\Schema\Schema(); $schemaManager = $this->database->getSchemaManager(); if (!$schemaManager->tablesExist($tableName)) { $referenceKey = self::REFERENCEKEY; $table = $schema->createTable($tableName); $reference = $table->addColumn("{$referenceKey}", "string"); $table->setPrimaryKey(array("{$referenceKey}")); $table->addUniqueIndex(array("{$referenceKey}")); $table->addColumn(self::CONTENTKEY, "text"); $sql = $schema->toSql($this->database->getDatabasePlatform()); foreach ($sql as $query) { $this->database->executeQuery($query); } } }
public function testSchema() { exit; $model = app::get('base')->model('setting'); $filter = ['app|than' => 2, 'app|lthan' => 2, 'app|nequal' => 2, 'app|notin' => array(1, 2, 3, 4), 'app|in' => array(1, 2, 3, 4), 'app|between' => [1, 10], 'app|has' => 'asdk']; echo $model->_filter($filter); exit; $filter = array('key' => 'aa4c2040d3220c132cf2fed9e31fb9ce324e137a', 'app' => 'kkk'); var_dump(app::get('base')->model('setting')->getList('app, key', $filter)); exit; $db = app::get('system')->database(); $db->exec('set SESSION autocommit=1;'); $db->exec('set @msgID = -1;'); var_dump($db->executeQuery('select @msgID')->fetchColumn()); exit; var_dump(with(new base_application_dbtable())->getAppTableNames('base')); exit; var_dump(url::to('/api')); exit; //$schema = new base_application_dbtable; //$dbinfo = $schema->detect($this->app,$this->table_name())->load(); if ($db->getSchemaManager()->tablesExist($real_table_name)) { $db->getSchemaManager()->dropTable($real_table_name); } //$appId = $this->target_app->app_id; $appId = 'syscategory'; $db = app::get($appId)->database(); $schema = new \Doctrine\DBAL\Schema\Schema(); //$table = $schema->createTable($this->real_table_name()); $table = $schema->createTable('syscategory_brand'); //$define = $this->load(); //$define = with(new base_application_dbtable)->detect($appId, 'brand')->load(); $define = $this->define; // 建立字段 $options['precision'] = 20; $options['scale'] = 3; $options['length'] = 0; $options['unsigned'] = 10; $options['fixed'] = true; // $options['unique'] = true; //$realType = $columnDefine['realtype']; $realType = 'integer'; $table->addColumn($columnName, $realType, $options); // 建立主键 if ($define['primary']) { $table->setPrimaryKey($define['primary']); } // 建立索引 if ($define['index']) { foreach ($define['index'] as $indexName => $indexDefine) { if (strtolower($indexDefine['prefix']) == 'unique') { $table->addUniqueIndex($indexDefine['columns'], $indexName); } else { $table->addIndex($indexDefine['columns'], $indexName); } } } $platform = $db->getDatabasePlatform(); $queries = $schema->toSql($platform); var_dump($queries); exit; }
function database() { //检测mysql函数库是否可用 $rst = function_exists('mysql_connect') && function_exists('mysql_get_server_info'); echo $rst ? app::get('dev')->_(" MySQL函数库可用...") . "\n" : app::get('dev')->_('MySQL函数库未安装...') . "\n"; //检测mysql数据库连接 if (!$rst) { echo app::get('dev')->_(" MySQL函数库连接出错..."); } else { $username = config::get('database.username'); $host = config::get('database.host'); $password = config::get('database.password'); $database = config::get('database.database'); $rst = false; if (!is_null($host)) { if (!is_null($password)) { $rs = mysql_connect($host, $username, $password); } elseif (!is_null($username)) { $rs = mysql_connect($host, $username); } else { $rs = mysql_connect($host); } $db_ver = mysql_get_server_info($rs); } else { $sock = get_cfg_var('mysql.default_socket'); if (PHP_OS != 'WINNT' && file_exists($sock) && is_writable($sock)) { $host = $sock; } else { $host = ini_get('mysql.default_host'); $port = ini_get('mysql.default_port'); if (!$host) { $host = '127.0.0.1'; } if (!$port) { $port = 3306; } $host = $host . ':' . $port; } } if (!$db_ver) { if (substr($host, 0, 1) == '/') { $fp = @fsockopen("unix://" . $host); } else { if ($p = strrpos($host, ':')) { $port = substr($host, $p + 1); $host = substr($host, 0, $p); } else { $port = 3306; } $fp = @fsockopen("tcp://" . $host, $port, $errno, $errstr, 2); } if (!$fp) { $db_ver = '无法连接'; } else { fwrite($fp, "\n"); $db_ver = fread($fp, 20); fclose($fp); if (preg_match('/([2-8]\\.[0-9\\.]+)/', $db_ver, $match)) { $db_ver = $match[1]; $rst = version_compare($db_ver, '4.0', '>='); } else { $db_ver = '无法识别'; } } } else { $rst = version_compare($db_ver, '4.1', '>='); } if ($db_ver == '无法连接') { $error_msg = ' Mysql数据库无法连接...'; } elseif ($db_ver == '无法识别') { $error_msg = ' Mysql数据库版本无法识别...'; } else { $error_msg = ' Mysql数据库版本是' . $db_ver . ',如果版本过低,请使用高于4.1的版本...'; } echo app::get('dev')->_($error_msg) . "\n"; } //检测mysql数据库是否可写可读 $db = db::connection(); if ($db->getSchemaManager()->tablesExist('test')) { $db->getSchemaManager()->dropTable('test'); } //$db->exec('drop table if exists test')."\n"; $schema = new \Doctrine\DBAL\Schema\Schema(); $table = $schema->createTable('test'); $table->addColumn('id', 'integer', ['unsigned' => true, 'notnull' => true, 'autoincrement' => true]); $table->addColumn('test', 'string', ['fixed' => true, 'notnull' => true]); $table->setPrimaryKey(['id']); $sql = current($schema->toSql($db->getDatabasePlatform())); $db->exec($sql); if ($db->getSchemaManager()->tablesExist('test')) { echo app::get('dev')->_(" Mysql创建测试表正常...") . "\n"; } else { echo app::get('dev')->_(" Mysql创建测试表不正常...") . "\n"; } $db->createQueryBuilder()->insert('test')->values(['id' => 1, 'test' => 'test'])->execute(); if ($db->executeQuery('select id from test where id=1')->fetchColumn() == 1) { echo app::get('dev')->_(" Mysql插入数据正常...") . "\n"; echo app::get('dev')->_(" Mysql读取数据正常...") . "\n"; } else { echo app::get('dev')->_(" Mysql插入数据不正常...") . "\n"; echo app::get('dev')->_(" Mysql读取数据不正常...") . "\n"; } $db->getSchemaManager()->dropTable('test'); }
public function parse_table($formid, $add_fields) { $tname = self::tname("form_data_" . $formid); $conn = Schema::getConnection(); $manager = $conn->getDoctrineSchemaManager(); $exist = $manager->tablesExist($tname); $schema = new \Doctrine\DBAL\Schema\Schema(); $table = $schema->createTable($tname); $table->addColumn("id", "bigint", array("unsigned" => true, 'autoincrement' => true, 'comment' => '自动编号')); $table->addColumn("user_id", "bigint", array("unsigned" => true, 'comment' => '用户ID')); $table->addColumn("form_id", "bigint", array("unsigned" => true, 'comment' => '外键ID')); $comment = $add_fields['comment']; foreach ($add_fields['fields'] as $key => $value) { $leipiplugins = $value['leipiplugins']; if ($leipiplugins == 'textarea' || $leipiplugins == 'listctrl') { $table->addColumn($value['name'], "text", array("notnull" => true, 'comment' => $comment[$value['name']])); } else { if ($leipiplugins == 'checkboxs') { $table->addColumn($value['name'], "smallint", array("unsigned" => true, 'comment' => $comment[$value['name']])); } else { $table->addColumn($value['name'], "string", array("length" => 255, 'comment' => $comment[$value['name']])); } } } $table->addColumn("created_at", "datetime", array("default" => 'CURRENT_TIMESTAMP', 'onUpdate' => 'CURRENT_TIMESTAMP', 'comment' => '创建时间')); $table->addColumn("update_at", "datetime", array('comment' => '修改时间')); $table->addIndex(['user_id']); $table->addIndex(['form_id']); $table->setPrimaryKey(array("id")); $form = $manager->listTableDetails(config('database.connections.mysql.prefix') . 'form'); $table->addForeignKeyConstraint($form, ['form_id'], ['id']); $user = $manager->listTableDetails(config('database.connections.mysql.prefix') . 'user'); $table->addForeignKeyConstraint($user, ['user_id'], ['id']); if ($exist === false) { $queries = $schema->toSql($manager->getDatabasePlatform()); $conn = DB::connection(); try { $conn->beginTransaction(); foreach ($queries as $key => $query) { $query = str_replace('DATETIME', 'timestamp', $query); $conn->statement($query); } $conn->commit(); } catch (\Illuminate\Database\QueryException $exc) { Log::error($exc->getMessage()); $conn->rollBack(); return false; } return true; } else { $platform = $manager->getDatabasePlatform(); $namespaces = array(); if ($platform->supportsSchemas()) { $namespaces = $manager->listNamespaceNames(); } $sequences = array(); if ($platform->supportsSequences()) { $sequences = $manager->listSequences(); } $s = new \Doctrine\DBAL\Schema\Schema([$manager->listTableDetails($tname)], $sequences, $manager->createSchemaConfig(), $namespaces); $comparator = new \Doctrine\DBAL\Schema\Comparator(); $schemaDiff = $comparator->compare($s, $schema); $queries = $schemaDiff->toSql($manager->getDatabasePlatform()); if (!$queries) { return true; } $conn = DB::connection(); try { $conn->beginTransaction(); foreach ($queries as $key => $query) { $query = str_replace('DATETIME', 'timestamp', $query); if (strstr($query, 'CHANGE update_at update_at DATETIME')) { continue; } $conn->statement($query); } $conn->commit(); } catch (\Illuminate\Database\QueryException $exc) { Log::error($exc->getMessage()); $conn->rollBack(); return false; } return true; } return false; }