Table of Contents

Create a Partial Table

Create a Partial Table

DB-VA provides the Split Table feature. It allows the user to optimize the size of the database, and minimizes the redundant persistent classes for handling one-to-one identifying relationship.

The following example reverses the phpbb_users table schema to Entity Relationship Diagram, and and splits the phpbb_users table so that user preference and user information are stored in different tables.

The Schema of the phpbb_users table:

# --------------------------------------------------------
#
# Table structure for table 'phpbb_users'
#
CREATE TABLE phpbb_users (
   user_id mediumint(8) NOT NULL,
   user_active tinyint(1) DEFAULT '1',
   username varchar(25) NOT NULL,
   user_password varchar(32) NOT NULL,
   user_session_time int(11) DEFAULT '0' NOT NULL,
   user_session_page smallint(5) DEFAULT '0' NOT NULL,
   user_lastvisit int(11) DEFAULT '0' NOT NULL,
   user_regdate int(11) DEFAULT '0' NOT NULL,
   user_level tinyint(4) DEFAULT '0',
   user_posts mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
   user_timezone decimal(5,2) DEFAULT '0' NOT NULL,
   user_style tinyint(4),
   user_lang varchar(255),
   user_dateformat varchar(14) DEFAULT 'd M Y H:i' NOT NULL,
   user_new_privmsg smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
   user_unread_privmsg smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
   user_last_privmsg int(11) DEFAULT '0' NOT NULL,
   user_emailtime int(11),
   user_viewemail tinyint(1),
   user_attachsig tinyint(1),
   user_allowhtml tinyint(1) DEFAULT '1',
   user_allowbbcode tinyint(1) DEFAULT '1',
   user_allowsmile tinyint(1) DEFAULT '1',
   user_allowavatar tinyint(1) DEFAULT '1' NOT NULL,
   user_allow_pm tinyint(1) DEFAULT '1' NOT NULL,
   user_allow_viewonline tinyint(1) DEFAULT '1' NOT NULL,
   user_notify tinyint(1) DEFAULT '1' NOT NULL,
   user_notify_pm tinyint(1) DEFAULT '0' NOT NULL,
   user_popup_pm tinyint(1) DEFAULT '0' NOT NULL,
   user_rank int(11) DEFAULT '0',
   user_avatar varchar(100),
   user_avatar_type tinyint(4) DEFAULT '0' NOT NULL,
   user_email varchar(255),
   user_icq varchar(15),
   user_website varchar(100),
   user_from varchar(100),
   user_sig text,
   user_sig_bbcode_uid char(10),
   user_aim varchar(255),
   user_yim varchar(255),
   user_msnm varchar(255),
   user_occ varchar(100),
   user_interests varchar(255),
   user_actkey varchar(32),
   user_newpasswd varchar(32),
   PRIMARY KEY (user_id),
   KEY user_session_time (user_session_time)
);

Reverse phpbb_users Table in DBVA-EC

Assume you have been installed DBVA-EC, the DBVA-EC can help you to reverse the database schema to Entity Relationship Diagram.

Create Project PHPbbReverseUser.
Select Modeling > ORM > Reverse Database... to open the Database to Data Model dialog box.
Select Java language and click Next.
Select the database to convert the table schema. Assume that our phpbb is installed in MySQL database or you can select and DBVA-EC support database to connect. Do not set this database setting as default because you will export that phpbb User table to other database.
1. If it is the first time you use DBVA-EC, it may not have the Adapter File for your selected database. You can use the drop down button to select *Download or Browse the most update driver.
  
  MySQL configuration
```
Driver file : <<MySQL Connector/J 3.1.10>> (Download using DB-VA EC)
Driver : MySQL(Connector/J Driver)
Connection URL : jdbc:mysql://localhost/phpbb
                 (You can change this connection URL to connect to the
                  database with phpbb schema in another machine)
```
Select the phpbb_users table anc click Next.
The Entity Relationship Diagram is created which includes the phpbb_users table.

Split the phpbb_user Table

From the ER Diagram, you can observe that the phpbb_users table contains too much information. You can use the split table feature to reduce the size of the phpbb_users table then it can make the developer easier to read the structure of phpbb_uers.

Select the phpbb_users table and right click to select Split Table from the popup menu.

Select the readable information to be a new split table members.

