VMS: Index-sequential file vs relational database

Content:

  1. Introduction
  2. Index-sequential file
  3. Relational database
  4. Comparison

Introduction:

The article VMS Tech Demo 3 - from index-sequential file to SQLite database showed how to migrate from index-sequential files to relational database using various modern tools.

But no matter the tools - the first question obviously are: is it beneficial to do such a migration.

The claim is:

Here we will look into whether and in what cases such a migration saves code (work).

We will look at an customer and order example very similar to the examples in the previously mentioned article.

For index-sequential file we will use languages:

I am not good at Cobol, but the examples compile, run and produce the desired output.

For relational database we will use SQLite again and languages:

Build and run commands will not be shown as they are covered by the previously mentioned article.

The number of lines obviously depends a bit on programming style and code formatting plus some semi-random decisions on how to count reusable code. But the code is as I write it and the code is posted here, so everyone can judge for themselves.

Also note that I am not equally familiar with all languages - I have no real Cobol experience, so the Cobol examples may not be optimal.

Index-sequential file:

Data definition:

recs.pas: 

const
   MAX_ORDERLINES_PER_ORDER = 100;

type
   orderline = record
                  item : packed array [1..32] of char;
                  price : integer;
               end;
   customer = record
                 customer_id : [key(0)] integer;
                 name : packed array [1..32] of char;
                 address : packed array [1..64] of char;
              end;
   order = record
              order_id : [key(0)] integer;
              customer_id : [key(1)] integer;
              ref : packed array [1..32] of char;
              status : packed array [1..16] of char;
              no_orderlines : integer;
              orderlines : array [1..MAX_ORDERLINES_PER_ORDER] of orderline;
           end;

customer.cob: 

FD CUSTOMER-FILE.
01 CUSTOMER-RECORD.
    03 CUSTOMER-ID PIC S9(8) COMP.
    03 NAME PIC X(32).
    03 ADDRESS PIC X(64).

order.cob: 

FD ORDER-FILE.
01 ORDER-RECORD.
    03 ORDER-ID PIC S9(8) COMP.
    03 ORDER-CUSTOMER-ID PIC S9(8) COMP.
    03 REF PIC X(32).
    03 ORDER-STATUS PIC X(16).
    03 NO-ORDERLINES PIC S9(8) COMP.
    03 ORDERLINES-ARRAY OCCURS 100 TIMES.
       05 ORDERLINE-ITEM PIC X(32).
       05 ORDERLINE-PRICE PIC S9(8) COMP.

Create:

Creating the index-sequential files.

cre.pas: 

program cre(input,output);

%include 'recs.pas'

var
   fc : file of customer;
   fo : file of order;

begin
   open(fc, 'customer.isq', new, organization := indexed, access_method := keyed);
   close(fc);
   writeln('customer.isq created');
   open(fo, 'order.isq', new, organization := indexed, access_method := keyed);
   close(fo);
   writeln('order.isq created');
end.

Import data:

Importing data from CSV files to index-sequential files.

csv.pas: 

var
   fcsv : text;

function csv_read_str : pstr;

var
   line : pstr;
   c : char;

begin
   line := '';
   c := ' ';
   while not(eoln(fcsv)) and (c <> ',')do begin
      read(fcsv, c);
      if c <> ',' then line := line + c;
   end;
   csv_read_str := line;
end;

function csv_read_int : integer;

var
   v : integer;

begin
   readv(csv_read_str, v);
   return v;
end;

procedure csv_read_lin;

begin
   readln(fcsv);
end;

imp.pas: 

program imp(input,output);

%include 'gentypes.pas'
%include 'recs.pas'
%include 'csv.pas'

procedure import_customers(fnm : pstr);

var
   fc : file of customer;
   c : customer;
   nrec : integer;

begin
   open(fcsv, fnm, old);
   reset(fcsv);
   open(fc, 'customer.isq', old, organization := indexed, access_method := keyed);
   nrec := 0;
   while not(eof(fcsv)) do begin
      c.customer_id := csv_read_int;
      c.name := csv_read_str;
      c.address := csv_read_str;
      csv_read_lin;
      fc^ := c;
      put(fc);
      nrec := nrec + 1;
   end;
   close(fc);
   close(fcsv);
   writeln(nrec:1,' records imported to customer.isq');
