product database using sqlite3

database.c

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>

#define DATABASE_PATH "products.db"

#define MENU_LIST_PRODUCTS 1
#define MENU_ADD_PRODUCT 2
#define MENU_DELETE_PRODUCT 3
#define MENU_UPDATE_PRODUCT 4
#define MENU_LOWEST_PRICED 5
#define MENU_HIGHEST_PRICED 6
#define MENU_PRICE_SUM 7
#define MENU_QUIT 8

const char *menu =
"1 list all products\n"
"2 add product\n"
"3 delete product\n"
"4 update product\n"
"5 lowest priced\n"
"6 highest priced\n"
"7 sum prices\n"
"8 quit\n";

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    printf("%4s  %7.2f  %s\n", argv[0], strtol(argv[2], NULL, 10)/100.0, argv[1]);
    return 0;
}

void add_product(sqlite3 *db) {
    char description[100];
    printf("description: ");
    fgets(description, 100, stdin);
    description[strlen(description)-1]='\0';
    
    char price_text[16];
    printf("price in cents: ");
    unsigned long price;
    fgets(price_text, 16, stdin);
    price = strtol(price_text, NULL, 10);
    
    sqlite3_stmt* stmt;
    const char *insert_statement = "INSERT INTO products (description, price) VALUES(?, ?);";
    int ret = sqlite3_prepare_v2(db, insert_statement, strlen(insert_statement), &stmt, NULL);
    if(ret != SQLITE_OK) {
        printf("ERROR, SQLITE INSERT %d\n", ret);
    }
    
    sqlite3_bind_text(stmt, 1, description, strlen(description), SQLITE_STATIC);
    sqlite3_bind_int(stmt, 2, price);
    
    ret = sqlite3_step(stmt);
    if(ret != SQLITE_DONE) {
        printf("ERROR,SQLITE INSERT %d\n", ret);
    }
    
    printf("inserted new product\n");
    sqlite3_finalize(stmt);
    
}

void update_product(sqlite3 *db) {
    sqlite3_stmt* stmt;
    int rc;
    char *zErrMsg = 0;
    
    printf("product id: ");
    char product_id_text[8];
    fgets(product_id_text, 8, stdin);
    int product_id = strtol(product_id_text, NULL, 10);

    char description[100];
    printf("description: ");
    fgets(description, 100, stdin);
    description[strlen(description)-1]='\0';
    
    char price_text[16];
    printf("price in cents: ");
    unsigned long price;
    fgets(price_text, 16, stdin);
    price = strtol(price_text, NULL, 10);
    
    const char *insert_statement = "UPDATE products SET description=?, price=? WHERE id=?;";
    int ret = sqlite3_prepare_v2(db, insert_statement, strlen(insert_statement), &stmt, NULL);
    if(ret != SQLITE_OK) {
        printf("ERROR, SQLITE INSERT %d\n", ret);
    }
    
    sqlite3_bind_text(stmt, 1, description, strlen(description), SQLITE_STATIC);
    sqlite3_bind_int(stmt, 2, price);
    sqlite3_bind_int(stmt, 3, product_id);
    
    ret = sqlite3_step(stmt);
    if(ret != SQLITE_DONE) {
        printf("ERROR,SQLITE UPDATE %d\n", ret);
    }
    
    printf("updated product\n");
    sqlite3_finalize(stmt);
    
}

void list_products(sqlite3 *db, char *sql) {
    int rc;
    char *zErrMsg = 0;
    
    printf("id    price    description\n");
    printf("----  -------  -----------\n");
    
    rc = sqlite3_exec(db, sql, callback, NULL, &zErrMsg);
    if( rc != SQLITE_OK ){
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
}

void delete_product(sqlite3 *db){
    sqlite3_stmt* stmt;
    int rc;
    char *zErrMsg = 0;
    const char *sql = "DELETE FROM products WHERE id=?;";
    
    printf("product id: ");
    char product_id_text[8];
    fgets(product_id_text, 8, stdin);
    int product_id = strtol(product_id_text, NULL, 10);
    
    int ret = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
    sqlite3_bind_int(stmt, 1, product_id);
    
    ret = sqlite3_step(stmt);
    if(ret != SQLITE_DONE) {
        printf("ERROR,SQLITE DELETE %d\n", ret);
    }
    
    sqlite3_finalize(stmt);
}

int main(int argc, char* argv[]) {
    sqlite3 *db;
    char *zErrMsg = 0;
    int  rc;
    const char *sql;
    int menu_selection;
    char selection_text[16];

    rc = sqlite3_open(DATABASE_PATH, &db);
    if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
    }

    sql = "CREATE TABLE IF NOT EXISTS products("
         "id INTEGER PRIMARY KEY AUTOINCREMENT,"
         "description VARCHAR(100),"
         "price INTEGER);";
    
    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
    if( rc != SQLITE_OK ){
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
    }

    
    while(menu_selection!=8){
        printf("\n%s", menu);
        printf("selection: ");
        fgets(selection_text, 16, stdin);
        menu_selection = strtol(selection_text, NULL, 10);
        
        switch (menu_selection) {
            case MENU_LIST_PRODUCTS:
                list_products(db, "SELECT * FROM products ORDER BY id;");
                break;
            case MENU_ADD_PRODUCT:
                add_product(db);
                break;
            case MENU_DELETE_PRODUCT:
                delete_product(db);
                break;
            case MENU_UPDATE_PRODUCT:
                update_product(db);
                break;
            case MENU_LOWEST_PRICED:
                list_products(db, "SELECT * FROM products ORDER BY price ASC LIMIT 1;");
                break;
            case MENU_HIGHEST_PRICED:
                list_products(db, "SELECT * FROM products ORDER BY price DESC LIMIT 1;");
                break;
            case MENU_PRICE_SUM:
                list_products(db, "SELECT 0,NULL,sum(price) FROM products;");
                break;
            case MENU_QUIT:
                break;
            default:
                printf("invalid selection\n");
        }
    }

    sqlite3_close(db);
    return 0;
}

products.db