commit cd4960e1493308198d655c3b6502879852bd5dce Author: Arturo Filastò art@fuffa.org Date: Sat Nov 10 14:45:56 2012 +0100

Add database support to oonib * We have function to automatically create tables based on the Storm db definition (borrowed from code I wrote for GLBackend) * Start plotting out the structure of the database support. --- oonib/__init__.py | 22 ++++++++ oonib/config.py | 7 +++ oonib/db/__init__.py | 30 ++++++++++ oonib/db/tables.py | 123 +++++++++++++++++++++++++++++++++++++++++++ oonib/models.py | 122 ++++++++++++++++++++++++++++++++++++++++++ oonib/oonibackend.py | 13 +++-- oonib/report/db/__init__.py | 31 ----------- oonib/report/models.py | 122 ------------------------------------------ 8 files changed, 311 insertions(+), 159 deletions(-)

diff --git a/oonib/__init__.py b/oonib/__init__.py index e69de29..f672728 100644 --- a/oonib/__init__.py +++ b/oonib/__init__.py @@ -0,0 +1,22 @@ +# -*- encoding: utf-8 -*- +# +# :authors: Arturo Filastò +# :licence: see LICENSE +""" +In here we shall keep track of all variables and objects that should be +instantiated only once and be common to pieces of GLBackend code. +""" +__all__ = ['database', 'db_threadpool'] + +from twisted.python.threadpool import ThreadPool + +from storm.uri import URI +from storm.twisted.transact import Transactor +from storm.databases.sqlite import SQLite + +from oonib import config + +database = SQLite(URI(config.main.database_uri)) +db_threadpool = ThreadPool(0, config.main.db_thread_pool_size) +db_threadpool.start() +transactor = Transactor(db_threadpool) diff --git a/oonib/config.py b/oonib/config.py index 7304ef7..70cb457 100644 --- a/oonib/config.py +++ b/oonib/config.py @@ -1,6 +1,12 @@ from ooni.utils import Storage import os

