DB Application - Java

Over the course of these exercises, you'll create a rudimentary photo-management system. While your application will lack many useful features, you will gain practice with using JDBC to connect to a database - and will implement several things that may be useful in your final project.

Setup

It is assumed that you are familiar with using Eclipse, the idea of event-driven programming, and the basics of components, layout managers, and event handling using JavaFX. If not, ask!

Review the JDBC Local Quick Start for information on how to set up the build path for an Eclipse project.
Then, create an Eclipse workspace directory for this course and a project photoalbum for these exercises.

Handin

Copy your files to a folder photoalbum in your handin directory (/classes/cs343/handin/username, where username is your CS account username).

Photo Album

These exercises involve the development of a basic photo management system. Each user can maintain an album of photos. Photos may be tagged with keywords (a given photo may have multiple keywords). The user may view all of the pictures in her album or select just those with a certain keyword. Users are identified by a username and password (so they must log in before viewing or modifying an album). Users may create new accounts, add photos to their album, or add keywords to a photo.

The username and password associated with each user of the photo-management system is distinct from the username and password which allows you to connect to the MySQL server and carry out queries. In the latter case, MySQL handles verifying the correctness of the password and enforcing the permissions associated with the user, while in the former case, the database only stores the necessary information - it is up to the database application (i.e. your web application) to use that information to verify the correctness of the password and to enforce any necessary access permissions.

The necessary data - usernames and passwords, URLs of the images in each album, and the keywords for each image - will be stored in the database with the following schema:

USER ( username, password )
ALBUM ( username, url, id )
KEYWORD ( id, keyword )

USER stores information about users and their passwords, ALBUM links images to users, and KEYWORD links keywords to images. id is numerical ID used to uniquely identify entries in the ALBUM table. ALBUM.username is a foreign key which refers to USER.username and KEYWORD.id is a foreign key which refers to ALBUM.id.

Use the database username_photoalbum (where username is replaced by your username) for the exercises. The user accounts arthur and ford have passwords tea and betelgeuse, respectively.

Remote access note: the Java program is run on your computer, so you will need to substitute localhost:9001 (or whatever is appropriate for your setup) in place of 172.21.7.83 if you are off campus. To make it easy to change, define a constant with the right value.

The full application will be built in stages. The first stage is to display a hardcoded set of photos:

Create a class Album to represent the contents of a photo album. It should have an instance variable that is a list of URLs (type String) for images. Include a constructor that initializes the list to contain at least six URLs (hardcode specific URLs for now). You can find a collection of images to choose from in http://172.21.7.83/cs343f20/pics/. Use absolute URLs (e.g. http://172.21.7.83/cs343f20/pics/108_0822.jpg, not just 108_0822.jpg). Also include a getter which returns the list of image URLs.
Create a class AlbumFrame to display the contents of the photo album. AlbumFrame should extend JFrame and display thumbnails of the images in the album, similar to what is shown below. The thumbnails should be arranged in a grid with no more than five images per row. Each thumbnail image should have dimensions 100x100 (regardless of the actual image size). AlbumFrame should have an instance variable of type Album which is initialized in the constructor.

Be sure to correctly handle any number of images - modifying the list of images in the Album constructor should be all that is necessary to display a different set of images or a different number of images.

Reference:
- Look up ImageView for information on displaying images. Note, however, that the example is not quite correct - the Image constructor requires the URL of the image to load so you will need to prepend "file:" to the filename e.g. use file:images/1.jpg for the file 1.jpg in the images folder of your project.
- getScaledInstance() is useful for resizing an image:
```
	  ImageView icon = new ImageView(image.getScaledInstance(100,100, Image.SCALE_SMOOTH));
	
```
- Consult the reference information posted on the schedule page for information on using JavaFX, including components, layout managers, and event listeners.
- If you are feeling ambitious, you can try to figure out how to use an image's metadata to determine the correct rotation.
Create a main program to create an instance of AlbumFrame so you can test the program.

Stage two is to retrieve a user's album from the database instead of having the set of images hardcoded.

In addition to what is described below, your solution should also do the following things:

Perform all appropriate error-checking. It is OK to write the SQL error message to the console to aid in debugging, but you should also have a more user-friendly solution (e.g. popping up a dialog box or otherwise displaying relevant information).
Properly free results and close database connections in all circumstances (including when errors occur).
Use the named constants you defined (see below) where appropriate. AlbumLoader should not hardcode any database connection parameters.

Create a class AlbumLoader to handle retrieving picture and album information from the database. Define the following constants:
- DATABASE whose value is the name of the database to connect to (username_photoalbum)
- HOST whose value is the name of the machine where MySQL is running (172.21.7.83)
- PORT whose value is the port to connect to (3306)
- DBUSER whose value is the name of the MySQL user to use when connecting to the database (use guest)
- DBPASS whose value is the password to use when connecting to the database (use guest)
And include the following methods:
- openConnection() - open a connection using the values defined in the constants
- closeConnection() - close the current connection
- getAlbum(user) - retrieve and return a list of the URLs of the images in the specified user's album
- getKeywords(user) - retrieve and return an alphabetical list of all of the keywords associated with the images in the specified user's album
- getKeywords(user,id) - retrieve and return an alphabetical list of the keywords associated with the specified image in the specified user's album
Store the currently open connection (if any) in an instance variable.

