SQLite3 URLに付随する情報を保存するDB設計とSQL

# 0.初めに
**1：開発を決意した流れ**
1. お気に入りのサイト検索やログイン情報を保存しときたい
1. DBがいいんだけど、ローカルで使いたい
1. SQLiteがあるやんけ!

**2：開発環境**
- SQLite3
- A5M2

**3：この記事を読むメリット**
1. SQLiteの導入からSQLまでが分かる
1. A5M2の使い方が分かる

# 1.SQLite3の導入からDB作成
[公式サイト](https://www.sqlite.org/download.html)からOSにあったSQLite3をダウンロードします。
sqlite-tools-ほにゃららってヤツがダウンロード対象です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/fa0494ce-1036-9561-29ab-e9497641c9a4.png)

ダウンロードできたらCドライブ直下にSQLiteフォルダを作成
解凍して中身を先ほど作ったフォルダ内にいれます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/36f53a8d-b396-79b3-b4d5-aecb7edecff1.png)
完了したらDBを作成していきます。
フォルダアドレスバーをクリックしてcmdと入力します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/8e375e9f-520b-4e49-d8e5-ffca59e538d1.png)
すると、コマンドプロンプトが開きます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/c973260e-c242-83e6-f206-866ecaef9606.png)

こちらで以下のコードを打ち込みます。
```.sql
sqlite3 devDB.db
```
```.sql
CREATE TABLE test(id);
```
実行後にdevDB.dbが作成されていれば成功です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/9f7b6a7e-e48c-96c0-869e-bc29e7b1de7d.png)

# 2.A5M2の導入からテーブル作成
[公式サイト](https://a5m2.mmatsubara.com/)からOSにあったA5M2をダウンロードします。

完了したらA5M2を起動します。
まずはDB接続から。
データベースアイコンを右クリック→データベースの追加と削除をクリック。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/ab9510e0-1e6e-b035-ff8a-26d806163923.png)

追加ボタンを押して接続タイプをSQLiteにします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/054ce224-602d-e10d-e1b4-a98b43f3e960.png)

データベースを先ほど作ったdevDB.dbのパスを入力し、テスト接続を押します。
この時に「接続に成功しました。」と出ればOKです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/9af90b94-0406-f145-cac9-e54db892068a.png)

OKボタン→OKボタンでデータベースにdevDB.dbを追加できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/5b572361-8329-2227-0bf3-aab14b7e4229.png)

ではDBに接続していきます。
データベース内のdevDB.dbをダブルクリック→接続クリック
コレで接続が完了します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/d8efe9de-126e-1bba-ea0c-7c528b9d6467.png)

接続が終わったのでSQLを使ってテーブルを作ります。
ファイル→新規からSQLをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/e900f86e-c89f-d7d4-8a77-eb7df00e921f.png)
データベースがdevDB.dbになっていることを確認してSQLを実行していきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/d08d9f05-33f0-7bad-691f-3b4a7b0c5372.png)
では、実際にテーブルを作っていきます。
```.sql
--テーブル作成
CREATE TABLE SaveURL( 
  UrlId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
  , Type TEXT NOT NULL
  , URL TEXT NOT NULL
  , LoginId TEXT
  , Password TEXT
  , Memo TEXT
  , DeletionFlag INTEGER NOT NULL
  , CreatedUser TEXT NOT NULL
  , CreatedAt TEXT NOT NULL
  , UpdateUser TEXT NOT NULL
  , UpdateAt TEXT NOT NULL
);
```
一行目にカーソルが合っている状態でCtrl+EnterでSQLを実行します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/946672a7-708b-59f0-b533-d5714899b84d.png)
実際にテーブルが作られたか確認します。
次のSQLを実行してください。
```.sql
--全件検索
SELECT
    UrlId
  , Type
  , URL
  , LoginId
  , Password
  , Memo
  , DeletionFlag
  , CreatedUser
  , CreatedAt
  , UpdateUser
  , UpdateAt
FROM
    SaveURL
;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/bb0676a1-7267-7585-3e67-8dccb9c151a8.png)
データが空の状態なので味気ないですね
では、データを挿入していきましょう。
```.sql
--挿入テンプレート
INSERT INTO
SaveURL(
  Type
  , URL
  , LoginId
  , Password
  , Memo
  , DeletionFlag
  , CreatedUser
  , CreatedAt
  , UpdateUser
  , UpdateAt
)
VALUES(
  'メモ'
  ,'test.com'
  , 'test'
  , 'password'
  ,'DeletionFlag=1 論理削除'
  , 1
  ,'管理者'
  ,datetime('now', 'localtime')
  ,'管理者'
  ,datetime('now', 'localtime')
);
```
実行後にもう一度、全件検索のSQLを流します。
先ほど挿入したデータがあればOKです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/ac569387-d664-b02a-eb8f-ed01bae6357a.png)

# 3.SQLでデータの抽出

**※[SQL生成ツール](https://codepen.io/DoNotPrayDebug/full/XWBdLgX)をWebに公開しています。**
得意じゃないかたは是非ご活用ください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/aa955cf3-216b-3cff-0c87-2982a5e03f7d.png)


テスト用データを使ってデータ抽出をやります。
※よく使うパターンのみ紹介です。
今回の全データはこちらになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/c2279103-8875-b9ce-7f8e-6b68f55aa04e.png)
```.sql
--削除以外を取得
SELECT
    UrlId
  , Type
  , URL
  , LoginId
  , Password
  , Memo
  , DeletionFlag
  , CreatedUser
  , CreatedAt
  , UpdateUser
  , UpdateAt
FROM
    SaveURL
WHERE
    DeletionFlag = 0
;
```
UrlIdが1以外が抽出されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/04f77a38-e4e1-a694-ee9b-131a5b3c33c9.png)
```.sql
--Type検索
SELECT
    UrlId
  , Type
  , URL
  , LoginId
  , Password
  , Memo
  , DeletionFlag
  , CreatedUser
  , CreatedAt
  , UpdateUser
  , UpdateAt
FROM
    SaveURL
WHERE
    DeletionFlag = 0
    AND Type = 'Twitter'
;
```
TypeがTwitterのデータのみが抽出できます(2件)![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2870946/8cab4236-1cbb-0e6f-b7c4-64894111c880.png)
# 4.終わり
ここまで記事を読んでいただき、ありがとうございます。

連絡や質問をしたい方は[Twitter](https://twitter.com/DebugDon)のDMで受け付けています。
企業、個人どちらとも大歓迎で対応します!