Skip to content

Instantly share code, notes, and snippets.

@coisnepe
Created June 5, 2017 22:13
Show Gist options
  • Save coisnepe/41e2b80ee83faa914f72839fc7e00dcc to your computer and use it in GitHub Desktop.
Save coisnepe/41e2b80ee83faa914f72839fc7e00dcc to your computer and use it in GitHub Desktop.
require "sequel"
require "colorize"
Sequel.split_symbols = true
puts "==> #{'Connecting to databases'.green}"
DB = Sequel.sqlite("/Users/USERNAME_HERE/Library/Messages/chat.db", readonly: true)
puts " ✅ #{'sqlite'.cyan}"
DBM = Sequel.postgres("imessage")
puts " ✅ #{'postgres'.cyan}"
puts "==> #{'Building the query...'.green}"
@query = DB[:message].join(:chat_message_join___cmj, message_id: :ROWID)
.join(:chat, ROWID: :cmj__chat_id)
.join(:handle, ROWID: :message__handle_id)
.where(message__associated_message_guid: nil)
.select(Sequel.as(:message__ROWID, :message_rowid),
Sequel.as(:message__guid, :message_guid),
Sequel.as(:message__text, :message_text),
Sequel.as(:message__service, :message_service),
Sequel.as(:message__account, :message_account),
Sequel.as(:message__is_from_me, :message_is_from_me),
Sequel.as(:message__account_guid, :message_account_guid),
Sequel.lit("datetime(date + strftime('%s','2001-01-01'), 'unixepoch', 'localtime') AS message_datetime"),
Sequel.as(:handle__rowid, :handle_rowid),
Sequel.as(:handle__id, :handle_id),
Sequel.as(:handle__service, :handle_service),
Sequel.as(:chat__ROWID, :chat_rowid),
Sequel.as(:chat__guid, :chat_guid),
Sequel.as(:chat__chat_identifier, :chat_chat_identifier),
Sequel.as(:chat__service_name, :chat_service_name),
Sequel.as(:chat__room_name, :chat_room_name),
Sequel.as(:chat__account_login, :chat_account_login),
Sequel.as(:chat__display_name, :chat_display_name),
)
puts "==> #{'Dropping \'messages\' from postgres'.red}"
DBM.drop_table :messages
puts "==> #{'Recreating \'messages\''.green}"
DBM.create_table :messages do
primary_key :id
end
puts "==> #{'Adding tables...'.green}"
@query.first.keys.each do |col|
DBM.alter_table :messages do
if %w(message_rowid chat_rowid handle_rowid).include? col.to_s
add_column col, :integer
elsif col.to_s == "message_text"
add_column col, :text
elsif col.to_s == "message_datetime"
add_column col, 'timestamp with time zone'
elsif col.to_s == "message_is_from_me"
add_column col, :boolean
else
add_column col, String
end
end
end
timer = Time.now
puts "==> #{'Starting to insert'} #{@query.count.to_s.red} messages"
DBM.transaction do
@query.each do |record|
record[:message_is_from_me] = (record[:message_is_from_me] == 1)
DBM[:messages].insert(record)
end
end
total = Time.now - timer
puts "==> #{'Done!'.green} Total time: #{total.round(2).to_s.cyan} seconds"
puts "==> #{'Disconnect and exit'.green}"
DB.disconnect
DBM.disconnect
# <<-SQL
# SELECT `message`.`rowid`
# AS
# 'message_rowid',
# `message`.`guid`
# AS 'message_guid',
# `message`.`text`
# AS 'message_text',
# `message`.`service`
# AS 'message_service',
# `message`.`account`
# AS 'message_account',
# `message`.`is_from_me`
# AS 'message_is_from_me',
# `message`.`account_guid`
# AS 'message_account_guid',
# Datetime(date + Strftime('%s', '2001-01-01'), 'unixepoch', 'localtime')
# AS
# message_datetime,
# `handle`.`rowid`
# AS 'handle_rowid',
# `handle`.`id`
# AS 'handle_id',
# `handle`.`service`
# AS 'handle_service',
# `chat`.`rowid`
# AS 'chat_rowid',
# `chat`.`guid`
# AS 'chat_guid',
# `chat`.`chat_identifier`
# AS 'chat_chat_identifier',
# `chat`.`service_name`
# AS 'chat_service_name',
# `chat`.`room_name`
# AS 'chat_room_name',
# `chat`.`account_login`
# AS 'chat_account_login',
# `chat`.`display_name`
# AS 'chat_display_name'
# FROM `message`
# INNER JOIN `chat_message_join`
# ON ( `chat_message_join`.`message_id` = `message`.`rowid` )
# INNER JOIN `chat`
# ON ( `chat`.`rowid` = `chat_message_join`.`chat_id` )
# INNER JOIN `handle`
# ON ( `handle`.`rowid` = `message`.`handle_id` )
# WHERE ( `message`.`associated_message_guid` IS NULL )
# SQL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment