TracCloudTables: Difference between revisions

From Redrock Wiki

No edit summary
No edit summary
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{WIP}}
{{WIP}}
 
<div class="tcWidgetPage">
==TracCloud Table List==
==TracCloud Table List==


Line 6: Line 6:


<hr>
<hr>
<div style="float: left; margin-top: 0em; margin-bottom: 1em"><big><b>SurveyTrac Related</b></big></div><div class="mw-collapsible mw-collapsed">
'''Types'''
{| class="wikitable" border="1" style="margin:auto"
!Type!!Description
|-
|varchar(#)||Alphanumeric field with a specified maximum length.
|-
|int||Integer, maximum value of about 4 billion, or a 10-digit numeric value.
|-
|bigint||Big integer, maximum value of about 9 quintillion, or a 16-digit numeric value.
|-
|tinyint||Tiny integer, typically used to store a 1 or 0 as true or false.
|-
|date||Date, formatted as YYYY-MM-DD
|-
|time||A specific time using a 24-hour format (HH:MM:SS), e.g., 13:45:00
|-
|datetime||Date and time, YYYY-MM-DD HH:MM:SS
|-
|double||Decimal # with up to 12 digits precision
|-
|json||JSON-formatted field that contains multiple fields and values (e.g., custom fields).
|}
 
<hr>
<div style="float: left; margin-top: 0em; margin-bottom: 1em"><big><b>SurveyTrac Related</b> (Surveys, SrvyQuestions, Responses, SrvyRespAnswers)</big></div><div class="mw-collapsible mw-collapsed">
<br><br>
<br><br>


Line 212: Line 236:
|VisitDay||varchar(20)
|VisitDay||varchar(20)
|}
|}
</div>
<hr>
<div style="float: left; margin-top: 0em; margin-bottom: 1em"><big><b>Students</b></big></div><div class="mw-collapsible mw-collapsed">
<br><br>
{| class="wikitable"
|+ Students
|-
! Column !! Type !! Notes
|-
|Sequence||bigint AI PK ||Static identifier for students, unrelated to ID.
|-
|UUID||varchar(36) ||
|-
|ID||bigint ||
|-
|Other_ID||varchar(40) ||
|-
|Other_ID2||varchar(40) ||
|-
|Barcode||varchar(80) ||
|-
|Status||varchar(80) ||
|-
|OtherStatus||varchar(80) ||
|-
|Legal_First||varchar(80) ||
|-
|First_Name||varchar(80) ||
|-
|Last_Name||varchar(80) ||
|-
|Middle||varchar(80) ||
|-
|Street||varchar(250) ||
|-
|Apt||varchar(80) ||
|-
|City||varchar(120) ||
|-
|State||varchar(80) ||
|-
|Zip||varchar(20) ||
|-
|Country||varchar(80) ||
|-
|Home_Phone||varchar(80) ||
|-
|Work_Phone||varchar(80) ||
|-
|Cell_Phone||varchar(80) ||
|-
|Email||varchar(120) ||
|-
|Preferred||varchar(80) ||
|-
|passhash||varchar(128) || Unused.
|-
|Attempts||bigint ||
|-
|resetCode||varchar(80) ||
|-
|lockedOut||int ||
|-
|TextAddr||varchar(120) ||
|-
|LastDateIn||date ||
|-
|Notes||text ||
|-
|UserName||varchar(120) ||
|-
|Password||varchar(128) || Unused.
|-
|Prefs||text ||
|-
|Birthdate||date ||
|-
|Gender||varchar(80) ||
|-
|Ethnicity||varchar(150) ||
|-
|Major||varchar(120) ||
|-
|Class||varchar(80) ||
|-
|DegreeGoal||varchar(80) ||
|-
|Cohort||varchar(80) ||
|-
|College||varchar(80) ||
|-
|Grad_Und||varchar(80) ||
|-
|DateStarted||date ||
|-
|DateWithdrawn||date ||
|-
|ReasonWithdrawn||varchar(128) ||
|-
|GradDate||date ||
|-
|GradDegree||varchar(128) ||
|-
|GPA||double ||
|-
|AccumHours||double ||
|-
|PrimaryConsultantID||bigint || Relates to Staff.Sequence.
|-
|FlagText||varchar(80) ||
|-
|CustomData||json ||
|-
|WatchLists||json ||
|-
|CreatedDT||timestamp ||
|-
|ModifiedDT||timestamp ||
|-
|Fund||varchar(80) ||
|-
|gen_BIOConfirmed||varchar(2) ||
|-
|gen_BIOConfirmedDT||varchar(20) ||
|-
|Pronouns||varchar(80) ||
|-
|Full_Name||varchar(255) ||
|-
|Full_Name2||varchar(255) ||
|-
|_LastImportedDT||datetime ||
|-
|PassLastDateChanged||date ||
|-
|PassFailedAttempts||int ||
|-
|AccountLocked||tinyint ||
|-
|AccountLockDateTime||datetime
|}
</div>
<hr>
<div style="float: left; margin-top: 0em; margin-bottom: 1em"><big><b>Subject Related</b> (Courses, Sections, Terms, Registrations, Faculty, SectionSchedule)</big></div><div class="mw-collapsible mw-collapsed">
<br><br>
{| class="wikitable"
|+ Courses
|-
! Column !! Type !! Notes
|-
|Sequence||bigint AI PK ||Unique identifier for each course.
|-
|Subject||varchar(40) ||
|-
|Course||varchar(20) ||
|-
|Title||varchar(120) ||
|-
|ExcludeFromCharts||tinyint ||
|-
|Dept||varchar(40) ||
|-
|SubjectCourse||varchar(200) ||
|-
|_LastImportedDT||datetime ||
|-
|CustomData||json||
|}
{| class="wikitable"
|+ Sections
|-
! Column !! Type !! Notes
|-
|Sequence||bigint AI PK ||Unique identifier for each section.
|-
|Inactive||tinyint ||
|-
|Code||varchar(80) ||
|-
|CourseID||bigint || Relates to Courses.Sequence
|-
|GradeGroupID||bigint ||
|-
|CRN||varchar(80) ||
|-
|TermID||bigint || Relates to Terms.Sequence
|-
|FacultyID||bigint || Relates to Faculty.Sequence
|-
|nonEnrolled||tinyint ||
|-
|CustomData||json ||
|-
|CreatedDT||timestamp ||
|-
|ignoreRegImportDeactivate||tinyint ||
|-
|Credits||double ||
|-
|_LastImportedDT||datetime ||
|-
|Title||varchar(120)||
|}
{| class="wikitable"
|+ Terms
|-
! Column !! Type !! Notes
|-
|Sequence||bigint AI PK ||Unique identifier for terms.
|-
|Active||tinyint ||
|-
|TermCode||varchar(80) ||
|-
|OrderIdx||int ||
|-
|ActiveFrom||date ||
|-
|ActiveTo||date ||
|-
|CustomData||json|| Unused.
|}
{| class="wikitable"
|+ Registrations
|-
! Column !! Type !! Notes
|-
|Sequence||bigint AI PK ||Unique identifier for registrations.
|-
|Inactive||tinyint ||
|-
|OverrideDate||tinyint ||
|-
|SectionID||bigint ||Relates to Sections.Sequence.
|-
|StudentID||bigint ||Relates to Students.Sequence (not Students.ID).
|-
|Grade||varchar(40) ||
|-
|ValidFromDT||datetime ||
|-
|ValidToDT||datetime ||
|-
|Notes||text ||
|-
|CustomData||json ||
|-
|_LastImportedDT||datetime ||
|-
|RegStatus||varchar(40) ||
|-
|EarnedCredits||double ||
|}
{| class="wikitable"
|+ Faculty
|-
! Column !! Type !! Notes
|-
|Sequence||bigint AI PK ||
|-
|UUID||varchar(36) ||
|-
|FirstName||varchar(80) ||
|-
|LastName||varchar(80) ||
|-
|Password||varchar(128) || Unused.
|-
|UserID||varchar(80) ||
|-
|UserName||varchar(80) ||
|-
|Custom2||text ||
|-
|Custom1||text ||
|-
|Salutation||varchar(40) ||
|-
|Department||varchar(60) ||
|-
|Inactive||tinyint ||
|-
|Phone||varchar(80) ||
|-
|Email||varchar(120) ||
|-
|CustomData||json ||
|-
|OtherID||varchar(80) ||
|-
|FullName||varchar(255) ||
|-
|facultyBIO||text ||
|-
|Pronouns||varchar(80) ||
|-
|_LastImportedDT||datetime ||
|-
|PassLastDateChanged||date ||
|-
|PassFailedAttempts||int ||
|-
|AccountLocked||tinyint ||
|-
|AccountLockDateTime||datetime||
|}
{| class="wikitable"
|+ SectionSchedule
|-
! Column !! Type !! Notes
|-
|Sequence||bigint AI PK ||
|-
|SectionID||bigint || Related to Sections.Sequence
|-
|Days||varchar(30) ||
|-
|Time||time ||
|-
|Duration||int ||
|-
|Description||text ||
|-
|OtherData||text ||
|-
|Campus||varchar(80) ||
|-
|Bldg||varchar(80) ||
|-
|Room||varchar(80) ||
|}
</div>
<hr>
<div style="float: left; margin-top: 0em; margin-bottom: 1em"><big><b>Profile Related</b> (Profiles, Centers, Groups, Reasons)</big></div><div class="mw-collapsible mw-collapsed">
<br><br>
{| class="wikitable"
|+ Profiles
|-
! Column !! Type !! Notes
|-
|Sequence||bigint AI PK ||
|-
|Type||int ||
|-
|Name||varchar(80) ||
|-
|Inactive||tinyint ||
|-
|ContactID||bigint ||
|-
|NameForConsultant||varchar(80) ||
|-
|CustomData||json ||
|-
|TimeZone||varchar(120) ||
|}
{| class="wikitable"
|+ Centers
|-
! Column !! Type !! Notes
|-
|Sequence|| bigint AI PK ||
|-
|Name|| varchar(80) ||
|-
|Prefs|| text ||
|-
|CourseListID|| bigint ||
|-
|fromEmail|| varchar(80) ||
|-
|ProfileType|| varchar(80) || Unused.
|-
|ProfileID|| bigint || Relates to Profiles.Sequence.
|-
|Inactive|| tinyint ||
|-
|PrimaryGroup|| bigint || Unused.
|-
|Settings|| json ||
|-
|OtherData|| json ||
|-
|UpdatedDT|| datetime ||
|-
|IsWritingCenter|| tinyint||
|}
{| class="wikitable"
|+ Reasons
|-
! Column !! Type !! Notes
|-
|Sequence|| bigint AI PK ||
|-
|ProfileID|| bigint || Relates to Profiles.Sequence.
|-
|Subcenters|| varchar(255) ||
|-
|Reason|| varchar(80) ||
|-
|Inactive|| tinyint ||
|-
|AutoLogTime|| int ||
|-
|AutoLogNow|| int ||
|-
|NotificationTime|| int ||
|-
|SnoozeTime|| int ||
|-
|isWork|| tinyint ||
|-
|isStaff|| tinyint ||
|-
|SortCode|| varchar(40) ||
|-
|NotForAppt|| tinyint ||
|-
|NotForLogin|| tinyint ||
|-
|NotForVisit|| tinyint ||
|-
|Category|| varchar(80) ||
|-
|CustomData|| json||
|}
{| class="wikitable"
|+ Groups
|-
! Column !! Type !! Notes
|-
|Sequence|| bigint AI PK ||
|-
|GroupName|| varchar(80) ||
|-
|ProfileID|| bigint || Relates to Profiles.Sequence.
|-
|Prefs|| json||
|}
</div>
</div>
<hr>
<hr>
Line 222: Line 699:


