A Beginner’s Guide to PPCS

1. What is PPCS? Why do we use it?
   a. Creating a database
   b. Creating a server instance
2. The basics
   a. Setup
   b. Main Program
   c. Connecting to the database and finding an index
   d. Finding a record
   e. Adding a record
   f. Updating a record
   g. Deleting a record
3. Download
4. Source

What is PPCS and why use it?

SIMPOL uses the Superbase Peer-to-Peer Client/Server protocol to allow multiple users to connect to a database. Before we start the actual programming there are a few things to note:

• Names within a PPCS server are flat, so databases with duplicate names, even if they are in different directories, cannot be on the same server.
• Access is provided at a record level in a shared read-write mode, this means you can create, lock, modify and delete records on the fly
• This also means adding, modifying, and removing fields and indexes or even creating or removing tables is not allowed.
• PPCS is a “connectionless” protocol, this means every client-server transaction is independent and no continual connection is upheld
• Currently, the only way to access a PPCS server is using the User Datagram Protocol (UDP).

Sample programs including a client and server program are provided in your installation of Superbase NG

Creating a Database

Before we get to the meat of this tutorial you need to be able to create a PPCS database, we’ve already showed you how to do this in the Personal Quick Start Guide (below)

Creating a server and conneting to it

Now you’ve created a basic (or even complex) database we can get on with the business of creating a server, which we will then connect to, to access the tables within. That’s all been covered in the following tutorial:

For people familiar with Classic this is a new way of running things but it is much more reliable and scalable. Classic required a shared network drive, in Superbase NG the server runs as a static connection that you connect to using an IP Address

The Basics

The rest of this tutorial will cover the basics for using a PPCS server:

1. Connect to the server (using UDP)
2. Connect to a database and find an index
3. Find a record
4. Add a record to the database
5. Update a record
6. Show a record
7. Deleting a record

Setup

We are going to be using two libraries, first, and most importantlyppcstype1. This is the library that will facilitate all of our server-client transactions. We are also going to be using the LogHandler library, this will allow us to better see what is happening while the program executes, and at the end of this tutorial your program should give you a log file that looks something like this:

The LogHandler library is an updated version of LogManager, it is available for download here, and should be placed lib folder wherever your SIMPOL installation is (for most people this will be C:\SIMPOL\lib

This program is going to walk you through the key functionality and capabilities of the ppcstype1 library, and the server-client interactions possible in Superbase NG

Main Program

The main function is split up into various areas, starting of course with the declaration and initialisation of all the variables we will require

Declaration and Initialisation

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 actually read and write records to the database.

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: in the initialisation of LogHandler object we are parsing a string into the object this will be the name of our log file. The second thing we are doing here is initialising our PPCS object which will hold our server connection. To initialise the PPCS object we have to set values for the following: udpport, error, and username. The actual values for these are not important for our basic example so we will set 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. Before we 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 is very IMPORTANT! It may not seem it now, but error checking is incredibly important. It is more than just good form, it allows you to when things do inevitably go wrong figure better figure out why and more importantly it stops the program from crashing as soon as something doesn’t work allowing you to deal with the error or exit the program gracefully.

The above error check is fairly simple, checking whether ppcs refers to .nul. However this is requires a different kind of operator (=@=). This operator is similar to the classic == but instead of checking whether the sides are equal it checks whether the sides refer to the same object (in our case a null object). This allows us to spit out a nice us a nice error message and exit the program if something goes wrong. This error would be logged both using loghandler and within the IDE (using our result variable)

Connecting to the database

Once we confirmed that the PPCS object has initialised 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);

The actual reference to our database file will require the use of another new type,ppcstype1file, at this point we can also declare a variable with the databases name as a string, although this step is not 100% needed.

Opening the database is done using openudpfile() function of our ppcs object, at minimum we need to parse the IP address and port number of the PPCS server, as well as the database name. By default the IP for a local server will be 127.0.0.1:4000. At this point, we can also check whether this was operation was successful

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 the first two objects a ppcstype1record, and a ppcstype1index these represent a database record and database 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;

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


log_handler.info_message("Connect to database '" + db_name + "'");


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;

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.

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

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 data types 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 separates 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 initialise 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 through record until it refers to a null object (.nul), this is possible because the select function will return a ppcstype1record when you successfully select a record and return .nul if it fails. Until select fails (record refers to .nul) we will log the id of the record as well as the first and last name in it when it eventually refers to .nul we log that we have found no more records.

