Mercurial > paster
annotate database.c @ 17:09d2f54562ec
pasterd-clean: initial import, closes #2473
| author | David Demelier <markand@malikania.fr> |
|---|---|
| date | Thu, 06 Feb 2020 14:17:14 +0100 |
| parents | 98ef2713eb0d |
| children | 21c103c33ac9 |
| rev | line source |
|---|---|
|
6
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
1 /* |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
2 * database.c -- sqlite storage |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
3 * |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
4 * Copyright (c) 2020 David Demelier <markand@malikania.fr> |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
5 * |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
6 * Permission to use, copy, modify, and/or distribute this software for any |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
7 * purpose with or without fee is hereby granted, provided that the above |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
8 * copyright notice and this permission notice appear in all copies. |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
9 * |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
10 * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
11 * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
12 * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
13 * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
14 * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
15 * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
16 * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
17 */ |
|
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
18 |
| 0 | 19 #include <assert.h> |
| 20 #include <stdlib.h> | |
| 21 #include <stdio.h> | |
| 22 #include <string.h> | |
| 23 | |
| 24 #include <sqlite3.h> | |
| 25 | |
| 26 #include "database.h" | |
| 27 #include "log.h" | |
| 28 #include "paste.h" | |
| 29 #include "util.h" | |
| 30 | |
| 31 static sqlite3 *db; | |
| 32 | |
| 33 static const char *sql_init = | |
| 34 "BEGIN EXCLUSIVE TRANSACTION;\n" | |
| 35 "\n" | |
| 36 "CREATE TABLE IF NOT EXISTS paste(\n" | |
| 37 " uuid TEXT PRIMARY KEY,\n" | |
| 38 " title TEXT,\n" | |
| 39 " author TEXT,\n" | |
| 40 " language TEXT,\n" | |
| 41 " code TEXT,\n" | |
| 42 " date INT DEFAULT CURRENT_TIMESTAMP,\n" | |
| 43 " visible INTEGER DEFAULT 1,\n" | |
| 44 " duration INT\n" | |
| 45 ");\n" | |
| 46 "\n" | |
| 47 "END TRANSACTION"; | |
| 48 | |
| 49 static const char *sql_get = | |
| 50 "SELECT uuid\n" | |
| 51 " , title\n" | |
| 52 " , author\n" | |
| 53 " , language\n" | |
| 54 " , code\n" | |
| 55 " , strftime('%s', date)\n" | |
| 56 " , visible\n" | |
| 57 " , duration\n" | |
| 58 " FROM paste\n" | |
|
1
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
59 " WHERE uuid = ?"; |
| 0 | 60 |
| 61 static const char *sql_insert = | |
| 62 "INSERT INTO paste(\n" | |
| 63 " uuid,\n" | |
| 64 " title,\n" | |
| 65 " author,\n" | |
| 66 " language,\n" | |
| 67 " code,\n" | |
| 68 " visible,\n" | |
| 69 " duration\n" | |
| 70 ") VALUES (?, ?, ?, ?, ?, ?, ?)"; | |
| 71 | |
| 72 static const char *sql_recents = | |
| 73 "SELECT uuid\n" | |
| 74 " , title\n" | |
| 75 " , author\n" | |
| 76 " , language\n" | |
| 77 " , code\n" | |
| 78 " , strftime('%s', date) AS date\n" | |
| 79 " , visible\n" | |
| 80 " , duration\n" | |
| 81 " FROM paste\n" | |
|
2
65607ae124b1
pasterd: implement /new
David Demelier <markand@malikania.fr>
parents:
1
diff
changeset
|
82 " WHERE visible = 1\n" |
|
5
f455893bf0b0
pasterd: show dates in /
David Demelier <markand@malikania.fr>
parents:
2
diff
changeset
|
83 " ORDER BY date DESC\n" |
| 0 | 84 " LIMIT ?\n"; |
| 85 | |
| 86 static const char *sql_clear = | |
| 87 "BEGIN EXCLUSIVE TRANSACTION;\n" | |
| 88 "\n" | |
| 89 "DELETE\n" | |
| 90 " FROM paste\n" | |
| 91 " WHERE strftime('%s', 'now') - strftime('%s', date) >= duration;" | |
| 92 "\n" | |
| 93 "END TRANSACTION"; | |
| 94 | |
|
1
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
95 /* sqlite3 use const unsigned char *. */ |
|
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
96 static char * |
|
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
97 dup(const unsigned char *s) |
|
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
98 { |
|
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
99 return estrdup(s ? (const char *)(s) : ""); |
|
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
100 } |
|
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
101 |
| 0 | 102 static const char * |
| 103 create_id(void) | |
| 104 { | |
| 105 static char uuid[256]; | |
| 106 | |
| 107 /* | |
| 108 * Not a very strong generation but does not require to link against | |
| 109 * util-linux. | |
| 110 * | |
| 111 * See https://stackoverflow.com/questions/2174768/generating-random-uuids-in-linux | |
| 112 */ | |
| 113 sprintf(uuid, "%x%x-%x-%x-%x-%x%x%x", | |
| 114 rand(), rand(), | |
| 115 rand(), | |
| 116 ((rand() & 0x0fff) | 0x4000), | |
| 117 rand() % 0x3fff + 0x8000, | |
| 118 rand(), rand(), rand()); | |
| 119 | |
| 120 return uuid; | |
| 121 } | |
| 122 | |
| 123 static void | |
| 124 convert(sqlite3_stmt *stmt, struct paste *paste) | |
| 125 { | |
|
1
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
126 paste->uuid = dup(sqlite3_column_text(stmt, 0)); |
|
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
127 paste->title = dup(sqlite3_column_text(stmt, 1)); |
|
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
128 paste->author = dup(sqlite3_column_text(stmt, 2)); |
|
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
129 paste->language = dup(sqlite3_column_text(stmt, 3)); |
|
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
130 paste->code = dup(sqlite3_column_text(stmt, 4)); |
| 0 | 131 paste->timestamp = sqlite3_column_int64(stmt, 5); |
| 132 paste->visible = sqlite3_column_int(stmt, 6); | |
| 133 paste->duration = sqlite3_column_int64(stmt, 7); | |
| 134 } | |
| 135 | |
| 136 bool | |
| 137 database_open(const char *path) | |
| 138 { | |
| 139 assert(path); | |
| 140 | |
| 141 log_debug("opening database: %s\n", path); | |
| 142 | |
| 143 if (sqlite3_open(path, &db) != SQLITE_OK) { | |
| 144 log_warn("unable to open %s: %s\n", path, sqlite3_errmsg(db)); | |
| 145 return false; | |
| 146 } | |
| 147 | |
| 148 if (sqlite3_exec(db, sql_init, NULL, NULL, NULL) != SQLITE_OK) { | |
| 149 log_warn("unable to initialize %s: %s\n", path, sqlite3_errmsg(db)); | |
| 150 return false; | |
| 151 } | |
| 152 | |
| 153 log_debug("successfully opened database: %s\n", path); | |
| 154 | |
| 155 return true; | |
| 156 } | |
| 157 | |
| 158 bool | |
| 159 database_recents(struct paste *pastes, size_t *max) | |
| 160 { | |
| 161 assert(pastes); | |
| 162 assert(max); | |
| 163 | |
| 164 sqlite3_stmt *stmt = NULL; | |
| 165 | |
| 166 memset(pastes, 0, *max * sizeof (struct paste)); | |
| 167 | |
| 168 if (sqlite3_prepare(db, sql_recents, -1, &stmt, NULL) != SQLITE_OK || | |
| 169 sqlite3_bind_int64(stmt, 1, *max) != SQLITE_OK) | |
| 170 goto sqlite_err; | |
| 171 | |
| 172 size_t i = 0; | |
| 173 | |
| 174 for (; i < *max && sqlite3_step(stmt) == SQLITE_ROW; ++i) | |
| 175 convert(stmt, &pastes[i]); | |
| 176 | |
| 177 sqlite3_finalize(stmt); | |
| 178 *max = i; | |
| 179 | |
| 180 return true; | |
| 181 | |
| 182 sqlite_err: | |
| 183 log_warn("database error (recents): %s\n", sqlite3_errmsg(db)); | |
| 184 | |
| 185 if (stmt) | |
| 186 sqlite3_finalize(stmt); | |
| 187 | |
| 188 return (*max = 0); | |
| 189 } | |
| 190 | |
| 191 bool | |
| 192 database_get(struct paste *paste, const char *uuid) | |
| 193 { | |
| 194 assert(paste); | |
| 195 assert(uuid); | |
| 196 | |
| 197 memset(paste, 0, sizeof (struct paste)); | |
| 198 | |
| 199 sqlite3_stmt* stmt = NULL; | |
| 200 | |
| 201 if (sqlite3_prepare(db, sql_get, -1, &stmt, NULL) != SQLITE_OK || | |
| 202 sqlite3_bind_text(stmt, 1, uuid, -1, NULL) != SQLITE_OK) | |
| 203 goto sqlite_err; | |
| 204 | |
| 205 switch (sqlite3_step(stmt)) { | |
| 206 case SQLITE_ROW: | |
| 207 convert(stmt, paste); | |
| 208 break; | |
| 209 case SQLITE_MISUSE: | |
| 210 case SQLITE_ERROR: | |
| 211 goto sqlite_err; | |
| 212 default: | |
| 213 break; | |
| 214 } | |
| 215 | |
| 216 sqlite3_finalize(stmt); | |
| 217 | |
| 218 return true; | |
| 219 | |
| 220 sqlite_err: | |
| 221 if (stmt) | |
| 222 sqlite3_finalize(stmt); | |
| 223 | |
| 224 log_warn("database error (get): %s", sqlite3_errmsg(db)); | |
| 225 | |
| 226 return false; | |
| 227 } | |
| 228 | |
| 229 bool | |
| 230 database_insert(struct paste *paste) | |
| 231 { | |
| 232 assert(paste); | |
| 233 | |
| 234 sqlite3_stmt* stmt = NULL; | |
| 235 | |
| 236 if (sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", NULL, NULL, NULL) != SQLITE_OK) { | |
| 237 log_warn("could not lock database: %s\n", sqlite3_errmsg(db)); | |
| 238 return false; | |
| 239 } | |
| 240 | |
| 241 if (sqlite3_prepare(db, sql_insert, -1, &stmt, NULL) != SQLITE_OK) | |
| 242 goto sqlite_err; | |
| 243 | |
| 244 /* Create a new uuid first. */ | |
| 245 paste->uuid = estrdup(create_id()); | |
| 246 | |
| 247 sqlite3_bind_text(stmt, 1, paste->uuid, -1, SQLITE_STATIC); | |
| 248 sqlite3_bind_text(stmt, 2, paste->title, -1, SQLITE_STATIC); | |
| 249 sqlite3_bind_text(stmt, 3, paste->author, -1, SQLITE_STATIC); | |
| 250 sqlite3_bind_text(stmt, 4, paste->language, -1, SQLITE_STATIC); | |
| 251 sqlite3_bind_text(stmt, 5, paste->code, -1, SQLITE_STATIC); | |
| 252 sqlite3_bind_int(stmt, 6, paste->visible); | |
| 253 sqlite3_bind_int64(stmt, 7, paste->duration); | |
| 254 | |
| 255 if (sqlite3_step(stmt) != SQLITE_DONE) | |
| 256 goto sqlite_err; | |
| 257 | |
| 258 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); | |
| 259 sqlite3_finalize(stmt); | |
| 260 | |
| 261 log_debug("new paste (%s) from %s expires in one %lld seconds", | |
| 262 paste->uuid, paste->author, paste->duration); | |
| 263 | |
| 264 return true; | |
| 265 | |
| 266 sqlite_err: | |
| 267 log_warn("database error (insert): %s", sqlite3_errmsg(db)); | |
| 268 sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL); | |
| 269 | |
| 270 if (stmt) | |
| 271 sqlite3_finalize(stmt); | |
| 272 | |
| 273 free(paste->uuid); | |
| 274 paste->uuid = NULL; | |
| 275 | |
| 276 return false; | |
| 277 } | |
| 278 | |
| 279 void | |
| 280 database_clear(void) | |
| 281 { | |
| 282 if (sqlite3_exec(db, sql_clear, NULL, NULL, NULL) != SQLITE_OK) | |
| 283 log_warn("database error (clear): %s\n", sqlite3_errmsg(db)); | |
| 284 } | |
| 285 | |
| 286 void | |
| 287 database_finish(void) | |
| 288 { | |
| 289 if (db) | |
| 290 sqlite3_close(db); | |
| 291 } |