+def get_root_path(): + this_directory = os.path.dirname(__file__) + root = os.path.join(this_directory, '..') + root = os.path.abspath(root) + return root + # XXX convert this to something that is a proper config file main = Storage() main.reporting_port = 8888 @@ -9,6 +15,7 @@ main.dns_udp_port = 5354 main.dns_tcp_port = 8002 main.daphn3_port = 9666 main.server_version = "Apache" +main.database_uri = "sqlite:"+get_root_path()+"oonib_test_db.db" #main.ssl_private_key = /path/to/data/private.key #main.ssl_certificate = /path/to/data/certificate.crt #main.ssl_port = 8433 diff --git a/oonib/db/__init__.py b/oonib/db/__init__.py new file mode 100644 index 0000000..8ddaff0 --- /dev/null +++ b/oonib/db/__init__.py @@ -0,0 +1,30 @@ +__all__ = ['createTables', 'database', 'transactor'] + +from twisted.python.threadpool import ThreadPool +from twisted.internet.defer import inlineCallbacks, returnValue, Deferred + +from storm.locals import Store +from storm.uri import URI +from storm.databases.sqlite import SQLite + +from oonib import database, transactor +from ooni.utils import log + +@inlineCallbacks +def createTables(): + """ + XXX this is to be refactored and only exists for experimentation. + """ + from oonib.db import models + for model_name in models.__all__: + try: + model = getattr(m, model_name) + except Exception, e: + log.err("Error in db initting") + log.err(e) + try: + log.debug("Creating %s" % model) + yield tables.runCreateTable(model, transactor, database) + except Exception, e: + log.debug(str(e)) + diff --git a/oonib/db/tables.py b/oonib/db/tables.py new file mode 100644 index 0000000..908a295 --- /dev/null +++ b/oonib/db/tables.py @@ -0,0 +1,123 @@ +# -*- encoding: utf-8 -*- +# +# :authors: Arturo Filastò +# :licence: see LICENSE + +from twisted.internet.defer import inlineCallbacks + +from storm.locals import Store +from storm.properties import PropertyColumn +from storm.exceptions import StormError + +from storm.variables import BoolVariable, DateTimeVariable, DateVariable +from storm.variables import DecimalVariable, EnumVariable +from storm.variables import FloatVariable, IntVariable, RawStrVariable +from storm.variables import UnicodeVariable, JSONVariable, PickleVariable + +def variableToSQLite(var_type): + """ + We take as input a storm.variable and we output the SQLite string it + represents. + """ + sqlite_type = "VARCHAR" + if isinstance(var_type, BoolVariable): + sqlite_type = "INTEGER" + elif isinstance(var_type, DateTimeVariable): + pass + sqlite_type = "" + elif isinstance(var_type, DateVariable): + pass + elif isinstance(var_type, DecimalVariable): + pass + elif isinstance(var_type, EnumVariable): + sqlite_type = "BLOB" + elif isinstance(var_type, FloatVariable): + sqlite_type = "REAL" + elif isinstance(var_type, IntVariable): + sqlite_type = "INTEGER" + elif isinstance(var_type, RawStrVariable): + sqlite_type = "BLOB" + elif isinstance(var_type, UnicodeVariable): + pass + elif isinstance(var_type, JSONVariable): + sqlite_type = "BLOB" + elif isinstance(var_type, PickleVariable): + sqlite_type = "BLOB" + return "%s" % sqlite_type + +def varsToParametersSQLite(variables, primary_keys): + """ + Takes as input a list of variables (convered to SQLite syntax and in the + form of strings) and primary_keys. + Outputs these variables converted into paramter syntax for SQLites. + + ex. + variables: ["var1 INTEGER", "var2 BOOL", "var3 INTEGER"] + primary_keys: ["var1"] + + output: "(var1 INTEGER, var2 BOOL, var3 INTEGER PRIMARY KEY (var1))" + """ + params = "(" + for var in variables[:-1]: + params += "%s %s, " % var + if len(primary_keys) > 0: + params += "%s %s, " % variables[-1] + params += "PRIMARY KEY (" + for key in primary_keys[:-1]: + params += "%s, " % key + params += "%s))" % primary_keys[-1] + else: + params += "%s %s)" % variables[-1] + return params + +def generateCreateQuery(model): + """ + This takes as input a Storm model and outputs the creation query for it. + """ + query = "CREATE TABLE "+ model.__storm_table__ + " " + + variables = [] + primary_keys = [] + + for attr in dir(model): + a = getattr(model, attr) + if isinstance(a, PropertyColumn): + var_stype = a.variable_factory() + var_type = variableToSQLite(var_stype) + name = a.name + variables.append((name, var_type)) + if a.primary: + primary_keys.append(name) + + query += varsToParametersSQLite(variables, primary_keys) + return query + +def createTable(model, transactor, database): + """ + Create the table for the specified model. + Specification of a transactor and database is useful in unittesting. + """ + if not transactor: + from oonib.db import transactor + if not database: + from oonib.db import database + store = Store(database) + create_query = generateCreateQuery(model) + try: + store.execute(create_query) + # XXX trap the specific error that is raised when the table exists + except StormError, e: + print "Failed to create table!" + print e + store.close() + store.commit() + store.close() + +@inlineCallbacks +def runCreateTable(model, transactor=None, database=None): + """ + Runs the table creation query wrapped in a transaction. + Transactions run in a separate thread. + """ + yield transactor.run(createTable, model, transactor, database) + diff --git a/oonib/models.py b/oonib/models.py new file mode 100644 index 0000000..ddf79a3 --- /dev/null +++ b/oonib/models.py @@ -0,0 +1,122 @@ +__all__ = ['Report', 'TestHelperTMP'] +from storm.twisted.transact import transact +from storm.locals import * + +from oonib.report.db import getStore, transactor + +def generateReportID(): + size = 100 + report_id = ''.join(random.choice(string.ascii_letters) for x in range(size)) + return report_id + +class OModel(object): + + transactor = transactor + + def getStore(self): + return Store(database) + + @transact + def create(self, query): + store = Store(database) + store.execute(query) + store.commit() + + @transact + def save(self): + store = getStore() + store.add(self) + store.commit() + +class Report(OModel): + """ + This represents an OONI Report as stored in the database. + + report_id: this is generated by the backend and is used by the client to + reference a previous report and append to it. It should be + treated as a shared secret between the probe and backend. + + software_name: this indicates the name of the software performing the test + (this will default to ooniprobe) + + software_version: this is the version number of the software running the + test. + + test_name: the name of the test on which the report is being created. + + test_version: indicates the version of the test + + progress: what is the current progress of the report. This allows clients + to report event partial reports up to a certain percentage of + progress. Once the report is complete progress will be 100. + + content: what is the content of the report. If the current progress is less + than 100 we should append to the YAML data structure that is + currently stored in such field. + """ + __storm_table__ = 'reports' + + createQuery = "CREATE TABLE " + __storm_table__ +\ + "(id INTEGER PRIMARY KEY, report_id VARCHAR, software_name VARCHAR,"\ + "software_version VARCHAR, test_name VARCHAR, test_version VARCHAR,"\ + "progress VARCHAR, content VARCHAR)" + + + id = Int(primary=True) + + report_id = Unicode() + + software_name = Unicode() + software_version = Unicode() + test_name = Unicode() + test_version = Unicode() + progress = Int() + + content = Unicode() + + @transact + def new(report): + store = self.getStore() + + print "Creating new report %s" % report + + report_id = generateReportID() + new_report = models.Report() + new_report.report_id = unicode(report_id) + + new_report.software_name = report['software_name'] + new_report.software_version = report['software_version'] + new_report.test_name = report['test_name'] + new_report.test_version = report['test_version'] + new_report.progress = report['progress'] + + if 'content' in report: + new_report.content = report['content'] + + print "Report: %s" % report + + store.add(new_report) + try: + store.commit() + except: + store.close() + + defer.returnValue({'backend_version': backend_version, 'report_id': + report_id}) + + +class TestHelperTMP(OModel): + __storm_table__ = 'testhelpertmp' + + createQuery = "CREATE TABLE " + __storm_table__ +\ + "(id INTEGER PRIMARY KEY, report_id VARCHAR, test_helper VARCHAR,"\ + " client_ip VARCHAR, creation_time VARCHAR)" + + id = Int(primary=True) + + report_id = Unicode() + + test_helper = Unicode() + client_ip = Unicode() + + creation_time = Date() diff --git a/oonib/oonibackend.py b/oonib/oonibackend.py index 9964854..799dcf0 100644 --- a/oonib/oonibackend.py +++ b/oonib/oonibackend.py @@ -1,10 +1,9 @@ -""" - ooni backend - ************ +# ooni backend +# ************ +# +# This is the backend system responsible for running certain services that are +# useful for censorship detection.

