diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..612c601 --- /dev/null +++ b/.gitignore @@ -0,0 +1,3 @@ +/vendor +composer.lock +.phpunit.result.cache diff --git a/README.md b/README.md new file mode 100644 index 0000000..b5828df --- /dev/null +++ b/README.md @@ -0,0 +1,98 @@ +# istlab/admin-crud + +Laravel アプリのデータベーステーブルをブラウザ上から参照・インライン編集するための管理用 CRUD パッケージです。 +ReviewHub の `AdminController` の CRUD 機能 (`admin.crud*`) を切り出したもので、デフォルト設定では元の URI・ルート名をそのまま再現します。 + +## 機能 + +- テーブル一覧と行数の表示 (`GET /admin_crud`) +- テーブル内容の表示・カラム絞り込み (LIKE / 整数完全一致) +- セルのクリック編集 (Ajax, `POST /admin_crud`) +- tinyint カラムのトグルスイッチ +- 行の追加 (Eloquent ファクトリ) / コピー (replicate) / 削除 +- チェックした行の一括コピー・一括削除 +- 全行削除 (truncate) +- AUTO_INCREMENT 値の設定 (MySQL / SQLite) +- 予約更新 (scheduled update) 連携 (オプション) + +## インストール + +ローカルパスリポジトリとして読み込む場合: + +```json +// 宿主アプリ側 composer.json +"repositories": [ + { + "type": "path", + "url": "../CrudPkg", + "options": { "symlink": true } + } +], +``` + +```bash +composer require istlab/admin-crud:* +php artisan vendor:publish --tag=admin-crud-assets # public/vendor/admin-crud に JS/CSS を配置 (必須) +php artisan vendor:publish --tag=admin-crud-config # config/admin-crud.php を公開 (任意) +``` + +サービスプロバイダ (`Istlab\AdminCrud\AdminCrudServiceProvider`) はパッケージ自動検出で登録されます。 + +## 設定 (`config/admin-crud.php`) + +| キー | デフォルト | 説明 | +|---|---|---| +| `path` | `admin_crud` | ルート URI のベース。`/admin_crud`, `/admin_crudcopy`, ... のようになります | +| `route_name_prefix` | `admin.` | ルート名プレフィックス (`admin.crud` 等) | +| `middleware` | `['web', 'auth']` | 全ルートに適用するミドルウェア | +| `gate` | `role_any` | 認可に使う Gate 名。`null` でチェック無効 | +| `roles` | `admin` | 参照・コピー・削除等に必要なロール (`$user->can($gate, $roles)`) | +| `post_roles` | `admin\|manager\|ec\|pub` | セル更新 (crudpost) に必要なロール | +| `model_namespace` | `App\Models` | Eloquent モデルの名前空間 | +| `layout_component` | `admin-crud::layout` | ページレイアウトの Blade コンポーネント名 | +| `limit` | `100` | 1ページの最大表示行数 | +| `scheduled_update_create_route` | `null` | 予約更新作成ページのルート名 (例: `scheduled_update.create`) | +| `jquery_url` | CDN (jquery 3.7.1) | ビューで読み込む jQuery の URL | + +### ReviewHub で使う場合の設定例 + +```php +// config/admin-crud.php +'layout_component' => 'app-layout', // 内に描画 +'jquery_url' => '/js/jquery.min.js', // 既存の jQuery を利用 +'scheduled_update_create_route' => 'scheduled_update.create', +``` + +`app-layout` を使う場合、レイアウトに `@stack('localcss')` と `@stack('localjs')` が必要です。 + +## 認可について + +デフォルトでは `role_any` という Gate を `$user->can('role_any', 'admin')` の形で呼びます。 +宿主アプリ側で次のような Gate を定義してください (ReviewHub では定義済みです): + +```php +Gate::define('role_any', function ($user, string $roles_str) { + // $roles_str は 'admin|manager' のようなパイプ区切り + ... +}); +``` + +Gate を使わない場合は `'gate' => null` にし、`middleware` で独自に保護してください。 + +## 旧実装からの移行 (ReviewHub) + +1. 上記の設定でパッケージをインストール +2. `routes/web.php` の `// begin CRUD` 〜 `// end CRUD` のルート定義を削除 +3. `AdminController` の `crud*` メソッドと `resources/views/admin/crud*.blade.php` は不要になります + (パッケージ側が同じ URI・ルート名を引き継ぐため、既存の `route('admin.crud', ...)` へのリンクはそのまま動きます) + +## 既存アプリとの共存 + +旧ルートを残したままインストールする場合は、ルート名・URI が衝突しないよう +`path` と `route_name_prefix` を変更してください (例: `'path' => 'crud'`, `'route_name_prefix' => 'crud.'`)。 + +## セキュリティ上の注意 + +- テーブル名は実在テーブルのホワイトリストで検証されます。 +- 更新対象カラム名もスキーマ情報で検証されます。 +- それでも本パッケージは任意テーブルを直接編集できる強力な管理機能です。必ず認証・認可を設定した上で、信頼できる管理者のみに公開してください。 diff --git a/composer.json b/composer.json new file mode 100644 index 0000000..6344a6b --- /dev/null +++ b/composer.json @@ -0,0 +1,36 @@ +{ + "name": "istlab/admin-crud", + "description": "Database table browser / inline CRUD editor for Laravel admin pages.", + "type": "library", + "license": "MIT", + "keywords": ["laravel", "admin", "crud", "database"], + "require": { + "php": "^8.1", + "laravel/framework": "^10.0 || ^11.0 || ^12.0 || ^13.0" + }, + "require-dev": { + "orchestra/testbench": "^8.0 || ^9.0 || ^10.0 || ^11.0" + }, + "autoload": { + "psr-4": { + "Istlab\\AdminCrud\\": "src/" + }, + "files": [ + "src/helpers.php" + ] + }, + "autoload-dev": { + "psr-4": { + "Istlab\\AdminCrud\\Tests\\": "tests/" + } + }, + "extra": { + "laravel": { + "providers": [ + "Istlab\\AdminCrud\\AdminCrudServiceProvider" + ] + } + }, + "minimum-stability": "stable", + "prefer-stable": true +} diff --git a/config/admin-crud.php b/config/admin-crud.php new file mode 100644 index 0000000..721b970 --- /dev/null +++ b/config/admin-crud.php @@ -0,0 +1,91 @@ + env('ADMIN_CRUD_PATH', 'admin_crud'), + + /* + |-------------------------------------------------------------------------- + | ルート名プレフィックス + |-------------------------------------------------------------------------- + | デフォルトは元のルート名をそのまま再現します: + | admin.crud, admin.crudcopy, admin.cruddelete, admin.crudchkdelete, + | admin.crud_setautoinc, admin.crudnew, admin.crudtruncate, admin.crudpost + */ + 'route_name_prefix' => 'admin.', + + /* + |-------------------------------------------------------------------------- + | ミドルウェア + |-------------------------------------------------------------------------- + */ + 'middleware' => ['web', 'auth'], + + /* + |-------------------------------------------------------------------------- + | 認可 (Gate) + |-------------------------------------------------------------------------- + | 各アクションで $user->can($gate, $roles) をチェックします。 + | 'gate' => null にするとチェックを省略します (ミドルウェア側で + | 完全に保護する場合など)。 + | roles : 参照・コピー・削除・truncate など + | post_roles : セルのインライン更新 (crudpost) + */ + 'gate' => 'role_any', + 'roles' => 'admin', + 'post_roles' => 'admin|manager|ec|pub', + + /* + |-------------------------------------------------------------------------- + | Eloquentモデルの名前空間 + |-------------------------------------------------------------------------- + | テーブル名から Str::studly(Str::singular($table)) でモデルクラスを + | 解決します (factory による行追加、replicate によるコピー等に使用)。 + */ + 'model_namespace' => 'App\\Models', + + /* + |-------------------------------------------------------------------------- + | レイアウト (Bladeコンポーネント) + |-------------------------------------------------------------------------- + | パッケージ同梱の最小レイアウトは 'admin-crud::layout' です。 + | Breeze 風のレイアウト (@stack('localcss') / @stack('localjs') あり) を + | 持つアプリでは 'app-layout' にすると 内に描画されます。 + */ + 'layout_component' => 'admin-crud::layout', + + /* + |-------------------------------------------------------------------------- + | 1ページに表示する最大行数 + |-------------------------------------------------------------------------- + */ + 'limit' => 100, + + /* + |-------------------------------------------------------------------------- + | 予約更新 (scheduled update) 連携 + |-------------------------------------------------------------------------- + | ルート名を設定し、かつテーブルに対応するEloquentモデルが存在する場合、 + | セルの右クリックメニューから target_type / target_id / field_name 付きで + | そのルートへリンクします (例: 'scheduled_update.create')。 + | null の場合は無効です。 + */ + 'scheduled_update_create_route' => null, + + /* + |-------------------------------------------------------------------------- + | jQuery の URL + |-------------------------------------------------------------------------- + | CRUDビューで読み込む jQuery です。アプリが既に jQuery を配信している + | 場合は '/js/jquery.min.js' などに変更してください。 + */ + 'jquery_url' => env('ADMIN_CRUD_JQUERY_URL', 'https://code.jquery.com/jquery-3.7.1.min.js'), +]; diff --git a/phpunit.xml b/phpunit.xml new file mode 100644 index 0000000..52e8d09 --- /dev/null +++ b/phpunit.xml @@ -0,0 +1,11 @@ + + + + + tests + + + diff --git a/resources/css/localflash.css b/resources/css/localflash.css new file mode 100644 index 0000000..b5c9d5a --- /dev/null +++ b/resources/css/localflash.css @@ -0,0 +1,12 @@ +@keyframes flash { + 0% { + background-color: #df9; + } + 100% { + background-color: inherit; + } + } + + .flash { + animation: flash 1s; + } diff --git a/resources/js/crud_table.js b/resources/js/crud_table.js new file mode 100644 index 0000000..bef7777 --- /dev/null +++ b/resources/js/crud_table.js @@ -0,0 +1,179 @@ +var toggleNoticeShown = false; + +// crud でのテキスト編集 +function other_textchange(event) { + // console.log(event.target.value); // selected option value + // console.log(event.target.id); // select + var tdid = event.target.id.replace("select__", "edit__"); + var text = $("#" + tdid).val(); + if (event.target.value == "remove_spaces_between_japanese") { + var newtext = text.replace(/([一-龥ぁ-ゔァ-ヴー々〆〤.,。、])\s+([一-龥ぁ-ゔァ-ヴー々〆〤.,。、])/g, '$1$2'); + $("#" + tdid).val(newtext); + } else if (event.target.value == "replace_kuten") { + var newtext = text.replace(/、/g, ",").replace(/。/g, "."); + $("#" + tdid).val(newtext); + } +} + +// x-toggle +function toggle(formid, id123) { + console.log("id123: " + id123); + var m = id123.match(/^(\w+)__(\d+)__([\w()]+)/); + crudpost(m[1], m[2], "x", m[3].toLowerCase()); +} + +$(".clicktoedit").click(function (e) { + var tdid = e.currentTarget.id; + if (origData[tdid] != null) return; + // tdidを分解する field__id__TYPE + var m = tdid.match(/^(\w+)__(\d+)__([\w()]+)/); + // グローバルにsizecols が定義されていれば、それを用いる。 + var localsizecols = (typeof sizecols !=='undefined') ? sizecols : 50; + var txt = $('#' + tdid).text().trim(); + var inputType = ""; + if (/text$/.test(m[3].toLowerCase())) { // text, longtext, mediumtext... + var attr = $('#' + tdid).data('orig'); + if (typeof attr !== 'undefined') txt = attr; + $('#' + tdid).html("
hint: cmd(ctrl)+enter to save, esc to cancel. "+ + ""+ + // "
"); + inputType = "textarea"; + } else if (m[3].toLowerCase() == "varchar" || m[3].toLowerCase() == "datetime" || m[3].toLowerCase() == "timestamp") { + $('#' + tdid).html(""); + inputType = "text"; + } else if (m[3].toLowerCase() == "tinyint") { + if (!toggleNoticeShown) { + var ok = confirm("Press OK to change the value. \n値を変更する場合はOKを押してください。(一度OKを押すと、次回以降は確認しません。)"); + if (!ok) return; + toggleNoticeShown = true; + } + crudpost(m[1], m[2], "x", m[3].toLowerCase()); + return; + } else { + $('#' + tdid).html(""); + inputType = "text"; + } + origData[tdid] = txt; + // $('#'+tdid).removeClass('clicktoedit'); + $('#' + tdid).removeClass('hover:text-blue-600'); + var editelem = $('#edit__' + tdid); + editelem.focus(); + // テキスト入力フィールドの最後の文字の位置を取得します + var endPosition = editelem.val().length; + // カーソルの位置を設定します + editelem[0].selectionStart = endPosition; + editelem[0].selectionEnd = endPosition; +}); + +// IME変換中はEnterは確定なので、送信しない。 +var isIMEActive = false; +$(".clicktoedit").on('compositionstart', function (e) { + isIMEActive = true; +}); +$(".clicktoedit").on('compositionend', function (e) { + isIMEActive = false; +}); + +function saveText(tdid) { + var m = tdid.match(/^(\w+)__(\d+)__([\w()]+)/); // tdidを分解する field__id__TYPE + if ($('#' + tdid).find("textarea").length > 0) { + var text = $('#' + tdid).find("textarea").val(); + $('#' + tdid).data('orig', text); + if (typeof mode_br !== 'undefined' && mode_br === true){ + var showtext = text.replaceAll("\n", "
"); + $('#' + tdid).html(showtext); + } else { + $('#' + tdid).text(text.replaceAll("\n", "\r\n")); + } + } else if ($('#' + tdid).find("input").length > 0) { + var text = $('#' + tdid).find("input").val(); + $('#' + tdid).text(text); + } else { + alert("error saveText"); + } + crudpost(m[1], m[2], text, m[3]); + + $('#' + tdid).addClass('hover:text-blue-600'); + origData[tdid] = null; + delete origData[tdid]; +} + +$(".clicktoedit").keydown(function (e) { + var tdid = e.currentTarget.id; + if (e.key === "Enter") { + if (isIMEActive) return; // IME変換中はEnterは確定なので、送信しない。 + if ($('#' + tdid).find("textarea").length > 0 && !e.ctrlKey && !e.metaKey) return; // textareaはCTRLorMetaが必要 + else { + saveText(tdid); + // if ($('#' + tdid).find("textarea").length > 0) { + // var text = $('#' + tdid).find("textarea").val(); + // $('#' + tdid).data('orig', text); + // if (typeof mode_br !== 'undefined' && mode_br === true){ + // var showtext = text.replaceAll("\n", "
"); + // $('#' + tdid).html(showtext); + // } else { + // $('#' + tdid).text(text.replaceAll("\n", "\r\n")); + // } + // } + // if ($('#' + tdid).find("input").length > 0) { + // var text = $('#' + tdid).find("input").val(); + // $('#' + tdid).text(text); + // } + // crudpost(m[1], m[2], text, m[3]); + } + } else if (e.key === "Escape" || e.key === "Esc") { + if (typeof mode_br !== 'undefined' && mode_br === true){ + $('#' + tdid).html(origData[tdid].replaceAll("\n", "
")); + } else { + $('#' + tdid).text(origData[tdid]); + } + $('#' + tdid).addClass('hover:text-blue-600'); + origData[tdid] = null; + delete origData[tdid]; + } +}) + +function crudpost(field, data_id, val, dtype) { + var fd = new FormData(); + fd.append('table', table); + fd.append('_token', $('meta[name="csrf-token"]').attr("content")); + fd.append('field', field); + fd.append('data_id', data_id); + fd.append('dtype', dtype); + fd.append('val', val); + fd.append('tdid', field + "__" + data_id + "__" + dtype); + var formDataObject = {}; + for (var pair of fd.entries()) { + formDataObject[pair[0]] = pair[1]; + } + var form = $("#admincrudpost"); + $.ajax({ + url: form.attr("action"), + type: form.attr("method"), + data: formDataObject, + timeout: 10000, + beforeSend: function (xhr, settings) { }, + complete: function (xhr, textStatus) { }, + success: function (result, textStatus, xhr) { + if (/^TOGGLE/.test(result)) { + var m = result.trim().match(/(TOGGLE) (\d+) (.+)$/); // resultを分解する TOGGLE val tdid + var elem = $('#td__' + m[3]); + // elem.text(m[2]); + } else { + var m = result.trim().match(/(OK) (.+)$/); + var elem = $('#' + m[2]); + } + elem.addClass('flash'); + setTimeout(function () { + elem.removeClass('flash'); + }, 1000); + }, + error: function (xhr, textStatus, error) { + alert("error crudpost"); + } + }); +} diff --git a/resources/js/crud_where.js b/resources/js/crud_where.js new file mode 100644 index 0000000..7eeac53 --- /dev/null +++ b/resources/js/crud_where.js @@ -0,0 +1,9 @@ +$("input.whereBy").keydown(function (e) { + var tdid = e.currentTarget.id; + var m = tdid.match(/^(\w+)__([\w()]+)/); // tdidを分解する whereBy name + if (e.key === "Enter") { + var form = $('#admincrudwhere'); + form.submit(); + } +}); + diff --git a/resources/views/components/alert/error.blade.php b/resources/views/components/alert/error.blade.php new file mode 100644 index 0000000..4f4cb81 --- /dev/null +++ b/resources/views/components/alert/error.blade.php @@ -0,0 +1,10 @@ + + diff --git a/resources/views/components/alert/success.blade.php b/resources/views/components/alert/success.blade.php new file mode 100644 index 0000000..b0e211d --- /dev/null +++ b/resources/views/components/alert/success.blade.php @@ -0,0 +1,10 @@ + + diff --git a/resources/views/components/layout.blade.php b/resources/views/components/layout.blade.php new file mode 100644 index 0000000..495e9ef --- /dev/null +++ b/resources/views/components/layout.blade.php @@ -0,0 +1,29 @@ + + + + + + + + {{ config('app.name', 'Laravel') }} - Admin CRUD + + @stack('localcss') + + + +
+ @if (isset($header)) +
+
+ {{ $header }} +
+
+ @endif +
+ {{ $slot }} +
+
+ @stack('localjs') + + + diff --git a/resources/views/components/linkbutton.blade.php b/resources/views/components/linkbutton.blade.php new file mode 100644 index 0000000..d9c772b --- /dev/null +++ b/resources/views/components/linkbutton.blade.php @@ -0,0 +1,28 @@ +@props([ + 'href' => '', + 'confirm' => null, + 'color' => 'blue', + 'target' => '_self', + 'size' => 'md', +]) +@php + if (!function_exists('admin_crud_confirm_func')) { + function admin_crud_confirm_func($conf) + { + if ($conf == null) { + return 'true'; + } + + return "confirm('{$conf}');"; + } + } +@endphp + + + {{ $slot }} + diff --git a/resources/views/components/submitbutton.blade.php b/resources/views/components/submitbutton.blade.php new file mode 100644 index 0000000..1441e33 --- /dev/null +++ b/resources/views/components/submitbutton.blade.php @@ -0,0 +1,20 @@ +@props([ + 'name' => 'action', + 'value' => '', + 'color' => 'blue', + 'id' => 'id_submit_button', + 'confirm' => '', + 'size' => 'md', +]) +@php + $id = $id == 'id_submit_button' ? $id . '_' . $value : $id; +@endphp + + diff --git a/resources/views/components/toggle.blade.php b/resources/views/components/toggle.blade.php new file mode 100644 index 0000000..495dd9a --- /dev/null +++ b/resources/views/components/toggle.blade.php @@ -0,0 +1,17 @@ +@props([ + 'id' => 'toggle', + 'name' => 'toggle', + 'checked' => false, + 'formid' => '', +]) +@php + $chk = $checked ? 'checked' : ''; +@endphp + +
+ + +
diff --git a/resources/views/db.blade.php b/resources/views/db.blade.php new file mode 100644 index 0000000..fab0d6c --- /dev/null +++ b/resources/views/db.blade.php @@ -0,0 +1,59 @@ + + +

