class TestDb(unittest.TestCase): """test db module""" # pylint: disable=too-many-public-methods # It's reasonable in this case. def setUp(self): self.db = Database(MySQLdb.Connect, host='database', user='******', passwd='password', db='test') def tearDown(self): self.db.close() def test_RecordSet(self): db = self.db db("""drop table if exists dzdb_test_table""") db("""create table dzdb_test_table (ID CHAR(10), AMOUNT NUMERIC(10,2),DTADD DATE,NOTES TEXT)""") db("""insert into dzdb_test_table values ("1234",50,"2005-01-14","Hello there")""") db("""insert into dzdb_test_table values ("5678",60,"2035-01-24","New notes")""") recordset = db('select * from dzdb_test_table') print recordset for rec in recordset: self.assertEquals(rec, ("1234", 50, "2005-01-14", "Hello there")) break def test_db_create_drop_table(self): table_names = lambda a: [x[0] for x in a('show tables')] db = self.db db('drop table if exists dzdb_test_table') self.assert_('dzdb_test_table' not in table_names(db)) db("""create table dzdb_test_table (ID CHAR(10),AMOUNT NUMERIC(10,2),DTADD DATE,NOTES TEXT)""") self.assert_('dzdb_test_table' in table_names(db)) db('drop table dzdb_test_table') self.assert_('dzdb_test_table' not in table_names(db)) def test_db_insert_update_record(self): # pylint: disable=protected-access insert_test = """insert into dzdb_test_table (ID, DTADD, amount, notes) values (%s, %s, %s, %s)""" select_all = 'select * from dzdb_test_table' db = self.db db('drop table if exists dzdb_test_table') db("""create table dzdb_test_table (ID CHAR(10), NAME CHAR(25), AMOUNT NUMERIC(10,2), DTADD DATE, NOTES TEXT)""") dt = datetime(2005, 1, 2) db(insert_test, '1234', dt, 50, 'Testing') self.assertEqual(db(select_all).cursor.rowcount, 1) db(insert_test, '4321', dt, 10.20, 'Testing 2') self.assertEqual(db(select_all).cursor.rowcount, 2) db(insert_test, '4321', dt, None, 'Updated') self.assertEqual(db(select_all).cursor.rowcount, 3) self.assertEqual( db('select * from dzdb_test_table').cursor._rows[2][4], "Updated") db('drop table dzdb_test_table') def test_last_rowid(self): db = self.db select_all = 'select * from dz_test_contacts' db('drop table if exists dz_test_contacts') db("""create table dz_test_contacts (contactid integer PRIMARY KEY AUTO_INCREMENT,userid char(20) UNIQUE, key (userid),password char(16), email char(60), key (email))""") db("""insert into dz_test_contacts values (1,"testuser","pass","*****@*****.**")""") self.assertEqual(db.lastrowid, 1) db("""insert into dz_test_contacts values (4,"2testuser","pass","*****@*****.**")""") self.assertEqual(db.lastrowid, 4) db.execute_many( """insert into dz_test_contacts (userid, password, email) values (%s,%s,%s)""", [ ('user3', 'pass3', '*****@*****.**'), ('user4', 'pass4', '*****@*****.**'), ('user5', 'pass5', '*****@*****.**'), ('user6', 'pass6', '*****@*****.**'), ]) self.assertEqual(db.lastrowid, 5) self.assertEqual(db(select_all).cursor.rowcount, 6) db('drop table dz_test_contacts') def test_record(self): db = self.db db("""create table dzdb_test_table (ID CHAR(10), AMOUNT NUMERIC(10,2), DTADD DATE, NOTES TEXT)""") db("""insert into dzdb_test_table values ("1234", 50, "2005-01-14", "Hello there")""") recordset = db('select * from dzdb_test_table') for rec in recordset: self.assertEqual(rec, ('1234', 50, date(2005, 1, 14), "Hello there")) db('drop table dzdb_test_table') def test_metadata(self): db = self.db db('drop table if exists dzdb_test_table') db("""create table dzdb_test_table (ID CHAR(10), AMOUNT NUMERIC(10,2), DTADD DATE, NOTES TEXT)""") db("""insert into dzdb_test_table values ("1234", 50, "2005-01-14", "Hello there")""") q = db('select * from dzdb_test_table') rec = [f[0] for f in q.cursor.description] self.assertEqual(rec, ['ID', 'AMOUNT', 'DTADD', 'NOTES']) db('drop table dzdb_test_table') def test_Database_decimal(self): db = self.db db('drop table if exists dzdb_test_table') db("""create table dzdb_test_table (ID CHAR(10), AMOUNT NUMERIC(10,2), DTADD DATE, NOTES TEXT, BUCKS DECIMAL(8,2))""") db( """insert into dzdb_test_table values ("100", 10.24, %s, "notes", 24.10)""", date(2014, 1, 1)) t = db('select * from dzdb_test_table') for row in t: self.assertEqual(row[-1], Decimal("24.10")) db('drop table dzdb_test_table')
class TestUser(unittest.TestCase): def setUp(self): params = dict( host='database', user='******', passwd='password', db='test', ) now = datetime.datetime(2016, 10, 11, 13, 12, 1) self.db = Database(MySQLdb.Connect, **params) self.db.autocommit(1) # Setup users table # ------------------------------------------------- self.db("DROP TABLE IF EXISTS `dz_users`") self.db(""" CREATE TABLE `dz_users` ( `userid` int(5) NOT NULL auto_increment, `loginid` char(50) default NULL, `password` varchar(125) default NULL, `firstname` char(40) default NULL, `lastname` char(40) default NULL, `email` char(60) default NULL, `phone` char(30) default NULL, `fax` char(30) default NULL, `dtupd` datetime default NULL, `dtadd` datetime default NULL, `status` char(1) default NULL, PRIMARY KEY (`userid`), UNIQUE KEY `userid` (`loginid`), KEY `userid_2` (`loginid`), KEY `email` (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; """) records = [ ('admin', 'admin', 'Admin', 'User', 'A', now, now), ('manager1', 'pass1', 'Manager', 'One', 'A', now, now), ('user1', 'pass2', 'User', 'One', 'A', now, now), ('user2', 'pass3', 'User', 'Two', 'A', now, now), ] self.db.execute_many(""" insert into dz_users (loginid, password, firstname, lastname, status, dtupd, dtadd) values (%s, old_password(%s), %s, %s, %s, %s, %s) """, records) # Setup groups table # ------------------------------------------------- self.db("DROP TABLE IF EXISTS `dz_groups`") self.db(""" CREATE TABLE `dz_groups` ( `groupid` int(11) NOT NULL auto_increment, `type` char(1) default NULL, `name` char(20) default NULL, `descr` char(60) default NULL, `admin` char(20) default NULL, PRIMARY KEY (`groupid`), UNIQUE KEY `name` (`name`), KEY `name_2` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; """) records = [ (1, 'U','administrators','System Administrators','administrators'), (2, 'U','users','Registered Users','administrators'), (3, 'U','guests','Guests','administrators'), (4, 'U','everyone','All users including guests','administrators'), (5, 'U','managers','Site Content Managers','administrators'), ] self.db.execute_many(""" insert into dz_groups values (%s, %s, %s, %s, %s) """, records) # Setup members table # ------------------------------------------------- self.db("DROP TABLE IF EXISTS `dz_members`") self.db(""" CREATE TABLE `dz_members` ( `userid` int(11) default NULL, `groupid` int(11) default NULL, UNIQUE KEY `contactid_2` (`userid`,`groupid`), KEY `contactid` (`userid`), KEY `groupid` (`groupid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; """) records = [ # admins (1, 1), # users (1, 2), (2, 2), (3, 2), (4, 2), # managers (2, 5), ] self.db.execute_many(""" insert into dz_members values (%s, %s) """, records) # Setup subgroups table # ------------------------------------------------- self.db("DROP TABLE IF EXISTS `dz_subgroups`") self.db(""" CREATE TABLE `dz_subgroups` ( `groupid` int(11) default NULL, `subgroupid` int(11) default NULL, UNIQUE KEY `groupid_2` (`groupid`,`subgroupid`), KEY `groupid` (`groupid`), KEY `subgroupid` (`subgroupid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; """) records = [ # admin (2, 1), # admins are subgroup of users (5, 1), # admins are subgroup of managers # users (4, 2), # users are subgroup of everyone # guests (4, 3), # guests are subgroup of everyone # Managers (2, 5), # managers are subgroup of users ] self.db.execute_many(""" insert into dz_subgroups values (%s, %s) """, records) # setup the system and install our own test database system.setup(os.path.expanduser('~')) system.db = self.db system.users = UserStore(self.db) # for authenticate method system.database = LegacyDatabase(MySQLdb.Connect, **params) # used by user.update_user, called by authenticate method system.database.autocommit(1) print self.db('select * from dz_users') print self.db('select * from dz_groups') print self.db('select * from dz_subgroups') print self.db('select * from dz_members') def tearDown(self): for name in ['dz_users','dz_groups', 'dz_subgroups', 'dz_memmbers']: self.db("DROP TABLE IF EXISTS `{}`".format(name)) self.db.close() def test_update_hash_scheme(self): old_hash = list(self.db('select password from dz_users'))[0][0] authenticated = authenticate('admin', 'admin') assert authenticated == True new_hash = list(self.db('select password from dz_users'))[0][0] assert old_hash <> new_hash assert len(old_hash) < len(new_hash) assert new_hash.startswith('$bcrypt')
class TestStore(unittest.TestCase): def setUp(self): params = dict( host='database', user='******', passwd='password', db='test', ) self.db = Database(MySQLdb.Connect, **params) self.db.autocommit(1) self.people = EntityStore(self.db, Person) self.joe_id = self.people.put(Person(name='Joe', age=50)) self.sam_id = self.people.put(Person(name='Sam', age=25)) self.people.put(Person(name='Ann', age=30)) def tearDown(self): self.people.zap() self.db.close() def test_put(self): jane_id = self.people.put(Person(name='Jane', age=25)) person = self.people.get(jane_id) self.assertEqual(dict(person), dict(_id=jane_id, name='Jane', age=25)) def test_get(self): joe = self.people.get(self.joe_id) self.assertEqual(dict(joe), dict(_id=self.joe_id, name='Joe', age=50)) def test_get_missing(self): joe = Person(name='Joe', age=50) joe_id = self.people.put(joe) person = self.people.get(joe_id) self.assertEqual(None, self.people.get(joe_id + 1)) def test_get_multiple(self): def sort_order(item): return keys.index(item['_id']) keys = [self.sam_id, self.joe_id] r = self.people.get(keys) sam = self.people.get(self.sam_id) joe = self.people.get(self.joe_id) self.assertEqual(sorted(r, key=sort_order), [sam, joe]) def test_get_put_get(self): sam = self.people.get(self.sam_id) self.assertEqual(sam.age, 25) self.assertEqual(len(self.people), 3) sam.age += 1 self.people.put(sam) self.assertEqual(len(self.people), 3) person = self.people.get(self.sam_id) self.assertEqual(person.age, 26) def test_delete_by_entity(self): sam = self.people.get(self.sam_id) self.assert_(sam) self.people.delete(sam) sam = self.people.get(self.sam_id) self.assertEqual(None, sam) def test_delete_by_id(self): sam = self.people.get(self.sam_id) self.assert_(sam) self.people.delete(self.sam_id) sam = self.people.get(self.sam_id) self.assertEqual(None, sam) def test_none(self): al_id = self.people.put(Person(name='Al', age=None)) al = self.people.get(al_id) self.assertEqual(al.age, None) def test_bool(self): al_id = self.people.put(Person(name='Al', done=False)) al = self.people.get(al_id) self.assertEqual(al.done, False) al.done = True self.people.put(al) person = self.people.get(al_id) self.assertEqual(person.done, True) def test_kind(self): self.assertEqual(self.people.kind, 'person') self.assertEqual(EntityStore(self.db, TestPerson).kind, 'test_person') def test_len(self): self.assertEqual(3, len(self.people)) def test_zap(self): self.assertEqual(3, len(self.people)) self.people.zap() self.assertEqual(0, len(self.people))
class TestCollect(unittest.TestCase): def setUp(self): # setup the system and install our own test database system.setup(os.path.expanduser('~')) user.initialize('guest') user.groups = ['managers'] params = dict( host='database', user='******', passwd='password', db='test', ) self.db = Database(MySQLdb.Connect, **params) self.db.autocommit(1) system.db = self.db # create the test collection self.collection = Collection('People', person_fields, Person, url='/myapp') # so we can see our print statements self.save_stdout = sys.stdout sys.stdout = sys.stderr def tearDown(self): # remove our test data self.collection.store.zap() self.db.close() sys.stdout = self.save_stdout def test_insert(self): self.collection.store.zap() t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content) insert_record_input = dict( CREATE_BUTTON='y', NAME='Joe', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), ) t = self.collection('new', **insert_record_input) t = self.collection() assert_same(VIEW_SINGLE_RECORD_LIST, t.content) def test_delete(self): self.collection.store.zap() t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content) joe_input = dict( CREATE_BUTTON='y', NAME='Joe', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), ) t = self.collection('new', **joe_input) sally_input = dict( CREATE_BUTTON='y', NAME='Sally', ADDRESS='123 Special St', SALARY=Decimal('45000'), ) t = self.collection('new', **sally_input) t = self.collection() assert_same(VIEW_TWO_RECORD_LIST, t.content) self.collection('delete', 'joe', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_NO_JOE_LIST, t.content) self.collection('delete', 'sally', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content) def test_update(self): self.collection.store.zap() t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content) joe_input = dict( CREATE_BUTTON='y', NAME='Joe', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), ) t = self.collection('new', **joe_input) sally_input = dict( CREATE_BUTTON='y', NAME='Sally', ADDRESS='123 Special St', SALARY=Decimal('45000'), ) t = self.collection('new', **sally_input) self.collection('joe', 'edit', **dict( SAVE_BUTTON='y', NAME='Jim', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), )) t = self.collection() assert_same(VIEW_UPDATED_JOE_LIST, t.content) self.collection('delete', 'jim', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_NO_JOE_LIST, t.content) self.collection('delete', 'sally', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content) def test_authorized_editors(self): self.collection.store.zap() t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content) joe_input = dict( CREATE_BUTTON='y', NAME='Joe', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), ) t = self.collection('new', **joe_input) sally_input = dict( CREATE_BUTTON='y', NAME='Sally', ADDRESS='123 Special St', SALARY=Decimal('45000'), ) t = self.collection('new', **sally_input) t = self.collection() assert_same(VIEW_TWO_RECORD_LIST, t.content) # only authorized users can edit collections user.groups = [] with self.assertRaises(UnauthorizedException): self.collection('joe', 'edit', **dict( SAVE_BUTTON='y', NAME='Jim', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), )) t = self.collection() assert_same(VIEW_TWO_RECORD_LIST, t.content) user.groups = ['managers'] self.collection('joe', 'edit', **dict( SAVE_BUTTON='y', NAME='Jim', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), )) t = self.collection() assert_same(VIEW_UPDATED_JOE_LIST, t.content) user.groups = [] with self.assertRaises(UnauthorizedException): self.collection('delete', 'jim', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_UPDATED_JOE_LIST, t.content) user.groups = ['managers'] self.collection('delete', 'jim', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_NO_JOE_LIST, t.content) self.collection('delete', 'sally', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content) def test_private(self): class PrivatePerson(Person): def allows(self, user, action=None): def is_owner(user): return user.user_id == self.owner_id def is_user(user): return user.is_authenticated actions = { 'create': is_user, 'read': is_owner, 'update': is_owner, 'delete': is_owner, } return actions.get(action)(user) #def private(rec, user, action=None): #return rec.owner == user.user_id self.collection = Collection('People', person_fields, PrivatePerson, url='/myapp') self.collection.can_edit = lambda: True #self.collection.authorization = private self.collection.store.zap() t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content) # user one inserts two records joe_input = dict( CREATE_BUTTON='y', NAME='Jim', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), ) t = self.collection('new', **joe_input) sally_input = dict( CREATE_BUTTON='y', NAME='Sally', ADDRESS='123 Special St', SALARY=Decimal('45000'), ) t = self.collection('new', **sally_input) t = self.collection() assert_same(VIEW_UPDATED_JOE_LIST, t.content) # user two inserts one record user.initialize('admin') self.collection('new', **dict( CREATE_BUTTON='y', NAME='Joe', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), )) t = self.collection() assert_same(VIEW_SINGLE_RECORD_LIST, t.content) # user one can still only see theirs user.initialize('guest') t = self.collection() assert_same(VIEW_UPDATED_JOE_LIST, t.content) # user can't read records that belong to others with self.assertRaises(UnauthorizedException): t = self.collection('joe') # user can't edit records that belong to others with self.assertRaises(UnauthorizedException): t = self.collection('joe', 'edit') # user can't do delete confirmation for records that belong to others with self.assertRaises(UnauthorizedException): t = self.collection('joe', 'delete') # user can't update records that belong to others with self.assertRaises(UnauthorizedException): t = self.collection('joe', 'edit', **dict( SAVE_BUTTON='y', NAME='Andy', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), )) # user can't delete records that belong to others with self.assertRaises(UnauthorizedException): self.collection('joe', 'delete', **{'CONFIRM': 'NO'}) # switch back to owner and do the same operations user.initialize('admin') self.collection('joe') self.collection('joe', 'edit') self.collection('joe', 'delete') self.collection('joe', 'edit', **dict( SAVE_BUTTON='y', NAME='Andy', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), )) self.collection('andy', 'delete', **{'CONFIRM': 'NO'}) user.initialize('guest') user.groups = ['managers'] self.collection('delete', 'jim', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_NO_JOE_LIST, t.content) self.collection('delete', 'sally', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content) def test_published(self): class PrivatePerson(Person): def allows(self, user, action=None): def is_owner(user): return user.user_id == self.owner_id def is_user(user): return user.is_authenticated actions = { 'create': is_user, 'read': is_user, 'update': is_owner, 'delete': is_owner, } return actions.get(action)(user) self.collection = Collection('People', person_fields, PrivatePerson, url='/myapp') self.collection.can_edit = lambda: True self.collection.store.zap() t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content) # user one inserts two records user.initialize('user') assert user.is_authenticated user.groups = ['managers'] joe_input = dict( CREATE_BUTTON='y', NAME='Jim', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), ) t = self.collection('new', **joe_input) sally_input = dict( CREATE_BUTTON='y', NAME='Sally', ADDRESS='123 Special St', SALARY=Decimal('45000'), ) t = self.collection('new', **sally_input) t = self.collection() assert_same(VIEW_UPDATED_JOE_LIST, t.content) # user two inserts one record user.initialize('admin') self.collection('new', **dict( CREATE_BUTTON='y', NAME='Joe', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), )) t = self.collection() assert_same(VIEW_ALL_RECORDS_LIST, t.content) # user one can also see all user.initialize('user') t = self.collection() assert_same(VIEW_ALL_RECORDS_LIST, t.content) # guest can't read records user.initialize('guest') with self.assertRaises(UnauthorizedException): t = self.collection('joe') # authenticated user can read records that belong to others user.initialize('user') t = self.collection('joe') # user can't edit records that belong to others user.initialize('guest') with self.assertRaises(UnauthorizedException): t = self.collection('joe', 'edit') # user can't edit records that belong to others user.initialize('user') with self.assertRaises(UnauthorizedException): t = self.collection('joe', 'edit') # guest can't do delete confirmation for records that belong to others user.initialize('guest') with self.assertRaises(UnauthorizedException): t = self.collection('joe', 'delete') # user can't do delete confirmation for records that belong to others user.initialize('user') with self.assertRaises(UnauthorizedException): t = self.collection('joe', 'delete') # user can't update records that belong to others with self.assertRaises(UnauthorizedException): t = self.collection('joe', 'edit', **dict( SAVE_BUTTON='y', NAME='Andy', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), )) # user can't delete records that belong to others with self.assertRaises(UnauthorizedException): self.collection('joe', 'delete', **{'CONFIRM': 'NO'}) # switch back to owner and do the same operations user.initialize('admin') self.collection('joe') self.collection('joe', 'edit') self.collection('joe', 'delete') self.collection('joe', 'edit', **dict( SAVE_BUTTON='y', NAME='Andy', ADDRESS='123 Somewhere St', SALARY=Decimal('40000'), )) self.collection('andy', 'delete', **{'CONFIRM': 'NO'}) # guest can't delete user.initialize('guest') user.groups = ['managers'] with self.assertRaises(UnauthorizedException): self.collection('delete', 'jim', **{'CONFIRM': 'NO'}) # guest can't delete with self.assertRaises(UnauthorizedException): self.collection('delete', 'sally', **{'CONFIRM': 'NO'}) # non-owner can't delete user.initialize('admin') user.groups = ['managers'] with self.assertRaises(UnauthorizedException): self.collection('delete', 'jim', **{'CONFIRM': 'NO'}) # non-owner can't delete with self.assertRaises(UnauthorizedException): self.collection('delete', 'sally', **{'CONFIRM': 'NO'}) # owner can delete user.initialize('user') user.groups = ['managers'] self.collection('delete', 'jim', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_NO_JOE_LIST, t.content) self.collection('delete', 'sally', **{'CONFIRM': 'NO'}) t = self.collection() assert_same(VIEW_EMPTY_LIST, t.content)
class TestDb(unittest.TestCase): """test db module""" # pylint: disable=too-many-public-methods # It's reasonable in this case. def setUp(self): self.db = Database( MySQLdb.Connect, host='database', user='******', passwd='password', db='test' ) def tearDown(self): self.db.close() def test_RecordSet(self): db = self.db db("""drop table if exists dzdb_test_table""") db("""create table dzdb_test_table (ID CHAR(10), AMOUNT NUMERIC(10,2),DTADD DATE,NOTES TEXT)""") db("""insert into dzdb_test_table values ("1234",50,"2005-01-14","Hello there")""") db("""insert into dzdb_test_table values ("5678",60,"2035-01-24","New notes")""") recordset = db('select * from dzdb_test_table') print recordset for rec in recordset: self.assertEquals(rec, ("1234", 50, "2005-01-14", "Hello there")) break def test_db_create_drop_table(self): table_names = lambda a: [x[0] for x in a('show tables')] db = self.db db('drop table if exists dzdb_test_table') self.assert_('dzdb_test_table' not in table_names(db)) db("""create table dzdb_test_table (ID CHAR(10),AMOUNT NUMERIC(10,2),DTADD DATE,NOTES TEXT)""") self.assert_('dzdb_test_table' in table_names(db)) db('drop table dzdb_test_table') self.assert_('dzdb_test_table' not in table_names(db)) def test_db_insert_update_record(self): # pylint: disable=protected-access insert_test = """insert into dzdb_test_table (ID, DTADD, amount, notes) values (%s, %s, %s, %s)""" select_all = 'select * from dzdb_test_table' db = self.db db('drop table if exists dzdb_test_table') db("""create table dzdb_test_table (ID CHAR(10), NAME CHAR(25), AMOUNT NUMERIC(10,2), DTADD DATE, NOTES TEXT)""") dt = datetime(2005, 1, 2) db(insert_test, '1234', dt, 50, 'Testing') self.assertEqual(db(select_all).cursor.rowcount, 1) db(insert_test, '4321', dt, 10.20, 'Testing 2') self.assertEqual(db(select_all).cursor.rowcount, 2) db(insert_test, '4321', dt, None, 'Updated') self.assertEqual(db(select_all).cursor.rowcount, 3) self.assertEqual( db( 'select * from dzdb_test_table' ).cursor._rows[2][4], "Updated") db('drop table dzdb_test_table') def test_last_rowid(self): db = self.db select_all = 'select * from dz_test_contacts' db('drop table if exists dz_test_contacts') db("""create table dz_test_contacts (contactid integer PRIMARY KEY AUTO_INCREMENT,userid char(20) UNIQUE, key (userid),password char(16), email char(60), key (email))""") db("""insert into dz_test_contacts values (1,"testuser","pass","*****@*****.**")""") self.assertEqual(db.lastrowid, 1) db("""insert into dz_test_contacts values (4,"2testuser","pass","*****@*****.**")""") self.assertEqual(db.lastrowid, 4) db.execute_many( """insert into dz_test_contacts (userid, password, email) values (%s,%s,%s)""", [ ('user3', 'pass3', '*****@*****.**'), ('user4', 'pass4', '*****@*****.**'), ('user5', 'pass5', '*****@*****.**'), ('user6', 'pass6', '*****@*****.**'), ]) self.assertEqual(db.lastrowid, 5) self.assertEqual(db(select_all).cursor.rowcount, 6) db('drop table dz_test_contacts') def test_record(self): db = self.db db("""create table dzdb_test_table (ID CHAR(10), AMOUNT NUMERIC(10,2), DTADD DATE, NOTES TEXT)""") db("""insert into dzdb_test_table values ("1234", 50, "2005-01-14", "Hello there")""") recordset = db('select * from dzdb_test_table') for rec in recordset: self.assertEqual( rec, ('1234', 50, date(2005, 1, 14), "Hello there") ) db('drop table dzdb_test_table') def test_metadata(self): db = self.db db('drop table if exists dzdb_test_table') db("""create table dzdb_test_table (ID CHAR(10), AMOUNT NUMERIC(10,2), DTADD DATE, NOTES TEXT)""") db("""insert into dzdb_test_table values ("1234", 50, "2005-01-14", "Hello there")""") q = db('select * from dzdb_test_table') rec = [f[0] for f in q.cursor.description] self.assertEqual(rec, ['ID', 'AMOUNT', 'DTADD', 'NOTES']) db('drop table dzdb_test_table') def test_Database_decimal(self): db = self.db db('drop table if exists dzdb_test_table') db("""create table dzdb_test_table (ID CHAR(10), AMOUNT NUMERIC(10,2), DTADD DATE, NOTES TEXT, BUCKS DECIMAL(8,2))""") db("""insert into dzdb_test_table values ("100", 10.24, %s, "notes", 24.10)""", date(2014, 1, 1)) t = db('select * from dzdb_test_table') for row in t: self.assertEqual(row[-1], Decimal("24.10")) db('drop table dzdb_test_table')