Databáze SQLite v C++ na Linuxu - prepared statements.

30.10.2019

V předchozím článku na toto téma jsme si ukázali procházení dat SQlite databáze pomocí funkce sqlite3_exec a callback funkce. Funkce sqlite3_exec ve skutečnosti uvnitř zapouzdřuje volání funkcí, se kterými se seznámíme dnes a které při přímém použití dávají více pružnosti pokud jde o různé formáty dat (číselné, BLOB) a také pokud text v datech obsahuje různé "speciální" znaky.

Pro použití "prepared statements" potřebujeme kromě handle na otevřenou databázi (typ sqlite3*) mít deklarovaný ukazatel na typ sqlite3_stmt. Celý princip získání nebo přidání/editace dat new je následující: 1. Vytvoříme platný ukazatel na sqlite3_stmt. 2. Pokud jde o přidání nebo změnu dat, pomocí funkcí skupiny sqlite3_bind_* nastavíme parametry na konkrétní data. 3. Zavoláme funkci sqlite3_step a to jednou při přidání/změně dat nebo jednou nebo vícekrát pokud jde o získání dat či procházení všemi daty v tabulce. 4. Resetujeme objekt sqlite3_stmt funkcí sqlite3_reset. 5. Volitelně můžeme (opakovaně) pokračovat návratem na bod 2. 6. Uvolnímee objekt sqlite3_stmt funkcí sqlite3_finalize.

Nyní už konkrétní příklad. Hlavičkové soubory a pomocné souborové funkce budou stejné jako v předchozím článku.

#include <cstdio>
#include <stdexcept>
#include <unistd.h>
#include <pwd.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <assert.h>
#include <sqlite3.h>
	
sqlite3* _sqlite3 = nullptr;

bool directory_exists(const char* fs_path)	
{
	struct stat ss;
	if (stat(fs_path, &ss) != 0)
		return false;
	else
		return (ss.st_mode & S_IFMT) == S_IFDIR;
}

bool file_exists(const char* file_path)	
{
	struct stat ss;
	if (stat(file_path, &ss) != 0)
		return false;
	else
		return (ss.st_mode & S_IFMT) == S_IFREG;
}

std::string get_cesta_db()
{
	std::string db_file = getenv("HOME");
	if (!directory_exists(db_file.c_str()))
		db_file = getpwuid(getuid())->pw_dir;
	if (!directory_exists(db_file.c_str()))
		throw std::runtime_error("Nepodařilo se zjistit domovský adresář.");
	db_file.append("/Dokumenty/test-stmt.db");
	return db_file;
}

Dále si vytvoříme funkce pro vytvoření databáze včetně tabulky a otevření této databáze. Tabulka bude obsahovat 3 sloupce: celé číslo, text a čas. Protože SQLite nemá přímo vestavěný typ pro uložení data/času, typ pro uložení času bude INTEGER. Konkrétně tam budeme ukládat standardní počet sekund od začátku periody, tj. hodnotu vrácenou funkcí time. Ještě poznámka k sloupci nazvaném id definovaném jako "INTEGER PRIMARY KEY". Takto nadefinovaný sloupec je jak název napovídá primárním klíčem a současně autoinkrement, to znamená že při přidávání a změně dat jej ignorujeme a při přidání nového záznamu je nastaven na hodnotu o 1 větší než předchozí přidaný záznam.

void vytvorit_databazi()
{
	std::string str_file = get_cesta_db();
	if (file_exists(str_file.c_str()))
	{
		if (remove(str_file.c_str()) !=0)
			throw std::runtime_error("Chyba smazání souboru");
	}
	int ires = sqlite3_open(str_file.c_str(), &_sqlite3);
	if (ires != SQLITE_OK)
		throw std::runtime_error(sqlite3_errstr(ires));
	ires = sqlite3_exec(_sqlite3, "CREATE TABLE IF NOT EXISTS polozky (id INTEGER PRIMARY KEY,"
		" cislo INTEGER DEFAULT 0, nazev TEXT, cas INTEGER)", nullptr, nullptr, nullptr);
	if (ires != SQLITE_OK)
		throw std::runtime_error(sqlite3_errstr(ires));
}

void otevrit_databazi()
{
	std::string str_file = get_cesta_db();
	if (!file_exists(str_file.c_str()))
		throw std::runtime_error("Soubor databáze nebyl nalezen");
	int ires = sqlite3_open(str_file.c_str(), &_sqlite3);
	if (ires != SQLITE_OK)
		throw std::runtime_error(sqlite3_errstr(ires));
}

Nyní vytvoříme funkci které nám vygeneruje 10 záznamů. Číslo a text budou na základě náhodného čísla v rozsahu 1-100 a do času nastavíme aktuální hodnotu vrácenou funkcí time() v okamžiku volání. Abychom měli v každé položce jinou hodnotu času, dáme do každého kroku cyklu volání sleep na 1 sekundu.

