Two Primary Key design principles

During my years of database design, I found 2 things that always make my life easier when it comes to primary keys:

1. I recommend to always use Surrogate (otherwise known as "dummy") primary keys. Never assign a "meaningful" column to be the primary key of a table. On Oracle, these are implemented by using sequences and before-insert triggers. In Microsoft's world, SQL Server and MS Access have the auto increment columns. I was once involved in a project where the Id Number was the primary key of an an "Individual" table. The designers took the word of the business people that an individual's Id Number never changes, so they assigned this field to the primary key of the table. Then they built a system of 300 tables, and about 100 of those had a foreign key referencing the above table/field. And of course after a couple of years in production, a case came in where an individual had lost their ID Number and a new one was issued. This was a nightmare: We had to write oracle scripts that dropped the foreign keys, do the update and then re create the foreign keys.
   The rule is, as long as the the primary key column has some meaning to the user, then this column may change and databases do not generally like updates on primary key values. There are ways to do it, I know, but why go through the trouble? Just always use Surrogate keys and you will be free from issues like changing or removing records by primary keys.
   Another reason to use surrogate primary keys is if you have some kind of user interface framework that inserts/updates/deletes rows, usually these frameworks work by identifying rows by primary keys. In case you do not have surrogate/dummy keys in your tables, you user interface can seriously mess up your data. Consider this scenario:
   
   1. User requests to edit record where id number equals '1000'. The system finds it by issuing the appropriate sql where clause ( "WHERE ID_NUMBER = 1000") and shows it on the screen.
   2. The user updates the data on the screen, and then mistakenly enters '1001' in the id number text box. She then submits the changes
   3. The system will then update using where clause "where id number='1001'" since it was changed by the user on the screen, and mistakenly update record 1001 instead of 1000! If you thing this is far fetched, think again! It happened within the first couple of days of UAT for a system I was involved in migrating from Informix to Java 2EE/Oracle. We ended up using the Oracle ROWID instead of the primary key to identify records for the user interface. Probably the best solution to begin with, but had the database use surrogate primary keys then we would not have a problem since user interface users would never see or be able to change the primary key value.
2. I recommend to Never use composite primary keys. Even if it makes sense from a normalization point of view, I always use a a SINGLE sequence/auto increment column to be the primary key of the table. This helps greatly when mapping the database table to some kind of Object Relation Mapping tool. Also, if again you will have to use a User Interface framework to manage your rows, having a sigle primary key column helps to keep the where clauses that identify rows short and easy to read and maintain. As an example, consider the case where you have a many to many relationship between an INDIVIDUAL table and a VEHICLES table. In other words, an individual can own many vehicles, and a vehicle can be owned by many individuals.   Additionally, a vehicle cannot be owned by an individual on the same date.  If we use a composite key, we will end up with something like this:
   
   
   
   
   
   With the above design, in order to identify a record for selects updates and deletes, your WHERE clauses have to include all 3 fields that logically define a unique record: SELECT * FROM VEHICLE_OWNER WHERE VEH_ID=:1 and IND_ID=:2 and OWN_START_DATE=:3
   Alternatively, you can use a single surrogate column for the primary key. You can then add a unique constraint to the table on the 2 foreign keys and the OWN_START_DATE field. Here is the design:
   
   
   
   
   With the above design, your WHERE clauses are kept short and maintanable:   SELECT * FROM VEHICLE_OWNER WHERE VEH_OWNER_ID=:1
   In addition, if in the future you introduce another table which needs a foreign key to VEHICLE_OWNER, you can easily extend your design since you only have a single field as the primary key. You simply add the VEH_OWNER_ID to that new table and create the foreign key. Had you use composite fields as the primary key, then you would have to drop the composite primary key, add a new column, populate values and create a single field primary key.

The Single Responsibility Principle (SRP)

Simply put: A class should have one and only one task to perform. Or another way to put it: A class should have one and only one reason to exist. Object Mentor says it (and describes it) best at http://www.objectmentor.com/resources/articles/srp.pdf

