Database layer in PHP

1
Database layer in PHP
2
Example application

• As an example, consider an information system of
  an association.
• We assume that the association has public www
  pages, which display public information in
  English and Finnish.
• The association has events, news, and links in
  the web.
• GET may be used for this part of the service.
• The members may maintain the information.
• This requires a login.
• Lets see the related, fairly simple, database
  design.
• I have implemented parts of the system as coding
  examples.
• Notice that the implementation is not complete!

3
Basic PHP programming

• In principle, uses techniques and the style of
  the programming tasks implemented this far in the
  course.
• I put in a .css file, just to demonstrate that it
  does have a place as well and not to always do
  all with tables.
• Php, sql and Html all messed up
• Maintenance gets hard
• Development gets hard, as the developer needs to
  know all technologies
• E.g. it is hard to separate the graphical design
  from coding.

4
What should be done?

• Start using the MVC model
• Separate the view part (presentation layer)
• html and other presentation-specific code should
  be separated from the main part.
• Separate presentation-layer software packages
  exist to separate the presentation from the
  controller and the model.
• Next lecture
• Separate the model part (database access)
• sql and other model-specific code should be
  separated.
• Specific packages exist to manage the database on
  a little bit higher level.
• Today

5
Database layer separation

• You may do this just by separating the database
  calls into specific functions.
• However, there are also software packages, which
  help you to implement the database access layer.
• They also intend to hide the database management
  system specific features (which unfortunately
  exist).
• This makes it easier to change from one database
  product to another.

6
DB

• Part of Pear libraries.
• There is now a new version MDB2 (not on our
  server).
• Is not the only one of its kind big, more
  complete and more slow than some competitors.
• Supports a large number of database systems.
• Nice wrapping of db specific features.
• Particular examples
• Access to autoincremented fields.
• Automatic prepare.
• Documentation http//pear.php.net/package/DB-gt
  Documentation -gt End-user documentation

7
Autoincremented integers

• Also called sequences.
• The DB may automatically allocate them e.g. to
  key values, if it is so instructed.
• Syntax varies from one DBMS to another.
• DB package hides this.
• But would want you to create the sequences from
  DB.
• If you want to access otherwise generated
  sequences, see sequence naming details (the
  scheme can be changed )
• You may want to access the sequence to know what
  sequence value was given to the inserted row.
• Retrieve the sequence value and put it into a
  field value.

8
When the database changes?

• Changing database structure is still a huge
  challenge.
• Maintainability advice 1 Do not use number-based
  indexing of table attributes.
• When the table attributes or their order changes,
  you are in trouble.
• Maintainability advice 2 Use views for data
  access. If the database changes, then you can
  just re-program your view to retrieve the
  information you need.

9
TRANSACTIONALCONSIDERATIONS
10
Transactional problems

• We start by discussing some potential problematic
  situations.
• The user has retrieved data and submits a
  modification.
• The data has been deleted in the meantime.
• The update will simply fail.
• Some other user has changed the data meantime
• Should the data be changed without viewing the
  current data or should the modification just be
  performed?
• The key value of data has changed
• This is less likely
• The user has managed to manipulate GET/POST data.
• The changed data may, e.g. through a GET
  parameter access data, that would be prohibited
  for the user.

11
Transactional problems...

• Insert collides with an existing key
• Should not happen, if keys have correct
  well-chosen real-world values.
• Also should not happen, if auto-generated
  keys/indices are used correctly.
• Delete does not locate the data as it has been
  deleted after it was retrieved
• The delete just fails, but this should not be
  serious.
• Delete deletes data that has been modified in the
  meantime without the user knowing that.
• This could be serious, as the user may not have
  deleted the data had she known about the change.

12
Solutions

• In principle, transactions can use locks to solve
  some of these problems.
• In PHP it seems a bit more complicated, as the
  natural lifecycle of a program does not expand
  over several user inputs.
• Even if we found a way, we probably would not
  want to hold locks over the unpredictable user
  input time.
• It may be better to just try to check, if the
  data has not changed over the user input time
• Store viewed data
• Use timestamps for latest change on table rows
• Space-efficient
• May be over-cautious, if only a part of the row
  has been seen (and only the other part has
  changed in the meantime).

13
Base solution

• When retrieving/viewing data, store either the
  timestamp or the data values in session data.
• When performing a delete or modification, start
  by retrieving the data.
• If the data/timestamp matches the session data,
  perform the modification or delete.
• If not, inform the user that the data has changed
  in the meantime.

14
Further considerations

• Suppose that we are not locking data over the
  user input time.
• The user is buying a flight in the internet.
• The reservation process lasts over several forms,
  collecting user information, payment information,
  etc.
• On the last form, when the payment is ready,
  other clients have at the same time bought all
  the remaining tickets.
• There may sometimes be a need to make some
  lock-like reservation operations.
• If we do not want to lock the items using the DB
  system capabilities, we may just mark their
  reservation status in the database.

15
Transaction length

• It may not be feasible nor even possible in some
  case to make the transaction live over the user
  input time.
• However, there may be a need for transactionality
  within execution of a single user request.
• If the transaction is read-only and there are no
  critical consistency or isolation requirements
  for transactions, there may not be a need for
  DBMS transaction structure.
• However, particularly when there are several
  updates, there is a good reason to use
  transactions
• Start transaction at the beginning of processing
  a request.
• In case of a severe error, roll back the
  transaction.
• If all goes well, commit the transaction at the
  end of processing a request.