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;
}
