Reading, Writing, Deleting Data
Reading, writing, updating and deleting data are common operations done with a database. Methods to perform these operations are found in api/query protocol of the GlorpSession class.
Reading
A session has the following methods for reading data or objects from the database
readOneOf: | readOneOf:where: |
readManyOf: | readManyOf:where: |
readManyOf:limit: | readManyOf:where:limit |
readManyOf:orderBy: |
The first argument of each method is a class of the objects you wish to read. The where: argument is a block with one argument. This block is used to generate the SQL select statement, but allows you to use objects and methods to specify the selection criteria. The readOneOf: methods will return the first record that satisfies the selection criteria. The readManyOf methods return an array of all objects that satisfy the selection criteria. The limit: argument can be used to restrict readManyOf: to return the first N objects that satisfy the selection criteria. There does not seem to be a simple way to read the objects starting with the N+1 object, so you need to use Query objects to do that (see the section Advanced Reading).
session readManyOf: Person limit: 2.
session readOneOf: Person where: [:each | each firstName = 'Sam'].
session readManyOf: Person orderBy: [:each | each lastName].
The Oracle ODBC platform does not support limits. This can be tested by executing ‘session platform supportsLimit’
Selection Criteria in where clause
The where: block of the read statement is used to generate SQL that is executed on the database. That is, these blocks are a language to specify SELECT statements. As a result there are some restrictions on what can be done in these blocks. The following description is based on examining senders of the messages readManyOf:where: in GLORP’s test cases.
The first restriction is on the methods that can be sent to the argument of the block. Messages sent to the block argument must be:
• The names of instance variables of object we are trying to read and the instance variables must be mapped to fields in the database.
• The messages =, <>, ~=, notNIL, isNIL
Since the block is used to generate SQL, there is no need for accessor methods for the instance variables. So for the following statement to work there is no need for a firstName method in the Person class.
session readOneOf: Person where: [:each | each firstName = 'Sam'].
The messages = or <> can be used to read objects based on objects previously read from the database. In the following example we read the first 4 people who are not Sam.
sam := session readOneOf: Person where: [:each |
each firstName = 'Sam'].
result := session readManyOf: Person where: [:each |
each <> sam] limit: 4.
The SQL generated by the last statement is:
SELECT t1.first_name, t1.last_name, t1.id
FROM PEOPLE t1
WHERE (t1.id <> 2)
That is [:each | each <> sam] generates a comparison based on the primary key of the object sam. If the sam object’s primary key (id) is nil then select statement will be:
SELECT t1.first_name, t1.last_name, t1.id
FROM PEOPLE t1
WHERE (t1.id IS NOT NULL)
The notNIL and isNIL messages are not particularly useful at this level. The notNIL or isNIL generates IS NULL or IS NOT NULL tests on the primary key of the table. The result is either the entire table or no elements. The following statement will read all the Person objects in the database.
result := session readManyOf: Person where: [:each | each notNIL].
The second restriction is what messages can be sent to instance variables in the block. Given that we currently only have one table the messages we can send are:
• Binary comparison operators: <, >, =, ~=, >=, <=, <>
• like:
• isNIL, notNIL
• Methods in the api protocol of ObjectExpression
We will add to this list when we have objects that are more complex and span multiple tables in the database. See the end of the section Association (Tie)Tables – Many-to-many.
We have already seen examples of using the binary operators. Here are a few more:
result := session readManyOf: Person where: [:each | each id >= 1].
numbers := #( 1 2 3).
result := session readManyOf: Person where: [:each | each id >= numbers first].
In the example below, the like method generates SQL select statements using SQL’s LIKE. The following reads all Person objects whose first name starts with S. The character “_” matches any single character. The character “%” matches any sequence of characters. In some databases LIKE is case sensitive (PostgreSQL for example) in others it is not (MySQL for example).
result := session readManyOf: Person where: [:each | each firstName like: 'S%' ].
The above statement generates the following SQL:
SELECT t1.first_name, t1.last_name, t1.id
FROM PEOPLE t1
WHERE (t1.first_name LIKE 'S%')
The following statement generates the NOT LIKE clause.
result := session readManyOf: Person where: [:each | (each firstName like: 'S%') not ].
The methods isNIL and notNIL are used to find object where a given column in the database is null or not. The following statement returns all people in the database whose first name is not null in the
result := session readManyOf: Person where: [:each | each firstName notNIL].
These operations can be combined with or, and negation messages. Here are a few such statements.
session
readManyOf: Person
where: [:each | (each firstName notNIL) & ( each lastName like: 'Whit%')].
session
readManyOf: Person
where: [:each | (each firstName notNIL) and: [each lastName like: 'Whit%']].
The methods in the api protocol of ObjectExpression are rather specialized so will not be covered in this tutorial. Look for senders of those messages in the GLORP test cases to see how they are used.
Deleting
You can delete an object via GlorpSession>>deleteAll:. Below is an example of deleting an object. First we get the object from the database. The delete: method removes the object’s data from the database.
foundPerson := session readOneOf: Person where: [:each | each firstName = 'Roger'].
session delete: foundPerson
When done this way the object is deleted immediately. Doing a delete inside a UnitOfWork allows you to rollback a delete.
session beginUnitOfWork.
foundPerson := session readOneOf: Person where: [:each | each firstName = 'Roger'].
session delete: foundPerson.
session rollbackUnitOfWork. “or session commitUnitOfWork”
The delete method on a session checks to see if it is in a UnitOfWork. If it is the object to delete is registered with the UnitOfWork. If it is not in a UnitOfWork is started, the object registered and then the UnitOfWork is committed.
Rereading
When we read data from a database the data exists in two places: in the database and in memory. If another thread or program modifies the database, the data in memory will be different than what is in the database. The method GlorpSession>>refresh: will update an object with the current data in the database.
foundPerson := session readOneOf: Person where: [:each | each firstName = 'Sam'].
“some time passes”
session refresh: foundPerson
Read Only Mappings
In our descriptor for a class we define mappings from the instance variables of a class to columns in a table. The default is to have the mapping both read and write. That is, the mapping can be used to read data from and write data to the column in the table. There are times when the mapping should be read only. That is, the mapping can only be used to read data from the column, but cannot write to the column. In the descriptor below the mapping for the first name is made read only.
We dropped and recreated the table after making the change below.
GlorpTutorialDescriptor >>descriptorForPerson: aDescriptor
| personTable |
personTable := self tableNamed: 'PEOPLE'.
aDescriptor table: personTable.
firstNameMapping := (aDescriptor newMapping: DirectMapping)
from: #firstName
to: (personTable fieldNamed: 'first_name').
firstNameMapping readOnly: true.
(aDescriptor newMapping: DirectMapping)
from: #lastName
to: (personTable fieldNamed: 'last_name').
(aDescriptor newMapping: DirectMapping)
from: #id
to: (personTable fieldNamed: 'ID').
While this demonstrates how to make a mapping read only, in this example you would want either all mappings read only or all mappings read-write. As it stands now the first name field would null for the newly added row when one tries to add a
Person object to the database with:
session beginUnitOfWork.
person := Person first: 'Roger' last: 'Whitney'.
session register: person.
session commitUnitOfWork
If you make all the mappings read only then when you try to add a person no rows will be added to the table. Read only mappings are more useful with more complex mappings.
Transactions
GLORP supports transactions. Operations done inside of a transaction can be committed or rolled back as is normal with transactions. There are two ways to explicitly use a transaction. The inTransactionDo: method will wrap the block in a transaction. If an exception is raised in the bock the transaction is rolled back. Otherwise the transaction is committed. The block can have either no or one argument. If the block does have an argument, it is passed a reference to the session.
session inTransactionDo: [ put code here]
session inTransactionDo: [:aSession | put code here]
There are times when more control over committing or rolling back a transaction in needed. In that case, explicitly start the transaction with the beginTransaction and either commit with commitTransaction or roll back with rollbackTransaction.
session beginTransaction.
“. . . Some Code . . .”
x < 10
ifTrue:[ session commitTransaction]
ifFalse: [ session rollbackTransaction]
A unit of work automatically wraps itself in a transaction, so there is no need to explicitly run a unit of work in a transaction. This explains why none of the examples so far has explicitly used a transaction.
Last modified date: 06/05/2020