end;

procedure import_orders(fnm : pstr);

var
   fo : file of order;
   o : order;
   i : integer;
   nrec : integer;

begin
   open(fcsv, fnm, old);
   reset(fcsv);
   open(fo, 'order.isq', old, organization := indexed, access_method := keyed);
   nrec := 0;
   while not(eof(fcsv)) do begin
      o.order_id := csv_read_int;
      o.customer_id := csv_read_int;
      o.ref := csv_read_str;
      o.status := csv_read_str;
      o.no_orderlines := csv_read_int;
      for i := 1 to o.no_orderlines do begin
         o.orderlines[i].item := csv_read_str;
         o.orderlines[i].price := csv_read_int;
      end;
      csv_read_lin;
      fo^ := o;
      put(fo);
      nrec := nrec + 1;
   end;
   close(fo);
   close(fcsv);
   writeln(nrec:1,' records imported to order.isq');
end;

begin
   import_customers('[-]customers.dat');
   import_orders('[-]ordersx.dat');
end.

Insert data:

Inserting new data into index-sequential files.

ins.pas: 

program ins(input, output);

%include 'recs.pas'

var
   o : order;
   fo : file of order;

begin
   open(fo, 'order.isq', old, organization := indexed, access_method := keyed);
   o.order_id := 11;
   o.customer_id := 1;
   o.ref := '-';
   o.status := 'Open';
   o.no_orderlines := 2;
   o.orderlines[1].item := 'Stuff';
   o.orderlines[1].price := 5;
   o.orderlines[2].item := 'Other stuff';
   o.orderlines[2].price := 5;
   fo^ := o;
   put(fo);
   close(fo);
   writeln('Pascal - insert OK');
end.

ins.cob: 

IDENTIFICATION DIVISION.
PROGRAM-ID.INS.

ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
    SELECT OPTIONAL ORDER-FILE ASSIGN TO "order.isq" ORGANIZATION IS INDEXED ACCESS MODE IS DYNAMIC RECORD KEY IS ORDER-ID.

DATA DIVISION.
FILE SECTION.
COPY "ORDER.COB".
WORKING-STORAGE SECTION.

PROCEDURE DIVISION.
MAIN-PARAGRAPH.
    OPEN I-O ORDER-FILE
    MOVE 12 TO ORDER-ID
    MOVE 1 TO ORDER-CUSTOMER-ID
    MOVE "-" TO REF
    MOVE "Open" TO ORDER-STATUS
    MOVE 2 TO NO-ORDERLINES
    MOVE "Stuff" TO ORDERLINE-ITEM(1)
    MOVE 5 TO ORDERLINE-PRICE(1)
    MOVE "Other stuff" TO ORDERLINE-ITEM(2)
    MOVE 5 TO ORDERLINE-PRICE(2)
    PERFORM INSERT-PARAGRAPH
    CLOSE ORDER-FILE
    DISPLAY "Cobol - insert OK:"
    STOP RUN.
INSERT-PARAGRAPH.
    WRITE ORDER-RECORD
        INVALID KEY DISPLAY "Error writing"
        NOT INVALID KEY CONTINUE
    END-WRITE.

Simple query:

A simple query - just list all customers.

smpl.pas: 

program smpl(input,output);

%include 'gentypes.pas'
%include 'recs.pas'

var
   fc : file of customer;
   c : customer;

begin
   writeln('Pascal - customers:');
   open(fc, 'customer.isq', old, organization := indexed, access_method := keyed);
   resetk(fc, 0);
   while not eof(fc) do begin
      c := fc^;
      writeln(trim(c.name), ' : ', trim(c.address));
      get(fc);
   end;
   close(fc);
end.

smpl.cob: 

IDENTIFICATION DIVISION.
PROGRAM-ID.SMPL.

ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
    SELECT OPTIONAL CUSTOMER-FILE ASSIGN TO "customer.isq" ORGANIZATION IS INDEXED ACCESS MODE IS DYNAMIC RECORD KEY IS CUSTOMER-ID.

