1
0

table.class.php 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437
  1. <?php
  2. namespace Juju\PDO;
  3. require_once(realpath(dirname(__DIR__).'/pdo.class.php'));
  4. use Juju\{PDO, PDO\Transaction};
  5. class Table {
  6. const COLUMN = 'column';
  7. const INDEX = 'index';
  8. const FOREIGN_KEY = 'foreignkey';
  9. private $pdo;
  10. private $name;
  11. private $exists;
  12. private $primaryKey;
  13. private $columns;
  14. private $index;
  15. private $foreignKey;
  16. private $columns_renamed;
  17. private $columns_removed;
  18. private $index_removed;
  19. private $foreignKey_removed;
  20. public function __construct($pdo, string $name){
  21. if($pdo instanceof PDO || $pdo instanceof Transaction){
  22. $this->pdo = $pdo;
  23. $this->name = $name;
  24. $this->rollback();
  25. }else{
  26. throw new \Exception("Invalid pdo argument");
  27. }
  28. }
  29. public function __get($name){
  30. switch($name){
  31. case 'exists':case 'name':case 'columns':
  32. return $this->$name;
  33. break;
  34. default:
  35. throw new \Exception("Invalid property {$name}");
  36. }
  37. }
  38. public function exists(){
  39. $pdo = $this->pdo;
  40. try{
  41. $count = $pdo->exec("select count(1) from `{$this->name}` limit 1");
  42. $this->exists = $count > 0;
  43. }catch(\Exception $e){
  44. $this->exists = false;
  45. }
  46. return $this->exists;
  47. }
  48. public function describe(){
  49. $this->columns = [];
  50. $this->primaryKey = [];
  51. $this->index = [];
  52. $this->foreignKeys = [];
  53. $this->columns_renamed = [];
  54. $this->columns_removed = [];
  55. $this->index_removed = [];
  56. $this->foreignKey_removed = [];
  57. if($this->exists){
  58. $pdo = $this->pdo;
  59. $query = $pdo->query("show create table `{$this->name}`");
  60. $sql = $query->fetch()['create table'];
  61. $query->closeCursor();
  62. if(preg_match_all('/ PRIMARY KEY \(((?:`[^`]+`,?)+)\)/s', $sql, $primaryKey, PREG_SET_ORDER) > 0){
  63. $this->primaryKey = array_map(
  64. function($item){
  65. return preg_replace('/`([^`]+)`/', '\\1', $item);
  66. },
  67. array_merge(
  68. ...array_map(
  69. function($item){
  70. return explode(',', $item[1]);
  71. }, $primaryKey
  72. )
  73. )
  74. );
  75. }
  76. unset($primaryKey);
  77. if(preg_match_all('/ ((?:UNIQUE)? ?KEY) `([^`]+)` \(((?:`[^`]+`,?)+)\)/s', $sql, $indexes, PREG_SET_ORDER) > 0){
  78. $this->indexes = array_reduce($indexes, function($indexes, $item){
  79. $indexes[$item[2]] = [
  80. 'unique'=> $item[1] == 'UNIQUE KEY',
  81. 'columns'=> array_map(function($item){
  82. return preg_replace('/`([^`]+)`/', '\\1', $item);
  83. }, explode(',', $item[3])),
  84. 'dirty'=> false
  85. ];
  86. return $indexes;
  87. });
  88. }
  89. unset($indexes);
  90. if(preg_match_all('/ CONSTRAINT `([^`]+)` FOREIGN KEY \(((?:`[^`]+`(?:, )?)+)\) REFERENCES `([^`]+)` \(((?:`[^`]+`(?:, )?)+)\)/s', $sql, $foreignKeys, PREG_SET_ORDER, 0) > 0){
  91. $this->foreignKeys = array_reduce($foreignKeys, function($foreignKeys, $item) use($sql){
  92. $columns = explode(',', $item[2]);
  93. $matches = explode(',', $item[4]);
  94. foreach($columns as $key => &$column){
  95. $column = [
  96. preg_replace('/`([^`]+)`/', '\\1', $column),
  97. preg_replace('/`([^`]+)`/', '\\1', $matches[$key])
  98. ];
  99. }
  100. $foreignKeys[$item[1]] = [
  101. 'references'=> $item[3],
  102. 'columns'=> $columns,
  103. 'dirty'=> false
  104. ];
  105. return $foreignKeys;
  106. });
  107. }
  108. unset($foreignKeys);
  109. if(preg_match_all('/ `([^`]+)` ([^\(]+\([^\)]+\))[^,\n]*,?/s', $sql, $columns, PREG_SET_ORDER) > 0){
  110. foreach($columns as $column){
  111. $default = null;
  112. $line = $column[0];
  113. $line .= substr($line, -1, 1) === ',' ? '' : ',';
  114. if(preg_match('/ `[^`]+` [^\(]+\([^\)]+\)[^D$]+DEFAULT (?!NULL)(.+)(?: AUTO_INCREMENT|UNIQUE|KEY|PRIMARY|COMMENT|COLUMN_FORMAT|STORAGE|REFERENCES|,)/u', $line, $match) == 1){
  115. $default = preg_replace("/'(.+)'/", "\\1", $match[1]);
  116. }
  117. $this->column(
  118. $column[1], // name
  119. $column[2], // type
  120. $default, // default
  121. preg_match('/`[^`]+` [^\(]+\([^\)]+\) NOT NULL/', $column[0]) === 1, // null
  122. preg_match('/ `[^`]+` [^\(]+\([^\)]+\)[^A$]+AUTO_INCREMENT,?/u', $line) == 1 // increment
  123. );
  124. $this->columns[$column[1]]['dirty'] = false;
  125. }
  126. }
  127. unset($columns);
  128. }
  129. }
  130. public function commit(){
  131. $pdo = $this->pdo;
  132. if(!$this->exists){
  133. $columns = '';
  134. foreach($this->columns as $name => $column){
  135. $columns .= "{$pdo->stringColumn($name, $column)},";
  136. }
  137. if(count($columns) > 0){
  138. $columns = rtrim($columns, ',');
  139. }
  140. $pk = $this->primaryKey();
  141. if(count($pk)){
  142. $pk = ", primary key (".implode(',', $pk).")";
  143. }
  144. $index = '';
  145. foreach($this->index as $name => $idx){
  146. $index .=", {$pdo->stringIndex($name, $idx)}";
  147. }
  148. $fk = '';
  149. foreach($this->foreignKeys as $name => $k){
  150. $fk .= ", {$pdo->stringForeignKey($name, $k)}";
  151. }
  152. $sql = "create table `{$this->name}` ({$columns}{$pk}{$index}{$fk})";
  153. $count = $pdo->exec($sql);
  154. if($count === false){
  155. throw $pdo->getError();
  156. }
  157. if(!$this->exists()){
  158. throw new \Exception("Unable to create table {$this->name}. Generated SQL: {$sql}");
  159. }
  160. unset($sql);
  161. $this->exists();
  162. }else{
  163. if(count($this->columns_renamed) > 0){
  164. $sql = "alter table `{$this->name}`";
  165. foreach($this->columns_renamed as $new => $old){
  166. if(isset($this->columns[$new])){
  167. $sql .= " change `{$old}` {$pdo->stringColumn($new, $this->columns[$new])}";
  168. }
  169. }
  170. $sql = rtrim($sql, ',');
  171. if($pdo->exec($sql) === false){
  172. throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
  173. }
  174. }
  175. $columns = array_filter($this->columns, function($item){
  176. return (bool)$item['dirty'];
  177. });
  178. if(count($columns) + count($this->columns_removed) > 0){
  179. $sql = "alter table `{$this->name}`";
  180. foreach($this->index_removed as $name){
  181. if($pdo->exec("show index from `{$this->name}` where KEY_name = ".$pdo->quote($name)) > 0){
  182. $sql .= " drop index {$name},";
  183. }
  184. }
  185. foreach($this->foreignKey_removed as $name){
  186. $sql .= " drop foreign key {$name},";
  187. }
  188. foreach($this->columns_removed as $name){
  189. $sql .= " drop column {$name},";
  190. }
  191. if(count($columns) > 0){
  192. $sql .= " add (";
  193. foreach($columns as $name => $column){
  194. $sql .= "{$pdo->stringColumn($name, $column)},";
  195. }
  196. $sql = rtrim($sql , ',') . "),";
  197. }
  198. unset($columns);
  199. $sql = rtrim($sql, ',');
  200. if($pdo->exec($sql) === false){
  201. throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
  202. }
  203. }
  204. $sql = '';
  205. if($pdo->exec("show index from `{$this->name}` where KEY_name = 'PRIMARY'") > 0){
  206. $sql .= " drop primary key,";
  207. }
  208. if(count($this->primaryKey) > 0){
  209. $sql .= " add primary key (".implode(',', $this->primaryKey)."),";
  210. }
  211. foreach(array_filter($this->index, function($item){
  212. return (bool)$item['dirty'];
  213. }) as $name => $idx){
  214. $sql .= " add {$pdo->stringIndex($name, $idx)},";
  215. }
  216. if(count($sql) > 0){
  217. $sql = "alter table `{$this->name}`".rtrim($sql, ',');
  218. if($pdo->exec($sql) === false){
  219. throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
  220. }
  221. }
  222. $foreignKeys =array_filter($this->foreignKeys, function($item){
  223. return (bool)$item['dirty'];
  224. });
  225. if(count($foreignKeys) > 0){
  226. $sql = "alter table `{$this->name}`";
  227. foreach($foreignKeys as $name => $fk){
  228. $sql .= " add {$pdo->stringForeignKey($name, $fk)},";
  229. }
  230. $sql = rtrim($sql, ',');
  231. if($pdo->exec($sql) === false){
  232. throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
  233. }
  234. }
  235. unset($foreignKeys);
  236. }
  237. $this->describe();
  238. }
  239. public function rollback(){
  240. $this->exists();
  241. $this->describe();
  242. }
  243. public function rename(string $type, string $old, string $new){
  244. switch($type){
  245. case self::COLUMN:
  246. if(isset($this->columns[$old])){
  247. if(!isset($this->columns[$new])){
  248. $this->columns[$new] = $this->columns[$old];
  249. unset($this->columns[$old]);
  250. if(isset($this->columns_renamed[$old])){
  251. unset($this->columns_renamed[$old]);
  252. }
  253. $this->columns_renamed[$new] = $old;
  254. }else{
  255. throw new \Exception("{$this->name}.{$new} already exists. Unable to rename {$this->name}.{$old}");
  256. }
  257. }
  258. break;
  259. default:
  260. throw new \Exception("Renaming {$type} is not implemented");
  261. }
  262. return $this;
  263. }
  264. public function drop(string $type = null, string $name = null){
  265. if(!is_null($type)){
  266. switch($type){
  267. case self::COLUMN:
  268. if(isset($this->columns[$name])){
  269. if($this->exists){
  270. $this->columns_removed = array_merge($this->columns_removed, [$name]);
  271. }
  272. unset($this->columns[$name]);
  273. }
  274. if(isset($this->columns_renamed[$name])){
  275. unset($this->columns_renamed[$name]);
  276. }
  277. break;
  278. case self::INDEX:
  279. if(isset($this->index[$name])){
  280. if($this->exists){
  281. $this->index_removed = array_merge($this->index_removed, [$name]);
  282. }
  283. unset($this->index[$name]);
  284. }
  285. break;
  286. case self::FOREIGN_KEY:
  287. if(isset($this->foreignKey[$name])){
  288. if($this->exists){
  289. $this->foreignKey_removed = array_merge($this->foreignKey_removed, [$name]);
  290. }
  291. unset($this->foreignKey[$name]);
  292. }
  293. break;
  294. default:
  295. throw new \Exception("Cannot drop {$name}. Unknown type {$type}");
  296. }
  297. }else{
  298. if($this->exists){
  299. $this->pdo->exec("drop table `{$this->name}`");
  300. }
  301. $this->rollback();
  302. }
  303. return $this;
  304. }
  305. public function column(string $name, string $type = null, $default = null, bool $null = false, bool $increment = false){
  306. if(!is_null($type)){
  307. $new = [
  308. 'type'=> $type,
  309. 'default'=> $default,
  310. 'null'=> $null,
  311. 'increment'=> $increment,
  312. 'dirty'=> false
  313. ];
  314. if(isset($this->columns[$name])){
  315. $old = $this->columns[$name];
  316. foreach($new as $key => $val){
  317. if($key != 'dirty' && $old[$key] !== $val){
  318. $new['dirty'] = true;
  319. break;
  320. }
  321. }
  322. }else{
  323. $new['dirty'] = true;
  324. }
  325. $this->columns[$name] = $new;
  326. return $this;
  327. }else{
  328. return isset($this->columns[$name]) ? $this->columns[$name] : null;
  329. }
  330. }
  331. public function index(string $name, array $columns = null, bool $unique = false){
  332. if(!is_null($columns)){
  333. foreach($columns as $column){
  334. if(!isset($this->columns[$column])){
  335. throw new \Exception("Can't add index. Column {$this->name}.{$column} doesn't exist");
  336. }
  337. }
  338. $this->index[$name] = [
  339. 'columns'=> $columns,
  340. 'unique'=> $unique,
  341. 'dirty'=>true
  342. ];
  343. return $this;
  344. }else{
  345. return isset($this->index[$name]) ? $this->index[$name] : null;
  346. }
  347. }
  348. public function addToIndex(string $name, string $column){
  349. if(!isset($this->index[$name])){
  350. throw new \Exception("Can't add column to index. Index {$this->name}.{$name} doesn't exist");
  351. }
  352. if(!isset($this->columns[$column])){
  353. throw new \Exception("Can't add column to index. Column {$this->name}.{$column} doesn't exist");
  354. }
  355. $this->index[$name]['columns'] = array_merge($this->index[$name]['columns'], [$column]);
  356. }
  357. public function foreignKey(string $name, string $references = null, array $columns = []){
  358. if(!is_null($references)){
  359. $table = $this->pdo->table($references);
  360. if(!$table->exists){
  361. throw new \Exception("Can't create foreign key {$name}. Table {$references} does not exist.");
  362. }
  363. foreach($columns as $column){
  364. if(is_null($this->column($column[0]))){
  365. throw new \Exception("Can't create foreign key {$name}. Column {$this->name}.{$column[0]} does not exist");
  366. }
  367. if(is_null($table->column($column[1]))){
  368. throw new \Exception("Can't create foreign key {$name}. Column {$references}.{$column[1]} does not exist");
  369. }
  370. }
  371. $this->foreignKeys[$name] = [
  372. 'references'=> $references,
  373. 'columns'=> $columns,
  374. 'dirty'=> true
  375. ];
  376. return $this;
  377. }else{
  378. return isset($this->foreignKeys[$name]) ? $this->foreignKeys[$name] : null;
  379. }
  380. }
  381. public function primaryKey(...$columns){
  382. if(count($columns) > 0){
  383. foreach($columns as $column){
  384. if(!isset($this->columns[$column])){
  385. throw new \Exception("Can't add Primary key. Column {$this->name}.{$column} doesn't exist");
  386. }
  387. }
  388. $this->primaryKey = $columns;
  389. return $this;
  390. }else{
  391. return $this->primaryKey;
  392. }
  393. }
  394. public function insert(array $data){
  395. return $this->exists ? $this->pdo->exec("insert into `{$this->name}` {$this->pdo->stringSet($data)}") : 0;
  396. }
  397. public function update(array $data, array $filter = null){
  398. return $this->exists ? $this->pdo->exec("update `{$this->name}` {$this->pdo->stringSet($data)} {$this->pdo->stringFilter($filter)}") : 0;
  399. }
  400. public function delete(array $filter = null){
  401. return $this->exists ? $this->pdo->exec("delete from `{$this->name}` {$this->pdo->stringFilter($filter)}") : 0;
  402. }
  403. public function fetch(array $columns = null, array $filter = null, int $start = null, int $amount = null){
  404. $results = [];
  405. $this->each(function($row) use($results){
  406. $results[] = $row;
  407. }, $columns, $filter, $start, $amount);
  408. return $results;
  409. }
  410. public function each(callable $fn, array $columns = null, array $filter = null, int $start = null, int $amount = null){
  411. if($this->exists){
  412. $limit = '';
  413. if(!is_null($start) && !is_null($amount)){
  414. $limit .= " limit {$start}, {$amount}";
  415. }
  416. $columns = $columns ?? ['*'];
  417. $cols = '';
  418. foreach($columns as $column){
  419. $cols .= "{$column},";
  420. }
  421. $cols = rtrim($cols, ',');
  422. $query = $this->pdo->query("select {$cols} from `{$this->name}` {$this->pdo->stringFilter($filter)} {$limit}");
  423. while($row = $query->fetch()){
  424. $fn($row);
  425. }
  426. $query->closeCursor();
  427. }
  428. return $this;
  429. }
  430. public function count(array $filter = null){
  431. return $this->exists ? $this->pdo->exec("select 1 from `{$this->name}` {$this->pdo->stringFilter($filter)}") : 0;
  432. }
  433. }
  434. ?>