Logging the id, and names is done using themember operator (!) this operator will attempt to return the value of the field whose name is given after the operator (see use above).

Adding a record to the Database

Adding records is very easy, and uses many of the techniques we’ve used above. To create a record we will need a new type, ppcstype1record, once we have initialised a record object we need to set the value of its fields, this is done using the member operator and is the same as any other assignment in SIMPOL.

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";

Once we’re happy with our record we can save it. Up until now, the record has only existed inside our program, normally we will want to put it onto a database for more permanent storage. We should, as always, check if this is successful

if record.save(error=errno) !@= .nul
      result = log_handler.error_message("Cannot add record to table. Error code: " + .tostr(errno, 10));

If we’ve successfully saved the record we can now add more records using the same method, always checking if everything saved properly

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

Updating a record requires selecting a record with a lock, locking is only required if we want to modify or delete a record, to prevent modification by others.

record =@ index.selectkey(index_id, lock=.true, error=errno);

Once we’ve selected the record we want to update and checked this was successful we can then update the record. We are going to only switch the first and second names of this record

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
string buffer;

buffer = record!first_name;
record!first_name = record!last_name;
record!last_name = buffer;

We then need to save the updated record (releasing it for other people to use again)

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

To double-check our work we are going to display this updated record, and as well as any other records in the same index. This is done with the below code, it should be very familiar to you at this point

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

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 the 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 deleted 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)");

Download

This file contains the program, database and configuration file.

Code

function main()
  LogHandler log_handler;
  string result;
  string user_name;
  integer errno;
  ppcstype1 ppcs;
  
  result = "Everything okay";
  user_name = "test";

  log_handler =@ LogHandler.new("ppcs-client.log", level = LOG_HANDLER_TRACE);
  log_handler.info_message("Start PPCS client application");
  
  errno = 0;                                    // Error number must be initialised
  ppcs =@ ppcstype1.new(udpport=.nul, error=errno, username=user_name); 
                                                // udpport have to set because we use UPD,
                                                // .nul because port number is unimportant  
  if ppcs =@= .nul
    result = log_handler.error_message("Cannot create ppcs object. Error code: " + .tostr(errno, 10));
  else                     
    ppcstype1file file;
    string db_name;
    
    db_name = "user";

    file =@ ppcs.openudpfile("127.0.0.1:4000", db_name, error=errno);
    if file =@= .nul
      result = log_handler.error_message("Cannot open file '" + db_name + "'. Error code " + .tostr(errno, 10));
    else  
      ppcstype1record record;
      ppcstype1index index;
      string index_name;
      integer index_id;
      boolean index_found;
      
      log_handler.info_message("Connect to database '" + db_name + "'");

      // set default variables
      index_id = 137;
      index_name = "id";      
      index_found = .false;

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

      //finding index with name
      index =@ file.firstindex;                 // get the first index field
      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;

      //finding a record with id 
      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);
        if record =@= .nul
          log_handler.error_message("Did not find record with id '" + .tostr(index_id, 10) + "'. Error code: " + .tostr(errno, 10));
          
          // select first entry
          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

        // Found an entry -> display
        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
          
          // Get the next records with the same index
          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
        
        end if

      end if

      errno = 0

      // add record to database
      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";
      if record.save(error=errno) !@= .nul
        result = log_handler.error_message("Cannot add record to table. Error code: " + .tostr(errno, 10));
      else
        // add record a second time
        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

      // update record
      if errno == 0 and index_found == .true
        log_handler.info_message("Update first record with id '" + .tostr(index_id, 10) + "'");
      
        // record have to lock because we want to change it
        record =@ index.selectkey(index_id, lock=.true, error=errno);
        // this shall not happen because we add this entry
        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
          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
                      
        end if

      end if
      
      // show updated record
      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);
        // this shall not happen because we add this entry
        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
          log_handler.trace_message(" -> id: " + .tostr(record!id, 10) + "; first name: " + record!first_name + "; last name:  " + record!last_name);

          // Get the next records with the same index
          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

      end if

      // delete added records
      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);
        // this shall not happen because we add this entry
        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
          // check delete of entry to .nul
          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

          // Get the next records with the same index
          while
            record =@ record.select(lock=.true, error=errno)
            if record !@= .nul
            // check delete of entry to .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)");

        end if
      end if
    end if
  end if             
end function result