Assignment 8 — Application Programming

The goal of this assignment is to gain experience with:

As with previous assignments, I am providing some code in an archive, and you need to fill in the interesting pieces.

1) Members, Messages, and Routing — again

You will be working with the same schema as in Assignment 5. There are member, message, and routing tables. In fact, the supplied data is the same as for Assignment 5. You will use these tables to write queries and updates in SQL, under the control of a C++ program. You will also need to write a stored procedure, invoked from one of your SQL queries.

What's in the archive

Header files

There are two header files:

Mostly empty .cpp files

Corresponding to each .h file is a .cpp file. You need to implement the empty functions in each. For example, in db.cpp you will find an empty query function, which submits a SQL query to the database. You need to fill in this function, taking care to implement the behavior specified in the comment in db.h.

main.cpp

This is the test driver. It uses the code that you will provide to:

Makefile

You can build your code using this Makefile by simply typing make. Doing so will create an executable named a8.

Data files

Three data files are provided:

The test driver will read these files, and generate calls to the functions add_member, add_message, and add_routing, (declared in msg.h).

sp.sql

This contains an empty stored procedure, that you need to fill in, as described later.

Schema

create table member( member_id serial primary key, name varchar not null, birth_date date); create table message( message_id serial primary key, message_date date not null, message_text varchar not null); create table routing( from_member_id int not null references member, to_member_id int not null references member, message_id int not null references message);

Running the test driver

Before running the driver, be sure to load your stored procedure into the database:
psql -h HOST -U USERNAME < sp.sql

You should do this once you unpack the archive, and each time that you modify sp.sql.

To run the test driver, run this command:
./a8 HOST DBNAME USER PASSWORD

You can compile the provided code, but the executbale will crash until you implement your part of the code.

The code you need to write

In db.cpp

All database access will be done through the functions that you write here. You will need to use the libpq functions we covered in class.

Pay very close attention to the failure cases, as described in the comments in db.h. If you don't obtain and handle the execution codes resulting from SQL commands (both queries and udpates), then your return values will be wrong, the test driver will misbehave, and you will not be able to complete this assignment.

In msg.cpp

Most of these functions are pretty straightforward, and will rely on the database interface you've implemented, (i.e., the functions of db.h).

add_member, add_message, and add_routing are for populating the database. birth_date and senders_and_receivers are for testing queries. (In fact, the test driver uses these to implement queries q1 and q7 from Assignment 5.

add_member, add_message

The member and message tables have primary key fields of type serial. This means that the database system provides the column's value, and the INSERT statement should not. I've provided the insert statements, see INSERT_MEMBER and INSERT_MESSAGE in msg.cpp.

One problem with this approach is that you don't know the member_id and message_id values that the database created for you. And the add_member and add_message functions need to return these values. You can obtain these values using by calling select lastval(). lastval() is a builtin (to Postgres) function that gives you the value of the last serial value generated on your database connection. So if you execute an insert statement, which inserts a row in a table with a serial column, (e.g. member, message), then select lastval() will obtain the value of the primary key column that was just inserted. select lastval() is in the PK_VALUE string in msg.cpp. You need to write the invocation of this piece of SQL, in the add_member and add_message functions.

add_routing

This is a bit different from add_member and add_message. The last argument to add_routing is a string containing a list of message ids. For example, if the driver calls add_routing(5, 19, "{109,234,567}") it means that member 5 has sent to member 19 three messages, whose ids are 198, 234, and 567. I.e., this call will result in three rows being added to the routing table: (5, 19, 109), (5, 19, 234), (5, 19, 567).

These multiple insertions to routing will be done by a single call to the stored procedure store_routing. The SQL invoking this stored procedure is in the variable INSERT_ROUTING in msg.cpp You need to invoke this from the add_routing function.

The stored procedure

You need to fill in the store_routing stored procedure, which can be found in sp.sql. Note that the store_routing function takes three arguments:

Fortunately, the message_ids format, {109,234,567} happens to be exactly the integer array literal syntax understood by Postgres. I.e., you can pass message_ids string to the message_ids argument of store_routing stored procedure.

You need to fill in the body of store_routing. It is a simple, short procedure. I expect that you will need to do some research in the Postgres documentation on how to work with PL/pgSQL arrays. Note that the return value from this function is the number of rows inserted.

Test of message API

This runs a self-test of the message API, which is in msg.h. It reports the number of errors found. The test starts in test_msg in main.cpp.

Debugging stored procedures

Debugging stored procedures is a little tricky, since you don't have easy access to the postgres process in which the stored procedures executes, (e.g. using gdb). Your main tool for debugging is to print data from the stored procedure itself. Output will appear on the console from which you run your code.

To do this, use the raise notice command inside your stored procedure:
raise notice 'YOUR MESSAGE HERE';

Note on Valgrind

libssl problems

Valgrind and libssl (which is used by libpq) seem to not get along, at least on my Linux machine. I get this terrifying error message running my implementation of a8:
vex: the `impossible' happened: isZeroU vex storage: T total 586842232 bytes allocated vex storage: P total 640 bytes allocated valgrind: the 'impossible' happened: LibVEX called failure_exit().

After much googling, I discovered that setting an SSL-related environment variable fixes the problem: export OPENSSL_ia32cap=0x00000000

Needless to say, this won't do anything about valgrind errors that are actually bugs in your code.

libcrypto problems

After you do the above, you will still probably see some errors that originate in libcrypto, e.g. ==16619== Use of uninitialised value of size 8 ==16619== at 0x4BA93DC: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4BA95DA: AES_encrypt (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4C911DC: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4CA01C2: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4CD3B46: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4CD3B8C: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4CD3C5A: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4CD4027: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4CD44FD: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4CD4AD8: RAND_DRBG_instantiate (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4CD59BA: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1) ==16619== by 0x4CD5A56: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.1.1)

I don't see how to get rid of these without also getting rid of checks that you do want. So just ignore these if you see them.

What to submit

Put these files in a zip file and submit: