def test_13_index_exists(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
table.index('by_age_name', '{age:03}{name}')
table.index('by_name', '{name}')
table.index('by_age', '{age:03}', duplicates=True)
self.assertTrue(table.exists('by_name'))
db.close()
db = Database(self._db_name)
table = db.table(self._tb_name)
self.assertTrue(table.exists('by_name'))
def test_14_table_empty(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
table.index('by_age_name', '{age:03}{name}')
table.index('by_name', '{name}')
table.index('by_age', '{age:03}', duplicates=True)
self.generate_data(db, self._tb_name)
self.assertEqual(table.records, len(self._data))
table.empty()
table = db.table(self._tb_name)
self.assertEqual(table.records, 0)
def test_12_table_reopen(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
table.index('by_age_name', '{age:03}{name}')
table.index('by_name', '{name}')
table.index('by_age', '{age:03}', duplicates=True)
self.generate_data(db, self._tb_name)
db.close()
db = Database(self._db_name)
table = db.table(self._tb_name)
self.assertEqual(['by_age', 'by_age_name', 'by_name'], table.indexes())
def test_23_function_index(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
self.generate_data(db, self._tb_name)
table.index('by_compound', '{cat}|{name}', duplicates=True)
table.index('by_age', '{age:03}', duplicates=True)
results = []
for doc in table.find('by_compound'):
results.append(doc['cat'])
self.assertEqual(results, ['A', 'A', 'A', 'B', 'B', 'B', 'B'])
table.empty()
table = db.table(self._tb_name)
self.generate_data(db, self._tb_name)
results = []
for doc in table.find('by_compound'):
results.append(doc['cat'])
self.assertEqual(results, ['A', 'A', 'A', 'B', 'B', 'B', 'B'])
for i in table.seek('by_compound', {'cat': 'A', 'name': 'Squizzey'}):
self.assertEqual(i['age'], 3000)
for i in table.seek('by_compound', {'cat': 'B', 'name': 'John Doe'}):
self.assertEqual(i['age'], 40)
self.assertEqual(
list(table.seek('by_compound', {
'cat': 'C',
'name': 'Squizzey'
})), [])
lower = {'cat': 'A', 'name': 'Squizzey'}
upper = {'cat': 'B', 'name': 'Gareth Bult1'}
iter = table.range('by_compound', lower, upper)
results = list(iter)
self.assertEqual(results[0]['name'], 'Squizzey')
self.assertEqual(results[1]['name'], 'Gareth Bult1')
results[0]['name'] = '!Squizzey'
results[0]['age'] = 1
table.save(results[0])
table._indexes['duff'] = None
with self.assertRaises(AttributeError):
table.save(results[0])
self.assertEqual(
list(table.find('by_compound'))[0]['name'], '!Squizzey')
self.assertEqual(list(table.find('by_age'))[0]['age'], 1)
def test_30_debug_range(self):
db = Database(self._db_name, size=size_mb(10))
self.assertTrue(isinstance(db, Database))
sessions = db.table('sessions')
sessions.ensure('by_session_key', 'pfx' + "_{sid}")
sessions.ensure('by_expiry', "{expiry:>12}", duplicates=True)
now = int(datetime.utcnow().timestamp())
sessions.append({
'expiry': now + 3600,
'sid': '0000b3f9cf2e4b43a6bb7b52e9597000'
})
self.assertEqual(len(list(sessions.range('by_expiry'))), 1)
self.assertEqual(
len(
list(
sessions.range('by_expiry',
lower={'expiry': 0},
upper={'expiry': now}))), 0)
sessions.append({
'expiry': now,
'sid': '0000b3f9cf2e4b43a6bb7b52e9597000'
})
self.assertEqual(
len(
list(
sessions.range('by_expiry',
lower={'expiry': 0},
upper={'expiry': now}))), 1)
sessions.append({
'expiry': now,
'sid': '0000b3f9cf2e4b43a6bb7b52e9597000'
})
self.assertEqual(
len(
list(
sessions.range('by_expiry',
lower={'expiry': 0},
upper={'expiry': now}))), 2)
sessions.append({
'expiry': now - 1,
'sid': '0000b3f9cf2e4b43a6bb7b52e9597000'
})
self.assertEqual(
len(
list(
sessions.range('by_expiry',
lower={'expiry': 0},
upper={'expiry': now}))), 3)
sessions.append({
'expiry': now + 4000,
'sid': '0000b3f9cf2e4b43a6bb7b52e9597000'
})
self.assertEqual(
len(
list(
sessions.range('by_expiry',
lower={'expiry': 0},
upper={'expiry': now}))), 3)
def test_16_check_delete_exception(self):
class f(object):
pass
db = Database(self._db_name)
table = db.table(self._tb_name)
with self.assertRaises(TypeError):
table.delete([f])
def test_08_delete(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
for doc in self._data:
table.append(dict(doc))
doc = next(table.find(limit=1))
table.delete(doc)
self.assertEqual(table.records, len(self._data) - 1)
def test_09_create_drop_index(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
table.index('by_name', '{name}')
table.index('by_age', '{age:03}', duplicates=True)
self.assertEqual(table.indexes(), ['by_age', 'by_name'])
db.drop(self._tb_name)
self.assertEqual(db.tables, [])
def test_30_ensure(self):
return
db = Database(self._db_name)
table = db.table(self._tb_name)
self.generate_data1(db, self._tb_name)
with db.begin():
index = table.ensure('by_name', '{name}', True, False)
index = table.ensure('by_name', '{name}', True, False)
index = table.ensure('by_name', '{name}', True, True)
def test_07_empty(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
for doc in self._data:
table.append(dict(doc))
self.assertEqual(table.records, len(self._data))
table.empty()
self.assertEqual(table.records, 0)
self.assertTrue(db.exists('demo1'))
def test_25_seek_one(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
self.generate_data(db, self._tb_name)
table.index('by_age', '{age:03}', duplicates=True)
doc = table.seek_one('by_age', {'age': 3000})
self.assertEqual(doc['age'], 3000)
self.assertEqual(doc['name'], 'Squizzey')
def test_15_check_append_exception(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
table.index('by_age_name', '{age:03}{name}')
table.index('by_name', '{name}')
table.index('by_age', '{age:03}', duplicates=True)
self.generate_data(db, self._tb_name)
table._indexes = 10
before = table.records
def test_19_count_with_txn(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
table.index('by_age_name', '{age:03}{name}')
table.index('by_name', '{name}')
table.index('by_age', '{age:03}', duplicates=True)
self.generate_data(db, self._tb_name)
with db.env.begin() as txn:
index = table.index('by_name')
self.assertTrue(index.count(txn=txn), 7)
def test_29_with_txn(self):
return
db = Database(self._db_name)
table = db.table(self._tb_name)
self.generate_data1(db, self._tb_name)
self.generate_data2(db, self._tb_name)
with db.begin():
with self.assertRaises(xIndexMissing):
next(table.find('123'))
with db.begin():
idx = table.index('by_name', '{name}', duplicates=True)
idx.empty(db.transaction.txn)
self.generate_data1(db, self._tb_name)
with db.begin():
table.drop_index('by_name')
table.index('by_name', '{name}', duplicates=True)
docs = list(table.find())
doc = docs[0]
table.delete(doc)
doc = docs[1]
doc['age'] += 1
table.save(doc)
docs = list(table.find())
doc = docs[0]
self.assertEqual(doc['age'], 3001)
all = db.tables_all
cmp = ['__binlog__', '__metadata__', '_demo1_by_name', 'demo1']
all.sort()
cmp.sort()
self.assertEqual(all, cmp)
db.set_binlog(False)
all = db.tables_all
all.sort()
cmp = ['__metadata__', '_demo1_by_name', 'demo1']
cmp.sort()
self.assertEqual(all, cmp)
db.drop('demo1')
with self.assertRaises(Exception):
with db.begin():
raise Exception('catch this')
self.assertEqual(db.tables_all, ['__metadata__'])
db.set_binlog(False)
db.sync(True)
db = Database(self._db_name, binlog=False)
db.set_binlog()
db.set_binlog(False)
def test_27_drop_reuse(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
self.generate_data(db, self._tb_name)
db.drop(self._tb_name)
table = db.table(self._tb_name)
self.generate_data(db, self._tb_name)
table.index('by_age', '{age:03}', duplicates=True)
doc = table.seek_one('by_age', {'age': 3000})
self.assertEqual(doc['age'], 3000)
self.assertEqual(doc['name'], 'Squizzey')
for doc in table.find():
_id = doc['_id']
name = doc['name']
break
db.restructure(self._tb_name)
table = db.table(self._tb_name)
for doc in table.find():
self.assertEqual(doc['name'], name)
self.assertEqual(doc['_id'], _id)
break
def test_11_compound_index(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
table.index('by_age_name', '{age:03}{name}')
self.generate_data(db, self._tb_name)
ages = [doc['age'] for doc in self._data]
ages.sort()
ages.reverse()
for row in table.find('by_age_name'):
self.assertEqual(row['age'], ages.pop())
with self.assertRaises(ValueError):
table.index('broken', '{')
def test_24_partial_index(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
self.generate_data(db, self._tb_name)
table.index('by_admin', '{admin}', duplicates=True)
try:
for doc in table.find('by_admin'):
pass
except Exception as error:
self.fail('partial key failure')
raise error
self.assertEqual(table.index('by_admin').count(), 3)
with self.assertRaises(AttributeError):
table.unindex('by_admin', 123)
def test_18_check_unindex_exception(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
with self.assertRaises(xIndexMissing):
table.drop_index('fred')
table.index('by_name', '{name}')
self.assertTrue('by_name' in table.indexes())
table.drop_index('by_name')
self.assertFalse('by_name' in table.indexes())
table.index('duff', '{name}')
table._indexes['duff'] = None
with self.assertRaises(AttributeError):
table.drop_index('duff')
def test_22_reindex(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
self.generate_data(db, self._tb_name)
by_age_name = table.index('by_age_name', '{age:03}{name}')
by_name = table.index('by_name', '{name}')
by_age = table.index('by_age', '{age:03}', duplicates=True)
self.assertEqual(by_age_name.count(), 7)
self.assertEqual(by_name.count(), 7)
self.assertEqual(by_age.count(), 7)
table.reindex()
self.assertEqual(by_age_name.count(), 7)
self.assertEqual(by_name.count(), 7)
self.assertEqual(by_age.count(), 7)
def test_20_index_get(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
table.index('by_age_name', '{age:03}{name}')
table.index('by_name', '{name}')
table.index('by_age', '{age:03}', duplicates=True)
self.generate_data(db, self._tb_name)
with db._env.begin() as txn:
index = table.index('by_name')
_id = index.get(txn, {'name': 'Squizzey'})
doc = table.get(_id)
self.assertTrue(doc['age'], 3000)
self.assertTrue(doc['name'], 'Squizzey')
with self.assertRaises(xIndexMissing):
list(table.find('fred', 'fred'))
def test_21_filters(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
table.index('by_age_name', '{age:03}{name}')
table.index('by_name', '{name}')
self.generate_data(db, self._tb_name)
result = list(table.find(expression=lambda doc: doc['age'] == 3000))[0]
self.assertEqual(result['age'], 3000)
self.assertEqual(result['name'], 'Squizzey')
result = list(
table.find('by_name', expression=lambda doc: doc['age'] == 21))[0]
self.assertEqual(result['age'], 21)
self.assertEqual(result['name'], 'Gareth Bult')
result = list(
table.find('by_name',
expression=lambda doc: doc['name'] == 'John Doe'))[0]
self.assertEqual(result['age'], 40)
self.assertEqual(result['name'], 'John Doe')
def test_23_range_text(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
self.generate_data(db, self._tb_name)
table.index('by_compound', '{cat}|{name}', duplicates=True)
table.index('by_age', '{age:03}', duplicates=True)
iter = list(table.range('by_compound'))
self.assertEqual(len(iter), 7)
lower = {'cat': ' ', 'name': ' '}
upper = {'cat': 'z', 'name': 'z'}
iter = list(table.range('by_compound', lower, upper))
self.assertEqual(len(iter), 7)
lower = {'cat': 'A', 'name': 'Fred Bloggs'}
upper = {'cat': 'z', 'name': 'z'}
iter = list(table.range('by_compound', lower, upper))
self.assertEqual(len(iter), 7)
lower = {'cat': 'A', 'name': 'Sq'}
upper = {'cat': 'A', 'name': 'z'}
iter = list(table.range('by_compound', lower, upper))
self.assertEqual(len(iter), 1)
upper = {'cat': 'z', 'name': 'z'}
iter = list(table.range('by_compound', upper=upper))
self.assertEqual(len(iter), 7)
lower = {'cat': ' ', 'name': ' '}
iter = list(table.range('by_compound', lower=lower))
self.assertEqual(len(iter), 7)
lower = {'cat': 'B', 'name': ' '}
iter = list(table.range('by_compound', lower=lower))
self.assertEqual(len(iter), 4)
upper = {'cat': 'B', 'name': ''}
iter = list(table.range('by_compound', upper=upper))
self.assertEqual(len(iter), 3)
#!/usr/bin/env python
from pynndb import Database
#
print(">> Define some arbitrary data")
data = [
{'name': 'Gareth Bult', 'age': 21},
{'name': 'Squizzey', 'age': 3000},
{'name': 'Fred Bloggs', 'age': 45},
{'name': 'John Doe', 'age': 0},
{'name': 'John Smith', 'age': 40},
]
db = Database("databases/raw") # Open (/create) a database
table = db.table('people') # Open (/create) a table
print('>> Index table by name and age')
table.index('by_name', '{name}')
table.index('by_age', '{age:03}', duplicates=True)
print('>> Adding data')
for item in data:
table.append(item)
print("Count=", table.records)
print('>> Scanning table sequentially')
for record in table.find():
print('{name} is {age} years old'.format(**record))
print('>> Scanning tables in name order [string index]')
for record in table.find('by_name'):
print('{name} sorted alphabetically'.format(**record))
def modify(model, key, change):
doc = model.get(key.encode())
if not doc:
print('Unable to locate key "{}"'.format(key))
return
doc.modify(change)
doc.save()
if __name__ == '__main__':
database = Database('databases/people_database',
{'env': {
'map_size': 1024 * 1024 * 10
}})
user_model = UserModel(table=database.table('users'))
address_model = AddressModel(table=database.table('addresses'))
user_address = ManyToMany(database, user_model, address_model)
#
# Really basic interface using functions built-in to the BaseModel class.
#
commands = {
'lst': user_model.list,
'add': user_model.add,
'mod': modify_user,
'lst_address': address_model.list,
'add_address': address_model.add,
'mod_address': modify_address,
}
try:
commands[argv[1]](*argv[2:])
class App(Cmd):
limit = 10
_data = None
_base = None
_db = None
_default_prompt = colored('pynndb', 'cyan') + colored('>', 'blue') + ' '
def __init__(self):
super().__init__()
path = PosixPath('~/.pynndb').expanduser()
Path.mkdir(path, exist_ok=True)
if not path.is_dir():
self.pfeedback('error: unable to open configuration folder')
exit(1)
self._data = path / 'local_data'
self._base = path / 'registered'
self._line = path / '.readline_history'
Path.mkdir(self._data, exist_ok=True)
Path.mkdir(self._base, exist_ok=True)
if not self._data.is_dir() or not self._base.is_dir():
self.pfeedback('error: unable to open configuration folder')
exit(1)
self.settable.update(
{'limit': 'The maximum number of records to return'})
self.prompt = self._default_prompt
self.do_shell = None
self.do_edit = None
self.do_load = None
self.do_pyscript = None
self.do_py = None
def preloop(self):
print()
print(colored('PyNNDB Command Line Interface '.format(__version__),
'green'),
end='')
print(colored('v{}'.format(__version__), 'red'))
try:
readline.read_history_file(str(self._line))
except FileNotFoundError:
pass
def postloop(self):
readline.set_history_length(5000)
readline.write_history_file(str(self._line))
def ppfeedback(self, method, level, msg):
self.pfeedback(
colored(method, 'cyan') + ': ' + colored(level, 'yellow') + ': ' +
colored(msg, 'red'))
return False
parser = ArgumentParser()
parser.add_argument('database',
nargs=1,
help='path name of database to register')
parser.add_argument('alias',
nargs=1,
help='the local alias for the database')
@with_argparser(parser)
def do_register(self, opts):
"""Register a new database with this tool\n"""
database = opts.database[0]
alias = opts.alias[0]
path = Path(database).expanduser()
if not path.exists():
self.ppfeedback('register', 'error',
'failed to find path "{}"'.format(database))
return
try:
db = Database(str(path))
db.close()
except Exception as e:
print(e)
if Path(self._base / alias).exists():
self.ppfeedback('register', 'failed',
'the alias already exists "{}"'.format(alias))
return
Path(self._base / alias).symlink_to(str(path),
target_is_directory=True)
complete_register = Cmd.path_complete
parser = ArgumentParser()
parser.add_argument('database', nargs='?', help='name of database to use')
@with_argparser(parser)
def do_use(self, opts):
"""Select the database you want to work with\n"""
if self._db:
self._db.close()
self._db = None
self.prompt = self._default_prompt
if not opts.database:
return
database = opts.database
if not Path(self._base / database).exists():
return self.ppfeedback(
'use', 'error',
'database path not found "{}"'.format(database))
try:
path_name = str(Path(self._base / database))
self._db = Database(path_name)
self.prompt = colored(database, 'green') + colored('>',
'blue') + ' '
except Exception as e:
return self.ppfeedback(
'register', 'error',
'failed to open database "{}"'.format(database))
def complete_use(self, text, line, begidx, endidx):
return [f for f in listdir(str(self._base)) if f.startswith(text)]
parser = ArgumentParser()
parser.add_argument('table', nargs=1, help='the name of the table')
@with_argparser(parser)
def do_explain(self, opts):
"""Sample the fields and field types in use in this table\n"""
if not self._db:
return self.ppfeedback('explain', 'error', 'no database selected')
table_name = opts.table[0]
if table_name not in self._db.tables:
return self.ppfeedback(
'register', 'error',
'table does not exist "{}"'.format(table_name))
table = self._db.table(table_name)
keys = {}
samples = {}
for doc in table.find(limit=10):
for key in doc.keys():
if key == '_id':
continue
ktype = type(doc[key]).__name__
if ktype in ['str', 'int', 'bool', 'bytes', 'float']:
sample = doc.get(key)
if sample:
if ktype == 'bytes':
sample = sample.decode()
samples[key] = sample
else:
sample = str(doc.get(key))
if len(sample) > 60:
sample = sample[:60] + '...'
samples[key] = sample
if key not in keys:
keys[key] = [ktype]
else:
if ktype not in keys[key]:
keys[key].append(ktype)
dbpp = db_pretty_print()
[
dbpp.append({
'Field name': key,
'Field Types': keys[key],
'Sample': samples.get(key, '')
}) for key in keys
]
dbpp.reformat()
for line in dbpp:
print(line)
def complete_explain(self, text, line, begidx, endidx):
return [t for t in self._db.tables if t.startswith(text)]
parser = ArgumentParser()
parser.add_argument('table', nargs=1, help='the name of the table')
@with_argparser(parser)
def do_analyse(self, opts):
"""Analyse a table to see how record sizes are broken down\n"""
if not self._db:
return self.ppfeedback('explain', 'error', 'no database selected')
table_name = opts.table[0]
if table_name not in self._db.tables:
return self.ppfeedback(
'register', 'error',
'table does not exist "{}"'.format(table_name))
db = self._db.env.open_db(table_name.encode())
with self._db.env.begin() as txn:
with txn.cursor(db) as cursor:
count = 0
rtot = 0
rmax = 0
vals = []
fn = cursor.first
while fn():
rlen = len(cursor.value().decode())
rtot += rlen
vals.append(rlen)
if rlen > rmax:
rmax = rlen
count += 1
fn = cursor.next
MAX = 20
div = rmax / MAX
arr = [0 for i in range(MAX + 1)]
for item in vals:
idx = int(item / div)
arr[idx] += 1
test = []
n = div
for item in arr:
label = int(n)
if n > 1024:
label = str(int(n / 1024)) + 'K' if n > 1024 else str(label)
else:
label = str(label)
test.append((label, item))
n += div
graph = Pyasciigraph()
print()
for line in graph.graph('Breakdown of record size distribution', test):
print(line)
def complete_analyse(self, text, line, begidx, endidx):
return [t for t in self._db.tables if t.startswith(text)]
parser = ArgumentParser()
parser.add_argument('table',
nargs=1,
help='the table you want records from')
parser.add_argument(
'fields',
nargs='*',
help='the fields to display: field:format [field:format..]')
parser.add_argument('-b',
'--by',
type=str,
help='index to search and sort by')
parser.add_argument('-k',
'--key',
type=str,
help='key expression to search by')
parser.add_argument('-e',
'--expr',
type=str,
help='expression to filter by')
parser.add_argument('-l',
'--limit',
nargs=1,
help='limit output to (n) records')
parser.add_argument('-d',
'--dump',
action='store_true',
help='output in JSON format')
parser.add_argument('-c',
'--count',
action='store_true',
help='count the total number of results available')
parser.add_argument('--delete',
action='store_true',
help='delete all matching records')
parser.add_argument('--edit', type=str, help='edit the record')
@with_argparser(parser)
def do_find(self, opts):
"""Select records from a table
find --by=(index) --key=(key) table field:format [field:format..]
"""
if not self._db:
return self.ppfeedback('find', 'error', 'no database selected')
table_name = opts.table[0]
if table_name not in self._db.tables:
return self.ppfeedback(
'find', 'error',
'table does not exist "{}"'.format(table_name))
table = self._db.table(table_name)
if opts.by and opts.by not in table.indexes():
return self.ppfeedback('find', 'error',
'index does not exist "{}"'.format(opts.by))
limit = int(opts.limit[0]) if opts.limit else self.limit
args = []
count = 0
kwrgs = {'limit': limit}
action = table.find
if opts.by:
args.append(opts.by)
if opts.key and opts.by:
action = table.seek
args.append(loads(opts.key))
if opts.expr:
kwrgs['expression'] = eval(opts.expr)
def docval(doc, k):
if '.' not in k:
return doc.get(k, 'null')
parts = k.split('.')
while len(parts):
k = parts.pop(0)
doc = doc.get(k, {})
return doc
if opts.count:
maxrec = sum(1 for doc in action(*args))
query = action(*args, **kwrgs)
if opts.delete:
dbpp = db_pretty_print()
beg = datetime.now()
keys = []
for doc in query:
keys.append(doc['_id'])
with self._db.env.begin(write=True) as txn:
table.delete(keys, txn=txn)
end = datetime.now()
tspan = colored('{:0.4f}'.format((end - beg).total_seconds()),
'yellow')
limit = '' if len(keys) < self.limit else colored(
'(Limited view)', 'red')
persc = colored(
'{}/sec'.format(
int(1 / (end - beg).total_seconds() * len(keys))), 'cyan')
displayed = colored(
'Deleted {}'.format(colored(str(len(keys)), 'yellow')), 'red')
if opts.count:
displayed += colored(
' of {}'.format(colored(maxrec, 'yellow')), 'green')
displayed += colored(' records', 'green')
self.pfeedback(
colored(
'{} in {}s {} {}'.format(displayed, tspan, limit, persc),
'green'))
elif opts.edit:
dbpp = db_pretty_print()
beg = datetime.now()
keys = []
fn = eval(opts.edit)
for doc in query:
keys.append(doc['_id'])
fn(doc)
table.save(doc)
end = datetime.now()
tspan = colored('{:0.4f}'.format((end - beg).total_seconds()),
'yellow')
limit = '' if len(keys) < self.limit else colored(
'(Limited view)', 'red')
persc = colored(
'{}/sec'.format(
int(1 / (end - beg).total_seconds() * len(keys))), 'cyan')
displayed = colored(
'Edited {}'.format(colored(str(len(keys)), 'yellow')), 'red')
if opts.count:
displayed += colored(
' of {}'.format(colored(maxrec, 'yellow')), 'green')
displayed += colored(' records', 'green')
self.pfeedback(
colored(
'{} in {}s {} {}'.format(displayed, tspan, limit, persc),
'green'))
elif opts.dump:
for doc in query:
json = dumps(doc, sort_keys=True, indent=4)
print(
highlight(json, lexers.JsonLexer(),
formatters.TerminalFormatter()))
else:
dbpp = db_pretty_print()
beg = datetime.now()
[
dbpp.append({k: docval(doc, k)
for k in opts.fields}) for doc in query
]
end = datetime.now()
dbpp.reformat()
for line in dbpp:
print(line)
tspan = colored('{:0.4f}'.format((end - beg).total_seconds()),
'yellow')
limit = '' if dbpp.len < self.limit else colored(
'(Limited view)', 'red')
persc = colored(
'{}/sec'.format(int(1 / (end - beg).total_seconds() *
dbpp.len)), 'cyan')
displayed = colored(
'Displayed {}'.format(colored(str(dbpp.len), 'yellow')),
'green')
if opts.count:
displayed += colored(
' of {}'.format(colored(maxrec, 'yellow')), 'green')
displayed += colored(' records', 'green')
self.pfeedback(
colored(
'{} in {}s {} {}'.format(displayed, tspan, limit, persc),
'green'))
def complete_find(self, text, line, begidx, endidx):
words = line.split(' ')
if len(words) > 2:
table_name = words[1]
if table_name in self._db.tables:
table = self._db.table(table_name)
doc = table.first()
fields = [f for f in doc.keys()]
return [f for f in fields if f.startswith(text)]
return [t for t in self._db.tables if t.startswith(text)]
parser = ArgumentParser()
parser.add_argument('table',
nargs=1,
help='the table you want records from')
parser.add_argument('field',
nargs=1,
help='the name of the field you are interested in')
parser.add_argument('-b',
'--by',
type=str,
help='index to search and sort by')
@with_argparser(parser)
def do_unique(self, opts):
"""Display a list of unique values for a chosen field
unique find --by=(index) table field
"""
if not self._db:
return self.ppfeedback('unique', 'error', 'no database selected')
table_name = opts.table[0]
if table_name not in self._db.tables:
return self.ppfeedback(
'unique', 'error',
'table does not exist "{}"'.format(table_name))
table = self._db.table(table_name)
if opts.by and opts.by not in table.indexes():
return self.ppfeedback('unique', 'error',
'index does not exist "{}"'.format(opts.by))
else:
index = table.index(opts.by)
field_name = opts.field[0]
dbpp = db_pretty_print()
counter = 0
with table.begin() as txn:
if index:
cursor = index.cursor(txn)
action = cursor.first
while action():
dbpp.append({
'id': str(counter),
field_name: cursor.key().decode(),
'count': str(cursor.count())
})
action = cursor.next_nodup
counter += 1
dbpp.reformat()
for line in dbpp:
print(line)
def complete_unique(self, text, line, begidx, endidx):
return [t for t in self._db.tables if t.startswith(text)]
def show_databases(self):
"""Show available databases"""
M = 1024 * 1024
dbpp = db_pretty_print()
for database in Path(self._base).iterdir():
mdb = database / 'data.mdb'
stat = mdb.stat()
mapped = stat.st_size
divisor = 1024
units = 'K'
if mapped > 1024 * 1024 * 1024:
divisor = 1024 * 1024 * 1024
units = 'G'
elif mapped > 1024 * 1024:
divisor = 1024 * 1024
units = 'M'
dbpp.append({
'Database name':
database.parts[-1],
'Mapped':
int(stat.st_size / divisor),
'Used':
int(stat.st_blocks * 512 / divisor),
'Util (%)':
int((stat.st_blocks * 512 * 100) / stat.st_size),
'Units':
units
})
dbpp.reformat()
for line in dbpp:
print(line)
def show_tables(self, *args):
"""Display a list of tables available within this database\n"""
dbpp = db_pretty_print()
for name in self._db.tables:
table = self._db.table(name)
db = self._db.env.open_db(name.encode())
with self._db.env.begin() as txn:
stat = txn.stat(db)
l = int(stat['leaf_pages'])
dbpp.append({
'Table name':
name,
'# Recs':
stat['entries'],
'Depth':
stat['depth'],
'Oflow%':
int(int(stat['overflow_pages']) * 100 / (l if l else 1)),
'Index names':
', '.join(table.indexes())
})
dbpp.reformat()
for line in dbpp:
print(line)
def show_indexes(self, table_name):
"""Display a list of indexes for the specified table\n"""
table = self._db.table(table_name)
dbpp = db_pretty_print()
with table.begin() as txn:
for index in table.indexes(txn=txn):
key = '_{}_{}'.format(table_name, index)
doc = loads(
txn.get(key.encode(), db=self._db._meta._db).decode())
dbpp.append({
'Table name':
table_name if table_name else 'None',
'Index name':
index if index else 'None',
'Entries':
table.index(index).count(),
'Key':
doc.get('func') if doc.get('func') else 'None',
'Dups':
'True' if doc['conf'].get('dupsort') else 'False',
'Create':
'True' if doc['conf'].get('create') else 'False'
})
dbpp.reformat()
for line in dbpp:
print(line)
parser = ArgumentParser()
parser.add_argument('option',
choices=['settings', 'databases', 'tables', 'indexes'],
help='what it is we want to show')
parser.add_argument('table', nargs='?', help='')
@with_argparser(parser)
def do_show(self, opts):
"""Show various settings"""
if opts.option == 'databases':
return self.show_databases()
if opts.option == 'settings':
return super().do_show('')
if not self._db:
return self.ppfeedback('unique', 'error', 'no database selected')
if opts.option == 'tables':
return self.show_tables()
if opts.option == 'indexes':
table_name = opts.table
if table_name not in self._db.tables:
return self.ppfeedback(
'register', 'error',
'table does not exist "{}"'.format(table_name))
return self.show_indexes(table_name)
def complete_show(self, text, line, begidx, endidx):
words = line.split(' ')
if len(words) < 3:
return [
i for i in ['settings', 'databases', 'indexes', 'tables']
if i.startswith(text)
]
if words[1] == 'indexes':
return [t for t in self._db.tables if t.startswith(text)]
class AddressModel(Table):
_calculated = {}
_display = [{
'name': 'line1',
'width': 30
}, {
'name': 'postcode',
'width': 15
}]
db = Database('databases/contacts_db', size=size_mb(10))
tbl_address_book = AddressBookModel(table=db.table('address_book'))
tbl_business = BusinessModel(table=db.table('business'))
tbl_person = PersonModel(table=db.table('person'))
tbl_address = AddressModel(table=db.table('address'))
address_book_business = ManyToMany(db, tbl_address_book, tbl_business)
address_book_person = ManyToMany(db, tbl_address_book, tbl_person)
business_person = ManyToMany(db, tbl_business, tbl_person)
business_address = ManyToMany(db, tbl_business, tbl_address)
person_address = ManyToMany(db, tbl_person, tbl_address)
print("Tables: ", db.tables)
doc = tbl_address_book.add({'name': 'Personal'})
doc.business.append({
'name': 'My Business Name'
'hour': int(rnd * 24)
}
txn.append(tbl, record)
finish = time()
print(" - {:5}:{:5} - Append Speed/sec = {:.0f}".format(start, count, count / (finish - begin)))
print('** SINGLE Threaded benchmark **')
print('** Probably better throughput with multiple processes')
print('')
call(['rm', '-rf', 'databases/perfDB'])
print("* No Indecies")
db = Database('databases/perfDB', conf={'writemap': False})
table = db.table('sessions')
chunk(db,table, 0, 5000)
chunk(db,table, 5000, 5000)
chunk(db,table, 10000, 5000)
db.close()
call(['rm', '-rf', 'databases/perfDB'])
print("* Indexed by sid, day, hour")
db = Database('databases/perfDB', conf={'writemap': False})
# db = Database('databases/perfDB')
table = db.table('sessions')
table.index('by_sid', '{sid}')
table.index('by_day', '{day}')
table.index('by_hour', '{hour}')
chunk(db,table, 0, 5000)
chunk(db,table, 5000, 5000)
def test_02_create_table(self):
db = Database(self._db_name, size=size_gb(0.1))
table = db.table(self._tb_name)
self.assertTrue(isinstance(table, Table))
def test_26_drop_reuse(self):
db = Database(self._db_name)
self.generate_data1(db, self._tb_name)
table = db.table(self._tb_name)
d = db.drop(self._tb_name)
self.generate_data1(db, self._tb_name)
def test_28_range(self):
db = Database(self._db_name)
table = db.table(self._tb_name)
data = [
{
'code': 'F',
'name': 'Tom'
},
{
'code': 'E',
'name': 'Dick'
},
{
'code': 'E',
'name': 'Dick1'
},
{
'code': 'D',
'name': 'Harry'
},
{
'code': 'C',
'name': 'Fred'
},
{
'code': 'B',
'name': 'John'
},
{
'code': 'B',
'name': 'John1'
},
{
'code': 'A',
'name': 'Sam'
},
]
for row in data:
table.append(row)
table.index('by_code', '{code}', duplicates=True)
res = list(table.find('by_code'))
self.assertEqual(res[0]['code'], 'A')
self.assertEqual(res[-1]['code'], 'F')
res = list(table.find())
lower = res[0]['_id']
upper = res[-1]['_id']
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural[0]['code'], 'F')
self.assertEqual(natural[-1]['code'], 'A')
res = list(table.find())
lower = res[0]['_id']
upper = res[-2]['_id']
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural[0]['code'], 'F')
self.assertEqual(natural[-1]['code'], 'B')
res = list(table.find())
lower = res[0]['_id']
upper = res[-1]['_id']
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural[0]['code'], 'F')
self.assertEqual(natural[-1]['code'], 'A')
res = list(table.find())
lower = None
upper = res[-1]['_id']
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural[0]['code'], 'F')
self.assertEqual(natural[-1]['code'], 'A')
res = list(table.find())
lower = res[0]['_id']
upper = None
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural[0]['code'], 'F')
self.assertEqual(natural[-1]['code'], 'A')
res = list(table.find())
lower = None
upper = None
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural[0]['code'], 'F')
self.assertEqual(natural[-1]['code'], 'A')
lower = res[0]['_id']
upper = res[0]['_id']
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural[0]['code'], 'F')
lower = res[-1]['_id']
upper = res[-1]['_id']
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural[0]['code'], 'A')
lower = res[0]['_id']
upper = res[0]['_id']
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural[0], res[0])
#
lower = res[0]['_id']
upper = res[1]['_id']
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural, res[0:2])
#
lower = res[0]['_id']
upper = res[2]['_id']
natural = list(table.range(None, {'_id': lower}, {'_id': upper}))
self.assertEqual(natural, res[0:3])
table.index('by_code', '{code}')
res = list(table.range('by_code', {'code': '0'}, {'code': 'Z'}))
self.assertEqual(res[0]['code'], 'A')
self.assertEqual(res[-1]['code'], 'F')
res = list(table.range('by_code', {'code': 'B'}, {'code': 'E'}))
self.assertEqual(res[0]['code'], 'B')
self.assertEqual(res[-1]['code'], 'E')
res = list(table.range('by_code', {'code': 'B'}, {'code': 'E'}))
self.assertEqual(res[0]['code'], 'B')
self.assertEqual(res[-1]['code'], 'E')
#
res = list(table.range('by_code', {'code': 'A'}, {'code': 'F'}))
self.assertEqual(res[0]['code'], 'A')
self.assertEqual(res[-1]['code'], 'F')
res = list(table.range('by_code', None, None))
self.assertEqual(res[0]['code'], 'A')
self.assertEqual(res[-1]['code'], 'F')