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

390 lines
10 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_SERIES0 "SELECT series.id, series.name, count(book) FROM series "\
"LEFT OUTER JOIN books_series_link ON books_series_link.series = series.id "\
"GROUP BY series.id "
#define STMT_SERIES_AUTHORS0 "SELECT DISTINCT authors.id, authors.name, books_series_link.series "\
"FROM authors, books_authors_link, books_series_link "\
"WHERE books_authors_link.book = books_series_link.book AND books_authors_link.author = authors.id "\
"AND books_series_link.series IN ( SELECT id FROM series "
#define STMT_SERIES_SEARCH "SELECT series.id, series.name FROM series WHERE "
#define STMT_SEARCH_TERM " series.sort like ? "
#define STMT_BOOL_AND " AND "
#define STMT_BOOL_OR " OR "
#define STMT_SEARCH_ORDER " ORDER BY series.sort"
enum stmt {
STMT_SERIES_ID_ASC,
STMT_SERIES_ID_DESC,
STMT_SERIES_NAME_ASC,
STMT_SERIES_NAME_DESC,
STMT_SERIES_AUTHORS_ID_ASC,
STMT_SERIES_AUTHORS_ID_DESC,
STMT_SERIES_AUTHORS_NAME_ASC,
STMT_SERIES_AUTHORS_NAME_DESC,
STMT_SERIE,
STMT_SERIE_BOOKS,
STMT_SERIE_AUTHORS,
STMT__MAX
};
static const char *const stmts[STMT__MAX] = {
/* STMT_SERIES_ID_ASC */
STMT_SERIES0 " ORDER BY series.id" STMT_PAGE,
/* STMT_SERIES_ID_DESC */
STMT_SERIES0 " ORDER BY series.id DESC" STMT_PAGE,
/* STMT_SERIES_NAME_ASC */
STMT_SERIES0 " ORDER BY series.sort" STMT_PAGE,
/* STMT_SERIES_NAME_DESC */
STMT_SERIES0 " ORDER BY series.sort DESC" STMT_PAGE,
/* STMT_SERIES_AUTHORS_ID_ASC */
STMT_SERIES_AUTHORS0 " ORDER BY series.id" STMT_PAGE ")",
/* STMT_SERIES_AUTHORS_ID_DESC */
STMT_SERIES_AUTHORS0 " ORDER BY series.id DESC" STMT_PAGE ")",
/* STMT_SERIES_AUTHORS_NAME_ASC */
STMT_SERIES_AUTHORS0 " ORDER BY series.sort" STMT_PAGE ")",
/* STMT_SERIES_AUTHORS_NAME_DESC */
STMT_SERIES_AUTHORS0 " ORDER BY series.sort DESC" STMT_PAGE ")",
/* STMT_SERIE */
"SELECT series.id, series.name FROM series WHERE series.id = ?",
/* STMT_SERIE_BOOKS */
"SELECT books.id, title, series_index FROM books \
LEFT OUTER JOIN books_series_link ON books.id = books_series_link.book \
WHERE books_series_link.series = ? ORDER BY series_index DESC",
/* STMT_SERIE_AUTHORS */
"SELECT DISTINCT authors.id, authors.name \
FROM authors, books_authors_link, books_series_link \
WHERE books_authors_link.book = books_series_link.book AND books_authors_link.author = authors.id \
AND books_series_link.series = ?",
};
struct seriesbook {
SeriesFull *s;
Book *b;
struct seriesbook *p;
};
struct seriesauth {
SeriesAdv *s;
Author *a;
struct seriesauth *p;
};
void
db_series_unfill(Series *p)
{
if (NULL == p)
return;
free(p->name);
}
void
db_series_free(Series *p)
{
if (NULL == p)
return;
db_series_unfill(p);
free(p);
}
void
db_series_adv_free(SeriesAdv *p)
{
if (NULL == p)
return;
db_series_unfill(&p->s);
free(p);
}
static int
query_series(const char* sort, const char *order)
{
int query = STMT_SERIES_ID_ASC;
if (NULL != sort && 0 == strcmp(sort, "name")) {
query = STMT_SERIES_NAME_ASC;
if (NULL != order && 0 == strcmp(order, "desc"))
query++;
} else {
if (NULL == order || 0 == strcmp(order, "desc"))
query++;
}
return query;
}
static int
query_seriesauth(const char* sort, const char *order)
{
int query = STMT_SERIES_AUTHORS_ID_ASC;
if (NULL != sort && 0 == strcmp(sort, "name")) {
query = STMT_SERIES_AUTHORS_NAME_ASC;
if (NULL != order && 0 == strcmp(order, "desc"))
query++;
} else {
if (NULL == order || 0 == strcmp(order, "desc"))
query++;
}
return query;
}
static void
db_assign_series_authors(struct seriesauth *list)
{
struct seriesauth *item = NULL;
struct seriesauth *p = NULL;
p = list;
while (NULL != p) {
if (NULL != p->s && NULL == p->s->a && p->s->asize > 0) {
p->s->a = kcalloc(p->s->asize, sizeof(Author));
p->s->asize = 0; // use as counter for insert
}
p->s->a[p->s->asize] = p->a;
p->s->asize++;
item = p;
p = p->p;
free(item);
}
}
static struct seriesauth *
db_load_series_authors(SeriesAdv **series, int count, struct ksqlstmt *stmt)
{
struct seriesauth *item = NULL;
struct seriesauth *p = NULL;
int64_t sid = -1;
int i = 0;
while (KSQL_ROW == ksql_stmt_step(stmt)) {
item = kcalloc(1, sizeof(struct seriesauth));
item->a = kcalloc(1, sizeof(Author));
item->a->id = ksql_stmt_int(stmt, 0);
item->a->name = kstrdup(ksql_stmt_str(stmt, 1));
sid = ksql_stmt_int(stmt, 2);
for (i = 0; i < count && NULL == item->s; i++) {
if (series[i]->s.id == sid) {
item->s = series[i];
item->s->asize++;
}
}
item->p = p;
p = item;
}
return p;
}
static struct seriesbook*
db_load_series_books(struct kreq *r, SeriesFull *series, int64_t id)
{
struct ksqlstmt *stmt;
struct seriesbook *p = NULL;
struct seriesbook *item = NULL;
ksql_stmt_alloc(r->arg, &stmt,
stmts[STMT_SERIE_BOOKS],
STMT_SERIE_BOOKS);
ksql_bind_int(stmt, 0, id);
while (KSQL_ROW == ksql_stmt_step(stmt)) {
item = kcalloc(1,sizeof(struct seriesbook));
Book *b = kcalloc(1, sizeof(Book));
b->id = ksql_stmt_int(stmt, 0);
b->title = kstrdup(ksql_stmt_str(stmt, 1));
b->s_idx = ksql_stmt_int(stmt, 2);
b->s = series->s.s;
item->b = b;
item->s = series;
series->s.books++;
item->p = p;
p = item;
}
ksql_stmt_free(stmt);
return p;
}
static void
db_assign_series_books(struct seriesbook *list)
{
struct seriesbook *p = NULL;
struct seriesbook *item = NULL;
p = list;
while (NULL != p) {
if (NULL != p->s && NULL == p->s->b && p->s->s.books > 0) {
p->s->b = kcalloc(p->s->s.books, sizeof(Book));
p->s->s.books = 0; // use as counter for insert
}
p->s->b[p->s->s.books] = p->b;
p->s->s.books++;
item = p;
p = p->p;
free(item);
}
}
SeriesFull *
db_serie_load(struct kreq *r, int64_t id)
{
struct ksqlstmt *stmt;
SeriesFull *series;
struct seriesauth *p = NULL;
struct seriesauth *item = NULL;
struct seriesbook *list = NULL;
ksql_stmt_alloc(r->arg, &stmt,
stmts[STMT_SERIE],
STMT_SERIE);
ksql_bind_int(stmt, 0, id);
if (KSQL_ROW != ksql_stmt_step(stmt)) {
ksql_stmt_free(stmt);
return(NULL);
}
series = kcalloc(1, sizeof(SeriesFull));
series->s.s.id = ksql_stmt_int(stmt, 0);
series->s.s.name = kstrdup(ksql_stmt_str(stmt, 1));
series->s.books = 0;
series->s.asize = 0;
ksql_stmt_free(stmt);
list = db_load_series_books(r, series, id);
db_assign_series_books(list);
ksql_stmt_alloc(r->arg, &stmt,
stmts[STMT_SERIE_AUTHORS],
STMT_SERIE_AUTHORS);
ksql_bind_int(stmt, 0, id);
while (KSQL_ROW == ksql_stmt_step(stmt)) {
item = kcalloc(1, sizeof(struct seriesauth));
item->a = kcalloc(1, sizeof(Author));
item->a->id = ksql_stmt_int(stmt, 0);
item->a->name = kstrdup(ksql_stmt_str(stmt, 1));
item->s = &series->s;
item->s->asize++;
item->p = p;
p = item;
}
ksql_stmt_free(stmt);
db_assign_series_authors(p);
return series;
}
int
db_series_load(struct kreq *r, SeriesAdv **series, const char *sort, const char *order, int limit, int offset)
{
if (limit < 0 || offset < 0)
return 0;
struct ksqlstmt *stmt;
int count = 0;
struct seriesauth *p = NULL;
int query = query_series(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)) {
series[count] = kcalloc(1, sizeof(SeriesAdv));
series[count]->s.id = ksql_stmt_int(stmt, 0);
series[count]->s.name = kstrdup(ksql_stmt_str(stmt, 1));
series[count]->books = ksql_stmt_int(stmt, 2);
series[count]->asize = 0;
count++;
}
ksql_stmt_free(stmt);
int queryauth = query_seriesauth(sort, order);
ksql_stmt_alloc(r->arg, &stmt,
stmts[queryauth],
queryauth);
ksql_bind_int(stmt, 0, limit);
ksql_bind_int(stmt, 1, offset);
p = db_load_series_authors(series, count, stmt);
ksql_stmt_free(stmt);
db_assign_series_authors(p);
return count;
}
int
db_series_search(struct kreq *r, int limit, Series** series, const char **terms, int num, int all)
{
if (limit < 0) {
return 0;
}
struct ksqlstmt *stmt;
int i, count = 0;
size_t sqlsz = sizeof(STMT_SERIES_SEARCH)
+ num * sizeof(STMT_SEARCH_TERM)
+ (num-1) * sizeof(STMT_BOOL_AND)
+ sizeof(STMT_SEARCH_ORDER);
char *sql = kcalloc(sqlsz, sizeof(char));
strlcat(sql, STMT_SERIES_SEARCH, 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);
debug(r, "SQL: %s", sql);
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) {
series[count] = kcalloc(1, sizeof(Series));
series[count]->id = ksql_stmt_int(stmt, 0);
series[count]->name = kstrdup(ksql_stmt_str(stmt, 1));
}
count++;
}
ksql_stmt_free(stmt);
return count;
}
Reference in New Issue View Git Blame Copy Permalink