Overview
Comment: | Create more SQL indexes |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | dev |
Files: | files | file ages | folders |
SHA1: |
7e2eed35c036315a241f9ccb898e638d |
User & Date: | bohwaz on 2020-11-18 12:40:37 |
Other Links: | branch diff | manifest | tags |
Context
2020-11-18
| ||
12:41 | Allow to delete a full year including all of its transactions check-in: d0422086f1 user: bohwaz tags: dev | |
12:40 | Create more SQL indexes check-in: 7e2eed35c0 user: bohwaz tags: dev | |
11:56 | Fix liens vers total check-in: 6e86bc54e8 user: bohwaz tags: dev | |
Changes
Modified src/include/data/1.0.0_migration.sql from [c2b1cf78f0] to [36a9974aec].
︙ | ︙ | |||
130 131 132 133 134 135 136 | END FROM cotisations_membres cm INNER JOIN cotisations c ON c.id = cm.id_cotisation; INSERT INTO services_reminders SELECT * FROM rappels; INSERT INTO services_reminders_sent SELECT id, id_membre, id_cotisation, CASE WHEN id_rappel IS NULL THEN (SELECT MAX(id) FROM rappels) ELSE id_rappel END, date | | > | 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 | END FROM cotisations_membres cm INNER JOIN cotisations c ON c.id = cm.id_cotisation; INSERT INTO services_reminders SELECT * FROM rappels; INSERT INTO services_reminders_sent SELECT id, id_membre, id_cotisation, CASE WHEN id_rappel IS NULL THEN (SELECT MAX(id) FROM rappels) ELSE id_rappel END, date FROM rappels_envoyes GROUP BY id_membre, id_cotisation, id_rappel; DROP TABLE cotisations; DROP TABLE cotisations_membres; DROP TABLE rappels; DROP TABLE rappels_envoyes; -- Suppression inutilisées |
︙ | ︙ |
Modified src/include/data/1.0.0_schema.sql from [dec3798374] to [b04b367120].
︙ | ︙ | |||
75 76 77 78 79 80 81 | date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date), expiry_date TEXT NULL CHECK (date(expiry_date) IS NULL OR date(expiry_date) = expiry_date) ); CREATE UNIQUE INDEX IF NOT EXISTS su_unique ON services_users (id_user, id_service, date); | > > > | | 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date), expiry_date TEXT NULL CHECK (date(expiry_date) IS NULL OR date(expiry_date) = expiry_date) ); CREATE UNIQUE INDEX IF NOT EXISTS su_unique ON services_users (id_user, id_service, date); CREATE INDEX IF NOT EXISTS su_service ON services_users (id_service); CREATE INDEX IF NOT EXISTS su_fee ON services_users (id_fee); CREATE INDEX IF NOT EXISTS su_paid ON services_users (paid); CREATE INDEX IF NOT EXISTS su_expiry ON services_users (expiry_date); CREATE TABLE IF NOT EXISTS services_reminders -- Rappels de devoir renouveller une cotisation ( id INTEGER NOT NULL PRIMARY KEY, id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE, |
︙ | ︙ | |||
100 101 102 103 104 105 106 107 108 109 110 111 112 113 | id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE, id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE, id_reminder INTEGER NOT NULL REFERENCES services_reminders (id) ON DELETE CASCADE, date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date) ); -- -- WIKI -- CREATE TABLE IF NOT EXISTS wiki_pages -- Pages du wiki | > > > > > | 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 | id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE, id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE, id_reminder INTEGER NOT NULL REFERENCES services_reminders (id) ON DELETE CASCADE, date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date) ); CREATE UNIQUE INDEX IF NOT EXISTS srs_index ON services_reminders_sent (id_user, id_service, id_reminder, date); CREATE INDEX IF NOT EXISTS srs_reminder ON services_reminders_sent (id_reminder); CREATE INDEX IF NOT EXISTS srs_user ON services_reminders_sent (id_user); -- -- WIKI -- CREATE TABLE IF NOT EXISTS wiki_pages -- Pages du wiki |
︙ | ︙ | |||
203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 | position INTEGER NOT NULL, -- position actif/passif/charge/produit type INTEGER NOT NULL DEFAULT 0, -- Type de compte spécial : banque, caisse, en attente d'encaissement, etc. user INTEGER NOT NULL DEFAULT 1 -- 1 = fait partie du plan comptable original, 0 = a été ajouté par l'utilisateur ); CREATE UNIQUE INDEX IF NOT EXISTS acc_accounts_codes ON acc_accounts (code, id_chart); CREATE TABLE IF NOT EXISTS acc_years -- Exercices ( id INTEGER NOT NULL PRIMARY KEY, label TEXT NOT NULL, start_date TEXT NOT NULL CHECK (date(start_date) IS NOT NULL AND date(start_date) = start_date), end_date TEXT NOT NULL CHECK (date(end_date) IS NOT NULL AND date(end_date) = end_date), closed INTEGER NOT NULL DEFAULT 0, id_chart INTEGER NOT NULL REFERENCES acc_charts (id) ); CREATE TABLE IF NOT EXISTS acc_transactions -- Opérations comptables ( id INTEGER PRIMARY KEY NOT NULL, type INTEGER NOT NULL DEFAULT 0, -- Type d'écriture, 0 = avancée (normale) | > > > > | 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 | position INTEGER NOT NULL, -- position actif/passif/charge/produit type INTEGER NOT NULL DEFAULT 0, -- Type de compte spécial : banque, caisse, en attente d'encaissement, etc. user INTEGER NOT NULL DEFAULT 1 -- 1 = fait partie du plan comptable original, 0 = a été ajouté par l'utilisateur ); CREATE UNIQUE INDEX IF NOT EXISTS acc_accounts_codes ON acc_accounts (code, id_chart); CREATE INDEX IF NOT EXISTS acc_accounts_type ON acc_accounts (type); CREATE INDEX IF NOT EXISTS acc_accounts_position ON acc_accounts (position); CREATE TABLE IF NOT EXISTS acc_years -- Exercices ( id INTEGER NOT NULL PRIMARY KEY, label TEXT NOT NULL, start_date TEXT NOT NULL CHECK (date(start_date) IS NOT NULL AND date(start_date) = start_date), end_date TEXT NOT NULL CHECK (date(end_date) IS NOT NULL AND date(end_date) = end_date), closed INTEGER NOT NULL DEFAULT 0, id_chart INTEGER NOT NULL REFERENCES acc_charts (id) ); CREATE INDEX IF NOT EXISTS acc_years_closed ON acc_years (closed); CREATE TABLE IF NOT EXISTS acc_transactions -- Opérations comptables ( id INTEGER PRIMARY KEY NOT NULL, type INTEGER NOT NULL DEFAULT 0, -- Type d'écriture, 0 = avancée (normale) |
︙ | ︙ | |||
245 246 247 248 249 250 251 252 253 254 255 256 257 258 | id_year INTEGER NOT NULL REFERENCES acc_years(id), id_creator INTEGER NULL REFERENCES membres(id) ON DELETE SET NULL, id_related INTEGER NULL REFERENCES acc_transactions(id) ON DELETE SET NULL -- écriture liée (par ex. remboursement d'une dette) ); CREATE INDEX IF NOT EXISTS acc_transactions_year ON acc_transactions (id_year); CREATE INDEX IF NOT EXISTS acc_transactions_date ON acc_transactions (date); CREATE TABLE IF NOT EXISTS acc_transactions_lines -- Lignes d'écritures d'une opération ( id INTEGER PRIMARY KEY NOT NULL, id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE, | > > > | 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 | id_year INTEGER NOT NULL REFERENCES acc_years(id), id_creator INTEGER NULL REFERENCES membres(id) ON DELETE SET NULL, id_related INTEGER NULL REFERENCES acc_transactions(id) ON DELETE SET NULL -- écriture liée (par ex. remboursement d'une dette) ); CREATE INDEX IF NOT EXISTS acc_transactions_year ON acc_transactions (id_year); CREATE INDEX IF NOT EXISTS acc_transactions_date ON acc_transactions (date); CREATE INDEX IF NOT EXISTS acc_transactions_related ON acc_transactions (id_related); CREATE INDEX IF NOT EXISTS acc_transactions_type ON acc_transactions (type); CREATE INDEX IF NOT EXISTS acc_transactions_status ON acc_transactions (status); CREATE TABLE IF NOT EXISTS acc_transactions_lines -- Lignes d'écritures d'une opération ( id INTEGER PRIMARY KEY NOT NULL, id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE, |
︙ | ︙ | |||
269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 | id_analytical INTEGER NULL REFERENCES acc_accounts(id) ON DELETE SET NULL, CONSTRAINT line_check1 CHECK ((credit * debit) = 0), CONSTRAINT line_check2 CHECK ((credit + debit) > 0) ); CREATE INDEX IF NOT EXISTS acc_transactions_lines_account ON acc_transactions_lines (id_account); CREATE TABLE IF NOT EXISTS acc_transactions_users -- Liaison des écritures et des membres ( id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE, id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE, id_service_user INTEGER NULL REFERENCES services_users (id) ON DELETE SET NULL, PRIMARY KEY (id_user, id_transaction) ); CREATE TABLE IF NOT EXISTS plugins ( id TEXT NOT NULL PRIMARY KEY, officiel INTEGER NOT NULL DEFAULT 0, nom TEXT NOT NULL, description TEXT NULL, | > > > > | 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 | id_analytical INTEGER NULL REFERENCES acc_accounts(id) ON DELETE SET NULL, CONSTRAINT line_check1 CHECK ((credit * debit) = 0), CONSTRAINT line_check2 CHECK ((credit + debit) > 0) ); CREATE INDEX IF NOT EXISTS acc_transactions_lines_account ON acc_transactions_lines (id_account); CREATE INDEX IF NOT EXISTS acc_transactions_lines_analytical ON acc_transactions_lines (id_analytical); CREATE INDEX IF NOT EXISTS acc_transactions_lines_reconciled ON acc_transactions_lines (reconciled); CREATE TABLE IF NOT EXISTS acc_transactions_users -- Liaison des écritures et des membres ( id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE, id_transaction INTEGER NOT NULL REFERENCES acc_transactions (id) ON DELETE CASCADE, id_service_user INTEGER NULL REFERENCES services_users (id) ON DELETE SET NULL, PRIMARY KEY (id_user, id_transaction) ); CREATE INDEX IF NOT EXISTS acc_transactions_users_service ON acc_transactions_users (id_service_user); CREATE TABLE IF NOT EXISTS plugins ( id TEXT NOT NULL PRIMARY KEY, officiel INTEGER NOT NULL DEFAULT 0, nom TEXT NOT NULL, description TEXT NULL, |
︙ | ︙ |