add user-related DB Storage & Manager
ref #3078
Change-Id: Idd0272f501545a9e8fa156008400b42cf0ea10ef
diff --git a/user_access_storage.py b/user_access_storage.py
new file mode 100644
index 0000000..3e108ef
--- /dev/null
+++ b/user_access_storage.py
@@ -0,0 +1,402 @@
+# -*- Mode:python; c-file-style:"gnu"; indent-tabs-mode:nil -*- */
+#
+# Copyright (C) 2014 Regents of the University of California.
+# Author: Teng Liang <philoliang2011@gmail.com>
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU Lesser General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+# A copy of the GNU General Public License is in the file COPYING.
+"""
+UserAccessStorage implements a basic storage of users and access
+"""
+
+import os
+import sqlite3
+from pyndn import Name
+from device_profile import DeviceProfile
+from hmac_key import HMACKey
+
+INIT_USER_TABLE = ["""
+CREATE TABLE IF NOT EXISTS
+ User(
+ id INTEGER,
+ prefix BLOB NOT NULL UNIQUE,
+ username BLOB NOT NULL UNIQUE,
+ hash BLOB NOT NULL,
+ salt BLOB NOT NULL,
+ type BLOB NOT NULL,
+
+ PRIMARY KEY (id)
+ );
+"""]
+
+INIT_ACCESS_TABLE = ["""
+CREATE TABLE IF NOT EXISTS
+ Access(
+ id INTEGER,
+ command_id INTEGER,
+ user_id INTEGER,
+ user_device BLOB NOT NULL,
+ access_token_name BLOB,
+ access_token_sequence INTEGER NOT NULL,
+ access_token BLOB NOT NULL,
+
+ PRIMARY KEY (id)
+ FOREIGN KEY(command_id) REFERENCES Command(id)
+ FOREIGN KEY(user_id) REFERENCES User(id)
+ );
+"""]
+
+class UserAccessStorage(object):
+ """
+ Create a new UserAccessStorage to work with an SQLite file.
+ :param str databaseFilePath: (optional) The path of the SQLite file. If ommitted, use the default path.
+ """
+ def __init__(self, databaseFilePath = None):
+ if databaseFilePath == None or databaseFilePath == "":
+ if not "HOME" in os.environ:
+ home = '.'
+ else:
+ home = os.environ["HOME"]
+
+ dbDirectory = os.path.join(home, '.ndn')
+ if not os.path.exists(dbDirectory):
+ os.makedirs(dbDirectory)
+
+ databaseFilePath = os.path.join(dbDirectory, 'ndnhome-controller.db')
+
+ self._database = sqlite3.connect(databaseFilePath)
+
+ #Check if the User table exists
+ cursor = self._database.cursor()
+ cursor.execute("SELECT name FROM sqlite_master WHERE TYPE ='table' and NAME = 'User'")
+ if cursor.fetchone() == None:
+ #no device table exists, create one
+ for command in INIT_USER_TABLE:
+ self._database.execute(command)
+ cursor.close()
+
+ #Check if the Access table exists
+ cursor = self._database.cursor()
+ cursor.execute("SELECT name FROM sqlite_master WHERE TYPE ='table' and NAME = 'Access'")
+ if cursor.fetchone() == None:
+ #no device table exists, create one
+ for command in INIT_ACCESS_TABLE:
+ self._database.execute(command)
+ cursor.close()
+
+ self._database.commit()
+
+ def doesTableExist(self, tableName):
+ """
+ check if table exists
+ :param str tableName
+ :return True if exists, otherwise False
+ """
+ selectOperation = "SELECT name FROM sqlite_master WHERE TYPE='table' and NAME='" + tableName +"'"
+ #print selectOperation
+ cursor = self._database.cursor()
+ cursor.execute(selectOperation)
+ if cursor.fetchone() == None:
+ result = False
+ else:
+ result = True
+ cursor.close()
+ return result
+
+ def doesUserExistByPrefix(self, prefix):
+ """
+ Check if the specified user already exists.
+
+ :param Name prefix: the prefix of user
+ :return True if the user exists, otherwise False
+ :rtype: bool
+ """
+ result = False
+
+ cursor = self._database.cursor()
+ cursor.execute("SELECT count(*) FROM User WHERE prefix =?", (prefix.toUri(),))
+ (count,) = cursor.fetchone()
+ if count > 0:
+ result = True
+ #print 'device with %s is founnd, count %d' %(prefix, count)
+
+ cursor.close()
+ return result
+
+ def doesUserExistByUsername(self, username):
+ """
+ Check if the specified user already exists.
+
+ :param str username: the username of user
+ :return True if the user exists, otherwise False
+ :rtype: bool
+ """
+ result = False
+
+ cursor = self._database.cursor()
+ cursor.execute("SELECT count(*) FROM User WHERE username =?", (username,))
+ (count,) = cursor.fetchone()
+ if count > 0:
+ result = True
+ #print 'device with %s is founnd, count %d' %(prefix, count)
+
+ cursor.close()
+ return result
+
+
+ def addUser(self, prefix, username, hash_, salt, type_):
+ """
+ Add a new user to User table, do nothing if the user already exists
+
+ :param Name prefix: the prefix of the user
+ :param str username: username
+ :param hash_:
+ :param salt:
+ :param str type: the type of user, either guest or user
+ :return the user id if it's added successfully, 0 if prefix conflict exists, -1 if username conflict exists
+ :rtype: INTEGER
+ """
+ data = ( prefix.toUri(), username, hash_, salt, type_)
+
+ #check if there already exists a user with the same prefix, if yes return 0
+ if self.doesUserExistByPrefix(prefix):
+ return 0
+
+ #check if there already exists a user with the same username, if yes return -1
+ if self.doesUserExistByUsername(username):
+ return -1
+
+ #add user to dababase
+ insertUser = (
+ "INSERT INTO User(prefix, username, hash, salt, type)"
+ "VALUES(?,?,?,?,?)"
+ )
+ cursor = self._database.cursor()
+ cursor.execute(insertUser, data)
+ self._database.commit()
+ result = cursor.lastrowid
+ cursor.close()
+ return result
+
+ def getUserId(self, prefix):
+ """
+ get the user id of a specified user
+ :param Name prefix: the prefix of the user
+ :return id of the user, 0 if the user doesn't exist
+ :rtype: INTEGER
+ """
+ if not self.doesUserExistByPrefix(prefix):
+ return 0
+ cursor = self._database.cursor()
+ operation = "SELECT id FROM User WHERE prefix=?"
+ cursor.execute(operation, (prefix.toUri(),))
+ result = cursor.fetchone()
+ userId = result[0]
+ self._database.commit()
+ cursor.close()
+ return userId
+
+ def updateUser(self, prefix, hash_, salt, type_):
+ """
+ update specifided user
+ :param Name prefix
+ :param hash_
+ :param salt
+ :param str type_: the type of the user
+ :return id of the user if successful, 0 if user not found
+ :rtype int
+ """
+ if not self.doesUserExistByPrefix(prefix):
+ return 0
+
+ updateUser = "UPDATE User Set hash=?, salt =?, type=? WHERE prefix=?"
+ cursor = self._database.cursor()
+ cursor.execute(updateUser, (hash_, salt, type_, prefix.toUri()))
+ self._database.commit()
+ result = cursor.lastrowid
+ cursor.close()
+
+ def getUserEntry(self, prefix):
+ """
+ get the specified User entry
+ :param Name prefix: the user prefix
+ :return the corresponding row of the device
+ :rtype: str
+ """
+ cursor = self._database.cursor()
+ cursor.execute("SELECT * FROM User WHERE prefix =?", (prefix.toUri(),))
+ row = cursor.fetchone()
+ cursor.close()
+ return row
+
+ def deleteUser(self, prefix):
+ """
+ delete specified user.
+ :param Name prefix: The user prefix
+ :return: 1 if successful, 0 if no device to delete, otherwise -1.
+ :rtype: INTEGER
+ """
+ result = -1
+ if not self.doesUserExistByPrefix(prefix):
+ return 0
+ cursor = self._database.cursor()
+ deleteUser = "DELETE FROM User WHERE prefix=?"
+ cursor.execute(deleteUser, (prefix.toUri(),))
+ self._database.commit()
+ cursor.close()
+ return 1
+
+ def doesAccessExist(self, commandId, userId, userDevice=None):
+ """
+ check if the specified access already exists.
+
+ :param int userId: user id
+ :param int commandId: command id
+ :param str userdevice: user device name
+ :return True if the access exists, otherwise False
+ :rtype: bool
+ """
+ result = False
+
+ cursor = self._database.cursor()
+ if userDevice == None:
+ operation = "SELECT count(*) FROM Access WHERE user_id =? AND command_id=? "
+ data = (userId, commandId)
+ else:
+ operation = "SELECT count(*) FROM Access WHERE user_id =? AND command_id=? AND user_device=?"
+ data = (userId, commandId, userDevice)
+ cursor.execute(operation, data)
+
+ (count,) = cursor.fetchone()
+ if count > 0:
+ result = True
+ cursor.close()
+ return result
+
+ def addAccess(self, commandId, userId, userDevice, accessToken):
+ """
+ Add a new access to the Access table, do nothing if the access already exists
+
+ :param int commandId: the command id
+ :param int userId: the user id
+ :param int userDevice: the user device
+ :param HMACKey accessToken: the access token
+ :return the access id if it's added successfully, 0 if the access already exists, otherwise -1
+ :rtype: int
+ """
+ result = -1
+ data = (commandId,
+ userId,
+ userDevice,
+ accessToken.getName(),
+ accessToken.getSequence(),
+ accessToken.getKey()
+ )
+
+ #check if the access already exists, if yes return 0
+ if self.doesAccessExist(commandId, userId, userDevice):
+ return 0
+
+ insertAccess = (
+ "INSERT INTO Access(command_id, user_id, user_device, access_token_name, access_token_sequence, access_token)"
+ "VALUES(?,?,?,?,?,?)"
+ )
+ cursor = self._database.cursor()
+ cursor.execute(insertAccess, data)
+ self._database.commit()
+ result = cursor.lastrowid
+ cursor.close()
+ return result
+
+ def deleteAccess(self, commandId, userId):
+ """
+ delete specified accesses.
+ :param int commandId: The command id
+ :param int userId: the user id
+ :return: 1 if successful, 0 if no access to delete.
+ :rtype: int
+ """
+ if not self.doesAccessExist(userId, userId):
+ return 0
+ cursor = self._database.cursor()
+ deleteAccess = "DELETE FROM Access WHERE command_id=? AND user_id=?"
+ cursor.execute(deleteAccess, (commandId, userId))
+ self._database.commit()
+ cursor.close()
+ return 1
+
+ def getCommandsOfUser(self, userId):
+ """
+ get all the commands to which the specified user has access.
+ :param int userId: user id
+ :return command id list if any commands exist, otherwise None
+ """
+ cursor = self._database.cursor()
+ operation = "SELECT DISTINCT command_id FROM Access WHERE user_id=?"
+ cursor.execute(operation,(userId,))
+ result = cursor.fetchall()
+ cursor.close()
+
+ commandIdList = []
+ if result == None:
+ return commandIdList
+ else:
+ for row in result:
+ commandIdList.append(row[0])
+
+ return commandIdList
+
+ def getAccessToken(self,commandId, userId, userDevice):
+ """
+ get access token of a specified access
+ :param int commandId: command id
+ :param int userId: user id
+ :param str userDevice: user device
+ :return access token if access exists, otherwise None
+ :rtype HMACKey
+ """
+ operation = "SELECT access_token_sequence, access_token, access_token_name FROM Access WHERE command_id = ? AND user_id = ? AND user_device=?"
+ cursor = self._database.cursor()
+ cursor.execute(operation, (commandId, userId, userDevice))
+ result = cursor.fetchone()
+ cursor.close()
+
+ if result == None:
+ return None
+ else:
+ accessToken = HMACKey(sequence = result[0], key = result[1], name = result[2])
+ return accessToken
+
+ def getUserDevices(self, commandId, userId):
+ """
+ get user devices given commandi id and user id
+ :param int commandId: command id
+ :param int userId: user id
+ :return a list of user device
+ :rtype list
+ """
+ operation = "SELECT user_device from Access WHERE command_id=? AND user_id=?"
+ cursor = self._database.cursor()
+ cursor.execute(operation,(commandId, userId))
+ result = cursor.fetchall()
+ cursor.close()
+
+ userDeviceList = []
+ if result == None:
+ return userDeviceList
+
+ for row in result:
+ userDeviceList.append(row[0])
+
+ return userDeviceList
+