2020_12_24_074739_table_improvement.php 7.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188
  1. <?php
  2. use App\Models\Coupon;
  3. use App\Models\Node;
  4. use App\Models\NodeOnlineIp;
  5. use App\Models\Order;
  6. use App\Models\RuleGroup;
  7. use App\Models\User;
  8. use App\Models\UserGroup;
  9. use Illuminate\Database\Migrations\Migration;
  10. use Illuminate\Database\Schema\Blueprint;
  11. use Illuminate\Support\Facades\Schema;
  12. class TableImprovement extends Migration
  13. {
  14. public function up()
  15. {
  16. // ----- 开始 数据库表关系优化 -----
  17. Schema::table('level', function (Blueprint $table) {
  18. $table->unique('level');
  19. });
  20. Schema::table('node_certificate', function (Blueprint $table) {
  21. $table->unique('domain');
  22. });
  23. Schema::table('ss_node', function (Blueprint $table) {
  24. $table->unsignedInteger('rule_group_id')->nullable()->comment('从属规则分组ID')->after('level');
  25. $table->foreign('rule_group_id')->references('id')->on('rule_group')->nullOnDelete();
  26. $table->rename('node');
  27. });
  28. Schema::table('ss_node_info', function (Blueprint $table) {
  29. $table->unsignedInteger('node_id')->comment('节点ID')->change();
  30. $table->rename('node_heartbeat');
  31. });
  32. Schema::table('ss_node_ip', function (Blueprint $table) {
  33. $table->rename('node_online_ip');
  34. });
  35. NodeOnlineIp::whereNodeId(0)->update(['node_id' => null]);
  36. NodeOnlineIp::whereUserId(0)->update(['user_id' => null]);
  37. Schema::table('node_online_ip', function (Blueprint $table) {
  38. $table->unsignedInteger('node_id')->comment('节点ID')->change();
  39. $table->unsignedInteger('user_id')->default(null)->nullable()->change();
  40. });
  41. Schema::table('ss_node_online_log', function (Blueprint $table) {
  42. $table->rename('node_online_log');
  43. });
  44. Schema::table('node_label', function (Blueprint $table) {
  45. $table->unsignedInteger('node_id')->comment('节点ID')->change();
  46. $table->unsignedInteger('label_id')->comment('标签ID')->change();
  47. $table->unique(['node_id', 'label_id']);
  48. $table->rename('label_node');
  49. });
  50. Order::whereGoodsId(0)->update(['goods_id' => null]);
  51. Order::whereCouponId(0)->orWhereNotIn('coupon_id', Coupon::withTrashed()->pluck('id')->toArray())->update(['coupon_id' => null]);
  52. Schema::table('order', function (Blueprint $table) {
  53. $table->unsignedInteger('user_id')->comment('购买者ID')->change();
  54. $table->foreign('goods_id')->references('id')->on('goods')->nullOnDelete();
  55. $table->foreign('coupon_id')->references('id')->on('coupon')->nullOnDelete();
  56. });
  57. Schema::create('node_user_group', function (Blueprint $table) {
  58. $table->increments('id');
  59. $table->unsignedInteger('node_id')->comment('节点ID');
  60. $table->unsignedInteger('user_group_id')->comment('从属用户分组ID');
  61. $table->unique(['user_group_id', 'node_id']);
  62. $table->foreign('node_id')->references('id')->on('node')->cascadeOnDelete();
  63. $table->foreign('user_group_id')->references('id')->on('user_group')->cascadeOnDelete();
  64. });
  65. Schema::table('payment', function (Blueprint $table) {
  66. $table->foreign('order_id')->references('id')->on('order')->cascadeOnDelete();
  67. });
  68. Schema::table('referral_apply', function (Blueprint $table) {
  69. $table->unsignedInteger('user_id')->comment('申请者ID')->change();
  70. });
  71. Schema::table('rule_log', function (Blueprint $table) {
  72. $table->unsignedInteger('user_id')->comment('触发者ID')->change();
  73. });
  74. Schema::create('rule_rule_group', function (Blueprint $table) {
  75. $table->increments('id');
  76. $table->unsignedInteger('rule_id')->comment('规则ID');
  77. $table->unsignedInteger('rule_group_id')->comment('从属规则分组ID');
  78. $table->unique(['rule_group_id', 'rule_id']);
  79. $table->foreign('rule_id')->references('id')->on('rule')->cascadeOnDelete();
  80. $table->foreign('rule_group_id')->references('id')->on('rule_group')->cascadeOnDelete();
  81. });
  82. Schema::table('ticket', function (Blueprint $table) {
  83. $table->unsignedInteger('user_id')->comment('用户ID')->change();
  84. });
  85. Schema::table('ticket_reply', function (Blueprint $table) {
  86. $table->unsignedInteger('ticket_id')->comment('工单ID')->change();
  87. });
  88. Schema::table('user', function (Blueprint $table) {
  89. $table->unsignedInteger('group_id')->nullable()->default(null)->comment('所属分组')->change();
  90. });
  91. User::whereGroupId(0)->update(['group_id' => null]);
  92. Schema::table('user', function (Blueprint $table) {
  93. $table->renameColumn('group_id', 'user_group_id');
  94. $table->foreign('user_group_id')->references('id')->on('user_group')->nullOnDelete();
  95. });
  96. Schema::table('user_baned_log', function (Blueprint $table) {
  97. $table->unsignedInteger('user_id')->comment('用户ID')->change();
  98. });
  99. Schema::table('user_credit_log', function (Blueprint $table) {
  100. $table->unsignedInteger('user_id')->comment('用户ID')->change();
  101. });
  102. Schema::table('user_daily_data_flow', function (Blueprint $table) {
  103. $table->unsignedInteger('user_id')->comment('用户ID')->change();
  104. });
  105. Schema::table('user_data_modify_log', function (Blueprint $table) {
  106. $table->unsignedInteger('user_id')->comment('用户ID')->change();
  107. });
  108. Schema::table('user_hourly_data_flow', function (Blueprint $table) {
  109. $table->unsignedInteger('user_id')->comment('用户ID')->change();
  110. });
  111. Schema::table('user_login_log', function (Blueprint $table) {
  112. $table->unsignedInteger('user_id')->comment('用户ID')->change();
  113. });
  114. Schema::table('user_subscribe', function (Blueprint $table) {
  115. $table->unsignedInteger('user_id')->comment('用户ID')->change();
  116. });
  117. Schema::table('user_traffic_log', function (Blueprint $table) {
  118. $table->unsignedInteger('user_id')->comment('用户ID')->change();
  119. $table->unsignedInteger('node_id')->comment('节点ID')->change();
  120. });
  121. // ----- 结束 数据库表关系优化 -----
  122. // ----- 开始 数据转化 & 弃用数据 -----
  123. foreach (RuleGroup::all() as $group) {
  124. $group->rules()->attach(json_decode($group->rules, true));
  125. foreach (json_decode($group->nodes, true) as $id) {
  126. $node = Node::find($id);
  127. if ($node) {
  128. $node->update(['rule_group_id' => $group->id]);
  129. }
  130. }
  131. }
  132. foreach (UserGroup::all() as $group) {
  133. $group->nodes()->attach(json_decode($group->nodes, true));
  134. }
  135. Schema::table('rule_group', function (Blueprint $table) {
  136. $table->dropColumn('nodes');
  137. $table->dropColumn('rules');
  138. });
  139. Schema::table('user_group', function (Blueprint $table) {
  140. $table->dropColumn('nodes');
  141. });
  142. Schema::table('node_rule', function (Blueprint $table) {
  143. $table->drop();
  144. });
  145. Schema::table('rule_group_node', function (Blueprint $table) {
  146. $table->drop();
  147. });
  148. // ----- 结束 数据转化 & 弃用数据 -----
  149. }
  150. public function down()
  151. {
  152. // 不可逆
  153. }
  154. }