Database Model

TimeSnapper has an open database model but we reserve the rights to make changes at any time without any notice!

TimeSnapper Tables

Activity

• Each screenshot that is taken has a lot of associated information. This helps TimeSnapper 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
 );

</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

<source lang="sql"> CREATE TABLE TIME_SPENT (

   SNAPDATE      DATE NOT NULL,
   MINUTESSPENT  NUMERIC(14,10) NOT NULL

); </source>