db.py (3955B)
1 # Copyright (c) 2019 Ivan Jelincic <parazyd@dyne.org> 2 # 3 # This file is part of diasporadiaries 4 # 5 # This program is free software: you can redistribute it and/or modify 6 # it under the terms of the GNU Affero General Public License as published by 7 # the Free Software Foundation, either version 3 of the License, or 8 # (at your option) any later version. 9 # 10 # This program is distributed in the hope that it will be useful, 11 # but WITHOUT ANY WARRANTY; without even the implied warranty of 12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13 # GNU Affero General Public License for more details. 14 # 15 # You should have received a copy of the GNU Affero General Public License 16 # along with this program. If not, see <http://www.gnu.org/licenses/>. 17 """ 18 Module for sqlite database operations. 19 """ 20 from sqlite3 import connect 21 22 from config import DB_PATH, sem 23 24 25 def initdb(dbpath): 26 """ 27 Initializes the sqlite3 database and returns the db context and cursor. 28 29 There are two tables, one which holds users, and the other holds stories. 30 """ 31 _dbctx = connect(dbpath, check_same_thread=False) 32 _db = _dbctx.cursor() 33 34 userdb_init = ''' 35 CREATE TABLE IF NOT EXISTS users ( 36 id integer PRIMARY KEY, 37 email text UNIQUE NOT NULL, 38 name text NOT NULL, 39 password text NOT NULL, 40 cap integer NOT NULL, 41 first_seen integer NOT NULL, 42 is_active integer NOT NULL 43 ); 44 ''' 45 46 storydb_init = ''' 47 CREATE TABLE IF NOT EXISTS stories ( 48 id integer PRIMARY KEY, 49 name text NOT NULL, 50 embark text NOT NULL, 51 disembark text NOT NULL, 52 email text, 53 city text, 54 about text, 55 story text NOT NULL, 56 timestamp integer NOT NULL, 57 visible integer NOT NULL, 58 deletekey text NOT NULL, 59 abstract text NOT NULL 60 ); 61 ''' 62 63 _db.execute(userdb_init) 64 _db.execute(storydb_init) 65 66 return _dbctx, _db 67 68 69 DBCTX, DB = initdb(DB_PATH) 70 71 72 def sql_select_col_where(col0, col1, val, table='stories'): 73 """ 74 Queries col0 where col1 = val. 75 """ 76 DB.execute(""" 77 SELECT %s FROM %s WHERE %s = '%s'; 78 """ % (col0, table, col1, val)) 79 return DB.fetchall() 80 81 82 def sql_select_col(col, table='stories'): 83 """ 84 Executes a SELECT query and returns the entire col. 85 """ 86 DB.execute("SELECT %s FROM %s;" % (col, table)) 87 return DB.fetchall() 88 89 90 def sql_delete_row_where(col, val, table='stories'): 91 """ 92 Executes a DELETE query where col=val. 93 """ 94 sem.acquire() 95 DB.execute(""" 96 DELETE 97 FROM %s 98 WHERE %s = '%s'; 99 """ % (table, col, val)) 100 DBCTX.commit() 101 sem.release() 102 103 104 def sql_update_row_where(vals, col, val, table='stories'): 105 """ 106 Executes an UPDATE query where col=val. 107 108 vals is a list of tuples. 109 """ 110 sem.acquire() 111 for i in vals: 112 DB.execute(""" 113 UPDATE %s 114 SET %s = ? 115 WHERE %s = ?; 116 """ % (table, i[0], col), (i[1], val)) 117 DBCTX.commit() 118 sem.release() 119 120 121 def sql_insert(args): 122 """ 123 Executes an sql INSERT query where args are VALUES to insert. 124 """ 125 # TODO: Make this more generic. 126 sem.acquire() 127 if len(args) == 12: 128 # Story 129 DB.execute(""" 130 INSERT INTO stories VALUES ( 131 ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? 132 ); 133 """, (args[0], args[1], args[2], args[3], args[4], args[5], 134 args[6], args[7], args[8], args[9], args[10], args[11])) 135 elif len(args) == 7: 136 # User 137 DB.execute(""" 138 INSERT INTO users VALUES ( 139 ?, ?, ?, ?, ?, ?, ? 140 ); 141 """, (args[0], args[1], args[2], args[3], args[4], args[5], 142 args[6])) 143 DBCTX.commit() 144 sem.release()