1
0

table.class.php 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475
  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. }else{
  144. $pk = '';
  145. }
  146. $index = '';
  147. foreach($this->index as $name => $idx){
  148. $index .=", {$pdo->stringIndex($name, $idx)}";
  149. }
  150. $fk = '';
  151. foreach($this->foreignKeys as $name => $k){
  152. $fk .= ", {$pdo->stringForeignKey($name, $k)}";
  153. }
  154. $sql = "create table `{$this->name}` ({$columns}{$pk}{$index}{$fk})";
  155. $count = $pdo->exec($sql);
  156. if($count === false){
  157. throw $pdo->getError();
  158. }
  159. if(!$this->exists()){
  160. throw new \Exception("Unable to create table {$this->name}. Generated SQL: {$sql}");
  161. }
  162. unset($sql);
  163. $this->exists();
  164. }else{
  165. if(count($this->columns_renamed) > 0){
  166. $sql = "alter table `{$this->name}`";
  167. foreach($this->columns_renamed as $new => $old){
  168. if(isset($this->columns[$new])){
  169. $sql .= " change `{$old}` {$pdo->stringColumn($new, $this->columns[$new])}";
  170. }
  171. }
  172. $sql = rtrim($sql, ',');
  173. if($pdo->exec($sql) === false){
  174. throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
  175. }
  176. }
  177. $columns = array_filter($this->columns, function($item){
  178. return (bool)$item['dirty'];
  179. });
  180. if(count($columns) + count($this->columns_removed) > 0){
  181. $sql = "alter table `{$this->name}`";
  182. foreach($this->index_removed as $name){
  183. if($pdo->exec("show index from `{$this->name}` where KEY_name = ".$pdo->quote($name)) > 0){
  184. $sql .= " drop index {$name},";
  185. }
  186. }
  187. foreach($this->foreignKey_removed as $name){
  188. $sql .= " drop foreign key {$name},";
  189. }
  190. foreach($this->columns_removed as $name){
  191. $sql .= " drop column {$name},";
  192. }
  193. if(count($columns) > 0){
  194. $sql .= " add (";
  195. foreach($columns as $name => $column){
  196. $sql .= "{$pdo->stringColumn($name, $column)},";
  197. }
  198. $sql = rtrim($sql , ',') . "),";
  199. }
  200. unset($columns);
  201. $sql = rtrim($sql, ',');
  202. if($pdo->exec($sql) === false){
  203. throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
  204. }
  205. }
  206. $sql = '';
  207. if($pdo->exec("show index from `{$this->name}` where KEY_name = 'PRIMARY'") > 0){
  208. $sql .= " drop primary key,";
  209. }
  210. if(count($this->primaryKey) > 0){
  211. $sql .= " add primary key (".implode(',', $this->primaryKey)."),";
  212. }
  213. foreach(array_filter($this->index, function($item){
  214. return (bool)$item['dirty'];
  215. }) as $name => $idx){
  216. $sql .= " add {$pdo->stringIndex($name, $idx)},";
  217. }
  218. if(count($sql) > 0){
  219. $sql = "alter table `{$this->name}`".rtrim($sql, ',');
  220. if($pdo->exec($sql) === false){
  221. throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
  222. }
  223. }
  224. $foreignKeys =array_filter($this->foreignKeys, function($item){
  225. return (bool)$item['dirty'];
  226. });
  227. if(count($foreignKeys) > 0){
  228. $sql = "alter table `{$this->name}`";
  229. foreach($foreignKeys as $name => $fk){
  230. $sql .= " add {$pdo->stringForeignKey($name, $fk)},";
  231. }
  232. $sql = rtrim($sql, ',');
  233. if($pdo->exec($sql) === false){
  234. throw new \Exception("Unable to update table {$this->name}\n{$this->pdo->getError()}");
  235. }
  236. }
  237. unset($foreignKeys);
  238. }
  239. $this->describe();
  240. }
  241. public function rollback(){
  242. $this->exists();
  243. $this->describe();
  244. }
  245. public function rename(string $type, string $old, string $new){
  246. switch($type){
  247. case self::COLUMN:
  248. if(isset($this->columns[$old])){
  249. if(!isset($this->columns[$new])){
  250. $this->columns[$new] = $this->columns[$old];
  251. unset($this->columns[$old]);
  252. if(isset($this->columns_renamed[$old])){
  253. unset($this->columns_renamed[$old]);
  254. }
  255. $this->columns_renamed[$new] = $old;
  256. }else{
  257. throw new \Exception("{$this->name}.{$new} already exists. Unable to rename {$this->name}.{$old}");
  258. }
  259. }
  260. break;
  261. default:
  262. throw new \Exception("Renaming {$type} is not implemented");
  263. }
  264. return $this;
  265. }
  266. public function drop(string $type = null, string $name = null){
  267. if(!is_null($type)){
  268. switch($type){
  269. case self::COLUMN:
  270. if(isset($this->columns[$name])){
  271. if($this->exists){
  272. $this->columns_removed = array_merge($this->columns_removed, [$name]);
  273. }
  274. unset($this->columns[$name]);
  275. }
  276. if(isset($this->columns_renamed[$name])){
  277. unset($this->columns_renamed[$name]);
  278. }
  279. break;
  280. case self::INDEX:
  281. if(isset($this->index[$name])){
  282. if($this->exists){
  283. $this->index_removed = array_merge($this->index_removed, [$name]);
  284. }
  285. unset($this->index[$name]);
  286. }
  287. break;
  288. case self::FOREIGN_KEY:
  289. if(isset($this->foreignKey[$name])){
  290. if($this->exists){
  291. $this->foreignKey_removed = array_merge($this->foreignKey_removed, [$name]);
  292. }
  293. unset($this->foreignKey[$name]);
  294. }
  295. break;
  296. default:
  297. throw new \Exception("Cannot drop {$name}. Unknown type {$type}");
  298. }
  299. }else{
  300. if($this->exists){
  301. $this->pdo->exec("drop table `{$this->name}`");
  302. }
  303. $this->rollback();
  304. }
  305. return $this;
  306. }
  307. public function column(string $name, string $type = null, $default = null, bool $null = false, bool $increment = false){
  308. if(!is_null($type)){
  309. $new = [
  310. 'type'=> $type,
  311. 'default'=> $default,
  312. 'null'=> $null,
  313. 'increment'=> $increment,
  314. 'dirty'=> false
  315. ];
  316. if(isset($this->columns[$name])){
  317. $old = $this->columns[$name];
  318. foreach($new as $key => $val){
  319. if($key != 'dirty' && $old[$key] !== $val){
  320. $new['dirty'] = true;
  321. break;
  322. }
  323. }
  324. }else{
  325. $new['dirty'] = true;
  326. }
  327. $this->columns[$name] = $new;
  328. return $this;
  329. }else{
  330. return isset($this->columns[$name]) ? $this->columns[$name] : null;
  331. }
  332. }
  333. public function index(string $name, array $columns = null, bool $unique = false){
  334. if(!is_null($columns)){
  335. foreach($columns as $column){
  336. if(!isset($this->columns[$column])){
  337. throw new \Exception("Can't add index. Column {$this->name}.{$column} doesn't exist");
  338. }
  339. }
  340. $this->index[$name] = [
  341. 'columns'=> $columns,
  342. 'unique'=> $unique,
  343. 'dirty'=>true
  344. ];
  345. return $this;
  346. }else{
  347. return isset($this->index[$name]) ? $this->index[$name] : null;
  348. }
  349. }
  350. public function addToIndex(string $name, string $column){
  351. if(!isset($this->index[$name])){
  352. throw new \Exception("Can't add column to index. Index {$this->name}.{$name} doesn't exist");
  353. }
  354. if(!isset($this->columns[$column])){
  355. throw new \Exception("Can't add column to index. Column {$this->name}.{$column} doesn't exist");
  356. }
  357. $this->index[$name]['columns'] = array_merge($this->index[$name]['columns'], [$column]);
  358. }
  359. public function foreignKey(string $name, string $references = null, array $columns = []){
  360. if(!is_null($references)){
  361. $table = $this->pdo->table($references);
  362. if(!$table->exists){
  363. throw new \Exception("Can't create foreign key {$name}. Table {$references} does not exist.");
  364. }
  365. foreach($columns as $column){
  366. if(is_null($this->column($column[0]))){
  367. throw new \Exception("Can't create foreign key {$name}. Column {$this->name}.{$column[0]} does not exist");
  368. }
  369. if(is_null($table->column($column[1]))){
  370. throw new \Exception("Can't create foreign key {$name}. Column {$references}.{$column[1]} does not exist");
  371. }
  372. }
  373. $this->foreignKeys[$name] = [
  374. 'references'=> $references,
  375. 'columns'=> $columns,
  376. 'dirty'=> true
  377. ];
  378. return $this;
  379. }else{
  380. return isset($this->foreignKeys[$name]) ? $this->foreignKeys[$name] : null;
  381. }
  382. }
  383. public function primaryKey(...$columns){
  384. if(count($columns) > 0){
  385. foreach($columns as $column){
  386. if(!isset($this->columns[$column])){
  387. throw new \Exception("Can't add Primary key. Column {$this->name}.{$column} doesn't exist");
  388. }
  389. }
  390. $this->primaryKey = $columns;
  391. return $this;
  392. }else{
  393. return $this->primaryKey;
  394. }
  395. }
  396. public function insert(array $data){
  397. if(!$this->exists){
  398. return 0;
  399. }
  400. $id = 0;
  401. $name = $this->name;
  402. $query = $this->pdo->query("insert into `{$name}` {$this->pdo->stringSet($data)}");
  403. $query->execute();
  404. $id = $this->lastInsertId();
  405. $query->closeCursor();
  406. return $id;
  407. }
  408. public function update(array $data, array $filter = null){
  409. return $this->exists ? $this->pdo->exec("update `{$this->name}` {$this->pdo->stringSet($data)} {$this->pdo->stringFilter($filter)}") : 0;
  410. }
  411. public function delete(array $filter = null){
  412. return $this->exists ? $this->pdo->exec("delete from `{$this->name}` {$this->pdo->stringFilter($filter)}") : 0;
  413. }
  414. public function fetch(array $columns = null, array $filter = null, int $start = null, int $amount = null){
  415. $results = [];
  416. $this->each(function($row) use(&$results){
  417. $results[] = $row;
  418. }, $columns, $filter, $start, $amount);
  419. return $results;
  420. }
  421. public function each(callable $fn, array $columns = null, array $filter = null, int $start = null, int $amount = null){
  422. if($this->exists){
  423. $limit = '';
  424. if(!is_null($start) && !is_null($amount)){
  425. $limit .= " limit {$start}, {$amount}";
  426. }
  427. $columns = $columns ?? ['*'];
  428. $cols = '';
  429. foreach($columns as $column){
  430. $cols .= "{$column},";
  431. }
  432. $cols = rtrim($cols, ',');
  433. $query = $this->pdo->query("select {$cols} from `{$this->name}` {$this->pdo->stringFilter($filter)} {$limit}");
  434. while($row = $query->fetch()){
  435. $fn($row);
  436. }
  437. $query->closeCursor();
  438. }
  439. return $this;
  440. }
  441. public function count(array $filter = null){
  442. if(!$this->exists){
  443. return 0;
  444. }
  445. $query = $this->pdo->query("select count(1) from `{$this->name}` {$this->pdo->stringFilter($filter)}");
  446. $count = $query->fetchColumn();
  447. $query->closeCursor();
  448. return $count;
  449. }
  450. public function lastInsertId(){
  451. if(!$this->exists){
  452. return 0;
  453. }
  454. $id = $this->pdo->lastInsertId();
  455. if($id == 0){
  456. $col = '';
  457. foreach($this->primaryKey as $key => $name){
  458. $column = $this->column($name);
  459. if($column['increment']){
  460. $col = $name;
  461. break;
  462. }
  463. }
  464. if(count($col) > 0){
  465. $query = $this->pdo->query("select {$col} from `{$this->name}` where {$col} = (select max({$col}) from `{$this->name}`)");
  466. $id = $query->fetchColumn();
  467. }
  468. }
  469. return $id;
  470. }
  471. }
  472. ?>