1
create table Envelopes (
2
EnvelopeKey int not null auto_increment,
3
EnvelopeId char(36) not null, /* GUID for client? */
4
InstanceId char(100) null,
5
SchemaVersion char(10) not null,
6
ApiVersion char(50) not null,
7
ApiLanguage char(50) not null,
8
UtcReceived datetime not null,
9
IPAddress char(20) null,
10
ApplicationId char(36) not null,
11
ApplicationName char(255) not null,
12
ApplicationVersion char(50) not null,
13
ApplicationType char(50) null,
14
CompanyId char(36) not null,
15
CompanyName char(100) not null,
16
Created datetime not null,
17
primary key (EnvelopeKey),
20
create index EnvelopeApplicationIndex
21
on Envelopes (EnvelopeKey, ApplicationId, ApplicationName, ApplicationVersion);
23
create table ApplicationMessages (
24
MessageId char(36) not null,
25
EnvelopeKey int not null,
26
SessionId char(36) not null,
27
UtcTimestamp datetime not null,
28
EventCode char(20) not null,
29
UserName char(100) not null,
30
IsAdminUser bit not null,
31
Created datetime not null,
32
primary key (MessageId),
33
foreign key (EnvelopeKey) references Envelopes(EnvelopeKey)
35
create index AppMsgsEnvelopeTimestampEventIndex
36
on ApplicationMessages (EnvelopeKey, UtcTimeStamp, EventCode);
38
create table FeatureMessages (
39
MessageId char(36) not null,
40
EnvelopeKey int not null,
41
SessionId char(36) not null,
42
UtcTimestamp datetime not null,
43
EventCode char(20) not null,
44
GroupId char(36) not null,
45
FeatureName char(100) not null,
46
Created datetime not null,
47
primary key (MessageId),
48
foreign key (EnvelopeKey) references Envelopes(EnvelopeKey)
50
-- 2 indexes that should help speed things along.
51
-- the different orders of the columns help 2 parts of the queries
52
create index FeatMsgsEventIndex
53
on FeatureMessages (UtcTimeStamp, EventCode, SessionId, EnvelopeKey, FeatureName);
54
create index FeatMsgsEvent2Index
55
on FeatureMessages (EventCode, UtcTimeStamp, SessionId, EnvelopeKey, FeatureName);
57
create table UniqueUsers (
58
UniqueUserKey int not null auto_increment,
59
UniqueUserId char(36) not null, /* Unique User ID */
61
LastRecieved int not null,
62
IPAddress char(20) null,
63
Country char(50) not null,
64
Longitude float not null,
65
Latitude float not null,
66
OSVersion char(100) not null,
67
OSServicePack int not null,
68
OSArchitecture int not null,
69
JavaVer char(50) not null,
70
NetVer char(50) not null,
73
ScreenRes char(20) not null,
74
CPUName char(40) not null,
75
CPUBrand char(20) not null,
77
CPUCores int not null,
79
MemTotal int not null,
81
DiskTotal int not null,
82
DiskFree int not null,
83
primary key (UniqueUserKey),
87
create table Sessions (
88
SessionKey int not null auto_increment,
89
SessionId char(36) not null, /* Session ID */
90
UniqueUserId char(36) not null,
91
StartApp int not null,
93
ApplicationId char(36) not null,
94
ApplicationVersion char(50) not null,
95
primary key (SessionKey),
100
EventId int not null auto_increment,
101
EventCode char(20) not null,
104
EventCategory char(50) null,
105
EventName char(50) null,
107
EventValue char(50) null,
109
EventDuration int null,
110
EventCompleted tinyint(1) null,
112
LogMessage char(100) null,
114
EventCustomName char(50) null,
115
EventCustomValue char(50) null,
116
/* Install & Uninstall */
117
ApplicationVersion char(20) null,
119
ExceptionMsg char(100) null,
120
ExceptionStackTrace char(500) null,
121
ExceptionSource char(100) null,
122
ExceptionTargetSite char(100) null,
124
SessionId char(36) not null,
125
UtcTimestamp int not null,
127
primary key (EventId)
130
create table Applications (
131
ApplicationKey int not null auto_increment,
132
ApplicationId char(36) not null,
133
ApplicationName char(255) not null,
134
ApplicationRecieving tinyint(1) null,
135
primary key (ApplicationKey),
136
unique(ApplicationID)
140
UserId int not null auto_increment,
141
UserName char(8) not null,
142
UserEmail char(30) not null,
143
UserPass char(32) not null,
144
ActivateKey char(20) null,
145
primary key (UserId),
146
unique(UserName, UserEmail)
b'\\ No newline at end of file'