In the previous episode we created a database with a users table. But, the table was empty. Today, we’re going to write code to add some users, and then run a few queries..
Adding Users to the Database (INSERT)
I’ve created a function called addDefaultUsers(). It’s job is to add a default set of users if they don’t exist:
/** Adds the default set of users.
*
* @return bool true if successful, and false otherwise
*/
bool addDefaultUsers(sqlite3 *db) {
// NOTE: NOT setting password (nobody can log in yet)
bool success = true;
success = success && ensureUserExists(db, "andyb", "Andy", "Baker");
success = success && ensureUserExists(db, "jmurray", "John", "Murray");
success = success && ensureUserExists(db, "echen", "Emily", "Chen");
success = success && ensureUserExists(db, "hdr", "Hans", "de Ruiter");
success = success && ensureUserExists(db, "lthompson", "Liam", "Thompson");
success = success && ensureUserExists(db, "noahk", "Noah", "Reeves");
success = success && ensureUserExists(db, "izzyj", "Isabella", "Johnson");
success = success && ensureUserExists(db, "atanaka", "Alex", "Tanaka");
success = success && ensureUserExists(db, "chloem", "Chloe", "Martinez");
success = success && ensureUserExists(db, "edubois", "Emile", "Dubois");
success = success && ensureUserExists(db, "dantheman", "Daniel", "Manuel");
success = success && ensureUserExists(db, "jleo", "Jacob", "Leo");
return success;
}
This, in turn, calls ensureUserExists() for each user. It performs the actual insert operations as follows:
/** Adds a user to the database without a password.
*
* NOTE: If the user exists, then this will do nothing
*
* @param username the username
* @param firstName the user's first name
* @param familyName the user's family name
*
* @return bool true if successful, and false otherwise
*/
bool ensureUserExists(sqlite3 *db, const char *username, const char *firstName, const char *familyName) {
if(!db || !username || !firstName || !familyName) {
fprintf(stderr, "NULL pointer passed to %s\n", __func__);
return false;
}
// NOTE: NOT setting password (nobody can log in yet)
char sql[4096];
snprintf(sql, sizeof(sql), "INSERT INTO users(username, first_name, family_name) "
"VALUES('%s', '%s', '%s') "
"ON CONFLICT(username) DO NOTHING;", username, firstName, familyName);
char *errMsg = NULL;
int retCode = sqlite3_exec(db, sql, NULL, NULL, &errMsg);
if (retCode == SQLITE_OK) {
return true;
} else {
fprintf(stderr, "SQL error when adding a user: %s\n", errMsg);
sqlite3_free(errMsg);
return false;
}
}
The key line is the snprintf() in line 18. It constructs the INSERT command that “inserts” a user into the users table if it doesn’t exist. We’re not setting the user’s ID, because that’s generated automatically. And we’re also skipping the password hash creation, because that’s something that a user should do themselves.
The “ON CONFLICT(username) DO NOTHING” clause tells SQLite to do nothing if the username already exists. Otherwise we’d get “constraint” errors when running this a second time.
Querying the Database (SELECT)
Okay, we have data in the database. Now let’s pretend that we’re writing an app that needs to list all users in pages of 10. We need to use two different queries to do this.
First, we need to get the COUNT of all rows in the users table (i.e., the number of users):
SELECT COUNT(*) FROM users;
This is what it looks like in C code:
/** Callback to extract the result of a count operation.
*/
static int readCountFunc(void *data, int numCols, char **colVals, char **colNames) {
int *countPtr = (int*)data;
if(numCols > 0) {
*countPtr = atoi(colVals[0]);
}
return 0;
}
/** Returns the total number of users in the database.
*/
unsigned getTotalUsers(sqlite3 *db) {
const char *sql = "SELECT COUNT(*) FROM users;";
int totalUsers = 0;
char *errMsg = NULL;
int retCode = sqlite3_exec(db, sql, readCountFunc, &totalUsers, &errMsg);
if (retCode != SQLITE_OK) {
fprintf(stderr, "SQL error when querying user count: %s\n", errMsg);
sqlite3_free(errMsg);
return 0;
}
return totalUsers;
}
The query is executed in getTotalUsers(), but the count is extracted in the readCountFunc() callback. This is how SQLite’s sqlite3_exec() function works. It will call the callback function for every row returned by the query. With COUNT we only have one value, which is stored in the first column as a string. We convert this to an integer using atoi().
Next, we use the SELECT query to print the list of users in groups of 10. Here’s the SQL query that does everything:
SELECT * FROM users ORDER BY username ASC LIMIT 10 OFFSET <offset>;
This query sorts the users table alphabetically by username, in ASCending order. It then returns the 10 rows starting at <offset> (which is replaced with the actual offset to the target page).
Here’s what it looks like in C code:
typedef struct TableData {
int currentRow;
int columns;
int columnWidth;
} TableData;
/** Prints a separator row for a table with fixed column widths.
*/
static void printTableSeparator(const int columns, const int width) {
for (int i = 0; i < columns; ++i) {
printf("+");
for (int j = 0; j < width; ++j) {
printf("-");
}
}
printf("+\n");
}
/** Prints a single row of a table.
*/
static void printTableRow(const int columns, const int width, const char** itemNames) {
for (int i = 0; i < columns; ++i) {
printf("|%-*.*s", width, width, itemNames[i] ? itemNames[i] : "");
}
printf("|\n");
}
/** Callback to print a single table row
*/
int printTableRowFunc(void *data, int numCols, char **colVals, char **colNames) {
struct TableData* table = data;
if (table->currentRow == 0) {
table->columns = numCols;
printTableSeparator(numCols, table->columnWidth);
printTableRow(numCols, table->columnWidth, colNames);
printTableSeparator(numCols, table->columnWidth);
}
printTableRow(numCols, table->columnWidth, colVals);
table->currentRow++;
return 0;
}
/** Prints a set of users in a table.
*
* @param db pointer to the database containing the users table
* @param startIndex the offset to the first user to print
* @param count the number of users to print in the table
*/
void printUsers(sqlite3 *db, unsigned startIndex, unsigned count) {
char sql[4096];
snprintf(sql, sizeof(sql), "SELECT * FROM users "
"ORDER BY username ASC LIMIT %u OFFSET %u;", count, startIndex);
TableData tableData = {
.currentRow = 0,
.columns = 0,
.columnWidth = 20,
};
char *errMsg = NULL;
int retCode = sqlite3_exec(db, sql, printTableRowFunc, &tableData, &errMsg);
if (retCode != SQLITE_OK) {
fprintf(stderr, "SQL error when querying user count: %s\n", errMsg);
sqlite3_free(errMsg);
return;
}
printTableSeparator(tableData.columns, tableData.columnWidth);
}
Most of this code is to print the results in a nice table. Make sure you can identify the SQL query, and the code that prints the values for each column.
Finally, we need some code to call the functions above:
// Print all users
unsigned totalUsers = getTotalUsers(db);
printf("Have %u users\n\n", totalUsers);
const unsigned rowsPerPage = 10;
unsigned numPages = (totalUsers + rowsPerPage - 1) / rowsPerPage;
for(unsigned i = 0; i < numPages; ++i) {
printUsers(db, i * rowsPerPage, rowsPerPage);
printf("Page %u of %u\n\n", i + 1, numPages);
}
printf("\n");
When we run this, we get a nice list of users separated into two pages:
Have 12 users
+--------------------+--------------------+--------------------+--------------------+--------------------+
|ID |username |password_hash |first_name |family_name |
+--------------------+--------------------+--------------------+--------------------+--------------------+
|1 |andyb | |Andy |Baker |
|8 |atanaka | |Alex |Tanaka |
|9 |chloem | |Chloe |Martinez |
|11 |dantheman | |Daniel |Manuel |
|3 |echen | |Emily |Chen |
|10 |edubois | |Emile |Dubois |
|4 |hdr | |Hans |de Ruiter |
|7 |izzyj | |Isabella |Johnson |
|12 |jleo | |Jacob |Leo |
|2 |jmurray | |John |Murray |
+--------------------+--------------------+--------------------+--------------------+--------------------+
Page 1 of 2
+--------------------+--------------------+--------------------+--------------------+--------------------+
|ID |username |password_hash |first_name |family_name |
+--------------------+--------------------+--------------------+--------------------+--------------------+
|5 |lthompson | |Liam |Thompson |
|6 |noahk | |Noah |Reeves |
+--------------------+--------------------+--------------------+--------------------+--------------------+
Page 2 of 2
The SELECT query is very powerful. We could also sort by first or last names in ascending or descending order. Or, we could filter the table, and only get users whose name starts with Dan, or something else.
Danger Ahead!
However, there’s trouble brewing. If we continue writing code like this then we’re likely to make a potentially catastrophic mistake. I’ll explain why in the next video which should appear over here when it’s ready. See you then…
Download the Source Code
The source code (and much more) is available in the Kea Campus, to Creator & higher tier members.