Database using the Peer-to-Peer Client/Server protocol

This tutorial is in beta while we work on a better LogHandler, and will be updated accordingly

This program provides an example of how we can use SIMPOL to access database files via the Superbase Peer-to-Peer Client/Server protocol. This means we need to first launch the server which will contain the database, and then connect to the server containing the database so we can edit records within the table. In this program we are going to do the following to our database:

  • Connect to a databse and find an index
  • Find a record
  • Add a record to the database
  • Update a record
  • Show a record
  • Deleting a record

The server configuration and launch of the server is covered here and should be done before continuing with this program

We are going to be using two libraries, one old and one new, first and most importantly ppcstype1 is the library that will allow us to actually connect to the server and use our database. The second library we are going to be using is LogManager this is going to be our log handler and will allow us to see what is happening in our program. At the end of the process you should have a log file with the following information in it:

The final log file that should be returned by this program

Setup

After you have created your new project, and before you actually start programming we have to configure the project settings. For this project this is quite simple, as we only need to add the LogManager library (found under SIMPOL\lib\logmanager.sml) The Project settings tab should now look like this:

Project Settings Includes and libraries settings for ppcs-client.sml

This program consists of the main function and one library, we are gonna walk our way through the main function and then explore what the library functions

Main Program

Before we even get to the main function, we will need to include our log-handler library, this is an updated version of the inbuilt LogManager it is available for download here and at the bottom of this page and is inserted first into the bin folder in your project and then into the program as follows:

include "log-handler.sma"

Declaration and Initialisation

Within the actual main function the first thing we need to do is to declare the variables we need:

LogHandler log_handler;
string user_name;
string result;
integer errno;
ppcstype1 ppcs;

The first of these, LogHandler, will be used to log the interactions between the program and the database. user_name will be used to declare the username with which you will access the database, resultwill be used as the return variable for the main function, allowing us to see errors within the IDE. errorno is an integer which will be used for debugging, and finally ppcs will be used to declare a ppcstype1 object which allows us to actually read and write to the database although not allowing us to modify the database definiton or create or modify database tables

user_name = "test";

log_handler =@ LogHandler.new("ppcs-client.log");
log_handler.info_message("Start PPCS client application");

errno = 0;
ppcs =@ ppcstype1.new(udpport=.nul, error=errno, username=user_name);

Two important things in this section of code: we are first initialising a LogHandler object, the string we parse into the object will be used as the file name for the log file. The precise mechanics of LogHandler will be discussed later for now all you need to know is that it will write to a file and allow us to debug the program using functions such as info_message. The second thing we are doing is initalising a PPCS object which we will use to connect to our database server. To initalise the PPCS object we have to set values for the following: udpport, error, and username. The actual values for these are not important so for this example we are setting the UDP port to .nul, the error to 0 and the username to any string we like (in this case test), if these are not set the program will throw errors and not execute correctly.

Connecting to the Database and finding an index

Checking the PPCS Object

Now that we have created our PPCS object we want to actually connect to server containing the database, right. However, before we can do this we should check whether we successfully created our ppcs object, this is done using the following code:

if ppcs =@= .nul
     result = log_handler.error_message("Cannot create ppcs object. Error code: " + \
                                        .tostr(errno, 10));
else

This code checks whether ppcs refers to a .nul object, this is done using =@= operator. The =@= operator returns true if both operands are references to the same object, or are both nul object references, otherwise it will return as false. This means that if anything has gone wrong with the initialisation of ppcs it will give us a nice error message. To allow this nice representation we have to convert the error code into a string, this is done using .tostr(errno, 10) where errno is the error code and 10 represents the base in which this number is written. This error message will be both logged using our log handler and displayed within the IDE, this is done to ease debugging. To display it in the IDE we are going assigning the error message to our return variableresult.

Connecting to the database

Once we confirmed that the PPCS object has initalised we can open the database on our PPCS server

else
    ppcstype1file file;
    string db_name;

    db_name = "user";

    file =@ ppcs.openudpfile("127.0.0.1:4000", db_name, error=errno);

To do this we need to first declare a ppcstype1file object, in this case called file, as well as a string for storing the database name, called db_name. Opening the database on our PPCS server will be done using the openudpfile function of our ppcs object, we need to parse at a minimum the IP address and port number of the PPCS server, this should by default be 127.0.0.1:4000, we also need to parse in the name of the file we want to open, finally to allow us to better see what error is raised we also need to parse in our error code variable. At this point we are checking whether we have successfully opened the database file

