Module: PSQLCM

Defined in:
lib/psql-cm/cli.rb,
lib/psql-cm/base.rb,
lib/psql-cm/dump.rb,
lib/psql-cm/setup.rb,
lib/psql-cm/submit.rb,
lib/psql-cm/restore.rb,
lib/psql-cm/version.rb,
lib/psql-cm/database.rb

Defined Under Namespace

Classes: CLI, Connection, Console

Constant Summary collapse

Version = 
'0.2.2'

Class Method Summary collapse

Class Method Details

.config ⇒ Object 



16
17
18
 
# File 'lib/psql-cm/base.rb', line 16

def config
  @config ||= OpenStruct.new
end

.configure! ⇒ Object 



75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
 
# File 'lib/psql-cm/database.rb', line 75

def configure!
  begin
    uri = URI.parse(::PSQLCM.uri)
  rescue => error
    halt! "PostgreSQL URI was incorrectly specified, format is:\n  --uri=postgres://{user}:{password}@{host}:{port}/{database}\nwhere user, password, port and database are optional."
  end

  query = uri.query.to_s.split('&')

  timeout = query.detect { |k| k.match /connect_timeout=/ }.to_s.sub(/.*=/,'')
  sslmode = query.detect { |k| k.match /sslmode=/ }.to_s.sub(/.*=/,'')

  database = uri.path.split('/').first.to_s
  database = 'postgres' if database.empty?

  unless @config.databases.detect { |name| name == database }
    @config.databases << database
  end

  @config.connection = {
    :host => uri.host,
    :port => uri.port || 5432,
    :dbname => database,
    :user => uri.user || ENV['USER'],
    :password => uri.password,
    :connect_timeout => timeout.empty? ? 20 : timeout.to_i,
    :sslmode => sslmode.empty? ? "disable" : sslmode # (disable|allow|prefer|require)
  }.delete_if { |key, value| value.nil? }
end

.databases ⇒ Object 



20
21
22
23
24
25
26
27
28
29
30
31
32
 
# File 'lib/psql-cm/base.rb', line 20

def databases
  @databases = db.
    exec("SELECT datname as name FROM pg_database WHERE datname !~ 'template*|postgres';").
    map {|row| row['name']}

  if config.databases.empty?
    halt! 'A list of databases must be given:\n  --databases={database_one}[,{database_two}[,...]]'
  else # filter out databases not specified.
    @databases.select!{ |name| config.databases.include?(name) }
  end
  debug "databases> #{@databases}"
  @databases
end

.db(name = 'postgres') ⇒ Object 



67
68
69
70
71
72
73
 
# File 'lib/psql-cm/database.rb', line 67

def db(name = 'postgres')
  @db ||= {}
  return @db[name] if @db[name]
  @config.connection || configure!

  @db[name] = Connection.new(:dbname => name)
end

.debug(*message) ⇒ Object 



7
8
9
 
# File 'lib/psql-cm/base.rb', line 7

def debug(*message)
  $stdout.puts message.join(' ') if config.debug
end

.dump! ⇒ Object 



3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
# File 'lib/psql-cm/dump.rb', line 3

def dump!
  debug "dump> sql_path: #{sql_path}"
  FileUtils.mkdir(sql_path) unless File.directory?(sql_path)
  Dir.chdir(sql_path) do
    debug "tree> #{tree}"
    tree.each_pair do |database, database_schemas|
      debug "dump> database: #{database}"
      FileUtils.mkdir_p(File.join(sql_path,database))
      database_schemas.each do |schema|
        debug "dump> schema: #{schema}"
        FileUtils.mkdir_p(File.join(sql_path,database))

        cm_file = File.join(sql_path,database,"#{schema}.sql")

        sh %W[ pg_dump #{db(database).psql_args}
             --schema=#{schema} --file=#{cm_file}
             --table=#{schema}.#{config.cm_table} #{database}
        ].join(' ')
      end
    end

    sh "git init; git add ." unless File.exists?('.git') && File.directory?('.git')

    sh "git commit -a -m 'PostgreSQL Change Management (psql-cm).\nDatabases: #{databases.join(', ')}\nTree: #{tree}'"
  end
end

.halt!(*message) ⇒ Object 



11
12
13
14
 
# File 'lib/psql-cm/base.rb', line 11

def halt!(*message)
  $stderr.puts message.join(' ')
  exit 1
end

.restore! ⇒ Object 



3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 
# File 'lib/psql-cm/restore.rb', line 3

def restore!
  File.directory?(sql_path) or
    halt! "Cannot restore from sql-path (#{sql_path}), it does not exist!"

  Dir.chdir(sql_path) do

    Dir['*'].each do |database|
      next unless File.directory? database

      Dir.chdir(database) do
        ensure_database_exists(database)

        debug "restore> #{database}"
        Dir['*.sql'].each do |cm_file|
          next if File.size(cm_file) == 0

          schema = cm_file.sub(".sql",'')
          ensure_schema_exists(database,schema)

          psqlrc_file = File.join(ENV['HOME'],'.psqlrc')
          FileUtils.touch(psqlrc_file) unless File.exists?(psqlrc_file)
          psqlrc = File.read(psqlrc_file)

          file = File.open(psqlrc_file,'w')
          file.write "SET search_path TO #{schema}; "
          file.close

          begin
            tag = "restore:#{database}:#{schema}>"
            debug tag, cm_file
            sh "psql #{db(database).psql_args} #{database} < #{cm_file}"

            ensure_cm_table_exists(database,schema)

            sql = "SELECT content from #{schema}.#{config.cm_table}
                   WHERE is_base IS true ORDER BY created_at ASC;"
            debug tag, "base:sql> #{sql}"
            db(database).exec(sql).each do |base_row|
              debug "BASE content:", base_row['content']
              tempfile = Tempfile.open('base.sql')
              tempfile.write(base_row['content'])
              sh "psql #{db(database).psql_args} #{database} < #{tempfile.path}"
              tempfile.close
            end

            sql = "SELECT content from #{schema}.#{config.cm_table}
                   WHERE is_base IS false
                   ORDER BY created_at ASC;"

            debug tag, "changes:sql> #{sql}"
            changes = db(database).exec(sql)
            debug tag, "change:count>#{changes.cmd_tuples}"
            changes.each do |row|
              debug tag, "content>\n#{row['content']}"
              tempfile = Tempfile.open('change.sql')
              tempfile.write(row['content'])
              sh "psql #{db(database).psql_args} #{database} < #{tempfile.path}"
              tempfile.close
            end
          ensure
            file = File.open(psqlrc_file,'w')
            file.write psqlrc
            file.close
          end
        end
      end
    end
  end