DATA DIVISION.
FILE SECTION.
COPY "CUSTOMER.COB".
WORKING-STORAGE SECTION.
01 EOF-FLAG PIC X.         
01 NAME-LEN PIC S9(8) COMP.
01 ADDRESS-LEN PIC S9(8) COMP.

PROCEDURE DIVISION.
MAIN-PARAGRAPH.
    DISPLAY "Cobol - customers:"
    OPEN I-O CUSTOMER-FILE
    MOVE 'N' TO EOF-FLAG
    PERFORM UNTIL EOF-FLAG = 'Y'
      READ CUSTOMER-FILE NEXT
        AT END MOVE 'Y' TO EOF-FLAG
        NOT AT END PERFORM SHOW-PARAGRAPH
      END-READ
    END-PERFORM
    CLOSE CUSTOMER-FILE
    STOP RUN.
SHOW-PARAGRAPH.
    PERFORM VARYING NAME-LEN FROM 32 BY -1 UNTIL NAME(NAME-LEN:1) NOT = " " OR NAME-LEN = 1 CONTINUE END-PERFORM
    PERFORM VARYING ADDRESS-LEN FROM 64 BY -1 UNTIL ADDRESS(ADDRESS-LEN:1) NOT = " " OR ADDRESS-LEN = 1 CONTINUE END-PERFORM
    DISPLAY NAME(1:NAME-LEN) " : " ADDRESS(1:ADDRESS-LEN).

Complex query:

A complex query - finding the top customers by sales.

cmplx.pas: 

program cmplx(input,output);

%include 'gentypes.pas'
%include 'recs.pas'

const
   MAX_CUSTOMERS = 10000;

type
   extcustomer = record
                    customer_name : packed array [1..32] of char;
                    total_sales : integer;
                 end;
   extcustomerlist = array [1..MAX_CUSTOMERS] of extcustomer;

var
   no_xc : integer;
   xc_list : extcustomerlist;

procedure load_data;

var
   fc : file of customer;
   fo : file of order;
   c : customer;
   o : order;
   i : integer;

begin
   open(fc, 'customer.isq', old, organization := indexed, access_method := keyed);
   open(fo, 'order.isq', old, organization := indexed, access_method := keyed);
   resetk(fc, 0);
   no_xc := 0;
   while not eof(fc) do begin
      c := fc^;
      no_xc := no_xc + 1;
      xc_list[no_xc].customer_name := c.name;
      xc_list[no_xc].total_sales := 0;
      resetk(fo, 1);
      findk(fo, 1, c.customer_id);
      while not(ufb(fo)) and not(eof(fo)) and (fo^.customer_id = c.customer_id) do begin
         o := fo^;
         if o.status = 'Delivered' then begin
            for i := 1 to o.no_orderlines do begin
               xc_list[no_xc].total_sales := xc_list[no_xc].total_sales + o.orderlines[i].price;
            end;
         end;
         get(fo);                  ;
      end;
      get(fc);
   end;
   close(fo);
   close(fc);
end;

procedure sort_data;

var
   i, j : integer;
   tmp : extcustomer;

begin
   for i := 1 to no_xc do begin
      for j := (i + 1) to no_xc do begin
         if xc_list[i].total_sales < xc_list[j].total_sales then begin
            tmp := xc_list[i];
            xc_list[i] := xc_list[j];
            xc_list[j] := tmp;
         end;
      end;
   end;
end;

procedure dump_data;

var
   i : integer;

begin
   writeln('Pascal - top customers by sales:');
   for i := 1 to min(no_xc, 3) do begin
      if xc_list[i].total_sales > 10 then begin
         writeln(trim(xc_list[i].customer_name),' : ',xc_list[i].total_sales:1);
      end;
   end;
end;

begin
   load_data;
   sort_data;
   dump_data;
end.

cmplx.cob: 

