214 lines
9.3 KiB
SQL
214 lines
9.3 KiB
SQL
-- =============================================================================
|
|
-- پچ اضطراری: wallets/transactions برای DB قدیمی بدون product_id
|
|
-- امن و idempotent — چند بار قابل اجرا؛ داده موجود را تغییر نمیدهد
|
|
--
|
|
-- علت خطای /customer/me:
|
|
-- Unknown column 'product_id' in 'where clause' ... from `wallets`
|
|
--
|
|
-- بعد از اجرا:
|
|
-- php artisan config:clear
|
|
-- (اختیاری) php artisan migrate — مایگریشن همنام را هم ثبت میکند
|
|
-- =============================================================================
|
|
|
|
SET @db = DATABASE();
|
|
|
|
-- wallets.product_id
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'wallets') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'wallets' AND COLUMN_NAME = 'product_id') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'wallets' AND COLUMN_NAME = 'fund_id') > 0,
|
|
'ALTER TABLE `wallets` ADD COLUMN `product_id` INT NULL DEFAULT NULL AFTER `fund_id`',
|
|
'ALTER TABLE `wallets` ADD COLUMN `product_id` INT NULL DEFAULT NULL'
|
|
),
|
|
'SELECT ''skip wallets.product_id'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- wallets.number
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'wallets') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'wallets' AND COLUMN_NAME = 'number') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'wallets' AND COLUMN_NAME = 'product_id') > 0,
|
|
'ALTER TABLE `wallets` ADD COLUMN `number` INT NOT NULL DEFAULT 0 AFTER `product_id`',
|
|
'ALTER TABLE `wallets` ADD COLUMN `number` INT NOT NULL DEFAULT 0'
|
|
),
|
|
'SELECT ''skip wallets.number'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- transactions.product_id
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'product_id') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'type') > 0,
|
|
'ALTER TABLE `transactions` ADD COLUMN `product_id` INT NULL DEFAULT NULL AFTER `type`',
|
|
'ALTER TABLE `transactions` ADD COLUMN `product_id` INT NULL DEFAULT NULL'
|
|
),
|
|
'SELECT ''skip transactions.product_id'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- transactions.order_id
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'order_id') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'customer_id') > 0,
|
|
'ALTER TABLE `transactions` ADD COLUMN `order_id` INT NULL DEFAULT NULL AFTER `customer_id`',
|
|
'ALTER TABLE `transactions` ADD COLUMN `order_id` INT NULL DEFAULT NULL'
|
|
),
|
|
'SELECT ''skip transactions.order_id'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- transactions.title
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'title') = 0,
|
|
'ALTER TABLE `transactions` ADD COLUMN `title` VARCHAR(255) NULL DEFAULT NULL',
|
|
'SELECT ''skip transactions.title'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- transactions.unit
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'unit') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'title') > 0,
|
|
'ALTER TABLE `transactions` ADD COLUMN `unit` VARCHAR(255) NULL DEFAULT NULL AFTER `title`',
|
|
'ALTER TABLE `transactions` ADD COLUMN `unit` VARCHAR(255) NULL DEFAULT NULL'
|
|
),
|
|
'SELECT ''skip transactions.unit'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- transactions.balance_cash
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'balance_cash') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'gold') > 0,
|
|
'ALTER TABLE `transactions` ADD COLUMN `balance_cash` BIGINT NULL DEFAULT NULL AFTER `gold`',
|
|
'ALTER TABLE `transactions` ADD COLUMN `balance_cash` BIGINT NULL DEFAULT NULL'
|
|
),
|
|
'SELECT ''skip transactions.balance_cash'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- transactions.balance_gold
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'balance_gold') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'balance_cash') > 0,
|
|
'ALTER TABLE `transactions` ADD COLUMN `balance_gold` INT NULL DEFAULT NULL AFTER `balance_cash`',
|
|
'ALTER TABLE `transactions` ADD COLUMN `balance_gold` INT NULL DEFAULT NULL'
|
|
),
|
|
'SELECT ''skip transactions.balance_gold'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- transactions.withdraw_id
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'withdraw_id') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'balance_gold') > 0,
|
|
'ALTER TABLE `transactions` ADD COLUMN `withdraw_id` INT UNSIGNED NULL DEFAULT NULL AFTER `balance_gold`',
|
|
'ALTER TABLE `transactions` ADD COLUMN `withdraw_id` INT UNSIGNED NULL DEFAULT NULL'
|
|
),
|
|
'SELECT ''skip transactions.withdraw_id'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- transactions.active
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'active') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'withdraw_id') > 0,
|
|
'ALTER TABLE `transactions` ADD COLUMN `active` TINYINT(1) NULL DEFAULT NULL AFTER `withdraw_id`',
|
|
'ALTER TABLE `transactions` ADD COLUMN `active` TINYINT(1) NULL DEFAULT NULL'
|
|
),
|
|
'SELECT ''skip transactions.active'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- transactions.number
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions') > 0
|
|
AND (SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'number') = 0,
|
|
IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'active') > 0,
|
|
'ALTER TABLE `transactions` ADD COLUMN `number` INT NULL DEFAULT NULL AFTER `active`',
|
|
'ALTER TABLE `transactions` ADD COLUMN `number` INT NULL DEFAULT NULL'
|
|
),
|
|
'SELECT ''skip transactions.number'' AS info'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SELECT
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'wallets' AND COLUMN_NAME = 'product_id') AS wallets_product_id,
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'transactions' AND COLUMN_NAME = 'product_id') AS transactions_product_id,
|
|
'ensure_wallet_module_columns.sql finished' AS status;
|