From index-sequential file to SQLite database

[Arne Vajhøj]

This topic has come up a few times.

Now I have written something about it.

A conversion done by Python code running in JVM via Jython
using mapped Java classes:

IS record--(map)--Java class--(automap)--Java class--(map)--DB tables

Also showing some before code in:
* Cobol
* Pascal
and some after code in:
* C
* Pascal
* Python
* PHP
* Java (JDBC)
* Java (JPA)

I am pretty sure that it will be an interesting read for anyone
considering migrating data from index-sequential files to
SQLite (or other relational database).

You may not necessarily like the approach used, but even
if you decide against using the tripple mapping approach
then it may still be interesting as part of the due diligence.

And all the insert and list examples are valid no matter
how the conversion is done.

Enough talk - link:

https://www.vajhoej.dk/arne/articles/vmstd3.html

Lot of code. Almost no comments. And the code may not always be
good for the languages that I do not know well (like Cobol).

Arne

[David Jones]

Post by Arne Vajhøj
This topic has come up a few times.
Now I have written something about it.
A conversion done by Python code running in JVM via Jython
IS record--(map)--Java class--(automap)--Java class--(map)--DB tables
* Cobol
* Pascal
* C
* Pascal
* Python
* PHP
* Java (JDBC)
* Java (JPA)

I first started using indexed files in Fortran, where it was sometimes as simple as adding a "KEY="
clause to the READ statement.

For SQLite applications, my C programs use an intermediate library that encapsulates the SQLite calls
in a statement storage object. The fiddly calls to sqlite3_bind... functions are handled by the execute()
method and those to sqlite3_column_... functions by the next_row() method:

#include "statement_store.h"
sqlite3 *db_cnx;
sps_store sps;
struct sps_context *ctx;
int emp_num, dept_num, rc;
char *first, *last;

rc = sqlite3_open ( "payroll.db", &db_cnx );
sps = sps_create_store (db_cnx, 0);
sps_define (sps, "fetch-emp-by-last",
"SELECT * FROM employee WHERE last LIKE ?1 ORDER by last_name,first_name", "s");

ctx = sps_execute (sps, "fetch-emp-by-last", "%");
while (sps_next_row(ctx, "itti", &emp_num, &first, &last, &dept_num))
{
printf("%8d %-12s %-15s %5d\n", emp_num, first, last, dept_num);
free ( first );
free ( last );
}

if ( ctx->rc != SQLITE_DONE ) printf ( "Error fetching rows!\n" );
printf ("Rows retrieved: %d\n", ctx->count);
rc = sps_rundown(ctx);