IDENTIFICATION DIVISION.
PROGRAM-ID.CMPLX.

ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
    SELECT OPTIONAL CUSTOMER-FILE ASSIGN TO "customer.isq"
                                  ORGANIZATION IS INDEXED
                                  ACCESS MODE IS DYNAMIC
                                  RECORD KEY IS CUSTOMER-ID.
    SELECT OPTIONAL ORDER-FILE ASSIGN TO "order.isq"
                               ORGANIZATION IS INDEXED
                               ACCESS MODE IS DYNAMIC
                               RECORD KEY IS ORDER-ID
                               ALTERNATE RECORD KEY IS ORDER-CUSTOMER-ID WITH DUPLICATES.

DATA DIVISION.
FILE SECTION.
COPY "CUSTOMER.COB".
COPY "ORDER.COB".
WORKING-STORAGE SECTION.
01 NO-EXT-CUSTOMER PIC S9(8) COMP.
01 EXT-CUSTOMER-LIST.
    03 EXT-CUSTOMER-ARRAY OCCURS 10000 TIMES.
        05 EXT-CUSTOMER-NAME PIC X(32).
        05 TOTAL-SALES PIC S9(8) COMP.
01 EOF-FLAG PIC X.         
01 DONE-FLAG PIC X.         
01 I PIC S9(8) COMP.
01 NAME-LEN PIC S9(8) COMP.
01 TS PIC 9(8) DISPLAY.

PROCEDURE DIVISION.
MAIN-PARAGRAPH.
    PERFORM LOAD-DATA-PARAGRAPH
    PERFORM SORT-DATA-PARAGRAPH
    PERFORM DUMP-DATA-PARAGRAPH
    STOP RUN.
LOAD-DATA-PARAGRAPH.
    MOVE 0 TO NO-EXT-CUSTOMER
    OPEN I-O CUSTOMER-FILE
    OPEN I-O ORDER-FILE
    MOVE 'N' TO EOF-FLAG
    PERFORM UNTIL EOF-FLAG = 'Y'
      READ CUSTOMER-FILE NEXT
        AT END MOVE 'Y' TO EOF-FLAG
        NOT AT END PERFORM LOAD-ONE-PARAGRAPH
      END-READ
    END-PERFORM
    CLOSE ORDER-FILE.
    CLOSE CUSTOMER-FILE.
LOAD-ONE-PARAGRAPH.
    COMPUTE NO-EXT-CUSTOMER = NO-EXT-CUSTOMER + 1
    MOVE NAME TO EXT-CUSTOMER-NAME(NO-EXT-CUSTOMER)
    MOVE 0 TO TOTAL-SALES(NO-EXT-CUSTOMER)
    MOVE 'N' TO EOF-FLAG
    MOVE 'N' TO DONE-FLAG
    MOVE CUSTOMER-ID TO ORDER-CUSTOMER-ID
    START ORDER-FILE KEY IS GREATER THAN OR EQUAL TO ORDER-CUSTOMER-ID
        INVALID KEY MOVE "Y" TO DONE-FLAG
        NOT INVALID KEY CONTINUE
    END-START
    PERFORM UNTIL EOF-FLAG = 'Y' OR DONE-FLAG = 'Y'
        READ ORDER-FILE NEXT
            AT END MOVE 'Y' TO EOF-FLAG
            NOT AT END PERFORM LOAD-ORDERS-PARAGRAPH
        END-READ
    END-PERFORM.
LOAD-ORDERS-PARAGRAPH.
    IF ORDER-CUSTOMER-ID NOT EQUAL CUSTOMER-ID THEN
        MOVE "Y" TO DONE-FLAG
    ELSE
        IF ORDER-STATUS = "Delivered" THEN
            PERFORM VARYING I FROM 1 BY 1 UNTIL I > NO-ORDERLINES
                COMPUTE TOTAL-SALES(NO-EXT-CUSTOMER) = TOTAL-SALES(NO-EXT-CUSTOMER) + ORDERLINE-PRICE(I)
            END-PERFORM
        END-IF
    END-IF.
SORT-DATA-PARAGRAPH.
    SORT EXT-CUSTOMER-ARRAY ON DESCENDING TOTAL-SALES.
