USP Browser 開発日誌 データベースの見直し

こんばんは。Nです。
夏休みも残すところ数日ですが、iOS版の開発はなかなか進展してません(-_-;)。

最近、Android、iOS版ともに関係ある点として、
USP Browserで用いているデータベースの構造を再考しようと思い、
開発そっちのけで少しSQLiteをいじっていました。

始めに、今利用している構造を紹介したいと思います。

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE cancelled_lectures (_id INTEGER PRIMARY KEY, cancelled_lecture_id INTEGER, makeup_lecture_id INTEGER, subject TEXT, professor TEXT, cancelled_lecture_date INTEGER, cancelled_lecture_period INTEGER, makeup_lecture_date INTEGER, makeup_lecture_period INTEGER, makeup_lecture_classroom TEXT, note_1 TEXT, note_2 TEXT, is_taken INTEGER, is_deleted INTEGER, UNIQUE (cancelled_lecture_id, makeup_lecture_id) ON CONFLICT IGNORE);
CREATE TABLE classroom_changes (_id INTEGER PRIMARY KEY, lecture_id INTEGER, subject TEXT, professor TEXT, lecture_date INTEGER, lecture_period INTEGER, old_classroom TEXT, new_classroom TEXT, note_1 TEXT, note_2 TEXT, is_taken INTEGER, is_deleted INTEGER, UNIQUE (lecture_id) ON CONFLICT IGNORE);
CREATE TABLE information (_id INTEGER PRIMARY KEY, information_id INTEGER, title TEXT, public_start_date INTEGER, public_end_date INTEGER, public_person TEXT, priority INTEGER, category INTEGER, is_read INTEGER, is_attached INTEGER, content TEXT, is_deleted INTEGER, is_fetched INTEGER, UNIQUE (information_id) ON CONFLICT IGNORE);
CREATE TABLE information_attachments (_id INTEGER PRIMARY KEY, information_id INTEGER, attachment_id INTEGER, url TEXT, filename TEXT, description TEXT, UNIQUE (information_id, attachment_id) ON CONFLICT IGNORE);
CREATE TABLE schedules (_id INTEGER PRIMARY KEY, student_id INTEGER, schedule_id INTEGER, title TEXT, start_date INTEGER, end_date INTEGER, start_time INTEGER, end_time INTEGER, place TEXT, priority INTEGER, content TEXT, is_deleted INTEGER, is_fetched INTEGER, UNIQUE (student_id, schedule_id) ON CONFLICT IGNORE);
sqlite>

SQLiteを含む多くのデータベースで使用するSQLという言語では、
CREATE TABLEという節を用いて、いわゆる表であるテーブルを作ります。
SQLiteでは、.schemaという命令を用いてどのようなテーブルが、
データベースに存在するのかを表示することができ、上の文字列はその結果です。

さすがに見にくいので、表にまとめたのがこちらです。
android_metaテーブルはAndroidがSQLiteを操作する際に、
自動的に使用するテーブルなので無視します

schedules
スケジュールのテーブル
_id 一意となるID
student_id 学生番号
schedule_id スケジュールのID
title タイトル
start_date 開始日付
end_date 終了日付
start_time 開始時刻
end_time 終了時刻
place 場所
priority 重要度
content 内容
is_deleted スケジュールの削除フラグ
is_fetched スケジュールの取得フラグ
student_idとschedule_idがともに重複した場合、上書きする。
information
お知らせのテーブル
_id 一意となるID
information_id お知らせのID
title タイトル
public_start_date 掲載開始日付
public_end_date 掲載終了日付
public_person 掲載者
priority 優先度
category ジャンル
is_read お知らせの既読フラグ
is_attached お知らせの添付フラグ
content 内容
is_deleted お知らせの削除フラグ
is_fetched お知らせの取得フラグ
information_idが重複した場合、上書きする。
cancelled_lectures
休講情報のテーブル
_id 一意となるID
cancelled_lecture_id 休講講義のID(ハッシュより生成)
makeup_lecture_id 補講講義のID(ハッシュより生成)
subject 講義
professor 教授
cancelled_lecture_date 休講日付
cancelled_lecture_period 休講時限
makeup_lecture_date 補講日付
makeup_lecture_period 補講時限
makeup_lecture_classroom 補講教室
note_1 備考1
note_2 備考2
is_taken 講義の履修フラグ
is_deleted 休講情報の削除フラグ
cancelled_lecture_idとmakeup_lecture_idがともに重複した場合、上書きする。
classroom_changes
教室変更のテーブル
_id 一意となるID
cancelled_lecture_id 講義のID(ハッシュより生成)
subject 講義
professor 教授
lecture_date 日付
lecture_period 時限
old_classroom 元々の教室
new_classroom 対象の教室
note_1 備考1
note_2 備考2
is_taken 講義の履修フラグ
is_deleted 教室変更の削除フラグ
lecture_idが重複した場合、上書きする。

現在のデータベースは閲覧する際には便利なテーブルになっています
IDさえ指定すればほしい情報は一括して得られるからです。
しかし、次のような欠点も抱えています。

  1. データ更新時のコスト…データの更新時に、既読といった情報は保持していなければならないため、取得済みの情報かどうかを1件1件確認しています。その作業はJavaで実装されるため、更新に要する時間は長くなります。
  2. 講義のIDの衝突…休講情報等の情報は生徒がIDを取得することができません。したがって情報を任意に特定することが可能なように、講義名と日付・時限によりハッシュを生成しています。しかし、int型で生成したハッシュコードはその組み合わせも限られており、衝突の可能性がわずかに存在します。衝突が生じた場合、任意の情報が表示されなくなります。また、AndroidとiOSでデータに互換性を持たせることが困難になります。

