Difference between revisions of "Database Model"

From TimeSnapper
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
TimeSnapper has an open database model but we reserve the rights to make changes at any time without any notice!
 
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 =
 
= TimeSnapper Tables =
  
 
== Activity ==
 
== Activity ==
* Each screenshot that is taken has a lot of associated information.  This helps TimeSnapper create interesting reports.
+
Each screenshot that is taken has a lot of associated information.  This helps TimeSnapper to create interesting reports.
  
 
<source lang="sql">
 
<source lang="sql">
Line 19: Line 21:
 
     PWDID          INTEGER,
 
     PWDID          INTEGER,
 
     URL            VARCHAR(1000),
 
     URL            VARCHAR(1000),
     WEBPROGID      INTEGER
+
     WEBPROGID      INTEGER,
 +
    ARCHIVED        INTEGER,
 +
    PROTECT        INTEGER
 
   );
 
   );
 
</source>
 
</source>
  
 
== Classification ==
 
== Classification ==
* List of programs and keywords that marks a screenshot as productive one.
+
List of programs and keywords that marks a screenshot as productive one.
 +
 
 
<source lang="sql">
 
<source lang="sql">
 
CREATE TABLE CLASSIFICATION (
 
CREATE TABLE CLASSIFICATION (
Line 43: Line 48:
  
 
== Filters ==
 
== Filters ==
* Exclusion and inclusion filters.
+
Exclusion and inclusion [[filters|Filters]].
 
<source lang="sql">
 
<source lang="sql">
 
CREATE TABLE FILTERS (
 
CREATE TABLE FILTERS (
Line 53: Line 58:
  
 
== Flag ==
 
== Flag ==
* The flags are on the timeline/[[TimeBar]].
+
The [[flags]] are on the timeline/[[TimeBar]].
 
<source lang="sql">
 
<source lang="sql">
 
CREATE TABLE FLAG (
 
CREATE TABLE FLAG (
Line 71: Line 76:
  
 
== Options ==
 
== 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.
+
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">
 
<source lang="sql">
 
CREATE TABLE OPTIONS (
 
CREATE TABLE OPTIONS (
Line 80: Line 86:
  
 
== Program ==
 
== 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).
+
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">
 
<source lang="sql">
 
CREATE TABLE PROGRAM (
 
CREATE TABLE PROGRAM (
Line 93: Line 100:
  
 
== Pwd ==
 
== 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.
+
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.
+
 
 +
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">
 
<source lang="sql">
Line 104: Line 112:
  
 
== Scorecardvalues ==
 
== Scorecardvalues ==
* Table for storing the scores in the scorecard for a particular day.
+
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).
+
This table gets truncated every time a new program or new caption is marked as productive (because a recalculation is needed).
  
 
<source lang="sql">
 
<source lang="sql">
Line 116: Line 124:
  
 
== Time_spent ==
 
== Time_spent ==
* Number of minutes spent in front of the computer per day
+
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">
 
<source lang="sql">
 
CREATE TABLE TIME_SPENT (
 
CREATE TABLE TIME_SPENT (

Latest revision as of 13:06, 14 April 2011

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>