This project was developed to practice with the Concurrent Database Access and Locking and it was one of the three final graded projects for the “Programming and Multimedia Production” exam of my Master level degree.
- Language used: PHP
- DBMS: MySQL
DB Tables created:
- users (user, pass): holds the users login information. Users’ passwords are stored as hash using the MD5 algorithm.
- magazzino (id_prodotto, nome_prodotto, prezzo, qnt): contains the information of the Items for sale in the Virtual Store. Each Item is identified by an id.
- carrello (id_prenotazione, user, id_prodotto, nome_prodotto, prezzo): the cart is saved in the DB. It is, therefore, not associated with the user’s session.
- acquisti(id_acquisto, user, id_prodotto, nome_prodotto, prezzo): contains information about all the user’s purchases.
- DBConnector.php: realizes a DB Connection Object . Saves the connection parameters in class attributes. Exports three methods: connect(), logout() and query($ qry), which, respectively: allows to connect to the DB, allows to disconnect from the DB and allows to query the DB
- index.php: shows a login form in HTML. After retrieving the login data, performs a query to the database to check the login data. The password, entered by the user in plain, is hashed with (MD5) in order to make the comparison in the hash domain. If the login succeeds, index.php creates a Session and saves the user name in the $ _SESSION array. Every other page has to check if the login was performed trying to retrieve the username from the $ _SESSION array.
- home.php: shows the catalog of products for sale and the user shopping cart. If the login was not carried out or the Session is expired, it does a redirect to index.php. A menu shows a link to logout (logout.php) or view the purchases (acquisti.php) history. It is possible, from the list of the items, select which one add to the cart (inserisci_nel_carrello.php). An item not available can not be added to the cart. From the list of items in the cart, it is possible select items to delete (elimina_da_carrello.php). At the bottom of the page, the total price is shown and a button to perform the payment (pagamento.php) is provided.
- logout.php: retrieves and destroys the Session. Performs the redirect to the login page.
- acquisti.php: checks if the login was carried out, if so: shows the user purchases. If the login was not carried out or the session is expired, it redirects to index.php.
- inserisci_nel_carrello.php: checks the availability of the Items in the store and in case an Item is available, adds it to the user’s cart. (NOTE: adding something in the user’s cart does not decrease the number of available items in stock. Because of the user’s cart has been made permanent, and then an Item may remain indefinitely in the user’s cart, it was decided to not reserve (quantity–) the Item until the payment)
- elimina_da_carrello.php: this function doesn’t need to access the DB because, as mentioned above, an Item added to the cart does not change the quantity in the stock.
- pagamento.php: The strategy chosen is: LOCK | read-modify-write | UNLOCK | Bank Transfer | LOCK | restore | UNLOCK. (NOTE: This strategy was chosen to enhance the performance of the Virtual Store, avoiding to block the entire DB during the communication with the Bank, which could last a long time). In particular, the payment functions consists of the following steps:
- Exclusively locking on all the tables involved
- Reading of the Item’s quantity in stock and, if it is not available: transaction cancellation and Release of the Tables
- If the Item’s available, decreasing of its quantity and adding it to the user’s purchases.
- Release of the Tables
- Simulation of a communication with the bank by means of a sleep
- Simulation of a response (positive/negative) of the bank by selecting a random number
- If the answer of the bank is positive, there is nothing to do but empty the user’s cart and return to the home page.
- If the answer is negative: acquiring the lock on all tables involved, restoring the previous situation, releasing all the tables and return to home .
- Each query to the DB has been enclosed by appropriate LOCK and UNLOCK queries.
- Each redirect carries an information message through $ _GET. This message is printed at the top of the homepage or the login page.