これらの問題を解決するために、この新しい構造を考えました。

CREATE TABLE schedule_titles(_id INTEGER PRIMARY KEY ON CONFLICT REPLACE, student_id INTEGER, date INTEGER, title TEXT, is_deleted INTEGER);
CREATE TABLE schedule_contents(_id INTEGER PRIMARY KEY ON CONFLICT REPLACE, priority INTEGER, start_time INTEGER, end_time INTEGER, place TEXT, content TEXT);
CREATE TABLE schedule_statuses(_id INTEGER PRIMARY KEY ON CONFLICT REPLACE, is_fetched INTEGER);

CREATE TABLE information_titles(_id INTEGER PRIMARY KEY ON CONFLICT REPLACE, is_read INTEGER, has_attachment INTEGER, start_date INTEGER, person TEXT, priority INTEGER, category INTEGER, title TEXT, is_deleted INTEGER);
CREATE TABLE information_contents(_id INTEGER PRIMARY KEY ON CONFLICT REPLACE, end_date INTEGER, content TEXT);
CREATE TABLE information_attachments(_id INTEGER PRIMARY KEY ON CONFLICT REPLACE, information_id INTEGER, url TEXT, filename TEXT, description TEXT);
CREATE TABLE information_statuses(_id INTEGER PRIMARY KEY ON CONFLICT REPLACE, is_fetched INTEGER, is_read INTEGER);

CREATE TABLE cancelled_lectures(_id INTEGER PRIMARY KEY, subject TEXT, professor TEXT, canceled_lecture_date INTEGER, canceled_lecture_period INTEGER, makeup_lecture_date INTEGER, makeup_lecture_period INTEGER, makeup_lecture_period TEXT, note_1 TEXT, note_2 TEXT, is_taken INTEGER, is_deleted INTEGER, UNIQUE(subject, professor, canceled_lecture_date, canceled_lecture_period) ON CONFLICT REPLACE, UNIQUE(subject, professor, makeup_lecture_date, makeup_lecture_period) ON CONFLICT REPLACE);

CREATE TABLE classroom_changes(_id INTEGER PRIMARY KEY, subject TEXT, professor TEXT, date INTEGER, period INTEGER, old_classroom TEXT, new_classroom TEXT, note_1 TEXT, note_2 TEXT, is_taken INTEGER, is_deleted INTEGER, UNIQUE(subject, professor, date, period) ON CONFLICT REPLACE);

変更点は次の通りです。

  1. テーブルの分割…データをどのタイミングで更新するかをもとにテーブルを分割しました。トップページを読み込む際に取得するタイトルとIDといった概要的な情報、詳細画面を読み込んだ時に取得する内容等の詳細な情報、またアプリ側が利用するフラグに分けることで、上書き(REPLACE)しても問題がなくなりました。
  2. 講義のIDの廃止…衝突を避けるため、IDを廃止し講義名等をSQLiteがネイティブなコードで判断するようにしました。
  3. schedule…scheduleの複数形であるschedulesは複数人のスケジュールのことを指すらしいです…。
  4. スケジュール、お知らせのIDをデータベースのIDとして利用…複数の生徒間でも一意性が確認できたので、こちらを利用するようにし、表示や更新の際のコストを低下させます。

…っと、こんな感じです。
Android版で利用するには、ある程度の作業が必要になるので、今年中にはしないと思いますorz。
最後に、データベースを新しいバージョンに移行させるためのコードもメモしておきます。
Step 1

ALTER TABLE schedules RENAME TO schedules_old;
ALTER TABLE information RENAME TO information_old;
ALTER TABLE information_attachments RENAME TO information_attachments_old;
ALTER TABLE cancelled_lectures RENAME TO cancelled_lectures_old;
ALTER TABLE classroom_changes RENAME TO classroom_changes_old;

Step 2
2つ上のコードでテーブルを作成。

Step 3

INSERT INTO schedule_titles SELECT schedule_id, student_id, start_date, title, is_deleted FROM schedules_old;
INSERT INTO schedule_contents SELECT schedule_id, priority, start_time, end_time, place, content FROM schedules_old;
INSERT INTO schedule_statuses SELECT schedule_id, is_fetched FROM schedules_old;

INSERT INTO information_titles SELECT information_id, is_read, is_attached, public_start_date, public_person, priority, category, title, is_deleted FROM information_old;
INSERT INTO information_contents SELECT information_id, public_end_date, content FROM information_old;
INSERT INTO information_attachments SELECT _id, information_id, url, filename, description FROM information_attachments_old;
INSERT INTO information_statuses SELECT information_id, is_fetched, is_read FROM information_old;

INSERT INTO cancelled_lectures SELECT _id, subject, professor, cancelled_lecture_date, cancelled_lecture_period, makeup_lecture_date, makeup_lecture_period, makeup_lecture_classroom, note_1, note_2, is_taken, is_deleted FROM cancelled_lectures_old;
INSERT INTO classroom_changes SELECT _id, subject, professor, lecture_date, lecture_period, old_classroom, new_classroom, note_1, note_2, is_taken, is_deleted FROM classroom_changes_old;

DROP TABLE schedules_old;
DROP TABLE information_old;
DROP TABLE information_attachments_old;
DROP TABLE cancelled_lectures_old;
DROP TABLE classroom_changes_old;

以上、Objective-Cを見たくなくなってきたNがお送りしました(~_~;)。

  1. コメント 0

  1. トラックバック 0

return top