[psp] foreign keys?

[kokes]

[psp] foreign keys?

[kokes]

Chyba na webu - http://www.psp.cz/sqw/hp.sqw?k=1305 - ta target tabulka se jmenuje osoby, ne osoba.

Identifikátor poslance, viz osoba:id_osoba.
Identifikátor člena vlády, viz osoba:id_osoba.

[kokes]

Typ akce, viz typ_prechodu:id_akce

tahle tabulka chybí

http://www.psp.cz/sqw/hp.sqw?k=1303

[kokes]

Číslo tisku, resp. část reference tisku. Pokud je tisk:id_druh in (41,45,46,47), pak do reference k číslu tisku patří i -E.

tisk: neexistuje

http://www.psp.cz/sqw/hp.sqw?k=1303

[kokes]

Identifikátor orgánu, viz organ:id_organ

organ misto organy
http://www.psp.cz/sqw/hp.sqw?k=1303

[kokes]

Identifikátor schůze, viz i_schuze:id_schuze.

i_schuze misto schuze, taky i_bod_schuze

http://www.psp.cz/sqw/hp.sqw?k=1305

[kokes]

Tohle je minový pole. Nejen že párkrát je v popisku zmíněno víc vazeb (ne nutně víc vazeb, jen se to nějak rozděluje podle nějakýho jinýho sloupce), tak ani u těch, kde je jedna vazba, to neni celý úplně košér. Třeba ta tabulka neexistuje, ta datová integrita nesedí atd. Kód je zhruba takový, prozatim to vzdávám.

import os
import re
import json

cre = re.compile('(\w+):(\w+)')

with open('mapping.json') as f:
    mapping = json.load(f)

fullnames = {mp['tabulka']: f'psp.{mp["tema"]}_{mp["tabulka"]}' for mp in mapping}
assert len(mapping) == len(fullnames)

# kvuli chybam ve schematu
fullnames['osoba'] = fullnames['osoby'] 
fullnames['org'] = fullnames['organy']
fullnames['organ'] = fullnames['organy']
fullnames['i_schuze'] = fullnames['schuze']
fullnames['i_bod_schuze'] = fullnames['bod_schuze']
             
for mp in mapping:
    tbl = f'psp.{mp["tema"]}_{mp["tabulka"]}'
    for sl in mp['sloupce']:
        m = cre.findall(sl['popis'] or '')
        if len(m) > 1:
#             print('-', mp['tema'], mp['tabulka'], sl['sloupec'], 'vicero potencialnich fkeys', sl['popis'])
            continue
        if len(m) == 0:
            continue
        reftable, refcolumn = m[0]
        if reftable in ('typ_prechodu', 'tisk'):
            continue
        fullreftable = fullnames[reftable]
        fkname = 'fk_{}_{}_{}__{}_{}'.format(mp['tema'], mp['tabulka'], sl['sloupec'], reftable, refcolumn)

        fk = f'ALTER TABLE {tbl} ADD CONSTRAINT {fkname} FOREIGN KEY ({sl["sloupec"]}) REFERENCES {fullreftable} ({refcolumn});'
        print(fk)

[kokes]

Malej nástřel je tu 5366cc1

import os
import re
import json
import psycopg2


cre = re.compile('(\w+):(\w+)')

with open('mapping.json') as f:
    mapping = json.load(f)

fullnames = {mp['tabulka']: f'psp.{mp["tema"]}_{mp["tabulka"]}' for mp in mapping}
assert len(mapping) == len(fullnames)

# kvuli chybam ve schematu
fullnames['osoba'] = fullnames['osoby'] 
fullnames['org'] = fullnames['organy']
fullnames['organ'] = fullnames['organy']
fullnames['i_schuze'] = fullnames['schuze']
fullnames['i_bod_schuze'] = fullnames['bod_schuze']

pg = psycopg2.connect(host='localhost')

for mp in mapping:
    tbl = f'psp.{mp["tema"]}_{mp["tabulka"]}'
    for sl in mp['sloupce']:
        m = cre.findall(sl['popis'] or '')
        if len(m) > 1:
#             print('-', mp['tema'], mp['tabulka'], sl['sloupec'], 'vicero potencialnich fkeys', sl['popis'])
            continue
        if len(m) == 0:
            continue
        reftable, refcolumn = m[0]
        if reftable in ('typ_prechodu', 'tisk'):
            continue
        fullreftable = fullnames[reftable]
        fkname = 'fk_{}_{}_{}__{}_{}'.format(mp['tema'], mp['tabulka'], sl['sloupec'], reftable, refcolumn)

        fk = f'ALTER TABLE {tbl} ADD CONSTRAINT {fkname} FOREIGN KEY ({sl["sloupec"]}) REFERENCES {fullreftable} ({refcolumn});'
        with pg, pg.cursor() as cur:
            cur.execute(f'ALTER TABLE {tbl} DROP CONSTRAINT IF EXISTS {fkname}')
        try:
            with pg, pg.cursor() as cur:
                cur.execute(fk)
        except (psycopg2.IntegrityError, psycopg2.ProgrammingError):
            continue
            
        sl['fkey'] = [fullreftable.replace('psp.', ''), refcolumn]
            

with open('mapping.json', 'w') as fw:
    json.dump(mapping, fw, ensure_ascii=False, indent=2)