#!/bin/usr/env python # -*- coding: utf-8 -*- import sqlite3 class sql: conn = None; def __init__(self, file): self.conn = sqlite3.connect(file) def create_db_user(self): sql = """ CREATE TABLE users ( id INTEGER PRIMARY KEY, nom VARCHAR (150), prenom VARCHAR (150) )""" c = self.conn.cursor() c.execute(sql) self.conn.commit() c.close() def add_user(self, nom, prenom): sql = """ INSERT INTO users VALUES (NULL,?,?) """ c = self.conn.cursor() c.execute(sql, (nom,prenom) ) self.conn.commit() c.close() def get_all_users(self): sql = """ SELECT * FROM users ORDER BY id """ c = self.conn.cursor() c.execute(sql) r = [] for row in c: r.append(dict([ ['id', row[0]], ['nom', row[1]], ['prenom', row[2]], ])) c.close() return r def create_db_domain(self): sql = """ CREATE TABLE domains ( id INTEGER PRIMARY KEY, id_user INTERGER, domain varchar(255) ) """ c = self.conn.cursor() c.execute(sql) self.conn.commit() c.close() def add_domain(self, id_user, domain ): #FIXME: domaine doublons sql = """ INSERT INTO domains VALUES (NULL,?,?) """ c = self.conn.cursor() c.execute(sql, (id_user,domain) ) self.conn.commit() c.close() def del_domain_by_id(self, id_domain): for site in self.get_infos_sites_by_domain(id_domain): self.del_site_by_id_site(site['id']) sql = """ DELETE FROM domains WHERE id=? """ c = self.conn.cursor() c.execute(sql, str(id_domain) ) self.conn.commit() c.close() def get_domains_by_id_user(self, id_user): sql = """ SELECT * FROM domains WHERE id_user=? """ c = self.conn.cursor() c.execute(sql, str(id_user) ) r = [] for row in c: r.append(dict([ ['id', row[0]], ['id_user', row[1]], ['domain', row[2]], ])) c.close() return r def get_domains_by_id_domain(self, id_domain): sql = """ SELECT * FROM domains WHERE id=? """ c = self.conn.cursor() c.execute(sql, str(id_domain) ) r = [] for row in c: r.append(dict([ ['id', row[0]], ['id_user', row[1]], ['domain', row[2]], ])) c.close() return r def create_db_site(self): sql = """ CREATE TABLE sites ( id INTEGER PRIMARY KEY, id_domain INTEGER, site VARCHAR(50), valid VARCHAR(3), bind VARCHAR(255) DEFAULT '*', port INTEGER DEFAULT '80', serveradmin VARCHAR(255), serveralias TEXT, options1 TEXT, options2 TEXT, options_all VARCHAR(3) DEFAULT 'off', options_execcgi VARCHAR(3) DEFAULT 'off', options_followsymlinks VARCHAR(3) DEFAULT 'off', options_includes VARCHAR(3) DEFAULT 'off', options_includesnoexec VARCHAR(3) DEFAULT 'off', options_indexes VARCHAR(3) DEFAULT 'off', options_multiviews VARCHAR(3) DEFAULT 'off', allowoverride VARCHAR(3) DEFAULT 'off', engine VARCHAR(3) DEFAULT 'on', register_globals VARCHAR(3) DEFAULT 'off', magic_quotes_gpc VARCHAR(3) DEFAULT 'off', short_open_tag VARCHAR(3) DEFAULT 'off', disable_functions TEXT, disable_classes TEXT, max_execution_time INTEGER DEFAULT '30', max_input_time INTEGER DEFAULT '60', memory_limit INTEGER DEFAULT '128', post_max_size INTEGER DEFAULT '8', file_uploads VARCHAR(3) DEFAULT 'on', upload_max_filesize INTEGER DEFAULT '2', display_errors VARCHAR(3) DEFAULT 'off', allow_url_fopen VARCHAR(3) DEFAULT 'on' ) """ c = self.conn.cursor() c.execute(sql) self.conn.commit() c.close() def add_site_by_id_domain(self, id_domain, site, valid = 'on', bind = '*', port = '80', serveradmin = 'me@lapinator.net', serveralias = '', options1 = '', options2 = '', options_all = 'off', options_execcgi = 'off', options_followsymlinks = 'off', options_includes = 'off', options_includesnoexec = 'off', options_indexes = 'off', options_multiviews = 'off', allowoverride = 'off', engine = 'on', register_globals = 'off', magic_quotes_gpc = 'off', short_open_tag = 'off', disable_functions = '', disable_classes = '', max_execution_time = '30', max_input_time = '60', memory_limit = '128', post_max_size = '8', file_uploads = 'on', upload_max_filesize = '2', display_errors = 'off', allow_url_fopen = 'on' ): sql = """ INSERT INTO sites VALUES (NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) """ c = self.conn.cursor() c.execute(sql,( id_domain, site, valid, bind, port, serveradmin, serveralias, options1, options2, options_all, options_execcgi, options_followsymlinks, options_includes, options_includesnoexec, options_indexes, options_multiviews, allowoverride, engine, register_globals, magic_quotes_gpc, short_open_tag, disable_functions, disable_classes, max_execution_time, max_input_time, memory_limit, post_max_size, file_uploads, upload_max_filesize, display_errors, allow_url_fopen )) self.conn.commit() c.close() def upd_site_by_id_site(self, id_site, site, valid, bind, port, serveradmin, serveralias, options1, options2, options_all, options_execcgi, options_followsymlinks, options_includes, options_includesnoexec, options_indexes, options_multiviews, allowoverride, engine, register_globals, magic_quotes_gpc, short_open_tag, disable_functions, disable_classes, max_execution_time, max_input_time, memory_limit, post_max_size, file_uploads, upload_max_filesize, display_errors, allow_url_fopen ): sql = """ UPDATE sites SET site = ?, valid = ?, bind = ?, port = ?, serveradmin = ?, serveralias = ?, options1 = ?, options2 = ?, options_all = ?, options_execcgi = ?, options_followsymlinks = ?, options_includes = ?, options_includesnoexec = ?, options_indexes = ?, options_multiviews = ?, allowoverride = ?, engine = ?, register_globals = ?, magic_quotes_gpc = ?, short_open_tag = ?, disable_functions = ?, disable_classes = ?, max_execution_time = ?, max_input_time = ?, memory_limit = ?, post_max_size = ?, file_uploads = ?, upload_max_filesize = ?, display_errors = ?, allow_url_fopen = ? WHERE id = ? """ c = self.conn.cursor() c.execute(sql,( site, valid, bind, port, serveradmin, serveralias, options1, options2, options_all, options_execcgi, options_followsymlinks, options_includes, options_includesnoexec, options_indexes, options_multiviews, allowoverride, engine, register_globals, magic_quotes_gpc, short_open_tag, disable_functions, disable_classes, max_execution_time, max_input_time, memory_limit, post_max_size, file_uploads, upload_max_filesize, display_errors, allow_url_fopen, id_site )) self.conn.commit() c.close() def get_infos_sites_by_domain(self, id_domain): sql = """ SELECT id, site, valid FROM sites WHERE id_domain=? """ c = self.conn.cursor() c.execute(sql, str(id_domain) ) r = [] for row in c: r.append(dict([ ['id', row[0]], ['site', row[1]], ['valid', row[2]] ])) c.close() return r def get_site_by_id_site(self, id_site): sql = """ SELECT * FROM sites WHERE id=? """ c = self.conn.cursor() c.execute(sql, str(id_site) ) r = [] for row in c: r.append(dict([ ['id', row[1]], ['site', row[2]], ['valid', row[3]], ['bind', row[4]], ['port', row[5]], ['serveradmin', row[6]], ['serveralias', row[7]], ['options1', row[8]], ['options2', row[9]], ['options_all', row[10]], ['options_execcgi', row[11]], ['options_followsymlinks', row[12]], ['options_includes', row[13]], ['options_includesnoexec', row[14]], ['options_indexes', row[15]], ['options_multiviews', row[16]], ['allowoverride', row[17]], ['engine', row[18]], ['register_globals', row[19]], ['magic_quotes_gpc', row[20]], ['short_open_tag', row[21]], ['disable_functions', row[22]], ['disable_classes', row[23]], ['max_execution_time', row[24]], ['max_input_time', row[25]], ['memory_limit', row[26]], ['post_max_size', row[27]], ['file_uploads', row[28]], ['upload_max_filesize', row[29]], ['display_errors', row[30]], ['allow_url_fopen', row[31]] ])) c.close() return r def del_site_by_id_site(self,id_site): sql = "DELETE FROM sites WHERE id=?"; c = self.conn.cursor() c.execute(sql, str(id_site) ) c.close() return; def create_fake_data(): db = sql(':memory:') db.create_db_user() db.create_db_domain() db.create_db_site() db.add_user(u"Vuibert",u"Laurent") db.add_domain(1,"lapinator.net") db.add_domain(1,"animeka.com") db.add_site_by_id_domain(1, "www") db.add_site_by_id_domain(1, "sql") db.add_site_by_id_domain(1, "logs") db.add_site_by_id_domain(2, "www") db.add_user(u"Marchand",u"Valérie") db.add_domain(2,"chouineblog.org") return db if __name__ == "__main__": db = create_fake_data() print db.get_all_users() print db.get_domains_by_id_user(1) print db.get_infos_sites_by_domain(1) print db.get_site_by_id_site(1) db.upd_site_by_id_site( 1, "fsdkjhfsdkjhflskd", "on", "192.168.1.1", "8080", "", "", "", "", "on", "on", "on", "on", "on", "on", "on", "on", "on", "on", "on", "on", "on", "on", "0", "0", "0", "0", "on", "0", "on", "on" ) print db.get_site_by_id_site(1) db.del_site_by_id_site(1) print db.get_infos_sites_by_domain(1) db.del_domain_by_id(1); print db.get_domains_by_id_user(1)