def create_tables(): schema = """ """ db.execute_query(schema)
def create_tables(): schema = """ CREATE TABLE sm_bookmarks ( bookmark_id SERIAL PRIMARY KEY, url TEXT NOT NULL, title TEXT NOT NULL, owner INTEGER, timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX url on sm_bookmarks(url); CREATE INDEX owner on sm_bookmarks(owner); CREATE TABLE sm_projects ( project_id SERIAL PRIMARY KEY, project_name TEXT NOT NULL, project_owner INTEGER ); CREATE INDEX project_name on sm_projects(project_name); CREATE INDEX project_owner on sm_projects(project_owner); CREATE TABLE sm_project_bookmarks ( project_id INTEGER NOT NULL, bookmark_id INTEGER NOT NULL, PRIMARY KEY (project_id, bookmark_id) );""" idB.execute_query(schema)
def create_tables(): schema = """ CREATE TABLE ticks ( task_id SERIAL PRIMARY KEY, task_content TEXT NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, priority INTEGER DEFAULT 0, completed timestamp DEFAULT NULL ); CREATE TABLE ticks_projects ( project_id SERIAL PRIMARY KEY, project_name TEXT NOT NULL ); CREATE INDEX ticks_project_name on ticks_projects(project_name); CREATE TABLE project_ticks ( project_id INTEGER NOT NULL, task_id INTEGER NOT NULL, PRIMARY KEY (project_id, task_id) );""" db.execute_query(schema)
def move_bookmark(bookmark_id, project_id): query = """DELETE FROM sm_project_bookmarks WHERE bookmark_id=%s;""" idB.execute_query(query, (bookmark_id,)) if project_id != 0: query = """INSERT INTO sm_project_bookmarks VALUES(%s, %s);""" idB.execute_query(query, (project_id, bookmark_id))
def delete_tick(task_id): query = """ DELETE FROM ticks WHERE task_id=%(tid)s; DELETE FROM project_ticks WHERE task_id=%(tid)s; """ db.execute_query(query, {'tid': task_id}) return task_id
def demote_tick(task_id): query = """ UPDATE ticks SET priority = priority - 1 WHERE task_id=%s; """ db.execute_query(query, (task_id,)) return task_id
def save_bookmark(url, title, project=None): query = """INSERT INTO sm_bookmarks (url, title, owner) VALUES (%s, %s, %s) RETURNING bookmark_id;""" bookmark_id = idB.execute_query(query, (url, title, 1)) if project: query = """INSERT INTO sm_project_bookmarks VALUES(%s,%s) RETURNING project_id;""" idB.execute_query(query, (project, bookmark_id)) return bookmark_id
def toggle_complete_tick(task_id): query = """ UPDATE ticks SET completed = CASE WHEN completed IS NULL THEN CURRENT_TIMESTAMP ELSE NULL END WHERE task_id=%s; """ db.execute_query(query, (task_id,)) return task_id
def get_project_bookmarks(project): query = """SELECT url, title, b.bookmark_id FROM sm_bookmarks b JOIN sm_project_bookmarks pb ON pb.bookmark_id=b.bookmark_id WHERE pb.project_id=%s ORDER BY timestamp DESC;""" results = idB.execute_query(query, (project,)) query = """SELECT project_name FROM sm_projects WHERE project_id=%s;""" project_name = idB.execute_query(query, (project,)) return results, project_name
def save_tick(content, project_id=None, task_id=None): if task_id: # update existing task query = """UPDATE ticks SET task_content=%s WHERE task_id=%s""" db.execute_query(query, (content,task_id)) else: query = """INSERT INTO ticks (task_content) VALUES (%s) RETURNING task_id;""" task_id = db.execute_query(query, (content,)) if project_id: query = """INSERT INTO ticks_projects VALUES(%s,%s) RETURNING project_id;""" db.execute_query(query, (project, task_id)) return task_id
def search_for_bookmarks(terms): query = """SELECT url, title, b.bookmark_id, project_name, p.project_id FROM sm_bookmarks b LEFT JOIN sm_project_bookmarks pb ON pb.bookmark_id=b.bookmark_id LEFT JOIN sm_projects p ON p.project_id=pb.project_id WHERE to_tsvector('english', title) @@ plainto_tsquery('english', %s) ORDER BY timestamp DESC;""" bookmarks = idB.execute_query(query, (terms,)) return bookmarks
def get_projects(): query = """SELECT p.project_id, project_name, (SELECT count(bookmark_id) FROM sm_project_bookmarks b WHERE b.project_id=p.project_id) as bookmark_count FROM sm_projects p ORDER BY project_name;""" results = idB.execute_query(query) return results
def get_incomplete_ticks(): query = """ SELECT task_id, task_content, completed FROM ticks WHERE completed IS NULL OR completed > %s ORDER BY priority DESC, created;""" eight_hours_ago = datetime.datetime.now() - datetime.timedelta(hours=8) tasks = db.execute_query(query, (eight_hours_ago,)) return tasks
def get_recent_bookmarks(page=0): BOOKMARKS_PER_PAGE = 25 offset = BOOKMARKS_PER_PAGE * page bookmark_count = idB.execute_query("""SELECT count(bookmark_id) FROM sm_bookmarks;""") more_bookmarks=False if bookmark_count > (offset+BOOKMARKS_PER_PAGE): more_bookmarks = True query = """SELECT url, title, b.bookmark_id, project_name, p.project_id FROM sm_bookmarks b LEFT JOIN sm_project_bookmarks pb ON pb.bookmark_id=b.bookmark_id LEFT JOIN sm_projects p ON p.project_id=pb.project_id ORDER BY timestamp DESC LIMIT %s OFFSET %s;""" results = idB.execute_query(query, (BOOKMARKS_PER_PAGE, offset)) return results, more_bookmarks
def tables_created(): query = """ SELECT relname FROM pg_class WHERE relname = 'ticks' OR relname = 'ticks_projects' OR relname = 'projects_ticks'; """ result = db.execute_query(query) if result: return True return False
def tables_created(): query = """ SELECT relname FROM pg_class WHERE relname = 'sm_bookmarks' OR relname = 'sm_projects' OR relname = 'sm_project_bookmarks'; """ result = idB.execute_query(query) if result: return True return False
def delete_bookmark(bookmark_id): query = """DELETE FROM sm_bookmarks WHERE bookmark_id=%s;""" idB.execute_query(query, (bookmark_id,)) query = """DELETE FROM sm_project_bookmarks WHERE bookmark_id=%s;""" idB.execute_query(query, (bookmark_id,))
def get_project_id(project_name): query = """SELECT project_id FROM sm_projects WHERE project_name=%s;""" project_id = idB.execute_query(query, (project_name,)) return project_id
def create_project(project): query = """INSERT INTO sm_projects (project_name) VALUES (%s) RETURNING project_id;""" project_id = idB.execute_query(query, (project,)) return project_id
def get_tick(task_id): query = """SELECT task_id, task_content FROM ticks WHERE task_id=%s;""" return db.execute_query(query, (task_id,))
def update_bookmark(bookmark_id, title, url): query = """UPDATE sm_bookmarks SET url=%s, title=%s WHERE bookmark_id=%s;""" idB.execute_query(query, (url, title, bookmark_id)) return bookmark_id