Use prepared statements for getAlbum and the two versions of getKeywords. Avoid duplicates in keywords and images. All methods other than openConnection assume that a connection has already been opened.
Finish adding support for loading a user's album from the database:
1. Modify Album:
  - Add instance variables to Album for the current user (a String) and an AlbumLoader. Initialize both in the constructor. It is OK to hardcode the current user's name (arthur is a user with album contents; ford is a user with an empty album).
  - Change the initialization of the list of images to just be an empty list, and add a reload() method which gets the current user's images and uses that to initialize the list of images.
2. Modify the user interface:
  - Add a menubar to AlbumFrame with a menu Album and a menu item Load.
  - Add an event handler for Load which calls reload() on the Album and then updates the AlbumFrame's display accordingly. If there are no images in the album, the message "Album is empty" should be displayed instead of the grid of thumbnails.
Reference:
- If you change the components in an already visible container, you will need to call validate() on the topmost affected container to invoke the layout manager again.
Display the keywords associated with images in the user's album:
1. Modify Album:
  - Add an instance variable for the list of keywords. Initialize it to the empty list in the constructor.
  - Update Album's reload() method to also get the keywords for the current user's images, which is used to initialize the list of keywords.
2. Modify the user interface:
  - Add a combobox containing an alphabetical list of the album's keywords above the thumbnail display.
  - Update Load's event handler to also update the contents of the combobox of keywords from the keyword list in Album.
Display the keywords associated with individual images: Have clicking on a thumbnail image bring up a dialog box displaying the full-size image (up to a bit smaller than the size of the screen) along with the keywords associated with that image, similar to the following (but with the full size image). If there are no keywords for an image, "No Keywords" should be displayed instead of the list of keywords.

Reference:
- See 13.4.1 of Introduction to Programming Using Java for more information on dialog boxes. Focus on simple dialogs and what you can do with them.

This stage completes the basic photo-management system.

In addition to what is described below, your solution should also do the following things:

Perform all appropriate error-checking (after the database connection is established, after the query is executed, before using variables whose values are expected to have been set elsewhere, etc). If an error occurs, print an appropriate error message along with a link to allow the user to return to an appropriate page (e.g. the login page if the username is not set or the "view album" page if the current image is not set).
Properly free results and close database connections in all circumstances (including when errors occur).
Use the named constants you defined in the main program where appropriate. AlbumLoader should not hardcode any database connection parameters.
Take appropriate security measures when inserting user input into SQL in order to avoid SQL injection problems.
Use prepared statements when prepared statements should be used.

Keep in mind that you can connect to the database via MySQL Workbench. This is useful for debugging actions that change the database - you can check the contents of tables after you've tried to insert something, or you can fix up tables if something got inserted improperly.

Add the following methods to AlbumLoader:
- createAccount(username,password) - execute the appropriate INSERT command to add the new information to the USER table
- checkLogin(username,password) - verify the user's login information (see below for more information)
- addImage(username,url) - execute the appropriate INSERT command to add the image to the ALBUM table
A note on passwords: MySQL provides a way to encrypt passwords so that they are not stored in plaintext. The way to do this is via the SHA function:
```
      INSERT INTO USER(username,password) VALUES ('arthur',SHA('tea'))
    
```
would add a new user 'arthur' to the USER table, storing the encrypted version of 'tea' as the password. (You'll need to substitute the actual values entered in the form for 'arthur' and 'tea'.) SHA implements SHA-1, a version of the Secure Hash Algorithm.

There are two ways logging in can be handled - one is to query the database for the password which goes along with the username that was entered and have the application check if they are the same, while the other is to query the database for the entry which matches the username and password and have the application check whether or not such a row was found. However, the first option doesn't work if the database stores encrypted passwords - because one feature of the encryption is that it is hard to figure out the original password given just the encrypted one, and without knowing how the SHA function works, the application can't encrypt the user's input to compare to what was retrieved from the database. But remember that passwords are stored encrypted, so you'll need to use SHA('tea') instead of just 'tea' (substituting the user's actual value for 'tea').

Also note: If you look at the table definition for ALBUM, you'll see that ALBUM.id is an auto-increment field. This means that you do not have to supply a value for it when inserting a new row into ALBUM - the system will automatically pick a unique value. For example, you could insert into ALBUM as follows:
```
	INSERT INTO ALBUM(username,url) VALUES ('arthur','http://172.21.7.83/cs343f20/pics/IMG_0176-edit.JPG')
      
```
Auto-increment fields are handy when you want to have a unique integer ID as a primary key (because, say, the actual PK field - like the URL in this case - is long and it wastes space to store that over and over in KEYWORD), but there isn't any meaning to particular values.
Support user accounts:
1. Add a Create Account item to the Album menu. It should bring up a dialog box where the user can enter their desired username and pasword. When the user clicks "OK", the account should be created and the current user set accordingly.
2. Add a Login item to the Album menu. It should bring up a dialog box where the user can enter their username and password. When the user clicks "OK", the login information should be checked. If it is correct, the current user should be set accordingly.
3. Update the behavior of Load Album so that it works with the current user rather than a hardcoded user.
Add the ability to add images to the album:
1. Add a Add Image menu item to the Album menu.
2. Add an event handler which pops up a dialog box to prompt for the URL of the image. When the user clicks "OK", the specified URL should be added to the current user's album and the display of the album should be updated to include the new image.

Additional Practice

For additional practice, add some additional features to the photo management system. Add methods to AlbumLoader as needed - no other class should access the DB directly.

Add the ability to add keywords to an image:
1. Add a text field and an "add keyword" button to the full-size image / keywords display window.
2. Clicking the button should add the keyword to the image and the list of keywords should be updated, both in the image display window and the album display window. It should end up looking something like the following: (though without the "return to album view" button)
  
  There is a potential wrinkle to the insertion. Given the schema for the KEYWORD table, you might expect to write the following to add the keyword "Ruapehu" to image 1:
```
      INSERT INTO KEYWORD(keyword,id) VALUES ('Ruapehu',1);
    
```
  The problem is that you don't know the id for the image - but you do know the URL. One solution is to query the ALBUM table for the row matching the current user (also from a session variable) and image URL, and then do the INSERT using the id that was retrieved in the query. Another solution is to use a version of INSERT which allows you to do both of these steps at the same time (i.e. to insert data retrieved from another table):
```
      INSERT INTO KEYWORD(keyword,id) SELECT 'Ruapehu',id 
                                      FROM ALBUM
                                      WHERE url='http://172.21.7.83/cs343f20/pics/IMG_0081.JPG' AND username='arthur'
    
```
  This says to insert the rows retrieved by the SELECT query into KEYWORD, where the first column of the SELECT result goes into the keyword column of KEYWORD and the second column goes into the id column of KEYWORD. Also note the use of a literal in the SELECT clause - SELECT 'Ruapehu',id means that for every row of ALBUM which matches the WHERE condition, the result will contain a row whose first column is 'Ruapehu' and whose second column is the value of the id field on that row. Of course, don't forget to substitute the appropriate values retrieved from session variables and forms instead of hard-coding values like "Ruapehu", "http://172.21.7.83/cs343f20/pics/IMG_0081.JPG" and "arthur".
Add support for viewing just those images with a specific keyword:
1. Add "show all" to the top of the combobox of keywords.
2. Add a button "filter album" next to the list of keywords. Add an event handler to extract the keyword to filter by from the form. If the keyword is anything but "show all", the current filter should be set to the selected keyword. (Otherwise the current filter should be unset.)
3. Refresh the thumbnails displayed so that just the images with the specified keyword are shown.

In the exercises above, usernames and passwords are stored in a DBMS-user-defined table rather than a DBMS system table and authentication and authorization are handled entirely by the application. (It is up to the application to check that the username and password are correct, and then only send subsequent queries to access the photo album if the login was successful.) This has the advantages that user credentials only need to be sent once (for the initial authentication) and that it is very flexible - different applications can handle authentication and authorization differently, even with the same database - but also has the significant drawback that any such scheme is easily bypassed by simply querying the DB directly.

An alternative (while still using the "one big application user" model) is to bundle authorization into the DB access by creating stored routines which check for a valid login before carrying out the desired task. DBMS user accounts would then be granted access only to the stored routines so that the authorization checks cannot be bypassed, but authentication information has to be sent (and checked) with every request.

For each task below, do three things:
- Write a stored routine (procedure or function, whichever is most appropriate) to accomplish the task.
- Identify the MySQL privileges needed to carry out the task. Be specific - for database object privileges (as opposed to administrative or database-level privileges), also identify the particular database object(s) for which the privilege is needed.
- In light of your answer for (b), include appropriate DEFINER and SQL SECURITY clauses in your CREATE statement from (a). As part of this, identify what DBMS user account(s) would be appropriate to have. Keep in mind that with SQL SECURITY INVOKER, the user calling the routine is the one whose username and password were sent with the mysqli_connect() call from a PHP page.
1. Given a username and an (unencrypted) password, create a new user account.
2. Given a username and an (unencrypted) password, determine whether the login is valid (i.e. the information corresponds to an existing account).
3. Given a username and an (unencrypted) password, retrieve the user's photo album (the URLs of the images in the album) if the login is valid and generate an error (SIGNAL SQLSTATE '02000' in that case) if not.
4. Given a username, a URL, and a keyword, add the keyword to the specified photo in that user's album. If the photo is not already in the user's album, also add the photo to the album.
5. Given a URL and a keyword, add a global keyword to the specified photo - that is, add the keyword to that photo in every album in which it appears.