EXPORTS SQLITE DATA INTO POSTGRESQL

2 - nap_export.rb

#!/usr/bin/env ruby

# 1283314500  EXPORTS NET-A-PORTER LIVE DATA INTO POSTGRESQL

require "logger"
require "sequel"
#require "pgpass"
require "fileutils"

module NAP
  LOGGERS = [Logger.new($stdout)]
  # PG = Sequel.connect(Pgpass.match(database: "productactivity").to_url)
  PG = Sequel.connect('postgres://user:password@host:port/database_name')


  require_relative "nap_model"
  Activity.create_table?

  module_function

  def log
    lambda{|*msg| LOGGERS.each{|logger| logger.__send__(*msg) }}
  end

  def import
    sources = Dir.glob("[0-9]*.sqlite").sort

    # Don't import the latest one, it might still be incomplete.
    #
    sources.pop

    sources.each do |source|
      Sequel.connect "sqlite://#{source}" do |db|
        log.(:info, "Importing #{db.uri} into #{PG.uri}")
        db[:activities].each do |row|
          next if PG[:activities][id: row[:id]]

          activity = Activity.new_using_server(PG)

          row.each do |key, value|
            activity[key] = value
          end

          activity.save
        end
      end

      FileUtils.mv source, "#{source}.done"
    end

    log.(:info, "#{Activity.count} activities in PostgreSQL")
  end
end

NAP.import

3 - nap_model.rb

#!/usr/bin/env ruby

# 1283314500  NET-A-PORTER LIVE DATABASE MODEL

Sequel::Model.plugin(:schema)
Sequel::Model.plugin(:sharding)

module NAP
  class Activity < Sequel::Model
    set_schema do
      String :id, primary_key: true

      Time :activityCreated
      String :activityChannel
      String :activityType
      String :city
      String :country
      String :hash
      String :imgURI
      String :pid
      String :title
    end
  end
end

4 - pgpass.conf

#
# CREATE USER nap PASSWORD 'nap1234';
# CREATE DATABASE productactivity OWNER nap;
#
# %APPDATA%\postgresql\pgpass.conf
#

localhost:5555:productactivity:nap:nap1234

5 - SQL Shell (pgsql)

Server [localhost]:
Database [postgres]:
Port [5555]:
Username [postgres]:
psql (9.1.3)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# CREATE USER nap PASSWORD 'nap1234';
CREATE ROLE
postgres=# CREATE DATABASE productactivity OWNER nap;
CREATE DATABASE
postgres=#