DUMP-DATA-PARAGRAPH.
    DISPLAY "Cobol - top customers by sales"
    PERFORM VARYING I FROM 1 BY 1 UNTIL I > 3 OR I > NO-EXT-CUSTOMER
        IF TOTAL-SALES(I) > 10 THEN
            PERFORM VARYING NAME-LEN FROM 32 BY -1 UNTIL EXT-CUSTOMER-NAME(I)(NAME-LEN:1) NOT = " " OR NAME-LEN = 1
                CONTINUE
            END-PERFORM
            MOVE TOTAL-SALES(I) TO TS
            DISPLAY EXT-CUSTOMER-NAME(I)(1:NAME-LEN) " : " TS
        END-IF
    END-PERFORM.

Change data structure and data:

A database change - add a field to the order record and set all values to '-'.

recs2.pas: 

const
   MAX_ORDERLINES_PER_ORDER = 100;

type
   orderline = record
                  item : packed array [1..32] of char;
                  price : integer;
               end;
   customer = record
                 customer_id : [key(0)] integer;
                 name : packed array [1..32] of char;
                 address : packed array [1..64] of char;
              end;
   order = record
              order_id : [key(0)] integer;
              customer_id : [key(1)] integer;
              ref : packed array [1..32] of char;
              status : packed array [1..16] of char;
              no_orderlines : integer;
              orderlines : array [1..MAX_ORDERLINES_PER_ORDER] of orderline;
           end;
   order2 = record
               order_id : [key(0)] integer;
               customer_id : [key(1)] integer;
               ref : packed array [1..32] of char;
               status : packed array [1..16] of char;
               salesrep : packed array [1..32] of char;
               no_orderlines : integer;
               orderlines : array [1..MAX_ORDERLINES_PER_ORDER] of orderline;
            end;

chg.pas: 

program chg(input,output);

%include 'gentypes.pas'
%include 'recs2.pas'

var
   fo : file of order;
   fo2 : file of order2;
   o : order;
   o2 : order2;

begin
   open(fo, 'order.isq', old, organization := indexed, access_method := keyed);
   open(fo2, 'order2.isq', new, organization := indexed, access_method := keyed);
   resetk(fo, 0);
   while not eof(fo) do begin
      o := fo^;
      o2.order_id := o.order_id;
      o2.customer_id := o.customer_id;
      o2.ref := o.ref;
      o2.status := o.status;
      o2.salesrep :='-';
      o2.no_orderlines := o.no_orderlines;
      o2.orderlines := o.orderlines;
      fo2^ := o2;
      put(fo2);
      get(fo);
   end;
   close(fo);
   close(fo2);
   writeln('File order.isq converted');
end.

Relational database:

Create:

Define 3 tables using command line tool. 

CREATE TABLE customers(
    customer_id INTEGER NOT NULL,
    name VARCHAR(32),
    address VARCHAR(64),
    PRIMARY KEY(customer_id)
);
CREATE TABLE orders(
    order_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    ref VARCHAR(32),
    status VARCHAR(16),
    PRIMARY KEY(order_id)
);
CREATE TABLE orderlines(
    orderline_id INTEGER NOT NULL,
    order_id INTEGER NOT NULL,
    item VARCHAR(32),
    price INTEGER NOT NULL,
    PRIMARY KEY(orderline_id)
);

Import data:

Importing data from CSV files using command line tool. 

.mode csv
.import [-]customers.dat customers
.import [-]orders.dat orders
.import [-]orderlines.dat orderlines

Insert data:

Inserting new data into tables.

ins.c: 

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include "sqlite3.h"

