Tables
Tables defined within the base schema include:
- base
- base_apikeys
- base_files
- base_groups
- base_groups_members
- base_guids
- base_logs
- base_payments_credit_cards
- base_payments_customers
- base_payments_details
- base_payments_invoices
- base_payments_plans
- base_payments_purchases
- base_payments_remove_cards
- base_payments_transactions
- base_places
- base_places_routes
- base_places_suggestions
- base_preregistrations
- base_projects
- base_projects_users
- base_templates
- base_users
- base_users_alternate_emails
- base_users_mobiles
- base_users_mobiles_enrolements
- base_users_sessions
- base_users_sessions_log
- base_users_termination_schedule
- base_users_tokens
- base_users_uids
Base
The intent of this table is to provide a place to record the 'base_url', which should be used for transactional emails, etc.
table base
{
@BASE_ID: Int^
@base_url: Text
%Filter = BASE_ID
%Module = baseschema
%Output = mysql
%OrderBy = base_url
%Prefix = base_
%Save = base_url
%Security = INVOKER
}
DROP TABLE base;
CREATE TABLE base
(
BASE_ID INT AUTO_INCREMENT,
base_url TEXT,
base_created DATETIME DEFAULT 0,
PRIMARY KEY( BASE_ID )
);
Generated from:
- https:spgen.org.local/?table_name=base&table_fields=BASE_ID%20%20%20INT%20%20%20AUTO_INCREMENT%0Abase_url%20%20TEXT&table_comments=Authorised%3DADMIN%0AFilter%3DBASE_ID%0ASave%3Dbase_url%0AOrderBy%3D
DROP TABLE base;
CREATE TABLE base
(
BASE_ID INT AUTO_INCREMENT,
base_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
base_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
base_deleted TIMESTAMP DEFAULT 0,
base_url TEXT DEFAULT '',
PRIMARY KEY(BASE_ID)
)
COMMENT 'Authorised=ADMIN',
COMMENT 'Filter=BASE_ID',
COMMENT 'Save=base_url',
COMMENT 'OrderBy=';
The 'base_users', 'base_users_alternate_emails', and 'base_users_uids' tables store the bulk of user information.
Alerts
The following table collects serious alerts from API calls or integration scripts. The system will automatically send a notification for each unique alert received, then hourly will send an aggregated alert.
table base_alert_recipients
{
@ALERT_RECIPIENT_ID: Int^
@alert_recipient_email: Text[255]
@alert_recipient_mobile: Text[50]
%Filter = ALERT_RECIPIENT_ID
%Module = baseschema
%Output = mysql
%OrderBy = alert_recipient_email;alert_recipient_mobile
%Prefix = alert_recipient_
%Security = INVOKER
}
table base_alerts
{
@ALERT_ID: Int^
@alert_sender_timestamp: Timestamp
@alert_facility: Text[50]
@alert_severity: Text[50]
@alert_hostname: Text[255]
@alert_program: Text[50]
@alert_message: Text
@alert_context: Text
@alert_action: Text
@alert_tracking_id: Text
@alert_notification_sent: Timestamp
%Filter = ALERT_ID;alert_severity;alert_hostname;alert_program
%Module = baseschema
%Output = mysql
%OrderBy = ALERT_ID;alert_sender_timestamp
%Prefix = alert_
%Save = alert_notification_sent
%Security = INVOKER
}
table base_alerts_repeats
{
@ALERT_ID: Int*
@ALERT_REPEAT_ID: Int^
@alert_repeat_sender_timestamp: Timestamp
@alert_repeat_tracking_id: Text
@alert_repeat_notification_sent: Timestamp
%Filter = ALERT_ID;ALERT_REPEAT_ID
%Module = baseschema
%Output = mysql
%OrderBy = ALERT_ID,ALERT_REPEAT_ID
%Prefix = alert_repeat_
%Security = INVOKER
}
API Keys
table base_apikeys
{
@USER_OWNER: Int*
@APIKEY_ID: Int^
@created: Datetime
@apikey: Text[64]
@apikey_type: Text[30]
@ip_address: Text
@ORG_ID: Int
@PROJECT_ID: Int
%Filter = APIKEY_ID
%Module = baseschema
%Output = mysql
%OrderBy = APIKEY_ID
%Prefix = apikey_
%Security = INVOKER
}
DROP TABLE base_apikeys;
CREATE TABLE base_apikeys
(
USER_OWNER INT DEFAULT 0,
APIKEY_ID INT AUTO_INCREMENT,
created DATETIME DEFAULT 0,
apikey CHAR(64) DEFAULT '',
apikey_type CHAR(30) DEFAULT 'USER',
ip_address TEXT,
ORG_ID INT DEFAULT 0,
PROJECT_ID INT DEFAULT 0,
PRIMARY KEY(APIKEY_ID)
);
Auth
When logging in, user first specifies organisation name and email, and the landing page supplies the service, e.g. OLMS. The auth server uses the org_name and service to determine the auth apihost. The auth apihost is passed the org name and email to determine if there is an associated account. If there is, the user is redirected to the associated auth login url unless the user is indicated as a local user, which will also indicated any required 2FA.
This allows the user to be redirected to a third party OpenID authenication mechanism if necessary. For now extra OpenID information is stored in the openid etc field.
The eventual login flow is responsible for 2FA. 2FA could actually happen before username/password authorisation.
table base_auth
{
@AUTH_ID: Int^
@auth_domain: Text
@auth_org_name: Text
@auth_service: Text
@auth_action: Text
@auth_apihost: Text
@auth_apikey: Text
@auth_login_url: Text
@auth_token_url: Text
@auth_token_path: Text
@auth_client_id: Text
@auth_client_secret: Text
@auth_redirect_uri: Text
@auth_openid_etc: Text
%Filter = APIKEY_ID
%Module = baseschema
%Output = mysql
%OrderBy = APIKEY_ID
%Prefix = auth_
%Security = INVOKER
}
ALTER TABLE base_auth ADD UNIQUE KEY(auth_org_name(50),auth_service(50));
Auth Provisional
table base_auth_provisional
{
@AUTH_ID: Int*
@PROVISIONAL_ID: Int^
@provisional_sid: Text
%Filter = PROVISIONAL_ID;provisional_sid
%Module = baseschema
%Output = mysql
%OrderBy = PROVISIONAL_ID
%Prefix = provisional_
%Security = INVOKER
%View = view_base_auth_provisional
}
Exceptions
The 'base_exceptions' table is used to record when SQL procedures have encountered issues and failed unexpectedly. This table is often used by scheduled events that encounter issues.
table base_exceptions
{
@EXCEPTION_ID: Int^
@exception_procedure_name: Text
@exception_message: Text
%Filter = EXCEPTION_ID
%Module = baseschema
%Output = mysql
%OrderBy = EXCEPTION_ID
%Prefix = exception_
%Security = INVOKER
}
DROP TABLE base_exceptions;
CREATE TABLE base_exceptions
(
EXCEPTION_ID INT AUTO_INCREMENT,
exception_created DATETIME DEFAULT 0,
exception_procedure_name TEXT DEFAULT '',
exception_message TEXT DEFAULT '',
PRIMARY KEY (EXCEPTION_ID)
);
Files
The 'base_files' table is used to store arbitrary file data. Once a file has been added, and a FILE primary key has been allocated, the key may be stored in other relevant tables.
table base_files
{
@USER: Int*
@FILE: Int^
@version: DateTime
@kind: Text[30]
@original_filename: Text[255]
@filename: Text[255]
@filetype: Text[99]
@filesize: Text[45]
@fileextension: Text[10]
@salt: Int
@token: Text[64]
@base64: LongBlob
%Filter = USER;FILE
%Module = baseschema
%Output = mysql
%OrderBy = FILE;filename
%Security = INVOKER
}
DROP TABLE base_files;
CREATE TABLE base_files
(
FILE INT NOT NULL AUTO_INCREMENT,
USER INT NOT NULL,
version DATETIME NOT NULL,
kind CHAR(30) NOT NULL,
original_filename CHAR(255) NOT NULL,
filename CHAR(255) NOT NULL,
filetype CHAR(99) NOT NULL,
filesize CHAR(45) NOT NULL,
fileextension CHAR(10) NOT NULL,
salt INT(4) NOT NULL,
token CHAR(64) NOT NULL,
base64 LONGBLOB NOT NULL,
PRIMARY KEY (FILE)
);
table base_forms
{
@FORM_ID: Int^
@form_order: Int
@form_category: Text[50]
@form_name: Text[50]
@form_autoform_id: Text[50]
@form_data_setup_fn: Text
@form_data_setup_url: Text
@form_data_setup_key: Text
@form_data_submit_fn: Text
@form_data_submit_url: Text
@form_data_handler_fn: Text
@form_data_handler_js: Text
%Filter = FORM_ID
%Module = baseschema
%Output = mysql
%OrderBy = form_order,form_category,form_name
%Prefix = form_
%Security = INVOKER
}
ALTER TABLE base_forms ADD COLUMN form_autoform_id TEXT DEFAULT '' AFTER form_name;
table base_forms_fieldsets
{
@FORM_ID: Int*
@FIELDSET_ID: Int^
@fieldset_order: Int
@fieldset_name: Text[50]
@fieldset_element_id: Text
@fieldset_classes: Text
%Filter = FORM_ID;FIELDSET_ID
%Module = baseschema
%Output = mysql
%OrderBy = fieldset_order,fieldset_name
%Prefix = fieldset_
%Security = INVOKER
}
table base_forms_fields
{
@FORM_ID: Int*
@FIELD_ID: Int^
@field_order: Int
@field_state: Text
@field_label: Text
@field_name: Text
@field_placeholder: Text
@field_type: Text
@field_width: Text
@field_attributes: Text
@field_classes: Text
@field_setup: Text
@field_kind: Text
@field_customisable: Bool
@FIELDSET_ID: Int
%Filter = FORM_ID;FIELD_ID
%Module = baseschema
%Output = mysql
%OrderBy = fieldset_order,field_order,field_name
%Prefix = field_
%Security = INVOKER
%View = view_base_forms_fields
}
table base_forms_fields_customised
{
@FORM_ID: Int*
@FIELD_ID: Int*
@ORG_ID: Int*
@FORM_FIELD_ID: Int^
@form_field_active: Bool
@form_field_state: Text
@form_field_label: Text
@form_field_width: Text
@form_field_kind: Text
%Filter = FORM_ID;FIELD_ID;ORG_ID;FORM_FIELD_ID;field_customisable
%Module = baseschema
%Output = mysql
%OrderBy = FORM_ID,FIELD_ID
%Prefix = form_field_
%Security = INVOKER
%View = view_base_forms_fields_customised
}
ALTER TABLE base_forms_fields_customised ADD UNIQUE KEY uniqueness(FIELD_ID,ORG_ID);
Groups
table base_groups
{
@GROUP_OWNER: Int
@GROUP_ID: Int^
@group_name: Text[99]
@group_code: Text[50]
%Filter = GROUP_ID
%Module = baseschema
%Output = mysql
%OrderBy = group_name
%Prefix = group_
%Security = INVOKER
}
DROP TABLE base_groups;
CREATE TABLE base_groups
(
GROUP_ID INT AUTO_INCREMENT,
GROUP_OWNER INT NOT NULL,
group_created DATETIME NOT NULL,
group_name CHAR(99) NOT NULL,
group_code CHAR(50) NOT NULL,
PRIMARY KEY (GROUP_ID)
);
table base_groups_members
{
@USER: Int*
@GROUP_ID: Int*
@GROUP_MEMBER_ID: Int^
@group_admin: Bool
%Filter = USER;GROUP_ID;GROUP_MEMBER_ID
%Module = baseschema
%Output = mysql
%OrderBy = GROUP_MEMBER_ID
%Prefix = group_member_
%Security = INVOKER
}
ALTER TABLE base_groups_members ADD UNIQUE KEY(USER,GROUP_ID);
DROP TABLE base_groups_members;
CREATE TABLE base_groups_members
(
GROUP_ID INT,
USER INT,
group_admin BOOL,
PRIMARY KEY (GROUP_ID,USER)
);
GUIDs (Globally Unique Identifiers)
Important: the 'base_guids' table does not use 'AUTO_INCREMENT' so as to not interfere with the operation of LAST_INSERT_ID().
table base_guids
{
@GUID_ID: Int^
@guid: Text[36]
@guid_type: Text[50]
@REF_ID: Int
%Filter = GUID_ID
%Module = baseschema
%Output = mysql
%OrderBy = GUID_ID
%Prefix = guid_
%Security = INVOKER
}
ALTER TABLE base_guids ADD INDEX(guid_type(50));
ALTER TABLE base_guids ADD INDEX(guid(36));
ALTER TABLE base_guids ADD INDEX( REF_ID );
DROP TABLE base_guids;
CREATE TABLE base_guids
(
GUID_ID INT NOT NULL,
guid CHAR(36) NOT NULL,
guid_created DATETIME NOT NULL,
guid_type CHAR(50) NOT NULL,
REF_ID INT NOT NULL,
PRIMARY KEY (GUID_ID), UNIQUE KEY (guid)
);
IP Blacklist
table base_ip_address_blacklist
{
@IP_ADDRESS_ID: Int^
@ip_address: Text[255]
@ip_address_count: Int
%Filter = IP_ADDRESS_ID;ip_address
%Module = base
%Output = mysql
%OrderBy = IP_ADDRESS_ID
%Prefix = ip_address_
%Security = INVOKER
}
Logs
table base_logs
{
@CALL_ID: Int*
@LOG_ID: Int^
@sessionid: Text[64]
@logged: DateTime
@level: Text[10]
@depth: Int
@source: Text
@message: Text
%Filter = CALL_ID;LOG_ID
%Module = baseschema
%Output = mysql
%OrderBy = LOG_ID
%Prefix = log_
%Security = INVOKER
}
DROP TABLE base_logs; CREATE TABLE base_logs ( LOG_ID INT(11) AUTO_INCREMENT, CALL_ID INT(11) NOT NULL, sessionid CHAR(64) NOT NULL, logged DATETIME NOT NULL, level CHAR(20) DEFAULT '', depth INT DEFAULT 0, source TEXT, message TEXT, PRIMARY KEY (LOG_ID) );
Menus
table base_menus
{
@MENU_ID: Int^
@menu_order: Int
@menu_label: Text
@menu_url: Text
@menu_classes: Text
@MENU_PARENT: Int
@ORG_ID: Int
%Filter = MENU_ID
%Module = baseschema
%Output = mysql
%OrderBy = menu_order
%Prefix = menu_
%Security = INVOKER
}
table base_menus_authorisation
{
@MENU_ID: Int*
@MENU_AUTH_ID: Int^
@menu_auth_roles: Text
@ORG_ID: Int
%Filter = ORG_ID;MENU_ID
%Module = baseschema
%Output = mysql
%OrderBy = MENU_AUTH_ID
%Prefix = menu_auth_
%Security = INVOKER
}
table base_menus_authorisation_override
{
@MENU_ID: Int*
@ORG_OVERRIDE_ID: Int*
@MENU_AUTH_OVERRIDE_ID: Int^
@menu_auth_override_roles: Text
%Filter = ORG_OVERRIDE_ID
%Module = baseschema
%Output = mysql
%OrderBy = ORG_OVERRIDE_ID
%Prefix = menu_auth_override_
%Security = INVOKER
}
https://spgen.org/?table_name=base_messaging&table_fields=USER%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INT%20%20NOT%20NULL%0AMESSAGE_ID%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INT%20%20AUTO_INCREMENT%0Amessage_guid%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TEXT%0Amessage_type%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TEXT%0Amessage_from%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TEXT%0Amessage_to%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TEXT%0Amessage_cc%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TEXT%0Amessage_bcc%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TEXT%20%0Amessage_reply_to%20%20%20%20%20%20%20%20%20%20%20%20TEXT%0Amessage_subject%20%20%20%20%20%20%20%20%20%20%20%20%20TEXT%0Amessage_tags%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TEXT%0Amessage_content_txt64%20%20%20%20%20%20%20LONGTEXT%0Amessage_content_htm64%20%20%20%20%20%20%20LONGTEXT%0Amessage_send_at%20%20%20%20%20%20%20%20%20%20%20%20%20DATETIME%0Amessage_sent_at%20%20%20%20%20%20%20%20%20%20%20%20%20DATETIME%0Amessage_response_code%20%20%20%20%20%20%20TEXT%0Amessage_last_send_at%20%20%20%20%20%20%20%20DATETIME%0Amessage_last_sent_at%20%20%20%20%20%20%20%20DATETIME%0Amessage_last_response_code%20%20TEXT&table_comments=Authorised%3DADMIN%0AFilter%3DMESSAGE_ID%0AOrderBy%3Dmessage_created%0APrefix%3DMessage_%0ASave%3D%20message_send_at%3Bmessage_sent_at%3Bmessage_response_code%3Bmessage_last_send_at%3Bmessage_last_sent_at%3Bmessage_last_response_code%0ASecurity%3DINVOKER
table base_messaging
{
@USER: Int*
@MESSAGE_ID: Int^
@message_guid: Text
@message_type: Text
@message_from: Text
@message_to: Text
@message_cc: Text
@message_bcc: Text
@message_reply_to: Text
@message_subject: Text
@message_tags: Text
@message_content_txt64: LongText
@message_content_htm64: LongText
@message_send_at: DateTime
@message_sent_at: DateTime
@message_response_code: Text
@message_last_send_at: DateTime
@message_last_sent_at: DateTime
@message_last_response_code: Text
@message_is_test: Bool
%Filter = MESSAGE_ID;message_guid;message_sent_at
%Module = baseschema
%Output = mysql
%OrderBy = MESSAGE_ID
%Prefix = message_
%Security = INVOKER
}
DROP TABLE base_messaging;
CREATE TABLE base_messaging
(
USER INT NOT NULL,
MESSAGE_ID INT AUTO_INCREMENT,
message_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
message_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
message_deleted TIMESTAMP DEFAULT 0,
message_guid TEXT DEFAULT '',
message_type TEXT DEFAULT '',
message_from TEXT DEFAULT '',
message_to TEXT DEFAULT '',
message_cc TEXT DEFAULT '',
message_bcc TEXT DEFAULT '',
message_reply_to TEXT DEFAULT '',
message_subject TEXT DEFAULT '',
message_tags TEXT DEFAULT '',
message_content_txt64 LONGTEXT DEFAULT '',
message_content_htm64 LONGTEXT DEFAULT '',
message_send_at DATETIME DEFAULT 0,
message_sent_at DATETIME DEFAULT 0,
message_response_code TEXT DEFAULT '',
message_last_send_at DATETIME DEFAULT 0,
message_last_sent_at DATETIME DEFAULT 0,
message_last_response_code TEXT DEFAULT '',
message_is_test BOOL DEFAULT 0,
PRIMARY KEY(MESSAGE_ID)
)
COMMENT 'Authorised=ADMIN',
COMMENT 'Filter=MESSAGE_ID',
COMMENT 'OrderBy=message_created',
COMMENT 'Prefix=Message_',
COMMENT 'Save=message_send_at;message_sent_at;message_response_code;message_last_send_at;message_last_sent_at;message_last_response_code',
COMMENT 'Security=INVOKER';
Organisations
table base_organisations
{
@ORG_ID: Int^
@org_guid: Guid
@org_name: Text
@org_code: Text
%Filter = ORG_ID
%Module = baseschema
%Output = mysql
%OrderBy = org_name
%Prefix = org_
%Security = INVOKER
}
DROP TABLE base_organisations;
CREATE TABLE base_organisations
(
ORG_ID INT AUTO_INCREMENT,
org_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
org_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
org_deleted DATETIME DEFAULT 0,
org_name TEXT DEFAULT '',
PRIMARY KEY (ORG_ID)
)
COMMENT 'Authorised=USER',
COMMENT 'Filter=ORG_ID;org_name',
COMMENT 'Save=org_name'
COMMENT 'OrderBy=org_name';
table base_organisations_users
{
@USER: Int*
@ORG_ID: Int*
@ORG_USER_ID: Int^
@org_user_role: Text
%Filter = USER;ORG_ID;ORG_USER_ID
%Module = baseschema
%Output = mysql
%OrderBy = ORG_USER_ID
%Prefix = org_user_
%Security = INVOKER
}
DROP TABLE base_organisations_users;
CREATE TABLE base_organisations_users
(
ORG_ID INT NOT NULL,
ORG_USER_ID INT NOT NULL,
org_user_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
org_user_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
org_user_deleted TIMESTAMP DEFAULT 0,
org_user_role TEXT,
PRIMARY KEY (ORG_ID,ORG_USER_ID)
);
table base_presets
{
@PRESET_ID: Int^
@preset_code: Text[50]
@preset_name: Text
@preset_description: Text
@preset_type: Text[50]
@preset_allow_override: Bool
%Filter = PRESET_ID
%Module = baseschema
%Output = mysql
%OrderBy = preset_code
%Prefix = preset_
%Security = INVOKER
}
table base_presets_values
{
@PRESET_ID: Int*
@PRESET_VALUE_ID: Int^
@preset_value: Text
@preset_value_default: Bool
%Filter = PRESET_ID;PRESET_VALUE_ID
%Module = baseschema
%Output = mysql
%OrderBy = preset_value_default+DESC,preset_value
%Prefix = preset_value_
%Security = INVOKER
}
table base_organisations_presets
{
@ORG_ID: Int*
@PRESET_ID: Int*
@PRESET_VALUE_ID: Int*
@ORG_PRESET_ID: Int^
@org_preset_override_value: Text
%Filter = ORG_ID;ORG_PRESET_ID
%Module = baseschema
%Output = mysql
%OrderBy = ORG_ID,ORG_PRESET_ID
%Prefix = org_preset_
%Security = INVOKER
%View = view_base_organisations_presets
}
Payments
DROP TABLE base_payments_credit_cards;
CREATE TABLE base_payments_credit_cards (
USER INT(11),
provided DATETIME,
final_four CHAR(4),
month TEXT,
year TEXT,
nonce TEXT,
token CHAR(16),
processed DATETIME NOT NULL DEFAULT 0,
PRIMARY KEY (USER,final_four)
);
DROP TABLE base_payments_customers;
CREATE TABLE base_payments_customers (
USER INT(11),
created DATETIME,
customer_id CHAR(16) NOT NULL DEFAULT '',
PRIMARY KEY (USER)
);
DROP TABLE base_payments_details;
CREATE TABLE base_payments_details (
USER INT(11),
given_name CHAR(99),
family_name CHAR(99),
address CHAR(99),
address2 CHAR(99),
suburb CHAR(99),
state CHAR(99),
country CHAR(99),
postcode CHAR(5),
PRIMARY KEY (USER)
);
DROP TABLE base_payments_invoices;
CREATE TABLE base_payments_invoices (
INVOICE INT(11) AUTO_INCREMENT,
USER INT(11),
raised DATE,
currency CHAR(16),
amount DECIMAL(13,2),
gst DECIMAL(13,2),
total DECIMAL(13,2),
paid DECIMAL(13,2),
transacted DATETIME,
PRIMARY KEY (INVOICE), UNIQUE KEY (USER,raised)
);
DROP TABLE base_payments_plans;
CREATE TABLE base_payments_plans
(
PLAN_ID INT(11) NOT NULL AUTO_INCREMENT,
id CHAR(99) NOT NULL DEFAULT '',
billingDayOfMonth INT(11),
billingFrequency TEXT,
currencyIsoCode TEXT,
description TEXT,
name TEXT,
numberOfBillingCycles TEXT,
price TEXT,
trialDuration INT(3),
trialDurationUnit TEXT,
trialPeriod TEXT,
createdAt DATETIME,
updatedAt DATETIME,
PRIMARY KEY (PLAN_ID)
);
DROP TABLE base_payments_purchases;
CREATE TABLE base_payments_purchases (
PURCHASE INT(11) AUTO_INCREMENT,
USER INT(11),
purchased DATETIME,
description CHAR(99),
cost DECIMAL(13,2),
transaction_id CHAR(16),
transacted DATETIME NOT NULL DEFAULT 0,
PRIMARY KEY (PURCHASE)
);
DROP TABLE base_payments_remove_cards;
CREATE TABLE base_payments_remove_cards (
USER INT(11),
customer_id CHAR(16),
PRIMARY KEY (USER)
);
DROP TABLE base_payments_transactions;
CREATE TABLE base_payments_transactions (
TRANSACTION INT(11) AUTO_INCREMENT,
USER INT(11),
transaction_id CHAR(16),
description CHAR(99),
date DATETIME,
type CHAR(50),
status CHAR(50),
payment_method_token CHAR(16),
amount DECIMAL(13,2),
PRIMARY KEY (TRANSACTION), UNIQUE KEY (USER,transaction_id)
);
Places
The 'base_places' table stores information related to a geospatial address to google place identifier lookup. If the Javascript Google Places API is being used, the initial tuple creation may contain all pertinant information; otherwise an asychronous job will use the Google Places API to retrive the appropriate possible suggestions.
Later, a person may confirm which of the suggestions should be matched with the original input by setting the 'CONFIRMED_PLACE_SUGGESTION_ID' to match the 'PLACE_SUGGESTION_ID' of the appropriate suggestion.
'OWNER' is an opaque key whose value is determined by the user.
table base_places
{
@GROUP_ID: Int*
@PLACE_ID: Int^
@input: Text
@suggested_google_place_id: Text
@floor: Text[10]
@street_number: Text[10]
@street: Text[99]
@suburb: Text[99]
@city: Text[99]
@state: Text[99]
@country: Text[99]
@postal_code: Text[10]
@latitude: Geo
@longitude: Geo
@place_processed: Datetime
@place_geocoded: Datetime
@place_confirmed: Datetime
@place_error: Text
@CONFIRMED_PLACE_SUGGESTION_ID: Int
%Filter = GROUP_ID;PLACE_ID;input
%Module = baseschema
%Output = mysql
%OrderBy = PLACE_ID
%Prefix = place_
%Security = INVOKER
}
DROP TABLE base_places;
CREATE TABLE base_places
(
PLACE_ID INT AUTO_INCREMENT,
GROUP_ID INT DEFAULT 0,
input TEXT,
suggested_google_place_id TEXT,
CONFIRMED_PLACE_SUGGESTION_ID INT DEFAULT 0,
floor CHAR(10) DEFAULT '',
street_number CHAR(10) DEFAULT '',
street CHAR(99) DEFAULT '',
suburb CHAR(99) DEFAULT '',
city CHAR(99) DEFAULT '',
state CHAR(99) DEFAULT '',
country CHAR(99) DEFAULT '',
postal_code CHAR(10) DEFAULT '',
latitude DECIMAL(10,7) DEFAULT 0.0,
longitude DECIMAL(10,7) DEFAULT 0.0,
place_created DATETIME DEFAULT 0,
place_processed DATETIME DEFAULT 0,
place_geocoded DATETIME DEFAULT 0,
place_confirmed DATETIME DEFAULT 0,
place_error TEXT,
PRIMARY KEY (PLACE_ID)
);
ALTER TABLE base_places ADD place_geocoded DATETIME DEFAULT 0 AFTER place_processed;
table base_places_routes
{
@GROUP_ID: Int*
@PLACE_OGN_ID: Int*
@PLACE_DST_ID: Int*
@ROUTE_ID: Int^
@route_processed: Datetime
@route_distance_metres: Int
@route_duration_seconds: Int
@route_shortest_distance_metres: Int
@route_shortest_duration_seconds: Int
%Filter = GROUP_ID;PLACE_OGN_ID;PLACE_DST_ID;ROUTE_ID
%Module = baseschema
%Output = mysql
%OrderBy = ROUTE_ID
%Prefix = route_
%Security = INVOKER
}
DROP TABLE base_places_routes;
CREATE TABLE base_places_routes
(
ROUTE_ID INT AUTO_INCREMENT,
GROUP_ID INT DEFAULT 0,
PLACE_OGN_ID INT DEFAULT 0,
PLACE_DST_ID INT DEFAULT 0,
route_created DATETIME DEFAULT 0,
route_processed DATETIME DEFAULT 0,
route_distance_metres INT DEFAULT 0,
route_duration_seconds INT DEFAULT 0,
route_shortest_distance_metres INT DEFAULT 0,
route_shortest_duration_seconds INT DEFAULT 0,
PRIMARY KEY (ROUTE_ID)
);
table base_places_suggestions
{
@GROUP_ID: Int*
@PLACE_ID: Int*
@PLACE_SUGGESTION_ID: Int^
@google_place_id: Text
@description: Text
@main_text: Text
@secondary_text: Text
@types: Text
@suggestion_type: Text
@suggestion_floor: Text[20]
@suggestion_street_number: Text[10]
@suggestion_street: Text[99]
@suggestion_suburb: Text[99]
@suggestion_city: Text[99]
@suggestion_state: Text[99]
@suggestion_country: Text[99]
@suggestion_postal_code: Text[10]
@suggestion_latitude: Coordinate
@suggestion_longitude: Coordinate
%Filter = GROUP_ID;PLACE_ID;PLACE_SUGGESTION_ID
%Module = baseschema
%Output = mysql
%OrderBy = PLACE_SUGGESTION_ID
%Prefix = suggestion_
%Security = INVOKER
}
DROP TABLE base_places_suggestions;
CREATE TABLE base_places_suggestions
(
PLACE_SUGGESTION_ID INT AUTO_INCREMENT,
PLACE_ID INT NOT NULL,
GROUP_ID INT DEFAULT 0,
google_place_id TEXT,
description TEXT,
main_text TEXT,
secondary_text TEXT,
types TEXT,
suggestion_type ENUM( '', 'GEOCODE', 'AUTOCORRECT'),
suggestion_created DATETIME DEFAULT 0,
suggestion_floor CHAR(20) DEFAULT '',
suggestion_street_number CHAR(10) DEFAULT '',
suggestion_street CHAR(99) DEFAULT '',
suggestion_suburb CHAR(99) DEFAULT '',
suggestion_city CHAR(99) DEFAULT '',
suggestion_state CHAR(99) DEFAULT '',
suggestion_country CHAR(99) DEFAULT '',
suggestion_postal_code CHAR(10) DEFAULT '',
suggestion_latitude DECIMAL(10,7) DEFAULT 0.0,
suggestion_longitude DECIMAL(10,7) DEFAULT 0.0,
PRIMARY KEY (PLACE_SUGGESTION_ID)
);
Pre-registrations
The pre-registrations table is used to store visitor signup information on a launch page.
table base_preregistrations
{
@PREREGISTRATION_ID: Int^
@name: Text[99]
@email: Text[99]
@mobile: Text[20]
@info: Text
@token: Text
@created: Datetime
@sent: Datetime
@confirmed: Datetime
%Filter = PREREGISTRATION_ID;email
%Module = baseschema
%Output = mysql
%OrderBy = PREREGISTRATION_ID;email
%Prefix = preregistration_
%Security = INVOKER
}
DROP TABLE base_preregistrations;
CREATE TABLE base_preregistrations
(
name CHAR(99),
email CHAR(99),
mobile CHAR(20),
info TEXT,
token TEXT,
created DATETIME,
sent DATETIME,
confirmed DATETIME,
PRIMARY KEY (email)
);
Process List
table base_processlist
{
@PROCESS_ID: Int^
@process_user: Text[32]
@process_host: Text[64]
@process_db: Text[64]
@process_command: Text[16]
@process_time: Int
@process_state: Text[64]
@process_info: Longtext
%Filter = PROCESS_ID
%Module = minutelogger
%OrderBy = PROCESS_ID
%Prefix = process_
%Security = INVOKER
}
Procedures
table base_procedures_check_log
{
@CHECK_LOG_ID: Int^
@check_log_checked_on: Date
@check_log_checked_by: Text
@check_log_procedure_name: Text
@check_log_comment: Text
%Filter = CHECK_LOG_ID;check_log_procedure_name
%Module = base
%Output = mysql
%OrderBy = check_log_procedure_name
%Prefix = check_log_
%Security = INVOKER
}
Projects
table base_projects
{
@USER: Int*
@ORG_ID: Int*
@GROUP_ID: Int*
@PROJECT_ID: Int^
@project_guid: Guid
@project_name: Text
@project_code: Text[50]
%Filter = ORG_ID;GROUP_ID;PROJECT_ID;project_guid
%Module = baseschema
%Output = mysql
%OrderBy = project_name
%Prefix = project_
%Security = INVOKER
}
DROP TABLE base_projects;
CREATE TABLE base_projects
(
USER INT NOT NULL,
ORG_ID INT NOT NULL,
GROUP_ID INT NOT NULL,
PROJECT_ID INT AUTO_INCREMENT,
project_guid CHAR(36) DEFAULT '',
project_name TEXT,
project_code CHAR(50) DEFAULT '',
PRIMARY KEY (PROJECT_ID)
);
table base_projects_users
{
@USER: Int*
@ORG_ID: Int*
@GROUP_ID: Int*
@PROJECT_ID: Int*
@PROJECT_USER_ID: Int^
@project_user_guid: Guid
@project_roles: Text
%Filter = USER;ORG_ID;GROUP_ID;PROJECT_ID;PROJECT_USER_ID;project_user_guid
%Module = baseschema
%Output = mysql
%OrderBy = PROJECT_USER_ID
%Prefix = project_user_
%Security = INVOKER
}
DROP TABLE base_projects_users;
CREATE TABLE base_projects_users
(
USER INT NOT NULL,
ORG_ID INT NOT NULL,
GROUP_ID INT NOT NULL,
PROJECT_ID INT NOT NULL,
PROJECT_USER_ID INT AUTO_INCREMENT,
project_user_guid CHAR(36) DEFAULT '',
project_roles TEXT,
PRIMARY KEY (PROJECT_USER_ID),
UNIQUE KEY (USER,ORG_ID,GROUP_ID,PROJECT_ID)
);
Settings
Settings are used to store admin settings
table base_settings
{
@SETTING_ID: Int^
@setting_name: Text[50]
@setting_value: Text
%Filter = SETTING_ID
%Module = baseschema
%Output = mysql
%OrderBy = setting_name
%Prefix = setting_
%Security = INVOKER
}
CREATE TABLE base_settings
(
SETTING_ID INT AUTO_INCREMENT,
setting_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
setting_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
setting_deleted TIMESTAMP DEFAULT 0,
setting_name CHAR(50) NOT NULL,
setting_value TEXT,
PRIMARY KEY (SETTING_ID)
);
Templates
Templates are used for transactional emails and generated reports.
table base_templates
{
@TEMPLATE_ID: Int^
@template_name: Text[50]
@template_txt64: LongText
@template_htm64: LongText
%Filter = TEMPLATE_ID
%Module = baseschema
%Output = mysql
%OrderBy = template_name
%Prefix = template_
%Security = INVOKER
}
CREATE TABLE base_templates
(
TEMPLATE_ID INT AUTO_INCREMENT,
template_created DATETIME NOT NULL,
template_name CHAR(50) NOT NULL,
template_txt64 TEXT,
template_htm64 TEXT,
#USER INT DEFAULT 0,
#GROUP INT DEFAULT 0,
PRIMARY KEY (TEMPLATE_ID)
);
Tests
table base_tests
{
@TEST_ID: Int^
@test_module: Text
@test_nr: Int
@test_name: Text
@test_type: Text[10]
@test_successful: Bool
@test_value: Text
@test_message: Text
%Filter = TEST_ID
%Module = util
%Output = mysql
%OrderBy = TEST_ID
%Prefix = test_
%Security = INVOKER
}
Users
Most account information is stored in the 'base_users' table, including the person's name and password, as well as primary email and mobile phone. The 'base_users_uids' table is used to associate a user with an account type -- potentially in the future this table will allow a user to have multiple account types. The 'base_users_sessions' table stores information related to user sessions such as a session id and its expiry. The 'base_users_tokens' table is used to store authorisation tokens, such as activation tokens, that are sent to the user to allow them to perform some action, such as authorising an account. The 'base_users_termination_schedule' table is used to manage the process of permanently deleting an account.
table base_users
{
@USER: Int^
@email: Text[99]
@email_provisional: Text[99]
@mobile: Text[30]
@mobile_provisional: Text[30]
@created: Datetime
@last_login: Datetime
@invalid_logins: Int
@user_mfa_type: Text[64]
@user_salt: Text[64]
@user_hash: Text[64]
@password_hash: Text[64]
@user_status: Text[20]
@send_confirmation: Bool
@sent: Bool
@confirmation_sent: Datetime
@confirmed: Datetime
@DELETED_BY: Int
@given_name: Text[50]
@family_name: Text[50]
@visits: Int
@ts_users: Datetime
%Filter = USER
%Module = baseschema
%Output = mysql
%OrderBy = given_name,family_name
%Prefix = user_
%Security = INVOKER
}
DROP TABLE base_users;
CREATE TABLE base_users
(
USER INT NOT NULL,
email CHAR(99) NOT NULL,
email_provisional CHAR(99) NOT NULL DEFAULT '',
mobile CHAR(30) NOT NULL DEFAULT 0,
mobile_provisional CHAR(30) NOT NULL DEFAULT 0,
created DATETIME NOT NULL,
last_login DATETIME NOT NULL DEFAULT 0,
invalid_logins INT NOT NULL DEFAULT 0,
user_salt CHAR(64) NOT NULL,
user_hash CHAR(64) NOT NULL,
password_hash CHAR(64) NOT NULL,
user_status CHAR(20) NOT NULL,
send_confirmation BOOL NOT NULL DEFAULT 0,
sent BOOL NOT NULL DEFAULT 0,
confirmation_sent DATETIME NOT NULL DEFAULT 0,
confirmed DATETIME NOT NULL DEFAULT 0,
user_deleted DATETIME NOT NULL DEFAULT 0,
DELETED_BY INT NOT NULL DEFAULT 0,
given_name CHAR(50) NOT NULL,
family_name CHAR(50) NOT NULL,
visits INT NOT NULL DEFAULT 1,
ts_users TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (email), UNIQUE KEY (USER)
);
ALTER TABLE base_users ADD COLUMN mobile CHAR(30) NOT NULL DEFAULT '' AFTER email_provisional; ALTER TABLE base_users ADD COLUMN mobile_provisional CHAR(30) NOT NULL DEFAULT '' AFTER mobile;
table base_users_alternate_emails
{
@USER: Int*
@TOKEN_ID: Int*
@ALTERNATE_EMAIL_ID: Int^
@email: Text[99]
@email_verified: Bool
%Filter = USER;ALTERNATE_EMAIL_ID
%Module = baseschema
%Output = mysql
%OrderBy = email
%Prefix = alternative_email_
%Security = INVOKER
}
DROP TABLE base_users_alternate_emails;
CREATE TABLE base_users_alternate_emails
(
USER INT NOT NULL,
TOKEN_ID INT NOT NULL,
email CHAR(99) NOT NULL,
email_verified BOOL NOT NULL DEFAULT 0,
PRIMARY KEY (USER,email)
);
table base_users_device_logins
{
@USER: Int*
@USER_DEVICE_LOGIN_ID: Int^
@user_device_login_guid: Guid
@user_device_login_salt: Text
@user_device_login_hash: Text
%Filter = USER_DEVICE_LOGIN_ID
%Module = baseschema
%Output = mysql
%OrderBy = USER_DEVICE_LOGIN_ID
%Prefix = user_device_login_
%Security = INVOKER
}
DROP TABLE base_users_device_logins;
CREATE TABLE base_users_device_logins
(
USER INT NOT NULL,
USER_DEVICE_LOGIN_ID INT AUTO_INCREMENT,
user_device_login_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
user_device_login_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
user_device_login_deleted TIMESTAMP DEFAULT 0,
user_device_login_guid CHAR(36) NOT NULL,
user_device_login_salt TEXT NOT NULL,
user_device_login_hash TEXT NOT NULL,
PRIMARY KEY(USER_DEVICE_LOGIN_ID)
)
COMMENT 'Authorised=ADMIN',
COMMENT 'Filter=USER_DEVICE_LOGIN_ID',
COMMENT 'OrderBy=USER_DEVICE_LOGIN_ID',
COMMENT 'Prefix=User_Device_Login_',
COMMENT 'Save=',
COMMENT 'Security=INVOKER';
table base_users_mobiles
{
@USER: Int
@USER_MOBILE_ID: Int^
@user_mobile: Text[20]
@user_mobile_device_id: Text[255]
@user_mobile_salt: Text[64]
@user_mobile_hash: Text[64]
%Filter = USER
%Module = baseschema
%Output = mysql
%OrderBy = user_mobile
%Prefix = user_mobile_
%Security = INVOKER
}
DROP TABLE base_users_mobiles;
CREATE TABLE base_users_mobiles
(
USER INT NOT NULL,
user_mobile CHAR(20) NOT NULL,
user_mobile_device_id CHAR(255) DEFAULT '',
user_mobile_salt CHAR(64) DEFAULT '',
user_mobile_hash CHAR(64) DEFAULT '',
PRIMARY KEY (USER, user_mobile)
);
table base_users_invalid
{
@INVALID_ID: Int^
@email: Text[99]
@invalid_logins: Int
@invalid_sent: Datetime
%Filter = INVALID_ID;email
%Module = baseschema
%Output = mysql
%OrderBy = email
%Prefix = invalid_
%Security = INVOKER
}
DROP TABLE base_users_invalid;
CREATE TABLE base_users_invalid
(
email CHAR(99) NOT NULL,
invalid_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
invalid_logins INT NOT NULL,
invalid_sent DATETIME DEFAULT 0,
PRIMARY KEY (email)
);
table base_users_mobiles_enrolments
{
@USER: Int*
@USER_MOBILE_ENROLMENT_ID: Int^
@user_mobile: Text[20]
@user_mobile_device_id: Text[255]
@sms_code: Text[20]
@sms_code_created: Datetime
@sms_code_sent: Datetime
@sms_code_verified: Datetime
%Filter = USER_MOBILE_ENROLMENT_ID
%Module = baseschema
%Output = mysql
%OrderBy = USER_MOBILE_ENROLMENT_ID
%Prefix = user_mobile_enrolment_
%Security = INVOKER
}
DROP TABLE base_users_mobiles_enrolments;
CREATE TABLE base_users_mobiles_enrolments
(
USER INT NOT NULL,
user_mobile CHAR(20) NOT NULL,
user_mobile_device_id CHAR(255) NOT NULL,
sms_code CHAR(20) NOT NULL,
sms_code_created DATETIME DEFAULT 0,
sms_code_sent DATETIME DEFAULT 0,
sms_code_verified DATETIME DEFAULT 0,
PRIMARY KEY (USER, user_mobile, user_mobile_device_id)
);
table base_users_second_factors
{
@USER: Int*
@SECOND_FACTOR_ID: Int^
@second_factor_confirmed: Datetime
@second_factor_type: Text[64]
@second_factor_email: Text
@second_factor_sms: Text
@second_factor_challenge: Text
%Filter = USER;SECOND_FACTOR_ID
%Module = baseschema
%Output = mysql
%OrderBy = SECOND_FACTOR_ID
%Prefix = second_factor_
%Security = INVOKER
}
table base_users_sessions
{
@SESSION_ID: Int^
@sid: Text[64]
@csrf: Text[64]
@AUTH_USER: Int
@email: Text[99]
@group_code: Text[50]
@created: Datetime
@enabled: Datetime
@updated: Datetime
@expiry: Int
@ip_address: Text
@second_factor_code: Text
%Filter = SESSION_ID;sid
%Module = baseschema
%Output = mysql
%OrderBy = SESSION_ID
%Prefix = session_
%Security = INVOKER
}
DROP TABLE base_users_sessions;
CREATE TABLE base_users_sessions
(
sid CHAR(64) NOT NULL,
csrf CHAR(64) NOT NULL,
AUTH_USER INT NOT NULL,
email CHAR(99) NOT NULL,
group_code CHAR(50) NOT NULL,
created DATETIME NOT NULL,
updated DATETIME NOT NULL,
expiry INT(64) NOT NULL,
PRIMARY KEY (sid)
);
table base_users_sessions_log
{
@SESSION_LOG_ID: Int^
@sid: Text[64]
@AUTH_USER: Int
@email: Text[99]
@group_code: Text[50]
@created: Datetime
@updated: Datetime
@expiry: Int
@ip_address: Text
%Filter = SESSION_LOG_ID
%Module = baseschema
%Output = mysql
%OrderBy = SESSION_LOG_ID
%Prefix = session_log_
%Security = INVOKER
}
DROP TABLE base_users_sessions_log;
CREATE TABLE base_users_sessions_log
(
sid CHAR(64) NOT NULL,
AUTH_USER INT NOT NULL,
email CHAR(99) NOT NULL,
group_code CHAR(50) NOT NULL,
created DATETIME NOT NULL,
updated DATETIME NOT NULL,
expiry INT(64) NOT NULL,
PRIMARY KEY (sid)
);
table base_users_termination_schedule
{
@USER: Int*
@TERMINATION_ID: Int^
@mark: Datetime
@time_of_termination: Datetime
%Filter = USER;TERMINATION_ID
%Module = baseschema
%Output = mysql
%OrderBy = TERMINATION_ID
%Prefix = termination_
%Security = INVOKER
}
DROP TABLE base_users_termination_schedule;
CREATE TABLE base_users_termination_schedule
(
USER INT NOT NULL,
mark DATETIME NOT NULL,
time_of_termination DATETIME NOT NULL,
PRIMARY KEY (USER)
);
table base_users_tokens
{
@USER: Int*
@TOKEN_ID: Int^
@token_expiry_days: Int
@token_expiry: Datetime
@token_type: Text[50]
@token: Text[64]
@token_sent: Datetime
@token_used: Datetime
%Filter = USER;TOKEN_ID;token
%Module = baseschema
%Output = mysql
%OrderBy = TOKEN_ID
%Prefix = token_
%Security = INVOKER
}
DROP TABLE base_users_tokens;
CREATE TABLE base_users_tokens
(
TOKEN_ID INT AUTO_INCREMENT,
USER INT NOT NULL,
token_created DATETIME NOT NULL,
token_expiry_days INT DEFAULT 0,
token_expiry DATETIME DEFAULT 0,
token_type CHAR(50) NOT NULL,
token CHAR(64) NOT NULL,
token_sent DATETIME DEFAULT 0,
token_used DATETIME DEFAULT 0,
PRIMARY KEY (TOKEN_ID)
);
table base_users_uids
{
@USER: Int^
@type: Text[50]
%Filter = USER
%Module = baseschema
%Output = mysql
%OrderBy = USER
%Prefix = user_uid_
%Security = INVOKER
}
DROP TABLE base_users_uids;
CREATE TABLE base_users_uids
(
USER INT NOT NULL AUTO_INCREMENT,
type CHAR(50) NOT NULL DEFAULT '',
PRIMARY KEY (USER)
);
table base_web_connections
{
@CONNECTION_ID: Int^
@connection_server_name: Text[99]
@connection_ip_remote: Text[45]
@connection_csrf_token: Text[64]
@connection_csrf_expiry: Datetime
%Filter = CONNECTION_ID
%Module = baseschema
%Output = mysql
%OrderBy = CONNECTION_ID
%Prefix = connection_
%Security = INVOKER
}
DROP TABLE base_web_connections;
CREATE TABLE base_web_connections
(
CONNECTION_ID INT NOT NULL AUTO_INCREMENT,
connection_server_name CHAR(99) NOT NULL,
connection_ip_remote CHAR(45) NOT NULL,
connection_csrf_expiry DATETIME NOT NULL,
connection_csrf_token CHAR(64) NOT NULL,
PRIMARY KEY (CONNECTION_ID),
UNIQUE KEY (connection_server_name,connection_ip_remote,connection_csrf_expiry)
);
table base_proc_check
{
@CHECK_ID: Int^
@name: Text[64]
@sid: Bool
@apikey: Bool
@success: Bool
@raised_exception: Bool
@exception: Text
%Filter = CHECK_ID
%Module = baseschema
%Output = mysql
%OrderBy = CHECK_ID
%Prefix = check_
%Security = INVOKER
}
DROP TABLE base_proc_check;
CREATE TABLE base_proc_check
(
CHECK_ID INT AUTO_INCREMENT,
name CHAR(64) DEFAULT '',
sid BOOL DEFAULT 0,
apikey BOOL DEFAULT 0,
success BOOL DEFAULT 0,
raised_exception BOOL DEFAULT 0,
exception TEXT DEFAULT '',
PRIMARY KEY (CHECK_ID), UNIQUE KEY(name)
);