<jupyter_start><jupyter_text>This notebook defines test cases ensuring the DB fits the busyness neeeds<jupyter_code>import pandas as pd 

from pgnotebook import PostgreSQL

pg = PostgreSQL(port=8000)<jupyter_output><empty_output><jupyter_text>Using create script generated from schema (http://dbdesigner.net/designer/schema/93018)<jupyter_code>pg.sql('drop schema if exists rail cascade')
with open('create.sql') as f:
    pg.sql(f.read())
with open('populate.sql') as f:
    pg.sql(f.read())
pg.show_tables(schema='rail')  # 21 tables created, the script also generated all the necessary constraints<jupyter_output><empty_output><jupyter_text>The core functionality to test:1. user must be able to query all routes / specific runs that fit desired (date, origin, destination) values2. user must be able to query all available seats for a specific run (and therefore route)<jupyter_code>pg.sql('select * from rail.route')  # we have 2 routes stored in DB
pg.sql("""
select * 
from rail.route_station 
order by route_id, station_index
""")  # this is how the route map looks like<jupyter_output><empty_output><jupyter_text>Case: Search for all routes that fit desired origin and destination<jupyter_code># assuming we've given origin_id = 1 and destination_id = 4
def get_routes(origin, destination):
    return pg.sql("""
    SELECT o.route_id
    FROM
    (
      SELECT route_id, station_index
      FROM rail.route_station
      WHERE station_id = {}
    ) o
    LEFT JOIN
    (
      SELECT route_id, station_index
      FROM rail.route_station
      WHERE station_id = {}
    ) d
      ON o.route_id = d.route_id
    WHERE
      o.station_index < d.station_index
      AND
      o.route_id = d.route_id
    """.format(origin, destination))
get_routes(1, 3)  # 2 routes available
get_routes(1, 4)  # should be only 1 route available
get_routes(3, 1)  # no routes in opposite direction<jupyter_output><empty_output><jupyter_text>Case: Search for all runs(scheduled voyages) that fit desired origin and destination- Similiar to the previous example, but we are looking for runs with specific date and route<jupyter_code># is also possible to pack this in one sql query
# here we'll reuse previous example for simplicity
def get_runs(origin, destination, date):

    df = get_routes(origin, destination)
    if df.empty:
        return pd.DataFrame()
    else:
        routes = df.route_id.tolist()
    query_routes_arg = ', '.join(map(str, routes))
    return pg.sql("""
    select * from run 
    where route_id in ({}) and date = '{}'
    """.format(query_routes_arg, date))
get_runs(origin=1, destination=3, date='2017-06-01')
get_runs(origin=1, destination=3, date='2017-05-31')
get_runs(origin=1, destination=4, date='2017-06-01')
get_runs(origin=4, destination=1, date='2017-06-01')<jupyter_output><empty_output><jupyter_text>Case check available tickets for a specific run  Query logicIn scope of specific run compare the set of available seats in the train with the set of booked tickets- if seat has no booked tickets attached to it - it's available- else check the difference of two sets: - set(A) contains all station indexes that booked for the seat on the run - set(B) contains all station indexes that desired to be booked The following example demonstrates the algorithm using python types though it probably can be implemented in pure SQL as well<jupyter_code># exclude bording and get-off stations from booked ticket indexes
a = {1, 2}
b = {4, 5, 6, 7}

# this is desired route to book a ticket for
c = {2, 3, 4}
a.remove(max(a)) 
a.remove(min(a))

b.remove(max(b))
b.remove(min(b))

occupied = a.union(b)
occupied  # this station_indexes for the seat is occupied<jupyter_output><empty_output><jupyter_text>If there's no intersection between occupied set and desired set then the seat can be actualy booked<jupyter_code># now to determine if we can book desired ticket
assert c.intersection(occupied) == set()  # no intersection<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09202916771435755, 'fraction_numerical': 0.01835554438018607, 'mean_word_length': 4.5326842837273995, 'pattern_counts': {'":': 0, '<': 30, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '633357', 'license': 'MIT', 'n_tokens_mistral': 1286, 'n_tokens_neox': 1217, 'n_words': 439, 'path': 'use_cases.ipynb', 'repo_name': 'sancau/ivelum_test_task_db', 'size': '3977'}