View this PageEdit this PageAttachments to this PageHistory of this PageHomeRecent ChangesSearch the SwikiHelp Guide
Hotspots: Admin Pages | Turn-in Site |
Current Links: Cases Final Project Summer 2007

Streamlining application development without using GLORP - Eugen Istoc

Full version of article:


GLORP is a Generic Lightweight Object-Relational Persistence mapping layer, and it is very beneficial on large scale projects. In this CS2340 class, however, there is a compromise in using GLROP in that the time needed to satisfy the learning curve might not be worth the trouble in the time span provided for the project, even though there is an increase in productivity once the GLORP framework is well understood. For quick deployment, however, I will assume that the reader chooses not to use GLROP, but to manually run and execute each query. I will present a strategy that will result in a useful database abstraction, and a well organized query execution schema.

The idea behind this approach revolves around these principles:

The Database class

The initialize method of the Database class establishes a connection to the database and saves that connection session in an instance variable. In our case db. Keep in mind, that from this point on, anytime we refer back to the Database, we are talking about the instance that was created in this initialize method. For the initialize method below, you need to modify the username, and the password fields to match your database login credentials. If you choose to use the Postgres database, which Cincom provides for free, you may use the same environment as the example below.

	"Initialize a newly created instance. This method must answer the receiver."
	| dbHandle |
	super initialize.
	"Establish a database connection"
	dbHandle := PostgreSQLEXDIConnection new.
		username: '[YOUR USERNAME]';
		password: '[YOUR PASSWORD]';
		environment: ''.
	dbHandle connect.
	"Create database session"
	db := dbHandle getSession.

Overview of the Singleton pattern

As stated earlier, the goal is to have the database connection available throughout the application. This is very beneficial, because you wouldn't have to worry about establishing a database connection every time you need to execute a query and, in fact, chances are that you will be executing quite a lot of queries. Toward the end of the project you will fully realize the benefit of the singleton pattern.

There 2 important method to make this object operate as a Singleton. First, we must make a class method, and a class variable to return the instance of the class. The method looks like this:

	instance ifNil: [instance := super new initialize].

This method assumes that there is a class variable instance, which contains an instance of the Database class. When you first run your application, to get an instance of the Database object, typically you would do the following:

db := Database getInstance.

As you see from the :getInstance method, we first check to see if an instance already exists. Remember, because instance is a class variable, it is saved as part of the visualworks image. Running the code above the first time, will behave as expected, but every time you try to execute the code above after the first time, you will notice that you get a variation of "Unhandled exception: Operating System Error". To save you from hours of debugging, you need to understand what is happening. In the :getInstance method above, notice we are checking to see whether instance (a class variable)is nil. Because, instance, is a class variable, it survives after the termination of the initial execution, yet the instance which it was holding is destroyed. When you attempt to re execute the code, however, instance is no longer nil, nor does it contain a reference to an instance of the database class. Your question might be "what does it hold then?". As stated earlier, because instance once held a reference to a Database object, if the object has been destroyed, it then holds a reference to the Database class. Remember, a class is very much different that an object. So the reason that the :getInstance method fails miserably, is because instance is never nil, thus an object is never created.

To solve the problem of persistent class variables, we must simply reset that instance variable to nil every time we run our application. To achieve that, we can just make another class method which its sole purpose is to set the instance class variable to nil, and we just need to be sure to call this method first.

   instance := nil.

NOTE: Its important that this is a class method

Now, we can modify the initial code we used to get an instance of the database to the following:

Database reset.
db := Database getInstance.

NOTE: We only need to reset our Database class one time. Typically, you can reset it in the class that serves as the entry point for your application.

Abstracting the CRUD (Create Read Update Delete)

One of the benefits of making the Database object a singleton is that we can now create generic create, select, update, and delete operations. The idea behind this generic methods, is that they must be able to adapt to as many objects in the application as possible. For example, they should be able to save to a table of 2 attributes, and one of 20 attributes. We have found that the best way to accomplish this is to create methods that take in dictionaries as inputs, after which we can loop through these dictionary to perform the necessary operations. Note, that these generic methods are part of the Database instance. Lets examine a generic method for updating a table.

