device_user_access_storage.py

# -*- 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.
"""
DeviceUserAccessStorage implements a basic storage of devices, users and access 
"""

import os
import sqlite3
from pyndn import Name
from device_profile import DeviceProfile
from hmac_key import HMACKey
INIT_DEVICE_TABLE = ["""
CREATE TABLE IF NOT EXISTS
  Device(
      id                             INTEGER,
      prefix                         BLOB NOT NULL UNIQUE,
      location                       BLOB,
      category                       BLOB,
      type                           BLOB,
      model                          BLOB,
      serial_number                  BLOB,
      manufacturer                   BLOB,
      seed_name                      BLOB NOT NULL,
      seed_sequence                  BLOB NOT NULL,
      seed_counter                   BLOB NOT NULL,
      seed                           BLOB NOT NULL,
      configuration_token            BLOB NOT NULL,
      configuration_token_sequence   BLOB NOT NULL,
      configuration_token_counter    BLOB NOT NULL,
      
      PRIMARY KEY (id)
  );
"""]

INIT_COMMAND_TABLE = ["""
CREATE TABLE IF NOT EXISTS
  Command(
      id                            INTEGER,
      device_id                     INTEGER NOT NULL,
      name                          BLOB NOT NULL,
      command_token_name            BLOB NOT NULL,
      command_token_sequence        BLOB NOT NULL,
      command_token_counter         BLOB NOT NULL,
      command_token                 BLOB NOT NULL,
      
      PRIMARY KEY(id)
      FOREIGN KEY(device_id) REFERENCES Device(id)
  );
"""]

INIT_SERVICE_PROFILE_TABLE = ["""
CREATE TABLE IF NOT EXISTS
  ServiceProfile(
      id                            INTEGER,
      device_id                     INTEGER NOT NULL,
      name                          BLOB NOT NULL,
      
      PRIMARY KEY(id)
      FOREIGN KEY(device_id) REFERENCES Device(id)
  );
"""]

