PHP DB::getTablePrefix方法代码示例

 public static function boot()
 {
     self::creating(function ($custom_field) {
         if (in_array($custom_field->db_column_name(), Schema::getColumnListing(DB::getTablePrefix() . CustomField::$table_name))) {
             //field already exists when making a new custom field; fail.
             return false;
         }
         return DB::statement("ALTER TABLE " . DB::getTablePrefix() . CustomField::$table_name . " ADD COLUMN (" . $custom_field->db_column_name() . " TEXT)");
     });
     self::updating(function ($custom_field) {
         //print("    SAVING CALLBACK FIRING!!!!!    ");
         if ($custom_field->isDirty("name")) {
             //print("DIRTINESS DETECTED!");
             //$fields=array_keys($custom_field->getAttributes());
             //;
             //add timestamp fields, add id column
             //array_push($fields,$custom_field->getKeyName());
             /*if($custom_field::timestamps) {
             
                     }*/
             //print("Fields are: ".print_r($fields,true));
             if (in_array($custom_field->db_column_name(), Schema::getColumnListing(CustomField::$table_name))) {
                 //field already exists when renaming a custom field
                 return false;
             }
             return DB::statement("UPDATE " . CustomField::$table_name . " RENAME " . self::name_to_db_name($custom_field->get_original("name")) . " TO " . $custom_field->db_column_name());
         }
         return true;
     });
     self::deleting(function ($custom_field) {
         return DB::statement("ALTER TABLE " . CustomField::$table_name . " DROP COLUMN " . $custom_field->db_column_name());
     });
 }

 /**
  * Run the migrations.
  *
  * @return void
  */
 public function up()
 {
     Schema::table('image_files', function ($table) {
         $table->text('attributes');
     });
     DB::statement('ALTER TABLE `' . DB::getTablePrefix() . 'image_files` CHANGE `attributes` `attributes` TEXT NOT NULL AFTER `size`');
 }

 /**
  * Run the migrations.
  *
  * @return void
  */
 public function up()
 {
     //
     $prefix = DB::getTablePrefix();
     DB::statement('ALTER TABLE ' . $prefix . 'users MODIFY phone varchar(20) null');
     DB::statement('ALTER TABLE ' . $prefix . 'users MODIFY jobtitle varchar(50) null');
 }

 public function orderByMessengers($sortOrder = 'asc')
 {
     $prefix = \DB::getTablePrefix();
     $this->queryBuilder()->selectRaw(sprintf('* , (SELECT COUNT(*) FROM "%1$smessengers" WHERE "%1$smessengers"."contact_id" = "%1$scontacts"."id") as "messengers_count"', $prefix));
     $this->queryBuilder()->orderBy('messengers_count', $sortOrder);
     return $this;
 }

 /**
  * Run the migrations.
  *
  * @return void
  */
 public function up()
 {
     /**
      * Since L4 doesn't support specifying collation for tables/text fields, 
      * An empty table (only contains ID) is created first. 
      */
     Schema::create($this->table, function ($table) {
         $table->bigIncrements('ID');
     });
     /**
      * Table collation is specified with raw MySQL SQL statement. 
      */
     $table_w_prefix = DB::getTablePrefix() . $this->table;
     DB::statement("ALTER TABLE `{$table_w_prefix}`\n            DEFAULT CHARACTER SET ascii COLLATE ascii_general_ci;");
     /**
      * ... Then all the actual data fields are added. 
      */
     Schema::table($this->table, function ($table) {
         $table->string('queue_name');
         $table->enum('status', ['deleted', 'pending', 'running']);
         $table->integer('attempts')->unsigned();
         $table->longText('payload');
         $table->bigInteger('fireon');
     });
 }

 /**
  * 取得未删除的信息
  *
  * @return array
  * @todo 数据量多时，查找属于指定分类，推荐位，标签三个的文章时使用redis集合交集处理，避免查询消耗。
  */
 public function AllContents($search = [])
 {
     $prefix = \DB::getTablePrefix();
     $currentQuery = $this->select(['article_main.*', 'users.name'])->leftJoin('users', 'article_main.user_id', '=', 'users.id')->leftJoin('article_classify_relation', 'article_main.id', '=', 'article_classify_relation.article_id')->leftJoin('article_classify', 'article_classify_relation.classify_id', '=', 'article_classify.id')->leftJoin('article_position_relation', 'article_main.id', '=', 'article_position_relation.article_id')->leftJoin('article_tag_relation', 'article_main.id', '=', 'article_tag_relation.article_id')->orderBy('article_main.id', 'desc')->where('article_main.is_delete', self::IS_DELETE_NO)->groupBy('article_main.id')->distinct();
     if (isset($search['keyword']) && !empty($search['keyword'])) {
         $currentQuery->where('article_main.title', 'like', "%{$search['keyword']}%");
     }
     if (isset($search['username']) && !empty($search['username'])) {
         $currentQuery->where('article_main.user_id', $search['username']);
     }
     if (isset($search['classify']) && !empty($search['classify'])) {
         $currentQuery->where('article_classify_relation.classify_id', $search['classify']);
     }
     if (isset($search['position']) && !empty($search['position'])) {
         $currentQuery->where('article_position_relation.position_id', $search['position']);
     }
     if (isset($search['tag']) && !empty($search['tag'])) {
         $currentQuery->where('article_tag_relation.tag_id', $search['tag']);
     }
     if (isset($search['timeFrom'], $search['timeTo']) and !empty($search['timeFrom']) and !empty($search['timeTo'])) {
         $search['timeFrom'] = strtotime($search['timeFrom']);
         $search['timeTo'] = strtotime($search['timeTo']);
         $currentQuery->whereBetween('article_main.write_time', [$search['timeFrom'], $search['timeTo']]);
     }
     $total = count($currentQuery->get()->all());
     $currentQuery->forPage($page = Paginator::resolveCurrentPage(), $perPage = self::PAGE_NUMS);
     $result = $currentQuery->get()->all();
     return new LengthAwarePaginator($result, $total, $perPage, $page, ['path' => Paginator::resolveCurrentPath()]);
 }

 public static function boot()
 {
     self::creating(function ($custom_field) {
         if (in_array($custom_field->db_column_name(), \Schema::getColumnListing(\DB::getTablePrefix() . CustomField::$table_name))) {
             //field already exists when making a new custom field; fail.
             return false;
         }
         \Schema::table(\DB::getTablePrefix() . \App\Models\CustomField::$table_name, function ($table) use($custom_field) {
             $table->text($custom_field->db_column_name())->nullable();
         });
     });
     self::updating(function ($custom_field) {
         if ($custom_field->isDirty("name")) {
             if (in_array($custom_field->db_column_name(), \Schema::getColumnListing(CustomField::$table_name))) {
                 //field already exists when renaming a custom field
                 return false;
             }
             return \DB::statement("UPDATE " . CustomField::$table_name . " RENAME " . self::name_to_db_name($custom_field->get_original("name")) . " TO " . $custom_field->db_column_name());
         }
         return true;
     });
     self::deleting(function ($custom_field) {
         return \DB::statement("ALTER TABLE " . CustomField::$table_name . " DROP COLUMN " . $custom_field->db_column_name());
     });
 }

    public function getLog($offset = 0, $limit = 20)
    {
        $offset = intval($offset);
        $limit = intval($limit);
        $controller = $this;
        $data = DB::select(DB::raw('SELECT MIN(`created_at`) AS `start`,
				MAX(`created_at`) AS `end`,
				MAX(UNIX_TIMESTAMP(`created_at`)) - MIN(UNIX_TIMESTAMP(`created_at`)) + 1 `duration`,
				`success`
			FROM (
				SELECT
					`t`.`id`,
					`t`.`created_at`,
					if (@last_success = success, @group, @group:=@group+1) group_number,
						@last_success := success as success
				FROM `' . DB::getTablePrefix() . 'checks_results` AS `t`
				CROSS JOIN (
					SELECT @last_status := null, @group:=0
				) as `init_vars`
				ORDER BY `t`.`created_at`
			) q
			GROUP BY `group_number`
			ORDER BY `start` DESC
			LIMIT ' . $offset . ', ' . $limit . ''));
        $data = array_map(function ($date) use($controller) {
            return array('start' => new \Carbon\Carbon($date->start), 'end' => new \Carbon\Carbon($date->end), 'duration' => $date->duration, 'duration_locale' => $controller->minutesToHuman($date->duration), 'success' => (bool) $date->success);
        }, $data);
        return $data;
    }

 /**
  * Reverse the migrations.
  *
  * @return void
  */
 public function down()
 {
     Schema::table('role_user', function (Blueprint $table) {
         $table->dropForeign('role_user_user_id_foreign');
         $table->dropForeign('role_user_role_id_foreign');
     });
     DB::statement("ALTER TABLE " . DB::getTablePrefix() . "role_user\n\t\t\tCHANGE user_id user_id INT(11) NOT NULL,\n\t\t\tCHANGE role_id role_id INT(11) NOT NULL");
 }

 /**
  * 取得一篇文章主表和副表的信息
  *
  * @param int $articleId 文章的ID
  * @return array
  */
 public function getContentDetailByArticleId($articleId)
 {
     $articleId = (int) $articleId;
     $this->prefix = \DB::getTablePrefix();
     $currentQuery = $this->select(\DB::raw($this->prefix . 'article_main.*, ' . $this->prefix . 'article_detail.content, group_concat(DISTINCT ' . $this->prefix . 'article_classify.name) as classnames, group_concat(DISTINCT ' . $this->prefix . 'article_tags.name) as tagsnames'))->leftJoin('article_detail', 'article_main.id', '=', 'article_detail.article_id')->leftJoin('article_classify_relation', 'article_classify_relation.article_id', '=', 'article_main.id')->leftJoin('article_classify', 'article_classify_relation.classify_id', '=', 'article_classify.id')->leftJoin('article_tag_relation', 'article_tag_relation.article_id', '=', 'article_main.id')->leftJoin('article_tags', 'article_tag_relation.tag_id', '=', 'article_tags.id')->where('article_main.is_delete', self::IS_DELETE_NO)->where('article_main.status', self::STATUS_YES)->where('article_main.id', $articleId)->first();
     $info = $currentQuery->toArray();
     return $info;
 }

 /**
  * Run the migrations.
  *
  * @return void
  */
 public function up()
 {
     $prefix = DB::getTablePrefix();
     DB::statement('ALTER TABLE ' . $prefix . 'licenses MODIFY order_number varchar(50) null');
     DB::statement('ALTER TABLE ' . $prefix . 'licenses MODIFY notes varchar(255) null');
     DB::statement('ALTER TABLE ' . $prefix . 'licenses MODIFY license_name varchar(100) null');
     DB::statement('ALTER TABLE ' . $prefix . 'licenses MODIFY license_email varchar(120) null');
 }

 public function run()
 {
     $sql = 'insert into ' . DB::getTablePrefix() . 'roles (role, role_name) values (?, ?)';
     DB::insert($sql, array('admin', 'Админ'));
     DB::insert($sql, array('moderator', 'Модератор'));
     DB::insert($sql, array('user', 'Пользователь'));
     DB::insert($sql, array('messageSender', 'Отправитель сообщений'));
     DB::insert($sql, array('messageSubscriber', 'Подписчик на получение сообщений'));
 }

 /**
  * Run the migrations.
  *
  * @return void
  */
 public function up()
 {
     DB::statement('ALTER TABLE ' . DB::getTablePrefix() . 'asset_logs MODIFY column asset_type varchar(100) null');
     DB::statement('ALTER TABLE ' . DB::getTablePrefix() . 'asset_logs MODIFY column added_on timestamp default "0000-00-00 00:00:00"');
     Schema::table('asset_logs', function ($table) {
         $table->renameColumn('added_on', 'created_at');
         $table->timestamp('updated_at');
         $table->softDeletes();
     });
 }

 /**
  * Reverse the migrations.
  *
  * @return  void
  */
 public function down()
 {
     Schema::table(\Config::get('countries.table_name'), function () {
         DB::statement("ALTER TABLE " . DB::getTablePrefix() . \Config::get('countries.table_name') . " MODIFY country_code VARCHAR(3) NOT NULL DEFAULT ''");
         DB::statement("ALTER TABLE " . DB::getTablePrefix() . \Config::get('countries.table_name') . " MODIFY iso_3166_2 VARCHAR(2) NOT NULL DEFAULT ''");
         DB::statement("ALTER TABLE " . DB::getTablePrefix() . \Config::get('countries.table_name') . " MODIFY iso_3166_3 VARCHAR(3) NOT NULL DEFAULT ''");
         DB::statement("ALTER TABLE " . DB::getTablePrefix() . \Config::get('countries.table_name') . " MODIFY region_code VARCHAR(3) NOT NULL DEFAULT ''");
         DB::statement("ALTER TABLE " . DB::getTablePrefix() . \Config::get('countries.table_name') . " MODIFY sub_region_code VARCHAR(3) NOT NULL DEFAULT ''");
     });
 }

 /**
  * Run the migrations.
  *
  * @return void
  */
 public function up()
 {
     //
     $prefix = DB::getTablePrefix();
     Schema::table('groups', function (Blueprint $table) {
         //
     });
     DB::statement('UPDATE ' . $prefix . 'groups SET permissions="{\\"admin\\":1,\\"users\\":1,\\"reports\\":1}" where id=1');
     DB::statement('UPDATE ' . $prefix . 'groups SET permissions="{\\"users\\":1,\\"reports\\":1}" where id=2');
 }