+ {{ __('Crud Tables') }} +

+
+ + @if (session('feedback.success')) + {{ session('feedback.success') }} + @endif + @if (session('feedback.error')) + {{ session('feedback.error') }} + @endif + +
+
+ + + + + + + + + + + @foreach ($tables as $table) + + + + + + + @endforeach + +
cnttable name(row mode)truncate
+ {{ $tableDataCounts[$table] }} + + {{ $table }} + + {{ $table }} + + @if ($tableDataCounts[$table] == 0) + truncate + @else + + {{ $tableDataCounts[$table] }} truncate + + @endif +
+
+
+ +
diff --git a/resources/views/row.blade.php b/resources/views/row.blade.php new file mode 100644 index 0000000..c253930 --- /dev/null +++ b/resources/views/row.blade.php @@ -0,0 +1,163 @@ + + +
+ + ← {{ $tableName }} に戻る + + + ← Crud Tables に戻る + +
+ +

+ {{ __('Edit') }} id={{ $data[0]->id ?? '' }} of {{ $tableName }} +

+
+ + @if (session('feedback.success')) + {{ session('feedback.success') }} + @endif + @if (session('feedback.error')) + {{ session('feedback.error') }} + @endif + @push('localcss') + + @endpush + +
+
+ @foreach ($data as $d) + + + + @foreach (['field', 'data', 'type'] as $th) + + @endforeach + + + + @foreach ($coldetails as $nam => $typ) + + + + + + + + @endforeach + +
+ {{ $th }} +
{{ $nam }} + {!! nl2br(e($d->$nam)) !!} + {{ $coldetails[$nam] }}
+ @endforeach + + @if (strlen($row ?? '') > 0) +
+ + この行 (id={{ $row }}) をコピーして編集 + + + + この行 (id={{ $row }}) を削除 + +
+ @endif + +
+ + ← {{ $tableName }} に戻る + + + ← Crud Tables に戻る + + +
+ +
+ + +
+
+ @csrf + @method('post') +
+ + @if ($scheduledUpdateCreateUrl) + + @endif + + @push('localjs') + + + + @endpush + + + + @if ($scheduledUpdateCreateUrl) + + @endif + +
diff --git a/resources/views/table.blade.php b/resources/views/table.blade.php new file mode 100644 index 0000000..8e348df --- /dev/null +++ b/resources/views/table.blade.php @@ -0,0 +1,221 @@ + + +
+ + ← Crud Tables に戻る + +
+ +

