Thoughts for the Impact4 Back-End


Database Structure

Table: Projects (stores the projects)
note: projects have an ID which increments for each version of it, but the GUID says the same for a unique project. To get the current project of GUID ABC1 you would have a sproc that does select top(1) from Project where GUID = ABC1 Order By ID or something like that
Column: ID (a unique ID for a version)
Column: GUID (the individual project's ID and shared key)
Column: Name, Contact, etc
Do we want to structure this in 3rd normal form instead of the XmlSerialized form for the following?
Column: Configuration (the serialized version of the Configuration class using XmlSerializer)
Column: Navigation (the serialized version of the Navigation class using XmlSerializer)
Column: Responsible (the UserID of the person responsible for this version number)
Table: ProjectData (data that came back over our web service interface)
note: we don't store any schema or metadata, we assume consistence via Projects.Configuration, thus we must have some process or sproc that validates correct types. maybe.
Column: ProjectGUID (the GUID from the Projects table)
Column: Name (field name)
Column: Value (field value - stored as an nvarchar)
Column: Type (the data type  -- whatever sproc we have to manage this table will have to cast return values and modify the recordset such that we drop the type and cast value to type)
(open item: should we map this to W3C xmlschema-2? - it would be a good way to solve complex and union data types)
Column: Source (where this data came from - an enum {SelfHost, Clinic, ResearchAssistant, Test})
Column: UserAgent (the user agent string we got from the insert)
Column: DateTime (timestamp this was put into the database)
Table: Users (stores the users)
Column: ID (the user ID)
Column: Username, Password, etc
Column: ProjectAccess (a "|" delimited string of GUIDs, thus allowing users to have access to multiple projects and projects to have multiple users)
Table: Modules (stores the unique modules)
note: we don't store an ID - modules are always referenced by their name
Column: Name (the modules name)
Column: Owner (the user ID that is responsible for this module)
Column: Anything else in the modules self definition
Table: ModulesDataSchema (stores the data schema for the module)
Column: ModuleName (the owning modules name)
Column: Name (the field name)
Column: DataType (the field's datatype. See discussion on ProjectData.Type)
Column: Access (the field's type, enum {Constant, Writeable})
Column: Required (true, false)



The easiest way to handle holding the complex datatype that is the configuration.xml is to make a class out of it, and then store it in the database as the Xml serialized version of the class. When a front end client wishes to use or modify the configuration, he or she must grab the XML stored in the database, deserialize it into a class, modify the class and then roundtrip it back into XML.


All the classes will be in the edu.ucsd.Impact4 and the edu.ucsd.Impact4.ProjectConfiguration namespace. The ProjectConfiguration namespace contains all the classes that belong to the navigation XML tag. They are the logic layer above the database. The italicized methods of the classes will interact with the database via sprocs.


-       User
o      new User()
o      User Commit()
o      Project[] GetProjects()
o      Project GetProject(ProjectGUID)
o      Project GetProject(Project)
o      static User Validate(UserName, Password)
o      Project[] FindResponsibleRevisions(ProjectGUID)
-       ModuleFactory
o      static Module GetAllModules()
o      static GetModuleDataSchema(Module)
-       Project
o      new Project()
o      Project Commit()
o      string Name, Contact, etc
o      int ID
o      GUID GUID
o      ConfigurationElement Configuration
o      NavigationElement Navigation
o      User[] GetUsers()
-       Data
o      RecordSet GetFullDataSet(ProjectGUID)
o      RecordSet GetData(ProjectGUID)
o      bool AddData(ProjectGUID, Name, Value)
o      bool AddData(ProjectGUID, Name, Value, Source)
o      bool AddData(ProjectGUID, Name, Value, Source, UserAgent)


-       ConfigurationElement
-       Data : ConfigurationElement
-       Property : ConfigurationElement
-       Help : ConfigurationElement
-       Library : ConfigurationElement
-       Display : ConfigurationElement
-       NavigationElement
o      AddAtEnd
o      AddAtBeginning
o      AddBefore(Index)
o      AddAfter(Index)
o      Delete(Index)
o      Indexer[]
-       Random : NavigationElement
-       Group : NavigationElement
-       Module : NavigationElement
-       Field : NavigationElement
-       Constant : NavigationElement
-       Condition : NavigationElement
-       True : NavigationElement
-       False : NavigationElement
-       Set : NavigationElement

Usage Scenario

An example usage scenario of this class library would look like:

// Attempt to get a handle on the user
User theUser = User.Validate(strUsername, strPassword);
catch (UserNotFoundException ex1)
      // notify the UI that the user was not validated
catch (Exception ex)
      // notify the UI that some other error occurred
// Now get all the projects
Projects[] allProjects = theUser.GetProjects();
// For sake of example, we only want the first project
Project firstProject = allProjects[0];
// change the name of the contact
firstProject.Contact = "Dawna Perkins"
// Add a value in the configuration
firstProject.Configuration.Properties.Add("checkXMLValidity", false);
// create a new module instance
Module toAdd = new Module("vas");
toAdd.Constants.Add("upperAnchor", "Death");
toAdd.Constants.Add("lowerAnchor", "Life");
toAdd.Fields.Add("vasValue", DataTypeEnum.Integer);
// Add a new module at the end of the navigation path
// Finally, save the project

Generation Ideas

Generation should be fairly simple. The generator will have to walk the Configuration.Library, Configuration.Help and Navigation.Module trees to find all modules, and then put them into the \Modules directory. Then will then need to get the configuration XML out of the database and save both halves into configuration.xml. Then, it should zip and deliver. See Distribution Directory Layout.txt for information on how the directory is laid out.