iPhoneのメッセージアプリのメッセージを保存する

広告

iPhoneのメッセージアプリをMacに保存できないかと思って調べてみました。

iTunesのバックアップのファイルから取り出すことができるみたいですね。

~/Library/Application\ Support/MobileSync/Backup/[UDID]/3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28

識別子(UDID)はそれぞれのiPhoneによって変わります。

3d0dから始まる長いファイルの名前ですが、このファイルはiPhoneで使用されている時(バックアップされたファイルではなく)はsms.dbという名前のSQLiteのデータベースファイルになっているようです。 なので、SQLiteでアクセスしてメッセージを取り出してみます。

iTunesのバックアップで暗号化をする設定にしていたら、SQLiteでアクセスした際にError: file is encrypted or is not a databaseのようなエラーとなりました。 なので、iTunesのバックアップで暗号化している場合は、一度、暗号化の設定をしていない状態でバックアップしてからファイルをコピーします。 コピーしたらまた暗号化の設定をしておきます。

table

最初にテーブルについて定義を確認しました。

_SqliteDatabaseProperties

column-name type-name column-constraint
key TEXT UNIQUE
value TEXT

message

colmun-name type-name column-constraint
ROWID INTEGER PRIMARY KEY AUTOINCREMENT
guid TEXT UNIQUE NOT NULL
text TEXT DEFAULT 0
replace INTEGER
service_center TEXT
handle_id INTEGER DEFAULT 0
subject TEXT
country TEXT
attributedBody BLOB
version INTEGER DEFAULT 0
type INTEGER DEFAULT 0
service TEXT
account TEXT
account_guid TEXT
error INTEGER DEFAULT 0
date INTEGER
date_read INTEGER
date_delivered INTEGER
is_delivered INTEGER DEFAULT 0
is_finished INTEGER DEFAULT 0
is_emote INTEGER DEFAULT 0
is_from_me INTEGER DEFAULT 0
is_empty INTEGER DEFAULT 0
is_delayed INTEGER DEFAULT 0
is_auto_reply INTEGER DEFAULT 0
is_prepared INTEGER DEFAULT 0
is_read INTEGER DEFAULT 0
is_system_message INTEGER DEFAULT 0
is_sent INTEGER DEFAULT 0
has_dd_results INTEGER DEFAULT 0
is_service_message INTEGER DEFAULT 0
is_forward INTEGER DEFAULT 0
was_downgraded INTEGER DEFAULT 0
is_archive INTEGER DEFAULT 0
cache_has_attachments INTEGER DEFAULT 0
cache_roomnames INTEGER DEFAULT 0
was_data_detected INTEGER DEFAULT 0
was_deduplicated INTEGER DEFAULT 0
is_audio_message INTEGER DEFAULT 0
is_played INTEGER DEFAULT 0
date_played INTEGER
item_type INTEGER DEFAULT 0
other_handle INTEGER DEFAULT -1
group_title TEXT
group_action_type INTEGER DEFAULT 0
share_status INTEGER
share_direction INTEGER
is_expirable INTEGER DEFAULT 0
expire_state INTEGER DEFAULT 0
message_action_type INTEGER DEFAULT 0
message_source INTEGER DEFAULT 0
associated_message_guid STRING DEFAULT NULL
balloon_bundle_id STRING DEFAULT NULL
payload_data BLOB
associated_message_type INTEGER DEFAULT 0
expressive_send_style_id STRING DEFAULT NULL
associated_message_range_location INTEGER DEFAULT 0
associated_message_range_length INTEGER DEFAULT 0
time_expressive_send_played INTEGER DEFAULT 0
message_summary_info BLOB DEFAULT NULL

sqlite_sequence

colmun-name type-name column-constraint
name
seq

chat

colmun-name type-name column-constraint
ROWID INTEGER PRIMARY KEY AUTOINCREMENT
guid TEXT UNIQUE NOT NULL
style INTEGER
state INTEGER
account_id TEXT
properties BLOB
chat_identifier TEXT
service_name TEXT
room_name TEXT
account_login TEXT
is_archived INTEGER DEFAULT 0
last_addressed_handle TEXT
display_name TEXT
group_id TEXT
is_filtered INTEGER DEFAULT 0
successful_query INTEGER DEFAULT 1

attachment

colmun-name type-name column-constraint
ROWID INTEGER PRIMARY KEY AUTOINCREMENT
guid TEXT UNIQUE NOT NULL
created_date INTEGER DEFAULT 0
start_date INTEGER DEFAULT 0
filename TEXT
uti TEXT
mime_type TEXT
transfer_state INTEGER DEFAULT 0
is_outgoing INTEGER DEFAULT 0
user_info BLOB
transfer_name TEXT
total_bytes INTEGER DEFAULT -1
is_sticker INTEGER DEFAULT 0
sticker_user_info BLOB
attribution_info BLOB
hide_attachment INTEGER DEFAULT 0

