BLOB->TEXT rewritten, all _user tables now handled by ext-user, except user_book...

[mailer.git] / inc / extensions / ext-user.php

diff --git a/inc/extensions/ext-user.php b/inc/extensions/ext-user.php
index f4397716d65108ebfe80ab04099d61667e6d4017..4a6a3856d2033ff703fa9a87476b9dae681bfa06 100644 (file)
--- a/inc/extensions/ext-user.php
+++ b/inc/extensions/ext-user.php
@@ -51,18 +51,18 @@ EXT_SET_VER_HISTORY(array("0.0", "0.1", "0.1.1", "0.1.2", "0.1.2", "0.1.3", "0.1
 switch ($EXT_LOAD_MODE)
 {
 case "register": // Do stuff when installation is running (modules.php?module=admin&action=login is called)
-       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}user_cats`");
-       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}user_cats` (
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_cats`");
+       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}_user_cats` (
   `id` BIGINT(22) NOT NULL AUTO_INCREMENT,
   `userid` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `cat_id` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
 PRIMARY KEY  (`id`),
 INDEX (`userid`),
 INDEX (`cat_id`)
-) TYPE={!type!}");
+) TYPE={!_TABLE_TYPE!}");
 
-       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}user_data`");
-       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}user_data` (
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_data`");
+       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}_user_data` (
   `userid` BIGINT(22) NOT NULL AUTO_INCREMENT,
   `surname` VARCHAR(255) NOT NULL DEFAULT '',
   `family` VARCHAR(255) NOT NULL DEFAULT '',
@@ -74,42 +74,47 @@ INDEX (`cat_id`)
   `birth_day` CHAR(2) NOT NULL DEFAULT '01',
   `birth_month` CHAR(2) NOT NULL DEFAULT '01',
   `birth_year` VARCHAR(4) NOT NULL DEFAULT '1970',
-  `password` VARCHAR(32) NOT NULL DEFAULT '',
-  `max_mails` INT(7) NOT NULL DEFAULT 0,
-  `receive_mails` INT(7) NOT NULL DEFAULT 0,
+  `password` VARCHAR(255) NOT NULL DEFAULT '',
+  `max_mails` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
+  `receive_mails` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `refid` BIGINT(22) NOT NULL DEFAULT 0,
   `status` ENUM('UNCONFIRMED','CONFIRMED','LOCKED') NOT NULL DEFAULT 'UNCONFIRMED',
-  `user_hash` VARCHAR(32) NOT NULL DEFAULT '',
+  `user_hash` VARCHAR(255) NULL DEFAULT NULL,
   `REMOTE_ADDR` VARCHAR(15) NOT NULL DEFAULT '0.0.0.0',
   `last_online` VARCHAR(10) NOT NULL DEFAULT 0,
   `last_module` VARCHAR(255) NOT NULL DEFAULT '',
   `ref_clicks` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `total_logins` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `gender` ENUM('M','F') NOT NULL DEFAULT 'M',
-  `used_points` FLOAT(22,3) NOT NULL DEFAULT '0.000',
+  `used_points` FLOAT(20,5) UNSIGNED NOT NULL DEFAULT 0.00000,
   `emails_sent` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `joined` VARCHAR(10) NOT NULL DEFAULT 0,
   `last_update` VARCHAR(10) NOT NULL DEFAULT 0,
   `last_profile_sent` VARCHAR(10) NOT NULL DEFAULT 0,
   `notified` ENUM('Y','N') NOT NULL DEFAULT 'N',
   `ref_payout` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
+  `last_login` VARCHAR(10) NOT NULL DEFAULT 0,
+  `login_failures` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
+  `last_failure` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY  (`userid`),
-INDEX (`refid`)
-) TYPE={!type!}");
+UNIQUE (`user_hash`),
+INDEX (`refid`),
+INDEX `status_mails` (`status`,`max_mails`)
+) TYPE={!_TABLE_TYPE!}");
 
-       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}user_points`");
-       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}user_points` (
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_points`");
+       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}_user_points` (
   `id` BIGINT(22) NOT NULL AUTO_INCREMENT,
   `userid` BIGINT(22) NOT NULL DEFAULT 0,
   `ref_depth` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
-  `points` FLOAT(22,3) NOT NULL DEFAULT '0.000',
-  `locked_points` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
+  `points` FLOAT(20,5) UNSIGNED NOT NULL DEFAULT 0.00000,
+  `locked_points` FLOAT(20,5) UNSIGNED NOT NULL DEFAULT 0.00000,
 PRIMARY KEY  (`id`),
 INDEX (`userid`)
-) TYPE={!type!}");
+) TYPE={!_TABLE_TYPE!}");
 
