Specifications for administrative tables

Fields are shown under each table.  Comments about future changes or unresolved design issues are shown in contrasting color.  Version specific features are noted by highlighted text.

• Data about log entries
  • tblEditLog, log of entries that alter the database
    • DateTime, timestamp for time of entry
    • SFuserID, integer, 2 bytes, identifier for species file signon that did the edit, special values -1 for a service pack, -2 for an editor from the software website
    • SFSuserID, integer, 2 bytes, identifer for Species File Software signon that did the edit (except special codes for certain administrative procedures)
      • -3 = change from a different species file (from species file merging process)
      • -2 = change from the Species File Software website
      • -1 = change from a service pack
    • UnitName, text, 100 bytes maximum, program unit that generated the entry
    • Description, text, 400 bytes maximum, description of change
    • WhereClause, text, 200 bytes maximum, information used in the where clause of a SQL query
    • DataSpecs, text, 400 bytes maximum, a listing of the data specifications as they appear in OldData and NewData
    • OldData, text, 8000 bytes maximum, previously recorded data
    • NewData, text, 8000 bytes maximum, newly recorded data
  • tblSFSeditLog, a single table in SFSdb, columns are the same as in tblEditLog, as modified in version 3.5
    • DateTime, timestamp for time of entry
    • FileID, integer, 2 bytes, = tblFiles.FileID, if > 0, then the file that made a change
    • SFuserID, integer, 2 bytes, identifier for species file signon that did the edit, FileID needed to locate the signon
    • SFSuserID, integer, 2 bytes, identifier for Species File Software signon that did the edit
    • UnitName, text, 100 bytes maximum, program unit that generated the entry
    • Description, text, 400 bytes maximum, description of change
    • WhereClause, text, 200 bytes maximum, information used in the where clause of a SQL query
    • DataSpecs, text, 400 bytes maximum, a listing of the data specifications as they appear in OldData and NewData
    • OldData, text, 8000 bytes maximum, previously recorded data
    • NewData, text, 8000 bytes maximum, newly recorded data
  • tblTestLog, log of test program runs and results
    • UnitName, text, 100 bytes maximum, program that ran test
    • DateTime, datetime, time when procedure was completed
    • ErrorsFound, integer, 2 bytes, count of errors found
    • ErrorsFixed, integer, 2 bytes, count of errors fixed, value of -1 means error fix not invoked, -2 means fix not programmed
• Miscellaneous
  • tblConstants, constant values to be referenced by programs
    • Name, text, 30 bytes maximum, name for the constant
      • Basic information about the database
        • Access, "Public" or "Private" to indicate if open to public
        • ContactLink, email address for the person to contact
        • Description, description of the database and website, displayed on the home page
        • HomeLink, URL for the home page
        • LogoLink, URL for website logo
        • SMTPhost, internet address of host server
      • Rows related to Species 2000
        • GSDIdentifier, identifier assigned by Species 2000 to a global species database
        • GSDShortName, short name supplied to Species 2000
        • GSDTitle, title supplied to Species 2000
      • Rows used for version tracking
        • CommonVersion, version number for the common code (number preceeding "." in version)
        • FileVersion, version number for the file specific portion of the code
        • ServicePack, sequence number for service packs completed (number following "." in version)
        • SPscript, sequence number for service pack scripts
        • HotFix, sequence number for hot fixes for programming errors
      • Rows for tracking hits to website (probably obsolete since now using Google Analytics)
        • SessionCount, count of sessions since DateCountStarted
        • CountHistory, history of session and/or other counts for website
        • DateCountStarted, date and time when current count started
        • HitCount, count of website hits, not yet implemented
      • Rows for splitting a species file and merging back together
        • CopiedFromDB, name of the database from which this was split out
        • CopyStep, step number in the process of splitting out a database
        • MaxIDtblCollectEvents, MaxIDtblDepos, etc., maximum ID value in the table when the splitting process is completed.  This is used at the time of merge to determine which IDs are newly created and must be given new IDs in the merged result.  Row MaxIDtblTaxa, column LastUpdate is used at the  time of merge to determine which rows int the merged into database were changed subsequent to the split.
        • MergeDetailDone, name of the substep completed
        • MergedToDB, name of the database merged into.  This is usually the same as CopiedFromDB, but may have a different value for test runs.
        • MergeStep, number of the step completed in the merge process
        • OldApexNameID, TaxonNameID in the original database of the apex taxon in the split out database
      • Rows about messages
        • EditorActionNeed, bitwise flags to call an editor's attention to an issue needing resolution
          • 0 = none
          • 1 = publication(s) to be resolved
        • MsgEditor, text of message to users with edit privileges
        • MsgPublic, text of message to all users
    • Value, integer, value for the constant
    • Hex, text, 10 bytes, hexadecimal representation of Value
    • String, text, 1000 bytes maximum, non numeric constants
    • LastUpdate, smalldatetime, date and time row last updated
    • ModifiedBy, integer, 2 bytes, UserID of the signon that made the change
  • tblGlossary, words that are recognized in a glossary search
    • Word, text, 30 characters maximum, the word or series of words to be recognized
    • Page, text, 25 characters maximum, the name of the Internet page containing the information
    • Name, text, 15 characters maximum, the name of the location inside the Internet page
  • tblFiles, data about the status of various species files, a single table in SFSdb
    • FileID, integer, 2 bytes, identifier for the file
    • ServerName, text, 15 bytes maximum, name of the server that contains the database
    • DatabaseName, text, 30 bytes maximum, name of the database file
    • FileTypeID, integer, 1 byte, = tblFileTypes.FileTypeID, type of database file
    • WebsiteName, text, 30 bytes maximum, name of the website as used in the URL [WebsiteName].speciesfile.org
    • AboveID, integer, 2 bytes, = FileID of the species file above it in the taxonomic hierarchy
    • Description, text, 1000 bytes maximum, description as found in tblConstants of the database
    • DateStarted, text, 20 bytes maximum
    • NumSpecies, integer, 4 bytes, number of valid species
    • NumNames, integer, 4 bytes, total number of names
    • NumCites, integer, 4 bytes, number of citations
    • NumImages, integer, 4 bytes, number of images
    • NumSpecimens, integer, 4 bytes, number of specimen records
    • NumKeyEndPoints, integer, 4 bytes, number of end points in keys
    • FileVersion, text, 20 bytes maximum, version for the file specific portion
    • CommonVersion, integer, 2 bytes, major version number for the shared common code
    • ServicePack, integer, 2 bytes, service pack number applied to the common code
    • Flags, integer, 2 bytes, used to manage which files use features under development
  • tblFileTypes, types of database files based on how they are used
    • FileTypeID, integer, 1 byte, identifier for the file type
    • Description, text, 30 bytes maximum, description of the usage type of the database file
  • tblSources, sources of data in tables in SFSdb
    • SourceID, integer, 2 bytes, identifier for the source
    • FileID, integer, 2 bytes, = tblFiles.FileID
    • Description, text, 100 characters maximum