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