commit dd799fd5be4bf390f8896ec74f95e2f6d773dc36 Author: hiro <hiro@torproject.org> Date: Fri Mar 1 14:48:42 2019 +0100 Update how Gettor communicates with DB Update scripts --- README.md | 3 +- bin/gettor_service | 5 +- gettor/services/email/sendmail.py | 2 +- gettor/utils/db.py | 204 +++++++++++++++----------------------- 4 files changed, 84 insertions(+), 130 deletions(-) diff --git a/README.md b/README.md index d219ba1..41acdeb 100644 --- a/README.md +++ b/README.md @@ -33,7 +33,7 @@ Here is a list of the main goals the new GetTor should accomplish: Installing GetTor ================= -WORKON_HOME=${HOME}/.virtualenvs +WORKON_HOME=${HOME}/venv export WORKON_HOME mkdir -p $WORKON_HOME source $(which virtualenvwrapper.sh) @@ -46,6 +46,7 @@ without exiting the shell, do ``$ deactivate``. export PYTHONPATH=$PYTHONPATH:${VIRTUAL_ENV}/lib/python/site-packages +$ ./scripts/create_db $ ./bin/gettor_service start diff --git a/bin/gettor_service b/bin/gettor_service index 8b636db..ae85f56 100755 --- a/bin/gettor_service +++ b/bin/gettor_service @@ -10,12 +10,9 @@ # # :license: This is Free Software. See LICENSE for license information. -source venv/bin/activate -alias twistd3="venv/bin/twistd" - case "$1" in start) - twistd3 --python=scripts/gettor --logfile=log/gettor.log --pidfile=gettor.pid + twistd --python=scripts/gettor --logfile=log/gettor.log --pidfile=gettor.pid ;; stop) kill -INT `cat gettor.pid` diff --git a/gettor/services/email/sendmail.py b/gettor/services/email/sendmail.py index 06e49b0..22ec3e9 100644 --- a/gettor/services/email/sendmail.py +++ b/gettor/services/email/sendmail.py @@ -21,7 +21,7 @@ from email import mime from twisted.internet import defer from twisted.mail.smtp import sendmail -from ...utils.db import DB +from ...utils.db import SQLite3 as DB from ...utils.commons import log diff --git a/gettor/utils/db.py b/gettor/utils/db.py index 2d40f5d..ee4c398 100644 --- a/gettor/utils/db.py +++ b/gettor/utils/db.py @@ -2,131 +2,87 @@ # # This file is part of GetTor, a Tor Browser distribution system. # -# :authors: Israel Leiva <ilv@riseup.net> +# :authors: isra <ilv@torproject.org> # see also AUTHORS file # -# :copyright: (c) 2008-2014, The Tor Project, Inc. -# (c) 2014, Israel Leiva -# # :license: This is Free Software. See LICENSE for license information. -import time -import sqlite3 -import datetime - -"""DB interface for comunicating with sqlite3""" - - -class DBError(Exception): - pass - - -class DB(object): - """ - - Public methods: - - add_request(): add a request to the database (requests table). - get_user(): get user info from the database (users table). - add_user(): add a user to the database (users table). - update_user(): update a user on the database (users table). - - Exceptions: - - DBError: Something went wrong when trying to connect/interact - with the database. - - """ - - def __init__(self, dbname): - """Create a new db object. - :param: dbname (string) the path of the database. - """ - self.dbname = dbname - - def connect(self): - """ """ - try: - self.con = sqlite3.connect(self.dbname) - self.con.row_factory = sqlite3.Row - except sqlite3.Error as e: - raise DBError("%s" % str(e)) - - def add_request(self): - """Add a request to the database. - - For now we just count the number of requests we have received so far. - - """ - try: - with self.con: - cur = self.con.cursor() - cur.execute("SELECT counter FROM requests WHERE id = 1") - row = cur.fetchone() - if row: - cur.execute("UPDATE requests SET counter=? WHERE id=?", - (row['counter']+1, 1)) - else: - cur.execute("INSERT INTO requests VALUES(?, ?)", (1, 1)) - except sqlite3.Error as e: - raise DBError("%s" % str(e)) - - def get_user(self, user, service): - """Get user info from the database. - - :param: user (string) unique (hashed) string that represents the user. - :param: service (string) the service related to the user (e.g. SMTP). - - :return: (dict) the user information, with fields as indexes - (e.g. row['user']). - - """ - try: - with self.con: - cur = self.con.cursor() - cur.execute("SELECT * FROM users WHERE id =? AND service =?", - (user, service)) - - row = cur.fetchone() - return row - except sqlite3.Error as e: - raise DBError("%s" % str(e)) - - def add_user(self, user, service, blocked): - """Add a user to the database. - - We add a user with one 'times' and the current time as 'last_request' - by default. - - :param: user (string) unique (hashed) string that represents the user. - :param: service (string) the service related to the user (e.g. SMTP). - :param: blocked (int) one if user is blocked, zero otherwise. - - """ - try: - with self.con: - cur = self.con.cursor() - cur.execute("INSERT INTO users VALUES(?,?,?,?,?)", - (user, service, 1, blocked, str(time.time()))) - except sqlite3.Error as e: - raise DBError("%s" % str(e)) - - def update_user(self, user, service, times, blocked): - """Update a user on the database. - - We update the user info with the current time as 'last_request'. - - :param: user (string) unique (hashed) string that represents the user. - :param: service (string) the service related to the user (e.g. SMTP). - :param: times (int) the number of requests the user has made. - :param: blocked (int) one if user is blocked, zero otherwise. - - """ - try: - with self.con: - cur = self.con.cursor() - cur.execute("UPDATE users SET times =?, blocked =?," - " last_request =? WHERE id =? AND service =?", - (times, blocked, str(time.time()), user, service)) - except sqlite3.Error as e: - raise DBError("%s" % str(e)) +from __future__ import absolute_import + +from datetime import datetime + +from twisted.python import log +from twisted.enterprise import adbapi + +class SQLite3(object): + """ + + """ + def __init__(self, dbname): + """Constructor.""" + self.dbpool = adbapi.ConnectionPool( + "sqlite3", dbname, check_same_thread=False + ) + + def query_callback(self, results=None): + """ """ + log.msg("Database query executed successfully.") + return results + + def query_errback(self, error=None): + """ """ + if error: + log.msg("Database error: {}".format(error)) + return None + + def new_request(self, id, command, service, platform, date, status): + """ """ + query = "INSERT INTO requests VALUES(?, ?, ?, ?, ?, ?)" + + return self.dbpool.runQuery( + query, (id, command, platform, service, date, status) + ).addCallback(self.query_callback).addErrback(self.query_errback) + + def get_requests(self, status, command, service): + """ """ + query = "SELECT * FROM requests WHERE service=? AND command=? AND "\ + "status = ?" + + return self.dbpool.runQuery( + query, (service, command, status) + ).addCallback(self.query_callback).addErrback(self.query_errback) + + def get_num_requests(self, id, service): + """ """ + query = "SELECT COUNT(rowid) FROM requests WHERE id=? AND service=?" + + return self.dbpool.runQuery( + query, (id, service) + ).addCallback(self.query_callback).addErrback(self.query_errback) + + def update_request(self, id, hid, status, service, date): + """ """ + query = "UPDATE requests SET id=?, status=? WHERE id=? AND "\ + "service=? AND date=?" + + return self.dbpool.runQuery( + query, (hid, status, id, service, date) + ).addCallback(self.query_callback).addErrback(self.query_errback) + + def update_stats(self, command, service, platform=None): + """ """ + now_str = datetime.now().strftime("%Y%m%d") + query = "REPLACE INTO stats(num_requests, platform, "\ + "command, service, date) VALUES(COALESCE((SELECT num_requests FROM stats "\ + "WHERE date=?)+1, 0), ?, ?, ?, ?) "\ + + return self.dbpool.runQuery( + query, (now_str,platform, command, service, now_str) + ).addCallback(self.query_callback).addErrback(self.query_errback) + + def get_links(self, platform, status): + """ """ + query = "SELECT * FROM links WHERE platform=? AND status=?" + return self.dbpool.runQuery( + query, (platform, status) + ).addCallback(self.query_callback).addErrback(self.query_errback)