def _sqlexec_add_record():
M.add_local('fake', 'fake_name', 'fake_st', 'fake_quantity',
'fake_received')
S.fake = faker.Faker()
S.fake.seed(S.seed)
S.fake_name = S.fake.name()
S.fake_st = S.fake.state_abbr()
S.fake_quantity = S.fake.random_int(0, 100)
S.fake_received = S.fake.boolean()
S.sqlcmd = "insert into REPORT values ('" + S.fake_name + "','" + S.fake_st + "'," + vfpfunc.str(
S.fake_quantity).strip() + ',' + vfpfunc.str(int(
S.fake_received)).strip() + ')'
print(S.sqlcmd)
return vfpfunc.sqlexec(S.sqlconn, S.sqlcmd)
def _sqlexec_add_record(sqlconn=False, seed=False):
vfpvar.pushscope()
fake = fake_name = fake_st = fake_quantity = fake_received = False # LOCAL Declaration
fake = faker.Faker()
fake.seed(seed)
fake_name = fake.name()
fake_st = fake.state_abbr()
fake_quantity = fake.random_int(0, 100)
fake_received = fake.boolean()
vfpvar[
'sqlcmd'] = "insert into REPORT values ('" + fake_name + "','" + fake_st + "'," + vfpfunc.num_to_str(
fake_quantity).strip() + ',' + vfpfunc.num_to_str(
int(fake_received)).strip() + ')'
print(vfpvar['sqlcmd'])
return vfpvar.popscope(vfpfunc.sqlexec(sqlconn, vfpvar['sqlcmd']))
def database_tests():
# FIX ME: SET SAFETY OFF
# FIX ME: SET ASSERTS ON
try:
DB.create_table('report',
'name c(50); st c(2); quantity n(5, 0); received l',
'free')
assert os.path.isfile('report.dbf')
assert DB.used('report')
try:
DB.use('report', 0, 'shared')
assert False
except Exception as err:
S.oerr = vfpfunc.Exception.from_pyexception(err)
print(S.oerr.message)
assert S.oerr.message == 'File is in use.'
_add_db_record(0)
_add_db_record(1)
_add_db_record(2)
_add_db_record(3)
assert DB.cpdbf() == 0
assert DB.fcount() == 4
DB.alter_table('report', 'add', 'age n(3, 0)')
assert DB.fcount() == 5
assert DB.field(2) == 'st'
assert not DB.found()
DB.goto(None, 0)
M.add_local('loopcount')
S.loopcount = 0
for _ in DB.scanner(scope=('rest', )):
assert len(S.name.strip()) > 0
S.loopcount += 1
assert S.loopcount == 4
DB.goto(None, 3)
S.loopcount = 0
for _ in DB.scanner(scope=('all', ),
condition=lambda: S.st.strip() == 'ID'):
assert len(S.name.strip()) > 0
S.loopcount += 1
assert S.loopcount == 2
S.loopcount = 0
for _ in DB.scanner(scope=('rest', ),
condition=lambda: S.st.strip() == 'ID'):
assert len(S.name.strip()) > 0
S.loopcount += 1
assert S.loopcount == 0
DB.goto(None, 0)
S.loopcount = 0
for _ in DB.scanner(scope=('rest', ),
condition=lambda: S.st.strip() == 'ID'):
assert len(S.name.strip()) > 0
S.loopcount += 1
assert S.loopcount == 2
del M.loopcount
assert S.name.strip(
) == 'Norma Fisher', S.name.strip() + ' should be Norma Fisher'
assert DB.recno() == 1
S.report_record = vfpfunc.scatter(totype='name')
assert S.report_record.name.strip(
) == 'Norma Fisher', S.report_record.name.strip(
) + ' should be Norma Fisher'
DB.goto(None, -1)
assert S.name.strip(
) == 'Joshua Wood', S.name.strip() + ' should be Joshua Wood'
assert S.report_record.name.strip(
) == 'Norma Fisher', S.report_record.name.strip(
) + ' should be Norma Fisher'
assert DB.recno() == 4
DB.goto(None, 1)
DB.locate(for_cond=lambda: S.st == 'ID')
assert S.name.strip(
) == 'Norma Fisher', S.name.strip() + ' should be Norma Fisher'
assert DB.found()
DB.continue_locate()
assert S.name.strip(
) == 'Ryan Gallagher', S.name.strip() + ' should be Ryan Gallagher'
DB.continue_locate()
assert DB.eof()
assert DB.recno() == DB.reccount() + 1
assert not DB.found()
S.countval = DB.count(None, ('all', ),
for_cond=lambda: S.quantity > 60)
assert S.countval == 2
assert DB.eof()
S.sumval = DB.sum(None, ('rest', ),
lambda: math.sqrt(S.quantity + 205),
for_cond=lambda: S.quantity > 50,
while_cond=lambda: S.quantity != 63)
assert S.sumval == 0
DB.goto(None, 0)
S.sumval = DB.sum(None, ('rest', ),
lambda: math.sqrt(S.quantity + 205),
for_cond=lambda: S.quantity > 50,
while_cond=lambda: S.quantity != 63)
assert S.sumval == 17 + 16
DB.index_on('st', 'st', 'ascending', True, False, False)
DB.seek(None, 'CA')
assert S.st.strip() == 'CA'
DB.goto(None, 0)
DB.delete_record(None, ('rest', ), for_cond=lambda: S.quantity > 60)
DB.pack('both', None, None)
DB.goto(None, 0)
assert DB.reccount() == 2
DB.replace('report', ('next', 1), 'name', 'N/A')
assert S.name.strip() == 'N/A'
DB.replace(None, ('all', ), 'name', 'Not Available')
assert DB.recno() == DB.reccount() + 1
DB.goto(None, -1)
assert S.name.strip() == 'Not Available'
DB.zap(None)
assert DB.reccount() == 0
DB.copy_structure('report2')
DB.use('report2', 0, 'shared', alias='somethingelse')
assert DB.alias() == 'report'
DB.select('report2')
assert DB.alias() == 'somethingelse'
assert DB.fcount() == 5
DB.alter_table('report2', 'drop', 'st')
assert DB.fcount() == 4
assert S.report_record.name.strip(
) == 'Norma Fisher', S.report_record.name.strip(
) + ' should be Norma Fisher'
DB.append(None, False)
vfpfunc.gather(val=S.report_record)
assert S.name.strip(
) == 'Norma Fisher', S.name.strip() + ' should be Norma Fisher'
assert DB.dbf() == 'report2.dbf'
DB.use(None, DB.select_function('report2'), None)
DB.use(None, DB.select_function('report'), None)
os.remove('report2.dbf')
except Exception as err:
S.err = vfpfunc.Exception.from_pyexception(err)
print(S.err.message)
DB.browse()
raise
finally:
os.remove('report.dbf')
S.sqlconn = vfpfunc.sqlconnect('testodbc')
assert S.sqlconn > 0
assert vfpfunc.sqlexec(
S.sqlconn,
'CREATE TABLE REPORT (NAME varchar(50), ST char(2), QUANTITY int(5), RECEIVED bit)'
) > 0
assert _sqlexec_add_record(S.sqlconn, 0) > 0
assert _sqlexec_add_record(S.sqlconn, 1) > 0
assert _sqlexec_add_record(S.sqlconn, 2) > 0
assert _sqlexec_add_record(S.sqlconn, 3) > 0
assert vfpfunc.sqlexec(S.sqlconn, 'SELECT * FROM REPORT')
DB.select('sqlresult')
assert S.name.strip() == 'Norma Fisher'
vfpfunc.sqlcommit(S.sqlconn)
vfpfunc.sqldisconnect(S.sqlconn)
S.sqlconn = vfpfunc.sqlstringconnect('dsn=testodbc')
assert S.sqlconn > 0
assert vfpfunc.sqltables(S.sqlconn) > 0
DB.select('sqlresult')
assert S.table_name.strip().lower() == 'report'
assert vfpfunc.sqlexec(S.sqlconn, 'DELETE FROM REPORT;')
assert vfpfunc.sqlrollback(S.sqlconn)
assert vfpfunc.sqlexec(S.sqlconn, 'SELECT * FROM REPORT')
DB.select('sqlresult')
assert S.name.strip() == 'Norma Fisher'
assert vfpfunc.sqlexec(S.sqlconn, 'DROP TABLE REPORT') > 0
vfpfunc.sqlcommit(S.sqlconn)
vfpfunc.sqldisconnect(S.sqlconn)
DB.close_tables(False)
def database_tests():
vfpvar.pushscope()
# FIX ME: SET SAFETY OFF
# FIX ME: SET ASSERTS ON
try:
vfpfunc.db.create_table(
'report', 'name c(50); st c(2); quantity n(5, 0); received l',
'free')
assert os.path.isfile('report.dbf')
assert vfpfunc.db.used('report')
try:
vfpfunc.db.use('report', 0, 'shared')
assert False
except Exception as oerr:
oerr = vfpfunc.Exception.from_pyexception(oerr)
print(oerr.message)
assert oerr.message == 'File is in use.'
_add_db_record(0)
_add_db_record(1)
_add_db_record(2)
_add_db_record(3)
assert vfpfunc.db.fcount() == 4
vfpfunc.db.alter_table('report', 'add', 'age n(3, 0)')
assert vfpfunc.db.fcount() == 5
assert vfpfunc.db.field(2) == 'st'
assert not vfpfunc.db.found()
vfpfunc.db.goto(None, 0)
loopcount = False # LOCAL Declaration
loopcount = 0
for _ in vfpfunc.db.scanner(scope=('rest', )):
assert len(vfpvar['name'].strip()) > 0
loopcount += 1
assert loopcount == 4
vfpfunc.db.goto(None, 3)
loopcount = 0
for _ in vfpfunc.db.scanner(
scope=('all', ),
condition=lambda: vfpvar['st'].strip() == 'ID'):
assert len(vfpvar['name'].strip()) > 0
loopcount += 1
assert loopcount == 2
loopcount = 0
for _ in vfpfunc.db.scanner(
scope=('rest', ),
condition=lambda: vfpvar['st'].strip() == 'ID'):
assert len(vfpvar['name'].strip()) > 0
loopcount += 1
assert loopcount == 0
vfpfunc.db.goto(None, 0)
loopcount = 0
for _ in vfpfunc.db.scanner(
scope=('rest', ),
condition=lambda: vfpvar['st'].strip() == 'ID'):
assert len(vfpvar['name'].strip()) > 0
loopcount += 1
assert loopcount == 2
del loopcount
assert vfpvar['name'].strip(
) == 'Norma Fisher', vfpvar['name'].strip() + ' should be Norma Fisher'
assert vfpfunc.db.recno() == 1
vfpfunc.db.goto(None, -1)
assert vfpvar['name'].strip(
) == 'Joshua Wood', vfpvar['name'].strip() + ' should be Joshua Wood'
assert vfpfunc.db.recno() == 4
vfpfunc.db.goto(None, 1)
vfpfunc.db.locate(for_cond=lambda: vfpvar['st'] == 'ID')
assert vfpvar['name'].strip(
) == 'Norma Fisher', vfpvar['name'].strip() + ' should be Norma Fisher'
assert vfpfunc.db.found()
vfpfunc.db.continue_locate()
assert vfpvar['name'].strip() == 'Ryan Gallagher', vfpvar[
'name'].strip() + ' should be Ryan Gallagher'
vfpfunc.db.continue_locate()
assert vfpfunc.db.eof()
assert vfpfunc.db.recno() == vfpfunc.db.reccount() + 1
assert not vfpfunc.db.found()
vfpvar['countval'] = vfpfunc.db.count(
None, ('all', ), for_cond=lambda: vfpvar['quantity'] > 60)
assert vfpvar['countval'] == 2
assert vfpfunc.db.eof()
vfpvar['sumval'] = vfpfunc.db.sum(
None, ('rest', ),
lambda: math.sqrt(vfpvar['quantity'] + 205),
for_cond=lambda: vfpvar['quantity'] > 50,
while_cond=lambda: vfpvar['quantity'] != 63)
assert vfpvar['sumval'] == 0
vfpfunc.db.goto(None, 0)
vfpvar['sumval'] = vfpfunc.db.sum(
None, ('rest', ),
lambda: math.sqrt(vfpvar['quantity'] + 205),
for_cond=lambda: vfpvar['quantity'] > 50,
while_cond=lambda: vfpvar['quantity'] != 63)
assert vfpvar['sumval'] == 17 + 16
vfpfunc.db.index_on('st', 'st', 'ascending', True, False, False)
vfpfunc.db.seek(None, 'CA')
assert vfpvar['st'].strip() == 'CA'
vfpfunc.db.goto(None, 0)
vfpfunc.db.delete_record(None, ('rest', ),
for_cond=lambda: vfpvar['quantity'] > 60)
vfpfunc.db.pack('both', None, None)
vfpfunc.db.goto(None, 0)
assert vfpfunc.db.reccount() == 2
vfpfunc.db.replace('report', ('next', 1), 'name', 'N/A')
assert vfpvar['name'].strip() == 'N/A'
vfpfunc.db.replace(None, ('all', ), 'name', 'Not Available')
assert vfpfunc.db.recno() == vfpfunc.db.reccount() + 1
vfpfunc.db.goto(None, -1)
assert vfpvar['name'].strip() == 'Not Available'
vfpfunc.db.zap(None)
assert vfpfunc.db.reccount() == 0
vfpfunc.db.copy_structure('report2')
vfpfunc.db.use('report2', 0, 'shared', alias='somethingelse')
assert vfpfunc.db.alias() == 'report'
vfpfunc.db.select('report2')
assert vfpfunc.db.alias() == 'somethingelse'
assert vfpfunc.db.fcount() == 5
vfpfunc.db.alter_table('report2', 'drop', 'st')
assert vfpfunc.db.fcount() == 4
vfpfunc.db.use(None, None, None)
os.remove('report2.dbf')
except Exception as err:
err = vfpfunc.Exception.from_pyexception(err)
print(err.message)
vfpfunc.db.browse()
raise
finally:
os.remove('report.dbf')
vfpvar['sqlconn'] = vfpfunc.sqlconnect('testodbc')
assert vfpvar['sqlconn'] > 0
assert vfpfunc.sqlexec(
vfpvar['sqlconn'],
'CREATE TABLE REPORT (NAME varchar(50), ST char(2), QUANTITY int(5), RECEIVED bit)'
) > 0
assert _sqlexec_add_record(vfpvar['sqlconn'], 0) > 0
assert _sqlexec_add_record(vfpvar['sqlconn'], 1) > 0
assert _sqlexec_add_record(vfpvar['sqlconn'], 2) > 0
assert _sqlexec_add_record(vfpvar['sqlconn'], 3) > 0
assert vfpfunc.sqlexec(vfpvar['sqlconn'], 'SELECT * FROM REPORT')
vfpfunc.db.select('sqlresult')
assert vfpvar['name'].strip() == 'Norma Fisher'
vfpfunc.sqlcommit(vfpvar['sqlconn'])
vfpfunc.sqldisconnect(vfpvar['sqlconn'])
vfpvar['sqlconn'] = vfpfunc.sqlstringconnect('dsn=testodbc')
assert vfpvar['sqlconn'] > 0
assert vfpfunc.sqltables(vfpvar['sqlconn']) > 0
vfpfunc.db.select('sqlresult')
assert vfpvar['table_name'].strip().lower() == 'report'
assert vfpfunc.sqlexec(vfpvar['sqlconn'], 'DELETE FROM REPORT;')
assert vfpfunc.sqlrollback(vfpvar['sqlconn'])
assert vfpfunc.sqlexec(vfpvar['sqlconn'], 'SELECT * FROM REPORT')
vfpfunc.db.select('sqlresult')
assert vfpvar['name'].strip() == 'Norma Fisher'
assert vfpfunc.sqlexec(vfpvar['sqlconn'], 'DROP TABLE REPORT') > 0
vfpfunc.sqlcommit(vfpvar['sqlconn'])
vfpfunc.sqldisconnect(vfpvar['sqlconn'])
vfpvar.popscope()
) == 'Norma Fisher', S.name.strip() + ' should be Norma Fisher'
assert DB.dbf() == 'report2.dbf'
DB.use(None, DB.select_function('report2'), None)
DB.use(None, DB.select_function('report'), None)
os.remove('report2.dbf')
except Exception as S.err:
S.err = vfpfunc.Exception.from_pyexception(S.err)
print(S.err.message)
DB.browse()
raise
finally:
os.remove('report.dbf')
S.sqlconn = vfpfunc.sqlconnect('testodbc')
assert S.sqlconn > 0
assert vfpfunc.sqlexec(
S.sqlconn,
'CREATE TABLE REPORT (NAME varchar(50), ST char(2), QUANTITY int(5), RECEIVED bit)'
) > 0
assert _sqlexec_add_record(S.sqlconn, 0) > 0
assert _sqlexec_add_record(S.sqlconn, 1) > 0
assert _sqlexec_add_record(S.sqlconn, 2) > 0
assert _sqlexec_add_record(S.sqlconn, 3) > 0
assert vfpfunc.sqlexec(S.sqlconn, 'SELECT * FROM REPORT')
DB.select('sqlresult')
assert S.name.strip() == 'Norma Fisher'
vfpfunc.sqlcommit(S.sqlconn)
vfpfunc.sqldisconnect(S.sqlconn)
S.sqlconn = vfpfunc.sqlstringconnect('dsn=testodbc')
assert S.sqlconn > 0
assert vfpfunc.sqltables(S.sqlconn) > 0
DB.select('sqlresult')
assert S.table_name.strip().lower() == 'report'