In a previous post I introduced the libpq PostgreSQL C library and used it to create a database, a few tables and a view. In this post I will demonstrate inserting, updating, deleting and selecting data using the database created in the previous post.
The Project
This project builds on the previous one so assumes you have read it and run the code, and still have the database.
The source code for this project consists of the following files:
- cdpgconnection.h
- cdpgconnection.c
- gallery.h
- photo.h
- cdpgdml.h
- cdpgdml.c
- dmldemo.c
which can be downloaded as a zip or cloned/downloaded from Github. This project shares cdpgconnection.h and cdpgconnection.c with the previous post so you might like to save the files for this post in the same folder and share the previous files.
Source Code Links
Opening and Closing a Database Connection
The following two files are carried over from the previous project and provide a quick and easy way of opening and closing a database connection. They could be regarded as a stand-in for something more sophisticated such as a thread pool.
cdpgconnection.h
#include<libpq-fe.h> PGconn* CDPGget_connection(char* connstring); void CDPGclose_connection(PGconn*);
cdpgconnection.c
#include<libpq-fe.h> void CDPGclose_connection(PGconn* conn) { PQfinish(conn); } PGconn* CDPGget_connection(char* connstring) { PGconn *conn = PQconnectdb(connstring); if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "CONNECTION_BAD %s\n", PQerrorMessage(conn)); CDPGclose_connection(conn); return NULL; } else if (PQstatus(conn) == CONNECTION_OK) { puts("CONNECTION_OK\n"); return conn; } }
Data Transfer Objects for Galleries and Photos
This is the schema of the database created in the previous post.
The following two files contain structs which will be used for creating items to insert into the database. For that reason they do not contain members for the ID primary keys but in a future post I will expand the principles introduced in this one to create a full data access layer. For that I will need to expand these structs to include their primary keys.
gallery.h
typedef struct gallery { char name[64]; char description[256]; }gallery;
photo.h
typedef struct photo { int galleryid; char title[64]; char description[256]; char photographer[64]; char datetaken[11]; }photo;
The DML Functions
At the heart of this project are the five functions prototyped in cdpgdml.h, which together provide basic CRUD functionality for our simple database.
cdpgdml.h
#include<libpq-fe.h> void CDPGinsert_galleries(PGconn* conn); void CDPGinsert_photos(PGconn* conn); void CDPGupdate_photo(PGconn* conn); void CDPGdelete_photo(PGconn* conn); void CDPGquery_galleriesphotos(PGconn* conn);
Inserting Galleries
Now we can start to implement the core CRUD functions, starting with a couple to insert galleries.
cdpgdml.c part 1
#include<time.h> #include<string.h> #include<libpq-fe.h> #include"gallery.h" #include"photo.h" void CDPGinsert_photo(PGconn* conn, photo p); void CDPGinsert_gallery(PGconn* conn, gallery g); void printdata(PGresult*); void CDPGinsert_galleries(PGconn* conn) { gallery galleries[4]; galleries[0] = (gallery){.name = "Edinburgh", .description = "Photos of Edinburgh"}; galleries[1] = (gallery){.name = "Jersey", .description = "Photos of Jersey"}; galleries[2] = (gallery){.name = "Canterbury", .description = "Photos of Canterbury"}; galleries[3] = (gallery){.name = "Cambridge", .description = "Photos of Cambridge"}; for(int c = 0; c < 4; c++) { CDPGinsert_gallery(conn, galleries[c]); } } void CDPGinsert_gallery(PGconn* conn, gallery g) { char* sql = "INSERT INTO public.galleries(name, description) VALUES ($1, $2)"; const char* params[2]; params[0] = g.name; params[1] = g.description; PGresult* res = PQexecParams(conn, sql, 2, NULL, params, NULL, NULL, 0); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "%s\n", PQerrorMessage(conn)); } else { puts("gallery inserted"); } PQclear(res); }
Note that after the various #includes I have prototyped three functions for use internally.
The CDPGinsert_galleries function creates and populates an array of galleries which it then iterates, passing each to the next function, CDPGinsert_gallery.
The CDPGinsert_gallery follows the same general pattern seen in the post in creating databases. However, there is one very important difference: here we need to inject variables into the SQL representing the values to be inserted. This is done as follows:
- Use a "$" plus an index number as a placeholder for each value. These start at 1 rather than 0.
- Create a string (char*) array large enough for each value.
- Set each element of the array to its corresponding value. Here the values are strings already but later we will need to convert ints and dates to strings before adding them to the array.
- Call PQexecParams with a connection and string of SQL as before, but this time also pass the size of the params array as the third argument, and the params array itself as the fifth.*
*If you are curious about the unused arguments take a look at the libpq library's official documentation https://www.postgresql.org/docs/9.1/libpq-exec.html#LIBPQ-EXEC-MAIN
The rest of this code is familiar from the DDL post - we just check the result status and print the corresponding message.
Running the Program
We now have enough code to run but need a short file with a main function to call the functions in cdpgdml.c.
dmldemo.c
#include<stdio.h> #include<stdlib.h> #include<stdbool.h> #include"cdpgconnection.h" #include"cdpgdml.h" int main(int argc, char* argv[]) { puts("--------------------------"); puts("| codedrome.com |"); puts("| PostgreSQL with libpq |"); puts("| DML: Manipulating Data |"); puts("--------------------------\n"); PGconn* connpg = CDPGget_connection("user=xxx password=xxx dbname=codedrome"); PGresult* res; if(connpg != NULL) { CDPGinsert_galleries(connpg); // CDPGinsert_photos(connpg); // CDPGupdate_photo(connpg); // CDPGdelete_photo(connpg); // CDPGquery_galleriesphotos(connpg); CDPGclose_connection(connpg); } return EXIT_SUCCESS; }
Remember to change the user and password in the connection string to your own. Now compile and run the code:
Compile and run
gcc dmldemo.c cdpgconnection.c cdpgdml.c -std=c11 -I/usr/include/postgresql -lpq -o dmldemo ./dmldemo
If you open pgAdmin (or whatever your favourite DBA tool is) and view the data in the galleries table it should look like this.
Inserting Photos
The basic pattern for inserting photos is the same as with galleries: create an array of items and then iterate it, calling a separate function to insert each one.
cdpgdml.c part 2
void CDPGinsert_photos(PGconn* conn) { photo photos[6]; struct tm time; char datestring[11]; time = (struct tm){.tm_sec = 0, .tm_min = 0, .tm_hour = 0, .tm_mday = 22, .tm_mon = 7 - 1, .tm_year = 2019 - 1900}; strftime(datestring, 11, "%Y-%m-%d", &time); photos[0] = (photo){.galleryid = 1, .title = "Edinburgh 1", .description = "First photo of Edinburgh", .photographer = "Chris"}; strncpy(photos[0].datetaken, datestring, 11); time = (struct tm){.tm_sec = 0, .tm_min = 0, .tm_hour = 0, .tm_mday = 22, .tm_mon = 7 - 1, .tm_year = 2019 - 1900}; strftime(datestring, 11, "%Y-%m-%d", &time); photos[1] = (photo){.galleryid = 1, .title = "Edinburgh 2", .description = "Second photo of Edinburg", .photographer = "Chris"}; strncpy(photos[1].datetaken, datestring, 11); time = (struct tm){.tm_sec = 0, .tm_min = 0, .tm_hour = 0, .tm_mday = 15, .tm_mon = 7 - 1, .tm_year = 2019 - 1900}; strftime(datestring, 11, "%Y-%m-%d", &time); photos[2] = (photo){.galleryid = 3, .title = "Canterbury 1", .description = "First photo of Canterbury", .photographer = "Chris"}; strncpy(photos[2].datetaken, datestring, 11); time = (struct tm){.tm_sec = 0, .tm_min = 0, .tm_hour = 0, .tm_mday = 15, .tm_mon = 7 - 1, .tm_year = 2019 - 1900}; strftime(datestring, 11, "%Y-%m-%d", &time); photos[3] = (photo){.galleryid = 3, .title = "Canterbury 2", .description = "Second photo of Canterbury", .photographer = "Chris"}; strncpy(photos[3].datetaken, datestring, 11); time = (struct tm){.tm_sec = 0, .tm_min = 0, .tm_hour = 0, .tm_mday = 8, .tm_mon = 7 - 1, .tm_year = 2019 - 1900}; strftime(datestring, 11, "%Y-%m-%d", &time); photos[4] = (photo){.galleryid = 4, .title = "Cambridge 1", .description = "First photo of Cambridge", .photographer = "Chris"}; strncpy(photos[4].datetaken, datestring, 11); time = (struct tm){.tm_sec = 0, .tm_min = 0, .tm_hour = 0, .tm_mday = 8, .tm_mon = 7 - 1, .tm_year = 2019 - 1900}; strftime(datestring, 11, "%Y-%m-%d", &time); photos[5] = (photo){.galleryid = 4, .title = "Cambridge 2", .description = "Second photo of Cambridge", .photographer = "Chris"}; strncpy(photos[5].datetaken, datestring, 11); for(int c = 0; c < 6; c++) { CDPGinsert_photo(conn, photos[c]); } } void CDPGinsert_photo(PGconn* conn, photo p) { char* sql = "INSERT INTO public.photos(galleryid, title, description, photographer, datetaken) VALUES ($1, $2, $3, $4, $5)"; const char* params[5]; char galleryid[8]; snprintf(galleryid, 8, "%d", p.galleryid); params[0] = galleryid; params[1] = p.title; params[2] = p.description; params[3] = p.photographer; params[4] = p.datetaken; PGresult* res = PQexecParams(conn, sql, 5, NULL, params, NULL, NULL, 0); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "%s\n", PQerrorMessage(conn)); } else { puts("photo inserted"); } PQclear(res); }
Creating the list of photos is a bit more complex because as well as having more members they also include a date. I won't go into the date handling here but if you want to read up on C's date and time functionality take a look at my post Dates and Times in C.
The CDPGinsert_photo function is also slightly more complex because photos include an int, the galleryid. This needs to be converted to a string using snprintf before being added to the params array. After that the code is identical to that used for inserting galleries apart from the number of parameters.
In main comment out CDPGinsert_galleries, uncomment CDPGinsert_photos and compile/run the program again. Now take a look at the data in the photos table.
Updating Photos
You might have spotted a typo in the second row - Edinburgh is missing its "h". As you probably guessed I did this deliberately to provide an opportunity to demonstrated the U (update) bit of the CRUD functionality we are writing.
cdpgdml.c part 3
void CDPGupdate_photo(PGconn* conn) { char* sql = "UPDATE public.photos SET description=$1 WHERE photoid=$2"; const char* params[2]; params[0] = "Second photo of Edinburgh"; params[1] = "2"; PGresult* res = PQexecParams(conn, sql, 2, NULL, params, NULL, NULL, 0); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "%s\n", PQerrorMessage(conn)); } else { puts("photo updated"); } PQclear(res); }
There is nothing new here, just the same pattern and techniques used for inserting galleries and photos. I have hard coded the values but of course in a real world solution they would be function arguments.
In main comment out CDPGinsert_photos, uncomment CDPGupdate_photo, and build/run. The data now looks like this.
Deleting Photos
Now let's move on to the CDPGdelete_photo function.
cdpgdml.c part 4
void CDPGdelete_photo(PGconn* conn) { char* sql = "DELETE FROM public.photos WHERE photoid=$1"; const char* params[1]; params[0] = "2"; PGresult* res = PQexecParams(conn, sql, 1, NULL, params, NULL, NULL, 0); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "%s\n", PQerrorMessage(conn)); } else { puts("photo deleted"); } PQclear(res); }
Again nothing new here so in main comment out CDPGupdate_photo and uncomment CDPGdelete_photo, then build and run. The photos table now looks like this.
Querying Data
As well as the galleries and photos tables created in the DDL post the database includes a view called galleriesphotos. This joins the two tables and returns data from each. The definition of the view is:
galleriesphotos View
SELECT galleries.name AS galleryname, galleries.description AS gallerydescription, photos.title AS phototitle, photos.description AS photodescription FROM galleries LEFT JOIN photos ON photos.galleryid = galleries.galleryid
Just as a reminder, the LEFT JOIN means all rows of the left table, galleries, will be returned even if they have no photos. In this case the photo data will be null.
You can see this if you open the galleriesphotos view in pgAdmin. The Jersey gallery has no photos but it is still included with blanks for phototitle and photodescription. (As a side comment I would prefer pgAdmin to show null rather than blanks as they are indistinguishable visually from empty strings.)
To finish off this project let's write some code to read the data from this view and display it in the terminal.
cdpgdml.c part 5
void CDPGquery_galleriesphotos(PGconn* conn) { char* sql = "SELECT galleryname, gallerydescription, phototitle, photodescription FROM public.galleriesphotos"; PGresult* res = PQexec(conn, sql); printdata(res); PQclear(res); } void printdata(PGresult* res) { if (PQresultStatus(res) != PGRES_TUPLES_OK) { printf("No data\n"); } else { int ncols = PQnfields(res); int nrows = PQntuples(res); for(int r = 0; r < nrows; r++) { for (int c = 0; c < ncols; c++) { char* colname = PQfname(res, c); printf("%s: ", colname); printf("%s\n", PQgetvalue(res, r, c)); } puts(""); } } }
The CDPGquery_galleriesphotos is very straightforward - it just throws a hard-coded bit of SQL to PQexec and passes the PGresult to printdata. I have omitted error checking here as it is done in printdata.
A PGresult doesn't just contain data, it also carries metadata around with it. This means we can write a general-purpose function which will take any PGresult and print its data complete with column names without any external knowledge of the data's structure. I have done just that with the printdata function.
In printdata, after checking for errors, we pick up the column and row counts using PQnfields and PQntuples respectively. These are then used in nested for loops to iterate the rows and columns. Inside the inner loop we retrieve and the column name and the corresponding data value.
In main comment out CDPGdelete_photo, uncomment CDPGquery_galleriesphotos, then do the build-and-run thing one last time. This is the output showing the same data as the screenshot above.
Program output
-------------------------- | codedrome.com | | PostgreSQL with libpq | | DML: Manipulating Data | -------------------------- CONNECTION_OK galleryname: Edinburgh gallerydescription: Photos of Edinburgh phototitle: Edinburgh 1 photodescription: First photo of Edinburgh galleryname: Canterbury gallerydescription: Photos of Canterbury phototitle: Canterbury 1 photodescription: First photo of Canterbury galleryname: Canterbury gallerydescription: Photos of Canterbury phototitle: Canterbury 2 photodescription: Second photo of Canterbury galleryname: Cambridge gallerydescription: Photos of Cambridge phototitle: Cambridge 1 photodescription: First photo of Cambridge galleryname: Cambridge gallerydescription: Photos of Cambridge phototitle: Cambridge 2 photodescription: Second photo of Cambridge galleryname: Jersey gallerydescription: Photos of Jersey phototitle: photodescription:
With this and the previous post I have covered all the basics of working with PostgreSQL in C with the libpq library: we can now create a database, add tables, and insert, update, delete and query data.
There are of course still plenty of more advanced areas to cover, and in the future I will write about reading database schemas, writing full data access layers and various other topics.