if file =@= .nul
      result = log_handler.error_message("Cannot open file '" + db_name + \
                                         "'. Error code " + .tostr(errno, 10));
else

This works because if the attempt to open the file is unsuccessful openudpfile returns .nul. If the attempt is successful it will return a ppcstype1file object, which is what we want here

Finding an index

We have now successfully connected to the database and now want to find a specific index, to do this we first need to initialise several variables. We are creating first two objects a ppcstype1record, and a ppcstype1index these represent a databsae record and datbase index respectively. index_name will be used to specify the name of the index we want to look for, index_found will be used to determine whether we successfully found an index and index_id will be used to find a record within that index.

ppcstype1record record;
ppcstype1index index;
string index_name;
integer index_id;
boolean index_found; log_handler.info_message("Connect to database '" + db_name + "'");

We are then also going to log that we have successfully connected to the database, and set some values to the variables we have just declared. index_id and index_name can be set to anything but for our examples the values below will work best and index_found should always be set to default to false

index_id = 137;
index_name = "id";
index_found = .false;

We will now find out how many records there are in the table and log this information, this step is not required but we are highlighting the existence of such a function.

log_handler.trace_message("Number of records in table: " + .tostr(file.recordcount(), 10));

The first thing we do in this code is to find the first index in our file. This is done using the firstindex function which refers to the ppcstype1index object that represents the first index in the file, and if there are no indexes on the file then it will refer a .nul object. We want to do two things now, loop through the database looking for an index with a specified name and to check whether there are any indexes actually present in the database. We can kill two proverbial stones with one stone using the following while loop

index =@ file.firstindex;
while index !@= .nul
    log_handler.trace_message("found index '" + index.field.name + "'");
    if index.field.name == index_name
         index_found = .true;
    else
         index =@ index.next;
    end if 
end while index =@= file.firstindex or index_found;
This while loop will only run when index is not equal to a .nul object and will exit when it has either run through the database, and is back to the beginning or index_found is true. Inside this while loop we will do several things, first we will log all indexes we have found and we will then check to see whether the index name matches the one we are looking for. If it does we will set index_found to true and thus exit the while loop or we will refer to the next index in the table. The indexes for a file form a closed loop, this means once we have cycled through once it will try and search from the first index again, and we will exit the loop.

Finding a record

In order to find a record we should first check whether the index we are looking in exists, we should then attempt to select the record we want and then check whether this was successful and providing logs of success/failure.

if not index_found
     result = log_handler.error_message("Index '" + index_name + "' not found!");
else
     boolean found_one;

     found_one = .false;
     record =@ index.selectkey(index_id, error=errno);

We are checking if index_found is not true (i.e. false) and if we haven’t found the index we want we will log this and display it in the IDE. If however, we have found the index we want we will create a boolean object which we will set to .false, this will be used later in the program. The next thing we will do is select a record with a specified value in the index, selectkey will return a ppcstype1record type object if it is successful and .nul if it is not. This means if we make record refer to the object returned we can check what object is referenced and if it is .nul we can log that we did not find the relevant record.

The next thing we want to do is determine the behaviour when a record is both found and not found. If we did not find the record we are looking for we should attempt to start from the first record, this can be done using the select function which will select by default the first record in the database. We then check whether this was successful, if it was found_one can be set to .true otherwise we should exit this if statement and move onto the next piece of code.
if record =@= .nul
    log_handler.error_message("Did not find record with id '" + .tostr(index_id, 10) + \
                              "'. Error code: " + .tostr(errno, 10));
    record =@ index.select(error=errno);
    if record =@= .nul
         result = log_handler.error_message("Did not find first record. Error code: " + \
                                            .tostr(errno, 10));
    else
         log_handler.trace_message("Start with first record");
         found_one = .true;
    end if
else
    log_handler.trace_message("Start with record with index '" + .tostr(index_id, 10) + "'");
    found_one = .true;
end if
However if we have found the relevant record we want to start with this record, this should be logged and found_one should be set to .true as done in the else statement above

Displaying the entry