+ {{ __('Crud Table') }} {{ $tableName }} {{ count($data) }}/{{ $numdata }} +

+
+ + @if (session('feedback.success')) + {{ session('feedback.success') }} + @endif + @if (session('feedback.error')) + {{ session('feedback.error') }} + @endif + @push('localcss') + + @endpush + +
+
+ + + + + + @foreach ($coldetails as $nam => $typ) + + @endforeach + + + @csrf + @method('post') + + + @foreach ($coldetails as $nam => $typ) + + @endforeach + + + + + @foreach ($data as $d) + + + @foreach ($coldetails as $nam => $typ) + @if ($typ == 'tinyint') + + @endforeach + +
Chk + {{ $nam }} ({{ $typ }})
+
+ + + + @else + + @if ($nam == 'id') + {{ $d->$nam }} + @else + {{ $d->$nam }} + @endif + @endif + @endforeach +
+ +
+ + あたらしい行を追加 + +
+ +
+
+ @csrf + @method('post') + + + 選択した行をコピー + + + + 選択した行を削除 + +
+
+ +
+
+ @csrf + @method('post') + + + + autoincrement値を設定 + +
+
+ +
+ + ← Crud Tables に戻る + +
+ +
+ + すべての行を削除(truncate) + +
+
+
+
+ @csrf + @method('post') +
+ + @if ($scheduledUpdateCreateUrl) + + @endif + + @push('localjs') + + + + @endpush + + + + @if ($scheduledUpdateCreateUrl) + + @endif + +
diff --git a/routes/web.php b/routes/web.php new file mode 100644 index 0000000..7f8c30f --- /dev/null +++ b/routes/web.php @@ -0,0 +1,18 @@ +group(function () use ($base, $prefix) { + Route::get($base, [CrudController::class, 'index'])->name($prefix . 'crud'); + Route::get($base . 'copy', [CrudController::class, 'copy'])->name($prefix . 'crudcopy'); + Route::get($base . 'delete', [CrudController::class, 'delete'])->name($prefix . 'cruddelete'); + Route::post($base . 'chkdelete', [CrudController::class, 'chkdelete'])->name($prefix . 'crudchkdelete'); // チェックしたものを削除またはコピー + Route::post($base . 'setautoinc', [CrudController::class, 'setautoinc'])->name($prefix . 'crud_setautoinc'); // autoincrement値を設定 + Route::get($base . 'new', [CrudController::class, 'create'])->name($prefix . 'crudnew'); + Route::get($base . 'truncate', [CrudController::class, 'truncate'])->name($prefix . 'crudtruncate'); + Route::post($base, [CrudController::class, 'post'])->name($prefix . 'crudpost'); +}); diff --git a/src/AdminCrud.php b/src/AdminCrud.php new file mode 100644 index 0000000..a22d252 --- /dev/null +++ b/src/AdminCrud.php @@ -0,0 +1,35 @@ +mergeConfigFrom(__DIR__ . '/../config/admin-crud.php', 'admin-crud'); + } + + public function boot(): void + { + $this->loadViewsFrom(__DIR__ . '/../resources/views', 'admin-crud'); + $this->loadRoutesFrom(__DIR__ . '/../routes/web.php'); + + Blade::anonymousComponentPath(__DIR__ . '/../resources/views/components', 'admin-crud'); + + if ($this->app->runningInConsole()) { + $this->publishes([ + __DIR__ . '/../config/admin-crud.php' => config_path('admin-crud.php'), + ], 'admin-crud-config'); + + $this->publishes([ + __DIR__ . '/../resources/js' => public_path('vendor/admin-crud/js'), + __DIR__ . '/../resources/css' => public_path('vendor/admin-crud/css'), + ], 'admin-crud-assets'); + } + } +} diff --git a/src/Http/Controllers/CrudController.php b/src/Http/Controllers/CrudController.php new file mode 100644 index 0000000..33ac08d --- /dev/null +++ b/src/Http/Controllers/CrudController.php @@ -0,0 +1,317 @@ +authorizeCrud(config('admin-crud.roles', 'admin')); + + if (!$req->has('table')) { + $tables = Schema::tables(); + $tableDataCounts = []; + foreach ($tables as $tn) { + $tableDataCounts[$tn] = DB::table($tn)->count(); + } + + return view('admin-crud::db', $this->viewVars(compact('tables', 'tableDataCounts'))); + } + + $tableName = (string) $req->input('table'); + $this->guardTable($tableName); + $coldetails = Schema::columnDetails($tableName); + $limit = (int) config('admin-crud.limit', 100); + + $whereBy = []; + $row = null; + if ($req->isMethod('post')) { + foreach ($req->all() as $fname => $fval) { + if (str_starts_with($fname, 'whereBy__') && $fval !== null && $fval !== '') { + $whereBy[substr($fname, 9)] = $fval; + } + } + } + if ($req->has('row')) { // 単一行編集モード + $whereBy['id'] = $req->input('row'); + $row = $req->input('row'); + } + + if (count($whereBy) === 0) { + if (isset($coldetails['created_at'])) { + $data = DB::table($tableName)->orderByDesc('created_at')->limit($limit)->get()->toArray(); + } elseif (isset($coldetails['id'])) { + $data = DB::table($tableName)->orderBy('id')->limit($limit)->get()->toArray(); + } else { + $data = DB::table($tableName)->limit($limit)->get()->toArray(); + } + $numdata = DB::table($tableName)->count(); + } else { + $query = DB::table($tableName); + foreach ($whereBy as $fn => $fv) { + if (!isset($coldetails[$fn])) { + continue; + } + $lowertype = strtolower($coldetails[$fn]); + if (in_array($lowertype, ['integer', 'bigint unsigned', 'int'], true)) { + $query->where($fn, '=', $fv); + } else { + $query->where($fn, 'LIKE', '%' . $fv . '%'); + } + } + if (isset($coldetails['orderint'])) { + $query->orderBy('orderint'); + } + $numdata = $query->count(); + $data = $query->limit($limit)->get()->toArray(); + } + + $view = $req->has('row') ? 'admin-crud::row' : 'admin-crud::table'; + [$scheduledUpdateTargetType, $scheduledUpdateCreateUrl] = $this->scheduledUpdate($tableName); + $nextId = (int) (DB::table($tableName)->max('id') ?? 0) + 1; + + return view($view, $this->viewVars(compact( + 'tableName', + 'coldetails', + 'data', + 'whereBy', + 'numdata', + 'row', + 'scheduledUpdateTargetType', + 'scheduledUpdateCreateUrl', + 'nextId' + ))); + } + + /** + * セルのインライン更新 (Ajax)。whereBy__ があれば絞り込み表示へフォワード。 + */ + public function post(Request $req) + { + $this->authorizeCrud(config('admin-crud.post_roles', 'admin')); + + foreach ($req->all() as $fname => $fval) { + if (str_starts_with($fname, 'whereBy__')) { + return $this->index($req); + } + } + + $tableName = (string) $req->input('table'); + $this->guardTable($tableName); + $field = (string) $req->input('field'); + abort_unless(array_key_exists($field, Schema::columnDetails($tableName)), 400); + + if ($req->input('dtype') === 'tinyint') { + $currentVal = (int) DB::table($tableName)->where('id', $req->input('data_id'))->value($field); + $newVal = 1 - $currentVal; + DB::table($tableName)->where('id', $req->input('data_id'))->update([$field => $newVal]); + + return "TOGGLE {$newVal} {$req->input('tdid')}"; + } + + DB::table($tableName)->where('id', $req->input('data_id'))->update([$field => $req->input('val')]); + + return "OK {$req->input('tdid')}"; + } + + /** + * 新しい行を追加 (モデルのfactoryを利用) + */ + public function create(Request $req) + { + $this->authorizeCrud(config('admin-crud.roles', 'admin')); + $tableName = (string) $req->input('table'); + $this->guardTable($tableName); + + $model = AdminCrud::modelFor($tableName); + if ($model && method_exists($model, 'factory')) { + $model::factory()->create(); + } + + return redirect(AdminCrud::route('crud', ['table' => $tableName])); + } + + /** + * 全行削除 (truncate) + */ + public function truncate(Request $req) + { + $this->authorizeCrud(config('admin-crud.roles', 'admin')); + $tableName = (string) $req->input('table'); + $this->guardTable($tableName); + + $model = AdminCrud::modelFor($tableName); + if ($model) { + $model::truncate(); + } elseif (in_array(DB::connection()->getDriverName(), ['sqlite', 'sqlite_testing'], true)) { + DB::table($tableName)->delete(); // SQLiteにはTRUNCATEがない + } else { + DB::statement("TRUNCATE TABLE {$tableName}"); + } + + return redirect(AdminCrud::route('crud', ['table' => $tableName])); + } + + /** + * 行をコピー (replicate) + */ + public function copy(Request $req) + { + $this->authorizeCrud(config('admin-crud.roles', 'admin')); + $tableName = (string) $req->input('table'); + $this->guardTable($tableName); + $row = $req->input('row'); + + $model = AdminCrud::modelFor($tableName); + if ($model) { + $datum = $model::find($row); + if (isset($datum)) { + $newdatum = $datum->replicate(); + $newdatum->save(); + + return redirect(AdminCrud::route('crud', ['table' => $tableName, 'row' => $newdatum->id])); + } + } + + return redirect(AdminCrud::route('crud', ['table' => $tableName])); + } + + /** + * 行を削除 + */ + public function delete(Request $req) + { + $this->authorizeCrud(config('admin-crud.roles', 'admin')); + $tableName = (string) $req->input('table'); + $this->guardTable($tableName); + $row = $req->input('row'); + + $model = AdminCrud::modelFor($tableName); + if ($model) { + $datum = $model::find($row); + if (isset($datum)) { + $datum->delete(); + } + } + + return redirect(AdminCrud::route('crud', ['table' => $tableName])); + } + + /** + * チェックした行を削除またはコピー + */ + public function chkdelete(Request $req) + { + $this->authorizeCrud(config('admin-crud.roles', 'admin')); + $tableName = (string) $req->input('table'); + $this->guardTable($tableName); + + $model = AdminCrud::modelFor($tableName); + if ($model) { + $dids = $req->input('did'); + if (!$dids || count($dids) === 0) { + return redirect(AdminCrud::route('crud', ['table' => $tableName])) + ->with('feedback.error', '削除またはコピーする行を選択してください。'); + } + if ($req->input('action') === 'bdelete') { + $model::whereIn('id', $dids)->forceDelete(); + } + if ($req->input('action') === 'bcopy') { + foreach ($dids as $did) { + $datum = $model::find($did); + if (isset($datum)) { + $newdatum = $datum->replicate(); + $newdatum->save(); + } + } + } + } else { + info('crudchkdelete: model for ' . $tableName . ' is not found.'); + } + + return redirect(AdminCrud::route('crud', ['table' => $tableName])); + } + + /** + * autoincrement値を設定する + */ + public function setautoinc(Request $req) + { + $this->authorizeCrud(config('admin-crud.roles', 'admin')); + $tableName = (string) $req->input('table'); + $this->guardTable($tableName); + + $autoinc = $req->input('autoinc'); + if (!is_numeric($autoinc) || (int) $autoinc < 1) { + return redirect(AdminCrud::route('crud', ['table' => $tableName])) + ->with('feedback.error', 'autoincには1以上の数値を入力してください。'); + } + + if (in_array(DB::connection()->getDriverName(), ['sqlite', 'sqlite_testing'], true)) { + DB::table('sqlite_sequence')->where('name', $tableName)->update(['seq' => (int) $autoinc - 1]); + } else { + DB::statement("ALTER TABLE {$tableName} AUTO_INCREMENT = " . (int) $autoinc); + } + + return redirect(AdminCrud::route('crud', ['table' => $tableName])) + ->with('feedback.success', "{$tableName}のautoincを{$autoinc}に設定しました。"); + } + + /** + * Gate による認可チェック。'gate' が null ならスキップ。 + */ + protected function authorizeCrud(string $roles): void + { + $gate = config('admin-crud.gate'); + if (empty($gate)) { + return; + } + $user = auth()->user(); + if (!$user || !$user->can($gate, $roles)) { + abort(403); + } + } + + /** + * 存在しないテーブル名への生SQL混入を防ぐため、テーブル一覧で検証する。 + */ + protected function guardTable(string $tableName): void + { + abort_unless(in_array($tableName, Schema::tables(), true), 404); + } + + /** + * 予約更新連携が有効でモデルが存在する場合のみ [モデルクラス, 作成URL] を返す。 + */ + protected function scheduledUpdate(string $tableName): array + { + $routeName = config('admin-crud.scheduled_update_create_route'); + $model = AdminCrud::modelFor($tableName); + if ($routeName && $model && Route::has($routeName)) { + return [$model, route($routeName)]; + } + + return [null, null]; + } + + /** + * 全ビュー共通の変数を追加する。 + */ + protected function viewVars(array $vars): array + { + return $vars + [ + 'layout' => config('admin-crud.layout_component', 'admin-crud::layout'), + 'jqueryUrl' => config('admin-crud.jquery_url'), + ]; + } +} diff --git a/src/Support/Schema.php b/src/Support/Schema.php new file mode 100644 index 0000000..73b3e49 --- /dev/null +++ b/src/Support/Schema.php @@ -0,0 +1,88 @@ + + */ + public static function tables(): array + { + $driver = DB::connection()->getDriverName(); + if (in_array($driver, ['sqlite', 'sqlite_testing'], true)) { + $rows = DB::select("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"); + $tables = array_map(fn ($item) => $item->name, $rows); + } else { + $rows = DB::select('SHOW TABLES'); + $tables = []; + foreach ($rows as $obj) { + foreach ($obj as $tn) { + $tables[] = $tn; + } + } + } + sort($tables); + + return $tables; + } + + /** + * カラム名 => データ型 (かっこ以下は除去) の連想配列を返す。 + * + * @return array + */ + public static function columnDetails(string $tableName): array + { + $driver = DB::connection()->getDriverName(); + $coldetails = []; + if (in_array($driver, ['sqlite', 'sqlite_testing'], true)) { + $columns = DB::select("pragma table_info('{$tableName}')"); + foreach ($columns as $cc) { + $coldetails[$cc->name] = (string) $cc->type; + } + } else { + $columns = DB::select("show full columns from `{$tableName}`"); + foreach ($columns as $colary) { + $coldetails[$colary->Field] = $colary->Type; + } + } + // Type() のかっこ以下は取り除く + foreach ($coldetails as $f => $t) { + $pos = strpos($t, '('); + if ($pos > 0) { + $coldetails[$f] = substr($t, 0, $pos); + } + } + + return $coldetails; + } + + /** + * カラム名 => カラムコメント (MySQL) またはカラム名 (SQLite) の連想配列を返す。 + * + * @return array + */ + public static function tableComments(string $dbName, string $tableName): array + { + $driver = DB::connection()->getDriverName(); + $coldetails = []; + if (in_array($driver, ['sqlite', 'sqlite_testing'], true)) { + $columns = DB::select("pragma table_info('{$tableName}')"); + foreach ($columns as $cc) { + $coldetails[$cc->name] = $cc->name; + } + } else { + $columns = DB::select("SELECT COLUMN_NAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{$dbName}' AND TABLE_NAME = '{$tableName}'"); + foreach ($columns as $colary) { + $coldetails[$colary->COLUMN_NAME] = $colary->COLUMN_COMMENT; + } + } + + return $coldetails; + } +} diff --git a/src/helpers.php b/src/helpers.php new file mode 100644 index 0000000..444e242 --- /dev/null +++ b/src/helpers.php @@ -0,0 +1,13 @@ +set('app.key', 'base64:' . base64_encode(random_bytes(32))); + $app['config']->set('database.default', 'testing'); + $app['config']->set('database.connections.testing', [ + 'driver' => 'sqlite', + 'database' => ':memory:', + 'prefix' => '', + ]); + // テストでは認証・Gate を無効化 + $app['config']->set('admin-crud.middleware', ['web']); + $app['config']->set('admin-crud.gate', null); + } + + protected function setUp(): void + { + parent::setUp(); + Schema::create('papers', function (Blueprint $table) { + $table->id(); + $table->string('title')->nullable(); + $table->tinyInteger('open')->default(0); + $table->timestamps(); + }); + DB::table('papers')->insert([ + ['title' => 'Hello', 'open' => 0, 'created_at' => now(), 'updated_at' => now()], + ['title' => 'World', 'open' => 1, 'created_at' => now(), 'updated_at' => now()], + ]); + } + + public function test_route_names_and_uris_match_original(): void + { + $this->assertEquals(url('/admin_crud'), route('admin.crud')); + $this->assertEquals(url('/admin_crudcopy'), route('admin.crudcopy')); + $this->assertEquals(url('/admin_cruddelete'), route('admin.cruddelete')); + $this->assertEquals(url('/admin_crudchkdelete'), route('admin.crudchkdelete')); + $this->assertEquals(url('/admin_crudsetautoinc'), route('admin.crud_setautoinc')); + $this->assertEquals(url('/admin_crudnew'), route('admin.crudnew')); + $this->assertEquals(url('/admin_crudtruncate'), route('admin.crudtruncate')); + $this->assertEquals(url('/admin_crud'), route('admin.crudpost')); + } + + public function test_table_list(): void + { + $this->get('/admin_crud') + ->assertOk() + ->assertSee('papers') + ->assertSee('Crud Tables'); + } + + public function test_table_view(): void + { + $this->get('/admin_crud?table=papers') + ->assertOk() + ->assertSee('Hello') + ->assertSee('World'); + } + + public function test_row_view(): void + { + $this->get('/admin_crud?table=papers&row=1') + ->assertOk() + ->assertSee('Hello'); + } + + public function test_whereby_filter_via_post(): void + { + $this->post('/admin_crud', ['table' => 'papers', 'whereBy__title' => 'Hell']) + ->assertOk() + ->assertSee('Hello') + ->assertDontSee('World'); + } + + public function test_post_update_cell(): void + { + $this->post('/admin_crud', [ + 'table' => 'papers', + 'field' => 'title', + 'data_id' => 1, + 'dtype' => 'varchar', + 'val' => 'Updated', + 'tdid' => 'title__1__varchar', + ])->assertOk(); + + $this->assertEquals('Updated', DB::table('papers')->where('id', 1)->value('title')); + } + + public function test_post_toggle_tinyint(): void + { + $this->post('/admin_crud', [ + 'table' => 'papers', + 'field' => 'open', + 'data_id' => 1, + 'dtype' => 'tinyint', + 'tdid' => 'open__1__tinyint', + ])->assertOk(); + + $this->assertEquals(1, DB::table('papers')->where('id', 1)->value('open')); + } + + public function test_post_rejects_unknown_table(): void + { + $this->post('/admin_crud', [ + 'table' => 'papers;DROP TABLE papers', + 'field' => 'title', + 'data_id' => 1, + 'dtype' => 'varchar', + 'val' => 'x', + 'tdid' => 'x', + ])->assertNotFound(); + } + + public function test_post_rejects_unknown_field(): void + { + $this->post('/admin_crud', [ + 'table' => 'papers', + 'field' => 'nonexistent', + 'data_id' => 1, + 'dtype' => 'varchar', + 'val' => 'x', + 'tdid' => 'x', + ])->assertStatus(400); + } + + public function test_unknown_table_is_404(): void + { + $this->get('/admin_crud?table=no_such_table')->assertNotFound(); + } + + public function test_truncate(): void + { + $this->get('/admin_crudtruncate?table=papers')->assertRedirect(); + $this->assertEquals(0, DB::table('papers')->count()); + } + + public function test_setautoinc(): void + { + $this->post('/admin_crudsetautoinc', ['table' => 'papers', 'autoinc' => 50]) + ->assertRedirect(); + $seq = DB::table('sqlite_sequence')->where('name', 'papers')->value('seq'); + $this->assertEquals(49, (int) $seq); + } + + public function test_setautoinc_validation(): void + { + $this->post('/admin_crudsetautoinc', ['table' => 'papers', 'autoinc' => 0]) + ->assertRedirect() + ->assertSessionHas('feedback.error'); + } + + public function test_new_without_model_just_redirects(): void + { + $this->get('/admin_crudnew?table=papers')->assertRedirect(); + $this->assertEquals(2, DB::table('papers')->count()); + } +}