/*
* File Name -- projectdb.c
* Programmer -- Robert S Laramee
*
* Description -- This program is for the programming project in
* CS775/875. It is the C program which populates
* a sample database for querying with the
* project.c application.
*
* Date Created -- 11 November, 1997
* Date "Completed" -- 10 December, 1997
*/
#include <stdio.h>
#include "libpq-fe.h"
PGconn *connection; /* global variable to reference connection */
void exit_nicely ();
void error (PGresult*, char*);
int main()
{
/* set up postrgres system variables to use postgres defaults */
char *pghost = NULL; /* host name of the backend server */
char *pgport = NULL; /* port of the backend server */
char *pgoptions = NULL; /* options to start up backendserver */
char *pgtty = NULL; /* debugging tty for the backend server */
char *dbname = "rlarameedb11";
/* char dbname[25]; */ /* database name needs to be read in */
PGresult *result;
char* space = " "; /* use as field separator to display tables */
int i, j, nfields, ntuples;
char query[250];
/* What's the name of the database the user would like to work with? */
/* printf("\nPlease enter a database name: ");
scanf("%s", dbname);
printf("\nWorking with the %s database.\n", dbname); */
/* establish the connection */
connection = PQsetdb (pghost, pgport, pgoptions, pgtty, dbname);
if (PQstatus (connection) == CONNECTION_BAD)
error (result, "connection to database failed");
/* -------------------- Set up the Employee table ----------------------*/
printf("\nSetting up the Employee table...");
sprintf(query, "CREATE TABLE Employee (ssn int4, fname char16, lname
char16, title char16, extension
int4, address char16, city char16,
state char16, gender char16,
startMonth int4, startDay int4,
startYear int4);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE Employee command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Employee VALUES (111111111, 'wanda',
'washington', 'salesperson', 11111, '1 washer way',
'Durham', 'NH', 'f', 5, 5, 1995);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "1st INSERT Employee command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Employee VALUES (222222222, 'jeff',
'jefferson', 'buyer', 22222, '2 joshua blvd',
'Dover', 'NH', 'm', 1, 4, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "2nd INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Employee VALUES (333333333, 'louie',
'lincoln', 'manager', 33333, '3 liberty ave',
'Portsmouth', 'NH', 'm', 10, 10, 1995);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "3rd INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Employee VALUES (444444444, 'toby',
'tyler', 'manager', 44444, '4 toes St',
'Boston', 'MA', 'm', 12, 12, 1995);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "4th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Employee VALUES (555555555, 'molly',
'monroe', 'salesperson', 55555, '5 miller ave',
'Newport', 'ME', 'f', 12, 12, 1995);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "5th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Employee VALUES (666666666, 'misty',
'madison', 'buyer', 66666, '6 market ct', 'Malborough',
'MA', 'f', 1, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "6th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Employee VALUES (777777777, 'alice',
'adams', 'salesperson', 77777, '7 avery ave',
'Andover', 'MA', 'f', 5, 20, 1994);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "7th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Employee VALUES (888888888, 'bruce',
'buchanon', 'manager', 88888, '8 bay rd', 'Boston',
'MA', 'm', 4, 14, 1995);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "8th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Employee VALUES (999999999, 'polly',
'pierce', 'buyer', 99999, '9 pentagon st',
'Portsmouth', 'NH', 'f', 6, 16, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "9th INSERT command failed\n");
}
PQclear(result);
printf("Employee table done.\n");
sprintf(query, "SELECT * FROM Employee;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT Employee command failed\n");
}
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
for (i=0; i < PQnfields(result); i++) {
printf("%s ", PQfname(result, i));
}
printf("\n----------------------------------------------------\n");
for (i=0; i < PQntuples(result); i++) {
for (j=0; j < PQnfields(result); j++) {
printf("%s ", PQgetvalue(result, i, j));
}
printf("\n");
}
PQclear(result);
/* -------------------- Set up the ExpenseReport table -------------------*/
printf("\nSetting up the Expense Report table...");
sprintf(query, "CREATE TABLE ExpenseReport(reportID int4, ssn int4,
reportName char16, monthSubmitted int4,
daySubmitted int4, yearSubmitted int4,
advanceAmount float4, deptCharged char16,
paid char16);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE ExpenseReport command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(1, 111111111, 'Sales Trip', 1, 25, 1996, 100.0, 'marketing',
'no');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "1st INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(2, 111111111, 'Sales Trip', 1, 25, 1996, 100.0, 'marketing',
'no');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "2nd INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(3, 111111111, 'Sales Trip', 1, 22, 1996, 100.0, 'marketing',
'no');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "3rd INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(4, 222222222, 'Business Trip', 5, 1, 1996, 200.0, 'transportation',
'no');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "4th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(5, 333333333, 'Press Tour', 5, 25, 1996, 300.0, 'engineering',
'yes');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "5th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(6, 333333333, 'Press Tour', 6, 20, 1996, 300.0, 'engineering',
'yes');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "5th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(7, 444444444, 'Business Trip', 7, 20, 1996, 400.0, 'transportation',
'no');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "6th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(8, 555555555, 'Press Tour', 8, 16, 1996, 500.0, 'marketing',
'yes');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "10th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(9, 555555555, 'Business Trip', 10, 30, 1996, 500.0, 'engineering',
'no');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "11th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(10, 666666666, 'Business Trip', 10, 30, 1996, 600.0, 'engineering',
'no');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "12th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(11, 777777777, 'Sales Trip', 11, 29, 1996, 700.0, 'marketing',
'yes');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "13th command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseReport VALUES
(12, 777777777, 'Business Trip', 12, 20, 1996, 700.0, 'engineering',
'no');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "14th command failed\n");
}
PQclear(result);
printf("Expense Report table done.\n");
sprintf(query, "SELECT * FROM ExpenseReport;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT * FROM ExpenseReport command failed\n");
}
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
for (i=0; i < PQnfields(result); i++) {
printf("%s ", PQfname(result, i));
}
printf("\n----------------------------------------------------\n");
for (i=0; i < PQntuples(result); i++) {
for (j=0; j < PQnfields(result); j++) {
printf("%s ", PQgetvalue(result, i, j));
}
printf("\n");
}
PQclear(result);
/* -------------------- Set up the ExpenseDetail table -------------------*/
printf("\nSetting up the Expense Detail table...");
sprintf(query, "CREATE TABLE ExpenseDetail(detailID int4, reportID
int4, categoryID int4, expenseAmount float4,
expenseMonth int4, expenseDay int4, expenseYear int4);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE ExpenseDetail command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (1, 1, 1, 100.0,
1, 10, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "1st INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (2, 1, 2, 100.0,
1, 10, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "2nd INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (3, 1, 3, 300.0,
1, 10, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "3rd INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (4, 1, 4, 50.0,
1, 10, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "4th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (5, 1, 5, 40.0,
1, 10, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "5th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (6, 2, 1, 50.0,
1, 18, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "6th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (7, 2, 3, 100.0,
1, 18, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "7th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (8, 3, 1, 50.0,
1, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "8th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (9, 3, 3, 200.0,
1, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "9th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (10, 4, 1, 50.0,
4, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "10th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (11, 4, 3, 200.0,
4, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "11th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (12, 5, 1, 60.0,
5, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "12th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (13, 6, 1, 30.0,
6, 10, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "13th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (14, 6, 3, 360.0,
6, 10, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "14th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (15, 7, 1, 20.0,
7, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "15th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (16, 8, 1, 40.0,
8, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "16th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (17, 8, 5, 40.0,
8, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "17th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (18, 9, 2, 50.0,
9, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "18th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (19, 9, 5, 10.0,
9, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "19th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (20, 10, 1, 70.0,
10, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "20th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (21, 10, 2, 130.0,
10, 30, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "21st INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (22, 11, 2, 100.0,
11, 25, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "22nd INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (23, 11, 3, 990.0,
11, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "23rd INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (24, 12, 1, 50.0,
12, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "24th INSERT command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseDetail VALUES (25, 12, 2, 500.0,
12, 15, 1996);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "25th INSERT command failed\n");
}
PQclear(result);
printf("Expense Detail table done.\n");
sprintf(query, "SELECT * FROM ExpenseDetail;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT * FROM ExpenseDetail command failed\n");
}
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
for (i=0; i < PQnfields(result); i++) {
printf("%s ", PQfname(result, i));
}
printf("\n----------------------------------------------------\n");
for (i=0; i < PQntuples(result); i++) {
for (j=0; j < PQnfields(result); j++) {
printf("%s ", PQgetvalue(result, i, j));
}
printf("\n");
}
/* -------------------- Set up the ExpenseCategory table -------------------*/
printf("\nSetting up the Expense Category table...");
sprintf(query, "CREATE TABLE ExpenseCategory(categoryID int4, category
char16);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "SOME command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseCategory VALUES
(1, 'meals');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "SOME command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseCategory VALUES
(2, 'transportation');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "SOME command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseCategory VALUES
(3, 'lodging');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "SOME command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseCategory VALUES
(4, 'entertainment');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "SOME command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO ExpenseCategory VALUES
(5, 'miscellaneous');");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "SOME command failed\n");
}
PQclear(result);
printf("Expense Category table done.\n\n");
sprintf(query, "SELECT * FROM ExpenseCategory;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT * FROM ExpenseCategory command failed\n");
}
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
for (i=0; i < PQnfields(result); i++) {
printf("%s ", PQfname(result, i));
}
printf("\n------------------------------\n");
for (i=0; i < PQntuples(result); i++) {
for (j=0; j < PQnfields(result); j++) {
printf("%s ", PQgetvalue(result, i, j));
}
printf("\n");
}
PQclear(result);
/* close connection to database */
exit_nicely (1);
}
/*
* Procedure: error
* Description: print error message, release query result, and terminate
* program using postgres functions PQprintMessage and PQclear
*
* Input: result -- postgres query result
* message -- print string
*
* Side Effects: alters stdout output stream
* invokes exit_nicely to quit, does not return to caller,
*/
void error (PGresult* result, char* message)
{
fprintf (stderr, "%s", PQerrorMessage(connection));
fprintf (stderr, "%s", message);
PQclear (result);
exit_nicely ();
}
/*
* Procedure: exit_nicely
* Description: disconnects from postgres database using
* postgres function PQfinish
*
* Input: global variable connection
*
* Side Effects: alters global variable conn
*/
void exit_nicely()
{
PQfinish(connection);
exit(1);
}