Many-to-many Database Relation in Yii

I have three database tables:

Phonebook - employee contact info.
Council - members of the Unit that are in the advisory council.
Offices - titles of all offices in the council

Council has fields for an employee id and the year of council service (and a primary key).
Offices are titles (and a primary key).
Phonebook has a field for employee id (and a primary key). It also has fields such as name, address, phone.

An index table, council_offices, has been created to link Council to Offices. A person on the council can hold many offices. Several people can have the same title. It has a field for the Council primary key and a field for the Offices primary key (and a primary key). A relation has been created for each field.

Each table is represented as a model in Yii. The relations method is of importance here. The Offices table does not have any relations.

CouncilOffices relations

Each record in the CouncilOffices table has a many-to-one relationship with the Council and Offices tables. Council and Offices are related as many-to-many. The way to implement this is with two many-to-one relationships through an intermediate table.

BELONGS-TO indicates that CouncilOffices is the many side and Council is the one side of the relation. The council_index field links to the primary field of the Council table. Create a foreign key in the council_offices table that links to the primary key from the council table.

A similar relationship exists for Offices.

        return array(
                    'councilRelation'=>array(self::BELONGS_TO, 'Council', 'council_index'),
                    'officesRelation'=>array(self::BELONGS_TO, 'Offices', 'offices_index'),
        );
   
Council relations

Council defines the many-to-many relationship to Offices. Offices does not define any relations. The third parameter defines how the relationship works: It uses the council_offices table, tying the council_index to the offices_index. These names are the actual names from the table, including the table name and field names. It is confusing that Offices is the name of the Yii class for the model, while council_offices is the actual name of the table in the database.

Council also defines a relationship to the phonebook. It should actually be one-to-one, but one-to-many will also work.

        return array(
                    'officeRelation'=>;array(self::MANY_MANY, 'Offices',
                        'council_offices(council_index,offices_index)',
                        ),
                    'phonebookRelation'=>;array(self::BELONGS_TO, 'Phonebook',
                        'id',
                        ),
        );

Create a foreign key from the id field of the council table to the phonebook table. In my case, the foreign key is to an index, not to a primary key. I am using mySql with INNODB tables. Yii does not specify this relationship, it only exists in the actual table. Create an index for the target field in the phonebook table and then use SQL to create the foreign key (phpMyAdmin only allows foreign keys to primary keys).

alter table council add foreign key (id) references phonebook(pid);

Phonebook relations

The Phonebook does not need any relations in Yii. It should have an index for the empolyee id (pid) in the phonebook table.

Form that accesses data

The payoff is accessing the relations. In the example below, $data is the CouncilOffices table.

Using Ping in Windows

The ping command from a Windows command prompt can be used to check the availability of a host:
Windows documentation.

Two options control how many pings to send. The default number is four.

ping -t

Keep sending Echo Requests until interrupted.

ping -n 5

Send the specified number of Echo Requests.

Two options can be used to test the size of the maximum transmission unit. Use one option to specify the size of the message to send and use the other option to prevent the message from being broken into smaller sizes. If the message is too large to send then the router will not send it.

ping -l 1500

Specify the size of the data field in the message to send.

ping -f

Do not fragment the message. The message must be sent as one unit or be rejected.

ping -f -l 1500

Combine both flags to test the size of the transmission unit. Try smaller numbers for the size until the message is sent.

One option can be used to find the time it takes to reach the host. It can also be used to find intermediate relay addresses.

ping -i 10

Specify the time that the message can live - time to live (TTL). If the time expires, then the message will not be sent. It may be that the message makes several hops before the time out is reached. The last name server to process the message may send a response indicating that the request timed out.

Start with a small number for the time to live and increase it until the host can respond.

Start with a TTL of 1 and you will get a response from a close name server to your machine. Increase the TTL by 1 on subsequent requests and you will get responses from different name servers along the route. Some requests may time out, since the name server that received the timed out packet did not forward the failure notification. Other servers will send a message that the request timed out.

The last procedure can also be used to check the name servers along the route. By increasing the TTL, different name servers will respond with messages. The flaw is that name servers are not required to send a response when a message expires. This is the procedure that the tracert command does.

Another option can be used to find the name servers along the route.

ping -r 9

Record the name servers that forward the message. The count must be in the range from 1 to 9.

Padding a numeric field with zeros in Excel

Use the Text function in Excel to pad a column with zeroes.

To pad to seven places, use the formula =TEXT(A1, "0000000").

Copy the values of this new column back to the original column, in the event that the original column is referenced in other formulas.

Converting Excel Date to mySql Date

I read about this trick from
http://blog.mclaughlinsoftware.com/2009/06/16/excel-date-conversion/

