Project Technologies and Dependencies
tech-stack : ASP.NET Core Web Api 2.1, EFCore 2.1, Log4Net 2.0
Api Endpoint : http://localhost:56665/api/product/search
Headers :
AuthorizationToken: Y2xpZW50MTpjbGllbnQxX3Bhc3N3b3Jk Content-Type: application/json
Sample Request :
{ "QueryText":"J5", "MinQuantity":50, "MaxQuantity":200 }
Sample Response :
{ "productList": [ { "product": { "id": 4, "title": "Phone", "description": "Samsung Galaxy J5", "categoryId": 1, "stockQuantity": 150, "timeStamp": "2021-06-07T20:30:42.722879" }, "category": { "id": 1, "categoryName": "Electronic", "categoryDescription": "Electronic Devices & Accessories", "minStockQuantity": 100, "timeStamp": "2021-06-07T20:24:14.794287" } } ] }
Database
PostgreSQL 13.2
Application Configuration
'appsettings.json' file includes connection string and logging levels. Main connectionstring for the project is 'ProductCmsConnection' to connect Postgres database.
Database Scripts and Structures
Applicacation is using one scheme on Postgres database named 'product_cms'.
'product_cms' scheme includes 2 tables in the below,
- t_category = table stores product categories.
- t_product = table stores products belong the categories.
Scripts (DDL)
Database : Postgres
CREATE SCHEMA product_cms AUTHORIZATION postgres;
CREATE TABLE product_cms.t_product ( "Id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, "Title" varchar(200) NOT NULL, "Description" varchar(255) NULL, "CategoryId" int4 not NULL, "StockQuantity" int4 not NULL, "TimeStamp" TIMESTAMP DEFAULT NOW(), CONSTRAINT pk_t_product PRIMARY KEY ("Id") );
CREATE TABLE product_cms.t_category ( "Id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, "CategoryName" varchar(200) NOT NULL, "CategoryDescription" varchar(255) NULL, "MinStockQuantity" int4 not NULL, "TimeStamp" TIMESTAMP DEFAULT NOW(), CONSTRAINT pk_t_category PRIMARY KEY ("Id") );
ALTER TABLE product_cms.t_product ADD CONSTRAINT fk_t_product_categoryid FOREIGN KEY ("CategoryId") REFERENCES product_cms.t_category("Id");
Scripts (DCL)
CREATE USER product_cms_user WITH PASSWORD 'pg-!123postgre!-';
grant USAGE on SCHEMA product_cms to product_cms_user;
ALTER ROLE product_cms_user SET search_path TO 'product_cms';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA product_cms TO product_cms_user;
Scripts (DML)
INSERT INTO product_cms.t_category ("CategoryName", "CategoryDescription", "MinStockQuantity", "TimeStamp") VALUES('Electronic', 'Electronic Devices & Accessories', 100, now()); INSERT INTO product_cms.t_category ("CategoryName", "CategoryDescription", "MinStockQuantity", "TimeStamp") VALUES('Gift', 'Gift and Flowers', 50, now()); INSERT INTO product_cms.t_category ("CategoryName", "CategoryDescription", "MinStockQuantity", "TimeStamp") VALUES('Dress', 'Jeans, Hat, T-shirts', 150, now());
INSERT INTO product_cms.t_product("Title", "Description", "CategoryId", "StockQuantity", "TimeStamp") VALUES('Phone', 'Samsung Galaxy A50', 1, 10, now()); INSERT INTO product_cms.t_product("Title", "Description", "CategoryId", "StockQuantity", "TimeStamp") VALUES('Phone', 'Samsung Galaxy A51', 1, 10, now()); INSERT INTO product_cms.t_product("Title", "Description", "CategoryId", "StockQuantity", "TimeStamp") VALUES('Phone', 'Samsung Galaxy A52', 1, 10, now()); INSERT INTO product_cms.t_product("Title", "Description", "CategoryId", "StockQuantity", "TimeStamp") VALUES('Phone', 'Samsung Galaxy J5', 1, 150, now()); INSERT INTO product_cms.t_product("Title", "Description", "CategoryId", "StockQuantity", "TimeStamp") VALUES('Phone', 'Samsung Galaxy J6', 1, 160, now()); INSERT INTO product_cms.t_product("Title", "Description", "CategoryId", "StockQuantity", "TimeStamp") VALUES('Phone', 'Samsung Galaxy A53', 1, 10, now()); INSERT INTO product_cms.t_product("Title", "Description", "CategoryId", "StockQuantity", "TimeStamp") VALUES('Jean', 'Blue Jean', 3, 10, now()); INSERT INTO product_cms.t_product("Title", "Description", "CategoryId", "StockQuantity", "TimeStamp") VALUES('Jean', 'Casual Jean', 3, 160, now()); INSERT INTO product_cms.t_product("Title", "Description", "CategoryId", "StockQuantity", "TimeStamp") VALUES('Flower', 'Daisy', 2, 40, now()); INSERT INTO product_cms.t_product("Title", "Description", "CategoryId", "StockQuantity", "TimeStamp") VALUES('Flower', 'Rose', 2, 55, now());