Changes between Initial Version and Version 1 of Phase0

Timestamp:

12/01/25 23:40:25 (4 weeks ago)

Author:

226052

Comment:

--

Phase0

@@ +1 @@
+== Креирање на табели
+
+{{{
+
+CREATE TABLE actor (
+    actor_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY ,
+    first_name VARCHAR(45) NOT NULL,
+    last_name VARCHAR(45) NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE category (
+    category_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    name VARCHAR(25) NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE language (
+    language_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    name VARCHAR(20) NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE film (
+    film_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    title VARCHAR(255) NOT NULL,
+    description TEXT,
+    release_year INT,
+    language_id BIGINT NOT NULL,
+    original_language_id BIGINT,
+    rental_duration SMALLINT DEFAULT 3 NOT NULL,
+    rental_rate NUMERIC(4,2) DEFAULT 4.99 NOT NULL,
+    length SMALLINT,
+    replacement_cost NUMERIC(5,2) DEFAULT 19.99 NOT NULL,
+    rating VARCHAR(10) DEFAULT 'G',
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    special_features TEXT[],
+    CONSTRAINT language_fk FOREIGN KEY (language_id) REFERENCES language(language_id) ON UPDATE CASCADE,
+    CONSTRAINT original_language_fk FOREIGN KEY (original_language_id) REFERENCES language(language_id) ON UPDATE CASCADE
+
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE film_category (
+    film_id BIGINT NOT NULL,
+    category_id BIGINT NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    CONSTRAINT film_category_pk PRIMARY KEY (film_id, category_id),
+    CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category(category_id) ON UPDATE CASCADE ON DELETE RESTRICT
+);
+}}}
+
+
+{{{
+
+CREATE TABLE film_actor (
+    actor_id BIGINT NOT NULL,
+    film_id BIGINT NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    CONSTRAINT pk_film_actor PRIMARY KEY (actor_id,film_id),
+    CONSTRAINT fk_actor FOREIGN KEY(actor_id) REFERENCES actor(actor_id) ON DELETE  RESTRICT ON UPDATE CASCADE,
+    CONSTRAINT fk_film FOREIGN KEY (film_id) REFERENCES film(film_id) ON DELETE  RESTRICT ON UPDATE CASCADE
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE country (
+    country_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    country VARCHAR(50) NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL
+);
+}}}
+
+
+{{{
+
+CREATE TABLE city (
+    city_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    city VARCHAR(50) NOT NULL,
+    country_id BIGINT NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    CONSTRAINT country_fk FOREIGN KEY (country_id) REFERENCES country(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE address (
+    address_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    address VARCHAR(50) NOT NULL,
+    address2 VARCHAR(50),
+    district VARCHAR(20) NOT NULL,
+    city_id BIGINT NOT NULL,
+    postal_code VARCHAR(10),
+    phone VARCHAR(20) NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES city(city_id) ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE store (
+    store_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    manager_staff_id BIGINT NOT NULL UNIQUE,
+    address_id BIGINT NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    CONSTRAINT address_fk FOREIGN KEY(address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE customer (
+    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    store_id BIGINT NOT NULL,
+    first_name VARCHAR(45) NOT NULL,
+    last_name VARCHAR(45) NOT NULL,
+    email VARCHAR(50),
+    address_id BIGINT NOT NULL,
+    active BOOLEAN DEFAULT true NOT NULL,
+    create_date DATE DEFAULT CURRENT_DATE NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store(store_id) ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE staff (
+    staff_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    first_name VARCHAR(45) NOT NULL,
+    last_name VARCHAR(45) NOT NULL,
+    address_id BIGINT NOT NULL,
+    email VARCHAR(50),
+    store_id BIGINT NOT NULL,
+    active BOOLEAN DEFAULT true NOT NULL,
+    username VARCHAR(16) NOT NULL,
+    password VARCHAR(40),
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    picture bytea,
+    CONSTRAINT staff_address_fk FOREIGN KEY(address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT staff_store_fk FOREIGN KEY(store_id) REFERENCES store(store_id) ON UPDATE CASCADE ON DELETE RESTRICT
+
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE inventory (
+    inventory_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    film_id BIGINT NOT NULL,
+    store_id BIGINT NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    CONSTRAINT film_fk FOREIGN KEY(film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT store_fk FOREIGN KEY (store_id) REFERENCES store(store_id) ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+}}}
+
+
+{{{
+
+CREATE TABLE rental (
+    rental_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    rental_date TIMESTAMP DEFAULT now() NOT NULL,
+    inventory_id BIGINT NOT NULL,
+    customer_id BIGINT NOT NULL,
+    return_date TIMESTAMP,
+    staff_id BIGINT NOT NULL,
+    last_update TIMESTAMP DEFAULT now() NOT NULL,
+    CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id) ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+}}}
+
+
+
+{{{
+
+CREATE TABLE payment (
+    payment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    customer_id BIGINT NOT NULL,
+    staff_id BIGINT NOT NULL,
+    rental_id BIGINT NOT NULL,
+    amount numeric(5,2) NOT NULL,
+    payment_date TIMESTAMP DEFAULT now() NOT NULL,
+    CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental(rental_id) ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+}}}
+
+
+
+
+== Пример скрипти за креирање на тест податоци
+
+{{{
+INSERT INTO language (name)
+VALUES ('English'), ('Spanish'), ('French'), ('German'), ('Italian');
+}}}
+
+{{{
+INSERT INTO category (name)
+SELECT 'Category ' || i
+FROM generate_series(1, 20) AS i;
+}}}
+
+{{{
+INSERT INTO actor (first_name, last_name)
+SELECT 'Actor' || i, 'Lastname' || i
+FROM generate_series(1, 100) AS i;
+}}}
+
+{{{
+INSERT INTO film
+(title, description, release_year, language_id, original_language_id, rental_duration,
+ rental_rate, length, replacement_cost, rating)
+SELECT
+    'Film #' || i,
+    'Description for film ' || i,
+    1980 + (i % 40),
+    1 + (i % 5),
+    NULL,
+    3 + (i % 5),
+    2.99 + (i % 5),
+    60 + (i % 90),
+    10.00 + (i % 20),
+    CASE WHEN i % 5 = 0 THEN 'PG'
+         WHEN i % 5 = 1 THEN 'G'
+         WHEN i % 5 = 2 THEN 'PG-13'
+         WHEN i % 5 = 3 THEN 'R'
+         ELSE 'NC-17'
+    END
+FROM generate_series(1, 500) AS i;
+}}}
+
+
+{{{
+INSERT INTO film_category (film_id, category_id)
+SELECT f.film_id, ((f.film_id + gs - 1) % 20) + 1
+FROM film f
+CROSS JOIN generate_series(0, 2) AS gs;
+}}}
+
+
+{{{
+INSERT INTO film_actor (actor_id, film_id)
+SELECT ((f.film_id + gs - 1) % 100) + 1, f.film_id
+FROM film f
+CROSS JOIN generate_series(0, 3) AS gs;
+}}}
+
+
+{{{
+INSERT INTO country (country)
+VALUES ('USA'), ('UK'), ('Germany'), ('France'), ('Spain');
+}}}
+
+{{{
+INSERT INTO city (city, country_id)
+SELECT 'City ' || i, (1 + (i % 5))
+FROM generate_series(1, 20) AS i;
+}}}
+
+
+{{{
+INSERT INTO address (address, address2, district, city_id, postal_code, phone)
+SELECT
+    'Address ' || i,
+    NULL,
+    'District ' || (i % 10),
+    1 + (i % 20),
+    '1000' || (i % 10),
+    '555-000-' || i
+FROM generate_series(1, 200) AS i;
+}}}
+
+
+{{{
+INSERT INTO store (manager_staff_id, address_id)
+SELECT i, i
+FROM generate_series(1, 5) AS i;
+}}}
+
+
+{{{
+INSERT INTO staff (first_name, last_name, address_id, email, store_id, active, username, password)
+SELECT
+    'Staff' || i,
+    'Lastname' || i,
+    1 + (i % 50),
+    'staff' || i || '@example.com',
+    1 + (i % 5),
+    TRUE,
+    'staff_user_' || i,
+    'password'
+FROM generate_series(1, 10) AS i;
+}}}
+
+
+{{{
+
+INSERT INTO customer (store_id, first_name, last_name, email, address_id)
+SELECT
+    1 + (i % 5),
+    'Customer' || i,
+    'Lastname' || i,
+    'customer' || i || '@example.com',
+    1 + (i % 200)
+FROM generate_series(1, 500) AS i;
+}}}
+
+
+{{{
+INSERT INTO inventory (film_id, store_id)
+SELECT
+    1 + (i % 500),
+    1 + (i % 5)
+FROM generate_series(1, 5000) AS i;
+}}}
+
+
+{{{
+INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
+SELECT
+    NOW() - (i || ' hours')::interval,
+    1 + (i % 5000),
+    1 + (i % 500),
+    1 + (i % 10)
+FROM generate_series(1, 5000) AS i;
+}}}
+
+
+{{{
+INSERT INTO payment (customer_id, staff_id, rental_id, amount)
+SELECT
+    1 + (i % 500),
+    1 + (i % 10),
+    i,
+    0.99 + (i % 10)
+FROM generate_series(1, 5000) AS i;
+}}}