def simple(facts, start_date, end_date, format, path = None): facts = copy.deepcopy(facts) # dont want to do anything bad to the input report_path = stuff.locale_from_utf8(path) if format == "tsv": writer = TSVWriter(report_path) elif format == "xml": writer = XMLWriter(report_path) elif format == "ical": writer = ICalWriter(report_path) else: #default to HTML writer = HTMLWriter(report_path, start_date, end_date) writer.write_report(facts) # some assembly required - hidden - saved a report for single day if start_date == end_date: trophies.unlock("some_assembly_required") # I want this on my desk - generated over 10 different reports if trophies.check("on_my_desk") == False: current = trophies.increment("reports_generated") if current == 10: trophies.unlock("on_my_desk") return writer
def activity_name_edited_cb(self, cell, path, new_text, model): id = model[path][0] category_id = model[path][2] activities = runtime.storage.get_category_activities(category_id) prev = None for activity in activities: if id == activity['id']: prev = activity['name'] else: # avoid two activities in same category with same name if activity['name'].lower() == new_text.lower(): if id == -1: # that was a new activity self.activity_store.remove(model.get_iter(path)) self.select_activity(activity['id']) return False if id == -1: #new activity -> add model[path][0] = runtime.storage.add_activity(new_text.decode("utf-8"), category_id) else: #existing activity -> update new = new_text.decode("utf-8") runtime.storage.update_activity(id, new, category_id) # size matters - when editing activity name just changed the case (bar -> Bar) if prev != new and prev.lower() == new.lower(): trophies.unlock("size_matters") model[path][1] = new_text return True
def simple(facts, start_date, end_date, format, path=None): facts = copy.deepcopy(facts) # dont want to do anything bad to the input report_path = stuff.locale_from_utf8(path) if format == "tsv": writer = TSVWriter(report_path) elif format == "xml": writer = XMLWriter(report_path) elif format == "ical": writer = ICalWriter(report_path) else: #default to HTML writer = HTMLWriter(report_path, start_date, end_date) writer.write_report(facts) # some assembly required - hidden - saved a report for single day if start_date == end_date: trophies.unlock("some_assembly_required") # I want this on my desk - generated over 10 different reports if trophies.check("on_my_desk") == False: current = trophies.increment("reports_generated") if current == 10: trophies.unlock("on_my_desk") return writer
def activity_name_edited_cb(self, cell, path, new_text, model): new_text = new_text.decode("utf-8") id = model[path][0] category_id = model[path][2] activities = runtime.storage.get_category_activities(category_id) prev = None for activity in activities: if id == activity['id']: prev = activity['name'] else: # avoid two activities in same category with same name if activity['name'].lower() == new_text.lower(): if id == -1: # that was a new activity self.activity_store.remove(model.get_iter(path)) self.select_activity(activity['id']) return False if id == -1: #new activity -> add model[path][0] = runtime.storage.add_activity( new_text, category_id) else: #existing activity -> update new = new_text runtime.storage.update_activity(id, new, category_id) # size matters - when editing activity name just changed the case (bar -> Bar) if prev != new and None != prev and prev.lower() == new.lower(): trophies.unlock("size_matters") model[path][1] = new_text return True
def on_clipboard_text(self, clipboard, text, data): # first check that we have a date selected fact = self.fact_tree.get_selected_fact() if not fact: return if isinstance(fact, dt.date): selected_date = fact else: selected_date = fact.date fact = Fact(text.decode("utf-8")) if not all((fact.activity, fact.start_time, fact.end_time)): return fact.start_time = fact.start_time.replace(year = selected_date.year, month = selected_date.month, day = selected_date.day) fact.end_time = fact.end_time.replace(year = selected_date.year, month = selected_date.month, day = selected_date.day) new_id = runtime.storage.add_fact(fact) # You can do that?! - copy/pasted an activity trophies.unlock("can_do_that") if new_id: self.fact_tree.select_fact(new_id)
def on_clipboard_text(self, clipboard, text, data): # first check that we have a date selected fact = self.fact_tree.get_selected_fact() if not fact: return if isinstance(fact, dt.date): selected_date = fact else: selected_date = fact.date fact = stuff.Fact(text.decode("utf-8")) if not all((fact.activity, fact.start_time, fact.end_time)): return fact.start_time = fact.start_time.replace(year=selected_date.year, month=selected_date.month, day=selected_date.day) fact.end_time = fact.end_time.replace(year=selected_date.year, month=selected_date.month, day=selected_date.day) new_id = runtime.storage.add_fact(fact) # You can do that?! - copy/pasted an activity trophies.unlock("can_do_that") if new_id: self.fact_tree.select_fact(new_id)
def run_fixtures(self): self.start_transaction() """upgrade DB to hamster version""" version = self.fetchone("SELECT version FROM version")["version"] current_version = 11 if version < 8: # working around sqlite's utf-f case sensitivity (bug 624438) # more info: http://www.gsak.net/help/hs23820.htm self.execute( "ALTER TABLE activities ADD COLUMN search_name varchar2") activities = self.fetchall("select * from activities") statement = "update activities set search_name = ? where id = ?" for activity in activities: self.execute(statement, (activity['name'].lower(), activity['id'])) # same for categories self.execute( "ALTER TABLE categories ADD COLUMN search_name varchar2") categories = self.fetchall("select * from categories") statement = "update categories set search_name = ? where id = ?" for category in categories: self.execute(statement, (category['name'].lower(), category['id'])) if version < 9: # adding full text search self.execute("""CREATE VIRTUAL TABLE fact_index USING fts3(id, name, category, description, tag)""" ) if version < 10: # adding exported self.execute( """ALTER TABLE facts ADD COLUMN exported bool default false""") self.execute("""UPDATE facts set exported=1""") if version < 11: #adding redmine_issues table self.execute( """CREATE TABLE redmine_issues (id int PRIMARY KEY, name varchar2(500), mine boolean, search_name varchar2(500), deleted integer)""" ) # at the happy end, update version number if version < current_version: #lock down current version self.execute("UPDATE version SET version = %d" % current_version) print "updated database from version %d to %d" % (version, current_version) # oldtimer – database version structure had been performed on startup (thus we know that user has been on at least 2 versions) if trophies: trophies.unlock("oldtimer") self.end_transaction()
def _finish(self, facts): # group by date by_date = [] for date, date_facts in itertools.groupby(facts, lambda fact: fact.date): by_date.append((date, [dict(fact) for fact in date_facts])) by_date = dict(by_date) date_facts = [] date = self.start_date while date <= self.end_date: str_date = date.strftime( # date column format for each row in HTML report # Using python datetime formatting syntax. See: # http://docs.python.org/library/time.html#time.strftime C_("html report", "%b %d, %Y") ) date_facts.append([str_date, by_date.get(date, [])]) date += dt.timedelta(days=1) data = dict( title=self.title, # grand_total = _("%s hours") % ("%.1f" % (total_duration.seconds / 60.0 / 60 + total_duration.days * 24)), totals_by_day_title=_("Totals by Day"), activity_log_title=_("Activity Log"), totals_title=_("Totals"), days_totals_heading=_("days"), activity_totals_heading=_("activities"), category_totals_heading=_("categories"), tag_totals_heading=_("tags"), show_prompt=_("Distinguish:"), header_date=_("Date"), header_activity=_("Activity"), header_category=_("Category"), header_tags=_("Tags"), header_start=_("Start"), header_end=_("End"), header_duration=_("Duration"), header_description=_("Description"), data_dir=runtime.data_dir, show_template=_("Show template"), template_instructions=_("You can override it by storing your version in %(home_folder)s") % {"home_folder": runtime.home_data_dir}, start_date=timegm(self.start_date.timetuple()), end_date=timegm(self.end_date.timetuple()), facts=json_dumps([dict(fact) for fact in facts]), date_facts=json_dumps(date_facts), all_activities_rows="\n".join(self.fact_rows), ) self.file.write(Template(self.main_template).safe_substitute(data)) if self.override: # my report is better than your report - overrode and ran the default report trophies.unlock("my_report") return
def _finish(self, facts): # group by date by_date = [] for date, date_facts in itertools.groupby(facts, lambda fact: fact.date): by_date.append((date, [dict(fact) for fact in date_facts])) by_date = dict(by_date) date_facts = [] date = self.start_date while date <= self.end_date: str_date = date.strftime( # date column format for each row in HTML report # Using python datetime formatting syntax. See: # http://docs.python.org/library/time.html#time.strftime C_("html report", "%b %d, %Y")) date_facts.append([str_date, by_date.get(date, [])]) date += dt.timedelta(days=1) data = dict( title=self.title, #grand_total = _("%s hours") % ("%.1f" % (total_duration.seconds / 60.0 / 60 + total_duration.days * 24)), totals_by_day_title=_("Totals by Day"), activity_log_title=_("Activity Log"), totals_title=_("Totals"), days_totals_heading=_("days"), activity_totals_heading=_("activities"), category_totals_heading=_("categories"), tag_totals_heading=_("tags"), show_prompt=_("Distinguish:"), header_date=_("Date"), header_activity=_("Activity"), header_category=_("Category"), header_tags=_("Tags"), header_start=_("Start"), header_end=_("End"), header_duration=_("Duration"), header_description=_("Description"), data_dir=runtime.data_dir, show_template=_("Show template"), template_instructions= _("You can override it by storing your version in %(home_folder)s") % {'home_folder': runtime.home_data_dir}, start_date=timegm(self.start_date.timetuple()), end_date=timegm(self.end_date.timetuple()), facts=json_dumps([dict(fact) for fact in facts]), date_facts=json_dumps(date_facts), all_activities_rows="\n".join(self.fact_rows)) self.file.write(Template(self.main_template).safe_substitute(data)) if self.override: # my report is better than your report - overrode and ran the default report trophies.unlock("my_report") return
def run_fixtures(self): self.start_transaction() """upgrade DB to hamster version""" version = self.fetchone("SELECT version FROM version")["version"] current_version = 10 if version < 8: # working around sqlite's utf-f case sensitivity (bug 624438) # more info: http://www.gsak.net/help/hs23820.htm self.execute("ALTER TABLE activities ADD COLUMN search_name varchar2") activities = self.fetchall("select * from activities") statement = "update activities set search_name = ? where id = ?" for activity in activities: self.execute(statement, (activity['name'].lower(), activity['id'])) # same for categories self.execute("ALTER TABLE categories ADD COLUMN search_name varchar2") categories = self.fetchall("select * from categories") statement = "update categories set search_name = ? where id = ?" for category in categories: self.execute(statement, (category['name'].lower(), category['id'])) if version < 9: # adding full text search self.execute("""CREATE VIRTUAL TABLE fact_index USING fts3(id, name, category, description, tag)""") if version < 10: # new Redmine facts table self.execute("""CREATE TABLE redmine_facts(id INTEGER NOT NULL, redmine_issue INTEGER, redmine_activity INTEGER)""") ids = self.fetchall("select id from facts") query = "insert into redmine_facts values(?, -1, -1)" for cid in ids: self.execute(query, (cid['id'],)) # at the happy end, update version number if version < current_version: #lock down current version self.execute("UPDATE version SET version = %d" % current_version) print "updated database from version %d to %d" % (version, current_version) # oldtimer – database version structure had been performed on startup (thus we know that user has been on at least 2 versions) if trophies: trophies.unlock("oldtimer") self.end_transaction()
def run_fixtures(self): self.start_transaction() """upgrade DB to hamster version""" version = self.fetchone("SELECT version FROM version")["version"] current_version = 11 if version < 8: # working around sqlite's utf-f case sensitivity (bug 624438) # more info: http://www.gsak.net/help/hs23820.htm self.execute("ALTER TABLE activities ADD COLUMN search_name varchar2") activities = self.fetchall("select * from activities") statement = "update activities set search_name = ? where id = ?" for activity in activities: self.execute(statement, (activity['name'].lower(), activity['id'])) # same for categories self.execute("ALTER TABLE categories ADD COLUMN search_name varchar2") categories = self.fetchall("select * from categories") statement = "update categories set search_name = ? where id = ?" for category in categories: self.execute(statement, (category['name'].lower(), category['id'])) if version < 9: # adding full text search self.execute("""CREATE VIRTUAL TABLE fact_index USING fts3(id, name, category, description, tag)""") if version < 10: # adding exported self.execute("""ALTER TABLE facts ADD COLUMN exported bool default false""") self.execute("""UPDATE facts set exported=1""") if version < 11: #adding redmine_issues table self.execute("""CREATE TABLE redmine_issues (id int PRIMARY KEY, name varchar2(500), mine boolean, search_name varchar2(500), deleted integer)""") # at the happy end, update version number if version < current_version: #lock down current version self.execute("UPDATE version SET version = %d" % current_version) print "updated database from version %d to %d" % (version, current_version) # oldtimer – database version structure had been performed on startup (thus we know that user has been on at least 2 versions) if trophies: trophies.unlock("oldtimer") self.end_transaction()
def on_db_file_change(monitor, gio_file, event_uri, event): if event == gio.FILE_MONITOR_EVENT_CHANGES_DONE_HINT: if gio_file.query_info(gio.FILE_ATTRIBUTE_ETAG_VALUE).get_etag() == self.__last_etag: # ours return elif event == gio.FILE_MONITOR_EVENT_CREATED: # treat case when instead of a move, a remove and create has been performed self.con = None if event in (gio.FILE_MONITOR_EVENT_CHANGES_DONE_HINT, gio.FILE_MONITOR_EVENT_CREATED): print "DB file has been modified externally. Calling all stations" self.dispatch_overwrite() # plan "b" – synchronize the time tracker's database from external source while the tracker is running trophies.unlock("plan_b")
def __remove_activity(self, id): """ check if we have any facts with this activity and behave accordingly if there are facts - sets activity to deleted = True else, just remove it""" query = "select count(*) as count from facts where activity_id = ?" bound_facts = self.fetchone(query, (id,))['count'] if bound_facts > 0: self.execute("UPDATE activities SET deleted = 1 WHERE id = ?", (id,)) else: self.execute("delete from activities where id = ?", (id,)) # Finished! - deleted an activity with more than 50 facts on it if bound_facts >= 50: trophies.unlock("finished")
def __remove_activity(self, id): """ check if we have any facts with this activity and behave accordingly if there are facts - sets activity to deleted = True else, just remove it""" query = "select count(*) as count from facts where activity_id = ?" bound_facts = self.fetchone(query, (id,))['count'] if bound_facts > 0: self.execute("UPDATE activities SET deleted = 1 WHERE id = ?", (id,)) else: self.execute("delete from activities where id = ?", (id,)) # Finished! - deleted an activity with more than 50 facts on it if trophies and bound_facts >= 50: trophies.unlock("finished")
def on_help_clicked(self, *args): gtk.show_uri(gtk.gdk.Screen(), "ghelp:hamster-applet", 0L) trophies.unlock("basic_instructions") return False
def run_fixtures(self): self.start_transaction() # defaults work_category = { "name": _("Work"), "entries": [ _("Reading news"), _("Checking stocks"), _("Super secret project X"), _("World domination") ] } nonwork_category = { "name": _("Day-to-day"), "entries": [_("Lunch"), _("Watering flowers"), _("Doing handstands")] } """upgrade DB to hamster version""" version = self.fetchone("SELECT version FROM version")["version"] current_version = 9 if version < 2: """moving from fact_date, fact_time to start_time, end_time""" self.execute(""" CREATE TABLE facts_new (id integer primary key, activity_id integer, start_time varchar2(12), end_time varchar2(12)) """) self.execute(""" INSERT INTO facts_new (id, activity_id, start_time) SELECT id, activity_id, fact_date || fact_time FROM facts """) self.execute("DROP TABLE facts") self.execute("ALTER TABLE facts_new RENAME TO facts") # run through all facts and set the end time # if previous fact is not on the same date, then it means that it was the # last one in previous, so remove it # this logic saves our last entry from being deleted, which is good facts = self.fetchall(""" SELECT id, activity_id, start_time, substr(start_time,1, 8) start_date FROM facts ORDER BY start_time """) prev_fact = None for fact in facts: if prev_fact: if prev_fact['start_date'] == fact['start_date']: self.execute( "UPDATE facts SET end_time = ? where id = ?", (fact['start_time'], prev_fact['id'])) else: #otherwise that's the last entry of the day - remove it self.execute("DELETE FROM facts WHERE id = ?", (prev_fact["id"], )) prev_fact = fact #it was kind of silly not to have datetimes in first place if version < 3: self.execute(""" CREATE TABLE facts_new (id integer primary key, activity_id integer, start_time timestamp, end_time timestamp) """) self.execute(""" INSERT INTO facts_new (id, activity_id, start_time, end_time) SELECT id, activity_id, substr(start_time,1,4) || "-" || substr(start_time, 5, 2) || "-" || substr(start_time, 7, 2) || " " || substr(start_time, 9, 2) || ":" || substr(start_time, 11, 2) || ":00", substr(end_time,1,4) || "-" || substr(end_time, 5, 2) || "-" || substr(end_time, 7, 2) || " " || substr(end_time, 9, 2) || ":" || substr(end_time, 11, 2) || ":00" FROM facts; """) self.execute("DROP TABLE facts") self.execute("ALTER TABLE facts_new RENAME TO facts") #adding categories table to categorize activities if version < 4: #adding the categories table self.execute(""" CREATE TABLE categories (id integer primary key, name varchar2(500), color_code varchar2(50), category_order integer) """) # adding default categories, and make sure that uncategorized stays on bottom for starters # set order to 2 in case, if we get work in next lines self.execute( """ INSERT INTO categories (id, name, category_order) VALUES (1, ?, 2); """, (nonwork_category["name"], )) #check if we have to create work category - consider work everything that has been determined so, and is not deleted work_activities = self.fetchone(""" SELECT count(*) as work_activities FROM activities WHERE deleted is null and work=1; """)['work_activities'] if work_activities > 0: self.execute( """ INSERT INTO categories (id, name, category_order) VALUES (2, ?, 1); """, (work_category["name"], )) # now add category field to activities, before starting the move self.execute(""" ALTER TABLE activities ADD COLUMN category_id integer; """) # starting the move # first remove all deleted activities with no instances in facts self.execute(""" DELETE FROM activities WHERE deleted = 1 AND id not in(select activity_id from facts); """) # moving work / non-work to appropriate categories # exploit false/true = 0/1 thing self.execute(""" UPDATE activities SET category_id = work + 1 WHERE deleted is null """) #finally, set category to -1 where there is none self.execute(""" UPDATE activities SET category_id = -1 WHERE category_id is null """) # drop work column and forget value of deleted # previously deleted records are now unsorted ones # user will be able to mark them as deleted again, in which case # they won't appear in autocomplete, or in categories # resurrection happens, when user enters the exact same name self.execute(""" CREATE TABLE activities_new (id integer primary key, name varchar2(500), activity_order integer, deleted integer, category_id integer); """) self.execute(""" INSERT INTO activities_new (id, name, activity_order, category_id) SELECT id, name, activity_order, category_id FROM activities; """) self.execute("DROP TABLE activities") self.execute("ALTER TABLE activities_new RENAME TO activities") if version < 5: self.execute("ALTER TABLE facts add column description varchar2") if version < 6: # facts table could use an index self.execute( "CREATE INDEX idx_facts_start_end ON facts(start_time, end_time)" ) self.execute( "CREATE INDEX idx_facts_start_end_activity ON facts(start_time, end_time, activity_id)" ) # adding tags self.execute( """CREATE TABLE tags (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT NOT NULL, autocomplete BOOL DEFAULT true)""" ) self.execute("CREATE INDEX idx_tags_name ON tags(name)") self.execute( "CREATE TABLE fact_tags(fact_id integer, tag_id integer)") self.execute( "CREATE INDEX idx_fact_tags_fact ON fact_tags(fact_id)") self.execute("CREATE INDEX idx_fact_tags_tag ON fact_tags(tag_id)") if version < 7: self.execute( """CREATE TABLE increment_facts (id integer primary key autoincrement, activity_id integer, start_time timestamp, end_time timestamp, description varchar2)""" ) self.execute( """INSERT INTO increment_facts(id, activity_id, start_time, end_time, description) SELECT id, activity_id, start_time, end_time, description from facts""" ) self.execute("DROP table facts") self.execute("ALTER TABLE increment_facts RENAME TO facts") if version < 8: # working around sqlite's utf-f case sensitivity (bug 624438) # more info: http://www.gsak.net/help/hs23820.htm self.execute( "ALTER TABLE activities ADD COLUMN search_name varchar2") activities = self.fetchall("select * from activities") statement = "update activities set search_name = ? where id = ?" for activity in activities: self.execute(statement, (activity['name'].lower(), activity['id'])) # same for categories self.execute( "ALTER TABLE categories ADD COLUMN search_name varchar2") categories = self.fetchall("select * from categories") statement = "update categories set search_name = ? where id = ?" for category in categories: self.execute(statement, (category['name'].lower(), category['id'])) if version < 9: # adding full text search self.execute("""CREATE VIRTUAL TABLE fact_index USING fts3(id, name, category, description, tag)""" ) # at the happy end, update version number if version < current_version: #lock down current version self.execute("UPDATE version SET version = %d" % current_version) print "updated database from version %d to %d" % (version, current_version) # oldtimer – database version structure had been performed on startup (thus we know that he has been on at least 2 versions) trophies.unlock("oldtimer") """we start with an empty database and then populate with default values. This way defaults can be localized!""" category_count = self.fetchone("select count(*) from categories")[0] if category_count == 0: work_cat_id = self.__add_category(work_category["name"]) for entry in work_category["entries"]: self.__add_activity(entry, work_cat_id) nonwork_cat_id = self.__add_category(nonwork_category["name"]) for entry in nonwork_category["entries"]: self.__add_activity(entry, nonwork_cat_id) self.end_transaction()
def on_tabs_window_state_changed(self, window, event): # not enough space - maximized the overview window maximized = window.get_window().get_state( ) & gtk.gdk.WINDOW_STATE_MAXIMIZED if maximized: trophies.unlock("not_enough_space")
def run_fixtures(self): self.start_transaction() # defaults work_category = {"name": _("Work"), "entries": [_("Reading news"), _("Checking stocks"), _("Super secret project X"), _("World domination")]} nonwork_category = {"name": _("Day-to-day"), "entries": [_("Lunch"), _("Watering flowers"), _("Doing handstands")]} """upgrade DB to hamster version""" version = self.fetchone("SELECT version FROM version")["version"] current_version = 9 if version < 2: """moving from fact_date, fact_time to start_time, end_time""" self.execute(""" CREATE TABLE facts_new (id integer primary key, activity_id integer, start_time varchar2(12), end_time varchar2(12)) """) self.execute(""" INSERT INTO facts_new (id, activity_id, start_time) SELECT id, activity_id, fact_date || fact_time FROM facts """) self.execute("DROP TABLE facts") self.execute("ALTER TABLE facts_new RENAME TO facts") # run through all facts and set the end time # if previous fact is not on the same date, then it means that it was the # last one in previous, so remove it # this logic saves our last entry from being deleted, which is good facts = self.fetchall(""" SELECT id, activity_id, start_time, substr(start_time,1, 8) start_date FROM facts ORDER BY start_time """) prev_fact = None for fact in facts: if prev_fact: if prev_fact['start_date'] == fact['start_date']: self.execute("UPDATE facts SET end_time = ? where id = ?", (fact['start_time'], prev_fact['id'])) else: #otherwise that's the last entry of the day - remove it self.execute("DELETE FROM facts WHERE id = ?", (prev_fact["id"],)) prev_fact = fact #it was kind of silly not to have datetimes in first place if version < 3: self.execute(""" CREATE TABLE facts_new (id integer primary key, activity_id integer, start_time timestamp, end_time timestamp) """) self.execute(""" INSERT INTO facts_new (id, activity_id, start_time, end_time) SELECT id, activity_id, substr(start_time,1,4) || "-" || substr(start_time, 5, 2) || "-" || substr(start_time, 7, 2) || " " || substr(start_time, 9, 2) || ":" || substr(start_time, 11, 2) || ":00", substr(end_time,1,4) || "-" || substr(end_time, 5, 2) || "-" || substr(end_time, 7, 2) || " " || substr(end_time, 9, 2) || ":" || substr(end_time, 11, 2) || ":00" FROM facts; """) self.execute("DROP TABLE facts") self.execute("ALTER TABLE facts_new RENAME TO facts") #adding categories table to categorize activities if version < 4: #adding the categories table self.execute(""" CREATE TABLE categories (id integer primary key, name varchar2(500), color_code varchar2(50), category_order integer) """) # adding default categories, and make sure that uncategorized stays on bottom for starters # set order to 2 in case, if we get work in next lines self.execute(""" INSERT INTO categories (id, name, category_order) VALUES (1, ?, 2); """, (nonwork_category["name"],)) #check if we have to create work category - consider work everything that has been determined so, and is not deleted work_activities = self.fetchone(""" SELECT count(*) as work_activities FROM activities WHERE deleted is null and work=1; """)['work_activities'] if work_activities > 0: self.execute(""" INSERT INTO categories (id, name, category_order) VALUES (2, ?, 1); """, (work_category["name"],)) # now add category field to activities, before starting the move self.execute(""" ALTER TABLE activities ADD COLUMN category_id integer; """) # starting the move # first remove all deleted activities with no instances in facts self.execute(""" DELETE FROM activities WHERE deleted = 1 AND id not in(select activity_id from facts); """) # moving work / non-work to appropriate categories # exploit false/true = 0/1 thing self.execute(""" UPDATE activities SET category_id = work + 1 WHERE deleted is null """) #finally, set category to -1 where there is none self.execute(""" UPDATE activities SET category_id = -1 WHERE category_id is null """) # drop work column and forget value of deleted # previously deleted records are now unsorted ones # user will be able to mark them as deleted again, in which case # they won't appear in autocomplete, or in categories # resurrection happens, when user enters the exact same name self.execute(""" CREATE TABLE activities_new (id integer primary key, name varchar2(500), activity_order integer, deleted integer, category_id integer); """) self.execute(""" INSERT INTO activities_new (id, name, activity_order, category_id) SELECT id, name, activity_order, category_id FROM activities; """) self.execute("DROP TABLE activities") self.execute("ALTER TABLE activities_new RENAME TO activities") if version < 5: self.execute("ALTER TABLE facts add column description varchar2") if version < 6: # facts table could use an index self.execute("CREATE INDEX idx_facts_start_end ON facts(start_time, end_time)") self.execute("CREATE INDEX idx_facts_start_end_activity ON facts(start_time, end_time, activity_id)") # adding tags self.execute("""CREATE TABLE tags (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT NOT NULL, autocomplete BOOL DEFAULT true)""") self.execute("CREATE INDEX idx_tags_name ON tags(name)") self.execute("CREATE TABLE fact_tags(fact_id integer, tag_id integer)") self.execute("CREATE INDEX idx_fact_tags_fact ON fact_tags(fact_id)") self.execute("CREATE INDEX idx_fact_tags_tag ON fact_tags(tag_id)") if version < 7: self.execute("""CREATE TABLE increment_facts (id integer primary key autoincrement, activity_id integer, start_time timestamp, end_time timestamp, description varchar2)""") self.execute("""INSERT INTO increment_facts(id, activity_id, start_time, end_time, description) SELECT id, activity_id, start_time, end_time, description from facts""") self.execute("DROP table facts") self.execute("ALTER TABLE increment_facts RENAME TO facts") if version < 8: # working around sqlite's utf-f case sensitivity (bug 624438) # more info: http://www.gsak.net/help/hs23820.htm self.execute("ALTER TABLE activities ADD COLUMN search_name varchar2") activities = self.fetchall("select * from activities") statement = "update activities set search_name = ? where id = ?" for activity in activities: self.execute(statement, (activity['name'].lower(), activity['id'])) # same for categories self.execute("ALTER TABLE categories ADD COLUMN search_name varchar2") categories = self.fetchall("select * from categories") statement = "update categories set search_name = ? where id = ?" for category in categories: self.execute(statement, (category['name'].lower(), category['id'])) if version < 9: # adding full text search self.execute("""CREATE VIRTUAL TABLE fact_index USING fts3(id, name, category, description, tag)""") # at the happy end, update version number if version < current_version: #lock down current version self.execute("UPDATE version SET version = %d" % current_version) print "updated database from version %d to %d" % (version, current_version) # oldtimer – database version structure had been performed on startup (thus we know that he has been on at least 2 versions) trophies.unlock("oldtimer") """we start with an empty database and then populate with default values. This way defaults can be localized!""" category_count = self.fetchone("select count(*) from categories")[0] if category_count == 0: work_cat_id = self.__add_category(work_category["name"]) for entry in work_category["entries"]: self.__add_activity(entry, work_cat_id) nonwork_cat_id = self.__add_category(nonwork_category["name"]) for entry in nonwork_category["entries"]: self.__add_activity(entry, nonwork_cat_id) self.end_transaction()
def __add_fact(self, serialized_fact, start_time, end_time = None, temporary = False): fact = stuff.Fact(serialized_fact, start_time = start_time, end_time = end_time) if not fact.activity or start_time is None: # sanity check return 0 # get tags from database - this will create any missing tags too tags = [dict(zip(('id', 'name', 'autocomplete'), row)) for row in self.GetTagIds(fact.tags)] now = datetime.datetime.now() # if in future - roll back to past if start_time > datetime.datetime.now(): start_time = dt.datetime.combine(now.date(), start_time.time()) if start_time > now: start_time -= dt.timedelta(days = 1) if end_time and end_time > now: end_time = dt.datetime.combine(now.date(), end_time.time()) if end_time > now: end_time -= dt.timedelta(days = 1) # now check if maybe there is also a category category_id = None if fact.category: category_id = self.__get_category_id(fact.category) if not category_id: category_id = self.__add_category(fact.category) trophies.unlock("no_hands") # try to find activity, resurrect if not temporary activity_id = self.__get_activity_by_name(fact.activity, category_id, resurrect = not temporary) if not activity_id: activity_id = self.__add_activity(fact.activity, category_id, temporary) else: activity_id = activity_id['id'] # if we are working on +/- current day - check the last_activity if (dt.datetime.now() - start_time <= dt.timedelta(days=1)): # pull in previous facts facts = self.__get_todays_facts() previous = None if facts and facts[-1]["end_time"] == None: previous = facts[-1] if previous and previous['start_time'] < start_time: # check if maybe that is the same one, in that case no need to restart if previous["activity_id"] == activity_id \ and set(previous["tags"]) == set([tag["name"] for tag in tags]) \ and (previous["description"] or "") == (fact.description or ""): return None # if no description is added # see if maybe previous was too short to qualify as an activity if not previous["description"] \ and 60 >= (start_time - previous['start_time']).seconds >= 0: self.__remove_fact(previous['id']) # now that we removed the previous one, see if maybe the one # before that is actually same as the one we want to start # (glueing) if len(facts) > 1 and 60 >= (start_time - facts[-2]['end_time']).seconds >= 0: before = facts[-2] if before["activity_id"] == activity_id \ and set(before["tags"]) == set([tag["name"] for tag in tags]): # resume and return update = """ UPDATE facts SET end_time = null WHERE id = ? """ self.execute(update, (before["id"],)) return before["id"] else: # otherwise stop update = """ UPDATE facts SET end_time = ? WHERE id = ? """ self.execute(update, (start_time, previous["id"])) # done with the current activity, now we can solve overlaps if not end_time: end_time = self.__squeeze_in(start_time) else: self.__solve_overlaps(start_time, end_time) # finally add the new entry insert = """ INSERT INTO facts (activity_id, start_time, end_time, description) VALUES (?, ?, ?, ?) """ self.execute(insert, (activity_id, start_time, end_time, fact.description)) fact_id = self.__last_insert_rowid() #now link tags insert = ["insert into fact_tags(fact_id, tag_id) values(?, ?)"] * len(tags) params = [(fact_id, tag["id"]) for tag in tags] self.execute(insert, params) self.__remove_index([fact_id]) return fact_id
def __add_fact(self, serialized_fact, start_time, end_time = None, temporary = False, redmine_issue = -1, redmine_activity = -1): fact = None if redmine_issue == -1: fact = Fact(serialized_fact, start_time = start_time, end_time = end_time) else: fact = RedmineFact(serialized_fact, redmine_issue_id = redmine_issue, redmine_time_activity_id = redmine_activity, start_time = start_time, end_time = end_time) start_time = start_time or fact.start_time end_time = end_time or fact.end_time if not fact.activity or start_time is None: # sanity check return 0 # get tags from database - this will create any missing tags too tags = [(tag['id'], tag['name'], tag['autocomplete']) for tag in self.get_tag_ids(fact.tags)] # now check if maybe there is also a category category_id = None if fact.category: category_id = self.__get_category_id(fact.category) if not category_id: category_id = self.__add_category(fact.category) if trophies: trophies.unlock("no_hands") # try to find activity, resurrect if not temporary activity_id = self.__get_activity_by_name(fact.activity, category_id, resurrect = not temporary) if not activity_id: activity_id = self.__add_activity(fact.activity, category_id, temporary) else: activity_id = activity_id['id'] # if we are working on +/- current day - check the last_activity if (dt.timedelta(days=-1) <= dt.datetime.now() - start_time <= dt.timedelta(days=1)): # pull in previous facts facts = self.__get_todays_facts() previous = None if facts and facts[-1]["end_time"] == None: previous = facts[-1] if previous and previous['start_time'] <= start_time: # check if maybe that is the same one, in that case no need to restart if previous["activity_id"] == activity_id \ and set(previous["tags"]) == set([tag["name"] for tag in tags]) \ and (previous["description"] or "") == (fact.description or ""): return None # if no description is added # see if maybe previous was too short to qualify as an activity if not previous["description"] \ and 60 >= (start_time - previous['start_time']).seconds >= 0: self.__remove_fact(previous['id']) # now that we removed the previous one, see if maybe the one # before that is actually same as the one we want to start # (glueing) if len(facts) > 1 and 60 >= (start_time - facts[-2]['end_time']).seconds >= 0: before = facts[-2] if before["activity_id"] == activity_id \ and set(before["tags"]) == set([tag["name"] for tag in tags]): # resume and return update = """ UPDATE facts SET end_time = null WHERE id = ? """ self.execute(update, (before["id"],)) return before["id"] else: # otherwise stop update = """ UPDATE facts SET end_time = ? WHERE id = ? """ self.execute(update, (start_time, previous["id"])) # done with the current activity, now we can solve overlaps if not end_time: end_time = self.__squeeze_in(start_time) else: self.__solve_overlaps(start_time, end_time) # finally add the new entry insert = """ INSERT INTO facts (activity_id, start_time, end_time, description) VALUES (?, ?, ?, ?) """ self.execute(insert, (activity_id, start_time, end_time, fact.description)) fact_id = self.__last_insert_rowid() # Redmine data if isinstance(fact, RedmineFact): insert = """INSERT INTO redmine_facts VALUES(?, ?, ?)""" self.execute(insert, (fact_id, fact.redmine_issue_id, fact.redmine_time_activity_id)) else: insert = """INSERT INTO redmine_facts VALUES(?, -1, -1)""" self.execute(insert, (fact_id,)) #now link tags insert = ["insert into fact_tags(fact_id, tag_id) values(?, ?)"] * len(tags) params = [(fact_id, tag[0]) for tag in tags] self.execute(insert, params) self.__remove_index([fact_id]) return fact_id
def __solve_overlaps(self, start_time, end_time): """finds facts that happen in given interval and shifts them to make room for new fact """ if end_time is None or start_time is None: return # possible combinations and the OR clauses that catch them # (the side of the number marks if it catches the end or start time) # |----------------- NEW -----------------| # |--- old --- 1| |2 --- old --- 1| |2 --- old ---| # |3 ----------------------- big old ------------------------ 3| query = """ SELECT a.*, b.name, c.name as category FROM facts a LEFT JOIN activities b on b.id = a.activity_id LEFT JOIN categories c on b.category_id = c.id WHERE (end_time > ? and end_time < ?) OR (start_time > ? and start_time < ?) OR (start_time < ? and end_time > ?) ORDER BY start_time """ conflicts = self.fetchall(query, (start_time, end_time, start_time, end_time, start_time, end_time)) for fact in conflicts: # won't eliminate as it is better to have overlapping entries than loosing data if start_time < fact["start_time"] and end_time > fact["end_time"]: continue # split - truncate until beginning of new entry and create new activity for end if fact["start_time"] < start_time < fact["end_time"] and \ fact["start_time"] < end_time < fact["end_time"]: logging.info("splitting %s" % fact["name"]) # truncate until beginning of the new entry self.execute("""UPDATE facts SET end_time = ? WHERE id = ?""", (start_time, fact["id"])) fact_name = fact["name"] # create new fact for the end new_fact = Fact(fact["name"], category = fact["category"], description = fact["description"]) new_fact_id = self.__add_fact(new_fact.serialized_name(), end_time, fact["end_time"]) # copy tags tag_update = """INSERT INTO fact_tags(fact_id, tag_id) SELECT ?, tag_id FROM fact_tags WHERE fact_id = ?""" self.execute(tag_update, (new_fact_id, fact["id"])) #clone tags if trophies: trophies.unlock("split") # overlap start elif start_time < fact["start_time"] < end_time: logging.info("Overlapping start of %s" % fact["name"]) self.execute("UPDATE facts SET start_time=? WHERE id=?", (end_time, fact["id"])) # overlap end elif start_time < fact["end_time"] < end_time: logging.info("Overlapping end of %s" % fact["name"]) self.execute("UPDATE facts SET end_time=? WHERE id=?", (start_time, fact["id"]))
def on_tabs_window_state_changed(self, window, event): # not enough space - maximized the overview window maximized = window.get_window().get_state() & gtk.gdk.WINDOW_STATE_MAXIMIZED if maximized: trophies.unlock("not_enough_space")
def __solve_overlaps(self, start_time, end_time): """finds facts that happen in given interval and shifts them to make room for new fact """ if end_time is None or start_time is None: return # possible combinations and the OR clauses that catch them # (the side of the number marks if it catches the end or start time) # |----------------- NEW -----------------| # |--- old --- 1| |2 --- old --- 1| |2 --- old ---| # |3 ----------------------- big old ------------------------ 3| query = """ SELECT a.*, b.name, c.name as category FROM facts a LEFT JOIN activities b on b.id = a.activity_id LEFT JOIN categories c on b.category_id = c.id WHERE (end_time > ? and end_time < ?) OR (start_time > ? and start_time < ?) OR (start_time < ? and end_time > ?) ORDER BY start_time """ conflicts = self.fetchall(query, (start_time, end_time, start_time, end_time, start_time, end_time)) for fact in conflicts: # won't eliminate as it is better to have overlapping entries than loosing data if start_time < fact["start_time"] and end_time > fact["end_time"]: continue # split - truncate until beginning of new entry and create new activity for end if fact["start_time"] < start_time < fact["end_time"] and \ fact["start_time"] < end_time < fact["end_time"]: logging.info("splitting %s" % fact["name"]) # truncate until beginning of the new entry self.execute("""UPDATE facts SET end_time = ? WHERE id = ?""", (start_time, fact["id"])) fact_name = fact["name"] # create new fact for the end new_fact = stuff.Fact(fact["name"], category = fact["category"], description = fact["description"], ) new_fact_id = self.__add_fact(new_fact.serialized_name(), end_time, fact["end_time"]) # copy tags tag_update = """INSERT INTO fact_tags(fact_id, tag_id) SELECT ?, tag_id FROM fact_tags WHERE fact_id = ?""" self.execute(tag_update, (new_fact_id, fact["id"])) #clone tags trophies.unlock("split") # overlap start elif start_time < fact["start_time"] < end_time: logging.info("Overlapping start of %s" % fact["name"]) self.execute("UPDATE facts SET start_time=? WHERE id=?", (end_time, fact["id"])) # overlap end elif start_time < fact["end_time"] < end_time: logging.info("Overlapping end of %s" % fact["name"]) self.execute("UPDATE facts SET end_time=? WHERE id=?", (start_time, fact["id"]))