
 =======================================
 Bookmark4U Database Upgrade Instruction
 =======================================

 Notice!
 -=-=-=-
  o Please read INSTALL.txt first if you want to upgrade your
    Bookmark4U to a higher version.

  o Backup your bookmark database before upgrading it.
    In MySQL, you can backup your database as follow:
    # mysqldump [-u user] [-p] database > backup.sql

    You can also use mysqlhotcopy command to make a backup of a database.
    # mysqlhotcopy db_name [/path/to/new_directory]

    Or, use the following commands in the mysql prompt to backup a table
    sql> BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory'

  o If you want to keep your inc/config.php file, add inside itself : 
      define(SETUP_DONE, 1);
    
 Version 2.1.0
 -=-=-=-=-=-=-
 o In a view to have better performances, execute this query :
       ALTER TABLE bk4u_bookmark ADD INDEX (uid, id, parent);
       ALTER TABLE bk4u_bookmark ADD INDEX (uid, type);
    
 Version 2.0.0
 -=-=-=-=-=-=-
 o By default, each table name is prefix with bk4u_.
   You can change it in inc/config.php :
      $conf[dbtbl_prefix] = "bk4u_";       # prefix all table names
   and repeat all the declaration below :
      $conf[dbtblpasswd]    = $conf[dbtbl_prefix]."passwd";
      $conf[dbtblpasswdtmp] = $conf[dbtbl_prefix]."passwd_temp";
      $conf[dbtblconfig]    = $conf[dbtbl_prefix]."config";
      $conf[dbtblcalendar]  = $conf[dbtbl_prefix]."calendar";
      $conf[dbtbltrash]     = $conf[dbtbl_prefix]."trash";
      $conf[dbtbladdress]   = $conf[dbtbl_prefix]."address";
      $conf[dbtblmemo]      = $conf[dbtbl_prefix]."memo";
      $conf[dbtblbookmark]  = $conf[dbtbl_prefix]."bookmark";
     
   Now, table names and prefix are in lowercase by default. 
   If you want to continue to use uppercase, redefine them in config.php.

   You can change the database table name using the following mysql command:
   sql> RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2,...]

   Rename the database tables name:
   sql> RENAME TABLE PASSWD TO bk4u_passwd;
   sql> RENAME TABLE PASSWD_TEMP TO bk4u_passwd_temp;
   sql> RENAME TABLE CONFIG TO bk4u_config;
   sql> RENAME TABLE CALENDAR TO bk4u_calendar;
   sql> RENAME TABLE MEMO TO bk4u_memo;
   sql> RENAME TABLE ADDRESS TO bk4u_address;
   sql> RENAME TABLE TRASH TO bk4u_trash;

   Database changes in v2.0.0 :
   +------------------+-------------------+------------------------+
   | Previous version | Bookmark4U v2.0.0 | Changes                |
   +------------------+-------------------+------------------------+ 
   | PASSWD           | bk4u_passwd       | no change              |
   | PASSWD_TEMP      | bk4u_passwd_temp  | no change              |
   | CONFIG           | bk4u_config       | no change              |
   | CALENDAR         | bk4u_calendar     | no change              |
   | MEMO             | bk4u_memo         | no change              |
   | ADDRESS          | bk4u_address      | no change              |
   | TRASH            | bk4u_trash        | CHANGED                |
   | BM4U_<username>  | bk4u_bookmark     | MERGED                 |
   +------------------+-------------------+------------------------+

 o The trash table as been changed to manage all attributes.
   The page admin/config.php must do all the work automatically.
     
   Manually, use:
     RENAME TABLE TRASH to trash;
     ALTER TABLE trash ADD COLUMN icon CHAR(80);
     UPDATE trash SET icon='';
     ALTER TABLE trash ADD COLUMN rate INTEGER;
     UPDATE trash SET rate='0';
     ALTER TABLE trash ADD COLUMN lnid INTEGER;
     ALTER TABLE trash ADD COLUMN uid INTEGER NOT NULL;
     ALTER TABLE trash ADD COLUMN rtime INTEGER DEFAULT -1;
     UPDATE trash SET rtime='-1';
     ALTER TABLE trash ADD COLUMN chkdate DATETIME;
     ALTER TABLE trash CHANGE `id` `id` INTEGER NOT NULL;

     # repeat for all users
     UPDATE trash SET uid=1 WHERE user='userA';
     UPDATE trash SET uid=2 WHERE user='userB';
     ...

     ALTER TABLE trash ADD INDEX (uid, id);
     ALTER TABLE trash DROP COLUMN user;
     RENAME TABLE trash to bk4u_trash;
    
 o All users' bookmarks are inside only one table from v2.0.0.
   A new table 'bk4u_bookmark' as been add to centralize all bookmarks.
   Refer to sql/db.*.sql for the definition of bookmark table.
   Be careful! Make a backup of your database before update.

   The page admin/config.php must do all the work automaticly.

   To upgrade manually, do followings:

   - Make bk4u_bookmark table by executing
       CREATE TABLE bk4u_bookmark ( ... );
     command in the sql/db.mysql.sql

   - Repeae for each user :

       INSERT INTO bk4u_bookmark (id, uid, type, parent, title, url, comment,
          rdate, visit, public, lastvisit, lastwhere, total,
          icon, rate, lnid, rtime, chkdate)
         SELECT id, bk4u_passwd.uid, type, parent, title, url, comment,
          BM4U_<username>.rdate, visit, public, lastvisit, lastwhere, total,
          icon, rate, lnid, rtime, chkdate
         FROM BM4U_<username>, bk4u_passwd
         WHERE bk4u_passwd.user='<username>';
       DROP TABLE BM4U_<username>; 

     where <myusername> is the login name of the user.

 o Two new options are added to improve performance : 
     $conf[cache_enable] = 1;   # if true, activate cache system
                                # on list of bookmarks
     $conf[generated_css] = 1;  # if true, css style are generated in temp
                                # dir. Else, css is write inside html
   They need to write into the temp/ directory. So, change the permission
   (with FTP or a shell) of this directory to 755.
 
  o Remember to add " define(SETUP_DONE, 1); " inside inc/config.php if you want to keep
    your configuration without erase it by setup wizard. 

 Version 1.8.4
 -=-=-=-=-=-=-
   o No requirement to change the database
   o Bug Fix for v1.8.3 and v1.8.2
     http://bookmark4u.sourceforge.net/board/read.php?table=board&no=485

 Version 1.8.3
 -=-=-=-=-=-=-
   o No requirement to change the database

 Version 1.8.2
 -=-=-=-=-=-=-
   o Add MEMO table into database.
     Refer to sql/db.*.sql for the definition of MEMO table.

 Version 1.8.1
 -=-=-=-=-=-=-
   o Add ADDRESS table into database.
     Refer to sql/db.*.sql for the definition of ADDRESS table.

 Version 1.8.0
 -=-=-=-=-=-=-
   o From this version, some fields are added into users' bookmark tables.
    There are two methods to update database table structure.
    One way is to use mysql command line tool, and the other is to use
    database configuration tool. The latter is strongly recommended.

   (Manually)

     To modify user's table structure:

     mysql> select user from PASSWD;
     +-------+
     | user  |
     +-------+
     | user1 |
     | user2 |
     +-------+
     mysql> ALTER TABLE [BM4U_]user1 ADD COLUMN icon CHAR(80);
     mysql> UPDATE [BM4U_]user1 SET icon='';
     mysql> ALTER TABLE [BM4U_]user1 ADD COLUMN lnid INTEGER;
     mysql> ALTER TABLE [BM4U_]user1 ADD COLUMN uid INTEGER;
     mysql> ALTER TABLE [BM4U_]user1 ADD COLUMN rtime INTEGER DEFAULT -1;
     mysql> UPDATE [BM4U_]user1 SET rtime='-1';

     where [BM4U_] is the user table prefix.

     Repeat this modification for all users(user1, user2) in the PASSWD table.


   (Using Database Configuration Tool)

     Open http://yourhost/path/to/bm4u/admin/config.php page.
     In the step 5, the tool checks users' bookmark tables in the database.
     If some tables require to be upgraded, error messages are printed:

       Step 5. Checking users' bookmark tables ... 
       Checking user1's bookmark table(BM4U_user1) ... Fail.
       Checking user1's bookmark table(BM4U_user1) ... Fail.
       You have problems in some users' database tables

     followed by SQL command execution blocks.
       +------------------------------+
       | SQL commands                 +
       +------------------------------+
             [[ Execute Above ]]

  Click the 'Execute Above' button one after another.
  After updating all tables, you can see the followin message.

    Congratulation!! You have successfully configured Bookmark4U!!


 Version 1.7.4
 -=-=-=-=-=-=-
   o No requirement to change the database

 Version 1.7.3
 -=-=-=-=-=-=-
   o No requirement to change the database

 Version 1.7.2
 -=-=-=-=-=-=-
  As an administrator, you can accept or deny of a creation of account.
  To use this feature, set the following variable in your inc/config.php

    $conf[registration_verify] = 1;

  And you are required to add an additional table(PASSWD_TEMP)
  and a row in the PASSWD table for the administrator.

  Refer the sql/db.mysql.sql for the definition of PASSWD_TEMP table.

  # temporary password table (for un-allowed user, same definition with PASSWD)
  CREATE TABLE PASSWD_TEMP (
    user      CHAR(20) NOT NULL PRIMARY KEY,    # user login name
    uid       INTEGER NOT NULL AUTO_INCREMENT,  # user ID (integer)
    passwd    CHAR(20),    # encrypted password
    name      CHAR(40),    # user real name
    email     CHAR(50),    # email address
    lastlogin DATETIME,    # last login time
    lastip    CHAR(15),    # last login IP
    logincnt  INTEGER,     # login counter
    rdate     DATETIME,    # registration date and time
    KEY(uid)
  );

  Add a row for administrator into PASSWD table with initial password 'test'.

  INSERT INTO PASSWD SET user='admin', uid=0, passwd=PASSWORD('test'),
  rdate=NOW();

  To login as an administrator, open http://.../path/to/bm4u/admin/ page.

 Version 1.7.1
 -=-=-=-=-=-=-
   o No requirement to change the database

 Version 1.7.0
 -=-=-=-=-=-=-
   o Add CALENDAR table into database.
     Refer to sql/db.*.sql for the definition of CALENDAR table.

 Version 1.6.2
 -=-=-=-=-=-=-
   o (optional) Prefix to user bookmark database table name
     If you want to prefix to the user bookmark database table name,
     edit the inc/config.php to set $conf[userdb_prefix].

     inc/config.php:
       $conf[userdb_prefix] = "BM4U_";  # prefix of user bookmark table name
       #$conf[userdb_prefix] = "";      # comment out this line

     And change the database table name for all existing users.

       mysql> select user from PASSWD;
       +-------+
       | user  |
       +-------+
       | user1 |
       | user2 |
       +-------+
       mysql> alter table user1 rename BM4U_user1;
       mysql> alter table user2 rename BM4U_user2;
       mysql> show tables;
       +----------------------+
       | Tables_in_bookmark4u |
       +----------------------+
       | BM4U_user1           |
       | BM4U_user2           |
       | CONFIG               |
       | PASSWD               |
       | TRASH                |
       +----------------------+

     If you don't want this, just set $conf[userdb_prefix] = "".

 Version 1.6.1
 -=-=-=-=-=-=-
   o No requirement to change the database

 Version 1.6.0
 -=-=-=-=-=-=-
   o Add TRASH table into database.
     Refer to sql/db.*.sql for the definition of TRASH.

 Version 1.5.x
 -=-=-=-=-=-=-
   o No requirement to change the database

 Version 1.4
 -=-=-=-=-=-
   o Add CONFIG table into database
     Refer to sql/db.*.sql for the definition of CONFIG,
     and add entries for existing users into CONFIG.

 Version 1.0 - 1.3
 -=-=-=-=-=-=-=-=-
   o No requirement to change the database

-- UPGRADE.txt
