static function SetupDBTables($old_ver = null)
{
global $wpdb;
$queries = array();
$tbl_cats = $wpdb->prefix . 'wpfb_cats';
$tbl_files = $wpdb->prefix . 'wpfb_files';
$tbl_files_id3 = $wpdb->prefix . 'wpfb_files_id3';
$queries[] = "CREATE TABLE IF NOT EXISTS `{$tbl_cats}` (\n `cat_id` int(8) unsigned NOT NULL auto_increment,\n `cat_name` varchar(255) NOT NULL default '',\n `cat_description` text,\n `cat_folder` varchar(300) NOT NULL,\n `cat_path` varchar(2000) NOT NULL,\n `cat_parent` int(8) unsigned NOT NULL default '0',\n `cat_num_files` int(8) unsigned NOT NULL default '0',\n `cat_num_files_total` int(8) unsigned NOT NULL default '0',\n `cat_user_roles` text NOT NULL default '',\n `cat_owner` bigint(20) unsigned default NULL,\n `cat_icon` varchar(255) default NULL,\n `cat_exclude_browser` enum('0','1') NOT NULL default '0',\n `cat_order` int(8) NOT NULL default '0',\n `cat_wp_term_id` bigint(20) NOT NULL default '0',\n `cat_scan_lock` bigint(20) unsigned NOT NULL default '0',\n PRIMARY KEY (`cat_id`),\n FULLTEXT KEY `USER_ROLES` (`cat_user_roles`)\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
$queries[] = "CREATE TABLE IF NOT EXISTS `{$tbl_files}` (\n `file_id` bigint(20) unsigned NOT NULL auto_increment,\n `file_name` varchar(300) NOT NULL default '',\n `file_name_original` varchar(300) NOT NULL default '',\n `file_path` varchar(2000) NOT NULL default '',\n `file_size` bigint(20) unsigned NOT NULL default '0',\n `file_date` datetime NOT NULL default '0000-00-00 00:00:00',\n `file_mtime` bigint(20) unsigned NOT NULL default '0',\n `file_hash` char(32) NOT NULL,\n `file_remote_uri` varchar(2000) NOT NULL default '',\n `file_thumbnail` varchar(255) default NULL,\n `file_display_name` varchar(255) NOT NULL default '',\n `file_description` text,\n `file_tags` varchar(255) NOT NULL default '',\n `file_requirement` varchar(255) default NULL,\n `file_version` varchar(64) default NULL,\n `file_author` varchar(255) default NULL,\n `file_language` varchar(255) default NULL,\n `file_platform` varchar(255) default NULL,\n `file_license` varchar(255) NOT NULL default '',\n `file_user_roles` text NOT NULL default '',\n `file_offline` enum('0','1') NOT NULL default '0',\n `file_direct_linking` enum('0','1','2') NOT NULL default '0',\n `file_force_download` enum('0','1') NOT NULL default '0',\n `file_category` int(8) unsigned NOT NULL default '0',\n `file_category_name` varchar(127) NOT NULL default '',\n `file_update_of` bigint(20) unsigned default NULL,\n `file_post_id` bigint(20) unsigned default NULL,\n `file_attach_order` int(8) NOT NULL default '0',\n `file_wpattach_id` bigint(20) NOT NULL default '0',\n `file_added_by` bigint(20) unsigned default NULL,\n `file_hits` bigint(20) unsigned NOT NULL default '0',\n `file_ratings` bigint(20) unsigned NOT NULL default '0',\n `file_rating_sum` bigint(20) unsigned NOT NULL default '0',\n `file_last_dl_ip` varchar(100) NOT NULL default '',\n `file_last_dl_time` datetime NOT NULL default '0000-00-00 00:00:00',\n `file_rescan_pending` tinyint(4) NOT NULL default '0',\n `file_scan_lock` bigint(20) unsigned NOT NULL default '0',\n " . "\n PRIMARY KEY (`file_id`),\n FULLTEXT KEY `DESCRIPTION` (`file_description`),\n FULLTEXT KEY `USER_ROLES` (`file_user_roles`)\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
$queries[] = "CREATE TABLE IF NOT EXISTS `{$tbl_files_id3}` (\n `file_id` bigint(20) unsigned NOT NULL auto_increment,\n `analyzetime` INT(11) NOT NULL DEFAULT '0',\n `value` LONGTEXT NOT NULL,\n `keywords` TEXT NOT NULL,\n PRIMARY KEY (`file_id`),\n FULLTEXT KEY `KEYWORDS` (`keywords`)\n) ENGINE=MyISAM DEFAULT CHARSET=utf8";
// errors of queries starting with @ are supressed
$queries[] = "@ALTER TABLE `{$tbl_cats}` DROP INDEX `FULLTEXT`";
$queries[] = "@ALTER TABLE `{$tbl_cats}` DROP INDEX `CAT_NAME`";
$queries[] = "@ALTER TABLE `{$tbl_cats}` DROP INDEX `CAT_FOLDER`";
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD UNIQUE `UNIQUE_FOLDER` ( `cat_folder` , `cat_parent` ) ";
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD UNIQUE `UNIQUE_FILE` ( `file_name` , `file_category` )";
// <= v0.1.2.2
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD `cat_icon` VARCHAR(255) NULL DEFAULT NULL";
// since v0.2.0.0
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_remote_uri` VARCHAR( 2000 ) NULL DEFAULT NULL AFTER `file_hash`";
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_force_download` enum('0','1') NOT NULL default '0'";
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_path` varchar(255) NOT NULL default '' AFTER `file_name`";
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD `cat_exclude_browser` enum('0','1') NOT NULL default '0'";
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD `cat_path` varchar(255) NOT NULL default '' AFTER `cat_folder`";
// removed since 0.2.9.25
//$queries[] = "@ALTER TABLE `$tbl_cats` ADD UNIQUE `UNIQUE_PATH` ( `cat_path` ) ";
//$queries[] = "@ALTER TABLE `$tbl_files` ADD UNIQUE `UNIQUE_PATH` ( `file_path` )";
// the new cat file counters
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD `cat_num_files` int(8) unsigned NOT NULL default '0' AFTER `cat_parent`";
$queries[] = "@ALTER TABLE `{$tbl_cats}` CHANGE `cat_files` `cat_num_files_total` INT( 8 ) UNSIGNED NOT NULL DEFAULT '0'";
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD `cat_num_files_total` int(8) unsigned NOT NULL default '0' AFTER `cat_num_files`";
// since 0.2.8
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_category_name` varchar(127) NOT NULL default '' AFTER `file_category`";
// since 0.2.9.1
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_user_roles` varchar(2000) NOT NULL default '' AFTER `file_license`";
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD `cat_user_roles` varchar(2000) NOT NULL default '' AFTER `cat_num_files_total`";
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_attach_order` int(8) NOT NULL default '0' AFTER `file_post_id`";
// since 0.2.9.3
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_wpattach_id` bigint(20) NOT NULL default '0' AFTER `file_attach_order`";
// since 0.2.9.9
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_tags` varchar(255) NOT NULL default '' AFTER `file_description`";
// 0.2.9.10
$queries[] = "@ALTER TABLE `{$tbl_files_id3}` CHANGE `value` `value` LONGTEXT";
// 0.2.9.12
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD `cat_order` int(8) NOT NULL default '0' AFTER `cat_exclude_browser`";
// since 0.2.9.25
$queries[] = "@ALTER TABLE `{$tbl_cats}` DROP INDEX `UNIQUE_PATH`";
$queries[] = "@ALTER TABLE `{$tbl_files}` DROP INDEX `UNIQUE_PATH`";
$queries[] = "ALTER TABLE `{$tbl_cats}` CHANGE `cat_path` `cat_path` VARCHAR( 2000 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''";
$queries[] = "ALTER TABLE `{$tbl_files}` CHANGE `file_path` `file_path` VARCHAR( 2000 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''";
$queries[] = "ALTER TABLE `{$tbl_cats}` CHANGE `cat_folder` `cat_folder` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''";
$queries[] = "ALTER TABLE `{$tbl_files}` CHANGE `file_name` `file_name` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''";
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD `cat_owner` bigint(20) unsigned NOT NULL default 0 AFTER `cat_user_roles`";
// add fulltext indices
if (!empty($old_ver) && version_compare($old_ver, '0.2.9.24') < 0) {
// TODO: search fields fulltext index!
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD FULLTEXT `USER_ROLES` (`file_user_roles`)";
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD FULLTEXT `USER_ROLES` (`cat_user_roles`)";
$queries[] = "@ALTER TABLE `{$tbl_files_id3}` ADD FULLTEXT `KEYWORDS` (`keywords`)";
}
// 2 is for file pages
if (!empty($old_ver) && version_compare($old_ver, '0.2.9.24') < 0) {
$queries[] = "ALTER TABLE `{$tbl_files}` CHANGE `file_direct_linking` `file_direct_linking` ENUM( '0', '1', '2' ) NOT NULL DEFAULT '0'";
}
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_rescan_pending` tinyint(4) NOT NULL default '0'";
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_scan_lock` bigint(20) unsigned NOT NULL default '0'";
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD `cat_scan_lock` bigint(20) unsigned NOT NULL default '0'";
// since 0.2.9.25
// fix (0,1,3) => (0,1,2)
$queries[] = "@ALTER TABLE `{$tbl_files}` CHANGE `file_direct_linking` `file_direct_linking` ENUM( '0', '1', '2' ) NOT NULL DEFAULT '0'";
// roles text
$queries[] = "ALTER TABLE `{$tbl_files}` CHANGE `file_user_roles` `file_user_roles` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''";
$queries[] = "ALTER TABLE `{$tbl_cats}` CHANGE `cat_user_roles` `cat_user_roles` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''";
$queries[] = "@ALTER TABLE `{$tbl_files}` ADD `file_name_original` varchar(300) NOT NULL default '' AFTER `file_name`";
$queries[] = "@ALTER TABLE `{$tbl_cats}` ADD `cat_wp_term_id` bigint(20) NOT NULL default '0'";
$queries[] = "ALTER TABLE `{$tbl_files}` CHANGE `file_remote_uri` `file_remote_uri` VARCHAR( 2000 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''";
$queries[] = "OPTIMIZE TABLE `{$tbl_cats}`";
$queries[] = "OPTIMIZE TABLE `{$tbl_files}`";
// dont use wpdb->query, because it prints errors
foreach ($queries as $sql) {
if ($sql[0] == '@') {
$sql = substr($sql, 1);
$wpdb->suppress_errors();
$wpdb->query($sql);
$wpdb->suppress_errors(false);
} else {
$wpdb->query($sql);
}
}
// since 0.2.9.13 : file_mtime, use file_date as default
if (!$wpdb->get_var("SHOW COLUMNS FROM `{$tbl_files}` LIKE 'file_mtime'")) {
$wpdb->query("ALTER TABLE `{$tbl_files}` ADD `file_mtime` bigint(20) unsigned NOT NULL default '0' AFTER `file_date`");
$files = $wpdb->get_results("SELECT file_id,file_date FROM {$tbl_files}");
foreach ((array) $files as $file) {
$wpdb->query("UPDATE `{$tbl_files}` SET `file_mtime` = '" . mysql2date('U', $file->file_date) . "' WHERE `file_id` = {$file->file_id}");
}
// this is faster, but UNIX_TIMESTAMP adds leap seconds, so all files will be synced again!
//$wpdb->query("UPDATE `$tbl_files` SET `file_mtime` = UNIX_TIMESTAMP(`file_date`) WHERE file_mtime = 0;");
}
// convert all required_level -> user_roles
if (!!$wpdb->get_var("SHOW COLUMNS FROM `{$tbl_files}` LIKE 'file_required_level'")) {
$files = $wpdb->get_results("SELECT file_id,file_required_level FROM {$tbl_files} WHERE file_required_level <> 0");
foreach ((array) $files as $file) {
$wpdb->query("UPDATE `{$tbl_files}` SET `file_user_roles` = '|" . WPFB_Setup::UserLevel2Role($file->file_required_level - 1) . "' WHERE `file_id` = {$file->file_id}");
}
$wpdb->query("ALTER TABLE `{$tbl_files}` DROP `file_required_level`");
}
if (!!$wpdb->get_var("SHOW COLUMNS FROM `{$tbl_cats}` LIKE 'cat_required_level'")) {
$cats = $wpdb->get_results("SELECT cat_id,cat_required_level FROM {$tbl_cats} WHERE cat_required_level <> 0");
foreach ((array) $cats as $cat) {
$wpdb->query("UPDATE `{$tbl_cats}` SET `cat_user_roles` = '|" . WPFB_Setup::UserLevel2Role($cat->cat_required_level - 1) . "' WHERE `cat_id` = {$cat->cat_id}");
}
$wpdb->query("ALTER TABLE `{$tbl_cats}` DROP `cat_required_level`");
}
/* NOT neeeded since using fulltext index!
// add leading | to user_roles
if(!empty($old_ver) && version_compare($old_ver, '0.2.9.24') < 0) {
$wpdb->query("UPDATE `$tbl_files` SET `file_user_roles` = CONCAT('|', `file_user_roles`) WHERE LEFT(`file_user_roles`, 1) <> '|'");
$wpdb->query("UPDATE `$tbl_cats` SET `cat_user_roles` = CONCAT('|', `cat_user_roles`) WHERE LEFT(`cat_user_roles`, 1) <> '|'");
}
*/
}