The trick is to convert the Excel date to the mySql format using the Text function.

=Text(A1,"yyyymmdd")

I want to keep the original date in the excel sheet, so I created a new column in the table and filled it with the above formula.

Next, I saved the spread sheet and then saved it again as a CSV file.

I removed the column names from the file, copied the column with the formula back to itself as values, and deleted the original date column.

I now have my original Excel file and a copy as a CSV that has the correct date format.

I then imported the CSV file into the mySql table.

Memory Leak Update

I have been using Hibernate with the default C3P0 connection pooling.

I have been receiving PERM GEN errors.

After reading Frank Kievet's blog about memory leaks, I started investigating.
http://frankkieviet.blogspot.com/2006/10/classloader-leaks-dreaded-permgen-space.html
http://frankkieviet.blogspot.com/2006/10/how-to-fix-dreaded-permgen-space.html

I added his code for causing a garbage collection in the perm gen and noticed that a lot of memory was being lost on each redeploy of the web app.

I downloaded the Eclipse, stand-alone Memory Analyzer (MAT).
http://www.eclipse.org/mat/

My steps to find the leak:

1. I ran the web app and used MAT to obtain a heap dump from Tomcat. 
2. I reran the web app again and obtained another heap dump. 
3. I opened the dominator tree for each dump.
4. The second dump contained the web app loader from the first dump. This class is causing the class loader memory leak.
5. In the second dominator tree, I right-clicked the stale web app loader and selected Path to GC Roots (excluding weak references).
6. The references listed are the ones causing the memory leak.

The leaks are usually for a third-party tool. It is important to close these tools properly. The tools I am using are Hibernate, C3P0 and MySQL. Each of these has to be closed properly. The best place for the code is in a SevletContextListener. I used several sites to find this information.
https://hibernate.atlassian.net/browse/HHH-7364
http://stackoverflow.com/questions/11872316/tomcat-guice-jdbc-memory-leak
http://docs.oracle.com/cd/E17952_01/connector-j-relnotes-en/news-5-1-23.html

I also had to update several jar files to newer versions:
hibernate-c3p0-4.1.1.Final.jar
mysql-connecto-java-5.1.28-bin.jar

package shared;

import com.mysql.jdbc.AbandonedConnectionCleanupThread;
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Enumeration;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

@WebListener
public class WebappListener implements ServletContextListener {

    @Override
    public void contextInitialized(ServletContextEvent sce) {
    }

    @Override
    public void contextDestroyed(ServletContextEvent sce) {
        try {
            AbandonedConnectionCleanupThread.shutdown();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        try {
            Enumeration enumer = DriverManager.getDrivers();
            while (enumer.hasMoreElements()) {
                DriverManager.deregisterDriver(enumer.nextElement());
            }
        } catch (java.sql.SQLException se) {
            se.printStackTrace();
        }
        shared.HibernateHelper.closeFactory();
    }
}

The HibernateHelper class is a helper class for using Hibernate. All the methods are static. It has a static variable for the session factory that already exists.

    static public void closeSessionFactory(SessionFactory factory) {
        if (factory != null) {
            if (factory instanceof SessionFactoryImpl) {
                SessionFactoryImpl sf = (SessionFactoryImpl) factory;
                ConnectionProvider conn = sf.getConnectionProvider();
               
                if (conn instanceof C3P0ConnectionProvider) {
                    ((C3P0ConnectionProvider) conn).close();
                }
            }
            factory.close();
        }
    }

    static public void closeFactory() {
        closeSessionFactory(sessionFactory);
    }

The relevant imports for this method are:

import org.hibernate.SessionFactory;
import org.hibernate.internal.SessionFactoryImpl;
import org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider;
import org.hibernate.service.jdbc.connections.spi.ConnectionProvider;

Starting with JSON

I am using jQuery in an application to build a user interface that creates a tree of classes. I want to use that tree as a template in another application that looks for a similar structure.

I am trying to use JSON to save the tree from the first app, read it in the second app, convert it to a Java object and then compare it to the other structure.

I took a quick look at JSON.simple, but I only looked at one post where the advice was to use either Jackson or GSON. I will try GSON first.

Download: http://code.google.com/p/google-gson/downloads/list

Tutorial: http://www.studytrails.com/java/json/java-google-json-parse-json-to-java.jsp

I have successfully completed my task.

1. Created JSON from the object structure using jQuery and javascript.
2. Added the JSON to a hidden field that is submitted to the server application.
3. In the server application using Java and GSON, I read the JSON data and created a class hierarchy from it.

These are the methods that created the JSON from my object structure. There are two structures, TextPart and Group. Group contains TextPart and other Groups. TextPart contains text and another field to indicate if the text is equivalent to another TextPart.
    