int main()
{
    sqlite3 *con;
    sqlite3_stmt *oins, *olins;
    char *osqlstr = "INSERT INTO orders VALUES(?,?,?,?)";
    char *olsqlstr = "INSERT INTO orderlines VALUES(?,?,?,?)";
    int stat;
    char s1[1000], s2[1000];
    stat = sqlite3_open("test.db", &con);
    stat = sqlite3_prepare(con, osqlstr, strlen(osqlstr), &oins, NULL);
    stat = sqlite3_prepare(con, olsqlstr, strlen(olsqlstr), &olins, NULL);
    sqlite3_bind_int(oins, 1, 11);
    sqlite3_bind_int(oins, 2, 1);
    strcpy(s1, "By C");
    sqlite3_bind_text(oins, 3, s1, strlen(s1), NULL);
    strcpy(s2, "Open");
    sqlite3_bind_text(oins, 4, s2, strlen(s2), NULL);
    stat = sqlite3_step(oins);
    sqlite3_finalize(oins);
    sqlite3_bind_int(olins, 1, 26);
    sqlite3_bind_int(olins, 2, 11);
    strcpy(s1, "Stuff");
    sqlite3_bind_text(olins, 3, s1, strlen(s1), NULL);
    sqlite3_bind_int(olins, 4, 5);
    stat = sqlite3_step(olins);
    sqlite3_finalize(olins);
    stat = sqlite3_prepare(con, olsqlstr, strlen(olsqlstr), &olins, NULL);
    sqlite3_bind_int(olins, 1, 27);
    sqlite3_bind_int(olins, 2, 11);
    strcpy(s1, "Other stuff");
    sqlite3_bind_text(olins, 3, s1, strlen(s1), NULL);
    sqlite3_bind_int(olins, 4, 5);
    stat = sqlite3_step(olins);
    sqlite3_finalize(olins);
    sqlite3_close(con);
    printf("C - insert OK\n");
    return 0;
}

ins.pas: 

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program ins(input, output);

var
   con : sqlite_ptr;
   oins, olins : sqlite_stmt_ptr;

begin
   con := psqlite_open('test.db');
   oins := psqlite_prepare(con, 'INSERT INTO orders VALUES(?,?,?,?)');
   psqlite_bind_int(oins, 1, 12);
   psqlite_bind_int(oins, 2, 1);
   psqlite_bind_text(oins, 3, 'By Pascal');
   psqlite_bind_text(oins, 4, 'Open');
   psqlite_step_nonquery(oins);
   psqlite_finalize(oins);
   olins := psqlite_prepare(con, 'INSERT INTO orderlines VALUES(?,?,?,?)');
   psqlite_bind_int(olins, 1, 28);
   psqlite_bind_int(olins, 2, 12);
   psqlite_bind_text(olins, 3, 'Stuff');
   psqlite_bind_int(olins, 4, 5);
   psqlite_step_nonquery(olins);
   psqlite_finalize(olins);
   olins := psqlite_prepare(con, 'INSERT INTO orderlines VALUES(?,?,?,?)');
   psqlite_bind_int(olins, 1, 29);
   psqlite_bind_int(olins, 2, 12);
   psqlite_bind_text(olins, 3, 'Other stuff');
   psqlite_bind_int(olins, 4, 5);
   psqlite_step_nonquery(olins);
   psqlite_finalize(olins);
   psqlite_close(con);
   writeln('Pascal - insert OK');
end.

ins.py: 

import sqlite3

con = sqlite3.connect('test.db')
c = con.cursor()
c.execute('INSERT INTO orders VALUES(?,?,?,?)', (13, 1, 'By Python', 'Open'))
c.execute('INSERT INTO orderlines VALUES(?,?,?,?)', (30, 13, 'Stuff', 5))
c.execute('INSERT INTO orderlines VALUES(?,?,?,?)', (31, 13, 'Other stuff', 5))
con.commit()
con.close()
print('Python - insert OK')

Ins.java: 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Ins {
    public static void main(String[] args) {
        try {
            Class.forName("org.sqlite.JDBC");
            Connection con = DriverManager.getConnection("jdbc:sqlite:test.db");
            PreparedStatement oins = con.prepareStatement("INSERT INTO orders VALUES(?,?,?,?)");
            PreparedStatement olins = con.prepareStatement("INSERT INTO orderlines VALUES(?,?,?,?)");
            oins.setInt(1, 14);
            oins.setInt(2, 1);
            oins.setString(3, "By Java");
            oins.setString(4, "Open");
            oins.executeUpdate();
            olins.setInt(1, 32);
            olins.setInt(2, 14);
            olins.setString(3, "Stuff");
            olins.setInt(4, 5);
            olins.executeUpdate();
            olins.setInt(1, 33);
            olins.setInt(2, 14);
            olins.setString(3, "Other stuff");
            olins.setInt(4, 5);
            olins.executeUpdate();
            oins.close();
            olins.close();
            con.close();
            System.out.println("Java - insert OK");
        } catch(Exception ex) {
            ex.printStackTrace();
        }
    }
}

