meutel
/
bouquins-bchs
1
0
Fork 0
Code Issues Pull Requests Releases Wiki Activity
bouquins-bchs/db_book.c

575 lines
15 KiB
C
Raw Permalink Blame History

/* $Id$ */
/*
* Copyright (c) 2016 Kristaps Dzonsons <kristaps@bsd.lv>
*
* Permission to use, copy, modify, and distribute this software for any
* purpose with or without fee is hereby granted, provided that the above
* copyright notice and this permission notice appear in all copies.
*
* THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
* WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
* MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
* ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
* WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
* ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
* OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
*/
#include <stdarg.h>
#include <stdint.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <unistd.h>
#include <kcgi.h>
#include <kcgijson.h>
#include "ksql.h"
#include "extern.h"
#define STMT_PAGE " LIMIT ? OFFSET ?"
#define STMT_BOOKS0 "SELECT books.id AS id,title,series_index,name as series_name,series.id AS series_id "\
"FROM books LEFT OUTER JOIN books_series_link ON books.id = books_series_link.book "\
"LEFT OUTER JOIN series ON series.id = books_series_link.series "
#define STMT_BOOKS_TAGS0 "SELECT name, books_tags_link.book as book FROM tags, books_tags_link "\
"WHERE tags.id = books_tags_link.tag AND books_tags_link.book IN ( SELECT id FROM books "
#define STMT_BOOKS_AUTHORS0 "SELECT authors.id, authors.name, books_authors_link.book as book "\
"FROM authors, books_authors_link WHERE books_authors_link.author = authors.id "\
"AND books_authors_link.book IN ( SELECT id FROM books "
#define STMT_WHERE " WHERE "
#define STMT_SEARCH_TERM " books.sort like ? "
#define STMT_BOOL_AND " AND "
#define STMT_BOOL_OR " OR "
#define STMT_SEARCH_ORDER " ORDER BY books.sort"
enum stmt {
STMT_BOOKS_COUNT,
STMT_BOOK,
STMT_BOOK_TAGS,
STMT_BOOK_AUTHORS,
STMT_BOOK_DATA,
STMT_BOOKS_ID_ASC,
STMT_BOOKS_ID_DESC,
STMT_BOOKS_TITLE_ASC,
STMT_BOOKS_TITLE_DESC,
STMT_BOOKS_TAGS_ID_ASC,
STMT_BOOKS_TAGS_ID_DESC,
STMT_BOOKS_TAGS_TITLE_ASC,
STMT_BOOKS_TAGS_TITLE_DESC,
STMT_BOOKS_AUTHORS_ID_ASC,
STMT_BOOKS_AUTHORS_ID_DESC,
STMT_BOOKS_AUTHORS_TITLE_ASC,
STMT_BOOKS_AUTHORS_TITLE_DESC,
STMT__MAX
};
static const char *const stmts[STMT__MAX] = {
/* STMT_BOOKS_COUNT */
"SELECT count(id) FROM books",
/* STMT_BOOK */
"SELECT books.id AS id,title, series_index, series.name AS series_name, series.id AS series_id, \
strftime('%s', timestamp), strftime('%Y', pubdate), isbn,lccn,path,uuid,has_cover, \
languages.lang_code, publishers.name AS pubname FROM books \
LEFT OUTER JOIN books_languages_link ON books_languages_link.book = books.id \
LEFT OUTER JOIN languages ON languages.id = books_languages_link.lang_code \
LEFT OUTER JOIN data ON data.book = books.id \
LEFT OUTER JOIN books_series_link ON books.id = books_series_link.book \
LEFT OUTER JOIN series ON series.id = books_series_link.series \
LEFT OUTER JOIN books_publishers_link ON books.id = books_publishers_link.book \
LEFT OUTER JOIN publishers ON publishers.id = books_publishers_link.publisher \
WHERE books.id = ?",
/* STMT_BOOK_TAGS */
"SELECT name FROM tags, books_tags_link WHERE tags.id = books_tags_link.tag AND books_tags_link.book = ?",
/* STMT_BOOK_AUTHORS */
"SELECT authors.id, authors.name, books_authors_link.book as book \
FROM authors, books_authors_link WHERE books_authors_link.author = authors.id \
AND books_authors_link.book = ?",
/* STMT_BOOK_DATA */
"SELECT data.name, data.format, data.uncompressed_size \
FROM data WHERE data.book = ?",
/* STMT_BOOKS_ID_ASC */
STMT_BOOKS0 " ORDER BY id" STMT_PAGE,
/* STMT_BOOKS_ID_DESC */
STMT_BOOKS0 "ORDER BY id DESC" STMT_PAGE,
/* STMT_BOOKS_TITLE_ASC */
STMT_BOOKS0 "ORDER BY books.sort" STMT_PAGE,
/* STMT_BOOKS_TITLE_DESC */
STMT_BOOKS0 "ORDER BY books.sort DESC" STMT_PAGE,
/* STMT_BOOKS_TAGS_ID_ASC */
STMT_BOOKS_TAGS0 "ORDER BY id" STMT_PAGE ")",
/* STMT_BOOKS_TAGS_ID_DESC */
STMT_BOOKS_TAGS0 "ORDER BY id DESC" STMT_PAGE ")",
/* STMT_BOOKS_TAGS_TITLE_ASC */
STMT_BOOKS_TAGS0 "ORDER BY books.sort" STMT_PAGE ")",
/* STMT_BOOKS_TAGS_TITLE_DESC */
STMT_BOOKS_TAGS0 "ORDER BY books.sort DESC" STMT_PAGE ")",
/* STMT_BOOKS_AUTHORS_ID_ASC */
STMT_BOOKS_AUTHORS0 "ORDER BY id" STMT_PAGE ")",
/* STMT_BOOKS_AUTHORS_ID_DESC */
STMT_BOOKS_AUTHORS0 "ORDER BY id DESC" STMT_PAGE ")",
/* STMT_BOOKS_AUTHORS_TITLE_ASC */
STMT_BOOKS_AUTHORS0 "ORDER BY books.sort" STMT_PAGE ")",
/* STMT_BOOKS_AUTHORS_TITLE_DESC */
STMT_BOOKS_AUTHORS0 "ORDER BY books.sort DESC" STMT_PAGE ")",
};
static void
db_book_unfill(Book *p)
{
if (NULL == p)
return;
free(p->title);
db_series_unfill(&p->s);
}
static void
db_book_data_unfill(BookData *p) {
if (NULL == p)
return;
free(p->name);
free(p->format);
}
void
db_book_free(Book *p)
{
db_book_unfill(p);
free(p);
}
void
db_book_adv_free(BookAdv *p)
{
if (NULL == p)
return;
size_t i;
db_book_unfill(&p->b);
for (i = 0; i < p->asize; i++) {
db_author_unfill(p->a[i]);
}
for (i = 0; i < p->tsize; i++) {
free(p->tags[i]);
}
free(p);
}
void
db_book_full_unfill(BookFull *p)
{
if (NULL == p)
return;
for (size_t i = 0; i < p->dsize; i++) {
db_book_data_unfill(p->data[i]);
}
free(p->isbn);
free(p->lccn);
free(p->path);
free(p->uuid);
free(p->lang);
free(p->publisher);
}
void
db_book_full_free(BookFull *p)
{
if (NULL == p)
return;
db_book_unfill(&p->ba.b);
for (size_t i = 0; i < p->ba.asize; i++) {
db_author_unfill(p->ba.a[i]);
}
db_book_full_unfill(p);
free(p);
}
void
db_book_fill(Book *book, struct ksqlstmt *stmt)
{
book->id = ksql_stmt_int(stmt, 0);
book->title = kstrdup(ksql_stmt_str(stmt, 1));
if ( ksql_stmt_isnull(stmt, 4) ) {
book->s.id = -1;
} else {
book->s_idx = ksql_stmt_double(stmt, 2);
book->s.name = kstrdup(ksql_stmt_str(stmt, 3));
book->s.id = ksql_stmt_int(stmt, 4);
}
}
int
db_books_count(struct kreq *r)
{
int count;
struct ksqlstmt *stmt;
ksql_stmt_alloc(r->arg, &stmt,
stmts[STMT_BOOKS_COUNT],
STMT_BOOKS_COUNT);
if (KSQL_ROW != ksql_stmt_step(stmt)) {
ksql_stmt_free(stmt);
return(0);
}
count = ksql_stmt_int(stmt, 0);
ksql_stmt_free(stmt);
return(count);
}
static void
db_assign_book_tags(struct booktag *list)
{
struct booktag *item = NULL;
struct booktag *p = NULL;
p = list;
while (NULL != p) {
if (NULL != p->b && NULL == p->b->tags && p->b->tsize > 0) {
p->b->tags = kcalloc(p->b->tsize, sizeof(char*));
p->b->tsize = 0; // use as counter for insert
}
p->b->tags[p->b->tsize] = p->tag;
p->b->tsize++;
item = p;
p = p->p;
free(item);
}
}
void
db_assign_book_authors(struct bookauth *list)
{
struct bookauth *item = NULL;
struct bookauth *p = NULL;
p = list;
while (NULL != p) {
if (NULL != p->b && NULL == p->b->a && p->b->asize > 0) {
p->b->a = kcalloc(p->b->asize, sizeof(Author));
p->b->asize = 0; // use as counter for insert
}
p->b->a[p->b->asize] = p->a;// add author
p->b->asize++;
item = p;
p = p->p;
free(item);
}
}
static int
query_books(const char* sort, const char *order)
{
int query = STMT_BOOKS_ID_ASC;
if (NULL != sort && 0 == strcmp(sort, "title")) {
query = STMT_BOOKS_TITLE_ASC;
if (NULL != order && 0 == strcmp(order, "desc"))
query++;
} else {
if (NULL == order || 0 == strcmp(order, "desc"))
query++;
}
return query;
}
static int
query_books_authors(const char* sort, const char *order)
{
int query = STMT_BOOKS_AUTHORS_ID_ASC;
if (NULL != sort && 0 == strcmp(sort, "title")) {
query = STMT_BOOKS_AUTHORS_TITLE_ASC;
if (NULL != order && 0 == strcmp(order, "desc"))
query++;
} else {
if (NULL == order || 0 == strcmp(order, "desc"))
query++;
}
return query;
}
static int
query_books_tags(const char* sort, const char *order)
{
int query = STMT_BOOKS_TAGS_ID_ASC;
if (NULL != sort && 0 == strcmp(sort, "title")) {
query = STMT_BOOKS_TAGS_TITLE_ASC;
if (NULL != order && 0 == strcmp(order, "desc"))
query++;
} else {
if (NULL == order || 0 == strcmp(order, "desc"))
query++;
}
return query;
}
static struct booktag *
db_load_books_tags(struct kreq *r, BookAdv **books, int count, const char *sort, const char *order, int limit, int offset)
{
struct ksqlstmt *stmt;
struct booktag *p = NULL;
struct booktag *item = NULL;
int i;
int64_t bid;
int query = query_books_tags(sort, order);
ksql_stmt_alloc(r->arg, &stmt,
stmts[query],
query);
ksql_bind_int(stmt, 0, limit);
ksql_bind_int(stmt, 1, offset);
while (KSQL_ROW == ksql_stmt_step(stmt)) {
item = kcalloc(1, sizeof(struct booktag));
item->tag = kstrdup(ksql_stmt_str(stmt, 0));
bid = ksql_stmt_int(stmt, 1);
for (i = 0; i < count && NULL == item->b; i++) {
if (books[i]->b.id == bid) {
item->b = books[i];
item->b->tsize++;
}
}
item->p = p;
p = item;
}
ksql_stmt_free(stmt);
return p;
}
struct bookauth *
db_load_books_authors(BookAdv **books, int count, struct ksqlstmt *stmt)
{
struct bookauth *p = NULL;
struct bookauth *item = NULL;
int i;
int64_t bid;
while (KSQL_ROW == ksql_stmt_step(stmt)) {
// add author to list, link related book and increment asize
item = kcalloc(1, sizeof(struct bookauth));
item->a = kcalloc(1, sizeof(Author));
item->a->id = ksql_stmt_int(stmt, 0);
item->a->name = kstrdup(ksql_stmt_str(stmt, 1));
bid = ksql_stmt_int(stmt, 2);
for (i = 0; i < count && NULL == item->b; i++) {
if (books[i]->b.id == bid) {
item->b = books[i];
item->b->asize++;
}
}
item->p = p;
p = item;
}
return p;
}
int
db_books_load(struct kreq *r, BookAdv **books, const char *sort, const char *order, int limit, int offset)
{
if (limit < 0 || offset < 0) {
return 0;
}
struct ksqlstmt *stmt;
struct bookauth *p_a;
struct booktag *p_t;
int count = 0;
int query = query_books(sort, order);
ksql_stmt_alloc(r->arg, &stmt,
stmts[query],
query);
ksql_bind_int(stmt, 0, limit);
ksql_bind_int(stmt, 1, offset);
while (KSQL_ROW == ksql_stmt_step(stmt)) {
books[count] = kcalloc(1, sizeof(BookAdv));
db_book_fill(&books[count]->b,stmt);
books[count]->asize = 0;
books[count]->tsize = 0;
count++;
}
ksql_stmt_free(stmt);
int querya = query_books_authors(sort, order);
ksql_stmt_alloc(r->arg, &stmt,
stmts[querya],
querya);
ksql_bind_int(stmt, 0, limit);
ksql_bind_int(stmt, 1, offset);
p_a = db_load_books_authors(books, count, stmt);
ksql_stmt_free(stmt);
db_assign_book_authors(p_a);
p_t = db_load_books_tags(r, books, count, sort, order, limit, offset);
db_assign_book_tags(p_t);
return count;
}
static void
db_book_full_tags(BookAdv *b, struct kreq *r, int64_t id)
{
struct ksqlstmt *stmt;
struct booktag *p = NULL;
struct booktag *item = NULL;
ksql_stmt_alloc(r->arg, &stmt,
stmts[STMT_BOOK_TAGS],
STMT_BOOK_TAGS);
ksql_bind_int(stmt, 0, id);
while (KSQL_ROW == ksql_stmt_step(stmt)) {
item = kcalloc(1, sizeof(struct booktag));
item->tag = kstrdup(ksql_stmt_str(stmt, 0));
item->b = b;
item->b->tsize++;
item->p = p;
p = item;
}
ksql_stmt_free(stmt);
db_assign_book_tags(p);
}
static void
db_book_full_authors(BookAdv *b, struct kreq *r, int64_t id) {
struct ksqlstmt *stmt;
struct bookauth *p = NULL;
struct bookauth *item = NULL;
ksql_stmt_alloc(r->arg, &stmt,
stmts[STMT_BOOK_AUTHORS],
STMT_BOOK_AUTHORS);
ksql_bind_int(stmt, 0, id);
while (KSQL_ROW == ksql_stmt_step(stmt)) {
item = kcalloc(1, sizeof(struct bookauth));
item->a = kcalloc(1, sizeof(Author));
item->a->id = ksql_stmt_int(stmt, 0);
item->a->name = kstrdup(ksql_stmt_str(stmt, 1));
item->b = b;
item->b->asize++;
item->p = p;
p = item;
}
ksql_stmt_free(stmt);
// assign authors to book
db_assign_book_authors(p);
}
static void
db_book_full_data(BookFull *b, struct kreq *r, int64_t id) {
struct ksqlstmt *stmt;
struct bookdata *p = NULL;
struct bookdata *item = NULL;
ksql_stmt_alloc(r->arg, &stmt,
stmts[STMT_BOOK_DATA],
STMT_BOOK_DATA);
ksql_bind_int(stmt, 0, id);
while (KSQL_ROW == ksql_stmt_step(stmt)) {
item = kcalloc(1, sizeof(struct bookdata));
item->d = kcalloc(1, sizeof(BookData));
item->d->name = kstrdup(ksql_stmt_str(stmt, 0));
item->d->format = kstrdup(ksql_stmt_str(stmt, 1));
item->d->size = ksql_stmt_int(stmt, 2);
item->b = b;
item->b->dsize++;
item->p = p;
p = item;
}
ksql_stmt_free(stmt);
while (NULL != p) {
if (NULL != p->b && NULL == p->b->data && p->b->dsize > 0) {
p->b->data = kcalloc(p->b->dsize, sizeof(BookData));
p->b->dsize = 0; // use as counter for insert
}
p->b->data[p->b->dsize] = p->d;
p->b->dsize++;
item = p;
p = p->p;
free(item);
}
}
BookFull *
db_book_load(struct kreq *r, int64_t id)
{
struct ksqlstmt *stmt;
BookFull *book;
ksql_stmt_alloc(r->arg, &stmt,
stmts[STMT_BOOK],
STMT_BOOK);
ksql_bind_int(stmt, 0, id);
if (KSQL_ROW != ksql_stmt_step(stmt)) {
ksql_stmt_free(stmt);
return(NULL);
}
book = kcalloc(1, sizeof(BookFull));
db_book_fill(&book->ba.b, stmt);
book->timestamp = ksql_stmt_int(stmt, 5);
book->pubdate = ksql_stmt_int(stmt, 6);
book->isbn = strornull(stmt, 7);
book->lccn = strornull(stmt, 8);
book->path = strornull(stmt, 9);
book->uuid = strornull(stmt, 10);
book->has_cover = ksql_stmt_int(stmt, 11);
book->lang = strornull(stmt, 12);
book->publisher = strornull(stmt, 13);
ksql_stmt_free(stmt);
db_book_full_tags(&book->ba, r, id);
db_book_full_authors(&book->ba, r, id);
db_book_full_data(book, r, id);
return book;
}
int
db_books_search(struct kreq *r, int limit, Book** books, const char **terms, int num, int all)
{
if (limit < 0) {
return 0;
}
struct ksqlstmt *stmt;
int i, count = 0;
size_t sqlsz = sizeof(STMT_BOOKS0)
+ sizeof(STMT_WHERE)
+ num * sizeof(STMT_SEARCH_TERM)
+ (num-1) * sizeof(STMT_BOOL_AND)
+ sizeof(STMT_SEARCH_ORDER);
char *sql = kcalloc(sqlsz, sizeof(char));
strlcat(sql, STMT_BOOKS0, sqlsz);
strlcat(sql, STMT_WHERE, sqlsz);
for (i=0; i<num; i++) {
strlcat(sql, STMT_SEARCH_TERM, sqlsz);
if (i<num-1 && all)
strlcat(sql, STMT_BOOL_AND, sqlsz);
if (i<num-1 && !all)
strlcat(sql, STMT_BOOL_OR, sqlsz);
}
strlcat(sql, STMT_SEARCH_ORDER, sqlsz);
ksql_stmt_alloc(r->arg, &stmt, sql, 0);
for (i=0; i<num; i++) {
size_t sz = 3 + strlen(terms[i]);
char *term = kcalloc(sz, sizeof(char));
strlcat(term, "\%", sz);
strlcat(term, terms[i], sz);
strlcat(term, "\%", sz);
ksql_bind_str(stmt, i, term);
}
while (KSQL_ROW == ksql_stmt_step(stmt)) {
if (count < limit) {
books[count] = kcalloc(1, sizeof(Book));
db_book_fill(books[count],stmt);
}
count++;
}
ksql_stmt_free(stmt);
return count;
}
Reference in New Issue View Git Blame Copy Permalink