summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorGravatar Étienne Deparis 2020-01-28 13:10:33 +0100
committerGravatar Étienne Deparis 2020-01-28 13:10:33 +0100
commit89d66a33876b895eb203a1854285cfcfa6396450 (patch)
tree452b7a012918333900db5ebd14fa2573a7921fd2
parentf803bb521f51d64cd358975a252814f1e64ff95e (diff)
downloadeshelf-89d66a33876b895eb203a1854285cfcfa6396450.tar.gz
eshelf-89d66a33876b895eb203a1854285cfcfa6396450.tar.xz
eshelf-89d66a33876b895eb203a1854285cfcfa6396450.zip
Abstract information gathered from the database
-rw-r--r--eshelf/cli.py33
-rw-r--r--eshelf/reader_orm.py92
2 files changed, 89 insertions, 36 deletions
diff --git a/eshelf/cli.py b/eshelf/cli.py
index db6863f..9815c99 100644
--- a/eshelf/cli.py
+++ b/eshelf/cli.py
@@ -83,11 +83,12 @@ class EShelfCli:
def cmd_collections(self, opts=[]):
finaldata = []
- for data in self.reader_db.list_collections():
- if data['f_readonly'] != 0:
- continue
+ for data in self.reader_db.list_shelves():
finaldata.append(
- '[{0}] {1}'.format(data['f_id_shelf'], data['f_name'])
+ '[{id}] {name}'.format(
+ id=data['id'],
+ name=data['name']
+ )
)
print_listing('Collections:', finaldata)
@@ -102,27 +103,27 @@ class EShelfCli:
shelf_infos = self.reader_db.get_shelf(opts[0])
if shelf_infos is None:
return
- shelf_name = shelf_infos['f_name']
- shelf_id = shelf_infos['f_id_shelf']
+ shelf_name = shelf_infos['name']
+ shelf_id = shelf_infos['id']
finaldata = []
remote_library_path = os.path.join(
self.remote_physical_path,
self.config['remote_library_path']
)
for book_data in self.reader_db.list_shelf_items(shelf_id):
- if not book_data['f_internal_uri'].startswith(remote_library_path):
+ if not book_data['internal_uri'].startswith(remote_library_path):
# Maybe a system file? Or a default one?
continue
mounted_file_path = re.sub(
self.remote_physical_path,
self.reader_mount_point,
- book_data['f_internal_uri']
+ book_data['internal_uri']
)
relative_file_path = re.sub(
remote_library_path, '',
- book_data['f_internal_uri']
+ book_data['internal_uri']
).lstrip('/')
lfile = os.path.join(self.library_path, relative_file_path)
@@ -138,8 +139,14 @@ class EShelfCli:
local_file = ' ({0})'.format(relative_file_path)
if os.path.isfile(mounted_file_path):
- finaldata.append('[{0}] {1}{2}'.format(
- book_data['f_id_item'], book_data['f_title'], local_file))
+ finaldata.append(
+ '[{id}] {title} — {author}{other}'.format(
+ id=book_data['id'],
+ title=book_data['title'],
+ author=book_data['author'],
+ other=local_file
+ )
+ )
if shelf_name is not None:
title = 'Books in {0}:'.format(shelf_name)
@@ -164,7 +171,7 @@ class EShelfCli:
book_infos = self.reader_db.get_book(book_id_or_title)
if book_infos is None:
self.bye(1)
- return shelf_infos['f_id_shelf'], book_infos['f_id_item']
+ return shelf_infos['id'], book_infos['id']
def cmd_add(self, opts=[]):
if len(opts) < 2:
@@ -193,7 +200,7 @@ class EShelfCli:
book_infos = self.reader_db.get_book(opts[0])
if book_infos is None:
self.bye(1)
- self.reader_db.update_book_title(book_infos['f_id_item'], opts[1])
+ self.reader_db.update_book_title(book_infos['id'], opts[1])
print('Title successfully updated to {0}.'.format(opts[1]))
def cmd_device(self, opts=[]):
diff --git a/eshelf/reader_orm.py b/eshelf/reader_orm.py
index b9afd4f..3e971bc 100644
--- a/eshelf/reader_orm.py
+++ b/eshelf/reader_orm.py
@@ -28,29 +28,59 @@ class ReaderOrm:
b_uuid = dict(self.get_cursor().execute(b_uuid_req).fetchone())
return b_uuid['f_db_uuid']
+ def format_shelf_infos(self, row):
+ data = dict(row)
+ return {
+ 'id': data['f_id_shelf'],
+ 'name': data['f_name']
+ }
+
def get_shelf(self, id_or_name):
+ base_query = '''
+ select f_id_shelf, f_name
+ from t_shelf
+ where f_readonly = 0
+ and {field} = ?
+ '''
if str(id_or_name).isdecimal():
- shelf_infos_req = 'select f_id_shelf, f_name ' \
- 'from t_shelf where f_id_shelf = ?'
+ shelf_infos_req = base_query.format(field='f_id_shelf')
else:
- shelf_infos_req = 'select f_id_shelf, f_name ' \
- 'from t_shelf where f_name = ? collate nocase'
+ shelf_infos_req = base_query.format(field='f_name')
+ shelf_infos_req += ' collate nocase'
cur = self.get_cursor()
shelf_infos = cur.execute(
shelf_infos_req, (id_or_name,)
).fetchone()
if shelf_infos is None:
- print('No such shelf {0}'.format(id_or_name))
+ print('No such shelf {0} or readonly'.format(id_or_name))
return None
- return dict(shelf_infos)
+ return self.format_shelf_infos(shelf_infos)
+
+ def format_book_infos(self, row):
+ data = dict(row)
+ return {
+ 'id': data['f_id_item'],
+ 'title': data['f_title'],
+ 'author': data['f_name'],
+ 'internal_uri': data['f_internal_uri']
+ }
def get_book(self, id_or_title):
+ # f_item_filetype == 1 is for folders
+ # f_item_filetype == 2 is for books
+ # f_item_filetype == 3 is for pictures
+ base_req = '''
+ select i.f_id_item, i.f_title, i.f_internal_uri, a.f_name
+ from t_item i
+ left join t_author a on a.f_id_author = i.f_author_id
+ where i.f_item_filetype = 2
+ and {field} = ?
+ '''
if str(id_or_title).isdecimal():
- book_infos_req = 'select f_id_item, f_title ' \
- 'from t_item where f_id_item = ?'
+ book_infos_req = base_req.format(field='i.f_id_item')
else:
- book_infos_req = 'select f_id_item, f_title ' \
- 'from t_item where f_title = ? collate nocase'
+ book_infos_req = base_req.format(field='i.f_title')
+ book_infos_req += ' collate nocase'
cur = self.get_cursor()
book_infos = cur.execute(
book_infos_req, (id_or_title,)
@@ -58,25 +88,41 @@ class ReaderOrm:
if book_infos is None:
print('No such book {0}'.format(id_or_title))
return None
- return dict(book_infos)
+ return self.format_book_infos(book_infos)
def list_shelf_items(self, shelf_id=None):
cur = self.get_cursor()
if shelf_id is None:
- query = 'select f_id_item, f_title, f_internal_uri ' \
- 'from t_item order by f_title'
- return [dict(row) for row in cur.execute(query)]
- query = 'select b.f_id_item, b.f_title, b.f_internal_uri ' \
- 'from t_item b, t_shelf_link tb where ' \
- 'tb.f_item_id = b.f_id_item and tb.f_shelf_id = ? ' \
- 'order by b.f_title'
- return [dict(row) for row in cur.execute(query, (shelf_id,))]
+ base_req = '''
+ select i.f_id_item, i.f_title, i.f_internal_uri, a.f_name
+ from t_item i
+ left join t_author a on a.f_id_author = i.f_author_id
+ where i.f_item_filetype = 2
+ order by f_title
+ '''
+ return [self.format_book_infos(row)
+ for row in cur.execute(base_req)]
+ base_req = '''
+ select i.f_id_item, i.f_title, a.f_name
+ from t_item i
+ inner join t_shelf_link s on s.f_item_id = i.f_id_item
+ left join t_author a on a.f_id_author = i.f_author_id
+ where i.f_item_filetype = 2
+ and s.f_shelf_id = ?
+ order by f_title
+ '''
+ return [self.format_book_infos(row)
+ for row in cur.execute(base_req, (shelf_id,))]
- def list_collections(self):
+ def list_shelves(self):
cur = self.get_cursor()
- query = 'select f_id_shelf, f_name, f_readonly ' \
- 'from t_shelf order by f_name'
- return [dict(row) for row in cur.execute(query)]
+ query = '''
+ select f_id_shelf, f_name
+ from t_shelf
+ where f_readonly = 0
+ order by f_name
+ '''
+ return [self.format_shelf_infos(row) for row in cur.execute(query)]
def add_shelf_link(self, book_id, shelf_id):
b_uuid = self.reader_db.get_b_uuid()