Laravel针对该物联网项目的数据库迁移文件核心内容

以下是针对该物联网项目的数据库迁移文件核心内容，包含设备管理、用户权限和操作日志等关键表结构：

1. 设备表迁移文件 (2025_05_16_000000_create_devices_table.php)

Schema::create('devices', function (Blueprint $table) {
    $table->id();
    $table->string('name')->comment('设备名称');
    $table->string('mac_address')->unique()->comment('MAC地址');
    $table->enum('type', ['switch', 'sensor'])->default('switch');
    $table->string('location')->nullable()->comment('安装位置');
    $table->json('gpio_config')->comment('GPIO配置 {"pin":1,"active_low":true}');
    $table->timestamp('last_online')->nullable()->comment('最后在线时间');
    $table->foreignId('user_id')->constrained()->comment('所属用户');
    $table->timestamps();
});

2. 用户设备关联表 (2025_05_16_000001_create_device_user_table.php)

Schema::create('device_user', function (Blueprint $table) {
    $table->foreignId('device_id')->constrained()->cascadeOnDelete();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->enum('permission', ['owner', 'operator'])->default('operator');
    $table->primary(['device_id', 'user_id']);
});

3. 设备操作日志表 (2025_05_16_000002_create_device_logs_table.php)

Schema::create('device_logs', function (Blueprint $table) {
    $table->id();
    $table->foreignId('device_id')->constrained()->cascadeOnDelete();
    $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
    $table->string('command')->comment('操作指令');
    $table->string('source')->comment('操作来源: web/mobile/api');
    $table->string('ip_address')->nullable();
    $table->timestamp('executed_at')->useCurrent();
    $table->boolean('success')->default(true);
    $table->text('response')->nullable()->comment('设备响应数据');
});

4. 设备状态历史表 (2025_05_16_000003_create_device_status_table.php)

Schema::create('device_status', function (Blueprint $table) {
    $table->id();
    $table->foreignId('device_id')->constrained();
    $table->float('voltage')->nullable()->comment('当前电压');
    $table->float('current')->nullable()->comment('实时电流');
    $table->boolean('relay_status')->default(false);
    $table->float('temperature')->nullable();
    $table->float('signal_strength')->comment('WiFi信号强度');
    $table->timestamp('recorded_at')->useCurrent();
});

5. 索引优化追加迁移 (2025_05_16_000004_add_indexes_to_tables.php)

Schema::table('device_logs', function (Blueprint $table) {
    $table->index(['device_id', 'executed_at']);
    $table->index('command');
});

Schema::table('device_status', function (Blueprint $table) {
    $table->index(['device_id', 'recorded_at']);
});

关键设计要点：

1. 使用json类型存储硬件配置参数
2. 设备-用户多对多关系实现共享控制
3. 状态与日志分离设计，高频状态数据单独存储
4. 所有外键约束包含级联删除规则
5. 为查询频繁字段添加复合索引

实际部署时建议：

php artisan migrate --seed  # 同时运行配套的Seeder
php artisan make:observer DeviceObserver  # 创建设备状态变更观察器