void naplnit_nahodne(size_t pocet = 10)
{
	assert(_sqlite3);
	srand(time(nullptr));
	char sz_sql[255];
	char sz_text[255];
	int cislo;
	int ires;
	time_t cas;
	sqlite3_stmt* stmt = nullptr;
	strcpy(sz_sql, "INSERT INTO polozky (cislo, nazev, cas) VALUES (?,?,?)");
	int sqlres = sqlite3_prepare_v2(_sqlite3, sz_sql,
		static_cast(strlen(sz_sql)), &stmt, nullptr);
	if (ires != SQLITE_OK)
		throw std::runtime_error(sqlite3_errstr(ires));
	for (size_t i = 0; i < pocet; i++)
	{
		cislo = rand() % 100 + 1;
		sprintf(sz_text, "%d - %X", cislo, cislo);
		time(&cas);
		ires = sqlite3_bind_int64(stmt, 1, (sqlite3_int64)cislo);
		if (ires != SQLITE_OK)
			throw std::runtime_error(sqlite3_errstr(ires));
		ires = sqlite3_bind_text(stmt, 2, sz_text, (int)(strlen(sz_text)), SQLITE_STATIC);
		if (ires != SQLITE_OK)
			throw std::runtime_error(sqlite3_errstr(ires));
		ires = sqlite3_bind_int64(stmt, 3, (sqlite3_int64)cas);
		if (ires != SQLITE_OK)
			throw std::runtime_error(sqlite3_errstr(ires));
		ires = sqlite3_step(stmt);
		if ((sqlres != SQLITE_OK) && (sqlres != SQLITE_DONE))
			throw std::runtime_error(sqlite3_errstr(ires));
		ires = sqlite3_reset(stmt);
		if (ires != SQLITE_OK)
			throw std::runtime_error(sqlite3_errstr(ires));
		sleep(1);
	}
	ires = sqlite3_finalize(stmt);
	if (ires != SQLITE_OK)
		throw std::runtime_error(sqlite3_errstr(ires));
}

Jak je vidět z kódu, druhým parametrem skupiny funkcí sqlite3_bind_* je pořadí sloupce z jejich seznamu v SQL příkazu INSERT. Pozor! Opravdu jde o pořadí číslované od 1, tedy nikoliv "céčkovský index", začínající od nuly, jak jsme běžně zvyklí.

Nyní zbývá ukázat si výpis dat pomocí "prepared statement". Jak je vidět, na rozdíl od callback funkce nyní voláme v cyklu funkci sqlite3_step, která pracuje nad zadaným ukazatelem na objekt sqlite3_stmt a pokusí se přejít na následující řádku vrácenou SQL příkazem SELECT zadaným při vytvoření objektu sqlite3_step a v případě úspěchu vrátí hodnotu SQLITE_ROW. Pokud jsme již na konci a další řádek neexistuje, vrátí hodnotu SQLITE_DONE (101), která tedy neznamená chybu. Pokud dojde k nějaké jiné neočekávané chybě, vrátí hodnotu SQLITE_ERROR (1). Proto v případě jejího zachycení vyvoláme výjimku std::runtime_error.

void vypsat_data()
{
	assert(_sqlite3);
	sqlite3_stmt* stmt = nullptr;
	char sz_sql[255];
	strcpy(sz_sql, "SELECT cislo, nazev, cas FROM polozky");
	int ires = sqlite3_prepare_v2(_sqlite3, sz_sql,
		static_cast(strlen(sz_sql)), &stmt, nullptr);
	if (ires != SQLITE_OK)
		throw std::runtime_error(sqlite3_errstr(ires));
	int istep;
	char* ptext;
	sqlite3_int64 cislo;
	struct tm* timeinfo;
	time_t cas;
	char sz_cas[16];
	istep = sqlite3_step(stmt);
	while (SQLITE_ROW == istep)
	{
		cislo = sqlite3_column_int64(stmt, 0);
		ptext = (char*)sqlite3_column_text(stmt, 1);
		cas = (time_t)sqlite3_column_int64(stmt, 2);
		timeinfo = localtime(&cas);
		strftime(sz_cas, sizeof(sz_cas), "%H:%M:%S", timeinfo);
		printf("%d, %s, %s\n", (int)cislo, ptext, sz_cas);
		istep = sqlite3_step(stmt);
		if (SQLITE_ERROR == istep)
			throw std::runtime_error(sqlite3_errstr(istep));
	}
	ires = sqlite3_reset(stmt);
	if (ires != SQLITE_OK)
		throw std::runtime_error(sqlite3_errstr(ires));
	ires = sqlite3_finalize(stmt);
	if (ires != SQLITE_OK)
		throw std::runtime_error(sqlite3_errstr(ires));
}

Opět důležité upozornění: v případě funkcí skupiny sqlite3_column_* je druhým parametrem tentokrát (na rozdíl od funkcí sqlite3_bind_*) index číslovaný od nuly, jak je zvykem v C/C++.

Celý kód si můžete prohlédnou na mém githubu. Přeložit lze kompilátorem GCC příkazem:

g++ main.cpp -g -lsqlite3 -osqlite-cpp