New Partial Table Name : phpbb_users_Info
Selected Columns       : user_from
                         user_email
                         user_icq
                         username
                         user_password
                         user_lang
                         user_aim
                         user_yim
                         user_msnm

The remaining columns in the original table are mainly the preferences of the user in phpbb_users table.

The split table is created in the ER Diagram.
Select Modeling > ORM > Sync to Class Diagram to Synchronize the Class Diagram from Entity Relationship Diagram.
Select the ORM persistence type.
The Class Diagram is created.
From the class diagram all the attributes are inside the Phpbb_users class. It means any update to the attributes of the Phpbb_users class will be automatically updated to the corresponding table(master or split).

Generate Classes and Export to Database

Select Modeling > ORM > Generate Code... to open the Database Code Generation dialog box.
Select generate Code and Database, Java language and generate Sampe.
Click Database tab, select Export to database and click Database Options button to configure the database.
Select the database to test the split table feature.
For example, create testphpbbuser database in MySQL, select the MySQL database and enter the required information.
Finally click OK to generate the Phpbb_users Java class.
You can see the generated Java classes in DBVA-EC’s Package Explorer.

Test Split Table

The generate Java classes include the sample class. You can modify the sample class to test the split table feature of the Phpbb_users.

Insert the new Phpbb_users instance in CreatePHPbbReverseUserData.java.

public void createTestData() throws PersistentException {
  PersistentTransaction t = PHPbbReverseUserPersistentManager.instance()
    .getSession().beginTransaction();
  try {
    Phpbb_users lUser = Phpbb_usersFactory.createPhpbb_users();
    lUser.setUsername("Edmond");
    lUser.setUser_lang("English");
    lUser.setUser_icq("12345678");
    lUser.setUser_msnm("12345678");
    lUser.setUser_aim("12345678");
    lUser.setUser_yim("12345678");
    lUser.setUser_from("12345678");
    lUser.setUser_email("abc@abc.com");
    lUser.setUser_password("21232f297a57a5a743894a0e4a801fc3");
    		
    //in phpbb_users table attributes
    lUser.setUser_active(true);
    lUser.setUser_timezone(new BigDecimal(323));
    lUser.setUser_dateformat("d M Y h:i a");
    lUser.setUser_posts(10);
    lUser.setUser_sig("Edmond is a nice person");
    lUser.save();
    t.commit();
  } catch (Exception e) {
    t.rollback();
  }
}

Source From: src\CreatePHPbbReverseUserData.java
Notice: Some attributes need to be initialized before Phpbb_users instance call because their corresponding columns do not allow null.

Select the phpbb_users table to read the user perference and select the phpbb_users_info to read the user personal information.
Select from phpbb_users table:
SQL statement : SELECT user_active, user_timezone, user_dateformat, user_sig FROM phpbb_users;

Select from phpbb_users_info:
SQL statement : SELECT * FROM phpbb_users_info;
You can see that the Phpbb_users instance information is stored in different tables.

Retrieve the Phpbb_users from Split Tables

You can modify the RetrieveAndUpdatePHPbbReverseUserData.java to test the read the Phpbb_users instance from the split tables.

Modify the RetrieveAndUpdatePHPbbReverseUserData.java

public void retrieveAndUpdateTestData() throws PersistentException {
  PersistentTransaction t = PHPbbReverseUserPersistentManager.instance()
    .getSession().beginTransaction();
  try {
    Phpbb_users lUser = Phpbb_usersFactory.loadPhpbb_usersByQuery(null, null);
    // Update the properties of the persistent object
    //in phpbb_user_info table attributes
    System.out.println("username : " + lUser.getUsername());
    System.out.println("password : " +lUser.getUser_password());
		
    //in phpbb_users table attributes
    System.out.println("is user active : " + lUser.getUser_active());
    System.out.println("user dateformat : " + lUser.getUser_dateformat());
    lUser.save();
    t.commit();
  } catch (Exception e) {
    t.rollback();
  }
}

Source From: src\RetrieveAndUpdatePHPbbreverseUserData.java

The result of the code executed.

username : Edmond
password : 21232f297a57a5a743894a0e4a801fc3
is user active : true
user dateformat : d M Y h:i a

The data is stored in different tables but it can be retrieved from a single Phpbb_users instance.

Downloads

Complete Sources
- phpbbreverseuser.zip