Database Guide : GLORP Tutorial : Simple Object One Table Example
Simple Object One Table Example
The first example stores Person objects in a single table. The Person class is given below. It has two instance variables and two methods to initialize the instance variables. First, create a new application, GlorpExperiments, and set GlorpMappings as its prerequisite. Create objects in this document in the GlorpExperiments app.
Object subclass: #Person
instanceVariableNames: 'firstName lastName '
classVariableNames: ''
poolDictionaries: ''
 
“Person class methods”
 
first: firstNameString last: lastNameString
^self new
setFirst: firstNameString
last: lastNameString;
yourself
 
“Person instance methods”
 
setFirst: firstNameString last: lastNameString
firstName := firstNameString.
lastName := lastNameString
 
Person>>firstName: anObject
firstName := anObject
Now define the table and the mapping between the table and objects of type Person. This is done in a descriptor class. The class must be a subclass of the class DescriptorSystem. Below is the class declaration. One of the methods will use the DirectMapping class which is in the GlorpMappings application, already in the prerequisite chain.
DescriptorSystem subclass: #GlorpTutorialDescriptor
instanceVariableNames: ''
classVariableNames: ''
poolDictionaries: ''
 
Five methods define the mapping between the table and Smalltalk objects. The methods are allTableNames, constructAllClasses, tableForXXX:, descriptorForYYY:, and classModelForYYY.
The method allTableNames just returns an array of all the tables that the descriptor defines. In this example there is just one table, PEOPLE. The method is given below. Keep in mind that while the database table names may not be case sensitive, Smalltalk strings and method names are case sensitive. Therefore, all references to the table in the descriptor must use the same case for the table name.
GlorpTutorialDescriptor >>allTableNames
^#( 'PEOPLE' )
Now define the table. This is done in a method called tableForXXX: where XXX is replaced with the table name. So in this example the name of the method is called tableForPEOPLE:. The method is given below. Don’t use SQL directly to define the table. This will allow GLORP to generate the correct SQL for whatever database is in use. This means that you need to learn the classes and methods to define tables. This will be done a bit later. Here is the method for the PEOPLE table.
GlorpTutorialDescriptor >>tableForPEOPLE: aTable
aTable createFieldNamed: 'first_name' type: (platform varChar: 50).
(aTable createFieldNamed: 'last_name' type: (platform varChar: 50)) bePrimaryKey.
The next step is to tell the descriptor about the class whose objects will be stored in the table. First have the method constructAllClasses list the classes involved in tables defined in the descriptor. At this point there is only one class. Here is the method:
GlorpTutorialDescriptor >>constructAllClasses
^(super constructAllClasses)
add: Person;
yourself
Now define the mapping between the table and the class. This is done in a method called descriptorForYYY: where YYY is replaced with the name of the class. In our case the method name is descriptorForPerson:. For each instance variable, create a DirectMapping between the instance variable and a column.
GlorpTutorialDescriptor >>descriptorForPerson: aDescriptor
| table |
table := self tableNamed: 'PEOPLE'.
aDescriptor table: table.
(aDescriptor newMapping: DirectMapping)
from: #firstName
to: (table fieldNamed: 'first_name').
(aDescriptor newMapping: DirectMapping)
from: #lastName
to: (table fieldNamed: 'last_name')
Each class needs a model. This is given in the method called classModelForYYY: where YYY is the name of the class. A class model provides information about the class. An example this simple does not need to provide the class model. GLORP will determine the correct values. Examples later in this tutorial use the class model.
classModelForPerson: aClassModel
aClassModel newAttributeNamed: #firstName.
aClassModel newAttributeNamed: #lastName.
Next, connect to the database, create the table and add data to the table.
First create a Login object with the correct database type and login information. Since the connection is to an Oracle database using ODBC, use the OracleODBCPlatform. (See the subclasses of DatabasePlatform for the databases supported by GLORP. This example uses an Oracle database called ‘sample’ defined in the ODBC driver and created when Oracle was installed. It serves to simplify the example as GLORP will not create databases.
login := Login new
database: OracleODBCPlatform new;
username: 'taylor';
password: 'foo';
connectString: 'orcl';
yourself
 
accessor := DatabaseAccessor forLogin: login.
accessor login.
An accessor to the database is enough to interact with the database. Simple execution of SQL strings can be accomplished directly on the accessor. However, the power of GLORP is in the high level interaction with the database. All the high level interaction with the database is done through a session. So here is how to get a session.
session := GlorpSession new.
session system: (GlorpTutorialDescriptor forPlatform: login database).
session accessor: accessor.
Second, create the tables defined in the GlorpTutorial. It is not necessary to create the tables using GLORP. The tables can be created using other tools and then used by GLORP. Indeed there are some database constraints that currently cannot be created using GLORP. If you need such constraints then you need to create the database tables via other means. Regardless of how you create the database tables the GLORP descriptor needs to contain the table definitions.
session inTransactionDo:
[session system allTables do:
[:each |
accessor
createTable: each
ifError: [:error |Transcript show: error messageText]]].
This code will generate the correct SQL to create the tables and execute it on the database. To print the SQL executed to the Transcript, execute the following:
session accessor logging: true.
Third, add some data to the table. The following Smalltalk code adds the data from a Person object to the table. A UnitOfWork1 stores the objects and commits them to the database when the UnitOfWork is committed. Not shown here, but GLORP can start transactions and commit them or roll them back.
session beginUnitOfWork.
person := Person new
setFirst: 'Pete'
last: 'Frost';
yourself.
session register: person.
session commitUnitOfWork.
Next, read some data from the database. Since the example is simple, the following code reads all people from the table.
people := session readManyOf: Person .
Next, add a few more people to the table:
session beginUnitOfWork.
#( 'Sam' 'Jose' 'Rose' )
with: #( 'Olson' 'Chan' 'Menon' )
do: [:first :last |
person := Person new
setFirst: first
last: last;
yourself.
session register: person].
session commitUnitOfWork.
The SQL generated when this was committed is below.
Begin Transaction
INSERT INTO PEOPLE (first_name,last_name) VALUES ('Rose','Menon');
INSERT INTO PEOPLE (first_name,last_name) VALUES ('Jose','Chan');
INSERT INTO PEOPLE (first_name,last_name) VALUES ('Sam','Olson')
(0.032 s)
Commit Transaction
Now that the table has enough entries, select data from the table based on some selection criteria.
foundPerson := session readOneOf: Person where: [:each |
each firstName = 'Jose']
The block appears to be sending a message to a Person object, but it is not. The block is used to generate the SQL statement given below. In the block “each firstName” refers to the name of an instance variable of the Person class. The syntax and semantics of the where: block are covered later.
FROM PEOPLE t1
WHERE (t1.first_name = 'Jose')
A UnitOfWork keeps track of the changes to objects. The following retrieves a Person, changes the first name of the Person and then writes the changes back to the database.
session beginUnitOfWork.
foundPerson := session readOneOf: Person where: [:each | each firstName = 'Jose'].
foundPerson firstName: 'RamJet'.
session commitUnitOfWork
Starting a UnitOfWork records all the objects read from the database. Committing the UnitOfWork writes all the changed objects back to the database.
Committing a UnitOfWork also clears the list of tracked objects. To cause the UnitOfWork to retain its list of objects, use commitUnitOfWorkAndContinue or saveAndContinue instead of commitUnitOfWork.
The following code rolls back a UnitOfWork.
session beginUnitOfWork.
foundPerson := session readOneOf: Person where: [:each | each lastName = 'Chan'].
foundPerson firstName: 'Ham'.
session rollbackUnitOfWork.
When you rollback a UnitOfWork all the objects in the UnitOfWork are also rolled back. That is, all the changes made to the objects are undone. In the above example the object foundPerson has it original first name.
Since it is fairly common to always commit a unit of work there is a short hand version.
session inUnitOfWorkDo:
[foundPerson := session readOneOf: Person where: [:each | each firstName = 'RamJet'].
foundPerson firstName: 'Jose']
The unit of work is automatically committed if no exception is raised when executing the block. If an exception is raised the unit of work is rolled back.
Some Table Details
In the tableForPEOPLE: method, define the table to hold the data from Person objects.
GlorpTutorialDescriptor >>tableForPEOPLE: aTable
aTable createFieldNamed: 'first_name' type: (platform varChar: 50).
(aTable createFieldNamed: 'last_name' type: (platform varChar: 50)) bePrimaryKey.
The above example leads to two questions:
What types can be declared a column and
What modifiers (NULL, NOTNULL, etc) can be used on a column
Defining Column Types
In the method tableForPEOPLE: above, aTable is an instance of DatabaseTable. The method sent to aTable, createFieldNamed: type:, adds a DatabaseField to the table. This represents a column of the table. The instance variable “platform” is an instance of a subclass of DatabasePlatform. In this example it is an instance of OracleODBCPlatform. DatabasePlatform and its subclasses define the types available to a column. DatabasePlatform contains the following methods to define types of a column.
blob
char
char:
character
clob
datetime
inMemorySequence
int4
integer
sequence
smallint
text
timestamp
varChar
varchar
varChar:
varchar:
 
OracleODBCPlatform inherits the methods above and contains the following methods to define types of a column. At least with the OracleODBCPlatform the varchar method with no arguments did not produce useable SQL.
bigint
blob
boolean
clob
date
decimal
double
float
float4
float8
int
int2
int4
int8
integer
numeric
real
sequence
serial
smallint
time
timestamp
timetz
varchar
Most of these are fairly standard database types and map to obvious Smalltalk types.
Column or Type Modifiers
 
GlorpTutorialDescriptor >>tableForPEOPLE: aTable
aTable createFieldNamed: 'first_name' type: (platform varChar: 50).
(aTable createFieldNamed: 'last_name' type: (platform varChar: 50)) bePrimaryKey.
When defining a table in GLORP you can add modifiers to columns. In the example above the last_name column is made the primary key. The DatabaseField class defines the following methods that modify columns:
beLockKey
beNullable:
bePrimaryKey
defaultValue:
isUnique:
type:
beNullable: A false value means the column cannot be null. The default value is true, that is the column can be null.
isUnique: A true value means all values in the column must be unique. The default value is false.
defaultValue: sets the default value of the column if the instance variable mapped to the column is nil. At least when using the OracleODBCPlatform default values don’t use the SQL default value. GLORP inserts the default value before sending the data to the database.
beLockedKey I have no idea what this does ToDo.
bePrimaryKey Declares a column or columns to be the primary key of the table. More on this in the next section.
type: Sets the type of the column. In the examples in this tutorial this method is not used as the types are set with createFieldNamed:type: method on a table.
Here is an example using the different modifiers.
tableForCOLUMN_MODIFIERS: aTable
(aTable createFieldNamed: 'a' type: platform sequence) bePrimaryKey .
(aTable createFieldNamed: 'b' type: (platform varchar: 10)) defaultValue: 'cat'.
(aTable createFieldNamed: 'c' type: platform boolean) beLockKey.
(aTable createFieldNamed: 'd' type: platform float4) beNullable: false.
(aTable createFieldNamed: 'e' type: platform real) isUnique: true.
(aTable createFieldNamed: 'f' type: platform float) isUnique: false.
(aTable createFieldNamed: 'g' type: (platform int))
defaultValue: 5;
beNullable: false
Here is the SQL generated by GLORP from the above table definition.
CREATE TABLE COLUMN_MODIFIERS (
A serial NOT NULL ,
B varchar(10) NULL ,
C boolean NULL ,
D float4 NOT NULL ,
E float4 NULL UNIQUE,
F float4 NULL ,
G int4 NOT NULL ,
CONSTRAINT COLUMN_MODIFIERS_PK PRIMARY KEY (a),
CONSTRAINT COLUMN_MODIFIERS_UNIQ UNIQUE (a)
)
 
Primary Keys
In general, GLORP will not insert an object into a table that does not have a primary key. Link tables and embedded values are exceptions.
Explicit Declaration of Primary Key
In this example you have already used a short cut to declare a column to be a primary key.
GlorpTutorialDescriptor >>tableForPEOPLE: aTable
aTable createFieldNamed: 'first_name' type: (platform varChar: 50).
(aTable createFieldNamed: 'last_name' type: (platform varChar: 50)) bePrimaryKey.
You can also explicitly indicate that a column is a primary key on a table as shown below.
GlorpTutorialDescriptor >>tableForPEOPLE: aTable
| keyField |
aTable createFieldNamed: 'first_name' type: (platform varChar: 50).
keyField := aTable createFieldNamed: 'last_name' type: (platform varChar: 50).
aTable addAsPrimaryKeyField: keyField.
Multi-Column Primary Key
If the primary key spans multiple columns in the table, make each column a primary key as done below.
GlorpTutorialDescriptor >>tableForPEOPLE: aTable
(aTable createFieldNamed: 'first_name' type: (platform varChar: 50)) bePrimaryKey.
(aTable createFieldNamed: 'last_name' type: (platform varChar: 50)) bePrimaryKey.
Auto-Generated Primary Key
It is common to generate a number to be the primary key for a table. You can use a sequence in the database to do this for the Person example. Add an id instance variable to the Person class so it has three instance variables.
Object subclass: #Person
instanceVariableNames: 'firstName lastName id '
classVariableNames: ''
poolDictionaries: ''
Modify the descriptorForPerson: and tableForPEOPLE: methods in the GlorpTutorialDescriptor to include the id information. Make the ID column in the table definition a sequence and the primary key.
GlorpTutorialDescriptor >>descriptorForPerson: aDescriptor
| table |
table := self tableNamed: 'PEOPLE'.
aDescriptor table: table.
(aDescriptor newMapping: DirectMapping)
from: #firstName
to: (table fieldNamed: 'first_name').
(aDescriptor newMapping: DirectMapping)
from: #lastName
to: (table fieldNamed: 'last_name').
(aDescriptor newMapping: DirectMapping)
from: #id
to: (table fieldNamed: 'ID').
 
GlorpTutorialDescriptor >>tableForPEOPLE: aTable
(aTable createFieldNamed: 'ID' type: platform sequence) bePrimaryKey.
aTable createFieldNamed: 'first_name' type: (platform varChar: 50).
aTable createFieldNamed: 'last_name' type: (platform varChar: 50)
Now create the table with the code below. Note the addition of the create sequences. With Oracle, sequences are not created explicitly so the code for the sequences is not needed, but is shown for other databases.
NOTE:
For ODBC, delete the table using DB tools before recreating it below.
 
login := Login new
database: OracleODBCPlatform new;
username: 'taylor';
password: 'foo';
connectString: 'orcl';
yourself.
accessor := DatabaseAccessor forLogin: login.
accessor login.
session := GlorpSession new.
session system: (GlorpTutorialDescriptor forPlatform: login database).
session accessor: accessor.
session inTransactionDo:
[session system platform areSequencesExplicitlyCreated
ifTrue:
[session system allSequences do:
[:each |
accessor createSequence: each
ifError: [:error | Transcript show: error messageText]]].
session system allTables do:
[:each |
accessor createTable: each
ifError: [:error | Transcript show: error messageText]]]
The above code does create the table. It generates the following SQL:
Begin Transaction
CREATE TABLE PEOPLE (ID serial NOT NULL ,FIRST_NAME varchar(50) NULL ,LAST_NAME varchar(50) NULL , CONSTRAINT PEOPLE_PK PRIMARY KEY (ID),
CONSTRAINT PEOPLE_UNIQ UNIQUE (ID))
Commit Transaction
Once the table is created add a Person object using:
session beginUnitOfWork.
person := Person first: 'Roger' last: 'Whitney'.
session register: person.
session commitUnitOfWork
When a session has a registered Person object with a nil value for the id instance variable, GLORP retrieves the next value in the id sequence and sets the id. Once this is done, the object is added to the table. If you create a new Person object with an id, GLORP will try to add it to the table when it is registered with a session and committed. However if you read the object from the database, modify it and then commit it in a unit of work the correct row of the database is updated.
Direct Instance Variable Access or Accessor Methods
When writing data to and reading data from a database, GLORP has to access data in an object’s instance variables. GLORP’s default is to access instance variables directly, bypassing the class’s accessor methods to access the instance variables. There are times when GLORP should use the accessor methods to read or set instance variables. These accessor methods in the Person class are shown below.
Person>>firstName: anObject
firstName := anObject
 
Person>>firstName
^firstName
 
Person>>lastName: anObject
lastName := anObject
 
Person>>lastName
^lastName
 
Person>>id: anObject
id := anObject
 
Person>>id
^id
The class model for the class specifies which instance variables are to be accessed by setter/getter methods. The code below indicates that GLORP is to access the instance variable firstName via the methods Person>>firstName and Person>>firstName:.
GlorpTutorialDescriptor>>classModelForPerson: aClassModel
aClassModel newAttributeNamed: #lastName.
(aClassModel newAttributeNamed: #firstName) useDirectAccess: false.
aClassModel newAttributeNamed: #id.
To tell GLORP to use accessor methods for all instance variables in all classes registered in the GlorpTutorialDescriptor, use the method DescriptorSystem>>useDirectAccessForMapping:
session := GlorpSession new.
descriptor := GlorpTutorialDescriptor forPlatform: login database.
Descriptor useDirectAccessForMapping: false.
session system: descriptor.
session accessor: accessor.
The above method determines at runtime which method of access to use on all classes. To insure that accessor methods are always used you can override the method useDirectAccessForMapping.
GlorpTutorialDescriptor>>useDirectAccessForMapping
^false

1 See Unit of Work pattern pp 184-189 in Fowler [2003]

Last modified date: 05/20/2020