navroute-eddn-storage/doc.txt

what to store?
1. Systems:
    history of system's updates
        softwareName + softwareVersion for every update
    system_name
    star_class
    system_id
    x
    y
    z

2. Routes
    Sequence of system_id s

tables:
    systems
        system_name
        star_class
        system_id
        x
        y
        z
        updated_timestamp (from event, not gateway)
        software_name
        software_version

    systems_history (should be done by triggers on `systems` updates)
        old_system_name
        new_system_name
        old_star_class
        new_star_class
        old_system_id
        new_system_id
        old_x
        new_x
        old_y
        new_y
        old_z
        new_z
        old_updated_timestamp
        new_updated_timestamp
        software_name
        software_version
        timestamp (default)  (timestamp of insertion)


    routes:
        route_id (by the program)
        point_id (from 0 to ...)
        system_id
        timestamp (from event, not gateway)


how to handle:

1. Iterate over the route, save data to `systems`
    1.1 If event's timestamp < updated_timestamp, then do nothing
2. Get last route_id from DB and increment it - it is our new route_id
3. Iterate over the route, save data to `routes`

creating:

create table if not exists systems (system_name text, star_class text, system_id int unique, x float, y float, z float,
updated_timestamp text, software_name text, software_version text);

create table if not exists routes (route_id int, point_id int, system_id int, timestamp str);

create table if not exists systems_history (old_system_name text, new_system_name text, old_star_class text,
new_star_class text, new_system_id int, old_system_id int, old_x text, new_x text, old_y text, new_y text, old_z text,
new_z text, old_updated_timestamp text, new_updated_timestamp text, software_name text, software_version text,
timestamp text default current_timestamp);

create trigger if not exists update_systems_history_insert
before insert on systems
begin
insert into systems_history (new_system_name, new_star_class, new_system_id, new_x, new_y, new_z, new_updated_timestamp,
software_name, software_version)
values (new.system_name, new.star_class, new.system_id, new.x, new.y, new.z, new.updated_timestamp, new.software_name, new.software_version);
end;

create trigger if not exists update_systems_history_update
before update on systems
begin
insert into systems_history (

old_system_name, new_system_name,
old_star_class, new_star_class,
old_system_id, new_system_id,
old_x, new_x,
old_y, new_y,
old_z, new_z,
old_updated_timestamp, new_updated_timestamp,
software_name,
software_version

) values (

old.system_name, new.system_name,
old.star_class, new.star_class,
old.system_id, new.system_id,
old.x, new.x,
old.y, new.y,
old.z, new.z,
old.updated_timestamp, new.updated_timestamp,
new.software_name,
new.software_version

);
end;