You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
146 lines
4.1 KiB
146 lines
4.1 KiB
USE lifanghe_education;
|
|
|
|
ALTER TABLE users MODIFY password_hash CHAR(64) NULL;
|
|
ALTER TABLE users MODIFY phone VARCHAR(30) NULL;
|
|
|
|
SET @schema_name = DATABASE();
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD COLUMN wechat_openid VARCHAR(80) NULL AFTER phone',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND COLUMN_NAME = 'wechat_openid'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD COLUMN wechat_unionid VARCHAR(80) NULL AFTER wechat_openid',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND COLUMN_NAME = 'wechat_unionid'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD COLUMN wechat_nickname VARCHAR(80) NULL AFTER wechat_unionid',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND COLUMN_NAME = 'wechat_nickname'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD COLUMN wechat_avatar VARCHAR(500) NULL AFTER wechat_nickname',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND COLUMN_NAME = 'wechat_avatar'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD COLUMN login_provider VARCHAR(30) NOT NULL DEFAULT ''password'' AFTER wechat_avatar',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND COLUMN_NAME = 'login_provider'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD COLUMN phone_bound TINYINT(1) NOT NULL DEFAULT 1 AFTER login_provider',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND COLUMN_NAME = 'phone_bound'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD COLUMN phone_bound_at VARCHAR(32) NULL AFTER phone_bound',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND COLUMN_NAME = 'phone_bound_at'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD COLUMN wechat_bound_at VARCHAR(32) NULL AFTER phone_bound_at',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND COLUMN_NAME = 'wechat_bound_at'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD UNIQUE KEY uk_users_wechat_openid (wechat_openid)',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND COLUMN_NAME = 'wechat_openid' AND NON_UNIQUE = 0
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD UNIQUE KEY uk_users_wechat_unionid (wechat_unionid)',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND COLUMN_NAME = 'wechat_unionid' AND NON_UNIQUE = 0
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(COUNT(*) = 0,
|
|
'ALTER TABLE users ADD INDEX idx_users_login_provider (login_provider)',
|
|
'SELECT 1'
|
|
)
|
|
FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_users_login_provider'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
UPDATE users
|
|
SET
|
|
login_provider = COALESCE(login_provider, 'password'),
|
|
phone_bound = IF(phone IS NULL OR phone = '', 0, 1),
|
|
phone_bound_at = IF((phone IS NULL OR phone = ''), NULL, COALESCE(phone_bound_at, last_login_at))
|
|
WHERE id IS NOT NULL;
|