Use SQLite to optimise the interlocking table generator

[trinnguyen]

There is another issue even if we could resolve the performance issue #44, it is of the swtbahn-cli. With the size of the interlocking table file of about 14GB, it would be almost impossible to load the whole table into the memory on the server. Instead, the server should parse and load the routes on-demand that might requires a lot of I/O operations and optimisations.

There is another way to store and load the interlocking table, that might also help solving the BahnDSL performance issue, by writing the data into an embedded database such as SQLite. The whole database is just a binary file and support basic features of SQL for manipulating and querying data. It also provides a C library which is transactional, and self-contained. The JVM application can manage the database file via JDBC driver. The whole change could be:

• BahnDSL: New router exporter using JDBC driver to create new database, tables and insert routes and associated entries into the tables. It result a database file locally, for example: interlocking_table.sqlite
• Copy the database file to swtbahn-cli server
• swtbahn-cli uses the provided C library to query subset of routes and their data, with help of SQL.

Below a draft database diagram:

@eyip002 What do you think about implementing this?

[eyip002]

@trinnguyen thanks! We also thought about having a database of routes. As you have mentioned, parsing and loading 14GB of YAML data into memory is impractical. We are also thinking of other ways to reduce the size of the interlocking table:

• Store "non-conflicting" routes instead, because a route typically conflicts with over 90% of all routes (the interlocking table for SWTbahn Full without conflicting routes is only 73.4MB);
• For a given pair of source and destination signals, store only the "best" N number of routes; or
• Limit the maximum number of intermediate signals that a route can go past (currently the command line argument route limits the number of intermediate signals to 0 (simple) or unlimited (extended).

[trinnguyen]

Close the issue as there is a solution described above.