-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdatabase.php
166 lines (125 loc) · 6.2 KB
/
database.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
<?php
// The SQL to uninstall this tool
$DATABASE_UNINSTALL = array();
/** Table names */
$TOKENS_CONFIG_TABLE_NAME = "{$CFG->dbprefix}tokens_configuration";
$TOKENS_INSTRUCTOR_OPTION_TABLE_NAME = "{$CFG->dbprefix}tokens_instructor_option";
$TOKENS_CATEGORY_TABLE_NAME = "{$CFG->dbprefix}tokens_category";
$TOKENS_REQUEST_TABLE_NAME = "{$CFG->dbprefix}tokens_request";
$TOKENS_AWARD_TABLE_NAME = "{$CFG->dbprefix}tokens_award";
/** Table schemas */
$TOKENS_CONFIGURATION = "CREATE TABLE {$TOKENS_CONFIG_TABLE_NAME} (
/* PRIMARY KEY */
configuration_id INTEGER NOT NULL AUTO_INCREMENT,
/* COMMON COLS */
user_id INTEGER NOT NULL, /* ID of the instructor the created the settings */
context_id INTEGER NOT NULL, /* Tracked and scoped, this is the course */
link_id INTEGER NOT NULL, /* Tracked but not scoped, this is the instance */
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
/* TOKEN COLS */
initial_tokens INTEGER NOT NULL,
use_by_date DATETIME NOT NULL, /* Tokens cannot be used AFTER this date */
notifications_pref BOOLEAN,
PRIMARY KEY(configuration_id),
UNIQUE(context_id, link_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8";
$TOKENS_INSTRUCTOR_OPTION = "CREATE TABLE {$TOKENS_INSTRUCTOR_OPTION_TABLE_NAME} (
/* PRIMARY KEY */
option_id INTEGER NOT NULL AUTO_INCREMENT,
/* COMMON COLS */
user_id INTEGER NOT NULL, /* ID of the instructor that chose the option */
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
/* TOKEN COLS */
configuration_id INTEGER NOT NULL, /* FK reference to the configuration */
notifications_pref BOOLEAN,
PRIMARY KEY(option_id),
CONSTRAINT `fk_option_configuration`
FOREIGN KEY (`configuration_id`)
REFERENCES `{$TOKENS_CONFIG_TABLE_NAME}` (`configuration_id`)
ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET=utf8";
$TOKENS_CATEGORY = "CREATE TABLE {$TOKENS_CATEGORY_TABLE_NAME} (
/* PRIMARY KEY */
category_id INTEGER NOT NULL AUTO_INCREMENT,
/* COMMON COLS */
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
/* TOKEN COLS */
configuration_id INTEGER NOT NULL, /* FK reference to the configuration */
category_name VARCHAR(255) NOT NULL,
sort_order INTEGER NOT NULL,
token_cost INTEGER NOT NULL,
PRIMARY KEY(category_id),
CONSTRAINT `fk_category_configuration`
FOREIGN KEY (`configuration_id`)
REFERENCES `{$TOKENS_CONFIG_TABLE_NAME}` (`configuration_id`)
ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET=utf8";
$TOKENS_REQUEST = "CREATE TABLE {$TOKENS_REQUEST_TABLE_NAME} (
/* PRIMARY KEY */
request_id INTEGER NOT NULL AUTO_INCREMENT,
/* COMMON COLS */
user_id INTEGER NOT NULL, /** Learner */
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
/* TOKEN COLS */
configuration_id INTEGER NOT NULL, /* FK reference to the configuration */
category_id INTEGER NOT NULL, /* FK reference to category */
learner_comment TEXT NOT NULL,
instructor_comment TEXT,
instructor_id INTEGER,
status_name VARCHAR(255) NOT NULL,
status_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, /* Will be submitted at first */
PRIMARY KEY(request_id),
CONSTRAINT `fk_request_configuration`
FOREIGN KEY (`configuration_id`)
REFERENCES `{$TOKENS_CONFIG_TABLE_NAME}` (`configuration_id`)
ON DELETE CASCADE,
CONSTRAINT `fk_request_category`
FOREIGN KEY (`category_id`)
REFERENCES `{$TOKENS_CATEGORY_TABLE_NAME}` (`category_id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8";
$TOKENS_AWARD = "CREATE TABLE {$TOKENS_AWARD_TABLE_NAME} (
/* PRIMARY KEY */
award_id INTEGER NOT NULL AUTO_INCREMENT,
/* COMMON COLS */
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
/* TOKEN COLS */
configuration_id INTEGER NOT NULL, /* FK reference to the configuration */
recipient_id VARCHAR(255) NOT NULL, /* Id of the recipient */
comment TEXT NOT NULL,
award_count INTEGER NOT NULL,
PRIMARY KEY(award_id),
CONSTRAINT `fk_award_configuration`
FOREIGN KEY (`configuration_id`)
REFERENCES `{$TOKENS_CONFIG_TABLE_NAME}` (`configuration_id`)
ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET=utf8";
/** Table installation (if tables don't exist) */
$DATABASE_INSTALL = array(
array($TOKENS_CONFIG_TABLE_NAME, $TOKENS_CONFIGURATION),
array($TOKENS_CATEGORY_TABLE_NAME, $TOKENS_CATEGORY),
array($TOKENS_REQUEST_TABLE_NAME, $TOKENS_REQUEST),
array($TOKENS_INSTRUCTOR_OPTION_TABLE_NAME, $TOKENS_INSTRUCTOR_OPTION),
array($TOKENS_AWARD_TABLE_NAME, $TOKENS_AWARD),
);
$DATABASE_UPGRADE = function ($oldversion) {
global $PDOX, $TOKENS_AWARD_TABLE_NAME, $TOKENS_CONFIG_TABLE_NAME;
// Add updated_at column
if ($PDOX->columnExists('recipient_id', "{$TOKENS_AWARD_TABLE_NAME}")) {
$sql = "ALTER TABLE {$TOKENS_AWARD_TABLE_NAME} MODIFY COLUMN recipient_id VARCHAR(255) NOT NULL";
echo ("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
if (!$PDOX->columnExists('general_note', "{$TOKENS_CONFIG_TABLE_NAME}")) {
$sql = "ALTER TABLE {$TOKENS_CONFIG_TABLE_NAME} ADD COLUMN general_note TEXT";
echo ("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
return '202211031707';
};