Overview
Comment: | Fix missing commit Sync file with schema.sql |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | emails |
Files: | files | file ages | folders |
SHA3-256: |
17e017e227f56854d9da654bf9fe3f61 |
User & Date: | bohwaz on 2022-05-30 14:12:03 |
Other Links: | branch diff | manifest | tags |
Context
2022-05-30
| ||
16:18 | Add ability to re-subscribe an unsubscribed address check-in: f81589e26c user: bohwaz tags: emails | |
14:12 | Fix missing commit Sync file with schema.sql check-in: 17e017e227 user: bohwaz tags: emails | |
14:10 | Merge trunk changes to emails check-in: fb5ee0b93f user: bohwaz tags: emails | |
2022-03-07
| ||
04:07 | Sync file with schema.sql check-in: ef2546d83f user: bohwaz tags: trunk | |
Changes
Modified src/include/data/1.1.0_schema.sql from [f967150e02] to [9f8fb4da93].
︙ | ︙ | |||
60 61 62 63 64 65 66 | description TEXT NULL, amount INTEGER NULL, formula TEXT NULL, -- Formule de calcul du montant de la cotisation, si cotisation dynamique (exemple : membres.revenu_imposable * 0.01) id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE, id_account INTEGER NULL REFERENCES acc_accounts (id) ON DELETE SET NULL CHECK (id_account IS NULL OR id_year IS NOT NULL), -- NULL if fee is not linked to accounting, this is reset using a trigger if the year is deleted | | > | 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | description TEXT NULL, amount INTEGER NULL, formula TEXT NULL, -- Formule de calcul du montant de la cotisation, si cotisation dynamique (exemple : membres.revenu_imposable * 0.01) id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE, id_account INTEGER NULL REFERENCES acc_accounts (id) ON DELETE SET NULL CHECK (id_account IS NULL OR id_year IS NOT NULL), -- NULL if fee is not linked to accounting, this is reset using a trigger if the year is deleted id_year INTEGER NULL REFERENCES acc_years (id) ON DELETE SET NULL, -- NULL if fee is not linked to accounting id_analytical INTEGER NULL REFERENCES acc_accounts (id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS services_users -- Enregistrement des cotisations et activités ( id INTEGER NOT NULL PRIMARY KEY, id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE, |
︙ | ︙ | |||
148 149 150 151 152 153 154 155 156 157 158 159 160 161 | type INTEGER NOT NULL DEFAULT 0, -- Type de compte spécial : banque, caisse, en attente d'encaissement, etc. user INTEGER NOT NULL DEFAULT 1 -- 0 = fait partie du plan comptable original, 1 = 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, | > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 | type INTEGER NOT NULL DEFAULT 0, -- Type de compte spécial : banque, caisse, en attente d'encaissement, etc. user INTEGER NOT NULL DEFAULT 1 -- 0 = fait partie du plan comptable original, 1 = 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); -- Balance des comptes par exercice CREATE VIEW IF NOT EXISTS acc_accounts_balances AS SELECT id_year, id, label, code, type, debit, credit, CASE -- 3 = dynamic asset or liability depending on balance WHEN position = 3 AND (debit - credit) > 0 THEN 1 -- 1 = Asset (actif) comptes fournisseurs, tiers créditeurs WHEN position = 3 THEN 2 -- 2 = Liability (passif), comptes clients, tiers débiteurs ELSE position END AS position, CASE WHEN position IN (1, 4) -- 1 = asset, 4 = expense OR (position = 3 AND (debit - credit) > 0) THEN debit - credit ELSE credit - debit END AS balance, CASE WHEN debit - credit > 0 THEN 1 ELSE 0 END AS is_debt FROM ( SELECT t.id_year, a.id, a.label, a.code, a.position, a.type, SUM(l.credit) AS credit, SUM(l.debit) AS debit FROM acc_accounts a INNER JOIN acc_transactions_lines l ON l.id_account = a.id INNER JOIN acc_transactions t ON t.id = l.id_transaction GROUP BY t.id_year, a.id ); CREATE TABLE IF NOT EXISTS acc_years -- Exercices ( id INTEGER NOT NULL PRIMARY KEY, label TEXT NOT NULL, |
︙ | ︙ | |||
354 355 356 357 358 359 360 | CREATE TABLE IF NOT EXISTS compromised_passwords_cache_ranges -- Cache des préfixes de mots de passe compromis ( prefix TEXT NOT NULL PRIMARY KEY, date INTEGER NOT NULL ); | < | 383 384 385 386 387 388 389 390 391 392 393 394 395 396 | CREATE TABLE IF NOT EXISTS compromised_passwords_cache_ranges -- Cache des préfixes de mots de passe compromis ( prefix TEXT NOT NULL PRIMARY KEY, date INTEGER NOT NULL ); CREATE TABLE emails ( -- List of emails addresses -- We are not storing actual email addresses here for privacy reasons -- So that we can keep the record (for opt-out reasons) even when the -- email address has been removed from the users table id INTEGER NOT NULL PRIMARY KEY, hash TEXT NOT NULL, |
︙ | ︙ |
Modified src/include/data/schema.sql from [f967150e02] to [9f8fb4da93].
︙ | ︙ | |||
60 61 62 63 64 65 66 | description TEXT NULL, amount INTEGER NULL, formula TEXT NULL, -- Formule de calcul du montant de la cotisation, si cotisation dynamique (exemple : membres.revenu_imposable * 0.01) id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE, id_account INTEGER NULL REFERENCES acc_accounts (id) ON DELETE SET NULL CHECK (id_account IS NULL OR id_year IS NOT NULL), -- NULL if fee is not linked to accounting, this is reset using a trigger if the year is deleted | | > | 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | description TEXT NULL, amount INTEGER NULL, formula TEXT NULL, -- Formule de calcul du montant de la cotisation, si cotisation dynamique (exemple : membres.revenu_imposable * 0.01) id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE, id_account INTEGER NULL REFERENCES acc_accounts (id) ON DELETE SET NULL CHECK (id_account IS NULL OR id_year IS NOT NULL), -- NULL if fee is not linked to accounting, this is reset using a trigger if the year is deleted id_year INTEGER NULL REFERENCES acc_years (id) ON DELETE SET NULL, -- NULL if fee is not linked to accounting id_analytical INTEGER NULL REFERENCES acc_accounts (id) ON DELETE SET NULL ); CREATE TABLE IF NOT EXISTS services_users -- Enregistrement des cotisations et activités ( id INTEGER NOT NULL PRIMARY KEY, id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE, |
︙ | ︙ | |||
148 149 150 151 152 153 154 155 156 157 158 159 160 161 | type INTEGER NOT NULL DEFAULT 0, -- Type de compte spécial : banque, caisse, en attente d'encaissement, etc. user INTEGER NOT NULL DEFAULT 1 -- 0 = fait partie du plan comptable original, 1 = 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, | > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 | type INTEGER NOT NULL DEFAULT 0, -- Type de compte spécial : banque, caisse, en attente d'encaissement, etc. user INTEGER NOT NULL DEFAULT 1 -- 0 = fait partie du plan comptable original, 1 = 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); -- Balance des comptes par exercice CREATE VIEW IF NOT EXISTS acc_accounts_balances AS SELECT id_year, id, label, code, type, debit, credit, CASE -- 3 = dynamic asset or liability depending on balance WHEN position = 3 AND (debit - credit) > 0 THEN 1 -- 1 = Asset (actif) comptes fournisseurs, tiers créditeurs WHEN position = 3 THEN 2 -- 2 = Liability (passif), comptes clients, tiers débiteurs ELSE position END AS position, CASE WHEN position IN (1, 4) -- 1 = asset, 4 = expense OR (position = 3 AND (debit - credit) > 0) THEN debit - credit ELSE credit - debit END AS balance, CASE WHEN debit - credit > 0 THEN 1 ELSE 0 END AS is_debt FROM ( SELECT t.id_year, a.id, a.label, a.code, a.position, a.type, SUM(l.credit) AS credit, SUM(l.debit) AS debit FROM acc_accounts a INNER JOIN acc_transactions_lines l ON l.id_account = a.id INNER JOIN acc_transactions t ON t.id = l.id_transaction GROUP BY t.id_year, a.id ); CREATE TABLE IF NOT EXISTS acc_years -- Exercices ( id INTEGER NOT NULL PRIMARY KEY, label TEXT NOT NULL, |
︙ | ︙ | |||
354 355 356 357 358 359 360 | CREATE TABLE IF NOT EXISTS compromised_passwords_cache_ranges -- Cache des préfixes de mots de passe compromis ( prefix TEXT NOT NULL PRIMARY KEY, date INTEGER NOT NULL ); | < | 383 384 385 386 387 388 389 390 391 392 393 394 395 396 | CREATE TABLE IF NOT EXISTS compromised_passwords_cache_ranges -- Cache des préfixes de mots de passe compromis ( prefix TEXT NOT NULL PRIMARY KEY, date INTEGER NOT NULL ); CREATE TABLE emails ( -- List of emails addresses -- We are not storing actual email addresses here for privacy reasons -- So that we can keep the record (for opt-out reasons) even when the -- email address has been removed from the users table id INTEGER NOT NULL PRIMARY KEY, hash TEXT NOT NULL, |
︙ | ︙ |