imessage.rb

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