class DeviceUserAccessStorage(object):
    """
    Create a new DeviceUserStorage 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 Device table exists
        cursor = self._database.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE TYPE ='table' and NAME = 'Device'")
        if cursor.fetchone() == None:
            #no device table exists, create one
            for command in INIT_DEVICE_TABLE:
                self._database.execute(command)
        cursor.close()
        
        #Check if the Command table exists
        cursor = self._database.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE TYPE ='table' and NAME = 'Command'")
        if cursor.fetchone() == None:
            #no command table exists, create one
            for command in INIT_COMMAND_TABLE:
                self._database.execute(command)
        cursor.close()

        #Check if the ServiceProfile table exists
        cursor = self._database.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE TYPE ='table' and NAME = 'ServiceProfile'")
        if cursor.fetchone() == None:
            #no ServiceProfile table exists, create one
            for command in INIT_SERVICE_PROFILE_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 doesDeviceExist(self, prefix):
        """
        Check if the specified device already exists.
       
        :param Name prefix: the prefix of device
        :return True if the device exists, otherwise False
        :rtype: bool
        """
        result = False
        #print prefix        
        cursor = self._database.cursor()
        cursor.execute("SELECT count(*) FROM Device 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 addDevice(self, deviceProfile, seed, configurationToken):
        """ 
        Add a new device to the Device table, do nothing if the device already exists
        
        :param DeviceProfile devicePorfile: the deviceProfile of the device
        :param Key seed: the seed of the device
        :param Key configurationToken: the configurationToken of the device
        :return the device id if it's added successfully, 0 if the device already exists, otherwise -1
        :rtype: INTEGER
        """
        result = -1
        data = (deviceProfile.getPrefix().toUri(),
               deviceProfile.getLocation(),
               deviceProfile.getCategory(),
               deviceProfile.getType(),
               deviceProfile.getModel(),
               deviceProfile.getManufacturer(),
               deviceProfile.getSerialNumber(),
               seed.getName(),
               seed.getSequence(),
               seed.getCounter(),
               seed.getKey(),
               configurationToken.getKey(),
               configurationToken.getSequence(),
               configurationToken.getCounter()
              )     

        #check if the device already exists, if yes return 0
        prefixName = deviceProfile.getPrefix()
        if self.doesDeviceExist(prefixName):
            return 0

        insertDevice = (
            "INSERT INTO Device(prefix, location, category, type, model, manufacturer, serial_number, seed_name, seed_sequence, seed_counter,seed, configuration_token, configuration_token_sequence, configuration_token_counter)"
            "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        )
        cursor = self._database.cursor()
        cursor.execute(insertDevice, data)
        self._database.commit()
        result = cursor.lastrowid
        cursor.close()
        return result

    def getDeviceProfileFromDevice(self, prefix):
        """
        get the specified device profile
        :param Name prefix: the device prefix
        :return device profile of the device if exists, otherwise return None
        :rtype: DeviceProfile
        """   
        operation = (
            "Select location, category, type, model, manufacturer, serial_number FROM Device "
            "WHERE prefix = ?"
        )
    
        cursor = self._database.cursor() 
        cursor.execute(operation, (prefix.toUri(),))
        result = cursor.fetchone() 
        cursor.close()
        # print result
        if result == None:
            return None
        else:               
            deviceProfile = DeviceProfile(prefix)
            deviceProfile.setLocation(result[0])
            deviceProfile.setCategory(result[1])
            deviceProfile.setType(result[2])
            deviceProfile.setModel(result[3])
            deviceProfile.setManufacturer(result[4])
            deviceProfile.setSerialNumber(result[5])
        
        return deviceProfile
               
    def getSeed(self, prefix):
        """
        get the seed of the specified device        
        :param Name prefix: the device prefix
        :return seed of the device if exists, otherwise return None
        :rtype: HMACKey

        """
        operation = (
            "Select seed_name, seed_sequence, seed_counter, seed FROM Device "
            "WHERE prefix = ?"
        )

        cursor = self._database.cursor()
        cursor.execute(operation, (prefix.toUri(),))
        result = cursor.fetchone()
        cursor.close()
        print result
        if result == None:
            return None
        else:
            seed = HMACKey(result[1], result[2], result[3], result[0]) 
        return seed

        # raise RuntimeError("getSeed is not implemented")

    def getConfigurationToken(self, prefix):
        """
        get the seed of the specified device        
        :param Name prefix: the device prefix
        :return seed of the device if exists, otherwise return None
        :rtype: HMACKey

        """
        operation = (
            "Select configuration_token_sequence, configuration_token_counter, configuration_token FROM Device "
            "WHERE prefix = ?"
        )

        cursor = self._database.cursor()
        cursor.execute(operation, (prefix.toUri(),))
        result = cursor.fetchone()
        cursor.close()
        print result
        if result == None:
            return None
        else:
            configurationToken = HMACKey(result[0], result[1], result[2])
        return configurationToken
        # raise RuntimeError("getConfigurationToken is not implemented")

    def getDeviceEntry(self, prefix):
        """
        get the specified device entry
        :param Name prefix: the device prefix
        :return '' return the corresponding row of the device
        :rtype: str
        """        
        cursor = self._database.cursor()
        cursor.execute("SELECT * FROM Device WHERE prefix =?", (prefix.toUri(),))
        row = cursor.fetchone()
        cursor.close()
        return row

    def deleteDevice(self, prefix):
        """
        delete specified device.
        :param Name prefix: The device prefix
        :return: 1 if successful, 0 if no device to delete, otherwise -1.
        :rtype: INTEGER	
        """
        result = -1
        if not self.doesDeviceExist(prefix):
            return 0
        cursor = self._database.cursor()
        deleteDevice = "DELETE FROM Device WHERE prefix=?"
        cursor.execute(deleteDevice, (prefix.toUri(),))
        self._database.commit()
        cursor.close()
        return 1
   
    def getDeviceId(self, prefix):
        """
        get the device id of some specified device
        :param Name prefix: the device prefix
        :return id of the device, 0 if the device doesn't exist
        :rtype: INTEGER
        """
        if not self.doesDeviceExist(prefix):
            return 0
        cursor = self._database.cursor()
        operation = "SELECT id FROM Device WHERE prefix=?"
        cursor.execute(operation, (prefix.toUri(),))
        result = cursor.fetchone()
        deviceId = result[0]
        self._database.commit()
        cursor.close()
        return deviceId
    
    def updateDevice(self, prefix, newDeviceProfile = None , newSeed = None , newcConfigurationToken = None ):
        """
        update specifided device 
        :param Name prefix 
        :param DeviceProfile newDeviceProfile
        :param HMACKey newSeed
        :param newConfigurationToken
        :return id of the device if successful, 0 if device not found, otherwise -1
        :rtype int
        """
        raise RuntimeError("getConfigurationToken is not implemented")

    def updateOneColumnOfDevice(self, prefix, columnName, newColumnValue):
        """
        update the value of a specified column of a specified device
        :param Name prefix: the device prefix
        :param str columnName: column to be updated 
        :param newColumnValue: new column value
        :return id of the device if successful. 0 if device not found, otherwise -1
        :rtype int
        """
        result  = -1
        if not self.doesDeviceExist(prefix):
            return 0
        
        updateDevice = "UPDATE Device Set " + columnName + "=? WHERE prefix=?"
        #print updateDevice       
        cursor = self._database.cursor()
        #print  newColumnValue
        cursor.execute(updateDevice, (newColumnValue,prefix.toUri()))
        self._database.commit()
        result = cursor.lastrowid
        cursor.close()
        return result

    def doesCommandExist(self, deviceId, commandName):
        """
        check if the specified command already exists.
       
        :param Integer device_id: the device id
        :return True if the command exists, otherwise False
        :rtype: bool
        """
        result = False

        cursor = self._database.cursor()
        cursor.execute("SELECT count(*) FROM Command WHERE device_id =? AND name = ?", (deviceId, commandName))
        (count,) = cursor.fetchone()
        if count > 0:
            result = True
        
        cursor.close()
        return result
    
    def addCommand(self, deviceId, name, commandToken):
        """ 
        Add a new command to the Command table, do nothing if the device already exists
        
        :param int deviceId: the device id to which the command belongs to
        :param str name: the command name
        :param HMACKey commandToken: the command token
        :return the command id if it's added successfully, 0 if the command already exists, otherwise -1
        :rtype: int
        """
        result = -1
        data = (deviceId,
               name,
               commandToken.getName(),
               commandToken.getSequence(),
               commandToken.getCounter(),
               commandToken.getKey()
               )

        #check if the command already exists, if yes return 0
        if self.doesCommandExist(deviceId, name):
            return 0

        insertCommand = (
            "INSERT INTO Command(device_id, name, command_token_name, command_token_sequence, command_token_counter, command_token)"
            "VALUES(?,?,?,?,?,?)"
        )
        cursor = self._database.cursor()
        cursor.execute(insertCommand, data)
        self._database.commit()
        result = cursor.lastrowid
        cursor.close()
        return result
    
    def deleteCommand(self, deviceId, name = None):
        """
        delete specified commands.
        :param str name: The command name, if None, delete all commands of the device
        :param int deviceId: device id of the command
        :return: 1 if successful, 0 if no command to delete, otherwise -1.
        :rtype: int
        """
        result = -1
        if not name == None: 
            if not self.doesCommandExist(deviceId, name):
                return 0
            cursor = self._database.cursor()
            deleteCommand = "DELETE FROM Command WHERE device_id=? AND name=?"
            cursor.execute(deleteCommand, (deviceId, name))
            self._database.commit()
            cursor.close()
            return 1
        else: 
            selectOperation = "SELECT count(*) FROM Command WHERE deviceId=?"
            cursor = self._database.cursor()
            cursor.execute(selectOperation, (deviceId,))
            (count,) = cursor.fetchone()
            if not count > 0:
                return 0
            deleteCommand = "DELETE FROM Command WHERE deviceId=?"
            cursor.execute(deleteCommand, (deviceId))
            self._database.commit()
            cursor.close()
            return 1
        
        return result
      
    def getCommandsOfDevice(self, deviceId):
        """
        get all the commands of a specified device
        :param int deviceId: device id of the command
        :return command name list if any commands exist, otherwise None

        """
        operation = "SELECT name FROM Command WHERE device_id = ?"
        #operation2 = "SELECT count(*) FROM Command WHERE device_id = ? "
        cursor = self._database.cursor()
        #cursor.execute(operation2,(deviceId,))
        #(count,) = cursor.fetchone

        #if not count > 0:
            #return None
        cursor.execute(operation, (deviceId,))
        result = cursor.fetchall()                    
        print result
        commandList = []
        if result == None:
           return commandList 
        else:  
           for row in result:
               commandList.append(row[0]) 
    
        return commandList

    def getCommandToken(self, deviceId, commandName):
        """
        get command token of a specified command 
        :param int deviceId: device id of the command
        :param str commandName: device name of the command
        :return command token if command existsm, otherwise None
        :rtype HMACKey
        """
        operation = "SELECT command_token_sequence, command_token_counter,command_token, command_token_name FROM Command WHERE device_id = ? AND name = ?"
        cursor = self._database.cursor()
        cursor.execute(operation, (deviceId, commandName))
        result = cursor.fetchone()
        if result == None:
            return None
        else:
            commandToken = HMACKey(result[0], result[1], result[2], result[3])
        return commandToken
  
    def doesServiceProfileExist(self, deviceId, serviceProfileName):
        """
        check if the specified service profile already exists.
       
        :param Integer device_id: the device id
        :param str serviceProfileName: name of the service profile 
        :return True if the service profile exists, otherwise False
        :rtype: bool
        """
        result = False

        cursor = self._database.cursor()
        cursor.execute("SELECT count(*) FROM ServiceProfile WHERE device_id =? AND name = ?", (deviceId, serviceProfileName))
        (count,) = cursor.fetchone()
        if count > 0:
            result = True

        cursor.close()
        return result

    def addServiceProfile(self, deviceId, name):
        """ 
        Add a new command to the ServiceProfile table, do nothing if the device already exists
        
        :param int deviceId: the device id to which the command belongs to
        :param str name: the service profile name
        :return the service profile id if it's added successfully, 0 if the service profile already exists, otherwise -1
        :rtype: int
        """
        result = -1
        data = (deviceId,
               name,
               )

        #check if the service profile already exists, if yes return 0
        if self.doesServiceProfileExist(deviceId, name):
            return 0

        insertCommand = (
            "INSERT INTO ServiceProfile(device_id, name)"
            "VALUES(?,?)"
        )
        cursor = self._database.cursor()
        cursor.execute(insertCommand, data)
        self._database.commit()
        result = cursor.lastrowid
        cursor.close()
        return result

    def deleteServiceProfile(self, deviceId, name = None):
        """
        delete specified service profile.
        :param str name: The command name, if None, delete all profiless of the device
        :param int deviceId: device id 
        :return: 1 if successful, 0 if no service profile to delete, otherwise -1.
        :rtype: int
        """
        result = -1
        if not name == None:
            if not self.doesServiceProfileExist(deviceId, name):
                return 0
            cursor = self._database.cursor()
            deleteProfile = "DELETE FROM ServiceProfile WHERE device_id=? AND name=?"
            cursor.execute(deleteProfile, (deviceId, name))
            self._database.commit()
            cursor.close()
            return 1
        else:
            selectOperation = "SELECT count(*) FROM ServiceProfile WHERE deviceId=?"
            cursor = self._database.cursor()
            cursor.execute(selectOperation, (deviceId,))
            (count,) = cursor.fetchone()
            if not count > 0:
                return 0
            deleteProfile = "DELETE FROM ServiceProfile WHERE deviceId=?"
            cursor.execute(deleteProfile, (deviceId))
            self._database.commit()
            cursor.close()
            return 1

        return result

    def getServiceProfilesOfDevice(self, deviceId):
        """
        get all the service profiles of a specified device
        :param int deviceId: device id of the command
        :return service profiles name list if any service profiles exist, otherwise None
        """
        operation = "SELECT name FROM ServiceProfile WHERE device_id = ?"
        cursor = self._database.cursor()
        cursor.execute(operation, (deviceId,))
        result = cursor.fetchall()
        #print result
        serviceProfileList = []
        if not result == None:
            for row in result:
                serviceProfileList.append(row[0])
        return serviceProfileList