Staff
Staff
Students
Announcements
Appointments
Appointments
Assignments
Assignments
AutoEmailBlock
AutoEmails
AutoEmailsHasRefTypeEmailRecipients
AvailBlocks
AvailBlocks
AvailSlots
Centers
CenterStaff
Certifications
Certifications
CertTypes
CertTypes
Checkouts
Courses
cron_scripts
CustomData
CustomDataTypes
CustomDataTypes
Dates
DocTemplates
DocTypes
DocTypes
Documents
Documents
Events
Faculty
Favorites
GoalsTasks
GradeGroups
GradeGroups
Groups
Lists
Lists
NotAvail
Notifications
NotificationTypes
NotificationTypes
OutstandingApptRequests
OutstandingApptRequests
PayCodeRates
PayCodes
PayCodes
PotentialAssignments
PotentialAssignments
PotentialAssignmentsSections
ProfileExtraGroups
ProfileExtraGroups
Profiles
Programs
ProgsListsAssigned
ProgsListsAssigned
ProspectRequests
ProspectRequests
Reasons
Registrations
Reservations
Resources
Resources
ResourceTypes
ResourceTypes
SageAutomations
SageAutomations
SageEmailType
SageEventType
Sections
SectionSchedule
SpecialNeeds
SpecialNeeds
StudentSearchAvails
SystemLogs
Tasks
Tasks
TaskTriggers
TaskTypes
TaskTypes
Terms
TermsAndConditions
TermsAndConditions
WatchLists
WatchLists
WorkRecords
WorkTypes
WorkTypes


