• R/O
  • SSH
  • HTTPS

jinrousiki: コミット


コミットメタ情報

リビジョン2287 (tree)
日時2018-12-08 01:52:35
作者umethyl

ログメッセージ

RoomLoaderDB

変更サマリ

差分

--- trunk/include/database/db_class.php (revision 2286)
+++ trunk/include/database/db_class.php (revision 2287)
@@ -327,6 +327,8 @@
327327 private $set = [];
328328 private $set_data = [];
329329 private $where = [];
330+ private $where_data = [];
331+ private $where_upper = [];
330332 private $where_not = [];
331333 private $where_not_null = [];
332334 private $where_in = [];
@@ -333,8 +335,11 @@
333335 private $where_not_in = [];
334336 private $where_bool = [];
335337 private $where_not_true = [];
338+ private $where_like = [];
339+ private $where_or = [];
336340 private $group = [];
337341 private $order = [];
342+ private $limit = [];
338343 private $distinct = false;
339344 private $lock = false;
340345
@@ -383,16 +388,16 @@
383388 return ' ORDER BY ' . ArrayFilter::ToCSV($stack);
384389 }
385390
391+ //LIMIT 句生成
392+ public static function SetLimit($from, $to) {
393+ return sprintf(' LIMIT %d, %d', $from, $to);
394+ }
395+
386396 //FOR UPDATE 句生成
387397 public static function SetLock($flag = true) {
388398 return $flag ? ' FOR UPDATE' : '';
389399 }
390400
391- //LIMIT 句生成
392- public static function SetLimit($from, $to) {
393- return sprintf(' LIMIT %d, %d', $from, $to);
394- }
395-
396401 //-- オブジェクト生成型 --//
397402 //コンストラクタ (メソッドチェイン用)
398403 public static function Init() {
@@ -438,6 +443,16 @@
438443 return $this->Store('where', $list);
439444 }
440445
446+ //WHERE 句登録 (値指定)
447+ public function WhereData($column, $data) {
448+ return $this->StoreWhere('where_data', $column, $data);
449+ }
450+
451+ //WHERE 句登録 (>)
452+ public function WhereUpper($column, $number) {
453+ return $this->StoreWhere('where_upper', $column, $number);
454+ }
455+
441456 //WHERE 句登録 (IN)
442457 public function WhereIn($column, $count) {
443458 return $this->StoreWhere('where_in', $column, $count);
@@ -468,6 +483,17 @@
468483 return $this->StoreWhere('where_not_true', $column);
469484 }
470485
486+ //WHERE 句登録 (LIKE)
487+ public function WhereLike($column) {
488+ return $this->StoreWhere('where_like', $column);
489+ }
490+
491+ //WHERE 句登録 (OR)
492+ public function WhereOr(array $list) {
493+ $this->where_or[] = $list;
494+ return $this;
495+ }
496+
471497 //GROUP BY 句登録
472498 public function Group(array $list) {
473499 return $this->Store('group', $list);
@@ -484,6 +510,12 @@
484510 return $this;
485511 }
486512
513+ //LIMIT 句登録
514+ public function Limit($from, $to) {
515+ $this->limit = [$from, $to];
516+ return $this;
517+ }
518+
487519 //FOR UPDATE 句登録
488520 public function Lock($lock = true) {
489521 $this->lock = $lock;
@@ -499,6 +531,7 @@
499531 $this->StoreBuild($this->BuildWhere());
500532 $this->StoreBuild($this->BuildGroup());
501533 $this->StoreBuild($this->BuildOrder());
534+ $this->StoreBuild($this->BuildLimit());
502535 $this->StoreBuild($this->BuildLock());
503536
504537 $this->query = ArrayFilter::Concat($this->list);
@@ -561,28 +594,55 @@
561594 return null;
562595 }
563596
597+ //OR の先頭グループを確保
598+ $where_or = $this->where_or;
599+ $or_target = array_shift($where_or);
600+ $or_stack = [];
601+
564602 $stack = [];
565603 foreach ($this->where as $value) {
604+ $data = ArrayFilter::Get($this->where_data, $value);
605+ $upper = ArrayFilter::Get($this->where_upper, $value);
566606 $count = ArrayFilter::Get($this->where_in, $value);
567607 $not_count = ArrayFilter::Get($this->where_not_in, $value);
568608 $bool = ArrayFilter::Get($this->where_bool, $value);
569609 if (false === is_null($count) && $count > 0) {
570- $list = array_fill(0, $count, '?');
571- $stack[] = $value . ' IN ' . Text::Quote(ArrayFilter::ToCSV($list));
610+ $list = array_fill(0, $count, '?');
611+ $query = $value . ' IN ' . Text::Quote(ArrayFilter::ToCSV($list));
572612 } elseif (false === is_null($not_count) && $not_count > 0) {
573- $list = array_fill(0, $not_count, '?');
574- $stack[] = $value . ' NOT IN ' . Text::Quote(ArrayFilter::ToCSV($list));
613+ $list = array_fill(0, $not_count, '?');
614+ $query = $value . ' NOT IN ' . Text::Quote(ArrayFilter::ToCSV($list));
575615 } elseif (false === is_null($bool)) {
576- $stack[] = $value . ' IS ' . ((true === $bool) ? self::ENABLE : self::DISABLE);
616+ $query = $value . ' IS ' . ((true === $bool) ? self::ENABLE : self::DISABLE);
617+ } elseif (false === is_null($data)) {
618+ $query = $value . ' = ' . $data;
619+ } elseif (false === is_null($upper)) {
620+ $query = $value . ' > ' . $upper;
577621 } elseif (in_array($value, $this->where_not)) {
578- $stack[] = $value . ' <> ?';
622+ $query = $value . ' <> ?';
579623 } elseif (in_array($value, $this->where_not_true)) {
580- $stack[] = $value . ' IS NOT TRUE';
624+ $query = $value . ' IS NOT TRUE';
581625 } elseif (in_array($value, $this->where_not_null)) {
582- $stack[] = $value . ' IS NOT NULL';
626+ $query = $value . ' IS NOT NULL';
627+ } elseif (in_array($value, $this->where_like)) {
628+ $query = $value . ' LIKE ?';
583629 } else {
584- $stack[] = $value . ' = ?';
630+ $query = $value . ' = ?';
585631 }
632+
633+ if (ArrayFilter::IsInclude($or_target, $value)) {
634+ $or_stack[] = $query;
635+ } elseif (count($or_stack) > 0) {
636+ //OR のバッファを登録
637+ $stack[] = Text::Quote(ArrayFilter::Concat($or_stack, ' OR '));
638+ $or_target = array_shift($where_or);
639+ $or_stack = [];
640+
641+ //このループの値はそのまま登録
642+ $stack[] = $query;
643+ } else {
644+ $stack[] = $query;
645+ }
586646 }
587647 return ArrayFilter::Concat(['WHERE', ArrayFilter::Concat($stack, ' AND ')]);
588648 }
@@ -608,6 +668,18 @@
608668 return 'ORDER BY ' . ArrayFilter::ToCSV($stack);
609669 }
610670
671+ //LIMIT 句構築
672+ private function BuildLimit() {
673+ if (count($this->limit) < 2) {
674+ return null;
675+ }
676+
677+ $stack = $this->limit;
678+ $from = array_shift($stack);
679+ $to = array_shift($stack);
680+ return sprintf('LIMIT %d, %d', $from, $to);
681+ }
682+
611683 //FOR UPDATE 句構築
612684 private function BuildLock() {
613685 return (true === $this->lock) ? 'FOR UPDATE' : null;
--- trunk/include/database/room_db_class.php (revision 2286)
+++ trunk/include/database/room_db_class.php (revision 2287)
@@ -309,31 +309,27 @@
309309 $column = [
310310 'name', 'comment', 'date', 'scene', 'vote_count', 'revote_count', 'scene_start_time'
311311 ];
312- $query = Query::SetSelect('room', self::SetColumn($column)) . Query::SetWhere('room_no');
312+ $query = self::GetQuery()->Select($column)->Lock($lock);
313313
314- DB::Prepare($query . Query::SetLock($lock), [$room_no]);
314+ DB::Prepare($query->Build(), [$room_no]);
315315 return DB::FetchAssoc(true);
316316 }
317317
318318 //終了した村番地を取得
319319 public static function GetFinished($reverse) {
320- extract(self::SetFinished());
321- $order = 'GROUP BY room_no' . Query::SetOrder('room_no', ! $reverse);
322- $query = sprintf('%s %s %s %s', $select, $from, $where, $order);
320+ $query = Query::Init()->Group(['room_no'])->Order(['room_no' => false === $reverse]);
323321 if (RQ::Get()->page != 'all') {
324322 $view = OldLogConfig::VIEW;
325- $query .= Query::SetLimit($view * (RQ::Get()->page - 1), $view);
323+ $query->Limit($view * (RQ::Get()->page - 1), $view);
326324 }
327325
328- DB::Prepare($query, $list);
326+ self::Prepare($query);
329327 return DB::FetchColumn();
330328 }
331329
332330 //終了した村数を取得
333331 public static function CountFinished() {
334- extract(self::SetFinished());
335- $query = sprintf('%s %s %s', $select, $from, $where);
336- DB::Prepare($query, $list);
332+ self::Prepare(Query::Init());
337333 return DB::Count();
338334 }
339335
@@ -341,94 +337,87 @@
341337 public static function LoadFinished($room_no) {
342338 $column = [
343339 'name', 'comment', 'date', 'option_role', 'max_user', 'winner',
344- 'establish_datetime', 'start_datetime', 'finish_datetime',
345- '(SELECT COUNT(user_no) FROM user_entry AS u WHERE u.room_no = r.room_no AND u.user_no > 0)
346- AS user_count'
340+ 'establish_datetime', 'start_datetime', 'finish_datetime'
347341 ];
348- $select = Query::SetSelect('room AS r', self::SetColumn($column));
349- $where = Query::SetWhere(['room_no', 'status']);
350- $query = $select . $where;
342+ $user_count_query = Query::Init()->Table('user_entry AS u')->Select(['COUNT(user_no)']);
343+ $user_count_query->WhereData('u.room_no', 'r.room_no')->WhereUpper('u.user_no', 0);
344+ $column[] = Text::Quote($user_count_query->Build()) . ' AS user_count';
351345
346+ $query = self::GetQuery()->Table('room AS r')->Select($column)->Where(['status']);
352347 return self::LoadRoom($query, [$room_no, RoomStatus::FINISHED]);
353348 }
354349
355350 //村クラス取得 (ユーザ登録用)
356351 public static function LoadEntryUser($room_no) {
357- $column = ['date', 'scene', 'option_role', 'max_user'];
358- $select = Query::SetSelect('room', self::SetColumn($column));
359- $where = Query::SetWhere('room_no');
360- $query = $select . $where . Query::SetLock();
361-
352+ $query = self::GetQuery()->Select(['date', 'scene', 'option_role', 'max_user'])->Lock();
362353 return self::LoadRoom($query, [$room_no]);
363354 }
364355
365356 //村クラス取得 (ユーザ登録画面用)
366357 public static function LoadEntryUserPage() {
367- $column = ['name', 'comment', 'option_role'];
368- $select = Query::SetSelect('room', self::SetColumn($column));
369- $where = Query::SetWhere('room_no');
370- $query = $select . $where;
371-
358+ $query = self::GetQuery()->Select(['name', 'comment', 'option_role']);
372359 return self::LoadRoom($query, [RQ::Get()->room_no]);
373360 }
374361
375362 //村存在判定
376363 public static function Exists() {
377- $query = Query::SetSelect('room', 'room_no') . Query::SetWhere('room_no');
378- DB::Prepare($query, [RQ::Get()->room_no]);
364+ $query = self::GetQueryBase()->Select(['room_no']);
365+ DB::Prepare($query->Build(), [RQ::Get()->room_no]);
379366 return DB::Exists();
380367 }
381368
382- //共通 column 生成
383- private static function SetColumn(array $column) {
384- $list = ['room_no AS id', 'status', 'game_option'];
385- ArrayFilter::Merge($list, $column);
386- return $list;
387- }
388-
389- //LIKE 文生成
369+ //LIKE 句生成
390370 private static function SetLike($str) {
391371 return '%' . $str . '%';
392372 }
393373
394- //終了村取得共通SQLセット
395- private static function SetFinished() {
396- $select = 'SELECT room_no';
397- $from = 'FROM room';
398- $where = 'WHERE status = ?';
399- $list = [RoomStatus::FINISHED];
374+ //共通 Query 取得
375+ private static function GetQuery() {
376+ return self::GetQueryBase()->Select(['room_no AS id', 'status', 'game_option']);
377+ }
400378
379+ //共通 Query Base 取得
380+ private static function GetQueryBase() {
381+ return Query::Init()->Table('room')->Where(['room_no']);
382+ }
383+
384+ //Prepare 処理
385+ private static function Prepare(Query $query) {
386+ $table = 'room';
401387 if (isset(RQ::Get()->role) || isset(RQ::Get()->name)) {
402- $from .= ' INNER JOIN user_entry USING (room_no)';
388+ $table .= ' INNER JOIN user_entry USING (room_no)';
403389 }
390+ $query->Table($table)->Select(['room_no'])->Where(['status']);
391+ $list = [RoomStatus::FINISHED];
404392
405393 if (isset(RQ::Get()->role)) {
406- $where .= ' AND role LIKE ?';
394+ $query->WhereLike('role');
407395 $list[] = self::SetLike(RQ::Get()->role);
408396 }
409397
410398 if (isset(RQ::Get()->name)) {
411- $where .= ' AND (uname LIKE ? OR handle_name LIKE ?)';
399+ $query->WhereOr(['uname', 'handle_name']);
400+ $query->WhereLike('uname')->WhereLike('handle_name');
412401 $name = self::SetLike(RQ::Get()->name);
413402 array_push($list, $name, $name);
414403 }
415404
416405 if (isset(RQ::Get()->room_name)) {
417- $where .= ' AND name LIKE ?';
406+ $query->WhereLike('name');
418407 $list[] = self::SetLike(RQ::Get()->room_name);
419408 }
420409
421410 if (isset(RQ::Get()->winner)) {
422- $where .= Query::AddWhere('winner');
411+ $query->Where(['winner']);
423412 $list[] = RQ::Get()->winner;
424413 }
425414
426- return ['select' => $select, 'from' => $from, 'where' => $where, 'list' => $list];
415+ DB::Prepare($query->Build(), $list);
427416 }
428417
429418 //共通村クラスロード
430- private static function LoadRoom($query, array $list) {
431- DB::Prepare($query, $list);
419+ private static function LoadRoom(Query $query, array $list) {
420+ DB::Prepare($query->Build(), $list);
432421 return DB::FetchClass('Room', true);
433422 }
434423 }
旧リポジトリブラウザで表示