$password_encrypted = $hasher->HashPassword($passForm);
$admin_uid = $db->query("INSERT INTO `user`\n (`givenname`, `surname`, `username`, `password`, `email`, `status`,\n `registered_at`,`expires_at`, `verified_mail`, `whitelist`, `description`)\n VALUES (?s, ?s, ?s, ?s, ?s, ?d, " . DBHelper::timeAfter() . ", " . DBHelper::timeAfter(5 * 365 * 24 * 60 * 60) . ", ?d, ?s, ?s)", $nameForm, '', $loginForm, $password_encrypted, $emailForm, 1, 1, '*,,', 'Administrator')->affectedRows;
$db->query("INSERT INTO loginout (`id_user`, `ip`, `when`, `action`)\n VALUES (?d, ?s, " . DBHelper::timeAfter() . ", ?s)", $admin_uid, $_SERVER['REMOTE_ADDR'], 'LOGIN');
$db->query("INSERT INTO admin (user_id, privilege) VALUES (?d, ?d)", $admin_uid, 0);
#
# Table structure for table `user_request`
#
$db->query("CREATE TABLE user_request (\n id INT(11) NOT NULL AUTO_INCREMENT,\n givenname VARCHAR(60) NOT NULL DEFAULT '',\n surname VARCHAR(60) NOT NULL DEFAULT '',\n username VARCHAR(50) NOT NULL DEFAULT '',\n password VARCHAR(255) NOT NULL DEFAULT '',\n email VARCHAR(100) NOT NULL DEFAULT '',\n verified_mail TINYINT(1) NOT NULL DEFAULT " . EMAIL_UNVERIFIED . ",\n faculty_id INT(11) NOT NULL DEFAULT 0,\n phone VARCHAR(20) NOT NULL DEFAULT '',\n am VARCHAR(20) NOT NULL DEFAULT '',\n state INT(11) NOT NULL DEFAULT 0,\n date_open DATETIME DEFAULT NULL,\n date_closed DATETIME DEFAULT NULL,\n comment TEXT NOT NULL,\n lang VARCHAR(16) NOT NULL DEFAULT 'el',\n status TINYINT(4) NOT NULL DEFAULT 1,\n request_ip VARCHAR(45) NOT NULL DEFAULT '',\n PRIMARY KEY (id)) {$charset_spec}");
$db->query("CREATE TABLE `auth` (\n `auth_id` int(2) NOT NULL auto_increment,\n `auth_name` varchar(20) NOT NULL default '',\n `auth_settings` text ,\n `auth_instructions` text ,\n `auth_default` tinyint(1) NOT NULL default 0,\n PRIMARY KEY (`auth_id`))\n {$charset_spec}");
$db->query("INSERT INTO `auth` VALUES\n (1, 'eclass', '', '', 1),\n (2, 'pop3', '', '', 0),\n (3, 'imap', '', '', 0),\n (4, 'ldap', '', '', 0),\n (5, 'db', '', '', 0),\n (6, 'shibboleth', '', '', 0),\n (7, 'cas', '', '', 0)");
$eclass_stud_reg = intval($eclass_stud_reg);
$eclass_prof_reg = intval($eclass_prof_reg);
$student_upload_whitelist = 'pdf, ps, eps, tex, latex, dvi, texinfo, texi, zip, rar, tar, bz2, gz, 7z, xz, lha, lzh, z, Z, doc, docx, odt, ott, sxw, stw, fodt, txt, rtf, dot, mcw, wps, xls, xlsx, xlt, ods, ots, sxc, stc, fods, uos, csv, ppt, pps, pot, pptx, ppsx, odp, otp, sxi, sti, fodp, uop, potm, odg, otg, sxd, std, fodg, odb, mdb, ttf, otf, jpg, jpeg, png, gif, bmp, tif, tiff, psd, dia, svg, ppm, xbm, xpm, ico, avi, asf, asx, wm, wmv, wma, dv, mov, moov, movie, mp4, mpg, mpeg, 3gp, 3g2, m2v, aac, m4a, flv, f4v, m4v, mp3, swf, webm, ogv, ogg, mid, midi, aif, rm, rpm, ram, wav, mp2, m3u, qt, vsd, vss, vst';
$teacher_upload_whitelist = 'htm, html, js, css, xml, xsl, cpp, c, java, m, h, tcl, py, sgml, sgm, ini, ds_store';
$db->query("CREATE TABLE `config` (\n `key` VARCHAR(32) NOT NULL,\n `value` TEXT NOT NULL,\n PRIMARY KEY (`key`))");
$default_config = array('autojudge_connector', 'CodepadConnector', 'base_url', $urlForm, 'default_language', $lang, 'dont_display_login_form', 0, 'email_required', 0, 'email_from', 1, 'email_verification_required', 0, 'dont_mail_unverified_mails', 0, 'am_required', 0, 'dropbox_allow_student_to_student', 0, 'block_username_change', 0, 'enable_mobileapi', 1, 'code_key', generate_secret_key(32), 'display_captcha', 0, 'insert_xml_metadata', 0, 'doc_quota', 500, 'video_quota', 500, 'group_quota', 500, 'dropbox_quota', 500, 'user_registration', 1, 'alt_auth_stud_reg', 2, 'alt_auth_prof_reg', 2, 'eclass_stud_reg', $eclass_stud_reg, 'eclass_prof_reg', $eclass_prof_reg, 'course_multidep', 0, 'user_multidep', 0, 'restrict_owndep', 0, 'restrict_teacher_owndep', 0, 'max_glossary_terms', '250', 'phpSysInfoURL', $phpSysInfoURL, 'email_sender', $emailForm, 'admin_name', $nameForm, 'email_helpdesk', $helpdeskmail, 'site_name', $campusForm, 'phone', $helpdeskForm, 'fax', $faxForm, 'postaddress', $postaddressForm, 'institution', $institutionForm, 'institution_url', $institutionUrlForm, 'account_duration', '126144000', 'language', $lang, 'active_ui_languages', $active_ui_languages, 'student_upload_whitelist', $student_upload_whitelist, 'teacher_upload_whitelist', $teacher_upload_whitelist, 'theme', 'default', 'theme_options_id', 0, 'login_fail_check', 1, 'login_fail_threshold', 15, 'login_fail_deny_interval', 5, 'login_fail_forgive_interval', 24, 'actions_expire_interval', 12, 'log_expire_interval', 5, 'log_purge_interval', 12, 'course_metadata', 0, 'opencourses_enable', 0, 'enable_indexing', 1, 'enable_search', 1, 'version', ECLASS_VERSION);
$db->query("INSERT INTO `config` (`key`, `value`) VALUES " . implode(', ', array_fill(0, count($default_config) / 2, '(?s, ?s)')), $default_config);
// table for cron parameters
$db->query("CREATE TABLE `cron_params` (\n `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,\n `name` VARCHAR(255) NOT NULL UNIQUE,\n `last_run` DATETIME NOT NULL) {$charset_spec}");
// tables for units module
$db->query("CREATE TABLE `course_units` (\n `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY ,\n `title` VARCHAR(255) NOT NULL DEFAULT '',\n `comments` MEDIUMTEXT,\n `visible` TINYINT(4),\n `public` TINYINT(4) NOT NULL DEFAULT 1,\n `order` INT(11) NOT NULL DEFAULT 0,\n `course_id` INT(11) NOT NULL) {$charset_spec}");
$db->query("CREATE TABLE `unit_resources` (\n `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY ,\n `unit_id` INT(11) NOT NULL ,\n `title` VARCHAR(255) NOT NULL DEFAULT '',\n `comments` MEDIUMTEXT,\n `res_id` INT(11) NOT NULL,\n `type` VARCHAR(255) NOT NULL DEFAULT '',\n `visible` TINYINT(4),\n `order` INT(11) NOT NULL DEFAULT 0,\n `date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00') {$charset_spec}");
$db->query("CREATE TABLE `actions_daily` (\n `id` int(11) NOT NULL auto_increment,\n `user_id` int(11) NOT NULL,\n `module_id` int(11) NOT NULL,\n `course_id` int(11) NOT NULL,\n `hits` int(11) NOT NULL,\n `duration` int(11) NOT NULL,\n `day` date NOT NULL,\n `last_update` DATETIME NOT NULL,\n PRIMARY KEY (`id`),\n KEY `actionsdailyindex` (`module_id`, `day`),\n KEY `actionsdailyuserindex` (`user_id`),\n KEY `actionsdailydayindex` (`day`),\n KEY `actionsdailymoduleindex` (`module_id`),\n KEY `actionsdailycourseindex` (`course_id`) )");
$db->query("CREATE TABLE IF NOT EXISTS `actions_summary` (\n `id` int(11) NOT NULL auto_increment,\n `module_id` int(11) NOT NULL,\n `visits` int(11) NOT NULL,\n `start_date` datetime NOT NULL default '0000-00-00 00:00:00',\n `end_date` datetime NOT NULL default '0000-00-00 00:00:00',\n `duration` int(11) NOT NULL,\n `course_id` INT(11) NOT NULL,\n PRIMARY KEY (`id`))");
$db->query("CREATE TABLE IF NOT EXISTS `logins` (\n `id` int(11) NOT NULL auto_increment,\n `user_id` int(11) NOT NULL,\n `ip` char(45) NOT NULL default '0.0.0.0',\n `date_time` datetime NOT NULL default '0000-00-00 00:00:00',\n `course_id` INT(11) NOT NULL,\n PRIMARY KEY (`id`))");
// bbb_servers table
$db->query('CREATE TABLE IF NOT EXISTS `bbb_servers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hostname` varchar(255) DEFAULT NULL,
`ip` varchar(255) NOT NULL,
`enabled` enum("true","false") DEFAULT NULL,
// Upgrade table admin_announcements if needed
if (DBHelper::fieldExists('admin_announcements', 'gr_body')) {
Database::get()->query("RENAME TABLE `admin_announcements` TO `admin_announcements_old`");
Database::get()->query("CREATE TABLE IF NOT EXISTS `admin_announcements` (\n `id` INT(11) NOT NULL AUTO_INCREMENT,\n `title` VARCHAR(255) DEFAULT NULL,\n `body` TEXT,\n `date` DATETIME NOT NULL,\n `begin` DATETIME DEFAULT NULL,\n `end` DATETIME DEFAULT NULL,\n `visible` ENUM('V','I') NOT NULL,\n `lang` VARCHAR(10) NOT NULL DEFAULT 'el',\n `ordre` MEDIUMINT(11) NOT NULL DEFAULT 0,\n PRIMARY KEY (`id`))");
Database::get()->query("INSERT INTO admin_announcements (title, body, `date`, visible, lang)\n SELECT gr_title AS title, CONCAT_WS(' ', gr_body, gr_comment) AS body, `date`, visible, 'el'\n FROM admin_announcements_old WHERE gr_title <> '' OR gr_body <> ''");
Database::get()->query("INSERT INTO admin_announcements (title, body, `date`, visible, lang)\n SELECT en_title AS title, CONCAT_WS(' ', en_body, en_comment) AS body, `date`, visible, 'en'\n FROM admin_announcements_old WHERE en_title <> '' OR en_body <> ''");
Database::get()->query("DROP TABLE admin_announcements_old");
}
DBHelper::fieldExists('admin_announcements', 'ordre') or Database::get()->query("ALTER TABLE `admin_announcements` ADD `ordre` MEDIUMINT(11) NOT NULL DEFAULT 0 AFTER `lang`");
// not needed anymore
if (DBHelper::tableExists('cours_faculte')) {
Database::get()->query("DROP TABLE cours_faculte");
}
}
if (version_compare($oldversion, '2.5', '<')) {
Database::get()->query("INSERT IGNORE INTO `config` (`key`, `value`) VALUES\n ('disable_eclass_stud_reg', '0'),\n ('disable_eclass_prof_reg', '0'),\n ('email_verification_required', '1'),\n ('dont_mail_unverified_mails', '1'),\n ('close_user_registration', '0'),\n ('max_glossary_terms', '250'),\n ('code_key', '" . generate_secret_key(32) . "')");
// old users have their email verified
if (DBHelper::fieldExists('user', 'verified_mail')) {
Database::get()->query('ALTER TABLE `user` MODIFY `verified_mail` TINYINT(1) NOT NULL DEFAULT ' . EMAIL_UNVERIFIED);
Database::get()->query('UPDATE `user` SET `verified_mail`= ' . EMAIL_VERIFIED);
}
DBHelper::fieldExists('user_request', 'verified_mail') or Database::get()->query("ALTER TABLE `user_request` ADD `verified_mail` TINYINT(1) NOT NULL DEFAULT " . EMAIL_UNVERIFIED . " AFTER `email`");
Database::get()->query("UPDATE `user` SET `email`=LOWER(TRIM(`email`))");
Database::get()->query("UPDATE `user` SET `username`=TRIM(`username`)");
}
if (version_compare($oldversion, '2.5.2', '<')) {
Database::get()->query("ALTER TABLE `user` MODIFY `password` VARCHAR(60) DEFAULT 'empty'");
Database::get()->query("DROP TABLE IF EXISTS passwd_reset");
}
if (version_compare($oldversion, '2.6', '<')) {
Database::get()->query("ALTER TABLE `config` CHANGE `value` `value` TEXT NOT NULL");
`value` TEXT NOT NULL,
PRIMARY KEY (`key`))");
$default_config = array(
'base_url', $urlForm,
'default_language', $lang,
'dont_display_login_form', 0,
'email_required', 0,
'email_from', 1,
'email_verification_required', 0,
'dont_mail_unverified_mails', 0,
'am_required', 0,
'dropbox_allow_student_to_student', 0,
'block_username_change', 0,
'enable_mobileapi', 1,
'code_key', generate_secret_key(32),
'display_captcha', 0,
'insert_xml_metadata', 0,
'doc_quota', 500,
'video_quota', 500,
'group_quota', 500,
'dropbox_quota', 500,
'user_registration', 1,
'alt_auth_stud_reg', 2,
'alt_auth_prof_reg', 2,
'eclass_stud_reg', $eclass_stud_reg,
'eclass_prof_reg', $eclass_prof_reg,
'course_multidep', 0,
'user_multidep', 0,
'restrict_owndep', 0,
'restrict_teacher_owndep', 0,
Database::get()->query("ALTER TABLE `admin_announcements` ADD `ordre` MEDIUMINT(11) NOT NULL DEFAULT 0 AFTER `lang`");
// not needed anymore
if (DBHelper::tableExists('cours_faculte')) {
Database::get()->query("DROP TABLE cours_faculte");
}
}
if (version_compare($oldversion, '2.5', '<')) {
Database::get()->query("INSERT IGNORE INTO `config` (`key`, `value`) VALUES
('disable_eclass_stud_reg', '0'),
('disable_eclass_prof_reg', '0'),
('email_verification_required', '1'),
('dont_mail_unverified_mails', '1'),
('close_user_registration', '0'),
('max_glossary_terms', '250'),
('code_key', '" . generate_secret_key(32) . "')");
// old users have their email verified
if (DBHelper::fieldExists('user', 'verified_mail')) {
Database::get()->query('ALTER TABLE `user` MODIFY `verified_mail` TINYINT(1) NOT NULL DEFAULT ' . EMAIL_UNVERIFIED);
Database::get()->query('UPDATE `user` SET `verified_mail`= ' . EMAIL_VERIFIED);
}
DBHelper::fieldExists('user_request', 'verified_mail') or
Database::get()->query("ALTER TABLE `user_request` ADD `verified_mail` TINYINT(1) NOT NULL DEFAULT " . EMAIL_UNVERIFIED . " AFTER `email`");
Database::get()->query("UPDATE `user` SET `email`=LOWER(TRIM(`email`))");
Database::get()->query("UPDATE `user` SET `username`=TRIM(`username`)");
}
if (version_compare($oldversion, '2.5.2', '<')) {
Database::get()->query("ALTER TABLE `user` MODIFY `password` VARCHAR(60) DEFAULT 'empty'");
