yunpengn/specification.md

## specification.md

      
    Raw
  

              specification.md
            
          
    Specification of the software tools/frameworks used


Apache HTTP Server > 2.4 [https://httpd.apache.org]
Postgres SQL Server  ~10 [https://www.postgresql.org]
Server-side language: PHP ~7.1 [http://www.php.net]
Front-end framework:

Bootstrap 4 [https://getbootstrap.com]
Cosmo theme by Bootswatch [https://bootswatch.com/cosmo/]


3rd-party libraries:

PHP Mailer [https://github.com/PHPMailer/PHPMailer]
jQuery Data-table plugin [https://datatables.net]
FontAwesome Icon ~5.0 [https://fontawesome.com]


Non-trivial integrity constraints

Notice: Only selected ones are mentioned below. We have much more constraints to ensure fully data integrity.

Uses a trigger to make sure the owner of a pet must be associated with a user profile of type owner. Raises an exception and roll-back the query if this is not true.

CREATE OR REPLACE FUNCTION check_pet_owner_type()
RETURNS TRIGGER AS $$
DECLARE
	count integer;
BEGIN
	SELECT COUNT(*) INTO count FROM user_profiles p WHERE p.username = NEW.username AND p.type = 'owner';
	IF count <= 0 THEN
		RAISE EXCEPTION '% is not a pet owner.', NEW.username;
		RETURN NULL;
	END IF;
	RETURN NEW;
END; $$
LANGUAGE PLPGSQL;

CREATE TRIGGER pet_owner_valid_type
BEFORE INSERT OR UPDATE
ON pets
FOR EACH ROW
EXECUTE PROCEDURE check_pet_owner_type();

Makes sure the provider of a service offer must be associated with a user profile of type peter.

CREATE OR REPLACE FUNCTION check_service_provider_type()
RETURNS TRIGGER AS $$
DECLARE
	count integer;
BEGIN
	SELECT COUNT(*) INTO count FROM user_profiles p WHERE p.username = NEW.provider AND p.type = 'peter';
	IF count <= 0 THEN
		RAISE EXCEPTION '% is not a care taker.', NEW.provider;
		RETURN NULL;
	END IF;
	RETURN NEW;
END; $$
LANGUAGE PLPGSQL;

CREATE TRIGGER service_provider_valid_type
BEFORE INSERT OR UPDATE
ON service_offers
FOR EACH ROW
EXECUTE PROCEDURE check_service_provider_type();

The provider of a service offer has to choose one of the pet owners as the successful bidder for his/her service. Thus, only that owner should have its bidding status as succeed, else should all be fail.

CREATE OR REPLACE FUNCTION check_service_history_type()
RETURNS TRIGGER AS $$
DECLARE
	succeedCount integer;
	failCount integer;
	totalCount integer;
BEGIN
	-- There should be one and only one bidding for this history reflected as successful.
	SELECT COUNT(*) INTO totalCount FROM bidding b WHERE b.service_id = NEW.service_id;
	SELECT COUNT(*) INTO succeedCount FROM bidding b WHERE b.service_id = NEW.service_id AND b.status = 'succeed';
	SELECT COUNT(*) INTO failCount FROM bidding b WHERE b.service_id = NEW.service_id AND b.status = 'fail';

	IF succeedCount != 1 OR failCount != totalCount - 1 THEN
		RAISE EXCEPTION 'The corresponding bidding status is not reflected as successful.';
		RETURN NULL;
	END IF;
	RETURN NEW;
END; $$
LANGUAGE PLPGSQL;

CREATE TRIGGER service_history_valid_type
BEFORE INSERT OR UPDATE
ON service_history
FOR EACH ROW
EXECUTE PROCEDURE check_service_history_type();
Advanced SQL features

(Notice: You may see some question marks "?" in the queries below. This is used as the placeholder in PHP PDO library. This library also protects us from 1st-order SQL injection attacks.)

Creates custom enum types to ensure the input value of an attribute only consists of legal ones, i.e., a set of pre-defined, static values.

CREATE TYPE user_type AS ENUM ('owner', 'peter');
CREATE TYPE bidding_status AS ENUM ('pending', 'succeed', 'fail');

Uses transaction to ensure operation ACID properties. For instance, when the user submits the "Create Service Offer" form, makes sure the service_offer row and service_target rows are either all inserted or none are (atomicity).

BEGIN TRANSACTION;
INSERT INTO service_offers (provider, start_date, end_date, decision_deadline, expected_salary) VALUES (?, ?, ?, ?, ?) RETURNING service_id;
INSERT INTO service_target (service_id, type) VALUES (service_id, ?);
...
COMMIT;

In addition, the query aboves also makes use of the RETURNING clause, which can be used to obtain data from modified rows while they are being manipulated. In this case, since service_id is of SERIAL type, we are unknown of its value before this row is inserted. Thus, we use RETURNING clause to get its value after this new row is inserted.
Complex DML SQL queries


Use string_agg function to solve n + 1 query pitfall. We want to show all the service targets (in the format of target1, target2, ...) for each service offer on a page. However, that means we need to query the service targets for each offer on the page (and then do string concatenation in PHP code), which causes an n + 1 query. We have to solve this with raw SQL since we are not using any ORM tools. The solution is: use string_agg to let SQL server do string concatenation for you and directly return the result.

SELECT o.service_id, o.provider, o.start_date, o.end_date, o.decision_deadline, o.expected_salary,
       (SELECT string_agg(type, ', ') FROM service_target t WHERE t.service_id = o.service_id) AS target
FROM opening_offers o;

Use CASE WHEN clause to do different actions on different rows. In the example below, sets the status to succeed when the bidder is the expected one; otherwise, sets to fail.

UPDATE bidding SET status = (CASE 
	WHEN bidder = ? THEN 'succeed'::bidding_status 
	ELSE 'fail'::bidding_status END)
WHERE service_id = ?;
In addition, this query uses type conversion because the status attribute explicitly requires a bidding_status enum type.

Uses INNER JOIN on three tables to acquire enough information we want. For instance, the query below displays the history of services that a pet owner received.

SELECT o.service_id, o.provider AS person, h.pet_name, o.start_date, o.end_date,
	b.points, h.review_for_taker AS review, h.rating_for_taker AS rating 
FROM service_offers o INNER JOIN bidding b ON o.service_id = b.service_id
	INNER JOIN service_history h ON b.service_id = h.service_id 
	AND b.bidder = h.owner AND b.pet_name = h.pet_name 
WHERE h.owner = ?;