[sshproxy-dev] Changes to sshproxy/SSHproxy/pwdb/mysql.py

[Pierre-Yves Rofes]

Index: sshproxy/SSHproxy/pwdb/mysql.py
diff -u sshproxy/SSHproxy/pwdb/mysql.py:1.4 sshproxy/SSHproxy/pwdb/mysql.py:1.5
--- sshproxy/SSHproxy/pwdb/mysql.py:1.4 Mon Sep 26 09:05:30 2005
+++ sshproxy/SSHproxy/pwdb/mysql.py     Tue Sep 27 13:55:48 2005
@@ -42,7 +42,6 @@
             select id, site_id, uid, password, `primary`
                 from user where site_id = %d
             """
-
         site_list = []
         sites = db.cursor()
         sites.execute(q_sites)
@@ -57,12 +56,12 @@
                                               port=port,
                                               location=location,
                                               user_list=user_list))
-        #print 'site ' + name + ' grants access to ' + user_list.__repr__()
-
         simple.SimplePwDB.__init__(self, site_list)
 
-#    def __del__(self):
-#        db.close()
+    #def __del__(self):
+    #   db.close()
+
+################## functions for script/add_profile #####################
 
     def list_profiles(self):
         q_getprofile = """
@@ -98,7 +97,9 @@
         profile.execute(q_addprofile % name)
         profile.close()
         return 1
-        
+
+################ functions for scripts/add_group ########################
+
     def list_groups(self):
         q_getgroup = """
             select id, name from sgroup
@@ -133,7 +134,191 @@
         group.execute(q_addgroup % name)
         group.close()
         return 1
-        
+
+################ functions for scripts/add_site #######################
+
+    def list_sites(self):
+        q_listsite = """
+            select id, name, ip_address, port, location from site
+        """
+        site = db.cursor()
+        site.execute(q_listsite)
+        p = []
+        for id, name, ip_address, port, location in site.fetchall():
+            p.append({ 'id': id, 'name': name, 'ip': ip_address, 'port': port, 
'location': location })
+        site.close()
+        return p
+
+    def get_site_for_script(self, name):
+        q_getsite = """
+            select id, name, ip_address, port, location from site where name = 
'%s'
+        """
+        site = db.cursor()
+        site.execute(q_getsite % name)
+        p = site.fetchone()
+        if not p or not len(p):
+            return None
+        site.close()
+        return { 'id': p[0], 'name': p[1], 'ip_address': p[2], 'port': p[3], 
'location': p[4] }
+
+    def add_site(self, name, ip_address, port, location):
+        q_addsite = """
+            insert into site (name, ip_address, port, location) values 
('%s','%s',%d,'%s')
+        """
+        if self.get_site_for_script(name):
+            return None
+        site = db.cursor()
+        site.execute(q_addsite % (name, ip_address, port, location))
+        site.close()
+        return 1
+
+################## functions for scripts/add_user #####################
+
+    def list_users(self):
+        q_listuser = """
+            select site_id, uid, password, `primary` from user
+        """
+        site = db.cursor()
+        site.execute(q_listuser)
+        p = []
+        for site_id, uid, password, primary in site.fetchall():
+            p.append({ 'uid': uid, 'site_id': site_id, 'password': password, 
'primary': primary })
+        site.close()
+        return p
+
+    def get_users(self, uid):
+        q_getuser = """
+            select site_id, uid, password, `primary` from user where uid = '%s'
+        """
+        user = db.cursor()
+        user.execute(q_getuser % uid)
+        p = user.fetchone()
+        if not p or not len(p):
+            return None
+        user.close()
+        return { 'site_id': p[0], 'uid': p[1], 'password': p[2], 'primary': 
p[3] }
+
+    def add_user(self, uid, site_id, password, primary):
+        q_adduser = """
+            insert into user (uid, site_id, password, `primary`) values 
('%s','%s','%s',%d)
+        """
+        if self.get_users(uid):
+            return None
+        user = db.cursor()
+        user.execute(q_adduser % (uid, site_id, password, primary))
+        user.close()
+        return 1
+
+################### functions for scripts/add_login   #############
+
+    def list_logins(self):
+        q_listlogin = """
+            select uid, password, `key` from login
+        """
+        site = db.cursor()
+        site.execute(q_listlogin)
+        p = []
+        for uid, password, key in site.fetchall():
+            p.append({ 'uid': uid, 'password': password, 'key': key })
+        site.close()
+        return p
+
+    def get_logins(self, uid):
+        q_getlogin = """
+            select uid, password, `key` from login where uid = '%s'
+        """
+        login = db.cursor()
+        login.execute(q_getlogin % uid)
+        p = login.fetchone()
+        if not p or not len(p):
+            return None
+        login.close()
+        return { 'uid': p[0], 'password': p[1], 'key': p[2] }
+
+    def add_login(self, uid, password, key):
+        q_addlogin = """
+            insert into login (uid, password, `key`) values ('%s','%s','%s')
+        """
+        if self.get_logins(uid):
+            return None
+        login = db.cursor()
+        login.execute(q_addlogin % (uid, password, key))
+        login.close()
+        return 1
+
+######### functions for link scripts/add_login_profile ###############
+
+    def list_login_prof(self):
+        q_list = """
+             select login_id, profile_id from login_profile
+        """
+        lists = db.cursor()
+        lists.execute(q_list)
+        p = []
+        for login_id,profile_id in lists.fetchall():
+            p.append({ 'login_id': login_id, 'profile_id': profile_id })
+        lists.close()
+        return p
+
+    def add_login_prof(self, login_id, profile_id):
+        q_addlogin = """
+            insert into login_profile (login_id, profile_id) values (%d,%d)
+        """
+        login = db.cursor()
+        login.execute(q_addlogin % (login_id, profile_id))
+        login.close()
+        return 1
+
+
+######### functions for link scripts/add_profile_sgroup ###############
+
+    def list_prof_group(self):
+        q_list = """
+             select profile_id, sgroup_id from profile_sgroup
+        """
+        lists = db.cursor()
+        lists.execute(q_list)
+        p = []
+        for profile_id,sgroup_id in lists.fetchall():
+            p.append({ 'profile_id': profile_id, 'sgroup_id': sgroup_id })
+        lists.close()
+        return p
+
+    def add_prof_group(self, profile_id, sgroup_id):
+        q_addlogin = """
+            insert into profile_sgroup (profile_id,sgroup_id) values (%d,%d)
+        """
+        login = db.cursor()
+        login.execute(q_addlogin % (profile_id, sgroup_id))
+        login.close()
+        return 1
+
+
+######### functions for link scripts/add_sgroup_site ###############
+
+    def list_group_site(self):
+        q_list = """
+             select sgroup_id,site_id from sgroup_site
+        """
+        lists = db.cursor()
+        lists.execute(q_list)
+        p = []
+        for sgroup_id,site_id in lists.fetchall():
+            p.append({ 'sgroup_id': sgroup_id, 'site_id': site_id })
+        lists.close()
+        return p
+
+    def add_group_site(self, sgroup_id, site_id):
+        q_addlogin = """
+            insert into sgroup_site (sgroup_id, site_id) values (%d,%d)
+        """
+        login = db.cursor()
+        login.execute(q_addlogin % (sgroup_id, site_id))
+        login.close()
+        return 1
+
+
+#######################################################################
 
     def is_allowed(self, username, password=None, key=None):
         """Check is a user is allowed to connect to the proxy."""