Simple query - adhoc:

A simple query - just list all customers - using command line tool.

smpl.com: 

SELECT name,address
FROM customers;

Simple query - application:

A simple query - just list all customers - using code.

smpl.c: 

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include "sqlite3.h"

int main()
{
    sqlite3 *con;
    sqlite3_stmt *sel;
    char *sqlstr = "SELECT name,address "
                   "FROM customers";
    int stat;
    printf("C - customers:\n");
    stat = sqlite3_open("test.db", &con);
    stat = sqlite3_prepare(con, sqlstr, strlen(sqlstr), &sel, NULL);
    while(sqlite3_step(sel) == SQLITE_ROW)
    {
        printf("%s : %s\n", sqlite3_column_text(sel, 0), sqlite3_column_text(sel, 1));
    }
    sqlite3_finalize(sel);
    sqlite3_close(con);
    return 0;
}

smpl.pas: 

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program smpl(input, output);

var
   con : sqlite_ptr;
   sel : sqlite_stmt_ptr;

begin
   writeln('Pascal - customers:');
   con := psqlite_open('test.db');
   sel := psqlite_prepare(con, 'SELECT name,address FROM customers');
   while psqlite_step_query(sel) do begin
      writeln(psqlite_column_text(sel, 0), ' : ', psqlite_column_text(sel, 1));
   end;
   psqlite_finalize(sel);
   psqlite_close(con);
end.

smpl.py: 

import sqlite3

print('Python - customers:')
con = sqlite3.connect('test.db')
c = con.cursor()
c.execute('''SELECT name,address
             FROM customers''')
for row in c.fetchall():
    print('%s : %s' % (row[0], row[1]))
con.commit()
con.close()

Smpl.java: 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Smpl {
    public static void main(String[] args) {
        try {
            System.out.println("Java - customers:");
            Class.forName("org.sqlite.JDBC");
            Connection con = DriverManager.getConnection("jdbc:sqlite:test.db");
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT name,address FROM customers");
            while(rs.next()) {
                String name = rs.getString(1);
                String address = rs.getString(2);
                System.out.printf("%s : %s\n", name, address);
            }
            rs.close();
            stmt.close();
            con.close();
        } catch(Exception ex) {
            ex.printStackTrace();
        }
    }
}

Complex query - adhoc:

A complex query - finding the top customers by sales - using command line tool. 

SELECT customers.name,SUM(orderlines.price) AS totalsales
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN orderlines ON orders.order_id = orderlines.order_id
WHERE orders.status = 'Delivered'
GROUP BY customers.name
HAVING totalsales > 10
ORDER BY totalsales DESC
LIMIT 3;

Complex query - application:

A complex query - finding the top customers by sales - using code.

cmplx.c: 

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include "sqlite3.h"

int main()
{
    sqlite3 *con;
    sqlite3_stmt *sel;
    char *sqlstr = "SELECT customers.name,SUM(orderlines.price) AS totalsales "
                   "FROM customers "
                   "LEFT JOIN orders ON customers.customer_id = orders.customer_id "
                   "LEFT JOIN orderlines ON orders.order_id = orderlines.order_id "
                   "WHERE orders.status = 'Delivered' "
                   "GROUP BY customers.name "
                   "HAVING totalsales > 10 "
                   "ORDER BY totalsales DESC "
                   "LIMIT 3";
    int stat;
    printf("C - top customers by sales:\n");
    stat = sqlite3_open("test.db", &con);
    stat = sqlite3_prepare(con, sqlstr, strlen(sqlstr), &sel, NULL);
    while(sqlite3_step(sel) == SQLITE_ROW)
    {
        printf("%s : %d\n", sqlite3_column_text(sel, 0), sqlite3_column_int(sel, 1));
    }
    sqlite3_finalize(sel);
    sqlite3_close(con);
    return 0;
}

cmplx.pas: 

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program cmplx(input, output);