The single responsibility principle is one of those things that sounds too theoretical. How can a class have just a single task to do? What if my system has 1000 tasks to be performed, do I have to create 1000 classes? Well, YES :-). And I am here to tell you that this principle works, in practice.

What did I gain by following this principle?

1. Improved the maintainability of my code: Since a class does only one thing, I can touch it w/o breaking any code that does other things. Simple :-)
2. Improved the extensibility of my code:

And the drawbacks?

1. The so called 'Class Explosion'. Your application may end up with too many classes to manage.
2. Yes, I can change code in classes without worrying too much about affecting other code, but first I have to find the code I need to change. And with myriad of classes it can get tricky to pinpoint what you want to change

A case AGAINST using Stored Procedures (and Triggers)

Many programmers are taught (and blindly follow) the "Stored Procedure" principle: If you have to insert/update/delete in a database table from a Java or .NET application, it is desirable to write stored procedures that do the insert/update/delete. The reasons usually given are:

1. Better Performance. The database server parses and compiles the stored procedure text once and re-uses the compiled string for later uses. This applies of course if you are using parametarized sql and you are not (god forbid!) using string concatenation.
2. By using a stored procedure, we allow the database server to also handle business logic, thus relieving the client site from processing.

This above reasons hold water for any RDBMS, albeit Oracle, MS SQL Server, Sybase, etc.

I would argue against using Stored Procedures. The main problems I have with the approach are:

1. The performance argument does not hold water at all. The database server will take the same resources and time to compile the stored procedure sql text, or the plain insert/update/delete sql text. The trick here is to use parameterized sql and not string concatenation.
2. The database is supposed to handle storage, not business processing. It's just not meant for programming logic. That is why we have the "Middleware", to handle our business logic.
3. By moving business logic to a stored procedure, then you are tying your business logic to procedural languages, and you are not taking advantage of Object Oriented programming concepts and techniques.
   The desired approach is to have all your business logic processing in the middle tier, or the client if you are working on a 2-tier system.
4. What about triggers? These are even worse. In my opinion triggers should NOT be used at all in an application. The only reason to use triggers is to get a new sequence number, and this applies only to Oracle. By having your logic dispersed in stored procedures, triggers and client code then you are decreasing the maintainability of your application.

To summarize, my advice is:

• Use plain insert/update/delete statements to update your database from your middleware/client code.
  Do not even create plain stored procedures that just do the insert/update/delete. In the future someone will have the bright idea to start writing business logic code in them. If they are there, they will use them.
• Use Java or any other Object Oriented language to encapsulate and capture ALL of your business logic.

By following the above, you increase the maintainability and extensibility of your application.

Configuring ESAPI for use with a Java Web Application (Java 1.4)

First thing's first: I would strongly advice against applying ESAPI to your web application if you are in production or even at the final stages of testing. Doing so will render all tests mute and you will have to re test the application from the ground up. Securing a web application should be one of the first things to consider, not the last.

ESAPI (The OWASP Enterprise Security API) is a free, open source, web application security control library that makes it easier for programmers to write lower-risk applications. The ESAPI libraries are designed to make it easier for programmers to retrofit security into existing applications or build a solid foundation for new development. If you can figure out these libraries that is :-). If you Google ESAPI Sample Code you wont find much and the documentation provided in ESAPI for Java is incomplete. They do have some general guidelines here: ESAPI Secure Coding Guidelines
So the best way to go is to download the source code from http://owasp-esapi-java.googlecode.com/svn/ and try to figure out from the very thin documentation and some reference implementation classes what to do. Here are the steps that I took to apply the ESAPI libraries to an **existing** web application.

1. For Java 1.4, download code (checkout from svn) from http://owasp-esapi-java.googlecode.com/svn/tags/releases/1.4.0
2. Add owasp-esapi-full-java-1.4.jar and antisamy-bin.1.2.jar to your project classpath.
   Note: From http://code.google.com/p/owaspantisamy/: AntiSamy is a collection of APIs for safely allowing users to supply their own HTML and CSS without exposing the site to XSS vulnerabilities.
