CREATE TABLE IF NOT EXISTS sys_keycloak_token_cache (
User varchar(150) NOT NULL,
Package varchar(100) NOT NULL,
AccessToken text NOT NULL,
RefreshToken text NULL,
ExpiresAt datetime NOT NULL,
CachedAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
LastUsed datetime NULL,
UseCount int NOT NULL DEFAULT 0,
PRIMARY KEY (User, Package)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS sys_keycloak_sync_queue (
SyncId char(36) NOT NULL,
User varchar(150) NOT NULL,
Package varchar(100) NOT NULL,
SyncType varchar(50) NOT NULL,
SyncData text NULL,
QueuedAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
ProcessedAt datetime NULL,
Status varchar(20) NOT NULL DEFAULT 'pending',
Attempts int NOT NULL DEFAULT 0,
LastError text NULL,
Priority int NOT NULL DEFAULT 5,
PRIMARY KEY (SyncId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
INSERT INTO sys_keycloak_sync_queue
(SyncId, User, Package, SyncType, SyncData,
QueuedAt, Status, Priority, Attempts)
VALUES (
'{sync_id}',
'{username}',
'{package}',
'{sync_type}',
'{sync_data_json}',
NOW(),
'pending',
{priority},
0
)
INSERT INTO sys_keycloak_token_cache
(User, Package, AccessToken, RefreshToken,
ExpiresAt, CachedAt, UseCount)
VALUES (
'{username}',
'{package}',
'{access_token}',
{refresh_token},
'{expires_at}',
NOW(),
0
)
ON DUPLICATE KEY UPDATE
AccessToken = VALUES(AccessToken),
RefreshToken = VALUES(RefreshToken),
ExpiresAt = VALUES(ExpiresAt),
CachedAt = VALUES(CachedAt),
UseCount = 0
SELECT AccessToken, RefreshToken, ExpiresAt, CachedAt
FROM sys_keycloak_token_cache
WHERE User = '{username}'
AND Package = '{package}'
AND CachedAt > DATE_SUB(
NOW(), INTERVAL {grace_period_hours} HOUR)
UPDATE sys_keycloak_token_cache
SET UseCount = UseCount + 1,
LastUsed = NOW()
WHERE User = '{username}'
AND Package = '{package}'
SELECT AccessToken, RefreshToken, ExpiresAt
FROM sys_keycloak_token_cache
WHERE User = '{username}'
AND Package = '{package}'
AND RefreshToken IS NOT NULL
SELECT User, Package
FROM sys_keycloak_token_cache
WHERE RefreshToken IS NOT NULL
AND ExpiresAt > NOW()
AND ExpiresAt < DATE_ADD(
NOW(), INTERVAL {hours_before_expiry} HOUR)
AND CachedAt > DATE_SUB(
NOW(), INTERVAL {grace_period_hours} HOUR)
SELECT SyncId, User, Package, SyncType,
SyncData, Attempts
FROM sys_keycloak_sync_queue
WHERE Status = 'pending'
AND Attempts < 5
ORDER BY Priority ASC, QueuedAt ASC
LIMIT {batch_size}
UPDATE sys_keycloak_sync_queue
SET Status = '{status}'
{error_clause}
{processed_clause}
{attempts_clause}
WHERE SyncId = '{sync_id}'
SELECT User, Email, Name, Password
FROM sys_user
WHERE Package = '{package}'
AND {where_clause}
SELECT COUNT(*) FROM sys_keycloak_sync_queue
WHERE Status = 'pending'
SELECT COUNT(*) FROM sys_keycloak_token_cache
WHERE CachedAt > DATE_SUB(NOW(), INTERVAL 24 HOUR)
DELETE FROM sys_keycloak_token_cache
WHERE CachedAt < DATE_SUB(
NOW(), INTERVAL {hours} HOUR)
DELETE FROM sys_keycloak_sync_queue
WHERE Status = 'completed'
AND ProcessedAt < DATE_SUB(
NOW(), INTERVAL {days} DAY)