Create the H2 database

[brianewalsh]

For the first pass at the databases, might I suggest the starting schema to be something like:

---

Table Name: properties Objective: The properties table will store the most recent value and currently active value for any and all properties.

Column Name: id Column Type: numeric Column Desc: The primary key used as a foreign key to other tables or quick joins

Column Name: key Column Type: Variable Character of maximum length 255 Column Desc: The name is a unique key that uniquely identifies the "name" of the property. In most cases the name should be the uri with parameters. Note that prior to inserting the values in the database or retrieving the values out of the database all of our URIs go through a normalization process that orders the parameters alphabetically and removes any parameters that are used for security and or internally. Some example URIs: sonja/database/username&release=1.0.0.0&systemType=development sonja/database/username&release=1.0.0.0&systemType=production sonja/database/username

Column Name: value Column Type: Variable Character of maximum length 2048 Column Desc: This is the actual value of the property. This is the only field that the user's can actually update. See the description of the table for the actions required upon update/delete.

Column Name: version Column Type: integer Column Desc: This column increments by one upon each update/delete of this property

Column Name: creation_user Column Type: Variable Character of maximum length 256 Column Desc: This value stores a uniquely identifiable field that can be found when querying the user table. Note that if the user is inactive and deleted this value should be maintained so a foreign key does not have to be established. This is more for auditing and historical reasons. This is the user that originally created this property and never changes even after the property is deleted and comes back to life this property should be the same as the originally creating user.

Column Name: creation_datetime Column Type: Date including time down to the millisecond. Column Desc: related to the creation user, this is the date and time this property was first created in our database.

Column Name: odometer Column Type: integer Column Desc: This property increments by one each time the server detects a request to obtain the value stored by this property. This keeps usage statistics on the property so we know how many times it is requested.

Column Name: last_accessed_datetime Column Type: Date including time down to the millisecond. Column Desc: This property is updated each time the odometer changes

Column Name: last_accessed_user Column Type: Variable Character of maximum length 256 Column Desc: This property is updated each time the odometer changes. This property is extremely helpful to determine if a server/user is retreiving a property as expected. If the property does not show the accesses then there is usually a problem. We can also check the logs

Column Name: last_modified_datetime Column Type: Date including time down to the millisecond. Column Desc: This property is updated only when the value field is changed.

Column Name: last_modified_user Column Type: Variable Character of maximum length 256 Column Desc: This property is updated only when the value field is changed.

---

Table Name: historical_properties Objective: This table is an exact copy of the properties table outlined above. This table is populated each time a property value is updated or deleted. The record that is changed or deleted is inserted into this table to maintain the historical values of the property (this is important as our users often want to see the previous values). This table only gets changed when a property is deleted or the "value" field of the property is changed (not on updates to the odometer or last accessed fields). Note that the properties table's primary key's will not be unique in this table because of the nature of the inserts on updates.

---

Table Name: tags Objective: This table stores all of the name and value pairs we consider tags which are usually parameters or query string values in the URI. Note that we use this table to store our system tags as well which in some cases are stripped off of the URI as they are used by the and do not help create the unique name of the URI. One example of these system tags are the security/group related tags. Since a property can have multiple security tags and anyone with one of the security tags can access it. At the moment tags are never deleted.

Column Name: id Column Type: numeric Column Desc: The primary key used as a foreign key to other tables or quick joins

Column Name: key Column Type: Variable Character of maximum length 256 Column Desc: A unique key, the name of the tag usually the lefthand side of the parameter in the URI

Column Name: value Column Type: Variable Character of maximum length 256 Column Desc: The value associated with this tag

Column Name: type Column Type: Variable Character of maximum length 256 Column Desc: The type can be public, private, or user defined. Note that the public is the default type. Private tags are typically stripped from the URI before we use the URI as a unique value. Some examples of private tags would be security and or username.

---

Table Name: properties_tag_mapping Objective: This table stores the mapping between our properties and their associated system tags. We do not store all tag relationships at the moment.

Column Name: properties_id Column Type: numeric Column Desc: Foreign Key to the id field of the properties table.

Column Name: tag_id Column Type: numeric Column Desc: Foreign Key to he id field of the tag table.

Note the primary key of this table is the combination of the two columns above.

---

Table Name: users Objective: This table stores a unique list of our usernames

Column Name: id Column Type: numeric Column Desc: The primary key used as a foreign key to other tables or quick joins

Column Name: username Column Type: Variable Character of maximum length 255 Column Desc: The name is a unique key that uniquely identifies each uesrs. This can be a DN from a certificate or a simple username depending on the access control mechanism.

Column Name: creation_user Column Type: Variable Character of maximum length 256 Column Desc: At some point we have to have a script to create the first users (this could also be a system created users).

Column Name: creation_datetime Column Type: Date including time down to the millisecond. Column Desc: related to the creation user, this is the date and time this property was first created in our database.

[jackrod]

It would be nice to simplify the schema so that attributes of the properties are service-tags. I updated the naming of some of the tables to be consistent with key/value pairing.

[danbogert]

Here is the sql for the inital schema, subject to change.

create table USERS ( ID identity, USERNAME varchar(256), CREATION_USER varchar(256), CREATION_DATETIME timestamp, constraint PK_USER_ID primary key (ID));

create table TAGS ( ID identity, KEY varchar(256), VALUE varchar(256), constraint PK_TAG_ID primary key (ID));

create table PROPERTIES ( ID identity, KEY varchar(256), VALUE varchar(256), VERSION int, CREATION_USER varchar(256), CREATION_DATETIME timestamp, ODOMETER int, LAST_ACCESSED_DATETIME timestamp, LAST_ACCESSED_USER varchar(256), LAST_MODIFIED_DATETIME timestamp, LAST_MODIFIED_USER varchar(256), constraint PK_PROP_ID primary key (ID), constraint UC_PROP_KEY unique (VALUE));

create table HISTORICAL_PROPERTIES ( ID identity, KEY varchar(256), VALUE varchar(256), VERSION int, CREATION_USER varchar(256), CREATION_DATETIME timestamp, ODOMETER int, LAST_ACCESSED_DATETIME timestamp, LAST_ACCESSED_USER varchar(256), LAST_MODIFIED_DATETIME timestamp, LAST_MODIFIED_USER varchar(256));

create table PROPERTIES_TAG_MAPPING ( PROPERTY_ID bigint, TAG_ID bigint, constraint PK_PROPTAG_ID primary key (PROPERTY_ID, TAG_ID), constraint FK_PROP_ID foreign key (PROPERTY_ID) references PROPERTIES(ID), constraint FK_TAG_ID foreign key (TAG_ID) references TAGS(ID));

[brianewalsh]

Added the type column to the tags table for public and private tags.

[brianewalsh]

Removed the unique keys from the historical properties table.

[jackrod]

We should look into building out the Tag tables...