2020_11_10_075555_improve_table.php 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450
  1. <?php
  2. use App\Models\CouponLog;
  3. use App\Models\Invite;
  4. use App\Models\Ticket;
  5. use App\Models\TicketReply;
  6. use App\Models\User;
  7. use App\Models\UserCreditLog;
  8. use App\Models\UserDailyDataFlow;
  9. use App\Models\UserDataModifyLog;
  10. use App\Models\UserHourlyDataFlow;
  11. use Illuminate\Database\Migrations\Migration;
  12. use Illuminate\Database\Schema\Blueprint;
  13. use Illuminate\Support\Facades\Schema;
  14. class ImproveTable extends Migration
  15. {
  16. /**
  17. * Run the migrations.
  18. *
  19. * @return void
  20. */
  21. public function up()
  22. {
  23. Schema::disableForeignKeyConstraints();
  24. Schema::table('coupon_log', function (Blueprint $table) {
  25. $table->unsignedInteger('coupon_id')->default(null)->nullable()->change();
  26. $table->unsignedInteger('goods_id')->default(null)->nullable()->change();
  27. $table->unsignedInteger('order_id')->default(null)->nullable()->change();
  28. $table->foreign('coupon_id')->references('id')->on('coupon')->nullOnDelete();
  29. $table->foreign('goods_id')->references('id')->on('goods')->nullOnDelete();
  30. $table->foreign('order_id')->references('id')->on('order')->cascadeOnDelete();
  31. });
  32. Schema::table('email_filter', function (Blueprint $table) {
  33. $table->index(['words', 'type']);
  34. });
  35. Schema::table('invite', function (Blueprint $table) {
  36. $table->unsignedInteger('inviter_id')->default(null)->nullable()->change();
  37. $table->unsignedInteger('invitee_id')->default(null)->nullable()->change();
  38. $table->foreign('inviter_id')->references('id')->on('user')->cascadeOnDelete();
  39. $table->foreign('invitee_id')->references('id')->on('user')->nullOnDelete();
  40. });
  41. Schema::table('node_auth', function (Blueprint $table) {
  42. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  43. });
  44. Schema::table('node_daily_data_flow', function (Blueprint $table) {
  45. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  46. });
  47. Schema::table('node_hourly_data_flow', function (Blueprint $table) {
  48. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  49. });
  50. Schema::table('node_label', function (Blueprint $table) {
  51. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  52. $table->foreign('label_id')->references('id')->on('label')->cascadeOnDelete();
  53. });
  54. Schema::table('node_rule', function (Blueprint $table) {
  55. $table->index(['node_id', 'rule_id']);
  56. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  57. $table->foreign('rule_id')->references('id')->on('rule')->cascadeOnDelete();
  58. });
  59. Schema::table('order', function (Blueprint $table) {
  60. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  61. });
  62. Schema::table('payment', function (Blueprint $table) {
  63. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  64. $table->index(['user_id', 'order_id']);
  65. });
  66. Schema::table('referral_apply', function (Blueprint $table) {
  67. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  68. });
  69. Schema::table('referral_log', function (Blueprint $table) {
  70. $table->unsignedInteger('invitee_id')->default(null)->nullable()->change();
  71. $table->unsignedInteger('order_id')->default(null)->nullable()->change();
  72. $table->foreign('inviter_id')->references('id')->on('user')->cascadeOnDelete();
  73. $table->foreign('invitee_id')->references('id')->on('user')->nullOnDelete();
  74. $table->foreign('order_id')->references('id')->on('order')->nullOnDelete();
  75. $table->index(['inviter_id', 'invitee_id']);
  76. });
  77. Schema::table('rule_group_node', function (Blueprint $table) {
  78. $table->foreign('rule_group_id')->references('id')->on('rule_group')->cascadeOnDelete();
  79. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  80. });
  81. Schema::table('rule_log', function (Blueprint $table) {
  82. $table->unsignedInteger('node_id')->default(null)->nullable()->change();
  83. $table->unsignedInteger('rule_id')->nullable()->change();
  84. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  85. $table->foreign('node_id')->references('id')->on('ss_node')->nullOnDelete();
  86. $table->foreign('rule_id')->references('id')->on('rule')->nullOnDelete();
  87. });
  88. Schema::table('ss_config', function (Blueprint $table) {
  89. $table->index('type');
  90. });
  91. Schema::table('ss_node', function (Blueprint $table) {
  92. $table->index('type');
  93. });
  94. Schema::table('ss_node_info', function (Blueprint $table) {
  95. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  96. });
  97. Schema::table('ss_node_ip', function (Blueprint $table) {
  98. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  99. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  100. });
  101. Schema::table('ss_node_online_log', function (Blueprint $table) {
  102. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  103. });
  104. Schema::table('ticket', function (Blueprint $table) {
  105. $table->unsignedInteger('admin_id')->default(null)->nullable()->comment('管理员ID')->change();
  106. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  107. $table->foreign('admin_id')->references('id')->on('user')->nullOnDelete();
  108. });
  109. Schema::table('ticket_reply', function (Blueprint $table) {
  110. $table->unsignedInteger('user_id')->default(null)->nullable()->comment('用户ID')->change();
  111. $table->unsignedInteger('admin_id')->default(null)->nullable()->comment('管理员ID')->change();
  112. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  113. $table->foreign('admin_id')->references('id')->on('user')->nullOnDelete();
  114. $table->foreign('ticket_id')->references('id')->on('ticket')->cascadeOnDelete();
  115. });
  116. Schema::table('user', function (Blueprint $table) {
  117. $table->foreign('inviter_id')->references('id')->on('user')->nullOnDelete();
  118. });
  119. Schema::table('user_baned_log', function (Blueprint $table) {
  120. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  121. });
  122. Schema::table('user_credit_log', function (Blueprint $table) {
  123. $table->unsignedInteger('order_id')->default(null)->nullable()->comment('订单ID')->change();
  124. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  125. $table->foreign('order_id')->references('id')->on('order')->nullOnDelete();
  126. });
  127. Schema::table('user_daily_data_flow', function (Blueprint $table) {
  128. $table->unsignedInteger('node_id')->default(null)->nullable()->comment('节点ID,null表示统计全部节点')->change();
  129. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  130. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  131. });
  132. Schema::table('user_data_modify_log', function (Blueprint $table) {
  133. $table->unsignedInteger('order_id')->default(null)->nullable()->comment('发生的订单ID')->change();
  134. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  135. $table->foreign('order_id')->references('id')->on('order')->nullOnDelete();
  136. });
  137. Schema::table('user_hourly_data_flow', function (Blueprint $table) {
  138. $table->unsignedInteger('node_id')->default(null)->nullable()->comment('节点ID,null表示统计全部节点')->change();
  139. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  140. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  141. });
  142. Schema::table('user_login_log', function (Blueprint $table) {
  143. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  144. });
  145. Schema::table('user_subscribe', function (Blueprint $table) {
  146. $table->unique('code');
  147. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  148. });
  149. Schema::table('user_subscribe_log', function (Blueprint $table) {
  150. $table->foreign('user_subscribe_id')->references('id')->on('user_subscribe')->cascadeOnDelete();
  151. });
  152. Schema::table('user_traffic_log', function (Blueprint $table) {
  153. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  154. $table->foreign('node_id')->references('id')->on('ss_node')->cascadeOnDelete();
  155. });
  156. Schema::table('verify', function (Blueprint $table) {
  157. $table->foreign('user_id')->references('id')->on('user')->cascadeOnDelete();
  158. });
  159. CouponLog::whereCouponId(0)->update(['coupon_id' => null]);
  160. CouponLog::whereGoodsId(0)->update(['goods_id' => null]);
  161. CouponLog::whereOrderId(0)->update(['order_id' => null]);
  162. Ticket::whereAdminId(0)->update(['admin_id' => null]);
  163. TicketReply::whereUserId(0)->update(['user_id' => null]);
  164. TicketReply::whereAdminId(0)->update(['admin_id' => null]);
  165. UserCreditLog::whereOrderId(0)->update(['order_id' => null]);
  166. UserHourlyDataFlow::whereNodeId(0)->update(['node_id' => null]);
  167. UserDailyDataFlow::whereNodeId(0)->update(['node_id' => null]);
  168. UserDataModifyLog::whereOrderId(0)->update(['order_id' => null]);
  169. Invite::whereInviterId(0)->update(['inviter_id' => null]);
  170. Invite::whereInviteeId(0)->update(['invitee_id' => null]);
  171. User::whereInviterId(0)->update(['inviter_id' => null]);
  172. Schema::enableForeignKeyConstraints();
  173. }
  174. /**
  175. * Reverse the migrations.
  176. *
  177. * @return void
  178. */
  179. public function down()
  180. {
  181. Schema::disableForeignKeyConstraints();
  182. CouponLog::whereCouponId(null)->update(['coupon_id' => 0]);
  183. CouponLog::whereGoodsId(null)->update(['goods_id' => 0]);
  184. CouponLog::whereOrderId(null)->update(['order_id' => 0]);
  185. Ticket::whereAdminId(null)->update(['admin_id' => 0]);
  186. TicketReply::whereUserId(null)->update(['user_id' => 0]);
  187. TicketReply::whereAdminId(null)->update(['admin_id' => 0]);
  188. UserCreditLog::whereOrderId(null)->update(['order_id' => 0]);
  189. UserHourlyDataFlow::whereNodeId(null)->update(['node_id' => 0]);
  190. UserDailyDataFlow::whereNodeId(null)->update(['node_id' => 0]);
  191. UserDataModifyLog::whereOrderId(null)->update(['order_id' => 0]);
  192. Invite::whereInviterId(null)->update(['inviter_id' => 0]);
  193. Schema::table('coupon_log', function (Blueprint $table) {
  194. $table->dropForeign(['coupon_id']);
  195. $table->dropForeign(['goods_id']);
  196. $table->dropForeign(['order_id']);
  197. $table->dropIndex('coupon_log_coupon_id_foreign');
  198. $table->dropIndex('coupon_log_goods_id_foreign');
  199. $table->dropIndex('coupon_log_order_id_foreign');
  200. });
  201. Schema::table('coupon_log', function (Blueprint $table) {
  202. $table->unsignedInteger('coupon_id')->default(0)->nullable(false)->change();
  203. $table->unsignedInteger('goods_id')->default(0)->nullable(false)->change();
  204. $table->unsignedInteger('order_id')->default(0)->nullable(false)->change();
  205. });
  206. Schema::table('email_filter', function (Blueprint $table) {
  207. $table->dropIndex(['words', 'type']);
  208. });
  209. Schema::table('invite', function (Blueprint $table) {
  210. $table->dropForeign(['inviter_id']);
  211. $table->dropForeign(['invitee_id']);
  212. $table->dropIndex('invite_inviter_id_foreign');
  213. $table->dropIndex('invite_invitee_id_foreign');
  214. });
  215. Schema::table('invite', function (Blueprint $table) {
  216. $table->unsignedInteger('inviter_id')->default(0)->nullable(false)->change();
  217. });
  218. Schema::table('node_auth', function (Blueprint $table) {
  219. $table->dropForeign(['node_id']);
  220. $table->dropIndex('node_auth_node_id_foreign');
  221. });
  222. Schema::table('node_daily_data_flow', function (Blueprint $table) {
  223. $table->dropForeign(['node_id']);
  224. });
  225. Schema::table('node_hourly_data_flow', function (Blueprint $table) {
  226. $table->dropForeign(['node_id']);
  227. });
  228. Schema::table('node_label', function (Blueprint $table) {
  229. $table->dropForeign(['node_id']);
  230. $table->dropForeign(['label_id']);
  231. $table->dropIndex('node_label_label_id_foreign');
  232. });
  233. Schema::table('node_rule', function (Blueprint $table) {
  234. $table->dropForeign(['node_id']);
  235. $table->dropForeign(['rule_id']);
  236. $table->dropIndex(['node_id', 'rule_id']);
  237. $table->dropIndex('node_rule_rule_id_foreign');
  238. });
  239. Schema::table('order', function (Blueprint $table) {
  240. $table->dropForeign(['user_id']);
  241. });
  242. Schema::table('payment', function (Blueprint $table) {
  243. $table->dropForeign(['user_id']);
  244. $table->dropIndex(['user_id', 'order_id']);
  245. });
  246. Schema::table('referral_apply', function (Blueprint $table) {
  247. $table->dropForeign(['user_id']);
  248. $table->dropIndex('referral_apply_user_id_foreign');
  249. });
  250. Schema::table('referral_log', function (Blueprint $table) {
  251. $table->dropForeign(['inviter_id']);
  252. $table->dropForeign(['invitee_id']);
  253. $table->dropForeign(['order_id']);
  254. $table->dropIndex(['inviter_id', 'invitee_id']);
  255. $table->dropIndex('referral_log_invitee_id_foreign');
  256. $table->dropIndex('referral_log_order_id_foreign');
  257. });
  258. Schema::table('referral_log', function (Blueprint $table) {
  259. $table->unsignedInteger('invitee_id')->nullable(false)->change();
  260. $table->unsignedInteger('order_id')->nullable(false)->change();
  261. });
  262. Schema::table('rule_group_node', function (Blueprint $table) {
  263. $table->dropForeign(['rule_group_id']);
  264. $table->dropForeign(['node_id']);
  265. $table->dropIndex('rule_group_node_rule_group_id_foreign');
  266. $table->dropIndex('rule_group_node_node_id_foreign');
  267. });
  268. Schema::table('rule_log', function (Blueprint $table) {
  269. $table->dropForeign(['user_id']);
  270. $table->dropForeign(['node_id']);
  271. $table->dropForeign(['rule_id']);
  272. $table->dropIndex('rule_log_node_id_foreign');
  273. $table->dropIndex('rule_log_rule_id_foreign');
  274. });
  275. Schema::table('rule_log', function (Blueprint $table) {
  276. $table->unsignedInteger('node_id')->default(0)->nullable(false)->change();
  277. $table->unsignedInteger('rule_id')->default(0)->nullable(false)->change();
  278. });
  279. Schema::table('ss_config', function (Blueprint $table) {
  280. $table->dropIndex(['type']);
  281. });
  282. Schema::table('ss_node', function (Blueprint $table) {
  283. $table->dropIndex(['type']);
  284. });
  285. Schema::table('ss_node_info', function (Blueprint $table) {
  286. $table->dropForeign(['node_id']);
  287. });
  288. Schema::table('ss_node_ip', function (Blueprint $table) {
  289. $table->dropForeign(['node_id']);
  290. $table->dropForeign(['user_id']);
  291. });
  292. Schema::table('ss_node_online_log', function (Blueprint $table) {
  293. $table->dropForeign(['node_id']);
  294. });
  295. Schema::table('ticket', function (Blueprint $table) {
  296. $table->dropForeign(['user_id']);
  297. $table->dropForeign(['admin_id']);
  298. $table->dropIndex('ticket_user_id_foreign');
  299. $table->dropIndex('ticket_admin_id_foreign');
  300. });
  301. Schema::table('ticket', function (Blueprint $table) {
  302. $table->unsignedInteger('admin_id')->default(0)->nullable(false)->change();
  303. });
  304. Schema::table('ticket_reply', function (Blueprint $table) {
  305. $table->unsignedInteger('user_id')->default(0)->nullable(false)->change();
  306. $table->dropForeign(['user_id']);
  307. $table->dropForeign(['admin_id']);
  308. $table->dropForeign(['ticket_id']);
  309. $table->dropIndex('ticket_reply_user_id_foreign');
  310. $table->dropIndex('ticket_reply_admin_id_foreign');
  311. $table->dropIndex('ticket_reply_ticket_id_foreign');
  312. });
  313. Schema::table('ticket_reply', function (Blueprint $table) {
  314. $table->unsignedInteger('admin_id')->default(0)->nullable(false)->change();
  315. });
  316. Schema::table('user', function (Blueprint $table) {
  317. $table->dropForeign(['inviter_id']);
  318. $table->dropIndex('user_inviter_id_foreign');
  319. });
  320. Schema::table('user_baned_log', function (Blueprint $table) {
  321. $table->dropForeign(['user_id']);
  322. $table->dropIndex('user_baned_log_user_id_foreign');
  323. });
  324. Schema::table('user_credit_log', function (Blueprint $table) {
  325. $table->dropForeign(['user_id']);
  326. $table->dropForeign(['order_id']);
  327. $table->dropIndex('user_credit_log_user_id_foreign');
  328. $table->dropIndex('user_credit_log_order_id_foreign');
  329. });
  330. Schema::table('user_credit_log', function (Blueprint $table) {
  331. $table->unsignedInteger('order_id')->default(0)->nullable(false)->change();
  332. });
  333. Schema::table('user_daily_data_flow', function (Blueprint $table) {
  334. $table->dropForeign(['user_id']);
  335. $table->dropForeign(['node_id']);
  336. $table->dropIndex('user_daily_data_flow_node_id_foreign');
  337. $table->unsignedInteger('node_id')->default(0)->nullable(false)->change();
  338. });
  339. Schema::table('user_data_modify_log', function (Blueprint $table) {
  340. $table->dropForeign(['user_id']);
  341. $table->dropForeign(['order_id']);
  342. $table->dropIndex('user_data_modify_log_user_id_foreign');
  343. $table->dropIndex('user_data_modify_log_order_id_foreign');
  344. });
  345. Schema::table('user_data_modify_log', function (Blueprint $table) {
  346. $table->unsignedInteger('order_id')->default(0)->nullable(false)->change();
  347. });
  348. Schema::table('user_hourly_data_flow', function (Blueprint $table) {
  349. $table->dropForeign(['user_id']);
  350. $table->dropForeign(['node_id']);
  351. $table->dropIndex('user_hourly_data_flow_node_id_foreign');
  352. $table->unsignedInteger('node_id')->default(0)->nullable(false)->change();
  353. });
  354. Schema::table('user_login_log', function (Blueprint $table) {
  355. $table->dropForeign(['user_id']);
  356. $table->dropIndex('user_login_log_user_id_foreign');
  357. });
  358. Schema::table('user_subscribe', function (Blueprint $table) {
  359. $table->dropUnique(['code']);
  360. $table->dropForeign(['user_id']);
  361. });
  362. Schema::table('user_subscribe_log', function (Blueprint $table) {
  363. $table->dropForeign(['user_subscribe_id']);
  364. });
  365. Schema::table('user_traffic_log', function (Blueprint $table) {
  366. $table->dropForeign(['user_id']);
  367. $table->dropForeign(['node_id']);
  368. $table->dropIndex('user_traffic_log_node_id_foreign');
  369. });
  370. Schema::table('verify', function (Blueprint $table) {
  371. $table->dropForeign(['user_id']);
  372. $table->dropIndex('verify_user_id_foreign');
  373. });
  374. }
  375. }