Database layer in php

Database layer in php

• 1.
  Jyrki Nummenmaa University ofTampere, CS Department Database layer in PHP WWW programming – http://www.cs.uta.fi/wo
• 2.
  Example application • As an example, consider an information system of an Jyrki Nummenmaa University of Tampere, CS Department 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. • Let’s see the related, fairly simple, database design. • I have implemented parts of the system as coding examples. – Notice that the implementation is not complete! WWW programming – http://www.cs.uta.fi/wo
• 3.
  Basic PHP programming • In principle, uses techniques and the style of the Jyrki Nummenmaa University of Tampere, CS Department 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. WWW programming – http://www.cs.uta.fi/wo
• 4.
  What should bedone? • Start using the MVC model Jyrki Nummenmaa University of Tampere, CS Department • 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 WWW programming – http://www.cs.uta.fi/wo
• 5.
  Database layer separation • You may do this just by separating the database calls into Jyrki Nummenmaa University of Tampere, CS Department 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. WWW programming – http://www.cs.uta.fi/wo
• 6.
  DB • Part of ”Pear” libraries. Jyrki Nummenmaa University of Tampere, CS Department – 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 -> Documentation -> End-user documentation WWW programming – http://www.cs.uta.fi/wo
• 7.
  Autoincremented integers • Also called sequences. Jyrki Nummenmaa University of Tampere, CS Department • 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. WWW programming – http://www.cs.uta.fi/wo
• 8.
  When the databasechanges? • Changing database structure is still a huge challenge. Jyrki Nummenmaa University of Tampere, CS Department • 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. WWW programming – http://www.cs.uta.fi/wo
• 9.
  Jyrki Nummenmaa University ofTampere, CS Department TRANSACTIONAL CONSIDERATIONS WWW programming – http://www.cs.uta.fi/wo
• 10.
  Transactional problems • We start by discussing some potential problematic situations. Jyrki Nummenmaa University of Tampere, CS Department • 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. WWW programming – http://www.cs.uta.fi/wo
• 11.
  Transactional problems... • Insert collides with an existing key Jyrki Nummenmaa University of Tampere, CS Department – 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. WWW programming – http://www.cs.uta.fi/wo
• 12.
  Solutions • In principle, transactions can use locks to solve some of these Jyrki Nummenmaa University of Tampere, CS Department 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). WWW programming – http://www.cs.uta.fi/wo
• 13.
  Base solution • When retrieving/viewing data, store either the timestamp or Jyrki Nummenmaa University of Tampere, CS Department 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. WWW programming – http://www.cs.uta.fi/wo
• 14.
  Further considerations • Suppose that we are not locking data over the user input Jyrki Nummenmaa University of Tampere, CS Department time. 1. The user is buying a flight in the internet. 2. The reservation process lasts over several forms, collecting user information, payment information, etc. 3. 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. WWW programming – http://www.cs.uta.fi/wo
• 15.
  Transaction length • It may not be feasible nor even possible in some case to Jyrki Nummenmaa University of Tampere, CS Department 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. WWW programming – http://www.cs.uta.fi/wo