Mercurial > paster
annotate database.c @ 72:1a98bc0daa49
misc: update copyright years
author | David Demelier <markand@malikania.fr> |
---|---|
date | Wed, 01 Feb 2023 13:12:59 +0100 |
parents | ecb0b90d94d8 |
children | 9bfe5ce3cc45 |
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 * |
72
1a98bc0daa49
misc: update copyright years
David Demelier <markand@malikania.fr>
parents:
60
diff
changeset
|
4 * Copyright (c) 2020-2023 David Demelier <markand@malikania.fr> |
6
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" | |
50
520f57836ff3
pasterd: fix default visibility
David Demelier <markand@malikania.fr>
parents:
44
diff
changeset
|
43 " visible INTEGER DEFAULT 0,\n" |
0 | 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 | |
25
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
95 static const char *sql_search = |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
96 "SELECT uuid\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
97 " , title\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
98 " , author\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
99 " , language\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
100 " , code\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
101 " , strftime('%s', date) AS date\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
102 " , visible\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
103 " , duration\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
104 " FROM paste\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
105 " WHERE title like ?\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
106 " AND author like ?\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
107 " AND language like ?\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
108 " AND visible = 1\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
109 " ORDER BY date DESC\n" |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
110 " LIMIT ?\n"; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
111 |
1
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
112 /* sqlite3 use const unsigned char *. */ |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
113 static char * |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
114 dup(const unsigned char *s) |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
115 { |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
116 return estrdup(s ? (const char *)(s) : ""); |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
117 } |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
118 |
0 | 119 static void |
120 convert(sqlite3_stmt *stmt, struct paste *paste) | |
121 { | |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
122 paste->id = dup(sqlite3_column_text(stmt, 0)); |
1
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
123 paste->title = dup(sqlite3_column_text(stmt, 1)); |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
124 paste->author = dup(sqlite3_column_text(stmt, 2)); |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
125 paste->language = dup(sqlite3_column_text(stmt, 3)); |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
126 paste->code = dup(sqlite3_column_text(stmt, 4)); |
0 | 127 paste->timestamp = sqlite3_column_int64(stmt, 5); |
128 paste->visible = sqlite3_column_int(stmt, 6); | |
129 paste->duration = sqlite3_column_int64(stmt, 7); | |
130 } | |
131 | |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
132 static bool |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
133 exists(const char *id) |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
134 { |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
135 assert(id); |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
136 |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
137 sqlite3_stmt *stmt = NULL; |
44
f939d7864de4
pasterd: fix unique key constraint
David Demelier <markand@malikania.fr>
parents:
43
diff
changeset
|
138 bool ret = true; |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
139 |
44
f939d7864de4
pasterd: fix unique key constraint
David Demelier <markand@malikania.fr>
parents:
43
diff
changeset
|
140 if (sqlite3_prepare(db, sql_get, -1, &stmt, NULL) == SQLITE_OK) { |
f939d7864de4
pasterd: fix unique key constraint
David Demelier <markand@malikania.fr>
parents:
43
diff
changeset
|
141 sqlite3_bind_text(stmt, 1, id, -1, NULL); |
f939d7864de4
pasterd: fix unique key constraint
David Demelier <markand@malikania.fr>
parents:
43
diff
changeset
|
142 ret = sqlite3_step(stmt) == SQLITE_ROW; |
f939d7864de4
pasterd: fix unique key constraint
David Demelier <markand@malikania.fr>
parents:
43
diff
changeset
|
143 sqlite3_finalize(stmt); |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
144 } |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
145 |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
146 return ret; |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
147 } |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
148 |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
149 static const char * |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
150 create_id(void) |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
151 { |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
152 static const char table[] = "abcdefghijklmnopqrstuvwxyz1234567890"; |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
153 static char id[12]; |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
154 |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
155 for (int i = 0; i < sizeof (id); ++i) |
43
a2789cb8ebe6
pasterd: avoid creating null identifiers
David Demelier <markand@malikania.fr>
parents:
42
diff
changeset
|
156 id[i] = table[rand() % (sizeof (table) - 1)]; |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
157 |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
158 return id; |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
159 } |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
160 |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
161 static bool |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
162 set_id(struct paste *paste) |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
163 { |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
164 assert(paste); |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
165 |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
166 paste->id = NULL; |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
167 |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
168 /* |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
169 * Avoid infinite loop, we only try to create a new id in 30 steps. |
44
f939d7864de4
pasterd: fix unique key constraint
David Demelier <markand@malikania.fr>
parents:
43
diff
changeset
|
170 * |
f939d7864de4
pasterd: fix unique key constraint
David Demelier <markand@malikania.fr>
parents:
43
diff
changeset
|
171 * On error, the function `exist` returns true to indicate we should |
f939d7864de4
pasterd: fix unique key constraint
David Demelier <markand@malikania.fr>
parents:
43
diff
changeset
|
172 * not try to save with that id. |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
173 */ |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
174 int tries = 0; |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
175 |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
176 do { |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
177 free(paste->id); |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
178 paste->id = estrdup(create_id()); |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
179 } while (++tries < 30 && exists(paste->id)); |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
180 |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
181 return tries < 30; |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
182 } |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
183 |
0 | 184 bool |
185 database_open(const char *path) | |
186 { | |
187 assert(path); | |
188 | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
189 log_info("database: opening %s", path); |
0 | 190 |
191 if (sqlite3_open(path, &db) != SQLITE_OK) { | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
192 log_warn("database: unable to open %s: %s", path, sqlite3_errmsg(db)); |
0 | 193 return false; |
194 } | |
195 | |
30
4f60cf394839
pasterd: enable lock timeout
David Demelier <markand@malikania.fr>
parents:
25
diff
changeset
|
196 /* Wait for 30 seconds to lock the database. */ |
4f60cf394839
pasterd: enable lock timeout
David Demelier <markand@malikania.fr>
parents:
25
diff
changeset
|
197 sqlite3_busy_timeout(db, 30000); |
4f60cf394839
pasterd: enable lock timeout
David Demelier <markand@malikania.fr>
parents:
25
diff
changeset
|
198 |
0 | 199 if (sqlite3_exec(db, sql_init, NULL, NULL, NULL) != SQLITE_OK) { |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
200 log_warn("database: unable to initialize %s: %s", path, sqlite3_errmsg(db)); |
0 | 201 return false; |
202 } | |
203 | |
204 return true; | |
205 } | |
206 | |
207 bool | |
208 database_recents(struct paste *pastes, size_t *max) | |
209 { | |
210 assert(pastes); | |
211 assert(max); | |
212 | |
213 sqlite3_stmt *stmt = NULL; | |
214 | |
215 memset(pastes, 0, *max * sizeof (struct paste)); | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
216 log_debug("database: accessing most recents"); |
0 | 217 |
218 if (sqlite3_prepare(db, sql_recents, -1, &stmt, NULL) != SQLITE_OK || | |
219 sqlite3_bind_int64(stmt, 1, *max) != SQLITE_OK) | |
220 goto sqlite_err; | |
221 | |
222 size_t i = 0; | |
223 | |
224 for (; i < *max && sqlite3_step(stmt) == SQLITE_ROW; ++i) | |
225 convert(stmt, &pastes[i]); | |
226 | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
227 log_debug("database: found %zu pastes", i); |
0 | 228 sqlite3_finalize(stmt); |
229 *max = i; | |
230 | |
231 return true; | |
232 | |
233 sqlite_err: | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
234 log_warn("database: error (recents): %s\n", sqlite3_errmsg(db)); |
0 | 235 |
236 if (stmt) | |
237 sqlite3_finalize(stmt); | |
238 | |
239 return (*max = 0); | |
240 } | |
241 | |
242 bool | |
243 database_get(struct paste *paste, const char *uuid) | |
244 { | |
245 assert(paste); | |
246 assert(uuid); | |
247 | |
42
62361336c415
pasterd: return false when we don't find a paste
David Demelier <markand@malikania.fr>
parents:
38
diff
changeset
|
248 sqlite3_stmt* stmt = NULL; |
62361336c415
pasterd: return false when we don't find a paste
David Demelier <markand@malikania.fr>
parents:
38
diff
changeset
|
249 bool found = false; |
62361336c415
pasterd: return false when we don't find a paste
David Demelier <markand@malikania.fr>
parents:
38
diff
changeset
|
250 |
0 | 251 memset(paste, 0, sizeof (struct paste)); |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
252 log_debug("database: accessing paste with uuid: %s", uuid); |
0 | 253 |
254 if (sqlite3_prepare(db, sql_get, -1, &stmt, NULL) != SQLITE_OK || | |
255 sqlite3_bind_text(stmt, 1, uuid, -1, NULL) != SQLITE_OK) | |
256 goto sqlite_err; | |
257 | |
258 switch (sqlite3_step(stmt)) { | |
259 case SQLITE_ROW: | |
260 convert(stmt, paste); | |
42
62361336c415
pasterd: return false when we don't find a paste
David Demelier <markand@malikania.fr>
parents:
38
diff
changeset
|
261 found = true; |
0 | 262 break; |
263 case SQLITE_MISUSE: | |
264 case SQLITE_ERROR: | |
265 goto sqlite_err; | |
266 default: | |
267 break; | |
268 } | |
269 | |
270 sqlite3_finalize(stmt); | |
271 | |
42
62361336c415
pasterd: return false when we don't find a paste
David Demelier <markand@malikania.fr>
parents:
38
diff
changeset
|
272 return found; |
0 | 273 |
274 sqlite_err: | |
275 if (stmt) | |
276 sqlite3_finalize(stmt); | |
277 | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
278 log_warn("database: error (get): %s", sqlite3_errmsg(db)); |
0 | 279 |
280 return false; | |
281 } | |
282 | |
283 bool | |
284 database_insert(struct paste *paste) | |
285 { | |
286 assert(paste); | |
287 | |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
288 sqlite3_stmt *stmt = NULL; |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
289 |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
290 log_debug("database: creating new paste"); |
0 | 291 |
292 if (sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", NULL, NULL, NULL) != SQLITE_OK) { | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
293 log_warn("database: could not lock database: %s", sqlite3_errmsg(db)); |
0 | 294 return false; |
295 } | |
296 | |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
297 if (!set_id(paste)) { |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
298 log_warn("database: unable to randomize unique identifier"); |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
299 sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL); |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
300 return false; |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
301 } |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
302 |
0 | 303 if (sqlite3_prepare(db, sql_insert, -1, &stmt, NULL) != SQLITE_OK) |
304 goto sqlite_err; | |
305 | |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
306 sqlite3_bind_text(stmt, 1, paste->id, -1, SQLITE_STATIC); |
0 | 307 sqlite3_bind_text(stmt, 2, paste->title, -1, SQLITE_STATIC); |
308 sqlite3_bind_text(stmt, 3, paste->author, -1, SQLITE_STATIC); | |
309 sqlite3_bind_text(stmt, 4, paste->language, -1, SQLITE_STATIC); | |
310 sqlite3_bind_text(stmt, 5, paste->code, -1, SQLITE_STATIC); | |
311 sqlite3_bind_int(stmt, 6, paste->visible); | |
312 sqlite3_bind_int64(stmt, 7, paste->duration); | |
313 | |
314 if (sqlite3_step(stmt) != SQLITE_DONE) | |
315 goto sqlite_err; | |
316 | |
317 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); | |
318 sqlite3_finalize(stmt); | |
319 | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
320 log_info("database: new paste (%s) from %s expires in one %lld seconds", |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
321 paste->id, paste->author, paste->duration); |
0 | 322 |
323 return true; | |
324 | |
325 sqlite_err: | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
326 log_warn("database: error (insert): %s", sqlite3_errmsg(db)); |
0 | 327 sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL); |
328 | |
329 if (stmt) | |
330 sqlite3_finalize(stmt); | |
331 | |
38
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
332 free(paste->id); |
48834441dc86
pasterd: generate smaller identifiers, closes #2480 @1h
David Demelier <markand@malikania.fr>
parents:
30
diff
changeset
|
333 paste->id = NULL; |
0 | 334 |
335 return false; | |
336 } | |
337 | |
25
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
338 bool |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
339 database_search(struct paste *pastes, |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
340 size_t *max, |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
341 const char *title, |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
342 const char *author, |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
343 const char *language) |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
344 { |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
345 assert(pastes); |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
346 assert(max); |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
347 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
348 sqlite3_stmt *stmt = NULL; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
349 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
350 log_debug("database: searching title=%s, author=%s, language=%s", |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
351 title ? title : "", |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
352 author ? author : "", |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
353 language ? language : ""); |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
354 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
355 memset(pastes, 0, *max * sizeof (struct paste)); |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
356 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
357 /* Select everything if not specified. */ |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
358 title = title ? title : "%"; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
359 author = author ? author : "%"; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
360 language = language ? language : "%"; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
361 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
362 if (sqlite3_prepare(db, sql_search, -1, &stmt, NULL) != SQLITE_OK) |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
363 goto sqlite_err; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
364 if (sqlite3_bind_text(stmt, 1, title, -1, NULL) != SQLITE_OK) |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
365 goto sqlite_err; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
366 if (sqlite3_bind_text(stmt, 2, author, -1, NULL) != SQLITE_OK) |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
367 goto sqlite_err; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
368 if (sqlite3_bind_text(stmt, 3, language, -1, NULL) != SQLITE_OK) |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
369 goto sqlite_err; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
370 if (sqlite3_bind_int64(stmt, 4, *max) != SQLITE_OK) |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
371 goto sqlite_err; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
372 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
373 size_t i = 0; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
374 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
375 for (; i < *max && sqlite3_step(stmt) == SQLITE_ROW; ++i) |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
376 convert(stmt, &pastes[i]); |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
377 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
378 log_debug("database: found %zu pastes", i); |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
379 sqlite3_finalize(stmt); |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
380 *max = i; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
381 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
382 return true; |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
383 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
384 sqlite_err: |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
385 log_warn("database: error (search): %s\n", sqlite3_errmsg(db)); |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
386 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
387 if (stmt) |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
388 sqlite3_finalize(stmt); |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
389 |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
390 return (*max = 0); |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
391 } |
f4e8a7920b94
pasterd: implement search, closes #2474 @30m
David Demelier <markand@malikania.fr>
parents:
21
diff
changeset
|
392 |
0 | 393 void |
394 database_clear(void) | |
395 { | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
396 log_debug("database: clearing deprecated pastes"); |
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
397 |
0 | 398 if (sqlite3_exec(db, sql_clear, NULL, NULL, NULL) != SQLITE_OK) |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
399 log_warn("database: error (clear): %s\n", sqlite3_errmsg(db)); |
0 | 400 } |
401 | |
402 void | |
403 database_finish(void) | |
404 { | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
405 log_debug("database: closing"); |
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
406 |
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
407 if (db) { |
0 | 408 sqlite3_close(db); |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
409 db = NULL; |
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
410 } |
0 | 411 } |