administrator
90 lines
2.0 KiB
Python
90 lines
2.0 KiB
Python
import psycopg2
|
|
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
|
|
|
|
# Pripojeni k postgres databazi
|
|
conn = psycopg2.connect(
|
|
host="192.168.1.76",
|
|
port=5432,
|
|
user="vladimir.buzalka",
|
|
password="Vlado7309208104++",
|
|
database="postgres"
|
|
)
|
|
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
|
|
cursor = conn.cursor()
|
|
|
|
# Vytvoreni databaze
|
|
try:
|
|
cursor.execute("DROP DATABASE IF EXISTS fotky_buzalkovi;")
|
|
print("[OK] Stara databaze smazana")
|
|
except:
|
|
pass
|
|
|
|
cursor.execute("CREATE DATABASE fotky_buzalkovi;")
|
|
print("[OK] Databaze fotky_buzalkovi vytvorena")
|
|
conn.close()
|
|
|
|
# Pripojeni k nove databazi
|
|
conn = psycopg2.connect(
|
|
host="192.168.1.76",
|
|
port=5432,
|
|
user="vladimir.buzalka",
|
|
password="Vlado7309208104++",
|
|
database="fotky_buzalkovi"
|
|
)
|
|
cursor = conn.cursor()
|
|
|
|
# Vytvoreni tabulek
|
|
cursor.execute("""
|
|
CREATE TABLE cameras (
|
|
id SERIAL PRIMARY KEY,
|
|
model VARCHAR(255) UNIQUE,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
""")
|
|
|
|
cursor.execute("""
|
|
CREATE TABLE photos (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
file_name VARCHAR(255) NOT NULL,
|
|
file_path VARCHAR(1000) NOT NULL,
|
|
file_hash VARCHAR(64) UNIQUE,
|
|
|
|
camera_id INT,
|
|
taken_at TIMESTAMP,
|
|
width INT,
|
|
height INT,
|
|
file_size BIGINT,
|
|
|
|
exif_data JSONB,
|
|
processing_status VARCHAR(50) DEFAULT 'pending',
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
|
|
FOREIGN KEY (camera_id) REFERENCES cameras(id),
|
|
INDEX idx_taken_at (taken_at),
|
|
INDEX idx_camera (camera_id),
|
|
INDEX idx_file_hash (file_hash)
|
|
);
|
|
""")
|
|
|
|
cursor.execute("""
|
|
CREATE TABLE photo_tags (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
photo_id BIGINT NOT NULL,
|
|
tag VARCHAR(100),
|
|
FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
|
|
INDEX idx_tag (tag)
|
|
);
|
|
""")
|
|
|
|
# Vytvoreni indexu pro EXIF data
|
|
cursor.execute("CREATE INDEX idx_exif_camera ON photos USING GIN (exif_data);")
|
|
|
|
conn.commit()
|
|
print("[OK] Schéma vytvoreno:")
|
|
print(" - cameras")
|
|
print(" - photos")
|
|
print(" - photo_tags")
|
|
print(" - indexy pro EXIF a vyhledavani")
|
|
|
|
conn.close()
|