-       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}user_links`");
-       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}user_links` (
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_links`");
+       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}_user_links` (
   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   `stats_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `userid` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
@@ -117,28 +122,40 @@ INDEX (`userid`)
 PRIMARY KEY  (`id`),
 INDEX (`userid`),
 INDEX (`stats_id`)
-) TYPE={!type!}");
+) TYPE={!_TABLE_TYPE!}");
 
-       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}user_stats`");
-       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}user_stats` (
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_stats`");
+       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}_user_stats` (
   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   `userid` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `cat_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `payment_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `subject` VARCHAR(200) NOT NULL DEFAULT '',
-  `url` TINYBLOB NOT NULL,
+  `url` TINYTEXT NOT NULL,
   `max_rec` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `timestamp_ordered` VARCHAR(10) NOT NULL DEFAULT '',
   `pool_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   `timestamp_sstart` VARCHAR(10) NOT NULL DEFAULT '',
   `timestamp_send` VARCHAR(10) NOT NULL DEFAULT '',
-  BIGINT(20) UNSIGNED NOT NULL,
+  `is_stats` ENUM('Y','N') NOT NULL DEFAULT 'N',
 PRIMARY KEY  (`id`),
 INDEX (`userid`),
 INDEX (`cat_id`),
 INDEX (`payment_id`),
 INDEX (`pool_id`)
-) TYPE={!type!}");
+) TYPE={!_TABLE_TYPE!}");
+
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_stats_data`");
+       ADD_EXT_SQL("CREATE TABLE `{!_MYSQL_PREFIX!}_user_stats_data` (
+`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
+`userid` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
+`stats_type` VARCHAR(255) NOT NULL DEFAULT 'unknown',
+`stats_data` VARCHAR(255) NOT NULL DEFAULT '',
+`inserted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+PRIMARY KEY(`id`),
+INDEX (`userid`),
+INDEX (`stats_type`)
+) TYPE={!_TABLE_TYPE!} COMMENT='Member statistics data'");
 
        // Admin menu
        ADD_EXT_SQL("INSERT INTO `{!_MYSQL_PREFIX!}_admin_menu` (`action`,`what`,`title`,`descr`,`sort`) VALUES ('user',NULL,'Mitglieder-Management','Mitglieder freischalten, sperren, Accounts editieren, Neuanmeldungen verwalten, {!POINTS!} gutschreiben und abziehen und und und...',3)");
@@ -157,11 +174,12 @@ case "remove": // Do stuff when removing extension
        // SQL commands to run
        ADD_EXT_SQL("DELETE LOW_PRIORITY FROM `{!_MYSQL_PREFIX!}_admin_menu` WHERE `what` IN('config_user','user_contct','list_user_del') OR `action`='user'");
        ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_del`");
-       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}user_cats`");
-       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}user_data`");
-       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}user_points`");
-       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}user_links`");
-       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}user_stats`");
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_cats`");
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_data`");
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_points`");
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_links`");
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_stats`");
+       ADD_EXT_SQL("DROP TABLE IF EXISTS `{!_MYSQL_PREFIX!}_user_stats_data`");
        break;
 
 case "activate": // Do stuff when admin activates this extension