3. Create a ESAPI.properties file in the root source directory of your web application. Do not place it in a package inside the root source directory because the DefaultSecurityConfiguration will not find it.
4. Download/Copy the antisamy-esapi.xml file in the root source directory of your web application.
5. Next step is to implement interfaces org.owasp.esapi.User and org.owasp.esapi.Authenticator This is because the default reference implementations use a File as the user database. So you will need to create your own, unless of course you store users in a text file. For our purposes, we store user information in a database table.

   Note that this is not necessary, unless you want to use the ESAPI API for user authentication and authorization. In addition, the org.owasp.esapi.User object carries with it some interesting methods to set and verify CSRF tokens.

6. In the context initializer of your web app, call

   ESAPI.setAuthenticator( new MyUserAuthenticator() );
   

   This will load the properties and print them in the console output, and then set the system Authenticator.
7. Next step is to write and configure an http filter class for your application. Good reference implementation are the java classes in the org.owasp.esapi.filters package. Here's an example that implements the doFilter method of the javax.servlet.Filter:

   public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) 
                     throws IOException, ServletException {
   
           if (!(request instanceof HttpServletRequest)) {
               chain.doFilter(request, response);
               return;
           }
   
           HttpServletRequest hrequest = (HttpServletRequest)request;
           HttpServletResponse hresponse = (HttpServletResponse)response;
   
           // this is necessary on every call
           ESAPI.httpUtilities().setCurrentHTTP(hrequest, hresponse);
           
           // doFilter by wrapping the request and the response to the 
           // ESAPI safe HttpServletRequest and  HttpServletResponse  
           chain.doFilter(new SafeRequest(hrequest), new SafeResponse(hresponse));
   
   }
   
   

   Another good example of an ESAPI filter is  org.owasp.esapi.filters.ESAPIFilter.java
   You need to be careful with the url-pattern of the ESAPIFilter filter. If you use /* then all http requests, including requests for images, css files and JavaScript files included on a page will pass through the filter and probably fail if you have a call to
   ESAPI.authenticator().login(request, response);. The recommendation here is to create a separate directory for your protected JSPs and put them in that directory. Anything else, like JavaScript files, css files and images should go into another folder and not have their requests pass through the filter. Don't forget to also specify url patters of protected servlets that include the secured directory.
8. Deploy and run or debug your web application. You should see the ESAPI properties printed on the console. If you see an error that ESAPI cannot load properties, make sure that the ESAPI properties file resides in the source of your web application code (i.e., in WEB-INF/classes)
9. You may need to modify the ESAPI validator properties. For example, if your application user interface is non-English, then the Validator.HTTPParameterValue pattern will not do:

   Validator.HTTPParameterValue=^[a-zA-Z0-9.\\-\\/+=_ ]*$

   The above will cause ESAPI to throw an IntrusionDetector exception if any of your html fields contain non-English characters. We had to change the above to:

   Validator.HTTPParameterValue=^[\p{L}\p{Nd}0-9.\\-\\/+=_ ]$

   to allow Unicode characters. See below for a list of all ESAPI.properties we had to change and why.
Depending on your application needs, you may need to modify other validation patterns in the ESAPI.properties file. But keep these changes to a minimum. The guys that wrote this code knew what they were doing.
10. And lastly, the hard part: Test, test and then test again. As mentioned above, the Validator.* regular expression patterns defined in ESAPI.properties may cause validation exceptions to be thrown. For example, the default cookie name validation pattern does not allow for dots in the cookie name, but in our case the application server was actually setting a cookie with a dot in the name.

List of ESAPI.properties that you may need to change if running on Oracle Application Server 10g

• Validator.HTTPCookieName: OAS 10g sets a cookie with name "oracle.uix" even if you do not use Oracle UIX. The HTTPCookieName pattern was changed to

  Validator.HTTPCookieName=^[a-zA-Z0-9.\\-_]{0,32}$ 

• Validator.HTTPCookieValue: The "oracle.uix" cookie value in our case was 0^^GMT+2:00 The pattern was changed to

  Validator.HTTPCookieValue=^[a-zA-Z0-9:.\\^\\-\\/+=_ ]*$ 

• Validator.HTTPParameterName: The default ESAPI.properties file allows for a maximum of 32 characters. We changed this to allow for 50:

  Validator.HTTPParameterName=^[a-zA-Z0-9_]{0,50}$ 

Changing the Oracle Character Set after installation

Sometimes when creating an Oracle Database, we forget to choose the correct character set on the relevant dbca screen. Then, when we then go to import data or create data we discover that the character set is not correct. With Oracle 11g, you can actually change it after the database creation. Here is sql plus commands to make this happen:

conn / as sysdba
-------
SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
-------
ALTER DATABASE CHARACTER SET EL8ISO8859P7;
--- EL8ISO8859P7 is greek

-- if the above fails:
ALTER DATABASE CHARACTER SET INTERNAL_USE EL8ISO8859P7;
SHUTDOWN IMMEDIATE;
STARTUP;
 
---if all this is not working run the following command to reload the stylesheet
dbms_metadata_util.load_stylesheets

WebLogic 10.3.5 Clustering Notes

Official Oracle Documentation on WebLogic Clustering is here

1. Click here for the recommended architecture
2. What means of cluster communications are considered ideal?
   The choice is between  Unicast and IP Sockets.  "When creating a new cluster, Oracle recommends that Unicast is used for messaging within a cluster.  Multicast is used only for backwards compatibility".
3. What about IP sockets?
   It seems that "IP sockets provide a simple, high-performance mechanism for transferring messages and data between two applications". Are IP sockets the best performance alternative? It seems to be.
4. We should use the native socket reader implementation
   "For best socket performance, configure the WebLogic Server host machine to use the native socket reader implementation for your operating system, rather than the pure-Java implementation." See here for instructions on how to configure native ip sockets
5. Deployment guidelines:
   1. Even though "partial" deployments are supported, it is better that all Servers in the cluster must are running during deployment
   2. Cluster membership should not change during the deployment process.
6. Load balancing, Software or Hardware?
   1. Software: use of HttpClusterServlet deployed as a front-end application.
   2. Hardware: Clusters that employ a hardware load balancing solution can use any load balancing algorithm supported by the hardware. These can include advanced load-based balancing strategies that monitor the utilization of individual machines. For load balancing hardware instead of a proxy plug-in, it must support a compatible passive or active cookie persistence mechanism, and SSL persistence.
   3. From this page, It seems that Round-Robin Load Balancing is the only method supported for Servlets/Jsps.  All other methods are for EJBs
7. Replication
   Coherence*Web seems to be a good option for Http session replication, particularly for large ADF applications that use jbo application modules.  See this page for more info.
8. Failover
1. Whole Server Migration: In the event of failure, the whole server is migrated to another web logic server in the cluster.  Used for failover of "pinned" services such as JMS and the JTA
2. Service Migration: In the event of failure of a service, the service is migrated to another web logic server in the cluster.

To be continued soon...

Working with the new SQLLocalDB From Microsoft

SQLLocalDB  is a new, lightweight database engine from Microsoft.  You can get it from here.  I have downloaded it and installed the 64 bit version on my Windows 7 laptop.  However, after installation there are no shortcuts created so no gui to manage this "instance".

To start the database engine, open a command line window and type the following:
1.

cd "c:\Program Files\Microsoft SQL Server\110\Tools\Binn"

Substitute for your installation directory.
2. Type:

"SqlLocalDB create "SQL11Local" 11.0 -s"

This will create an sql server instance called "SQL11Local" and start it.
3. Type:

"SQLLocalDB info SQL11Local"

Note the Instance pipe name in the info window.  We will use that to connect to this instance from SQL Server Management Studio

Now start SQL management Studio and in the Server name textbox copy and paste the Instance pipe name from the info command results.  You should connect to the database instance and you can do all the usual things like create , delete and attach databases.