程序代写代做 Java database algorithm html go jquery javascript Project 3: Well-Designed Photo Gallery

You are required to create/initialize your database using SQL.
Initialize your database by using an initialization script (secure/init.sql).
Do not commit and push any .sqlite or .db files.
You may ignore the .checksum file created by open_or_init_sqlite_db() .
You are not permitted to use DB Browser for SQLite for creating or modifying your database for this assignment; you must use init.sql.
You may use DB Browser for SQLite to view your development database as well as test your SQL queries. All seed data must reside in secure/init.sql. Do not use DB Browser for SQLite to “create” seed data.
Rigorous SQL injection prevention is required for this assignment. Use prepared statements with parameter markers.
You must connect and interact with the database using PHP’s PDO extension. No other methods/libraries are permitted.
5. File Uploads
All file uploads should be placed in a sub-folder under the uploads folder.
All uploads sub-folders should have the same name as their corresponding database table.
For example, if you have a table called images then you should have an folder called uploads/images.
All files stored in each uploads sub-folder are required to be named as the primary key of the corresponding database record.
Forexample,forimagewith id of1,youshouldstorethatasuploads/images/1.jpg.
Hint: Store the file extension as a field for each record to support multiple image formats (jpg, png, gif). Do not commit any uploaded images (except seed images) to your Git repository.
Do not store file uploads in the images folder; store file uploads in the uploads folder.
Never mix static site files with user data.
6. Seed Data (Populated Database + Images)
You should provide initial seed data for your website.
You should have at least 10 images.
You should have at least 5 tags.
At least 3 tags must applied to at least 1 image. At least 8 images need to have a tag.
At least 3 images need to have multiple tags.
All seed data should be SQL queries in your database initialization script (secure/init.sql).
INFO 2300 – Spring 2020 (04/01/2020, 10:40) Page 6/12

All seed data should be SQL queries in your database initialization script (secure/init.sql).
You are not permitted to add seed data to your development database using DB Browser for SQLite. All seed image files should be stored in a sub-folder under the uploads folder.
The sub-folder should be named the same as the corresponding database table.
All seed images should named to correspond to their respective database record’s primary key. Do not commit files in the uploads folder that are not seed files.
Your seed data and uploads should be under 10MB.
7. Best Practices
Your website’s implementation should follow best practices (see Project 1, Project 2, and class notes). This includes making efficient use of templates (PHP includes) and user-defined functions.
Filter Input
You should thoroughly filter all input.
Use parameter markers in your SQL queries
Escape Output
Make sure you escape any untrusted inputs.
You should rigorously and thoroughly test your website and forms to make sure there are no errors and everything works as intended.
8. Scope
There is no minimum/maximum number of pages you need to implement.
You do not need to implement a responsive design with media queries.
You must filter input and escape output to protect your database and your users. Your forms should provide corrective feedback.
Your forms do not need to be sticky.
INFO 2300 – Spring 2020 (04/01/2020, 10:40) Page 7/12

Milestone 1: Design, Plan, and Draft Website
In Milestone 1, you’ll design and plan your website and database. You’ll also generate seed data and build a draft of your website.
Complete the Milestone 1 section of the design journey: documents/design-journey.md. 1. Design
Design your Project 3 website. When designing take care to meet the gallery requirements above. Design is substantial component of Project 3. Take it seriously. You should strive to produce a well designed site that you would be proud to include in your portfolio.
Feel free to annotate your sketches with notes about how the behavior or design changes based on certain conditions. For example, “only show tags for this image…”
There is no minimum number of sketches. Include as many as necessary to fully plan out your website’s design.
2. HTTP Requests
Your gallery will need a quite a few HTTP requests to function. Plan out your requests in the design journey. Use your final design to determine every request you need, what that request does, what type of HTTP request it is (GET or POST), and what parameters you will pass along in your request.
Carefully follow the request requirements above.
3. Plan your Database Schema & Queries
Plan out your database schema.
Take the time to carefully plan your database. The extra care you put into planning your database will save you time in later milestones with less complex SQL queries. Trust me, poorly planned databases can lead to some pretty impossible SQL queries.
Think about the types of SQL queries you need to implement your design.
You may feel totally lost here. You may feel like there are so many requirements and you don’t know where to begin. This is normal. It’s difficult learning how to take the tools from your web programmer’s toolbox and use those to implement something new. Take it one step at a time. Think about how you might write a query for each of the requirements above. After you go through this process, you’ll starting feeling a lot more confident about this!
INFO 2300 – Spring 2020 (04/01/2020, 10:40) Page 8/12

