Database Model
TimeSnapper has an open database model but we reserve the rights to make changes at any time without any notice!
We use Firebird as our database engine. It's very fast and mature and can be run embedded without any user interventions.
TimeSnapper Tables
Activity
Each screenshot that is taken has a lot of associated information. This helps TimeSnapper to create interesting reports.
<source lang="sql"> CREATE TABLE ACTIVITY (
ID INTEGER NOT NULL, SNAPDATE TIMESTAMP NOT NULL, FILENAME VARCHAR(500), FILESIZE INTEGER, PROGRAM VARCHAR(100), PROGRAMCAPTION VARCHAR(1000), DURATION INTEGER, CRC32 INTEGER, PROGID INTEGER, PWDID INTEGER, URL VARCHAR(1000), WEBPROGID INTEGER, ARCHIVED INTEGER, PROTECT INTEGER );
</source>
Classification
List of programs and keywords that marks a screenshot as productive one.
<source lang="sql"> CREATE TABLE CLASSIFICATION (
ID INTEGER NOT NULL, PROGID INTEGER, KEYWORDS VARCHAR(500)
); </source>
Cloudwords
<source lang="sql"> CREATE TABLE CLOUDWORDS (
WORD VARCHAR(8000) NOT NULL, WORDTYPE INTEGER NOT NULL, TS_DEFAULT INTEGER
); </source>
Filters
Exclusion and inclusion Filters. <source lang="sql"> CREATE TABLE FILTERS (
NAME VARCHAR(50) NOT NULL, INCLUSIVE INTEGER NOT NULL, CONDITIONS VARCHAR(8000)
); </source>
Flag
The flags are on the timeline/TimeBar. <source lang="sql"> CREATE TABLE FLAG (
ID INTEGER NOT NULL, FLAGDATE TIMESTAMP NOT NULL, FLAGTYPE VARCHAR(20), TEXT1 VARCHAR(250), TEXT2 VARCHAR(250), TEXT3 VARCHAR(250), TEXT4 VARCHAR(250), TEXT5 VARCHAR(250), TEXT6 VARCHAR(250), COMMENT VARCHAR(8000), COLOR INTEGER
); </source>
Options
This is where TimeSnapper stores all its options, e.g. interval, screens to capture, window location and sizes and more. Plugins can use this as well.
<source lang="sql"> CREATE TABLE OPTIONS (
NAME VARCHAR(100) NOT NULL, VAL VARCHAR(1000)
); </source>
Program
Some basic information about each program that has been captured. Web pages are also considered an application so they get their own entry here (and ProgType = 1).
<source lang="sql"> CREATE TABLE PROGRAM (
ID INTEGER, NAME VARCHAR(100), PATH VARCHAR(255), TOTALDURATION INTEGER, LASTUSED TIMESTAMP, PROGTYPE INTEGER
); </source>
Pwd
No worries, we don't store any passwords here. Just the hash of the password. This table is used to know if the user has entered the correct password for decrypting an image.
This table can contain multiple entries because images encrypted with an old password don't get decrypted and encrypted back when a new password is entered.
<source lang="sql"> CREATE TABLE PWD (
PWDID INTEGER NOT NULL, HASH VARCHAR(50) NOT NULL
); </source>
Scorecardvalues
Table for storing the scores in the scorecard for a particular day. This table gets truncated every time a new program or new caption is marked as productive (because a recalculation is needed).
<source lang="sql"> CREATE TABLE SCORECARDVALUES (
ID INTEGER NOT NULL, GRADE NUMERIC(5,2), GRADEDATE DATE
); </source>
Time_spent
Number of minutes spent in front of the computer per day. This is an optimization table was added to help with reporting.
<source lang="sql"> CREATE TABLE TIME_SPENT (
SNAPDATE DATE NOT NULL, MINUTESSPENT NUMERIC(14,10) NOT NULL
); </source>