Once we have found a record we want to display all the fields in it nicely, this is made easier by dealing with the different datatypes that the field could have differently. For this we want a while loop that starts from the first field and loops through all the fields until it has reached the first field again, and inside that an if statement which seperates the different datatypes.

if found_one == .true
     ppcstype1field field;

     field =@ file.firstfield
     while
          if field.datatype =@= string
              log_handler.trace_message(" -> " + field.name + ": " + record.get(field));
          else if field.datatype =@= integer or \
                  field.datatype =@= date or \
                  field.datatype =@= time
              log_handler.trace_message(" -> " + field.name + ": " + \
                                       .tostr(record.get(field), 10));
          else if field.datatype =@= number
              log_handler.trace_message(" -> " + field.name + ": " + \
                                       .tostr(.fix(record.get(field), 100), 10));
          end if
          field =@ field.next
     end while field =@= file.firstfield

We are here initially checking if we have actually found a record, we then declare a ppcstype1field object which we will initalise by referring it to the firstfield function. Once this has been done we can begin to loop through the fields, and then we need to convert the contents of the field into a string so we can parse them into our trace_message function. For strings this requires no work and we can simply get the value of the field in our record using the get function. However when we are dealing with an integer, a date or a time we need to convert this to a string using .tostr before we can parse it. The last type we will address is the number type, this will both be converted using .tostr but also rounded to the nearest 10th of a fraction using .fix Once this has been done we move onto the next field and repeat until we have come back to the first field at which point we exit the while loop

At this point we also want to make sure we have gotten all the records with the same index, this is done as follows

while
     record =@ record.select(error=errno)
     if record =@= .nul
          log_handler.trace_message("No more record found. Error code: " + .tostr(errno, 10));
     else
         log_handler.trace_message(" -> id: " + .tostr(record!id, 10) + "; first name: " + \
                                  record!first_name + "; last name: " + record!last_name);
     end if
end while record =@= .nul

The while loop here loops until record refers to .nul, this is possible because the select function will return a ppcstype1record when you successfully select a record and return .nul if it fails. If record refers to .nul we log that there are no more records found otherwise we will log the record id, as well as the first and last name of that record, this is done using the ! member operator, this operator will attempt to return the value of the field whose name is given after the operator (exact use above). Next we are goign to add a record to the database

Adding a record to the Database

To add a record you need to create a ppcstype1record object, set the fields and then save it to the database. The first thing we will do is reset the error code. Then we will create a new ppcstype1record object using the newrecord function, and subsequently using the member operator ! we will set values for the record fields.

errno = 0

log_handler.info_message("Add record with id '" + .tostr(index_id, 10) + "'");
record =@ file.newrecord();
record!id = index_id;
record!first_name = "First";
record!last_name = "Record";

We now need to save the record into the database, as up until this point it has not been permanently stored. We should also check if this is successful, and if it is not log it and return it to the IDE. If it is successful we will add a second record, using the same method as above, checking if this recordwas saved successfully and if it wasn’t logging this and returning it to the IDE

if record.save(error=errno) !@= .nul
      result = log_handler.error_message("Cannot add record to table. Error code: " + \
                                        .tostr(errno, 10));
else
    record =@ file.newrecord();
    record!id = index_id;
    record!first_name = "Second";
    record!last_name = "Record";
    if record.save(error=errno) !@= .nul
         result = log_handler.error_message("Cannot add record to table. Error code: " + \
                                           .tostr(errno, 10));
    end if
end if

Updating a record

To update a record we first have to check whether we have a valid index and have been able to save the records we added, we then want to select the record with a lock (this is required if we want to modify or delete the record, and to prevent modification or deletion by others). We can then update the record, in this example we are merely switching the first and second names of the record. We then need to save the updated record and test whether the save was successful or not. If it is unsuccessful we should log this, plus the error code and error text that the save function returns

 if errno == 0 and index_found == .true
    log_handler.info_message("Update first record with id '" + .tostr(index_id, 10) + "'");

    record =@ index.selectkey(index_id, lock=.true, error=errno);
    if record =@= .nul
         log_handler.error_message("Did not find record with id '" + .tostr(index_id, 10) + \
                                        "'. Error code: " + .tostr(errno, 10));
         errno = -1;
    else

The above code will only run if we managed to successfully add the records we are about to update and have found an index. We are first checking for these conditions, then selecting the records, making sure we have set the lock varibale to .true and then tested whether this this operation was succesful or not.

