PHP ActiveRecord::execute方法代码示例

 public function testInit()
 {
     @unlink('test.db');
     ActiveRecord::setDb(new PDO('sqlite:test.db'));
     ActiveRecord::execute("CREATE TABLE IF NOT EXISTS user (\n            id INTEGER PRIMARY KEY, \n            name TEXT, \n            password TEXT \n        );");
     ActiveRecord::execute("CREATE TABLE IF NOT EXISTS contact (\n            id INTEGER PRIMARY KEY, \n            user_id INTEGER, \n            email TEXT,\n            address TEXT\n        );");
 }

 /**
  * @depends testInit
  */
 public function testError()
 {
     try {
         ActiveRecord::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         ActiveRecord::execute('CREATE TABLE IF NOT EXISTS');
     } catch (Exception $e) {
         $this->assertInstanceOf('PDOException', $e);
         $this->assertEquals('HY000', $e->getCode());
         $this->assertEquals('SQLSTATE[HY000]: General error: 1 near "EXISTS": syntax error', $e->getMessage());
     }
 }

 public function actionUser()
 {
     $model = new InstallUserForm();
     $error = false;
     $success = 'n';
     $model->fullname = "Developer";
     $model->username = "dev";
     if (isset($_POST['InstallUserForm'])) {
         $model->attributes = $_POST['InstallUserForm'];
         if ($model->validate()) {
             ActiveRecord::execute("\n                set foreign_key_checks = 0;\n                UPDATE `p_user` SET\n                    `id` = '1',\n                    `email` = '-',\n                    `username` = '{$model->username}',\n                    `password` = '" . Helper::hash($model->password) . "',\n                    `last_login` = '2015-02-26 07:06:32',\n                    `is_deleted` = '0'\n                WHERE `id` = '1';\n                ");
             Installer::createIndexFile("running");
             $this->redirect(['/install/default/finish']);
         }
     }
     $this->renderForm('InstallUserForm', $model, ['error' => $error, 'success' => $success]);
 }

}
class Contact extends ActiveRecord
{
    public $table = 'contact';
    public $primaryKey = 'id';
    public $relations = array('user' => array(self::BELONGS_TO, 'User', 'user_id'));
}
ActiveRecord::setDb(new PDO('sqlite:test.db', null, null, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)));
try {
    ActiveRecord::execute("CREATE TABLE IF NOT EXISTS user (");
    ActiveRecord::execute("select * from aaa");
} catch (Exception $e) {
    var_export($e);
}
ActiveRecord::execute("CREATE TABLE IF NOT EXISTS user (\n\t\t\t\tid INTEGER PRIMARY KEY, \n\t\t\t\tname TEXT, \n\t\t\t\tpassword TEXT \n\t\t\t);");
ActiveRecord::execute("CREATE TABLE IF NOT EXISTS contact (\n\t\t\t\tid INTEGER PRIMARY KEY, \n\t\t\t\tuser_id INTEGER, \n\t\t\t\temail TEXT,\n\t\t\t\taddress TEXT\n\t\t\t);");
$user = new User();
$user->name = 'demo';
$user->password = md5('demo');
var_dump($user->insert());
$contact = new Contact();
$contact->address = 'test';
$contact->email = 'test1234456@domain.com';
$contact->user_id = $user->id;
var_dump($contact->insert());
/*
$contact = new Contact();
$contact->address = 'test';
$contact->email = 'test1234456@domain.com';
$contact->user_id = 2;
var_dump($contact->insert());

    return $params;
})->error(302, function ($path, $halt = false) {
    header("Location: {$path}", true, 302);
    $halt && exit;
})->error(405, function ($message) {
    header("Location: /posts", true, 302);
    die('405');
})->delete('/uninstall', function ($router) {
    @unlink('blog.db');
    $router->error(302, '/install');
})->get('/install', function ($router) {
    ActiveRecord::execute("CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY, name TEXT, email TEXT, password TEXT);");
    ActiveRecord::execute("CREATE TABLE IF NOT EXISTS category (id INTEGER PRIMARY KEY, name TEXT, count INTEGER);");
    ActiveRecord::execute("CREATE TABLE IF NOT EXISTS post (id INTEGER PRIMARY KEY, user_id INTEGER, category_id INTEGER, title TEXT,content TEXT, time INTEGER);");
    ActiveRecord::execute("CREATE TABLE IF NOT EXISTS comments (id INTEGER PRIMARY KEY, name TEXT, post_id INTEGER, comment_id INTEGER,content TEXT, time INTEGER);");
    ActiveRecord::execute("CREATE TABLE IF NOT EXISTS tag (id INTEGER PRIMARY KEY, name TEXT, count INTEGER);");
    ActiveRecord::execute("CREATE TABLE IF NOT EXISTS post_tag (id INTEGER PRIMARY KEY, post_id INTEGER, tag_id INTEGER);");
    $user = new User();
    $user->name = 'admin';
    $user->email = 'admin@example.com';
    $user->password = md5('admin');
    $user->insert();
    $category1 = (new Category(array('name' => 'Blog', 'count' => 0)))->insert();
    $category2 = (new Category(array('name' => 'PHP', 'count' => 0)))->insert();
    $post = (new Post(array('title' => 'REACT BASE FIDDLE (JSX)', 'content' => 'REACT BASE FIDDLE (JSX)', 'category_id' => $category1->id, 'time' => time())))->insert();
    $post->updateTag('PHP,Blog')->updateCategory();
    (new Comment(array('name' => 'admin', 'post_id' => $post->id, 'comment_id' => 0, 'content' => 'Test Comment', 'time' => time())))->insert();
    $router->error(302, '/posts', true);
})->get('/user/:userid/post', array(new PostController(), 'listall'))->get('/tag/:tagid/post', array(new PostController(), 'listall'))->get('/category/:categoryid/post', array(new PostController(), 'listall'))->get('/', array(new PostController(), 'listall'))->get('/posts', array(new PostController(), 'listall'))->get('/post/create', array(new PostController(), 'create'), 'auth')->post('/post/create', array(new PostController(), 'create'), 'auth')->get('/post/:id/delete', array(new PostController(), 'delete'), 'auth')->get('/post/:id/edit', array(new PostController(), 'edit'), 'auth')->post('/post/:id/edit', array(new PostController(), 'edit'), 'auth')->get('/post/:id/view', array(new PostController(), 'view'))->get('/:page', function ($page) {
    MicroTpl::render('web/' . $page . '.html', array('page' => $page), 'web/layout.html');
})->execute(array());

//.........这里部分代码省略.........
  KEY `{{nfy_messages}}_subscription_id_idx` (`subscription_id`),
  CONSTRAINT `p_nfy_messages_ibfk_2` FOREIGN KEY (`subscription_id`) REFERENCES `p_nfy_subscriptions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `p_nfy_subscriptions`;
CREATE TABLE `p_nfy_subscriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `queue_id` varchar(255) NOT NULL,
  `label` varchar(255) DEFAULT NULL,
  `subscriber_id` int(11) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `{{nfy_subscriptions}}_queue_id_subscriber_id_idx` (`queue_id`,`subscriber_id`),
  KEY `{{nfy_subscriptions}}_queue_id_idx` (`queue_id`),
  KEY `{{nfy_subscriptions}}_subscriber_id_idx` (`subscriber_id`),
  KEY `{{nfy_subscriptions}}_is_deleted_idx` (`is_deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `p_nfy_subscription_categories`;
CREATE TABLE `p_nfy_subscription_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscription_id` int(11) NOT NULL,
  `category` varchar(255) NOT NULL,
  `is_exception` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `{{nfy_subscription_categories}}_subscription_id_category_idx` (`subscription_id`,`category`),
  KEY `{{nfy_subscription_categories}}_subscription_id_idx` (`subscription_id`),
  CONSTRAINT `p_nfy_subscription_categories_ibfk_2` FOREIGN KEY (`subscription_id`) REFERENCES `p_nfy_subscriptions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



DROP TABLE IF EXISTS `p_role`;
CREATE TABLE `p_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `role_name` varchar(255) NOT NULL,
  `role_description` varchar(255) NOT NULL,
  `menu_path` varchar(255) DEFAULT NULL,
  `home_url` varchar(255) DEFAULT NULL,
  `repo_path` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `p_todo`;
CREATE TABLE `p_todo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(30) NOT NULL DEFAULT 'todo',
  `note` text NOT NULL,
  `options` text NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `p_todo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `p_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `p_user`;
CREATE TABLE `p_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `email` varchar(255) NOT NULL COMMENT 'E-Mail',
  `username` varchar(255) NOT NULL COMMENT 'Username',
  `password` varchar(255) NOT NULL COMMENT 'Password',
  `last_login` datetime DEFAULT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `p_user_role`;
CREATE TABLE `p_user_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_id` int(11) DEFAULT NULL COMMENT 'User ID',
  `role_id` int(11) DEFAULT NULL,
  `is_default_role` enum('Yes','No') NOT NULL DEFAULT 'No',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `p_user_role_ibfk_5` FOREIGN KEY (`role_id`) REFERENCES `p_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `p_user_role_ibfk_7` FOREIGN KEY (`user_id`) REFERENCES `p_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `p_role` (`id`, `role_name`, `role_description`, `menu_path`, `home_url`, `repo_path`) VALUES
(1,\t'dev',\t'IT - Developer',\t'',\t'',\t'');

INSERT INTO `p_user_role` (`id`, `user_id`, `role_id`, `is_default_role`) VALUES
(1,\t1,\t1,\t'Yes');

INSERT INTO `p_user` (`id`, `email`, `username`, `password`, `last_login`, `is_deleted`) VALUES
(1,\t'-',\t'dev',\t'{{{password}}}',\tnow(),\t0);
                
EOF;
        $sql = str_replace("{{{password}}}", Helper::hash('dev'), $sql);
        Yii::import('application.components.model.*');
        ActiveRecord::execute($sql);
    }