[Quad] 1. Intro + DCL, DDL and DML

University of Auckland students struggle with uncertainty every semester when deciding which papers to enroll in. They search for the paper they want to enroll in on search engines and social media platforms but rarely find useful information about it. Moreover, the few posts or comments they do come across are often outdated and unhelpful.

Getting Started

Therefore, I decided to create a website where UOA students can write lecture reviews and give ratings. This will allow students to enroll in the paper that best fits their needs. I was not able to find any front-end developer, but I guess this could be the perfect opportunity for me to become a full-stack developer.

This week, I have been working on the fundamentals of building a website: creating a rough draft using Figma, defining APIs in Notion, setting up the file structure with Visual Studio Code, and initializing database using DCL, DML, and DDL.

I decided to name a website Quad, name after the area at UOA where students gather and have meal together. For the backend, I will be using Spring Boot and java, while the frontend will be built with React and TypeScript. MySQL will be the database at the moment, though that may change later.

Logo

Each circle, tilted triangle, and square represents students from diverse backgrounds. The heart symbolizes the love we share and receive in our campus!

Rough draft created in Figma

The most important thing I focused on while designing the website was ensuring that the reviews are well-categorized by its department and study so that users can easily find the review they are looking for.

Visualizing API structure

Link to the API document

An API document is a structured document that organizes API endpoints by their addresses. It includes details such as HTTP methods, API addresses, variable names, data types for requests and responses, and HTTP status codes.

Creating an API document offers several benefits. In real-world development, an API document is can be used to enables frontend developers to work independently without relying on backend data. By mocking API responses, the frontend can proceed with development without waiting for the backend to be completed.

However, as a full-stack developer handling both frontend and backend, the primary reason for creating an API document is to synchronize types, variables, and error handling between the two sides. Mismatches between the frontend and backend can lead to various issues, such as API request failures due to type errors and unexpected database behavior such as saving data in wrong data type. These reasons are also why I chose to use TypeScript instead of JavaScript, ensuring stricter type consistency.

Put vs Patch

Both PUTand PATCH modify data in the database, so how do we decide which one to use? The main difference is that PUT replaces the entire resource, while PATCH updates only specific fields (partial update). This distinction might seem ambiguous at first. For example, in Quad, there is a like feature where users can toggle their like status on or off by clicking a button. I decide to implement like toggle feature by creating or deleting a Like Entity. Since I am dealing with entire resource: the Like Entity, I used PUT method instead of PATCH method.

Then why am I not using the POST method, even though I am creating a Like Entity?

The reason is that POST is not idempotent, meaning that sending the same request multiple times does not guarantee the same response each time. However, I want my LIKE feature to behave consistently every time it is called, ensuring the same result regardless of repeated requests. These are the only two behaves that I want like feature to have:

​ 1. If the user has already liked the review, the LikeEntity is deleted.

​ 2. If the user has not liked the review, a new LikeEntity is created.

Therefore, I considered using the PUT method instead of the POST method, as it is more efficient to handle both actions within a single request.

On the other hand, for editing a review, the server does not create or delete the entire review entity. Instead, they update specific fields in ready-written review such as title, content, and rating with new values.

Only certain fields in Review Entity are being modified, PATCH is the better choice for this feature.

DCL, DDL and DML

Okey! We have just finished defining what data and types we need for this website. Let’s start granting database access, creating database tables and manipulating them by adding mock data. SQL command can be categorized into three types: DCL, DDL and DML.

1. DCL(Data Control Language) is used for user access control and permissions. It grants or restricts privileges on database objects. For example, DCL command below grants Kim the ablility to SELECT, UPDATE, DELETE and INSERT on thisWebsite database.
2. DDL(Data Definition Language) is responsible for defining the database structure. Common DDL commands include CREATE, ALTER, and DROP. They affects the structure of table, but not the data itself. We can define column names and data types when creating a table using the CREATE query, modify the table structure with the ALTER query, and delete a table using the DROP query.
3. Now that we have granted user a permission using DCL and defined the table structure using DDL, it is time to actually inject mock datas and check if the features are can work correctly. DML(Data Manipulation Language) allows us to insert, update, retrieve, and delete individual records in the database. The example below shows how to insert a new course into the course table using an INSERT query.