# Database <style> .reveal { font-size: 24px; } </style> --- ## Website intro A library app where you can add in your favourite books as recommendations for others. --- ## Code examples --- # Teamwork - Worked together as a group - Later split into pairs to work on individual issues --- ## Criteria - [x] Authentication - [x] Express Server - [x] Well-organised modular codebase - [x] SQLite database - [x] Hosted on Fly.io - [ ] File uploads :cry: - [ ] Validate user-submitted data on the server - [ ] Handle errors and inform the user - [ ] Styled appropriately --- ## KSBs (Niete) ### Knowledge - K2 - Roles and responsibilities within the software development lifecycle (who is responsible for what) - K6 - How teams work effectively to produce software and how to contribute appropriately - K7 - Software design approaches and patterns, to identify reusable solutions to commonly occurring problems - K10 - Principles and uses of relational and non-relational databases ### Skills - S1 - Create logical and maintainable code - S2 - Develop effective user interfaces - S3 - Link code to data sets - S8 - Create simple software designs to effectively communicate understanding of the program - S10 - Build, manage and deploy code into the relevant environment ### Behaviours - B1 - Works independently and takes responsibility. For example, has a disciplined and responsible approach to risk and stays motivated and committed when facing challenges - B2 - Applies logical thinking. For example, uses clear and valid reasoning when making decisions related to undertaking work instructions - B3 - Maintains a productive, professional and secure working environment - B4 - Works collaboratively with a wide range of people in different roles, internally and externally, with a positive attitude to inclusion & diversity - B6 - Shows initiative and takes responsibility for solving problems within their own remit, being resourceful when faced with a problem to solve. - B9 - Committed to continued professional development. --- ## UX Lead - Created a style guide - Worked with Iman to create the look of the APP - Lead on project documentation by regularly updating the `README.md` file --- ## File structure  --- ## Schema ```sql BEGIN; CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT UNIQUE, email TEXT UNIQUE, hash TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS sessions ( id TEXT PRIMARY KEY, user_id INTEGER REFERENCES users(id), expires_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS foods ( id INTEGER PRIMARY KEY AUTOINCREMENT, dish_name TEXT, food_desc TEXT, user_id INTEGER REFERENCES uses(id), rating INTEGER, image_path TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); COMMIT; ``` --- ## Model  --- ## How we added a new book to the database (Iman) ```js const insert_book = db.prepare(/*sql*/ ` INSERT INTO books(name, author_id, year, genres_id) VALUES( $name, $author_id, $year, $genres_id ) RETURNING id `); function addBookToDB(book) { return insert_book.get(book); } ``` --- ## Handle POST request (Karol) ```js function handleAddBook(request, response) { let { name, author, year, genres_id } = request.body; if (Object.keys(errors).length > 0) { const body = htmlTemplate( "Add book", forms.addbookform(genres.listGenres(), errors), "All books", "/" ); response.send(body); } else { name = sanitize(name); author = sanitize(author); year = sanitize(year); let author_id = getAuthorId(author)?.id || inserteAuthorToDB({ name: author }).id; const new_book = { name, author_id, year, genres_id }; addBookToDB(new_book); response.redirect("/"); } } ``` --- ## Project Board: https://github.com/orgs/fac26/projects/9 --- # Design ---  ---  --- ## Challenges --- ## Questions?