end

.run!(action = config.action) ⇒ Object 



58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/psql-cm/base.rb', line 58

def run!(action = config.action)
  case action
  when 'console'
    require 'psql-cm/cli'
    ::PSQLCM::Console.run!
  when 'dump'
    dump!
  when 'restore'
    restore!
  when 'setup'
    setup!
  when 'submit'
    submit!
  else
    halt! "An action must be given! {setup, dump, restore}" if action.nil?
    halt! "Action '#{action}' is not handled."
  end
end

.schemas(dbname = 'postgres') ⇒ Object 



34
35
36
37
38
39
40
41
42
43
44
45
46
 
# File 'lib/psql-cm/base.rb', line 34

def schemas(dbname = 'postgres')
  schema_select = "SELECT nspname AS name FROM pg_namespace WHERE nspname !~ '^pg_.*|information_schema';"

  @schemas = db(dbname).exec(schema_select).map{|row| row['name']}

  # Filter out schemas not specified, if specified.
  unless config.schemas.empty?
    @schemas.select!{ |name| config.schemas.include?(name) }
  end

  debug "schemas> #{@schemas}"
  @schemas
end

.setup! ⇒ Object 



3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
# File 'lib/psql-cm/setup.rb', line 3

def setup!
  tree.each_pair do |database, schemas|
    ensure_database_exists(database)
    schemas(database).each do |schema|
      ensure_schema_exists(database,schema)
      ensure_cm_table_exists(database,schema)

      Tempfile.open('base.sql') do |temp_file|
        sh %W[ pg_dump #{db(database).psql_args}
        --schema-only --exclude-table=pg_psql_cm
        --schema=#{schema} --file=#{temp_file.path} #{database}
        ].join(' ')

        content = %x{cat #{temp_file.path}}
        name = %x{git config user.name}.strip
        email = %x{git config user.email}.strip
        implementer = "#{name}"
        implementer << "<#{email}>" unless email.empty?

        db(database).exec(
          "INSERT INTO #{schema}.#{config.cm_table}
          (is_base, implementer, content)
          VALUES (true, $1, $2);",
          [implementer, content]
        )
      end
    end
  end

  dump!
end

.sh(command) ⇒ Object 



77
78
79
80
81
 
# File 'lib/psql-cm/base.rb', line 77

def sh(command)
  debug "$ #{command}"
  output = %x[#{command} 2>&1 | awk '! /NOTICE/']
  debug output
end

.submit! ⇒ Object 



3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
# File 'lib/psql-cm/submit.rb', line 3

def submit!
  databases.each do |database|
    schemas(database).each do |schema|
      if config.change.to_s.empty?
        halt! "Content must be given! (--change=<file or \"sql string\">)"
      elsif File.exists?(config.change)
        content = File.open(config.change, 'r') { |file| file.read }
      else # SQL String
        content = config.change
      end

      name = %x{git config user.name}.strip
      email = %x{git config user.email}.strip
      implementer = "#{name}"
      implementer << "<#{email}>" unless email.empty?

      debug "validate> #{database}.#{schema}.#{config.cm_table}: #{config.change}"

      # Transactional Validation -- Submit if successful, blow up otherwise.
      transaction = "BEGIN;SET search_path TO #{schema},public; #{content}; COMMIT;"
      result = db(database).exec(transaction)

      debug "submit> #{database}.#{schema}.#{config.cm_table}: #{config.change}"
      db(database).exec(
        "INSERT INTO #{schema}.#{config.cm_table}
          (is_base,implementer,content)
          VALUES (false,$1,$2)",
            [implementer,content]
        )
    end # schemas
  end # databases
end

.tree ⇒ Object 



48
49
50
51
52
53
54
55
56
 
# File 'lib/psql-cm/base.rb', line 48

def tree
  return @tree if @tree
  @tree = {}
  databases.each do |dbname|
    @tree[dbname] = schemas(dbname)
  end
  debug "tree> #{@tree}"
  @tree
end

.uri ⇒ Object 



83
84
85
86
87
 
# File 'lib/psql-cm/base.rb', line 83

def uri
  return config.uri unless config.uri.to_s.empty?
  $stdout.puts "NOTICE: uri is not set, defaulting to postgres://127.0.0.1:5432 (format: postgres://{user}:{password}@{host}:{port}/{database} (where user, password, port and database are optional)"
  config.uri = "postgres://127.0.0.1:5432"
end

.verbose(*message) ⇒ Object 



3
4
5
 
# File 'lib/psql-cm/base.rb', line 3

def verbose(*message)
  $stdout.puts message.join(' ') if (config.verbose || config.debug)
end