Download Complete Time Tracking Database Script v3.12

28
Complete Time Tracking Database Script v3.12
</DataSet>
</Data>
Using SELECT Commands with SETVAR
A singleton SELECT command that returns a single value can also be used with the SETVAR
command to store the value in a variable. For example:
SETVAR VariableName TO
SELECT NAME
FROM CTTUSER
WHERE USERNAME = 'Admin';
Restricted Data Access
Database users are assigned one of three roles:
· GENROLE: Limited read.
· ADVROLE: Full read.
· SYSROLE: Read write.
These roles determine the read and write data access for the user. Users with limited read
functionality, GENROLE, will be unable to retrieve data directly from many tables. The following
views are provided to allow the retrieval of a limited set of data from the CTTUSER, CATEGORY
and RECORDEDTIME tables:
· V_USERGEN
· V_CATEGORYGEN
· V_RECORDEDTIMEGEN
INSERT, UPDATE and DELETE Commands
The INSERT, UPDATE and DELETE SQL commands modify data in the database, adding new
rows, changing rows and deleting rows respectively. They must be used with great caution as
there are particular rules that must be followed when using them.
General Stored Procedures
The following stored procedures are available for general use:
· SP_CATEGORYTREE: Returns the category hierarchy from a given parent category (or all
categories) in description or code order to the depth requested.
· SP_CATEGORYTREE_BYCODE: A convenient way to use SP_CATEGORYTREE without the
need to provide input parameters. Returns the entire category hierarchy in code order with
descriptions up to 5 levels deep.
· SP_CATEGORYTREE_BYDESC: A convenient way to use SP_CATEGORYTREE without the
need to provide input parameters. Returns the entire category hierarchy in description order with
descriptions up to 5 levels deep.
· SP_CATEGORYSTRUCTURE: Fast category hierarchy (ID, PARENTID) without regard to order
of categories within a given parent.
· SP_CATEGORYTREE_FILTERED: Returns the hierarchy of categories given a specific filter
(WHERE clause).
· SP_WEEKLYTIMESHEETREPORT: Total duration data for a 7-day period by category.
· SP_SPLITTEXT: Split the given delimited text into individual values.
· U_AUDITENABLED: Returns 'Y' if the audit trail feature is enabled, else 'N'.
· U_CRLF: Returns a Windows carriage-return/linefeed character pair.
· U_CURRENTDATETIME: Returnes the current date and time of the server.
· U_TIME_UNIT_TO_DURATION: Convert a duration in a given time unit to a duration in days.
· U_DB_VERSION: Returns the current database version.
© 2012 Backslash Pty Ltd. All Rights Reserved.