    //grab the text, replacing " with \".
    function getTextPartText(textPart) {
        return $.trim($(textPart).children('code').text()).replace(/\"/g, "\\\"");     
    }
  
    //grab the text, replacing " with \". Change to lower case, change space to underscore.
    function getTextPartEquivalent(textPart) {
        var result = $.trim($(textPart).children('var').text().toLowerCase());
        result = result.toLowerCase().replace(/\s/g,'_');
        return result.replace(/\"/g, "\\\"");     
    }
  
    function textPartToJson(textPart) {
        return '{ ' +
                '"textPart" : "' + getTextPartText(textPart) + '"' +
                ', "equivalent" : "' + getTextPartEquivalent(textPart) + '"' +
               '}';
    }
  
    function groupToJson(group) {
        var state, result, children, comma, classAttr, i, radioGroup;
      
        state = "sequential";      
        radioGroup = $(group).children('.radioGroupType');
        if (radioGroup.length > 0) {
            if (radioGroup.children(".groupTypeRandom").prop('checked')) {
                state = "random";
            }
        }
        result = '{ "groupType":"' + state + '", "children": [';
        children = $(group).children();
        comma="";
        for (i = 0; i < children.length; i++) {
            classAttr = $(children[i]).attr('class');
            if ( classAttr === 'group') {
                result += comma + groupToJson(children[i]);         
                comma = ", ";
            } else if ( classAttr === 'textPart') {
                result += comma + textPartToJson(children[i]);                
                comma = ", ";
            } else {
                console.log("warn: skipping object in groupToJson: ", classAttr);
            }
        }
        result += "]}";
        return result;
    }

In the server application, create an equivalent object structure using GSON.

Object processJsonObject(JsonObject object) {
        if (object.has("children")) {
            Group group = new Group();
            group.setGroupType(GroupType.valueOf(object.get("groupType").getAsString().toUpperCase()));
            group.setChildren(processJsonArray(object.getAsJsonArray("children")));
            System.out.print("---start group");
            System.out.println(String.format(" (%s) ---", object.get("groupType").getAsString()));     
            processJsonArray(object.getAsJsonArray("children"));
            System.out.println("---end group---");
            return group;
        } else if (object.has("textPart")) { 
            TextPart textPart = new TextPart();
            textPart.setTextPart(object.get("textPart").getAsString());
            textPart.setEquivalent(object.get("equivalent").getAsString());
            System.out.print(object.get("textPart").getAsString());       
            System.out.println(String.format(", %s", object.get("equivalent").getAsString()));
            return textPart;
        } else {
            return null;
        }
    }
   
    Object[] processJsonArray(JsonArray array) {
        ArrayList list = new ArrayList();
        for (JsonElement element : array) {
            if (element.isJsonArray()) {
                list.add(processJsonArray((JsonArray)element));
            } else if (element.isJsonObject()) {
                list.add(processJsonObject((JsonObject)element));
            } else {
                System.out.println(String.format("No object or array: %s", element));
            }
        }
        return list.toArray();
    }

I added a serialize and deserialize routine for saving the object structure. This allows the structure to be referenced by other applications.

public void serialize() {
      try
      {
         FileOutputStream fileOut =
           new FileOutputStream(context.getRealPath("/WEB-INF/templates/example.rote"));
         ObjectOutputStream out = new ObjectOutputStream(fileOut);
         out.writeObject(getRoot());
         out.close();
         fileOut.close();
         System.out.printf("Serialized data is saved in /WEB-INF/templates/example.rote");
      }catch(IOException i)
      {
         i.printStackTrace();
      }
    }
   
    public void deserialize() {
        try
      {
         FileInputStream fileIn =
           new FileInputStream(context.getRealPath("/WEB-INF/templates/example.rote"));
         ObjectInputStream in = new ObjectInputStream(fileIn);
         setRoot((Group) in.readObject());
         in.close();
         fileIn.close();
      }catch(IOException i)
      {
         i.printStackTrace();
         return;
      }catch(ClassNotFoundException c)
      {
         System.out.println("Example class not found");
         c.printStackTrace();
         return;
      }
    }

In the other application, I read the file that was created by the first application.

Using jQuery

Download jQuery form http://jquery.com/download/

Add a script tag to the HTML page for jQuery. I am using Tomcat, so I am using JSP.

    <script src="${pageContext.request.contextPath}/jquery/jquery-2.0.3.min.js">

Place some jQuery inside another script tag.

        <script>
        $( document ).ready(function() {
            console.log( "ready!" );
        });
        </script>

Found a great tool for playing with jQuery: http://jsfiddle.net/