ceph/src/rgw/driver/dbstore/config/sqlite_schema.h

   1 // -*- mode:C++; tab-width:8; c-basic-offset:2; indent-tabs-mode:t -*-
   2 // vim: ts=8 sw=2 smarttab ft=cpp
   3
   4 /*
   5  * Ceph - scalable distributed file system
   6  *
   7  * Copyright (C) 2022 Red Hat, Inc.
   8  *
   9  * This is free software; you can redistribute it and/or
  10  * modify it under the terms of the GNU Lesser General Public
  11  * License version 2.1, as published by the Free Software
  12  * Foundation. See file COPYING.
  13  *
  14  */
  15
  16 #pragma once
  17
  18 #include <initializer_list>
  19
  20 namespace rgw::dbstore::config::schema {
  21
  22 struct Migration {
  23   // human-readable description to help with debugging migration errors
  24   const char* description = nullptr;
  25   // series of sql statements to apply the schema migration
  26   const char* up = nullptr;
  27   // series of sql statements to undo the schema migration
  28   const char* down = nullptr;
  29 };
  30
  31 static constexpr std::initializer_list<Migration> migrations {{
  32     .description = "create the initial ConfigStore tables",
  33     .up = R"(
  34 CREATE TABLE IF NOT EXISTS Realms (
  35   ID TEXT PRIMARY KEY NOT NULL,
  36   Name TEXT UNIQUE NOT NULL,
  37   CurrentPeriod TEXT,
  38   Epoch INTEGER DEFAULT 0,
  39   VersionNumber INTEGER,
  40   VersionTag TEXT
  41 );
  42 CREATE TABLE IF NOT EXISTS Periods (
  43   ID TEXT NOT NULL,
  44   Epoch INTEGER DEFAULT 0,
  45   RealmID TEXT NOT NULL REFERENCES Realms (ID),
  46   Data TEXT NOT NULL,
  47   PRIMARY KEY (ID, Epoch)
  48 );
  49 CREATE TABLE IF NOT EXISTS PeriodConfigs (
  50   RealmID TEXT PRIMARY KEY NOT NULL REFERENCES Realms (ID),
  51   Data TEXT NOT NULL
  52 );
  53 CREATE TABLE IF NOT EXISTS ZoneGroups (
  54   ID TEXT PRIMARY KEY NOT NULL,
  55   Name TEXT UNIQUE NOT NULL,
  56   RealmID TEXT NOT NULL REFERENCES Realms (ID),
  57   Data TEXT NOT NULL,
  58   VersionNumber INTEGER,
  59   VersionTag TEXT
  60 );
  61 CREATE TABLE IF NOT EXISTS Zones (
  62   ID TEXT PRIMARY KEY NOT NULL,
  63   Name TEXT UNIQUE NOT NULL,
  64   RealmID TEXT NOT NULL REFERENCES Realms (ID),
  65   Data TEXT NOT NULL,
  66   VersionNumber INTEGER,
  67   VersionTag TEXT
  68 );
  69 CREATE TABLE IF NOT EXISTS DefaultRealms (
  70   ID TEXT,
  71   Empty TEXT PRIMARY KEY
  72 );
  73 CREATE TABLE IF NOT EXISTS DefaultZoneGroups (
  74   ID TEXT,
  75   RealmID TEXT PRIMARY KEY REFERENCES Realms (ID)
  76 );
  77 CREATE TABLE IF NOT EXISTS DefaultZones (
  78   ID TEXT,
  79   RealmID TEXT PRIMARY KEY REFERENCES Realms (ID)
  80 );
  81 )",
  82     .down = R"(
  83 DROP TABLE IF EXISTS Realms;
  84 DROP TABLE IF EXISTS Periods;
  85 DROP TABLE IF EXISTS PeriodConfigs;
  86 DROP TABLE IF EXISTS ZoneGroups;
  87 DROP TABLE IF EXISTS Zones;
  88 DROP TABLE IF EXISTS DefaultRealms;
  89 DROP TABLE IF EXISTS DefaultZoneGroups;
  90 DROP TABLE IF EXISTS DefaultZones;
  91 )"
  92   }
  93 };
  94
  95
  96 // DefaultRealms
  97
  98 static constexpr const char* default_realm_insert1 =
  99 "INSERT INTO DefaultRealms (ID, Empty) VALUES ({}, '')";
 100
 101 static constexpr const char* default_realm_upsert1 =
 102 R"(INSERT INTO DefaultRealms (ID, Empty) VALUES ({0}, '')
 103 ON CONFLICT(Empty) DO UPDATE SET ID = {0})";
 104
 105 static constexpr const char* default_realm_select0 =
 106 "SELECT ID FROM DefaultRealms LIMIT 1";
 107
 108 static constexpr const char* default_realm_delete0 =
 109 "DELETE FROM DefaultRealms";
 110
 111
 112 // Realms
 113
 114 static constexpr const char* realm_update5 =
 115 "UPDATE Realms SET CurrentPeriod = {1}, Epoch = {2}, VersionNumber = {3} + 1 \
 116 WHERE ID = {0} AND VersionNumber = {3} AND VersionTag = {4}";
 117
 118 static constexpr const char* realm_rename4 =
 119 "UPDATE Realms SET Name = {1}, VersionNumber = {2} + 1 \
 120 WHERE ID = {0} AND VersionNumber = {2} AND VersionTag = {3}";
 121
 122 static constexpr const char* realm_delete3 =
 123 "DELETE FROM Realms WHERE ID = {} AND VersionNumber = {} AND VersionTag = {}";
 124
 125 static constexpr const char* realm_insert4 =
 126 "INSERT INTO Realms (ID, Name, VersionNumber, VersionTag) \
 127 VALUES ({}, {}, {}, {})";
 128
 129 static constexpr const char* realm_upsert4 =
 130 "INSERT INTO Realms (ID, Name, VersionNumber, VersionTag) \
 131 VALUES ({0}, {1}, {2}, {3}) \
 132 ON CONFLICT(ID) DO UPDATE SET Name = {1}, \
 133 VersionNumber = {2}, VersionTag = {3}";
 134
 135 static constexpr const char* realm_select_id1 =
 136 "SELECT * FROM Realms WHERE ID = {} LIMIT 1";
 137
 138 static constexpr const char* realm_select_name1 =
 139 "SELECT * FROM Realms WHERE Name = {} LIMIT 1";
 140
 141 static constexpr const char* realm_select_default0 =
 142 "SELECT r.* FROM Realms r \
 143 INNER JOIN DefaultRealms d \
 144 ON d.ID = r.ID LIMIT 1";
 145
 146 static constexpr const char* realm_select_names2 =
 147 "SELECT Name FROM Realms WHERE Name > {} \
 148 ORDER BY Name ASC LIMIT {}";
 149
 150
 151 // Periods
 152
 153 static constexpr const char* period_insert4 =
 154 "INSERT INTO Periods (ID, Epoch, RealmID, Data) \
 155 VALUES ({}, {}, {}, {})";
 156
 157 static constexpr const char* period_upsert4 =
 158 "INSERT INTO Periods (ID, Epoch, RealmID, Data) \
 159 VALUES ({0}, {1}, {2}, {3}) \
 160 ON CONFLICT DO UPDATE SET RealmID = {2}, Data = {3}";
 161
 162 static constexpr const char* period_select_epoch2 =
 163 "SELECT * FROM Periods WHERE ID = {} AND Epoch = {} LIMIT 1";
 164
 165 static constexpr const char* period_select_latest1 =
 166 "SELECT * FROM Periods WHERE ID = {} ORDER BY Epoch DESC LIMIT 1";
 167
 168 static constexpr const char* period_delete1 =
 169 "DELETE FROM Periods WHERE ID = {}";
 170
 171 static constexpr const char* period_select_ids2 =
 172 "SELECT ID FROM Periods WHERE ID > {} ORDER BY ID ASC LIMIT {}";
 173
 174
 175 // DefaultZoneGroups
 176
 177 static constexpr const char* default_zonegroup_insert2 =
 178 "INSERT INTO DefaultZoneGroups (RealmID, ID) VALUES ({}, {})";
 179
 180 static constexpr const char* default_zonegroup_upsert2 =
 181 "INSERT INTO DefaultZoneGroups (RealmID, ID) \
 182 VALUES ({0}, {1}) \
 183 ON CONFLICT(RealmID) DO UPDATE SET ID = {1}";
 184
 185 static constexpr const char* default_zonegroup_select1 =
 186 "SELECT ID FROM DefaultZoneGroups WHERE RealmID = {}";
 187
 188 static constexpr const char* default_zonegroup_delete1 =
 189 "DELETE FROM DefaultZoneGroups WHERE RealmID = {}";
 190
 191
 192 // ZoneGroups
 193
 194 static constexpr const char* zonegroup_update5 =
 195 "UPDATE ZoneGroups SET RealmID = {1}, Data = {2}, VersionNumber = {3} + 1 \
 196 WHERE ID = {0} AND VersionNumber = {3} AND VersionTag = {4}";
 197
 198 static constexpr const char* zonegroup_rename4 =
 199 "UPDATE ZoneGroups SET Name = {1}, VersionNumber = {2} + 1 \
 200 WHERE ID = {0} AND VersionNumber = {2} AND VersionTag = {3}";
 201
 202 static constexpr const char* zonegroup_delete3 =
 203 "DELETE FROM ZoneGroups WHERE ID = {} \
 204 AND VersionNumber = {} AND VersionTag = {}";
 205
 206 static constexpr const char* zonegroup_insert6 =
 207 "INSERT INTO ZoneGroups (ID, Name, RealmID, Data, VersionNumber, VersionTag) \
 208 VALUES ({}, {}, {}, {}, {}, {})";
 209
 210 static constexpr const char* zonegroup_upsert6 =
 211 "INSERT INTO ZoneGroups (ID, Name, RealmID, Data, VersionNumber, VersionTag) \
 212 VALUES ({0}, {1}, {2}, {3}, {4}, {5}) \
 213 ON CONFLICT (ID) DO UPDATE SET Name = {1}, RealmID = {2}, \
 214 Data = {3}, VersionNumber = {4}, VersionTag = {5}";
 215
 216 static constexpr const char* zonegroup_select_id1 =
 217 "SELECT * FROM ZoneGroups WHERE ID = {} LIMIT 1";
 218
 219 static constexpr const char* zonegroup_select_name1 =
 220 "SELECT * FROM ZoneGroups WHERE Name = {} LIMIT 1";
 221
 222 static constexpr const char* zonegroup_select_default0 =
 223 "SELECT z.* FROM ZoneGroups z \
 224 INNER JOIN DefaultZoneGroups d \
 225 ON d.ID = z.ID LIMIT 1";
 226
 227 static constexpr const char* zonegroup_select_names2 =
 228 "SELECT Name FROM ZoneGroups WHERE Name > {} \
 229 ORDER BY Name ASC LIMIT {}";
 230
 231
 232 // DefaultZones
 233
 234 static constexpr const char* default_zone_insert2 =
 235 "INSERT INTO DefaultZones (RealmID, ID) VALUES ({}, {})";
 236
 237 static constexpr const char* default_zone_upsert2 =
 238 "INSERT INTO DefaultZones (RealmID, ID) VALUES ({0}, {1}) \
 239 ON CONFLICT(RealmID) DO UPDATE SET ID = {1}";
 240
 241 static constexpr const char* default_zone_select1 =
 242 "SELECT ID FROM DefaultZones WHERE RealmID = {}";
 243
 244 static constexpr const char* default_zone_delete1 =
 245 "DELETE FROM DefaultZones WHERE RealmID = {}";
 246
 247
 248 // Zones
 249
 250 static constexpr const char* zone_update5 =
 251 "UPDATE Zones SET RealmID = {1}, Data = {2}, VersionNumber = {3} + 1 \
 252 WHERE ID = {0} AND VersionNumber = {3} AND VersionTag = {4}";
 253
 254 static constexpr const char* zone_rename4 =
 255 "UPDATE Zones SET Name = {1}, VersionNumber = {2} + 1 \
 256 WHERE ID = {0} AND VersionNumber = {2} AND VersionTag = {3}";
 257
 258 static constexpr const char* zone_delete3 =
 259 "DELETE FROM Zones WHERE ID = {} AND VersionNumber = {} AND VersionTag = {}";
 260
 261 static constexpr const char* zone_insert6 =
 262 "INSERT INTO Zones (ID, Name, RealmID, Data, VersionNumber, VersionTag) \
 263 VALUES ({}, {}, {}, {}, {}, {})";
 264
 265 static constexpr const char* zone_upsert6 =
 266 "INSERT INTO Zones (ID, Name, RealmID, Data, VersionNumber, VersionTag) \
 267 VALUES ({0}, {1}, {2}, {3}, {4}, {5}) \
 268 ON CONFLICT (ID) DO UPDATE SET Name = {1}, RealmID = {2}, \
 269 Data = {3}, VersionNumber = {4}, VersionTag = {5}";
 270
 271 static constexpr const char* zone_select_id1 =
 272 "SELECT * FROM Zones WHERE ID = {} LIMIT 1";
 273
 274 static constexpr const char* zone_select_name1 =
 275 "SELECT * FROM Zones WHERE Name = {} LIMIT 1";
 276
 277 static constexpr const char* zone_select_default0 =
 278 "SELECT z.* FROM Zones z \
 279 INNER JOIN DefaultZones d \
 280 ON d.ID = z.ID LIMIT 1";
 281
 282 static constexpr const char* zone_select_names2 =
 283 "SELECT Name FROM Zones WHERE Name > {} \
 284 ORDER BY Name ASC LIMIT {}";
 285
 286
 287 // PeriodConfigs
 288
 289 static constexpr const char* period_config_insert2 =
 290 "INSERT INTO PeriodConfigs (RealmID, Data) VALUES ({}, {})";
 291
 292 static constexpr const char* period_config_upsert2 =
 293 "INSERT INTO PeriodConfigs (RealmID, Data) VALUES ({0}, {1}) \
 294 ON CONFLICT (RealmID) DO UPDATE SET Data = {1}";
 295
 296 static constexpr const char* period_config_select1 =
 297 "SELECT Data FROM PeriodConfigs WHERE RealmID = {} LIMIT 1";
 298
 299 } // namespace rgw::dbstore::config::schema