var
   con : sqlite_ptr;
   sel : sqlite_stmt_ptr;

begin
   writeln('Pascal - top customers by sales:');
   con := psqlite_open('test.db');
   sel := psqlite_prepare(con, 'SELECT customers.name,SUM(orderlines.price) AS totalsales ' +
                               'FROM customers ' +
                               'LEFT JOIN orders ON customers.customer_id = orders.customer_id ' +
                               'LEFT JOIN orderlines ON orders.order_id = orderlines.order_id ' +
                               'WHERE orders.status = ''Delivered'' ' +
                               'GROUP BY customers.name ' +
                               'HAVING totalsales > 10 ' +
                               'ORDER BY totalsales DESC ' +
                               'LIMIT 3');
   while psqlite_step_query(sel) do begin
      writeln(psqlite_column_text(sel, 0), ' : ', psqlite_column_int(sel, 1):1);
   end;
   psqlite_finalize(sel);
   psqlite_close(con);
end.

cmplx.py: 

import sqlite3

print('Python - top customers by sales:')
con = sqlite3.connect('test.db')
c = con.cursor()
c.execute('''SELECT customers.name,SUM(orderlines.price) AS totalsales
             FROM customers
             LEFT JOIN orders ON customers.customer_id = orders.customer_id
             LEFT JOIN orderlines ON orders.order_id = orderlines.order_id
             WHERE orders.status = 'Delivered'
             GROUP BY customers.name
             HAVING totalsales > 10
             ORDER BY totalsales DESC
             LIMIT 3''')
for row in c.fetchall():
    print('%s : %d' % (row[0], row[1]))
con.commit()
con.close()

Cmplx.java: 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Cmplx {
    public static void main(String[] args) {
        try {
            System.out.println("Java - top customers by sales:");
            Class.forName("org.sqlite.JDBC");
            Connection con = DriverManager.getConnection("jdbc:sqlite:test.db");
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT customers.name,SUM(orderlines.price) AS totalsales " +
                                             "FROM customers " +
                                             "LEFT JOIN orders ON customers.customer_id = orders.customer_id " +
                                             "LEFT JOIN orderlines ON orders.order_id = orderlines.order_id " +
                                             "WHERE orders.status = 'Delivered' " +
                                             "GROUP BY customers.name " +
                                             "HAVING totalsales > 10 " +
                                             "ORDER BY totalsales DESC " +
                                             "LIMIT 3");
            while(rs.next()) {
                String name = rs.getString(1);
                int totalsales = rs.getInt(2);
                System.out.printf("%s : %d\n", name, totalsales);
            }
            rs.close();
            stmt.close();
            con.close();
        } catch(Exception ex) {
            ex.printStackTrace();
        }
    }
}

Change data structure and data:

A database change - add a field to the order table and set all values to '-'.

chg.com: 

ALTER TABLE orders ADD COLUMN salesrep VARCHAR(32);
UPDATE orders SET salesrep = '-';

Comparison:

Example Index-sequential file Relational database (SQLite) Conclusion
Create database Pascal : 16 + 22 lines SQL script : 24 lines About the same amount of code
Import data to database Pascal: 69 + 35 lines SQL script : 8 lines A lot less code
Insert data into database Pascal : 24 lines
Cobol : 34 lines		 C : 44 lines
Pascal : 33 lines
Python : 10 lines
Java : 35 lines		 About the same amount of code
Simple query - application Pascal : 20 + 13 lines
Cobol : 33 lines		 C : 24 lines
Pascal : 17 lines
Python : 11 lines
Java : 26 lines		 About the same amount of code
Simple query - adhoc SQL script : 5 lines A lot less code
Complex query - application Pascal : 92 lines
Cobol : 91 lines		 C : 31 lines
Pascal : 25 lines
Python : 18 lines
Java : 34 lines		 A lot less code
Complex query - adhoc SQL script : 12 lines A lot less code
Change of data structue and data update Pascal : 32 + 31 lines SQL script : 6 lines A lot less code

We conclude that a relational database requires a lot less code for:

We conclude that a relational database requires about the same amount code for:

Article history:

Version Date Description
1.0 November 19th 2021 Initial version

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj