Java程序辅导
C C++ Java Python Processing编程在线培训 程序编写 软件开发 视频讲解
C C++ Java Python Processing编程在线培训 程序编写 软件开发 视频讲解
©nabg CSCI110 Exercise 4: Database - MySQL The exercise This exercise is to be completed in the laboratory and your completed work is to be shown to the laboratory tutor. The work should be done in week-8 but completion by the end of week-11 is acceptable. (No marks unless demonstrated by end of week 11.) The completed exercise is worth 4 marks. When you have demonstrated your work to the laboratory tutor, you should sign off on the tutor’s marking sheet. Overview All realistic web-applications (web-apps) involve a relational database! They may present selected data from the database or may add new data. You just have to learn how to work with databases and the SQL language that is used to manipulate data in the tables of a relational database. This exercise will illustrate just a few simple features of databases and the SQL language. You can find further examples in some of the tutorials that are available on the web: • Database and SQL tutorials include – – http://www.wdvl.com/Authoring/DB/ – http://www.w3schools.com/sql/default.asp – http://www.sql-tutorial.net/ Each laboratory computer will be running a copy of the MySQL database server. These servers have accounts set up – student1, student2, …, student150. You will have been allocated an account, e.g. student13, and a password. You should be able to use any of the database engines. Each is separate; so if you create tables in the database on say mega-pc15 you won’t find them on mega-pc11. It’s best to consistently use the one machine. The host in the URL for your database will normally be localhost (assuming that works in the lab – that depends on how the network has been set up). You can specify the actual machine name (e.g. mega-pc17.cs.uow.edu.au). (In principle, you can access the tables you created on a computer other than the one that you are using by just specifying its URL. However, if that machine is switched off or is in Windows mode that won’t work.) Parts of the exercise use the MySQL tools; most parts use PHP to access the tables that you create. Part 1 A “ baby” example (1 mark) 1. Use the MySQL “Workbench” tool (in the “Applications/Programming” menu on the Ubuntu systems) to log in to the database. ©nabg You will need to create a database connection – there is a “New Connection” option in the “SQL Development” section of the Workbench window. Use the “studentxx” account that you were allocated with the password that was supplied. The default schema should be specified; its name is the same as the username. (As in the case of Netbeans, the system may hassle you for “keyring” passwords – just remove any keyrings file, and if it asks tell it not to try to save your password in the ring (keychain).) It probably will save the details, and your connection may subsequently appear in the list of known connections. 2. You should then get to the “SQL Development” view. You will see a variety of schema (databases) that are known to the local database engine. Your “studentxxx” entry should be there. Select it. (Hopefully, the system will have been configured correctly and you won’t be able to use any of the other schema!) Select your schema (database) and set it as the “default”. ©nabg You can view your tables. Right-click to create a new table (or in the schema view – all those things that look like oil-barrels – there will be an “add table” button”). 3. The example data table is for a “Name your Baby” web-app. The table will have two columns, both containing string data. The first column is “Gender” and its values should be Boy or Girl. (You could use a MySQL enum type; but in this demonstration a string (varchar) type is used.) The second column is “Name” and its values will be popular names for children. Select your schema – right-click and get the menu offering “create table”: ©nabg 4. The first tab pane has options for defining the table name and the “Engine”. MySQL has a variety of mechanisms for storing relational tables – the “Engine” is really the underlying data representation. The traditional default was MyISAM. The MyISAM scheme doesn’t enforce things like foreign key constraints and doesn’t support transaction isolation. The alternative InnoDB “Engine” has these features (at an additional cost of course). Just use the system’s default “Engine” for this simple application. Name the table. MySQL is case sensitive so “babynames”, “Babynames”, and “BabyNames” would all be different tables. You PHP code will have to refer to the table using its correct case sensitive name. 5. Other tabs allow for the definition of the columns (names and data types), indexing, foreign keys, and other more specialised options. In this simple example, you need only define a couple of columns – both being “string” type (varchar() in database jargon). MySQL will start with its default “id” column – it assumes that all records are going to have an integer primary key. You simply overwrite this entry and add a second column – you will need to change the size of the varchar (for some reason known only to MySQL developers this defaults to 45 characters). ©nabg 6. MySQL will show the SQL code that it will use to create the table. Run the code. The table definition can be saved to a text file (e.g. one named “createtable.sql”). This can be useful in more complex examples as you can then recreate a table by simply running the SQL code from this saved file. In all your real work, you should save the SQL createtable script as a .sql file that is associated with the project. Using the MySQL table creation “wizard” is useful when you are first starting but you must make the effort to learn how to write your own “create table scripts”. 7. Data can now be inserted using the “Query Tab”: (SQL commands are run in Workbench SQL Developer by clicking the “lightning bolt” button in the command pane: ©nabg Simple queries can be run: 8. You will often need to populate a table with test data for a web application. Entering the data through something like the “Query Tab” isn’t the best way; quite often you need to restart, deleting the table and re-initializing it for another test. It is better to have the SQL “insert into xxx values (…)” statements in a text file: MySQL’s Workbench will let you read a file (File/Open script) and execute the statements: ©nabg 9. The SQL Devlopment query browser is however quite useful for testing SQL statements that you propose to use in applications: 10. The NetBeans IDE has a SQL client component that will work with most databases – MySQL, DB2, Oracle, “Java DB” (a simple database engine implemented in Java that comes with NetBeans), etc. This is accessible via the “Services” tab in NetBeans: ©nabg 11. The code needed to connect to MySQL is in the libraries available to NetBeans. So, it should be possible to right-click on the MySQL entry and ask it to create an actual connection to the database. MySQL is using Port 3006. 12. NetBeans should be able to open the connection and will then display it along with its “localhost:1527” connections (these relate to a tutorial database system that is included with NetBeans). The new MySQL connection can be opened to show some details of its tables, “views”, and “procedures”. The table tab can be opened up to show the tables – of course there is only one table: “babyNames”. A right-click “View Data” on this table will show data from the table. ©nabg You can run any SQL statements you wish via NetBeans. It’s really your choice as to whether you use the MySQL tools or the NetBeans SQL client. (There is a third option – use PhpMyAdmin. We will not cover this in CSCI110, but it is a popular choice if your database is hosted on some ISP site and you cannot use the MySQL tools. There are lots of good tutorials on PhpMyAdmin available on the Internet e.g. http://www.reg.ca/faq/PhpMyAdminTutorial.html ) 13. The web project that uses this database should now be constructed. If you connected to MySQL from NetBeans you should close this connection (pick the connection in the services view and right-click “Disconnect”) and then switch to the “Projects” view and create a new project Create a project, Exercise 4, in your public_hml directory. This should have a static HTML form and a PHP script. Create a simple form that will appear something like the following: (I am not giving you the HTML for that form – you should be able to write it yourself by now!) ©nabg 14. The processing program, BabyNameService.php, will have to: Check the data. You always have to check the data. Divert hacker attacks to a suitable error response page. Connect to the MySQL database (URL defines machine, usually localhost; user-name and password will be the same studentxx/password combination as used with the MySQL Query Browser; the “data base” has the same name as the user-name). Prepare a statement that can be run; set parameters from the input data. Retrieve matching names. Display a HTML page with these names. 15. The program will have a block of PHP code defining functions and some “main line” code, and some HTML with a little embedded PHP: The main line code starts by checking the inputs. If data are missing, or if the value for “gender” is anything other than ‘Boy’ or ‘Girl’, or if the value in “letter” is anything other than a single capital case letter – then we have a hacker. Dismiss them: ©nabg If the data appear OK, then a connection to the database must be opened, the query run (the results, possible names, are left in the global array $bnames), and the database connection must be closed. 16. The code for connecting to a database is pretty standard: 17. The search function “prepares” a SQL select statement, sets the parameters from the inputs, executes the statement, and loops through the retrieved rows adding names to the global array $bnames: 18. The HTML markup for the response page includes some fragments of embedded PHP. These illustrate the use of the “alternative syntax” form of PHP’s ©nabg conditional statement: 19. Your application should run: Part 2 A more realistic example (3 marks) In this part, you “develop” an essential part of all “student management packages” – the component that deals with students’ requests that they be excused from completing particular assessment items. (The version that you implement is somewhat simplified.) There are two classes of users – students who submit requests through the web, and reviewers who must first login and who can then review new requests and delete old requests. Use cases We can represent the system using UML (Unified Modelling Language) “use case” diagrams – you will get used to these in your subsequent studies, they are an ubiquitous part of software specifications. ©nabg Use case 1: Student request for exemption The system shall display a form with fields for the student to enter their identifier, the subject identifier and task identifier for the assessment item, and a text area field for entering the reason why this task should not count toward their assessment. Simple data checks are applied and the data are recorded in the system’s database; a simple response page is generated acknowledging receipt of the request. Use case 2: Reviewer login Reviewers must login to gain access to other processing options. Reviewers provide a name and password; these data are checked against a data table holding names and encrypted passwords. Logged in status is maintained using cookies and session data. The system shall also provide a logout option. Use case 3: Review of unassessed requests ©nabg Logged in reviewers shall be able to see a tabular listing of outstanding requests. They shall be able to mark each as either “Approved”, or “Rejected”, or “Ignored” (ignored requests are left as still outstanding, they will be processed later – possibly by a different reviewer). The reviewer shall be able to submit their changes; the submitted changes will be used to update the system’s records. A simple acknowledgement page will report how many records were changed. Use case 4: Reviewer deletes old records Logged in reviewers can enter a date and request the deletion of all records that have been given “Approved” or “Rejected” status on or before the given date. The system will generate a simple response page detailing the number of records that have been deleted. Storyboard walkthrough Another typical design element for an application is a “storyboard walkthrough”. This will show rough designs for the forms and responses for the different uses of the system and will provide clients with an idea of what it will be like to use the system. These walkthroughs serve as a mechanism for getting initial feedback from clients; the client may object to the proposed approach and suggest alternatives – such refinements of the requirements are best acquired at this early stage when little work has been done, and little needs to be undone if the initial approach was wrong. Walkthroughs use invented data. They must be convincing to clients, so the invented data should be typical of the real data in the problem domain (don’t use text like “abcde” or “excuse 1” – invent realistic text). The “walkthrough” here uses screenshots from my implementation of the code that you will be creating. ©nabg Student use: ©nabg Reviewer login: Login page is displayed. If the combination of username and password is invalid, the same page is re-displayed. If a valid username/password combination is given, the response is the reviewer’s options page. Reviewer’s options page ©nabg Reviewer deletes old records: The reviewer enters a date. Records processed on or before this date are deleted. The response page shows the number of records deleted. Review of open requests The system provides a form page containing details of all open requests. The data are shown in a HTML table that also contains controls that allow individual requests to be accepted, rejected, or left for future processing (ignored). The selection of processing choices can be submitted to the system which will then update its records. The generated response page shows the number of records updated. ©nabg Tasks 1. Create a new NetBeans project – E4New. The project will eventually have a number of PHP scripts, CSS, and Javascript. “SQL files” with the SQL statements needed to create and populate the tables can be included in the project. (Of course, such files would not be deployed onto the real server! It’s just convenient to make them part of the project while doing development). ©nabg The project will use Lea Smarts Javascript calendar, as used in earlier exercises. Copy the Javascript, CSS, and image files for the calendar into your new project (do this at Linux command level using cp and mv commands). 2. Create and populate a MySQL table for reviewers names and passwords. You can continue to use the MySQL “wizard” to help compose the create table commands but transfer the generated SQL statements to a SQL file, and run them via NetBeans. (This way you can easily recreate your tables in a standard state after doing inserts, updates, or deletions; or you can create them on a different machine from the one that you originally used.) The “reviewers” table (creation and population) will be in the file “createreviewers.sql” (don’t use “stud0000”s database – use your own!): (MySQL’s insert statement allows for function calls in the “values” part – here we request that the MD5 hash algorithm be applied to the clear text passwords, making the password data stored difficult for hackers to exploit.) Use Netbeans to connect to your MySQL process and run these commands to create the reviewers table (the mechanisms are illustrated in steps 11 and 12 of part 1 above). Similarly create and populate the “excuses” table: ©nabg (The insert statements have a NULL for the primary key “idexcuses” – MySQL is providing these values through its “auto-increment primary key” mechanism.) 3. Create a simple stylesheet for the web pages (Lea Smart’s “calendar” comes with its own stylesheet, you need a separate one to define the overall appearance of the pages). Your stylesheet doesn’t need to be too elaborate for this little example: ©nabg 4. Create the “submitexcuse.php” script. This handles “get” requests by displaying a form. Data submitted via this form is posted back to the same script. The “post” handling will create a new record in the data table. The “connectToMySQL()” function is used in most of the scripts – just copy-&- ©nabg paste the code as needed: The showForm() function uses a block of HTML text as a “here document” to define the data entry form used by students. The “action” attribute of the form is set to post data back to the same script: The doLogRequest() function creates a new entry in the database (if data are missing, it sends the user back to the data entry step): ©nabg It generates a simple acknowledgement page: You should be able to run this part of the application now. You can check that it works by using MySQL WorkBench SQL Developer options to check the contents of the updated table: ©nabg 5. You need reviewerlogin.php and reviewerlogout.php scripts. The application uses PHP sessions to manage logins. PHP code from its library will use a cookie that it sets in the client browser. The cookie keys into PHP’s session state storage (actually using temporary files). Session storage is used to keep a record of the reviewer who has logged in. Reviewerlogin This has the “connectToMySQL” code shown above and the following: The code starts by establishing a session (the PHP library code will add a cookie to the response and create necessary $_SESSION data). If a reviewer is already logged in, the script diverts the user directly to the page displaying options for reviewers. Otherwise, it determines whether the request is a “GET” or a “POST”. If the request is a “GET”, the user needs to receive the login form. ©nabg If the request is a “POST”, submitted username and password data must be checked: The code applies the md5() hash function to the password – to match the data table where the passwords are encrypted. It then connects to the MySQL server and runs a request to verify that the combination of name and (encrypted) ©nabg password does actually exist. If there is no match, the user is again shown the login form. If there is a match, the user is shown the page with reviewer options. ReviewerLogout This script must clear the session records from browser and server. The code is standardised – resume work on session, destroy all data. Then, some acknowledgement is returned: 6. The reviewers.php script. This must check that it is being used by a logged in reviewer (otherwise some smart aleck student might guess the script name and enter it into their browser and so get the ability to approve their own requests for exemptions). If the script is not being used by a logged in user, it redirects the user to the login page. (Why does it use array_key_exists(‘reviewer’, $_SESSION) instead of something like isset($_SESSION[‘reviewer’]? Well, any code that involves $_SESSION[‘reviewer’] when this does not exist will result in an error message in the log file; these error messages can obscure more important information – such as evidence for a hacker attack.) ©nabg The showForm function has an link that invokes the script showing current outstanding requests, and a small data entry form that posts data to the record deletion script. The data entry form makes use of Lea Smart’s Javascript calendar – it’s much like the example in exercise 2. ©nabg 7. The deletold.php script uses the posted date value in a SQL statement that deletes processed records: ©nabg ©nabg Your application should now allow you to delete old records; you should check by examining the tables in MySQL Workbench: Before After deletion (and some further updates): 8. Finally, you need the reviewopenrequest.php script. ©nabg Like the other scripts used by reviewers, this needs to re-establish a session, and check that the user is properly logged in. As usual, it handles “GET” requests by showing a form, and handles “POST” requests by processing submitted data. The printPageHeader() function simply outputs some canned HTML that gets used in all the responses from this script: The form generated by this script is a little more elaborate than usual. It’s going ©nabg to have a table with rows used for entering processing options for some arbitrary number of items. Each item has a distinct group of 3 radio buttons (its own “approve”, “reject”, and “ignore” options). The button selected must identify the record that is to be processed and its disposition. The form code will show a table, or a message saying that there aren’t any records to process. The code to actually generate the table is hidden in a fold of this part of the script which shows only the database handling needed to get the records, and the final part of the HTML output: The loop that runs through the retrieved records will generate a HTML table row for each one; the first record processed also needs to output the table header row. ©nabg Different “radio button groups” are generated for each row (the row count is used as part of the name of the radio button group, it’s going to be group_1, group_2 etc). The identifier for the database record (its auto-index primary key) is embedded in the values that will be returned from a selected radio-button – e.g. for record 6, the value returned will be A6 if the approve button is selected, or R6 or I6 for the other options. (The code also generates unique ids for the