solutions
:: Accessing an existing database
Accessing an existing database
Introduction
ABEL has a special backend to read and write existing databases. This backend was designed to use ABEL alongside EiffelStore with very little setup.
The drawback of this approach is that the backend has some of the limitations of EiffelStore:
- Only flat classes can be stored and retrieved.
- The class name must match the type name in lowercase, and each attribute must match a column name.
- ABEL treats all objects as values without identity (like expanded types). There is a mechanism however to override this default.
- There is no concept of a root status.
The setup
Let's assume a simple EiffelStore application for managing a (very simple) MySQL customer database.The database consists of a single table created by the following SQL statement:
CREATE TABLE customer (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR (100),
last_name VARCHAR (100),
age INTEGER
)
In your Eiffel code you have a class which matches the table:
class
CUSTOMER
inherit
ANY redefine out end
create
make
feature -- Access
id: INTEGER
-- The customer ID.
first_name: STRING
-- The customer's first name.
last_name: STRING
-- The customer's last name.
age: INTEGER
-- The age of the customer.
feature -- Element change
set_age (an_age: like age)
-- Set `age' to `an_age'
do
age := an_age
end
feature -- Output
out: STRING
-- String output of `Current'.
do
Result := id.out + ": " + first_name + " " + last_name + " " + age.out + "%N"
end
feature {NONE} -- Initialization
make (an_id: like id; first: like first_name; last: like last_name; an_age: like age)
-- Initialization for `Current'.
do
id := an_id
first_name := first
last_name := last
age := an_age
end
end
Connection setup
Because we're using an existing MySQL database, we need to choose the
class
TUTORIAL
create
make
feature -- Access
repository: PS_REPOSITORY
feature {NONE} -- Initialization
make
-- Set up the repository.
local
factory: PS_MYSQL_RELATIONAL_REPOSITORY_FACTORY
do
create factory.make
-- Feel free to change the login credentials.
factory.set_database ("my_database")
factory.set_user ("root")
factory.set_password ("1234")
repository := factory.new_repository
end
end
That's it. You're now ready to read and write table records using the repository.
Querying objects
With the newly created repository, we can now query for
print_customers
-- Print all customers.
local
query: PS_QUERY [CUSTOMER]
do
create query.make
repository.execute_query (query)
across
query as cursor
loop
print (cursor.item)
end
if query.has_error then
print ("An error occurred!%N")
end
query.close
end
Writing objects
You can also use ABEL to write or update customers, although the semantics are a bit different compared to other backends.ABEL by default treats every object as a value tuple without identity (like an expanded type).The reason is that the primary key of an object is usually stored directly inside the object, and a user may change it and thus mess with ABEL's internal data structures.
The implication of this is that a user is only allowed to call
The following code shows how to insert and update objects.
insert_customer
-- Insert a new customer.
local
albo: CUSTOMER
transaction: PS_TRANSACTION
do
-- Assume 42 is an valid, unused primary key.
create albo.make (42, "Albo", "Bitossi", 1)
transaction := repository.new_transaction
if not transaction.has_error then
-- This results in an insert, because
-- according to our previous assumption
-- there is no record with primary key 42
transaction.insert (albo)
end
-- Cleanup and error handling.
if not transaction.has_error then
transaction.commit
end
if transaction.has_error then
print ("An error occurred.%N")
end
end
update_customer
-- Update an existing customer.
local
factory: PS_CRITERION_FACTORY
query: PS_QUERY [CUSTOMER]
transaction: PS_TRANSACTION
do
create query.make
query.set_criterion (factory ("id", "=", 42))
transaction := repository.new_transaction
if not transaction.has_error then
transaction.execute_query (query)
end
across
query as cursor
loop
cursor.item.set_age (2)
-- The result is an update, because an object
-- with primary key 42 is already present.
transaction.insert (cursor.item)
end
-- Cleanup and error handling
query.close
if not transaction.has_error then
transaction.commit
end
if transaction.has_error then
print ("An error occurred!%N")
end
end
Managed types
Maybe you realized the weak spot in the previous section:We assumed that a primary key does not exist yet.This is a very dangerous assumption, especially in a multi-user setting.The way to resolve this issue is to usually to declare the primary key column as auto-incremented and let the database handle primary key generation.
It is possible to use this facility in ABEL by declaring a type as "managed" and specifying the primary key column.This only works for tables which actually have an auto-incremented integer primary key column.
There are some changes when declaring a type as managed:
- ABEL will keep track of object identity. Thus it is possible (and recommended) to use {PS_TRANSACTION}.update.
- As ABEL now takes care of primary keys, it is not allowed to change the primary key of an object. If it happens anyway, an error will be returned.
- To insert a new object, you can just set the primary key attribute to zero. The database will then generate a new key.
- After a successful insert, ABEL will update the Eiffel object with the new primary key.
Our customer database table fulfills all requirements for ABEL to manage its primary key handling, thus we can rewrite the above examples:
class
TUTORIAL
create
make
feature -- Access
repository: PS_REPOSITORY
generated_id: INTEGER
-- The ID generated by the database.
feature {NONE} -- Initialization
make
-- Set up the repository.
local
factory: PS_MYSQL_RELATIONAL_REPOSITORY_FACTORY
do
create factory.make
factory.set_database ("my_database")
factory.set_user ("root")
factory.set_password ("1234")
-- Tell ABEL to manage the `CUSTOMER' type.
factory.manage ({CUSTOMER}, "id")
repository := factory.new_repository
insert_customer
update_customer
end
feature -- Tutorial functions
insert_customer
-- Insert a new customer.
local
albo: CUSTOMER
transaction: PS_TRANSACTION
do
-- Note that the ID is now set to 0.
create albo.make (0, "Albo", "Bitossi", 1)
transaction := repository.new_transaction
if not transaction.has_error then
-- The next statement will be an insert in any case.
transaction.insert (albo)
end
-- The generated ID is now stored in `albo'
generated_id := albo.id
-- Cleanup and error handling.
if not transaction.has_error then
transaction.commit
end
if transaction.has_error then
print ("An error occurred.%N")
end
end
update_customer
-- Update an existing customer.
local
factory: PS_CRITERION_FACTORY
query: PS_QUERY [CUSTOMER]
transaction: PS_TRANSACTION
do
create factory
create query.make
query.set_criterion (factory ("id", "=", generated_id))
transaction := repository.new_transaction
if not transaction.has_error then
transaction.execute_query (query)
end
across
query as cursor
loop
cursor.item.set_age (3)
-- It is possible to call update.
transaction.update (cursor.item)
end
-- Cleanup and error handling
query.close
if not transaction.has_error then
transaction.commit
end
if transaction.has_error then
print ("An error occurred!%N")
end
end
end