- This is the backend system responsible for running certain services that - are useful for censorship detection. -""" import json import random import string @@ -29,6 +28,7 @@ from oonib import config from oonib.testhelpers.httph import HTTPReturnJSONHeadersHelper from oonib.testhelpers.dns import ProxyDNSServer #from oonib.testhelpers.daphn3 import Daphn3Server +from oonib import db_threadpool

from cyclone import web

@@ -70,3 +70,4 @@ if config.helpers.http_return_request_port: internet.TCPServer(int(config.helpers.http_return_request_port), HTTPReturnJSONHeadersHelper).setServiceParent(serviceCollection)

+reactor.addSystemEventTrigger('after', 'shutdown', db_threadpool.stop) diff --git a/oonib/report/db/__init__.py b/oonib/report/db/__init__.py deleted file mode 100644 index 383b24b..0000000 --- a/oonib/report/db/__init__.py +++ /dev/null @@ -1,31 +0,0 @@ -from twisted.python import log -from twisted.python.threadpool import ThreadPool -from twisted.internet.defer import inlineCallbacks -from storm.twisted.transact import Transactor, transact -from storm.locals import * - -from storm.uri import URI -from storm.databases.sqlite import SQLite - -database = SQLite(URI('sqlite:///test.db')) - -threadpool = ThreadPool(0, 10) -threadpool.start() -transactor = Transactor(threadpool) - -def getStore(): - store = Store(database) - return store - -@inlineCallbacks -def create_tables(): - from oonib.report.models import models - - for x in models.__all__: - query = getattr(models.__getattribute__(x), 'createQuery') - try: - yield transactor.run(create, query) - except: - log.msg("Failing in creating table for %s. Maybe it already exists?" % x) - -create_tables() diff --git a/oonib/report/models.py b/oonib/report/models.py deleted file mode 100644 index ddf79a3..0000000 --- a/oonib/report/models.py +++ /dev/null @@ -1,122 +0,0 @@ -__all__ = ['Report', 'TestHelperTMP'] -from storm.twisted.transact import transact -from storm.locals import * - -from oonib.report.db import getStore, transactor - -def generateReportID(): - size = 100 - report_id = ''.join(random.choice(string.ascii_letters) for x in range(size)) - return report_id - -class OModel(object): - - transactor = transactor - - def getStore(self): - return Store(database) - - @transact - def create(self, query): - store = Store(database) - store.execute(query) - store.commit() - - @transact - def save(self): - store = getStore() - store.add(self) - store.commit() - -class Report(OModel): - """ - This represents an OONI Report as stored in the database. - - report_id: this is generated by the backend and is used by the client to - reference a previous report and append to it. It should be - treated as a shared secret between the probe and backend. - - software_name: this indicates the name of the software performing the test - (this will default to ooniprobe) - - software_version: this is the version number of the software running the - test. - - test_name: the name of the test on which the report is being created. - - test_version: indicates the version of the test - - progress: what is the current progress of the report. This allows clients - to report event partial reports up to a certain percentage of - progress. Once the report is complete progress will be 100. - - content: what is the content of the report. If the current progress is less - than 100 we should append to the YAML data structure that is - currently stored in such field. - """ - __storm_table__ = 'reports' - - createQuery = "CREATE TABLE " + __storm_table__ +\ - "(id INTEGER PRIMARY KEY, report_id VARCHAR, software_name VARCHAR,"\ - "software_version VARCHAR, test_name VARCHAR, test_version VARCHAR,"\ - "progress VARCHAR, content VARCHAR)" - - - id = Int(primary=True) - - report_id = Unicode() - - software_name = Unicode() - software_version = Unicode() - test_name = Unicode() - test_version = Unicode() - progress = Int() - - content = Unicode() - - @transact - def new(report): - store = self.getStore() - - print "Creating new report %s" % report - - report_id = generateReportID() - new_report = models.Report() - new_report.report_id = unicode(report_id) - - new_report.software_name = report['software_name'] - new_report.software_version = report['software_version'] - new_report.test_name = report['test_name'] - new_report.test_version = report['test_version'] - new_report.progress = report['progress'] - - if 'content' in report: - new_report.content = report['content'] - - print "Report: %s" % report - - store.add(new_report) - try: - store.commit() - except: - store.close() - - defer.returnValue({'backend_version': backend_version, 'report_id': - report_id}) - - -class TestHelperTMP(OModel): - __storm_table__ = 'testhelpertmp' - - createQuery = "CREATE TABLE " + __storm_table__ +\ - "(id INTEGER PRIMARY KEY, report_id VARCHAR, test_helper VARCHAR,"\ - " client_ip VARCHAR, creation_time VARCHAR)" - - id = Int(primary=True) - - report_id = Unicode() - - test_helper = Unicode() - client_ip = Unicode() - - creation_time = Date()