PageRenderTime 49ms CodeModel.GetById 19ms RepoModel.GetById 1ms app.codeStats 0ms

/tests/framework/db/schema/CSqliteTest.php

https://gitlab.com/RECITEC/yii
PHP | 336 lines | 281 code | 47 blank | 8 comment | 5 complexity | 9da9468497e9e84ccaf08b391a8c587c MD5 | raw file
  1. <?php
  2. Yii::import('system.db.CDbConnection');
  3. Yii::import('system.db.schema.sqlite.CSqliteSchema');
  4. class CSqliteTest extends CTestCase
  5. {
  6. /**
  7. * @var CDbConnection
  8. */
  9. private $db;
  10. public function setUp()
  11. {
  12. if(!extension_loaded('pdo') || !extension_loaded('pdo_sqlite'))
  13. $this->markTestSkipped('PDO and SQLite extensions are required.');
  14. $this->db=new CDbConnection('sqlite::memory:');
  15. $this->db->active=true;
  16. $this->db->pdoInstance->exec(file_get_contents(dirname(__FILE__).'/../data/sqlite.sql'));
  17. }
  18. public function tearDown()
  19. {
  20. $this->db->active=false;
  21. }
  22. public function testSchema()
  23. {
  24. $schema=$this->db->schema;
  25. $this->assertTrue($schema instanceof CDbSchema);
  26. $this->assertEquals($schema->dbConnection,$this->db);
  27. $this->assertTrue($schema->commandBuilder instanceof CDbCommandBuilder);
  28. $this->assertEquals('\'posts\'',$schema->quoteTableName('posts'));
  29. $this->assertEquals('"id"',$schema->quoteColumnName('id'));
  30. $this->assertTrue($schema->getTable('posts') instanceof CDbTableSchema);
  31. $this->assertTrue($schema->getTable('foo')===null);
  32. }
  33. public function testTable()
  34. {
  35. $table=$this->db->schema->getTable('posts');
  36. $this->assertTrue($table instanceof CDbTableSchema);
  37. $this->assertEquals('posts',$table->name);
  38. $this->assertEquals('\'posts\'',$table->rawName);
  39. $this->assertEquals('id',$table->primaryKey);
  40. $this->assertEquals(array('author_id'=>array('users','id')),$table->foreignKeys);
  41. $this->assertTrue($table->sequenceName==='');
  42. $this->assertEquals(5,count($table->columns));
  43. $this->assertTrue($table->getColumn('id') instanceof CDbColumnSchema);
  44. $this->assertTrue($table->getColumn('foo')===null);
  45. $this->assertEquals(array('id','title','create_time','author_id','content'),$table->columnNames);
  46. $table=$this->db->schema->getTable('orders');
  47. $this->assertEquals(array('key1','key2'),$table->primaryKey);
  48. $table=$this->db->schema->getTable('items');
  49. $this->assertEquals('id',$table->primaryKey);
  50. $this->assertEquals(array('col1'=>array('orders','key1'),'col2'=>array('orders','key2')),$table->foreignKeys);
  51. $table=$this->db->schema->getTable('types');
  52. $this->assertTrue($table->primaryKey===null);
  53. $this->assertTrue($table->foreignKeys===array());
  54. $this->assertTrue($table->sequenceName===null);
  55. $table=$this->db->schema->getTable('invalid');
  56. $this->assertNull($table);
  57. }
  58. public function testColumn()
  59. {
  60. $values=array
  61. (
  62. 'name'=>array('id', 'title', 'create_time', 'author_id', 'content'),
  63. 'rawName'=>array('"id"', '"title"', '"create_time"', '"author_id"', '"content"'),
  64. 'defaultValue'=>array(null, null, null, null, null),
  65. 'size'=>array(null, 128, null, null, null),
  66. 'precision'=>array(null, 128, null, null, null),
  67. 'scale'=>array(null, null, null, null, null),
  68. 'dbType'=>array('integer','varchar(128)','timestamp','integer','text'),
  69. 'type'=>array('integer','string','string','integer','string'),
  70. 'isPrimaryKey'=>array(true,false,false,false,false),
  71. 'isForeignKey'=>array(false,false,false,true,false),
  72. 'allowNull'=>array(false,false,false,false,true),
  73. );
  74. $this->checkColumns('posts',$values);
  75. $values=array
  76. (
  77. 'name'=>array('int_col', 'int_col2', 'char_col', 'char_col2', 'char_col3', 'char_col4', 'char_col5', 'float_col', 'float_col2', 'blob_col', 'numeric_col', 'time', 'bool_col', 'bool_col2', 'null_col', 'created_at'),
  78. 'rawName'=>array('"int_col"', '"int_col2"', '"char_col"', '"char_col2"', '"char_col3"', '"char_col4"', '"char_col5"', '"float_col"', '"float_col2"', '"blob_col"', '"numeric_col"', '"time"', '"bool_col"', '"bool_col2"', '"null_col"', '"created_at"'),
  79. 'defaultValue'=>array(null, 1, null, 'something', null, null, 'NULL', null, '1.23', null, '33.22', '123', null, true, null, null),
  80. 'size'=>array(null, null, 100, 100, null, 100, 100, 4, null, null, 5, null, null, null, null, null),
  81. 'precision'=>array(null, null, 100, 100, null, 100, 100, 4, null, null, 5, null, null, null, null, null),
  82. 'scale'=>array(null, null, null, null, null, null, null, 3, null, null, 2, null, null, null, null, null),
  83. 'dbType'=>array('int','integer','char(100)','varchar(100)','text','varchar(100)','varchar(100)','real(4,3)','double','blob','numeric(5,2)','timestamp','bool','boolean','integer','timestamp'),
  84. 'type'=>array('integer','integer','string','string','string','string','string','double','double','string','string','string','boolean','boolean','integer','string'),
  85. 'isPrimaryKey'=>array(false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false),
  86. 'isForeignKey'=>array(false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false),
  87. 'allowNull'=>array(false,true,false,true,true,true,true,false,true,true,true,true,false,true,true,false),
  88. );
  89. $this->checkColumns('types',$values);
  90. }
  91. protected function checkColumns($tableName,$values)
  92. {
  93. $table=$this->db->schema->getTable($tableName);
  94. foreach($values as $name=>$value)
  95. {
  96. foreach(array_values($table->columns) as $i=>$column)
  97. {
  98. $type1=gettype($column->$name);
  99. $type2=gettype($value[$i]);
  100. $this->assertTrue($column->$name===$value[$i], "$tableName.{$column->name}.$name is {$column->$name} ($type1), different from the expected {$value[$i]} ($type2).");
  101. }
  102. }
  103. }
  104. public function testCommandBuilder()
  105. {
  106. $schema=$this->db->schema;
  107. $builder=$schema->commandBuilder;
  108. $this->assertTrue($builder instanceof CDbCommandBuilder);
  109. $table=$schema->getTable('posts');
  110. $c=$builder->createInsertCommand($table,array('title'=>'test post','create_time'=>time(),'author_id'=>1,'content'=>'test content'));
  111. $this->assertEquals('INSERT INTO \'posts\' ("title", "create_time", "author_id", "content") VALUES (:yp0, :yp1, :yp2, :yp3)',$c->text);
  112. $c->execute();
  113. $this->assertEquals(6,$builder->getLastInsertId($table));
  114. $c=$builder->createCountCommand($table,new CDbCriteria);
  115. $this->assertEquals('SELECT COUNT(*) FROM \'posts\' \'t\'',$c->text);
  116. $this->assertEquals(6,$c->queryScalar());
  117. $c=$builder->createDeleteCommand($table,new CDbCriteria(array(
  118. 'condition'=>'id=:id',
  119. 'params'=>array('id'=>6))));
  120. $this->assertEquals('DELETE FROM \'posts\' WHERE id=:id',$c->text);
  121. $c->execute();
  122. $c=$builder->createCountCommand($table,new CDbCriteria);
  123. $this->assertEquals(5,$c->queryScalar());
  124. $c=$builder->createFindCommand($table,new CDbCriteria(array(
  125. 'select'=>'id, title',
  126. 'condition'=>'id=:id',
  127. 'params'=>array('id'=>5),
  128. 'order'=>'title',
  129. 'limit'=>2,
  130. 'offset'=>0)));
  131. $this->assertEquals('SELECT id, title FROM \'posts\' \'t\' WHERE id=:id ORDER BY title LIMIT 2',$c->text);
  132. $rows=$c->query()->readAll();
  133. $this->assertEquals(1,count($rows));
  134. $this->assertEquals('post 5',$rows[0]['title']);
  135. $c=$builder->createUpdateCommand($table,array('title'=>'new post 5'),new CDbCriteria(array(
  136. 'condition'=>'id=:id',
  137. 'params'=>array('id'=>5))));
  138. $c->execute();
  139. $c=$builder->createFindCommand($table,new CDbCriteria(array(
  140. 'select'=>'title',
  141. 'condition'=>'id=:id',
  142. 'params'=>array('id'=>5))));
  143. $this->assertEquals('new post 5',$c->queryScalar());
  144. $c=$builder->createSqlCommand('SELECT title FROM posts \'t\' WHERE id=:id',array(':id'=>3));
  145. $this->assertEquals('post 3',$c->queryScalar());
  146. $c=$builder->createUpdateCounterCommand($table,array('author_id'=>-2),new CDbCriteria(array('condition'=>'id=5')));
  147. $this->assertEquals('UPDATE \'posts\' SET "author_id"="author_id"-2 WHERE id=5',$c->text);
  148. $c->execute();
  149. $c=$builder->createSqlCommand('SELECT author_id FROM posts WHERE id=5');
  150. $this->assertEquals(1,$c->queryScalar());
  151. // test bind by position
  152. $c=$builder->createFindCommand($table,new CDbCriteria(array(
  153. 'select'=>'title',
  154. 'condition'=>'id=?',
  155. 'params'=>array(4))));
  156. $this->assertEquals('SELECT title FROM \'posts\' \'t\' WHERE id=?',$c->text);
  157. $this->assertEquals('post 4',$c->queryScalar());
  158. // another bind by position
  159. $c=$builder->createUpdateCommand($table,array('title'=>'new post 4'),new CDbCriteria(array(
  160. 'condition'=>'id=?',
  161. 'params'=>array(4))));
  162. $c->execute();
  163. $c=$builder->createSqlCommand('SELECT title FROM posts WHERE id=4');
  164. $this->assertEquals('new post 4',$c->queryScalar());
  165. // testCreateCriteria
  166. $c=$builder->createCriteria('column=:value',array(':value'=>'value'));
  167. $this->assertEquals('column=:value',$c->condition);
  168. $this->assertEquals(array(':value'=>'value'),$c->params);
  169. $c=$builder->createCriteria(array('condition'=>'column=:value','params'=>array(':value'=>'value')));
  170. $this->assertEquals('column=:value',$c->condition);
  171. $this->assertEquals(array(':value'=>'value'),$c->params);
  172. $c2=$builder->createCriteria($c);
  173. $this->assertTrue($c2!==$c);
  174. $this->assertEquals('column=:value',$c2->condition);
  175. $this->assertEquals(array(':value'=>'value'),$c2->params);
  176. // testCreatePkCriteria
  177. $c=$builder->createPkCriteria($table,1,'author_id>1');
  178. $this->assertEquals('\'posts\'."id"=1 AND (author_id>1)',$c->condition);
  179. $c=$builder->createPkCriteria($table,array(1,2));
  180. $this->assertEquals('\'posts\'."id" IN (1, 2)',$c->condition);
  181. $c=$builder->createPkCriteria($table,array());
  182. $this->assertEquals('0=1',$c->condition);
  183. $table2=$schema->getTable('orders');
  184. $c=$builder->createPkCriteria($table2,array('key1'=>1,'key2'=>2),'name=\'\'');
  185. $this->assertEquals('\'orders\'."key1"=1 AND \'orders\'."key2"=2 AND (name=\'\')',$c->condition);
  186. $c=$builder->createPkCriteria($table2,array(array('key1'=>1,'key2'=>2),array('key1'=>3,'key2'=>4)));
  187. $this->assertEquals('\'orders\'."key1"||\',\'||\'orders\'."key2" IN (1||\',\'||2, 3||\',\'||4)',$c->condition);
  188. // createColumnCriteria
  189. $c=$builder->createColumnCriteria($table,array('id'=>1,'author_id'=>2),'title=\'\'');
  190. $this->assertEquals('\'posts\'."id"=:yp0 AND \'posts\'."author_id"=:yp1 AND (title=\'\')',$c->condition);
  191. $c=$builder->createPkCriteria($table2,array());
  192. $this->assertEquals('0=1',$c->condition);
  193. }
  194. public function testResetSequence()
  195. {
  196. $max=$this->db->createCommand("SELECT MAX(id) FROM users")->queryScalar();
  197. $this->db->createCommand("DELETE FROM users")->execute();
  198. $this->db->createCommand("INSERT INTO users (username, password, email) VALUES ('user4','pass4','email4')")->execute();
  199. $max2=$this->db->createCommand("SELECT MAX(id) FROM users")->queryScalar();
  200. $this->assertEquals($max+1,$max2);
  201. $userTable=$this->db->schema->getTable('users');
  202. $this->db->createCommand("DELETE FROM users")->execute();
  203. $this->db->schema->resetSequence($userTable);
  204. $this->db->createCommand("INSERT INTO users (username, password, email) VALUES ('user4','pass4','email4')")->execute();
  205. $max=$this->db->createCommand("SELECT MAX(id) FROM users")->queryScalar();
  206. $this->assertEquals(1,$max);
  207. $this->db->createCommand("INSERT INTO users (username, password, email) VALUES ('user4','pass4','email4')")->execute();
  208. $max=$this->db->createCommand("SELECT MAX(id) FROM users")->queryScalar();
  209. $this->assertEquals(2,$max);
  210. $this->db->createCommand("DELETE FROM users")->execute();
  211. $this->db->schema->resetSequence($userTable,10);
  212. $this->db->createCommand("INSERT INTO users (username, password, email) VALUES ('user4','pass4','email4')")->execute();
  213. $max=$this->db->createCommand("SELECT MAX(id) FROM users")->queryScalar();
  214. $this->assertEquals(10,$max);
  215. $this->db->createCommand("INSERT INTO users (username, password, email) VALUES ('user4','pass4','email4')")->execute();
  216. $max=$this->db->createCommand("SELECT MAX(id) FROM users")->queryScalar();
  217. $this->assertEquals(11,$max);
  218. }
  219. public function testCheckIntegrity1()
  220. {
  221. $this->db->schema->checkIntegrity(false);
  222. $this->db->schema->checkIntegrity(true);
  223. }
  224. public function testCheckIntegrity2()
  225. {
  226. $this->db->schema->checkIntegrity(true);
  227. $this->assertEquals(0,$this->db->createCommand('SELECT COUNT(*) FROM profiles WHERE user_id=9999')->queryScalar());
  228. if(version_compare(PHP_VERSION,'5.3.0','>='))
  229. $this->setExpectedException('CDbException');
  230. $this->db->createCommand("INSERT INTO profiles (first_name,last_name,user_id) VALUES ('orphaned','profile',9999)")->execute();
  231. $this->assertEquals(1,$this->db->createCommand('SELECT COUNT(*) FROM profiles WHERE user_id=9999')->queryScalar());
  232. }
  233. public function testCheckIntegrity3()
  234. {
  235. $this->db->schema->checkIntegrity(false);
  236. $this->assertEquals(0,$this->db->createCommand('SELECT COUNT(*) FROM profiles WHERE user_id=9999')->queryScalar());
  237. $this->db->createCommand("INSERT INTO profiles (first_name,last_name,user_id) VALUES ('orphaned','profile',9999)")->execute();
  238. $this->assertEquals(1,$this->db->createCommand('SELECT COUNT(*) FROM profiles WHERE user_id=9999')->queryScalar());
  239. }
  240. public function testRenameTable()
  241. {
  242. $this->db->schema->refresh();
  243. $this->assertArrayHasKey('profiles',$this->db->schema->tables);
  244. $this->assertArrayHasKey('users',$this->db->schema->tables);
  245. $this->assertArrayNotHasKey('profiles_renamed',$this->db->schema->tables);
  246. $this->assertArrayNotHasKey('users_renamed',$this->db->schema->tables);
  247. $this->db->schema->refresh();
  248. $this->db->createCommand($this->db->schema->renameTable('profiles','profiles_renamed'))->execute();
  249. $this->db->createCommand($this->db->schema->renameTable('users','users_renamed'))->execute();
  250. $this->db->schema->refresh();
  251. $this->assertArrayNotHasKey('profiles',$this->db->schema->tables);
  252. $this->assertArrayNotHasKey('users',$this->db->schema->tables);
  253. $this->assertArrayHasKey('profiles_renamed',$this->db->schema->tables);
  254. $this->assertArrayHasKey('users_renamed',$this->db->schema->tables);
  255. }
  256. public function testMultipleInsert()
  257. {
  258. $builder=$this->db->getSchema()->getCommandBuilder();
  259. $tableName='types';
  260. $data=array(
  261. array(
  262. 'int_col'=>1,
  263. 'char_col'=>'char_col_1',
  264. 'char_col2'=>'char_col_2_1',
  265. 'float_col'=>1.1,
  266. 'bool_col'=>true,
  267. ),
  268. array(
  269. 'int_col'=>2,
  270. 'char_col'=>'char_col_2',
  271. 'float_col'=>2.2,
  272. 'bool_col'=>false,
  273. ),
  274. );
  275. $command=$builder->createMultipleInsertCommand($tableName,$data);
  276. $command->execute();
  277. $rows=$builder->dbConnection->createCommand('SELECT * FROM '.$builder->dbConnection->quoteTableName($tableName))->queryAll();
  278. $this->assertEquals(count($data),count($rows),'Records count miss matches!');
  279. foreach($rows as $rowIndex=>$row)
  280. foreach($row as $columnName=>$value)
  281. {
  282. $columnIndex=array_search($columnName,$data[$rowIndex],true);
  283. if($columnIndex==false)
  284. continue;
  285. $expectedValue=$data[$rowIndex][$columnIndex];
  286. $this->assertTrue($expectedValue==$value,"Value for column '{$columnName}' incorrect!");
  287. }
  288. }
  289. }