Skip to content

Commit e2deee4

Browse files
folsomketvdijen
folsomke
authored and
tvdijen
committed
Changed the UNIQUE key to a PRIMARY key for the tableVersion and saml_Logo… (#1862)
* Changed the UNIQUE key to a PRIMARY key for the tableVersion and LogoutStore tables for better compatibility with Drupal. * Increased version number of 'saml_LogoutStore' table to 5 and initialized version number of 'tableVersion' table to 1; older versions of these tables are updated with PRIMARY KEY replacing UNIQUE.
1 parent b615bca commit e2deee4

2 files changed

Lines changed: 155 additions & 5 deletions

File tree

‎modules/saml/src/SP/LogoutStore.php‎

Lines changed: 81 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -33,7 +33,85 @@ class LogoutStore
3333
private static function createLogoutTable(SQLStore $store): void
3434
{
3535
$tableVer = $store->getTableVersion('saml_LogoutStore');
36-
if ($tableVer === 4) {
36+
if ($tableVer === 5) {
37+
return;
38+
} elseif ($tableVer === 4) {
39+
// The _authSource index is being changed from UNIQUE to PRIMARY KEY for table version 5.
40+
switch ($store->driver) {
41+
case 'pgsql':
42+
// Drop old index and add primary key
43+
$update = [
44+
'ALTER TABLE ' . $store->prefix . '_saml_LogoutStore DROP CONSTRAINT IF EXISTS ' .
45+
$store->prefix . '_saml_LogoutStore___authSource__nameId__sessionIndex_key',
46+
'ALTER TABLE ' . $store->prefix . '_saml_LogoutStore ADD PRIMARY KEY ' .
47+
'(_authSource, _nameId, _sessionIndex)'
48+
];
49+
break;
50+
case 'sqlsrv':
51+
/**
52+
* Drop old index and add primary key.
53+
* NOTE: We get the name of the index by looking for the only unique index with a default name.
54+
*/
55+
$update = [
56+
'ALTER TABLE ' . $store->prefix . '_saml_LogoutStore DROP INDEX IF EXISTS SELECT CONSTRAINT_NAME ' .
57+
'FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME=' . $store->prefix . '_saml_LogoutStore ' .
58+
'AND CONSTRAINT_NAME LIKE \'UQ__%"\'',
59+
'ALTER TABLE ' . $store->prefix . '_saml_LogoutStore ADD CONSTRAINT _authSource ' .
60+
'PRIMARY KEY CLUSTERED (_authSource, _nameId, _sessionIndex)'
61+
];
62+
break;
63+
case 'sqlite':
64+
/**
65+
* Because SQLite does not support field alterations, the approach is to:
66+
* Create a new table with the primary key
67+
* Copy the current data to the new table
68+
* Drop the old table
69+
* Rename the new table correctly
70+
*/
71+
$update = [
72+
'CREATE TABLE ' . $store->prefix .
73+
'_saml_LogoutStore_new (_authSource VARCHAR(255) NOT NULL,' .
74+
'_nameId VARCHAR(40) NOT NULL, _sessionIndex VARCHAR(50) NOT NULL, ' .
75+
'_expire TIMESTAMP NOT NULL, _sessionId VARCHAR(50) NOT NULL, PRIMARY KEY' .
76+
'(_authSource, _nameID, _sessionIndex))',
77+
'INSERT INTO ' . $store->prefix . '_saml_LogoutStore_new SELECT * FROM ' .
78+
$store->prefix . '_saml_LogoutStore',
79+
'DROP TABLE ' . $store->prefix . '_saml_LogoutStore',
80+
'ALTER TABLE ' . $store->prefix . '_saml_LogoutStore_new RENAME TO ' .
81+
$store->prefix . '_saml_LogoutStore',
82+
'CREATE INDEX ' . $store->prefix . '_saml_LogoutStore_expire ON ' .
83+
$store->prefix . '_saml_LogoutStore (_expire)',
84+
'CREATE INDEX ' . $store->prefix . '_saml_LogoutStore_nameId ON ' .
85+
$store->prefix . '_saml_LogoutStore (_authSource, _nameId)'
86+
];
87+
break;
88+
case 'mysql':
89+
// Drop old index and add primary key
90+
$update = [
91+
'ALTER TABLE ' . $store->prefix . '_saml_LogoutStore DROP INDEX _authSource',
92+
'ALTER TABLE ' . $store->prefix . '_saml_LogoutStore ADD PRIMARY KEY ' .
93+
'(_authSource(191), _nameId, _sessionIndex)'
94+
];
95+
break;
96+
default:
97+
// Drop old index and add primary key
98+
$update = [
99+
'ALTER TABLE ' . $store->prefix . '_saml_LogoutStore DROP INDEX _authSource',
100+
'ALTER TABLE ' . $store->prefix . '_saml_LogoutStore ADD PRIMARY KEY ' .
101+
'(_authSource, _nameId, _sessionIndex)'
102+
];
103+
break;
104+
}
105+
106+
try {
107+
foreach ($update as $query) {
108+
$store->pdo->exec($query);
109+
}
110+
} catch (Exception $e) {
111+
Logger::warning('Database error: ' . var_export($store->pdo->errorInfo(), true));
112+
return;
113+
}
114+
$store->setTableVersion('saml_LogoutStore', 5);
37115
return;
38116
} elseif ($tableVer < 4 && $tableVer > 0) {
39117
throw new Exception(
@@ -48,7 +126,7 @@ private static function createLogoutTable(SQLStore $store): void
48126
_sessionIndex VARCHAR(50) NOT NULL,
49127
_expire ' . ($store->driver === 'pgsql' ? 'TIMESTAMP' : 'DATETIME') . ' NOT NULL,
50128
_sessionId VARCHAR(50) NOT NULL,
51-
UNIQUE (_authSource' . ($store->driver === 'mysql' ? '(191)' : '') . ', _nameID, _sessionIndex)
129+
PRIMARY KEY (_authSource' . ($store->driver === 'mysql' ? '(191)' : '') . ', _nameId, _sessionIndex)
52130
)';
53131
$store->pdo->exec($query);
54132

@@ -61,7 +139,7 @@ private static function createLogoutTable(SQLStore $store): void
61139
', _nameId)';
62140
$store->pdo->exec($query);
63141

64-
$store->setTableVersion('saml_LogoutStore', 4);
142+
$store->setTableVersion('saml_LogoutStore', 5);
65143
}
66144

67145

‎src/SimpleSAML/Store/SQLStore.php‎

Lines changed: 74 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -102,16 +102,88 @@ private function initTableVersionTable(): void
102102
} catch (PDOException $e) {
103103
$this->pdo->exec(
104104
'CREATE TABLE ' . $this->prefix .
105-
'_tableVersion (_name VARCHAR(30) NOT NULL UNIQUE, _version INTEGER NOT NULL)'
105+
'_tableVersion (_name VARCHAR(30) PRIMARY KEY NOT NULL, _version INTEGER NOT NULL)'
106106
);
107+
$this->setTableVersion('tableVersion', 1);
107108
return;
108109
}
109110

110111
while (($row = $fetchTableVersion->fetch(PDO::FETCH_ASSOC)) !== false) {
111112
$this->tableVersions[$row['_name']] = intval($row['_version']);
112113
}
113-
}
114114

115+
$tableVer = $this->getTableVersion('tableVersion');
116+
if ($tableVer === 1) {
117+
return;
118+
} else {
119+
// The _name index is being changed from UNIQUE to PRIMARY KEY for table version 1.
120+
switch ($this->driver) {
121+
case 'pgsql':
122+
// Drop old index and add primary key
123+
$update = [
124+
'ALTER TABLE ' . $this->prefix . '_tableVersion DROP CONSTRAINT IF EXISTS ' .
125+
$this->prefix . '_tableVersion__name_key',
126+
'ALTER TABLE ' . $this->prefix . '_tableVersion ADD PRIMARY KEY (_name)'
127+
];
128+
break;
129+
case 'sqlsrv':
130+
/**
131+
* Drop old index and add primary key.
132+
* NOTE: Because the index has a default name, we need to look it up in the information
133+
* schema.
134+
*/
135+
$update = [
136+
'ALTER TABLE ' . $this->prefix . '_tableVersion DROP INDEX IF EXISTS SELECT CONSTRAINT_NAME ' .
137+
'FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME=' . $this_prefix . '_tableVersion',
138+
'ALTER TABLE ' . $this->prefix . '_tableVersion ADD CONSTRAINT _name PRIMARY KEY CLUSTERED (_name)'
139+
];
140+
break;
141+
case 'sqlite':
142+
/**
143+
* Because SQLite does not support field alterations, the approach is to:
144+
* Create a new table with the primary key
145+
* Copy the current data to the new table
146+
* Drop the old table
147+
* Rename the new table correctly
148+
*/
149+
$update = [
150+
'CREATE TABLE ' . $this->prefix .
151+
'_tableVersion (_name VARCHAR(30) PRIMARY KEY NOT NULL, _version INTEGER NOT NULL)',
152+
'INSERT INTO ' . $this->prefix . '_tableVersion_new SELECT * FROM ' .
153+
$this->prefix . '_tableVersion',
154+
'DROP TABLE ' . $this->prefix . '_tableVersion',
155+
'ALTER TABLE ' . $this->prefix . '_tableVersion_new RENAME TO ' .
156+
$this->prefix . '_tableVersion'
157+
];
158+
break;
159+
case 'mysql':
160+
// Drop old index and add primary key
161+
$update = [
162+
'ALTER TABLE ' . $this->prefix . '_tableVersion DROP INDEX _name',
163+
'ALTER TABLE ' . $this->prefix . '_tableVersion ADD PRIMARY KEY (_name)'
164+
];
165+
break;
166+
default:
167+
// Drop old index and add primary key
168+
$update = [
169+
'ALTER TABLE ' . $this->prefix . '_tableVersion DROP INDEX _name',
170+
'ALTER TABLE ' . $this->prefix . '_tableVersion ADD PRIMARY KEY (_name)'
171+
];
172+
break;
173+
}
174+
175+
try {
176+
foreach ($update as $query) {
177+
$this->pdo->exec($query);
178+
}
179+
} catch (Exception $e) {
180+
Logger::warning('Database error: ' . var_export($this->pdo->errorInfo(), true));
181+
return;
182+
}
183+
$this->setTableVersion('tableVersion', 1);
184+
return;
185+
}
186+
}
115187

116188
/**
117189
* Initialize key-value table.

0 commit comments

Comments
 (0)