4. Plan PHP Code
Plan out the structure of your website.
Tip: Do not have a PHP page for each table in your database. You’ll often discover that the way you present information to your user is different than the way you store that information in the database.
Plan out your pseudocode for each page. Each page’s pseudocode should indicate where your planned database queries will execute. They should also take care to filter input, and escape output.
For example, you might want to do something like this:
if the user uploaded image (SELECT user_id from images WHERE id = 4) then
show all tags (SELECT * from tags WHERE image_id = 4) to the user so they can delete them if
they want
if user clicks on delete link (image.php?action=delete_tag&tag_id=10&image_id=4), then (DELETE tag_id = 5 for image_id = 4 in image_tags table)
(double check during filter input that current user “owns” the image before permitting
deletion of tag)
end
end
Note: Observe how I’m using query string parameters to pass information back to the server in order to complete certain requests.
It’s okay if your plan doesn’t exactly match your final code, although it should be close. The point of this exercise is to have you thoroughly think through how you will implement this before you start coding. It’s a lot easier to starting coding once you’ve thought through how you will code it. I really want to help you develop this habit of planning first, and coding second. It really does save you time in the long run and you’ll often be expected to do this for most programming related jobs.
Tip: Feel free to utilize user-defined functions to make this process easier for you.
INFO 2300 – Spring 2020 (04/01/2020, 10:40) Page 9/12

5. Database Creation & Seed Data
Create your database based on your plan and populate it with initial seed data. Take care to meet the “Seed Data” requirements above.
You are not permitted to create or populate your database with DB Browser for SQLite. For this project you will need to write the SQL queries to create the tables and insert the seed data in the secure/init.sql file. You will need to look up the reference documentation for creating tables in SQL. We are not covering this in lecture or labs because we want you to have practice with SQL reference documentation.
We are using this script approach (init.sql) so that once Project 3 is over we can help you deploy your website to an actual live production server. Recall that production servers do not usually support SQLite. Instead they use MySQL or PostgreSQL. In order to deploy your website you’ll need to have an SQL script ready in order to create the database on actual web server.
You will also need to upload seed image files for your seed data. Follow the requirements in “Seed Data” above.
6. Draft Website
Start coding up your website. You should try to get as much of the HTML and CSS done as you can. That way you can focus on the PHP and SQL in future milestones. It’s okay if you don’t get very far yet. Just get it started.
INFO 2300 – Spring 2020 (04/01/2020, 10:40) Page 10/12

Milestone 2: Gallery
In Milestone 2, you’ll begin to implement the gallery.
1. Implementation Requirements
The user should be able to view all images in a gallery.
The user should be able to view a single image and it’s details.
The user should be able to upload an image.
Your tags should be mostly there, but it’s okay if some of it doesn’t yet work.
2. Connecting to the Database
Since we’re no longer using DB Browser for SQLite, you’ll need handle creating your database differently to support deploying our website after you’ve finished this Project.
You will need to connect to your database using the
$db = open_or_init_sqlite_db(‘secure/gallery.sqlite’, ‘secure/init.sql’); functionprovidedforyouin includes/init.php. This function checks to see if your SQLite database exists, if it does it connects to it. If it does not exists (i.e. there is no gallery.sqlite file) then this function creates the database initializing it with your secure/init.sql script. If you want to delete your database, just delete the gallery.sqlite file and the next time you refresh a page in your web browser, your database will be recreated!
IMPORTANT! If you change secure/init.sql you must delete secure/gallery.sqlite to regenerate the database. Many students fail to do this before their final submission only to submit broken init.sql scripts. The database then fails to create for the grader and we pretty much have to give you a 0 because your Project 3 won’t work without a database. CHECK CHECK CHECK your init.sql script by deleting gallery.sqlite and visiting index.php in your browser BEFORE you submit!
Warning: Do not run the PHP Server and DB Browser for SQLite at the same time! This can cause your database to become corrupted. If you need to view the database, stop the PHP server and then open DB Browser. When you are done with DB Browser you must exit it before you restart the PHP server.
Tip: If your database does become corrupted and you need a new one, simply delete the .sqlite file from the secure folder. The next time you connect to your database from your PHP code, your database will be recreated from init.sql.
INFO 2300 – Spring 2020 (04/01/2020, 10:40) Page 11/12

Final Submission: Complete & Polished Website
You will finish coding the website that you planned in Milestone 1 and started implementing in Milestone 2. You should now implement everything necessary to meet the full requirements of the assignment. You should spend most of your time polishing your design. Your design should be professional, be aesthetically pleasing, employ visual design principles, and leverage existing design patterns to improve usability; your design should be worthy of your portfolio.
Complete the Final Submission section of the design journey: documents/design-journey.md.
Test your final web site thoroughly, especially your forms. We will try to break your web page during grading. We will
also try to inject HTML & SQL during grading.
IMPORTANT! TEST YOUR DATABASE! Delete gallery.sqlite and visit index.php and double check that your database was successfully re-created from init.sql. No database created for the graders = 0.
INFO 2300 – Spring 2020 (04/01/2020, 10:40) Page 12/12