class Rosterがとりあえずかたちになりました

d:id:imait:20090217:1234874681に作りかけを公開してましたRosterクラス。今日、とりあえずかたちになりましたので、ここに載せておきます。

これからご飯食べるので、細かいことはまた後に書きます。

# -*- coding: utf-8 -*-
'''
Class Roster which treats users with sqlite3
'''

import hashlib
import sqlite3
import pickle
import base64


class Roster:
    '''Class of Roser of users'''

    def __init__(self, dbpath):
        self.dbpath = dbpath
        self.dbtablename = u'usertable'

        connection = self._open_db()
        cursor = connection.cursor()
        cursor.execute('select * from sqlite_master \
        where type=\'table\' and name=?;', \
                       (self.dbtablename, ))
        tablecount = cursor.fetchall()
        if len(tablecount) == 0:
            cursor.execute('create table %s (uid primary key, password, \
            data, created_time, changed_time, active);' % \
                           self.dbtablename)
        cursor.close()
        connection.commit()
        connection.close()


    def exists(self, uid):
        if isinstance(uid, basestring):
            connection = self._open_db()
            cursor = connection.cursor()
            recordcount = self._count_user(cursor, uid)
            cursor.close()
            connection.close()
            if recordcount == 1:
                return True
            else:
                return False
        else:
            raise TypeError(u'need string, got %r' % \
                            type(uid))

    def count(self):
        connection = self._open_db()
        cursor = connection.cursor()
        cursor.execute('select count(*) from %s;' % \
                       self.dbtablename)
        recordcount = cursor.fetchone()
        cursor.close()
        connection.close()
        return recordcount[0]

    def list(self):
        connection = self._open_db()
        cursor = connection.cursor()
        cursor.execute('select uid from %s;' % \
                       self.dbtablename)
        uidlist = list()
        for record in cursor.fetchall():
            uidlist.append(record[0])
        cursor.close()
        connection.close()
        return uidlist

    def create_user(self, uid, password):
        message = hashlib.new('sha256')
        message.update(password)
        password = message.hexdigest()

        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if recordcount == 0:
            try:
                cursor.execute('insert into %s (uid, password, created_time, \
                changed_time, active) values(\'%s\', \'%s\', \
                datetime(\'now\'), datetime(\'now\'), \'%s\');' % \
                               (self.dbtablename, uid, password, False))
            except:
                raise
            finally:
                cursor.close()
                connection.commit()
                connection.close()
        else:
            cursor.close()
            connection.close()
            raise UserExistsError(u'user ' + uid + u' already exists')

    def delete_user(self, uid):
        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if recordcount == 1:
            try:
                cursor.execute('delete from %s where uid=\'%s\';' % \
                               (self.dbtablename, uid))
            except:
                raise
            finally:
                cursor.close()
                connection.commit()
                connection.close()
        else:
            cursor.close()
            connection.close()
            raise UserNotExistsError(u'user ' + uid + u' not exists')

    def confirm_user(self, uid, password):
        message = hashlib.new('sha256')
        message.update(password)
        password = message.hexdigest()

        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if self.is_active(uid):
            if recordcount == 1:
                try:
                    cursor.execute('select password from %s \
                    where uid=\'%s\';' % \
                                   (self.dbtablename, uid))
                except:
                    raise
                else:
                    cpassword = cursor.fetchone()
                    cpassword = cpassword[0]
                    if cpassword == password:
                        return True
                    else:
                        return False
                finally:
                    cursor.close()
                    connection.close()
            else:
                cursor.close()
                connection.close()
                raise UserNotExistsError(u'user ' + uid + u' not exists')
        else:
            return False

    def change_password(self, uid, password):
        message = hashlib.new('sha256')
        message.update(password)
        password = message.hexdigest()

        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if recordcount == 1:
            try:
                cursor.execute('update %s set password=\'%s\', \
                changed_time=datetime(\'now\') where uid=\'%s\';' % \
                               (self.dbtablename, password, uid))
            except:
                raise
            finally:
                cursor.close()
                connection.commit()
                connection.close()
        else:
            cursor.close()
            connection.close()
            raise UserNotExistsError(u'user ' + uid + u' not exists')

    def is_active(self, uid):
        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if recordcount == 1:
            try:
                cursor.execute('select active from %s where uid=\'%s\';' % \
                               (self.dbtablename, uid))
            except:
                raise
            else:
                status = cursor.fetchone()[0]
                if status == 'True':
                    status = True
                else:
                    status = False
                return status
            finally:
                cursor.close()
                connection.close()
        else:
            cursor.close()
            connection.close()
            raise UserNotExistsError(u'user ' + uid + u' not exists')

    def activate_user(self, uid):
        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if recordcount == 1:
            try:
                cursor.execute('update %s set active=\'%s\', \
                changed_time=datetime(\'now\') where uid=\'%s\';' % \
                               (self.dbtablename, True, uid))
            except:
                raise
            finally:
                cursor.close()
                connection.commit()
                connection.close()
        else:
            cursor.close()
            connection.close()
            raise UserNotExistsError(u'user ' + uid + u' not exists')

    def inactivate_user(self, uid):
        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if recordcount == 1:
            try:
                cursor.execute('update %s set active=\'%s\', \
                changed_time=datetime(\'now\') where uid=\'%s\';' % \
                               (self.dbtablename, False, uid))
            except:
                raise
            finally:
                cursor.close()
                connection.commit()
                connection.close()
        else:
            cursor.close()
            connection.close()
            raise UserNotExistsError(u'user ' + uid + u' not exists')

    def get_created_time(self, uid):
        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if recordcount == 1:
            try:
                cursor.execute('select created_time from %s \
                where uid=\'%s\';' % \
                               (self.dbtablename, uid))
            except:
                raise
            else:
                created_time = cursor.fetchone()
                return created_time[0]
            finally:
                cursor.close()
                connection.close()
        else:
            cursor.close()
            connection.close()
            raise UserNotExistsError(u'user ' + uid + u' not exists')

    def get_changed_time(self, uid):
        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if recordcount == 1:
            try:
                cursor.execute('select changed_time from %s \
                where uid=\'%s\';' % \
                               (self.dbtablename, uid))
            except:
                raise
            else:
                changed_time = cursor.fetchone()
                return changed_time[0]
            finally:
                cursor.close()
                connection.close()
        else:
            cursor.close()
            connection.close()
            raise UserNotExistsError(u'user ' + uid + u' not exists')

    def load_user_data(self, uid):
        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if recordcount == 1:
            try:
                cursor.execute('select data from %s where uid=\'%s\';' % \
                               (self.dbtablename, uid))
            except:
                raise
            else:
                data = cursor.fetchone()
                data = data[0]
                if data != None:
                    try:
                        return pickle.loads(base64.decodestring(data))
                    except:
                        raise
            finally:
                cursor.close()
                connection.close()
        else:
            cursor.close()
            connection.close()
            raise UserNotExistsError(u'user ' + uid + u' not exists')

    def save_user_data(self, uid, data):
        connection = self._open_db()
        cursor = connection.cursor()
        recordcount = self._count_user(cursor, uid)
        if recordcount == 1:
            data = base64.encodestring(pickle.dumps(data))
            try:
                cursor.execute('update %s set data=\'%s\', \
                changed_time=datetime(\'now\') where uid=\'%s\';' % \
                               (self.dbtablename, data, uid))
            except:
                raise
            finally:
                cursor.close()
                connection.commit()
                connection.close()
        else:
            cursor.close()
            connection.close()
            raise UserNotExistsError(u'user ' + uid + u' not exists')


    # internal methods

    def _open_db(self):
        return sqlite3.connect(self.dbpath)

    def _count_user(self, cursor, uid):
        cursor.execute('select count(*) from %s where uid=\'%s\';' % \
                       (self.dbtablename, uid))
        recordcount = cursor.fetchone()
        return recordcount[0]



# Exceptions

class Error(Exception):
    '''Base class for exception in this module.'''
    pass

class UserExistsError(Error):
    '''Exception raised when created user which already exists'''

    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value)

class UserNotExistsError(Error):
    '''Exception raised when deleted user which not exists'''

    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value)