handle

colmun-name type-name column-constraint
ROWID INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE
id TEXT NOT NULL, UNIQUE
country TEXT
service TEXT NOT NULL, UNIQUE
uncanonicalized_id TEXT

message_attachment_join

colmun-name type-name column-constraint
message_id INTEGER REFERENCES message (ROWID) ON DELETE CASCADE, UNIQUE
attachment_id INTEGER REFERENCES attachment (ROWID) ON DELETE CASCADE, UNIQUE

chat_handle_join

colmun-name type-name column-constraint
chat_id INTEGER REFERENCES chat (ROWID) ON DELETE CASCADE, UNIQUE
handle_id INTEGER REFERENCES handle (ROWID) ON DELETE CASCADE, UNIQUE

chat_message_join

colmun-name type-name column-constraint
chat_id INTEGER REFERENCES chat (ROWID) ON DELETE CASCADE, PRIMARY KEY
message_id INTEGER REFERENCES message (ROWID) ON DELETE CASCADE, PRIMARY KEY

deleted_messages

colmun-name type-name column-constraint
ROWID INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE
guid TEXT NOT NULL

messageテーブルにメッセージがありそうですね。 attachmentテーブルに添付ファイルの情報がありそうですが、実際の添付されたファイルは別のところにありそうで、今回はそこまで調べていません。 chatテーブルはLINEでいうところのトークルームのような情報がありそうです。 handleに相手のアドレスの情報があるようです。

取り出す

簡単に取り出すために次のようなコマンドとSQLにしてみました。 カレントディレクトリにsms.csvという名前でファイルを作成します。 CSV的な結果になるようにしていますが、文字コードの問題があるので、Excelでは文字化けしてしまいます。 でも、UTF-8なので、絵文字はちゃんと表示されます。

$ sqlite3 3d0d7e5fb2ce288813306e4d4636395e047a3d28
sqlite> .output ./sms.csv
sqlite> .separator ,
sqlite> SELECT
  '"' || T2.text || '"'
  , T2.subject
  , datetime(T2.date + 978307200, 'unixepoch', 'localtime') AS date
  , CASE T2.is_from_me WHEN 0 THEN 'Received' WHEN 1 THEN 'Sent' ELSE 'Unknown' END AS is_from_me
  , T3.id
FROM
  chat_message_join T0
  INNER JOIN
    chat T1
  ON
    T0.chat_id = T1.ROWID
  INNER JOIN
    message T2
  ON
    T0.message_id = T2.ROWID
  INNER JOIN
    handle T3
  ON
    T2.handle_id = T3.ROWID
ORDER BY
  T2.date DESC;

Node.jsとSQLite

Node.jsとSQLiteからアクセスしたソースも書いておきます。 こちらはExcelで開けるようにShift_JISに変換してみました。 その代わり絵文字は文字化けしてしまいます。

sqlite3iconvのnpmパッケージを使ってみました。

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./3d0d7e5fb2ce288813306e4d4636395e047a3d28')
const Iconv = require('iconv').Iconv;
const sjis = new Iconv('UTF-8', 'Shift_JIS//TRANSLIT//IGNORE');
const fs = require('fs');

const filename = './sms.csv';
db.serialize(() => {
  const sql = [];
  sql.push('SELECT');
  sql.push('  T2.text');
  sql.push('  , T2.subject');
  sql.push('  , datetime(T2.date + 978307200, \'unixepoch\', \'localtime\') AS date');
  sql.push('  , CASE T2.is_from_me WHEN 0 THEN \'Received\' WHEN 1 THEN \'Sent\' ELSE \'Unknown\' END AS is_from_me');
  sql.push('  , T3.id');
  sql.push('FROM');
  sql.push('  chat_message_join T0');
  sql.push('  INNER JOIN');
  sql.push('    chat T1');
  sql.push('  ON');
  sql.push('    T0.chat_id = T1.ROWID');
  sql.push('  INNER JOIN');
  sql.push('    message T2');
  sql.push('  ON');
  sql.push('    T0.message_id = T2.ROWID');
  sql.push('  INNER JOIN');
  sql.push('    handle T3');
  sql.push('  ON');
  sql.push('    T2.handle_id = T3.ROWID');
  sql.push('ORDER BY');
  sql.push('  T2.date DESC');
  db.each(sql.join(' '), (err, row) => {
    if (err) {
      console.log(JSON.stringify(err));
      return;
    }
    fs.appendFile(filename, sjis.convert(`${row.date}, ${row.id}, ${row.is_from_me}, ${row.subject}, \"${row.text}\"\r\n`));
  });
});

db.close();

参考

広告
広告