52 lines
2.5 KiB
PHP
52 lines
2.5 KiB
PHP
<?php
|
|
use Index\Data\IDbConnection;
|
|
use Index\Data\Migration\IDbMigration;
|
|
use Misuzu\ClientInfo;
|
|
|
|
final class UpdateUserAgentStorage_20230721_121854 implements IDbMigration {
|
|
public function migrate(IDbConnection $conn): void {
|
|
// convert user agent fields to BLOB and add field for client info storage
|
|
$conn->execute('
|
|
ALTER TABLE msz_login_attempts
|
|
CHANGE COLUMN attempt_user_agent attempt_user_agent TEXT NOT NULL COLLATE "utf8mb4_bin" AFTER attempt_created,
|
|
ADD COLUMN attempt_client_info TEXT NULL DEFAULT NULL AFTER attempt_user_agent
|
|
');
|
|
$conn->execute('
|
|
ALTER TABLE msz_sessions
|
|
CHANGE column session_user_agent session_user_agent TEXT NOT NULL COLLATE "utf8mb4_bin" AFTER session_ip_last,
|
|
ADD COLUMN session_client_info TEXT NULL DEFAULT NULL AFTER session_user_agent
|
|
');
|
|
|
|
// make sure all existing fields have client info fields filled
|
|
$updateLoginAttempts = $conn->prepare('UPDATE msz_login_attempts SET attempt_client_info = ? WHERE attempt_user_agent = ?');
|
|
$selectLoginAttempts = $conn->query('SELECT DISTINCT attempt_user_agent FROM msz_login_attempts');
|
|
while($selectLoginAttempts->next()) {
|
|
$updateLoginAttempts->reset();
|
|
$userAgent = $selectLoginAttempts->getString(0);
|
|
$updateLoginAttempts->addParameter(1, json_encode(ClientInfo::parse($userAgent)));
|
|
$updateLoginAttempts->addParameter(2, $userAgent);
|
|
$updateLoginAttempts->execute();
|
|
}
|
|
|
|
$updateSessions = $conn->prepare('UPDATE msz_sessions SET session_client_info = ? WHERE session_user_agent = ?');
|
|
$selectSessions = $conn->query('SELECT DISTINCT session_user_agent FROM msz_sessions');
|
|
while($selectSessions->next()) {
|
|
$updateSessions->reset();
|
|
$userAgent = $selectSessions->getString(0);
|
|
$updateSessions->addParameter(1, json_encode(ClientInfo::parse($userAgent)));
|
|
$updateSessions->addParameter(2, $userAgent);
|
|
$updateSessions->execute();
|
|
}
|
|
|
|
// make client info fields NOT NULL
|
|
$conn->execute('
|
|
ALTER TABLE msz_login_attempts
|
|
CHANGE COLUMN attempt_client_info attempt_client_info TEXT NOT NULL COLLATE "utf8mb4_bin" AFTER attempt_user_agent
|
|
');
|
|
$conn->execute('
|
|
ALTER TABLE msz_sessions
|
|
CHANGE COLUMN session_client_info session_client_info TEXT NOT NULL COLLATE "utf8mb4_bin" AFTER session_user_agent
|
|
');
|
|
}
|
|
}
|