save: aTable where: conditions change: updates
	"I update aTable filtered with the conditions. I set the values to the ones defined in updates"
	| whereClause setClause counter |
	whereClause := ' WHERE '.
	setClause := ' SET '.
	counter := 0.
	conditions keysAndValuesDo: 
			[:field :value |
			whereClause := whereClause , field , ' = ' , value.
			counter := counter + 1.
			counter = conditions size ifFalse: [whereClause := whereClause , ' AND ']].
	counter := 0.
	updates keysAndValuesDo: 
			[:field :value |
			setClause := setClause , field , ' = ''' , value , ''''.
			counter := counter + 1.
			counter = updates size ifFalse: [setClause := setClause , ', ']].
	Database getInstance setQuery: 'UPDATE "' , aTable , '"' , setClause , whereClause

In the example above, the input variables conditions, and updates are Dictionaries. Essentially what we are doing, is we are concatenating all the key, value pairs in the conditions variable with an "AND", and we are concatenating the key, value pairs in the updates variable with "," (comma). Finally we
build our SQL statement, and we execute it (we will cover the the execution process later in this article).

Let us consider that there exists an object containing the following properties which we we need to save to the database:

	id := 3.
	username := 'john'.
	password := 'john31'.
	firstName := 'John'.
	lastName := 'Doe'.
	school := 'Georgia Tech'.

Assume that each of the object properties have their respective setter and getter methods. In order to save this object we need to get an instance of the database object, build our dictionary of conditions, our dictionary of changes, and finally send everything to the :save method, which will take care of executing the query for us.

	|conditions updates |
	conditions := Dictionary new.
	updates := Dictionary new.
	"Set the conditions"
	conditions at: 'id' put: id.
	"Set the fields to update"
	updates at: 'username' put: username.
	updates at: 'password' put: password.
	updates at: 'firstName' put: firstName.
	updates at: 'lastName' put: lastName.
	updates at: 'school' put: school.
	"Send the updates and conditions to the Database instance"
	Database getInstance save: 'User' where: conditions change: updates.

Notice that we don't necessarily have to add all the fields to the updates dictionary. This object only updates what it wants. There is very loose coupling between this User object and the Database object. All that the Database object is concerned about is that it receives a dictionary of the updates, and one of the conditions. These dictionaries maybe of length 1, but they must be dictionaries. The resulting SQL that is generated by the generic update method is (assuming sample object data):

UPDATE "Users"
SET username = 'john', password = 'john31', firstName = 'John', lastName = 'Doe', school = 'Georgia Tech' 
WHERE id = 3

We'll leave it as an exercise for you to write the generic methods for insert, delete, and, select.

Controlling query execution

By now, you should be able to use your generic methods to select, update, delete, and insert data into the database. These methods will mostly be used when working with object persistence. For example, saving a user, updating a course's information, etc. There are situations, however, when you must execute special queries with a distinctly formatted result. For example, if you wish to generate a report of the average grades for a particular section, you cannot simply use the generic select method that you have made to satisfy the needs of the report. In these kind of situations, you must make available a method that just takes in a raw query string, and executes it as is.

To assist in this, our strategy was to have a single method that actually queries the database :setQuery. This method takes in as a parameter just a query string, and simply executes against the Database session. The method looks like this:

setQuery: aQuery
	"I exectue aQuery. I keep a reference of the query for logging purposes (planned)"
	query := aQuery.
	db prepare: aQuery.
	db execute.

Notice that we first store the query in a instance variable query. Although this is optional, it can be helpful as you're designing your application to output the last query that was executed, for debugging purposes. Furthermore, if you're considering of implementing security logs, this is very helpful of getting each query and logging it, as well as the user that initiated that query.


By using this strategy to abstract the database operations, it will quickly streamline the rest of the application development process. Keep in mind that his design is very flexible. For example, you may add more help methods, apart from the suggested ones that satisfy only the CRUD operations. If your particular application demonstrates a trend in the way it in which queries the database, it would be a good idea to include that trend into the abstraction.

Lastly, coupling this database layer with other singleton objects (Security, or Session), is fairly straight forward, as all that you would need to do is hold references to the correct instances. In our particular implementation, there was an excessive amount of coupling between these 3 objects, so if there is one thing we could change, is to decrease the dependencies among them. It is very easy to fall into the trap of making singleton objects very dependent on other singleton objects because of the idea they they will exist throughout the application, but you will encounter problems, because there is an increase in circular dependencies, which might be hard to spot. With that in mind, have fun with small talk. If you would like to checkout our code, its available in the repository under the name "CMS_Team_Tech"

Links to this Page