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

0.初めに

1：開発を決意した流れ

1. お気に入りのサイト検索やログイン情報を保存しときたい
2. DBがいいんだけど、ローカルで使いたい
3. SQLiteがあるやんけ!

2：開発環境

• SQLite3
• A5M2

3：この記事を読むメリット

1. SQLiteの導入からSQLまでが分かる
2. A5M2の使い方が分かる

1.SQLite3の導入からDB作成

公式サイトからOSにあったSQLite3をダウンロードします。
sqlite-tools-ほにゃららってヤツがダウンロード対象です。

ダウンロードできたらCドライブ直下にSQLiteフォルダを作成
解凍して中身を先ほど作ったフォルダ内にいれます。

完了したらDBを作成していきます。
フォルダアドレスバーをクリックしてcmdと入力します。

すると、コマンドプロンプトが開きます。

こちらで以下のコードを打ち込みます。

.sql

sqlite3 devDB.db

.sql

CREATE TABLE test(id);

実行後にdevDB.dbが作成されていれば成功です。

2.A5M2の導入からテーブル作成

公式サイトからOSにあったA5M2をダウンロードします。

完了したらA5M2を起動します。
まずはDB接続から。
データベースアイコンを右クリック→データベースの追加と削除をクリック。

追加ボタンを押して接続タイプをSQLiteにします。

データベースを先ほど作ったdevDB.dbのパスを入力し、テスト接続を押します。
この時に「接続に成功しました。」と出ればOKです。

OKボタン→OKボタンでデータベースにdevDB.dbを追加できます。

ではDBに接続していきます。
データベース内のdevDB.dbをダブルクリック→接続クリック
コレで接続が完了します。

接続が終わったのでSQLを使ってテーブルを作ります。
ファイル→新規からSQLをクリックします。

データベースがdevDB.dbになっていることを確認してSQLを実行していきます。

では、実際にテーブルを作っていきます。

.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を実行します。

実際にテーブルが作られたか確認します。
次のSQLを実行してください。

.sql

--全件検索
SELECT
    UrlId
  , Type
  , URL
  , LoginId
  , Password
  , Memo
  , DeletionFlag
  , CreatedUser
  , CreatedAt
  , UpdateUser
  , UpdateAt
FROM
    SaveURL
;

データが空の状態なので味気ないですね
では、データを挿入していきましょう。

.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です。

3.SQLでデータの抽出

※SQL生成ツールをWebに公開しています。
得意じゃないかたは是非ご活用ください。

テスト用データを使ってデータ抽出をやります。
※よく使うパターンのみ紹介です。
今回の全データはこちらになります。

.sql

--削除以外を取得
SELECT
    UrlId
  , Type
  , URL
  , LoginId
  , Password
  , Memo
  , DeletionFlag
  , CreatedUser
  , CreatedAt
  , UpdateUser
  , UpdateAt
FROM
    SaveURL
WHERE
    DeletionFlag = 0
;

UrlIdが1以外が抽出されます。

.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件)

4.終わり

ここまで記事を読んでいただき、ありがとうございます。

連絡や質問をしたい方はTwitterのDMで受け付けています。
企業、個人どちらとも大歓迎で対応します!