commit 63ac797272c791e55c67e1dde3f99b790387c6c9
parent 54630b3a5e5e9836130f47e3f5c0f10eb073b6cf
Author: Matthias Balk <mbalk@mbalk.de>
Date: Sun, 28 Jul 2024 12:31:06 +0200
Lunch Poll Legacy -- first version
Diffstat:
13 files changed, 716 insertions(+), 0 deletions(-)
diff --git a/LICENSE b/LICENSE
@@ -0,0 +1,26 @@
+Copyright 2024 Matthias Balk
+
+Redistribution and use in source and binary forms, with or without
+modification, are permitted provided that the following conditions are met:
+
+1. Redistributions of source code must retain the above copyright notice, this
+list of conditions and the following disclaimer.
+
+2. Redistributions in binary form must reproduce the above copyright notice,
+this list of conditions and the following disclaimer in the documentation
+and/or other materials provided with the distribution.
+
+3. Neither the name of the copyright holder nor the names of its contributors
+may be used to endorse or promote products derived from this software without
+specific prior written permission.
+
+THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
+ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
+WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
+DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
+FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
+DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
+SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
+CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
+OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
+OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
diff --git a/Makefile b/Makefile
@@ -0,0 +1,60 @@
+CC = cc
+
+# needed for strlcpy(3bsd) on Linux
+# install package 'libbsd-dev'!
+INCS_BSD_OVERLAY = `pkg-config --cflags libbsd-overlay`
+LIBS_BSD_OVERLAY = `pkg-config --libs libbsd-overlay`
+
+CFLAGS = -std=c99 -pedantic -Wall -Wno-deprecated-declarations ${INCS_BSD_OVERLAY}
+
+OPTIM = -Os
+OPTIM_DEBUG = -O0 -ggdb
+
+INCS_SQLITE = `pkg-config --cflags sqlite3`
+LIBS_SQLITE = `pkg-config --libs --static sqlite3`
+
+
+all: bin/mittag.cgi
+
+mittag.o: src/mittag.c
+ ${CC} ${OPTIM_DEBUG} ${CFLAGS} ${INCS_SQLITE} -c src/mittag.c -static -o $@
+
+date-utils.o: src/date-utils.c
+ ${CC} ${OPTIM_DEBUG} ${CFLAGS} -c src/date-utils.c -static -o $@
+
+utils.o: src/utils.c
+ ${CC} ${OPTIM_DEBUG} ${CFLAGS} -c src/utils.c -static -o $@
+
+http.o: src/http.c
+ ${CC} ${OPTIM_DEBUG} ${CFLAGS} -c src/http.c -static -o $@
+
+bin/mittag.cgi: utils.o date-utils.o http.o mittag.o
+ mkdir -p bin
+ ${CC} ${OPTIM_DEBUG} utils.o date-utils.o http.o mittag.o ${LIBS_BSD_OVERLAY} ${LIBS_SQLITE} -static -o bin/mittag.cgi
+
+test: all var/mittag.db
+ #echo 'vote-2=2&vote-6=6&vote-43=43&name=m%C3%A4+%C3%9F+%C3%84+%C3%A9%20%E2%99%A5&date=2024-01-12' | PATH_INFO=/votes REQUEST_METHOD=POST CONTENT_TYPE=application/x-www-form-urlencoded bin/mittag.cgi
+ PATH_INFO=/votes QUERY_STRING="date=2012-05-10" REQUEST_METHOD=GET bin/mittag.cgi
+ #PATH_INFO=/votes QUERY_STRING="foobar=2024-07-27" REQUEST_METHOD=GET bin/mittag.cgi
+
+run-test-server: bin/mittag.cgi bin/var/mittag.db thttpd-2.29/thttpd
+ echo starting http daemon
+ echo try curl -i http://localhost:60100/mittag.cgi/votes
+ ./thttpd-2.29/thttpd -p 60100 -c '**.cgi' -d ./bin/
+
+thttpd-2.29/thttpd:
+ wget -c http://www.acme.com/software/thttpd/thttpd-2.29.tar.gz -O - | tar -xzf -
+ cd thttpd-2.29 && ./configure && make
+
+var/mittag.db:
+ cat var/mittag.sql | sqlite3 var/mittag.db
+
+bin/var/mittag.db:
+ mkdir -p bin/var
+ cat var/mittag.sql | sqlite3 bin/var/mittag.db
+
+clean:
+ rm -f bin/mittag.cgi mittag.o utils.o date-utils.o http.o
+
+tags:
+ ctags -R . /usr/include/sqlite3*
diff --git a/README b/README
@@ -1,2 +1,48 @@
Lunch Poll Legacy
=================
+
+A simple CGI web application to poll colleagues where to have lunch.
+
+The „Legacy“ in the name is because of the programming language and
+the technology used. :-)
+
+It is implemented in C. Its predecessor, `mittag.py`, was written in
+Python 2.
+
+License: See file `LICENSE`.
+
+
+Try it out!
+-----------
+
+Type
+
+ make clean test
+
+or
+
+ make clean run-test-server
+
+
+Deployment
+----------
+
+As `Lunch Poll Legacy` is developed on GNU/Linux and run on OpenBSD [1],
+[2], there is a branch `deployment` with necessary changes for
+deployment.
+
+
+Features to come
+----------------
+
+- Make it possible to vote for future dates.
+- Prevent changes for past days.
+- Prevent changes for the current days if it is afternoon.
+- Links to go to previous / next day.
+
+
+------------------------------------------------------------------------
+
+
+[1] https://www.openbsd.org/
+[2] https://learnbchs.org/
diff --git a/src/config.h b/src/config.h
@@ -0,0 +1,13 @@
+/**
+ * Lunch Poll Legacy
+ * Copyright 2024 Matthias Balk
+ */
+
+#ifndef __CONFIG_H__
+#define __CONFIG_H__
+
+#define PROG_VERSION "1.0"
+#define PROG_NAME "Lunch Poll Legacy"
+#define COPYRIGHT "Copyright 2024 Matthias Balk"
+
+#endif /* __CONFIG_H__ */
diff --git a/src/date-utils.c b/src/date-utils.c
@@ -0,0 +1,25 @@
+/**
+ * Lunch Poll Legacy
+ * Copyright 2024 Matthias Balk
+ */
+
+#include <sys/types.h>
+#include <time.h>
+#include <string.h>
+
+#include "date-utils.h"
+
+u_int8_t get_day_of_week(const char* date)
+{
+ struct tm tm;
+ memset(&tm, 0, sizeof(tm));
+ if (strptime(date, "%Y-%m-%d", &tm) == NULL) {
+ return 0;
+ }
+
+ /* tm_wday: Day of the week [0, 6] (Sunday = 0) */
+ if (tm.tm_wday == 0) {
+ return DOW_SUN;
+ }
+ return 0x1 << (tm.tm_wday - 1);
+}
diff --git a/src/date-utils.h b/src/date-utils.h
@@ -0,0 +1,21 @@
+/**
+ * Lunch Poll Legacy
+ * Copyright 2024 Matthias Balk
+ */
+
+#ifndef __DATE_UTILS_H__
+#define __DATE_UTILS_H__
+
+#include <sys/types.h>
+
+#define DOW_MON (0x1 << 0)
+#define DOW_TUE (0x1 << 1)
+#define DOW_WED (0x1 << 2)
+#define DOW_THU (0x1 << 3)
+#define DOW_FRI (0x1 << 4)
+#define DOW_SAT (0x1 << 5)
+#define DOW_SUN (0x1 << 6)
+
+u_int8_t get_day_of_week(const char* date);
+
+#endif /* __DATE_UTILS_H__ */
diff --git a/src/http.c b/src/http.c
@@ -0,0 +1,114 @@
+/**
+ * Lunch Poll Legacy
+ * Copyright 2024 Matthias Balk
+ */
+
+#include <ctype.h>
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+
+#include "http.h"
+
+static StatusCode status_codes[] = {
+ { 200, "OK" },
+ { 204, "No Content" },
+ { 302, "Found" },
+ { 303, "See Other" },
+ { 400, "Bad Request" },
+ { 404, "Not Found" },
+ { 405, "Method Not Allowed" },
+ { 415, "Unsupported Media Type" },
+ { 500, "Internal Server Error" },
+};
+
+
+void header_status(int code)
+{
+ StatusCode *sc;
+ for (sc = status_codes; sc < status_codes + LEN(status_codes); sc++) {
+ if (code == sc->code) {
+ printf("Status: %d %s\r\n", sc->code, sc->name);
+ return;
+ }
+ }
+
+ if (code != 500) {
+ header_status(500);
+ }
+}
+
+void header(const char *key, const char *value)
+{
+ printf("%s: %s\r\n", key, value);
+}
+
+void err_exit(const char *msg)
+{
+ header_status(500);
+ header_end();
+ perror(msg);
+ exit(EXIT_FAILURE);
+}
+
+/* https://stackoverflow.com/a/14530993 */
+void urldecode(const char *src, char *dst /*, size_t dst_size TODO*/)
+{
+ char a, b;
+ while (*src) {
+ if ((*src == '%') &&
+ ((a = src[1]) && (b = src[2])) &&
+ (isxdigit(a) && isxdigit(b))) {
+ if (a >= 'a')
+ a -= 'a' - 'A';
+ if (a >= 'A')
+ a -= 'A' - 10;
+ else
+ a -= '0';
+ if (b >= 'a')
+ b -= 'a' - 'A';
+ if (b >= 'A')
+ b -= 'A' - 10;
+ else
+ b -= '0';
+
+ *dst++ = 16*a+b;
+ src+=3;
+ }
+ else if (*src == '+') {
+ *dst++ = ' ';
+ src++;
+ }
+ else {
+ *dst++ = *src++;
+ }
+ }
+ *dst++ = '\0';
+}
+
+
+/*
+ * Uses strtok(3), x_www_form_urlencoded will be changed!
+ */
+void split_and_decode_form_params(char* x_www_form_urlencoded, char** params)
+{
+ int idx = 0;
+ params[idx] = strtok(x_www_form_urlencoded, "&");
+ while (params[idx++] != NULL) {
+ if (idx >= MAX_PARAMS_COUNT) {
+ fprintf(stderr, "error: too many params (>= %d)\n", MAX_PARAMS_COUNT);
+ err_exit("error: too many params");
+ }
+ params[idx] = strtok(NULL, "&");
+
+ /* we expected the decoded string to be shorter than the encoded string! */
+ if (params[idx] != NULL) {
+ char *value = strchr(params[idx], '=') + 1;
+ size_t sz = strlen(value) + 1;
+ char *value_tmp = calloc(sz, sizeof (char));
+ urldecode(value, value_tmp);
+ strlcpy(value, value_tmp, sz * sizeof (char));
+ free(value_tmp);
+ }
+ }
+}
diff --git a/src/http.h b/src/http.h
@@ -0,0 +1,32 @@
+/**
+ * Lunch Poll Legacy
+ * Copyright 2024 Matthias Balk
+ */
+
+#ifndef __HTTP_H__
+#define __HTTP_H__
+
+#define LEN(a) (sizeof(a) / sizeof(a)[0])
+#define header_end() (puts("\r"))
+
+#define MAX_PARAMS_COUNT 128
+#define MAX_POST_SIZE 4096
+
+typedef struct {
+ unsigned int code;
+ const char *name;
+} StatusCode;
+
+typedef struct {
+ const char *path;
+ const char *method;
+ void (*func)(void);
+} Route;
+
+void header_status(int code);
+void header(const char *key, const char *value);
+void err_exit(const char *msg);
+void urldecode(const char *src, char *dst /*, size_t dst_size TODO*/);
+void split_and_decode_form_params(char* x_www_form_urlencoded, char** params);
+
+#endif /* __HTTP_H__ */
diff --git a/src/mittag.c b/src/mittag.c
@@ -0,0 +1,290 @@
+/**
+ * Lunch Poll Legacy
+ * Copyright 2024 Matthias Balk
+ */
+
+#include <errno.h>
+#include <regex.h>
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+#include <time.h>
+
+#include <sqlite3.h>
+
+#include "http.h"
+#include "date-utils.h"
+#include "utils.h"
+#include "mittag.h"
+#include "config.h"
+
+
+static sqlite3 *db;
+static char _header_printed = 0;
+
+
+static int get_votes_callback(void *unused,
+ int num_cols,
+ char **col_values,
+ char **col_names)
+{
+ /* TODO: direct output is not always beautiful, think about alternatives */
+ if (!_header_printed) {
+ _header_printed = 1;
+ header_status(200);
+ header("X-OSSE", PROG_VERSION);
+ header("Content-Type", "text/html; charset=utf-8");
+ header_end();
+
+ puts("<!DOCTYPE html>");
+ puts("<html lang=\"de\"><head><meta charset=\"utf-8\">");
+ puts("<meta name=\"robots\" content=\"noindex\">");
+ puts("<title>Alle mampfen Mamba. Mampfred auch.</title></head>");
+ puts("<body><form action=\"/mittag.cgi/votes\" method=\"POST\"><table>");
+ }
+
+ if (strlen(col_values[2] /* r_url */)) {
+ printf("<tr><td><input type=\"checkbox\" name=\"restaurant_id\" "
+ "value=\"%s\"></td><td>%s</td><td>%s</td><td><a href=\"%s\" "
+ "target=\"_blank\">%s</a></td></tr>\n",
+ col_values[4], /* r_id */
+ col_values[3], /* num_votes */
+ col_values[0], /* voters */
+ col_values[2], /* r_url */
+ col_values[1]); /* r_name */
+ }
+ else {
+ printf("<tr><td><input type=\"checkbox\" name=\"restaurant_id\" "
+ "value=\"%s\"></td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
+ col_values[4], /* r_id */
+ col_values[3], /* num_votes */
+ col_values[0], /* voters */
+ col_values[1]); /* r_name */
+ }
+
+ return 0;
+}
+
+static void get_votes(void)
+{
+ char date[MAX_LEN_DATE + 1] = { 0 };
+
+ char *query_string = getenv("QUERY_STRING");
+ if (query_string != NULL) {
+ char* params[MAX_POST_SIZE];
+ split_and_decode_form_params(query_string, params);
+
+ for (int idx = 0; idx < MAX_PARAMS_COUNT && params[idx] != NULL; idx++) {
+ if (strstr(params[idx], "date=") == params[idx]) {
+ strlcpy(date,
+ strchr(params[idx], '=') + 1,
+ (MAX_LEN_DATE + 1) * sizeof(char));
+ break;
+ }
+ }
+ }
+
+ if (*date == 0) {
+ time_t t = time(NULL);
+ struct tm *tm = gmtime(&t);
+ strftime(date, (MAX_LEN_DATE + 1) * sizeof(char), "%Y-%m-%d", tm);
+ }
+
+ char buffer[350];
+ snprintf(buffer, 350 * sizeof(char),
+ "SELECT "
+ "CASE WHEN GROUP_CONCAT(v.voter) IS NOT NULL THEN "
+ "GROUP_CONCAT(v.voter, ', ') ELSE '' END AS 'voters', "
+ "r.name, "
+ "r.url, "
+ "COUNT(v.id) AS 'num_votes', "
+ "r.id "
+ "FROM "
+ "restaurant r "
+ "LEFT JOIN vote v "
+ "ON v.restaurant_id = r.id AND "
+ "v.date = '%s' "
+ "WHERE "
+ "r.days_open & %d <> 0 "
+ "GROUP BY "
+ "r.id "
+ "ORDER BY "
+ "num_votes DESC, "
+ "r.category ASC, "
+ "r.id ASC;",
+ date, get_day_of_week(date));
+ char *zErrMsg = NULL;
+ int rc = sqlite3_exec(db, buffer, get_votes_callback, 0, &zErrMsg);
+ if (rc != SQLITE_OK) {
+ _header_printed = 1;
+ header_status(500);
+ header_end();
+
+ fprintf(stderr, "SQL error: %s\n", zErrMsg);
+ sqlite3_free(zErrMsg);
+ return;
+ }
+
+ if (!_header_printed) {
+ header_status(404);
+ header_end();
+ return;
+ }
+
+ puts("</table>Name: <input type=\"text\" name=\"name\">");
+ puts("<input type=\"submit\" value=\"Abstimmen\"></form>");
+ printf("<p>%s %s -- %s</p></body></html>\n",
+ PROG_NAME, PROG_VERSION, COPYRIGHT);
+}
+
+static void post_votes(void)
+{
+ char *content_type = getenv("CONTENT_TYPE");
+ if (content_type == NULL ||
+ strcmp("application/x-www-form-urlencoded", content_type) != 0) {
+ header_status(415);
+ header_end();
+ return;
+ }
+
+ char buffer[MAX_POST_SIZE] = { 0 };
+ fread(buffer, sizeof(*buffer), MAX_POST_SIZE - 1, stdin);
+ if (ferror(stdin)) {
+ const char* err = strerror(errno);
+ fputs(err, stderr);
+ err_exit(err);
+ }
+ if (!feof(stdin)) {
+ fprintf(stderr,
+ "error: posted data exceeds maximum supported size of %lu bytes\n",
+ MAX_POST_SIZE * sizeof(*buffer));
+ err_exit("error: posted data exceeds maximum supported size");
+ }
+
+ char* params[MAX_POST_SIZE];
+ split_and_decode_form_params(buffer, params);
+
+ char *name = NULL;
+ for (int idx = 0; params[idx] != NULL; idx++) {
+ if (strstr(params[idx], "name=") == params[idx]) {
+ name = strchr(params[idx], '=') + 1;
+ break;
+ }
+ }
+ /* TODO: trim beginning and trailing whitespaces from name:
+ * 'w3m' adds a trailing newline... */
+
+ if (name != NULL) name = trim(name);
+
+ if (name == NULL ||
+ *name == '\0' ||
+ strlen(name) > MAX_LEN_NAME) {
+ header_status(400);
+ header("Content-Type", "text/plain; charset=utf-8");
+ header_end();
+ puts("required field 'name' is missing or too long");
+ return;
+ }
+
+ char date[MAX_LEN_DATE + 1] = { 0 };
+ time_t t = time(NULL);
+ struct tm *tm = gmtime(&t);
+ strftime(date, (MAX_LEN_DATE + 1) * sizeof(char), "%Y-%m-%d", tm);
+
+
+ char stmnt[48 + MAX_LEN_NAME + MAX_LEN_DATE];
+ snprintf(stmnt, (48 + MAX_LEN_NAME + MAX_LEN_DATE) * sizeof(char),
+ "DELETE FROM vote WHERE voter = '%s' AND date = '%s'",
+ name, date);
+
+ char *zErrMsg = NULL;
+ int rc = sqlite3_exec(db, stmnt, NULL, 0, &zErrMsg);
+ if (rc != SQLITE_OK) {
+ _header_printed = 1;
+ header_status(500);
+ header_end();
+
+ fprintf(stderr, "SQL error: %s\n", zErrMsg);
+ sqlite3_free(zErrMsg);
+ return;
+ }
+
+ for (int idx = 0; params[idx] != NULL; idx++) {
+ if (strstr(params[idx], "restaurant_id=") == params[idx]) {
+ char *id = strchr(params[idx], '=') + 1;
+
+ char stmnt[64 + MAX_LEN_NAME + MAX_LEN_DATE + MAX_LEN_RESTAURANT_ID];
+ snprintf(stmnt,
+ (64 + MAX_LEN_NAME + MAX_LEN_DATE + MAX_LEN_RESTAURANT_ID)
+ * sizeof(char),
+ "INSERT INTO vote (voter, date, restaurant_id) "
+ "VALUES ('%s', '%s', %s)",
+ name, date, id);
+
+ char *zErrMsg = NULL;
+ int rc = sqlite3_exec(db, stmnt, NULL, 0, &zErrMsg);
+ if (rc != SQLITE_OK) {
+ _header_printed = 1;
+ header_status(500);
+ header_end();
+
+ fprintf(stderr, "SQL error: %s\n", zErrMsg);
+ sqlite3_free(zErrMsg);
+ return;
+ }
+ }
+ }
+
+ header_status(303);
+ header("Location", "/mittag.cgi/votes/");
+ header_end();
+}
+
+static void open_database(void)
+{
+ /* directory which contains the database must be writeable! */
+ /* TODO: pledge?
+ TODO: OPEN-Flag dependent on GET (READ ONLY) / POST (WRITE ONLY) */
+ if (sqlite3_open_v2("var/mittag.db", &db, SQLITE_OPEN_READWRITE, NULL)
+ != SQLITE_OK)
+ {
+ const char* err = sqlite3_errmsg(db);
+ fputs(err, stderr);
+ sqlite3_close(db);
+ err_exit("sqlite3_open error");
+ }
+}
+
+static void close_database(void)
+{
+ sqlite3_close(db);
+}
+
+int main(int argc, char **argv)
+{
+ Route *r;
+ char path_found = 0;
+ char *path = getenv("PATH_INFO") ? getenv("PATH_INFO") : "/";
+ for (r = routes; r < routes + LEN(routes); r++) {
+ regex_t preg;
+ if (regcomp(&preg, r->path, REG_EXTENDED | REG_NOSUB) != 0)
+ {
+ err_exit("regcomp");
+ }
+ path_found = regexec(&preg, path, 0, NULL, 0) != REG_NOMATCH;
+ regfree(&preg);
+ if (path_found && getenv("REQUEST_METHOD")) {
+ if (strcmp(getenv("REQUEST_METHOD"), r->method) == 0) {
+ open_database();
+ /* TODO: pass args */
+ r->func();
+ close_database();
+ return EXIT_SUCCESS;
+ }
+ }
+ }
+
+ !path_found ? header_status(404) : header_status(405);
+ header_end();
+ return EXIT_SUCCESS;
+}
diff --git a/src/mittag.h b/src/mittag.h
@@ -0,0 +1,26 @@
+/**
+ * Lunch Poll Legacy
+ * Copyright 2024 Matthias Balk
+ */
+
+#ifndef __MITTAG_H__
+#define __MITTAG_H__
+
+#include "http.h"
+
+static void get_votes(void);
+static void post_votes(void);
+static void open_database(void);
+static void close_database(void);
+
+static Route routes[] = {
+ { "^/votes/?$", "GET", get_votes },
+ { "^/votes/?$", "POST", post_votes },
+};
+
+/* maximum length of fields excluding the terminating null byte ('\0') */
+#define MAX_LEN_DATE 10
+#define MAX_LEN_NAME 64
+#define MAX_LEN_RESTAURANT_ID 5
+
+#endif /* __MITTAG_H__ */
diff --git a/src/utils.c b/src/utils.c
@@ -0,0 +1,24 @@
+/**
+ * Lunch Poll Legacy
+ * Copyright 2024 Matthias Balk
+ */
+
+#include <ctype.h>
+#include <string.h>
+
+
+/*
+ * 'str' will be changed if there are TRAILING whitespaces!
+ */
+char* trim(char *str)
+{
+ while (isspace(*str)) str++;
+
+ char *end = str + strlen(str) - 1;
+ while (end > str && isspace(*end)) {
+ *end = '\0';
+ end--;
+ }
+
+ return str;
+}
diff --git a/src/utils.h b/src/utils.h
@@ -0,0 +1,11 @@
+/**
+ * Lunch Poll Legacy
+ * Copyright 2024 Matthias Balk
+ */
+
+#ifndef __UTILS_H__
+#define __UTILS_H__
+
+char* trim(char *str);
+
+#endif /* __UTILS_H__ */
diff --git a/var/mittag.sql b/var/mittag.sql
@@ -0,0 +1,28 @@
+CREATE TABLE restaurant (
+ id INTEGER PRIMARY KEY,
+ name TEXT,
+ url TEXT,
+ category TEXT,
+ address TEXT,
+ days_open INT(3) DEFAULT 0
+);
+
+INSERT INTO restaurant VALUES
+ (NULL, 'Restaurant 1', 'http://restaurant-1.com/', '10-bistro', 'DE', 127),
+ (NULL, 'Restaurant 2', 'http://restaurant-2.com/', '10-bistro', 'DE', 28);
+
+CREATE TABLE vote (
+ id INTEGER PRIMARY KEY,
+ voter TEXT,
+ date TEXT,
+ restaurant_id INTEGER
+);
+
+CREATE TABLE comment (
+ id INTEGER PRIMARY KEY,
+ name TEXT,
+ comment TEXT,
+ date DATE,
+ valid_from DATE,
+ valid_until DATE
+);