Line 308: Line 748:


==Resources==
==Resources==
Reservations
Checkouts
-->
-->
 
</div>
__NOTOC__
__NOTOC__

Latest revision as of 08:58, 10 July 2024

    This page is still in-progress! Come back later to learn more.


TracCloud Table List

This is a list of TracCloud data tables, primarily for reference in regards to API access.

Types

Type Description
varchar(#) Alphanumeric field with a specified maximum length.
int Integer, maximum value of about 4 billion, or a 10-digit numeric value.
bigint Big integer, maximum value of about 9 quintillion, or a 16-digit numeric value.
tinyint Tiny integer, typically used to store a 1 or 0 as true or false.
date Date, formatted as YYYY-MM-DD
time A specific time using a 24-hour format (HH:MM:SS), e.g., 13:45:00
datetime Date and time, YYYY-MM-DD HH:MM:SS
double Decimal # with up to 12 digits precision
json JSON-formatted field that contains multiple fields and values (e.g., custom fields).
SurveyTrac Related (Surveys, SrvyQuestions, Responses, SrvyRespAnswers)



Surveys
Column Type Notes
Sequence bigint AI PK Static identifier for each Survey.
Name varchar(80)
CreatedBy bigint Relates to Staff.Sequence.
Options json This field can be ignored for API purposes.
ProfileID bigint Relates to Profiles.Sequence.
CenterID bigint
AutoSend varchar(80) The chosen "Initiated Via" option.
Active tinyint
ActiveFrom date
ActiveTo date
Instructions text
useXML tinyint Unused.
Assessment tinyint Unused.
Function varchar(80) Unused.
Confirmation text
HeadText text
SrvyQuestions
Column Type Notes
Sequence bigint AI PK Static identifier for each Question, sequences are not reused even across multiple surveys.
Question text
SurveyID bigint Relates to Surveys.Sequence.
Points double
Answer varchar(80)
AnswerChoices text
Options json
LinkName varchar(80) Unused.
LinkURL varchar(80) Unused.
Type varchar(80)
Number int
CustomData json
Responses
Column Type Notes
Sequence bigint AI PK Static identifier for each response.
SurveyID bigint Relates to Surveys.Sequence.
linkedUID varchar(36)
SentByID varchar(36)
StudentID bigint Relates to Students.Sequence (not Students.ID).
SentBy varchar(80)
SentDate datetime
AnsweredDate datetime
StaffID bigint Relates to Staff.Sequence.
FacultyID bigint Relates to Faculty.Sequence.
SentManually tinyint
CustomData json
SrvyRespAnswers
Column Type Notes
Sequence bigint AI PK Static identifier for each question response.
QuestionID bigint Relates to SrvyQuestions.Sequence.
ResponseID bigint Relates to Responses.Sequence.
aAnswer varchar(80)
nAnswer double
tAnswer text
CustomData json
Visits



Visits
Column Type Notes
Sequence bigint AI PK
EnteredDT datetime
TimeIn datetime
TimeOut datetime
Duration int
isWork tinyint
StudentID bigint Relates to Students.Sequence (not Students.ID).
CenterID bigint Relates to Centers.Sequence.
ConsultantID bigint Relates to Staff.Sequence.
RegistrationID bigint Relates to Registrations.Sequence.
ReasonID bigint Relates to Reasons.Sequence.
TotalTime double
isWaiting tinyint
WaitTime double
CustomData json
NotificationDT datetime
isTerminated tinyint
PostPaid tinyint
Posted1 tinyint
Posted2 tinyint
Posted3 tinyint
Fund varchar(80)
CtrNotes text
SchedNotes text
StudNotes text
tOptions text
SurveyRespID bigint Relates to SrvyRespAnswers.Sequence
TermID bigint Relates to Terms.Sequence.
SectionID bigint Relates to Sections.Sequence.
Location varchar(512)
Online tinyint
TimeZone varchar(120)
asynchVisitID bigint
VisitDay2 varchar(80)
gen2_date_EnteredDT date
gen2_date_TimeIn date
gen2_date_TimeOut date
VisitDay varchar(20)
Students



Students
Column Type Notes
Sequence bigint AI PK Static identifier for students, unrelated to ID.
UUID varchar(36)
ID bigint
Other_ID varchar(40)
Other_ID2 varchar(40)
Barcode varchar(80)
Status varchar(80)
OtherStatus varchar(80)
Legal_First varchar(80)
First_Name varchar(80)
Last_Name varchar(80)
Middle varchar(80)
Street varchar(250)
Apt varchar(80)
City varchar(120)
State varchar(80)
Zip varchar(20)
Country varchar(80)
Home_Phone varchar(80)
Work_Phone varchar(80)
Cell_Phone varchar(80)
Email varchar(120)
Preferred varchar(80)
passhash varchar(128) Unused.
Attempts bigint
resetCode varchar(80)
lockedOut int
TextAddr varchar(120)
LastDateIn date
Notes text
UserName varchar(120)
Password varchar(128) Unused.
Prefs text
Birthdate date
Gender varchar(80)
Ethnicity varchar(150)
Major varchar(120)
Class varchar(80)
DegreeGoal varchar(80)
Cohort varchar(80)
College varchar(80)
Grad_Und varchar(80)
DateStarted date
DateWithdrawn date
ReasonWithdrawn varchar(128)
GradDate date
GradDegree varchar(128)
GPA double
AccumHours double
PrimaryConsultantID bigint Relates to Staff.Sequence.
FlagText varchar(80)
CustomData json
WatchLists json
CreatedDT timestamp
ModifiedDT timestamp
Fund varchar(80)
gen_BIOConfirmed varchar(2)
gen_BIOConfirmedDT varchar(20)
Pronouns varchar(80)
Full_Name varchar(255)
Full_Name2 varchar(255)
_LastImportedDT datetime
PassLastDateChanged date
PassFailedAttempts int
AccountLocked tinyint
AccountLockDateTime datetime
Subject Related (Courses, Sections, Terms, Registrations, Faculty, SectionSchedule)



Courses
Column Type Notes
Sequence bigint AI PK Unique identifier for each course.
Subject varchar(40)
Course varchar(20)
Title varchar(120)
ExcludeFromCharts tinyint
Dept varchar(40)
SubjectCourse varchar(200)
_LastImportedDT datetime
CustomData json
Sections
Column Type Notes
Sequence bigint AI PK Unique identifier for each section.
Inactive tinyint
Code varchar(80)
CourseID bigint Relates to Courses.Sequence
GradeGroupID bigint
CRN varchar(80)
TermID bigint Relates to Terms.Sequence
FacultyID bigint Relates to Faculty.Sequence
nonEnrolled tinyint
CustomData json
CreatedDT timestamp
ignoreRegImportDeactivate tinyint
Credits double
_LastImportedDT datetime
Title varchar(120)
Terms
Column Type Notes
Sequence bigint AI PK Unique identifier for terms.
Active tinyint
TermCode varchar(80)
OrderIdx int
ActiveFrom date
ActiveTo date
CustomData json Unused.
Registrations
Column Type Notes
Sequence bigint AI PK Unique identifier for registrations.
Inactive tinyint
OverrideDate tinyint
SectionID bigint Relates to Sections.Sequence.
StudentID bigint Relates to Students.Sequence (not Students.ID).
Grade varchar(40)
ValidFromDT datetime
ValidToDT datetime
Notes text
CustomData json
_LastImportedDT datetime
RegStatus varchar(40)
EarnedCredits double
Faculty
Column Type Notes
Sequence bigint AI PK
UUID varchar(36)
FirstName varchar(80)
LastName varchar(80)
Password varchar(128) Unused.
UserID varchar(80)
UserName varchar(80)
Custom2 text
Custom1 text
Salutation varchar(40)
Department varchar(60)
Inactive tinyint
Phone varchar(80)
Email varchar(120)
CustomData json
OtherID varchar(80)
FullName varchar(255)
facultyBIO text
Pronouns varchar(80)
_LastImportedDT datetime
PassLastDateChanged date
PassFailedAttempts int
AccountLocked tinyint
AccountLockDateTime datetime
SectionSchedule
Column Type Notes
Sequence bigint AI PK
SectionID bigint Related to Sections.Sequence
Days varchar(30)
Time time
Duration int
Description text
OtherData text
Campus varchar(80)
Bldg varchar(80)
Room varchar(80)
Profile Related (Profiles, Centers, Groups, Reasons)



Profiles
Column Type Notes
Sequence bigint AI PK
Type int
Name varchar(80)
Inactive tinyint
ContactID bigint
NameForConsultant varchar(80)
CustomData json
TimeZone varchar(120)
Centers
Column Type Notes
Sequence bigint AI PK
Name varchar(80)
Prefs text
CourseListID bigint
fromEmail varchar(80)
ProfileType varchar(80) Unused.
ProfileID bigint Relates to Profiles.Sequence.
Inactive tinyint
PrimaryGroup bigint Unused.
Settings json
OtherData json
UpdatedDT datetime
IsWritingCenter tinyint
Reasons
Column Type Notes
Sequence bigint AI PK
ProfileID bigint Relates to Profiles.Sequence.
Subcenters varchar(255)
Reason varchar(80)
Inactive tinyint
AutoLogTime int
AutoLogNow int
NotificationTime int
SnoozeTime int
isWork tinyint
isStaff tinyint
SortCode varchar(40)
NotForAppt tinyint
NotForLogin tinyint
NotForVisit tinyint
Category varchar(80)
CustomData json
Groups
Column Type Notes
Sequence bigint AI PK
GroupName varchar(80)
ProfileID bigint Relates to Profiles.Sequence.
Prefs json

More coming soon...

Retrieved from "https://wiki.go-redrock.com/index.php?title=TracCloudTables&oldid=10386"