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