---
# System prepended metadata

title: DB demo. report
tags: [sql]

---

# DB demo. report
[TOC]

reoprt link: https://hackmd.io/@how8570/DB_demo_report#/



## 實作功能 與 demo

- 查詢店家與料理，幫助使用者選擇餐廳

## :wrench:使用工具

- [GO 語言](https://golang.org/) - The Go Programming Language
- [SQLite 資料庫管理系統](https://www.sqlite.org/index.html) - a C-language library that implements a small, fast, SQL database engine...


## :package: code repo. on github

- [連結](https://github.com/how8570/sqlExercise)


## schemas



### schemas
![](https://i.imgur.com/KA1yfdm.png)


### ER model

![](https://i.imgur.com/ZLwUpi5.png)


### sql

```sql=
CREATE TABLE IF NOT EXISTS stores(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name text NOT NULL,
    open_begin time NULL, 
    open_end time NULL,
    location text NOT NULL UNIQUE,
    comment text NULL
);

CREATE TABLE IF NOT EXISTS store_tags(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    store_id INTEGER,
    tag text NULL,
    FOREIGN KEY(store_id) REFERENCES stores(id),
    UNIQUE(store_id,tag)
);

CREATE TABLE IF NOT EXISTS dishes(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    store_id INTEGER,
    name text NOT NULL,
    price INTEGER NOT NULL,
    comment text NULL,
    FOREIGN KEY(store_id) REFERENCES stores(id)
);

CREATE TABLE IF NOT EXISTS dish_tags(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    dishes_id INTEGER,
    tag text NULL,
    FOREIGN KEY(dishes_id) REFERENCES dishes(id),
    UNIQUE(dishes_id,tag)
);


CREATE TABLE IF NOT EXISTS users(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username text NOT NULL UNIQUE,
    pass text NOT NULL,
    email text NOT NULL UNIQUE
);

```






### demo 後的更新

- 移除修改按鈕
    - 改為直接從操作後台 sqlite3 修改
不再使用 web 端介面修改(./store/[id=[0-9]+] web介面仍然保留)
- 能夠使用搜尋欄搜尋店家 用 OR 區隔關鍵字
    - :warning: 能夠 sql injection 攻擊
- 能夠列出能查詢的料理



#### 查詢功能
- 單一查詢
查詢內容: 牛肉麵
![](https://i.imgur.com/9HqFXcq.png)


- 多查詢 用OR連結
查詢內容: 牛肉麵 陽春麵
![](https://i.imgur.com/4qutjux.png)



#### 列出能查詢的料理
- 按下 "查詢所有料理" 按鈕
![](https://i.imgur.com/KER39BI.png)



## About me

- 姓名: 黃上豪
- 學號: 410621221
- 系級: 資工二

###### tags: `sql`