Once we have determined we have successfully selected the record to modify we will declare a buffer string so we can switch the first and last name of the record. We will then save our modification, and as ever log if this fails and return it to the IDE. The error logging here is of note because we are not only logging the error code as we have done with all previous error logs but also any error text that is generated during the execution of the save function.

string buffer;

buffer = record!first_name;
record!first_name = record!last_name;
record!last_name = buffer;
if record.save(error=errno, errortext=buffer) !@= .nul
         result = log_handler.error_message("Cannot update record. Error code: " + \
                                           .tostr(errno, 10) + ": " + buffer);
end if

Show the updated record

We have now successfully updated the record and now want to show this updated record to do so we are again going to check whether any error has been thrown during any of the previous code, as well as whether we have found an index. We then need to select the record, check whether this was done successfully, log if it wasn’t. And upon success we are going to log the updated record as well as any in the same index

if errno == 0 and index_found == .true
      log_handler.trace_message("Show updated record with id '" + .tostr(index_id, 10) + "'");

      record =@ index.selectkey(index_id, error=errno);
      if record =@= .nul
            log_handler.error_message("Did not find record with id '" + .tostr(index_id, 10) + \
                                     "'. Error code: " + .tostr(errno, 10));
            errno = -1;

The above code snippet checks whether no error has occurred and whether we have found an index. It then logs which record we are going to show, attempts to open the record, and if it fails to open a record, logs this error. Finally we set the error code to -1.

Below we are logging the updated record and then using a while loop going through all other records in the index and also logging these

else
     log_handler.trace_message(" -> id: " + .tostr(record!id, 10) + "; first name: " + \
                              record!first_name + "; last name: " + record!last_name);
     while
          record =@ record.select(error=errno)
          if record !@= .nul
                 log_handler.trace_message(" -> id: " + .tostr(record!id, 10) + \
                                           "; first name: " + record!first_name + \
                                           "; last name: " + record!last_name);
           end if
     end while record =@= .nul
end if

Deleting a record

Deleting a record itself is rather simple but to make this tutorial as useful as possible we are going to do this. deleting record with specific ID

First we are going to check whether we actually have an index from which we can delete records. Then we need to select all records we want to delete, we are going to be deleting all the records we have created. Finally we are going to delete the record itself.

if index_found == .true
    integer count;

    log_handler.info_message("Delete all record with id '" + .tostr(index_id, 10) + "'");

    count = 0;

    record =@ index.selectkey(index_id, lock=.true, error=errno);
    if record =@= .nul
          log_handler.error_message("Did not find record with id '" + .tostr(index_id, 10) + \
                                    "'. Error code: " + .tostr(errno, 10));
          errno = -1;
    else
          if record.delete(error=errno) !@= .nul
               log_handler.error_message("Cannot delete first record. Error code: " + \
                                        .tostr(errno, 10));
          else
               count = count + 1;
          end if

In the code above we are first of all checking whether our index_found variable is set to .true, this means that we have an index from which we can delete records, we are then declaring a variable that will allow us to count the number of records we have deleted. We then need to select the record we want to delete, if this doesn’t happen successfully we need to log the error. If we have successfully selected a record we can delete it, again making sure this happens successfully and if it doesn’t we need to log the error. If we successfully delete something we should increment the number of deleted records.

Once we have delted this initial entry we also want to delete all other records with the same id. This is done with a while loop that will exit when we can no longer select a record successfully. We are looping through the records using the select function and deleting all records of the same ID. We are of course going to test if the deletion occurred successfully and log if it does not and increment the number of deleted records if it does. The final thing we are going to do is log the amount of records we have deleted.

while
      record =@ record.select(lock=.true, error=errno)
      if record !@= .nul
             if record.delete(error=errno) !@= .nul
             log_handler.error_message("Cannot delete " + .tostr(count, 10) + \
                                      ". record. Error code: " + .tostr(errno, 10));
             else
                   count = count + 1;
            end if

       end if
end while record =@= .nul

log_handler.trace_message("Deleted " + .tostr(count, 10) + " record(s)");

log-handler.sma

program zip containg the program, database and configuration file.

The Peer-to-Peer Client/Server (PPCS) Component (WIP)

The Peer-to-Peer Client/Server (PPCS) Component (Old)

LogHandler (WIP)