PageRenderTime 15ms CodeModel.GetById 9ms app.highlight 2ms RepoModel.GetById 1ms app.codeStats 1ms

/docs/dbmodel.rtf

https://code.google.com/p/parsets/
Unknown | 143 lines | 130 code | 13 blank | 0 comment | 0 complexity | 5738de54ca8da9ae7d3aa90290737d8b MD5 | raw file
  1{\rtf1\ansi\ansicpg1252\cocoartf949\cocoasubrtf460
  2{\fonttbl\f0\fswiss\fcharset0 ArialMT;}
  3{\colortbl;\red255\green255\blue255;}
  4{\*\listtable{\list\listtemplateid1\listhybrid{\listlevel\levelnfc23\levelnfcn23\leveljc2\leveljcn2\levelfollow0\levelstartat1\levelspace360\levelindent0{\*\levelmarker \{disc\}}{\leveltext\leveltemplateid0\'02\'05.;}{\levelnumbers\'01;}}{\listname ;}\listid1}
  5{\list\listtemplateid2\listhybrid{\listlevel\levelnfc23\levelnfcn23\leveljc2\leveljcn2\levelfollow0\levelstartat1\levelspace360\levelindent0{\*\levelmarker \{disc\}}{\leveltext\leveltemplateid0\'02\'05.;}{\levelnumbers\'01;}}{\listname ;}\listid2}
  6{\list\listtemplateid3\listhybrid{\listlevel\levelnfc23\levelnfcn23\leveljc2\leveljcn2\levelfollow0\levelstartat1\levelspace360\levelindent0{\*\levelmarker \{disc\}}{\leveltext\leveltemplateid0\'02\'05.;}{\levelnumbers\'01;}}{\listname ;}\listid3}
  7{\list\listtemplateid4\listhybrid{\listlevel\levelnfc23\levelnfcn23\leveljc2\leveljcn2\levelfollow0\levelstartat1\levelspace360\levelindent0{\*\levelmarker \{disc\}}{\leveltext\leveltemplateid0\'02\'05.;}{\levelnumbers\'01;}}{\listname ;}\listid4}
  8{\list\listtemplateid5\listhybrid{\listlevel\levelnfc23\levelnfcn23\leveljc2\leveljcn2\levelfollow0\levelstartat1\levelspace360\levelindent0{\*\levelmarker \{disc\}}{\leveltext\leveltemplateid0\'02\'05.;}{\levelnumbers\'01;}}{\listname ;}\listid5}
  9{\list\listtemplateid6\listhybrid{\listlevel\levelnfc23\levelnfcn23\leveljc2\leveljcn2\levelfollow0\levelstartat1\levelspace360\levelindent0{\*\levelmarker \{disc\}}{\leveltext\leveltemplateid0\'02\'05.;}{\levelnumbers\'01;}}{\listname ;}\listid6}}
 10{\*\listoverridetable{\listoverride\listid1\listoverridecount0\ls1}{\listoverride\listid2\listoverridecount0\ls2}{\listoverride\listid3\listoverridecount0\ls3}{\listoverride\listid4\listoverridecount0\ls4}{\listoverride\listid5\listoverridecount0\ls5}{\listoverride\listid6\listoverridecount0\ls6}}
 11\margl1440\margr1440\vieww11040\viewh11660\viewkind0
 12\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\ql\qnatural\pardirnatural
 13
 14\f0\fs22 \cf0 \
 15
 16\i This document is slightly out of sync with the code. It does describe the overall idea, though.
 17\i0 \
 18\
 19\pard\pardeftab720\ql\qnatural
 20
 21\b \cf0 ParSets NG Local Database Model
 22\b0 \
 23ParSets's local db is stored in a single SQLite database file called local.db. This document describes the layout of this database and the Java classes used to interact with it.\
 24\
 25The overall structure of the db is similar to the OLAP star model. There are two kinds of tables: administration tables and data tables. Each data set has entries in the administration tables, and one or two data tables.\
 26\
 27
 28\b Administration Tables
 29\b0 \
 30\
 31
 32\b Admin_DataSets
 33\b0 \
 34Contains a list of all data sets in the data base, their names, etc.\
 35\pard\tx220\tx720\pardeftab720\li720\fi-720\ql\qnatural
 36\ls1\ilvl0\cf0 {\listtext	\'95	}
 37\i Name
 38\i0  - human-readable name of the data set\
 39{\listtext	\'95	}
 40\i Handle
 41\i0  - internal name of the data set, derived from the filename or the human-readable name. Can only contain lowercase a-z and 0-9, no punctuation or spaces\
 42{\listtext	\'95	}
 43\i Section
 44\i0  - text field that can contain the name of a section the dataset is to be listed in. Not currently used in the ParSets program.\
 45{\listtext	\'95	}
 46\i DateAdded
 47\i0  - date and time the data set was added to the data base\
 48\pard\tx560\pardeftab720\ql\qnatural
 49\cf0 \
 50
 51\b Admin_Dimensions
 52\b0 \
 53List all dimensions in all data sets. The handle of the data set is the foreign key to get the list of dimensions.\
 54\pard\tx220\tx720\pardeftab720\li720\fi-720\ql\qnatural
 55\ls2\ilvl0\cf0 {\listtext	\'95	}
 56\i DataSet
 57\i0  - handle of the data set the dimension belongs to\
 58{\listtext	\'95	}Name - human-readable name of the dimension\
 59{\listtext	\'95	}Handle - internal name of the dimension, perhaps taken from the column header of the imported file. Follows the same rules as data set handle. Has to be unique within the data set.\
 60{\listtext	\'95	}Type - type of the dimension. Dimensions that are considered dimensions in the OLAP model are called "categorical" here. See list of types for measures below.\
 61\pard\pardeftab720\ql\qnatural
 62\cf0 \
 63
 64\b Admin_Categories
 65\b0 \
 66List all categories in all data sets. The handles of the data set and the dimension are the foreign keys here.\
 67\pard\tx220\tx720\pardeftab720\li720\fi-720\ql\qnatural
 68\ls3\ilvl0\cf0 {\listtext	\'95	}DataSet - handle of the data set the category belongs to\
 69{\listtext	\'95	}Dimension - handle of the dimension the category belongs to\
 70{\listtext	\'95	}Name - human-readable name of the category\
 71{\listtext	\'95	}Handle - handle of the category, either from the file or derived from name. Same rules as data set handle.\
 72{\listtext	\'95	}Number - number of the category within the dimension. This number has to be greater than 0 and is used for all internal calculations, as well as for indexing into the dimensions and data tables\
 73\pard\pardeftab720\ql\qnatural
 74\cf0 \
 75
 76\b Admin_DerivedDimensions
 77\b0 \
 78Table for specifying user-defined derived dimensions. Not currently implemented.\
 79\
 80
 81\b Data Tables
 82\b0 \
 83Each data set has one or two data tables associated with it, named 
 84\i handle
 85\i0 _dims and 
 86\i handle
 87\i0 _measures. The dims table has to exist for each data set, the measures table is optional.\
 88\
 89\pard\pardeftab720\ql\qnatural
 90
 91\i\b \cf0 handle
 92\i0 _dims
 93\b0 \
 94This table contains all the categorical dimensions as dimensions, and has an entry for each combination of categories that exists in the data set. In addition to the dimensions, the table has one column for the key of that combination (stored as a hexadecimal string because it can be larger than 64 bits) and the count of how many times each combination of categories exists.\
 95There are also pre-aggregated entries that use 0 for each combination of categories, including one entry with all zeros giving the size of the entire data set.\
 96\pard\tx220\tx720\pardeftab720\li720\fi-720\ql\qnatural
 97\ls4\ilvl0\cf0 {\listtext	\'95	}Key - the key value combining the numerical category values for all categories. Also used to connect to the measures.\
 98{\listtext	\'95	}Count - the number of times that combination of values occurs in the data set\
 99{\listtext	\'95	}Dimensions - one column for each dimension, using the dimension's handle as its name. The type of these columns is numerical.\
100\pard\pardeftab720\ql\qnatural
101\cf0 \
102\pard\pardeftab720\ql\qnatural
103
104\i\b \cf0 handle
105\i0 _measures
106\b0 \
107This table contains all numerical data dimensions, or measures. The only column all these tables have in common is the key, which is the same as in the 
108\i handle
109\i0 _dims table. All other columns represent numerical data dimensions, as specified in the Admin_Dimensions table. The type of the column depends on the numerical type (see below).\
110\pard\tx220\tx720\pardeftab720\li720\fi-720\ql\qnatural
111\ls5\ilvl0\cf0 {\listtext	\'95	}Key - the key from 
112\i handle
113\i0 _dims specifying which combination of categories the row belongs to. This does not have to be unique, many numerical rows can correspond to the same combination of categorical values. Also includes the pre-aggregatd keys, values in each dimension are summed up.\
114{\listtext	\'95	}Measures - one column for each numerical dimension in the data set.\
115\pard\pardeftab720\ql\qnatural
116\cf0 \
117\
118
119\b Measure Data Types
120\b0 \
121\pard\tx220\tx720\pardeftab720\li720\fi-720\ql\qnatural
122\ls6\ilvl0\cf0 {\listtext	\'95	}Integer - whole numbers, using the range of a 32-bit integer (Java type int)\
123{\listtext	\'95	}Real - floating-point numbers, stored as single-precision floats (Java type float)\
124{\listtext	\'95	}Date - Dates (potentially including time) stored as a string, using SQLIte's "native" time format, which mostly conforms to RFC 3339, e.g. "1990-12-31T23:59:60Z" - time zone information is ignored (Java type Calendar)\
125\pard\pardeftab720\ql\qnatural
126\cf0 \
127\
128
129\b Useful Resources
130\b0 \
131\
132SQLite, {\field{\*\fldinst{HYPERLINK "http://sqlite.org/"}}{\fldrslt http://sqlite.org/}}\
133\
134Good description of OLAP Star Schema,\
135\pard\pardeftab720\ql\qnatural
136{\field{\*\fldinst{HYPERLINK "http://ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx"}}{\fldrslt \cf0 http://ciobriefings.com/Publications/WhitePapers/DesigningtheStarSchemaDatabase/tabid/101/Default.aspx}}\
137\
138SQLite Date and Time Functions, {\field{\*\fldinst{HYPERLINK "http://sqlite.org/lang_datefunc.html"}}{\fldrslt http://sqlite.org/lang_datefunc.html}}\
139\
140RFC 3339, Date and Time on the Internet: Timestamps\
141\pard\pardeftab720\ql\qnatural
142{\field{\*\fldinst{HYPERLINK "http://www.faqs.org/rfcs/rfc3339.html"}}{\fldrslt \cf0 http://www.faqs.org/rfcs/rfc3339.html}}\
143}