Module: XMigra::PgSQLSpecifics

Defined in:
lib/xmigra/db_support/psql.rb

Constant Summary collapse

SYSTEM_NAME = 
'PostgreSQL'
IDENTIFIER_SUBPATTERN = 
'[[:alpha:]_][[:alnum:]_$]*|"(?:[^"]|"")+"'
DBNAME_PATTERN = 
/^
  (?:(#{IDENTIFIER_SUBPATTERN})\.)?
  (#{IDENTIFIER_SUBPATTERN})
  (\(
    (?:
      (?:#{IDENTIFIER_SUBPATTERN}\.)?#{IDENTIFIER_SUBPATTERN}
      (?:,\s*
        (?:#{IDENTIFIER_SUBPATTERN}\.)?#{IDENTIFIER_SUBPATTERN}
      )*
    )?
  \))?
$/ix

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.in_plpgsql(*args) ⇒ Object 



756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
 
# File 'lib/xmigra/db_support/psql.rb', line 756

def in_plpgsql(*args)
  variables = args[0].kind_of?(Hash) ? args.shift : {}
  s = args.shift
  name = "xmigra_" + Digest::MD5.hexdigest(s)
  
  decl_block = (if variables.length > 0
    ["DECLARE\n"].tap do |lines|
      variables.each_pair do |n, d|
        lines << "  #{n} #{d};\n"
      end
    end.join('')
  else
    ''
  end)
  
  s = s[0..-2] if s.end_with? "\n"
  XMigra.dedent(%Q{
    CREATE OR REPLACE FUNCTION #{name}() RETURNS VOID AS $$
    #{decl_block}BEGIN
    %s
    END;
    $$ LANGUAGE plpgsql;
    
    SELECT #{name}();
    DROP FUNCTION #{name}();
  }) % [XMigra.dedent(s)]
end

.oid_type(type) ⇒ Object 



795
796
797
798
799
800
801
802
803
804
 
# File 'lib/xmigra/db_support/psql.rb', line 795

def oid_type(type)
  case type
  when View then 'regclass'
  when Function then 'regprocedure'
  when Class
    raise XMigra::Error, "Invalid access object type '#{type.name}'"
  else
    raise XMigra::Error, "Invalid access object type '#{type.class.name}'"
  end
end

.string_literal(s) ⇒ Object 



784
785
786
 
# File 'lib/xmigra/db_support/psql.rb', line 784

def string_literal(s)
  "'%s'" % [s.gsub("'", "''")]
end

.strip_identifier_quoting(s) ⇒ Object 



788
789
790
791
792
793
 
# File 'lib/xmigra/db_support/psql.rb', line 788

def strip_identifier_quoting(s)
  case
  when s[0,1] == '"' && s[-1,1] == '"' then return s[1..-2].gsub('""', '"')
  else return s
  end
end

Instance Method Details

#alter_table_columns_sql_statements(col_pairs) ⇒ Object 



739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
 
# File 'lib/xmigra/db_support/psql.rb', line 739

def alter_table_columns_sql_statements(col_pairs)
  col_pairs.flat_map do |old_col, col|
    [].tap do |parts|
      if !old_col.nullable? && col.nullable?
        parts << "ALTER TABLE #{name} ALTER COLUMN #{col.name} DROP NOT NULL;"
      end
      if old_col.type != col.type
        parts << "ALTER TABLE #{name} ALTER COLUMN #{col.name} TYPE #{col.type};"
      end
      if old_col.nullable? && !col.nullable?
        parts << "ALTER TABLE #{name} ALTER COLUMN #{col.name} SET NOT NULL;"
      end
    end
  end
end

#branch_id_literal ⇒ Object 



636
637
638
639
640
 
# File 'lib/xmigra/db_support/psql.rb', line 636

def branch_id_literal
  @pgsql_branch_id_literal ||= PgSQLSpecifics.string_literal(
    XMigra.secure_digest(branch_identifier)
  )
end

#branch_upgrade_sql ⇒ Object 



654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
 
# File 'lib/xmigra/db_support/psql.rb', line 654

def branch_upgrade_sql
  return unless respond_to? :branch_identifier
  
  parts = []
  
  parts << PgSQLSpecifics.in_plpgsql({
    :UpgradeCommands => 'text', 
    :CompletedMigration => 'RECORD'
  }, %Q{
    IF #{upgrading_to_new_branch_test_sql} THEN
      RAISE NOTICE 'Migrating from previous schema branch:';
      
      FOR UpgradeCommands IN
      SELECT bu."UpgradeSql"
      FROM xmigra.branch_upgrade bu
      WHERE bu."Next" = #{branch_id_literal}
      ORDER BY bu."ApplicationOrder" ASC
      LOOP
        EXECUTE UpgradeCommands;
      END LOOP;
    
      SELECT "CompletesMigration" AS applied, "Current" AS old_branch
      INTO CompletedMigration
      FROM xmigra.branch_upgrade
      WHERE "Next" = #{branch_id_literal};
      
      DELETE FROM xmigra.applied WHERE "MigrationID" = CompletedMigration.applied;
      
      INSERT INTO xmigra.applied ("MigrationID", "VersionBridgeMark", "Description")
      VALUES (CompletedMigration.applied, TRUE, 'Branch upgrade from branch ' || CompletedMigration.old_branch || '.');
      
      RAISE NOTICE '    done';
    END IF;
    
    DELETE FROM xmigra.branch_upgrade;
    
  })
  
  if branch_upgrade.applicable? migrations
    parts << XMigra.dedent(%Q{
      INSERT INTO xmigra.branch_upgrade
      ("Current", "Next", "CompletesMigration", "UpgradeSql")
      VALUES (
        #{branch_id_literal},
        #{PgSQLSpecifics.string_literal branch_upgrade.target_branch},
        #{PgSQLSpecifics.string_literal branch_upgrade.migration_completed_id},
        #{PgSQLSpecifics.string_literal branch_upgrade.sql}
      );
    })
  else
    parts << %Q{INSERT INTO xmigra.branch_upgrade ("Current") VALUES (#{branch_id_literal});\n}
  end
  
  return parts.join("\n")
end

#check_chain_continuity_sql ⇒ Object 



280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
 
# File 'lib/xmigra/db_support/psql.rb', line 280

def check_chain_continuity_sql
  PgSQLSpecifics.in_plpgsql({:VersionBridge => "INT"}, %Q{
    IF NOT #{upgrading_to_new_branch_test_sql} THEN
      RAISE NOTICE 'Checking migration chain continuity:';
      
      VersionBridge := (
        SELECT COALESCE(MAX(m."ApplicationOrder"), 0)
        FROM xmigra.applied a
        INNER JOIN temp$xmigra_migrations m
          ON a."MigrationID" = m."MigrationID"
        WHERE a."VersionBridgeMark"
      );
      
      IF EXISTS(
        SELECT *
        FROM xmigra.applied a
        INNER JOIN temp$xmigra_migrations m
          ON a."MigrationID" = m."MigrationID"
        INNER JOIN temp$xmigra_migrations p
          ON m."ApplicationOrder" - 1 = p."ApplicationOrder"
        WHERE p."ApplicationOrder" > VersionBridge
        AND p."MigrationID" NOT IN (
          SELECT a2."MigrationID" FROM xmigra.applied a2
        )
      ) THEN
        RAISE EXCEPTION 'Previously applied migrations interrupt the continuity of the migration chain.';
      END IF;
      
      RAISE NOTICE '    done';
    END IF;
  })
end

#check_execution_environment_sql ⇒ Object 



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
72
73
74
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
 
# File 'lib/xmigra/db_support/psql.rb', line 40

def check_execution_environment_sql
  XMigra.dedent %Q{
    CREATE OR REPLACE FUNCTION enable_plpgsql() RETURNS VOID AS $$
      CREATE LANGUAGE plpgsql;
    $$ LANGUAGE SQL;

    SELECT
      CASE
      WHEN EXISTS(
        SELECT 1
        FROM pg_catalog.pg_language
        WHERE lanname = 'plpgsql'
      )
      THEN NULL
      ELSE enable_plpgsql()
      END;

    DROP FUNCTION enable_plpgsql();

    CREATE OR REPLACE FUNCTION f_raise(text)
    RETURNS VOID
    LANGUAGE plpgsql AS
    $$
    BEGIN
      RAISE EXCEPTION '%', $1;
    END;
    $$;
    
    CREATE OR REPLACE FUNCTION f_alert(text)
    RETURNS VOID
    LANGUAGE plpgsql AS
    $$
    BEGIN
      RAISE NOTICE '%', $1;
    END;
    $$;
    
    CREATE OR REPLACE FUNCTION f_resolvename(varchar(100), varchar(100)) 
    RETURNS OID 
    LANGUAGE plpgsql AS
    $$
    DECLARE
      Statement TEXT;
      Result OID;
    BEGIN
      Statement := 'SELECT ' || quote_literal($1) || '::' || $2 || '::oid;';
      EXECUTE Statement INTO Result;
      RETURN Result;
    EXCEPTION
      WHEN OTHERS THEN RETURN NULL;
    END;
    $$;

    SELECT
      CASE
      WHEN current_database() IN ('postgres', 'template0', 'template1')
        THEN f_raise('Invalid target database.')
      END;
  }
end

#check_existence_sql(for_existence, error_message) ⇒ Object 



582
583
584
585
586
587
588
589
590
591
 
# File 'lib/xmigra/db_support/psql.rb', line 582

def check_existence_sql(for_existence, error_message)
  error_message_literal = PgSQLSpecifics.string_literal sprintf(error_message, quoted_name)
  
  XMigra.dedent %Q{
    SELECT CASE
      WHEN #{existence_test_sql(!for_existence)}
      THEN f_raise(#{error_message_literal})
      END;
  }
end

#check_preceding_migrations_sql ⇒ Object 



239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
 
# File 'lib/xmigra/db_support/psql.rb', line 239

def check_preceding_migrations_sql
  branch_check = production ? XMigra.dedent(%Q{
    IF EXISTS(
      SELECT * FROM xmigra.branch_upgrade LIMIT 1
    ) AND NOT EXISTS(
      SELECT * FROM xmigra.branch_upgrade
      WHERE #{branch_id_literal} IN ("Current", "Next")
      LIMIT 1
    ) THEN
      RAISE EXCEPTION 'Existing database is from a different (and non-upgradable) branch.';
    END IF;
    
  }, '  ') : ''
  
  PgSQLSpecifics.in_plpgsql({:VersionBridge => "INT"}, %Q{
    #{branch_check[2..-1]}
    
    RAISE NOTICE 'Checking preceding migrations:';
    
    IF NOT #{upgrading_to_new_branch_test_sql} THEN
      VersionBridge := (
        SELECT COALESCE(MAX("ApplicationOrder"), 0)
        FROM xmigra.applied
        WHERE "VersionBridgeMark"
      );
      
      IF EXISTS (
        SELECT * FROM xmigra.applied a
        WHERE a."ApplicationOrder" > VersionBridge
        AND a."MigrationID" NOT IN (
          SELECT m."MigrationID" FROM temp$xmigra_migrations m
        )
      ) THEN
        RAISE EXCEPTION 'Unknown in-branch migrations have been applied.';
      END IF;
    END IF;
    
    RAISE NOTICE '    done';
  })
end

#create_and_fill_indexes_table_sql ⇒ Object 



223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/xmigra/db_support/psql.rb', line 223

def create_and_fill_indexes_table_sql
  intro = XMigra.dedent %Q{
    CREATE TEMP TABLE temp$xmigra_updated_indexes (
      "IndexID"    varchar(80) PRIMARY KEY
    ) ON COMMIT DROP;
  }
  
  insertion = XMigra.dedent %Q{
    INSERT INTO temp$xmigra_updated_indexes ("IndexID") VALUES (%s);
  }
  
  return intro + indexes.collect do |index|
    insertion % [PgSQLSpecifics.string_literal(index.id)]
  end.join("\n")
end

#create_and_fill_migration_table_sql ⇒ Object 



200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'lib/xmigra/db_support/psql.rb', line 200

def create_and_fill_migration_table_sql
  intro = XMigra.dedent %Q{
    CREATE TEMP TABLE temp$xmigra_migrations (
      "MigrationID"              varchar(80) NOT NULL,
      "ApplicationOrder"         int NOT NULL,
      "Description"              text NOT NULL,
      "Install"                  boolean DEFAULT FALSE NOT NULL
    ) ON COMMIT DROP;
  }
  
  mig_insert = XMigra.dedent %Q{
    INSERT INTO temp$xmigra_migrations ("MigrationID", "ApplicationOrder", "Description")
    VALUES (%s);
  }
  
  parts = [intro]
  migrations.each_with_index do |m, i|
    description_literal = PgSQLSpecifics.string_literal(m.description.strip)
    parts << (mig_insert % ["'#{m.id}', #{i + 1}, #{description_literal}"])
  end
  return parts.join('')
end

#create_new_indexes_sql ⇒ Object 



453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
 
# File 'lib/xmigra/db_support/psql.rb', line 453

def create_new_indexes_sql
  return nil if indexes.empty?
  PgSQLSpecifics.in_plpgsql(indexes.collect do |index|
    XMigra.dedent %Q{
      RAISE NOTICE 'Index #{index.id}:';
      
      IF EXISTS(
        SELECT * FROM temp$xmigra_updated_indexes ui
        WHERE ui."IndexID" = '#{index.id}'
        AND ui."IndexID" NOT IN (
          SELECT i."IndexID" FROM xmigra.indexes i
        )
      ) THEN
        RAISE NOTICE '    creating...';
        
        EXECUTE #{PgSQLSpecifics.string_literal index.definition_sql};
        
        INSERT INTO xmigra.indexes ("IndexID", "name")
        VALUES ('#{index.id}', #{PgSQLSpecifics.string_literal index.quoted_name});
        
        RAISE NOTICE '    done';
      ELSE
        RAISE NOTICE '    already exists';
      END IF;
    }
    
  end.join("\n"))
end

#creation_notice ⇒ Object 



593
594
595
 
# File 'lib/xmigra/db_support/psql.rb', line 593

def creation_notice
  "SELECT f_alert(#{PgSQLSpecifics.string_literal "Creating #{printable_type} #{quoted_name}:"});"
end

#ensure_permissions_table_sql ⇒ Object 



482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
 
# File 'lib/xmigra/db_support/psql.rb', line 482

def ensure_permissions_table_sql
  "-- ------------ SET UP XMIGRA PERMISSION TRACKING OBJECTS ------------ --\n\n" +
  PgSQLSpecifics.in_plpgsql(%Q{
    RAISE NOTICE 'Setting up XMigra permission tracking:';
    
    IF NOT EXISTS (
      SELECT * FROM information_schema.schemata
      WHERE schema_name = 'xmigra'
    ) THEN
      CREATE SCHEMA xmigra;
    END IF;
    
    IF NOT EXISTS (
      SELECT * FROM information_schema.tables
      WHERE table_schema = 'xmigra' AND table_name = 'revokable_permissions'
    ) THEN
      CREATE TABLE xmigra.revokable_permissions (
        permissions varchar(200) NOT NULL,
        "object" varchar(150) NOT NULL,
        "role" oid NOT NULL
      );
    END IF;
    
  }) + XMigra.dedent(%Q{
    CREATE OR REPLACE FUNCTION xmigra.ip_prepare_revoke(varchar(200), varchar(150), varchar(80)) RETURNS VOID AS $$
    BEGIN
      INSERT INTO xmigra.revokable_permissions (permissions, "object", "role")
      SELECT $1, $2, r.oid
      FROM pg_catalog.pg_roles r
      WHERE r.rolname = $3;
    END;
    $$ LANGUAGE plpgsql;
  })
end

#ensure_version_tables_sql ⇒ Object 



101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
 
# File 'lib/xmigra/db_support/psql.rb', line 101

def ensure_version_tables_sql
  PgSQLSpecifics.in_plpgsql %Q{
    RAISE NOTICE 'Ensuring version tables:';
    
    IF NOT EXISTS(
      SELECT * FROM information_schema.schemata
      WHERE schema_name = 'xmigra'
    ) THEN
      CREATE SCHEMA xmigra;
    END IF;
    
    IF NOT EXISTS(
      SELECT * FROM information_schema.tables
      WHERE table_schema = 'xmigra' AND table_name = 'applied'
    ) THEN
      CREATE TABLE xmigra.applied (
        "MigrationID"          varchar(80) PRIMARY KEY,
        "ApplicationOrder"     SERIAL,
        "VersionBridgeMark"    boolean DEFAULT FALSE NOT NULL,
        "Description"          text NOT NULL
      );
    END IF;
    
    IF NOT EXISTS(
      SELECT * FROM information_schema.tables
      WHERE table_schema = 'xmigra' AND table_name = 'previous_states'
    ) THEN
      CREATE TABLE xmigra.previous_states (
        "Changed"                   timestamp NOT NULL,
        "MigrationApplicationOrder" int NOT NULL,
        "FromMigrationID"           varchar(80),
        "ToRangeStartMigrationID"   varchar(80) NOT NULL,
        "ToRangeEndMigrationID"     varchar(80) NOT NULL,
        
        CONSTRAINT PK_previous_states PRIMARY KEY (
          "Changed",
          "MigrationApplicationOrder"
        )
      );
    END IF;
    
    IF NOT EXISTS(
      SELECT * FROM information_schema.tables
      WHERE table_schema = 'xmigra' AND table_name = 'access_objects'
    ) THEN
      CREATE TABLE xmigra.access_objects (
        "type" varchar(40) NOT NULL,
        "name" varchar(150) PRIMARY KEY,
        "order" SERIAL
      );
    END IF;
    
    IF NOT EXISTS(
      SELECT * FROM information_schema.tables
      WHERE table_schema = 'xmigra' and table_name = 'indexes'
    ) THEN
      CREATE TABLE xmigra.indexes (
        "IndexID" varchar(80) PRIMARY KEY,
        "name" varchar(150) NOT NULL
      );
    END IF;
    
    IF NOT EXISTS(
      SELECT * FROM information_schema.tables
      WHERE table_schema = 'xmigra' and table_name = 'branch_upgrade'
    ) THEN
      CREATE TABLE xmigra.branch_upgrade (
        "ApplicationOrder" SERIAL,
        "Current" varchar(80) PRIMARY KEY,
        "Next" varchar(80) NULL,
        "UpgradeSql" text NULL,
        "CompletesMigration" varchar(80) NULL
      );
    END IF;
    
    IF NOT EXISTS(
      SELECT * FROM information_schema.views
      WHERE table_schema = 'xmigra' and table_name = 'last_applied_migrations'
    ) THEN
      CREATE VIEW xmigra.last_applied_migrations AS
      SELECT
        row_number() OVER (ORDER BY a."ApplicationOrder" DESC) AS "RevertOrder",
        a."Description"
      FROM
        xmigra.applied a
      WHERE
        a."ApplicationOrder" > COALESCE((
          SELECT ps."MigrationApplicationOrder"
          FROM xmigra.previous_states ps
          JOIN xmigra.applied a2 ON ps."ToRangeStartMigrationID" = a2."MigrationID"
          ORDER BY ps."Changed" DESC
          LIMIT 1
        ), 0);
    END IF;
    
    RAISE NOTICE '    done';
  }
end

#existence_test_sql(for_existence = true) ⇒ Object 



630
631
632
633
634
 
# File 'lib/xmigra/db_support/psql.rb', line 630

def existence_test_sql(for_existence=true)
  name_literal = PgSQLSpecifics.string_literal(quoted_name)
  oid_type_strlit = PgSQLSpecifics.string_literal(PgSQLSpecifics.oid_type(self))
  "f_resolvename(#{name_literal}, #{oid_type_strlit}) IS #{"NOT " if for_existence}NULL"
end

#filename_metavariable ⇒ Object 



24
 
# File 'lib/xmigra/db_support/psql.rb', line 24

def filename_metavariable; "[{filename}]"; end

#formatted_name ⇒ Object 



617
618
619
620
621
622
623
624
625
626
627
628
 
# File 'lib/xmigra/db_support/psql.rb', line 617

def formatted_name
  ''.tap do |result|
    name_parts.each do |p|
      if p.kind_of? Array
        result << '()'.insert(1, p.join(', '))
      else
        result << '.' unless result.empty?
        result << (yield p)
      end
    end
  end
end

#function_definition_template_sql ⇒ Object 



728
729
730
731
732
733
734
735
736
737
 
# File 'lib/xmigra/db_support/psql.rb', line 728

def function_definition_template_sql
  XMigra.dedent(%Q{
    CREATE FUNCTION [{filename}] (
      <<<parameters>>>
    ) RETURNS <<<return-type>>>
    AS $$
      <<<function-body>>>
    $$ LANGUAGE plpgsql;
  })
end

#grant_permissions_sql(permissions, object, principal) ⇒ Object 



539
540
541
542
543
544
545
546
547
548
549
 
# File 'lib/xmigra/db_support/psql.rb', line 539

def grant_permissions_sql(permissions, object, principal)
  strlit = PgSQLSpecifics.method(:string_literal)
  permissions_string = permissions.to_a.join(', ')
  
  PgSQLSpecifics.in_plpgsql %Q{
    RAISE NOTICE 'Granting #{permissions_string} on #{object} to #{principal}:';
    GRANT #{permissions_string} ON #{object} TO #{principal};
        PERFORM xmigra.ip_prepare_revoke(#{strlit[permissions_string]}, #{strlit[object]}, #{strlit[principal]});
    RAISE NOTICE '    done';
  }
end

#granting_permissions_comment_sql ⇒ Object 



535
536
537
 
# File 'lib/xmigra/db_support/psql.rb', line 535

def granting_permissions_comment_sql
  "\n-- ---------------------- GRANTING PERMISSIONS ----------------------- --\n\n"
end

#in_ddl_transaction(options = {}) ⇒ Object 



26
27
28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/xmigra/db_support/psql.rb', line 26

def in_ddl_transaction(options={})
  transaction_wrapup = begin
    if options[:dry_run]
      PgSQLSpecifics.in_plpgsql(%Q{
        RAISE NOTICE 'Dry-run successful.  Rolling back changes.';
      }) + "\nROLLBACK;"
    else
      "COMMIT;"
    end
  end
  
  ["BEGIN;", yield, transaction_wrapup].join("\n")
end

#index_template_sql ⇒ Object 



710
711
712
713
714
715
 
# File 'lib/xmigra/db_support/psql.rb', line 710

def index_template_sql
  XMigra.dedent(%Q{
    CREATE INDEX [{filename}]
    ON <<<table>>> (<<<columns>>>);
  })
end

#insert_access_creation_record_sql ⇒ Object 



551
552
553
554
555
556
 
# File 'lib/xmigra/db_support/psql.rb', line 551

def insert_access_creation_record_sql
  XMigra.dedent %Q{
    INSERT INTO xmigra.access_objects ("type", "name")
    VALUES ('#{self.class::OBJECT_TYPE}', #{PgSQLSpecifics.string_literal quoted_name});
  }
end

#migration_application_sql ⇒ Object 



558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
 
# File 'lib/xmigra/db_support/psql.rb', line 558

def migration_application_sql
  PgSQLSpecifics.in_plpgsql(%Q{
    IF EXISTS (
      SELECT * FROM temp$xmigra_migrations
      WHERE "MigrationID" = '#{id}'
      AND "Install"
    ) THEN
      RAISE NOTICE #{PgSQLSpecifics.string_literal %Q{Applying "#{File.basename(file_path)}":}};
      
      EXECUTE
    %s;
      
      INSERT INTO xmigra.applied ("MigrationID", "Description")
      VALUES ('#{id}', #{PgSQLSpecifics.string_literal description});
      
      RAISE NOTICE '    done';
    END IF;
  }) % [PgSQLSpecifics.string_literal(sql)]
end

#name_parts ⇒ Object 



597
598
599
600
601
602
603
604
605
606
607
 
# File 'lib/xmigra/db_support/psql.rb', line 597

def name_parts
  if m = DBNAME_PATTERN.match(name)
    [m[1], m[2]].compact.collect do |p|
      PgSQLSpecifics.strip_identifier_quoting(p)
    end.tap do |result|
      result << [].tap {|types| m[3][1..-2].scan(DBNAME_PATTERN) {|m| types << $&}} if m[3]
    end
  else
    raise XMigra::Error, "Invalid database object name"
  end
end

#procedure_definition_template_sql ⇒ Object

Raises:



724
725
726
 
# File 'lib/xmigra/db_support/psql.rb', line 724

def procedure_definition_template_sql
  raise XMigra::NewAccessArtifactAdder::UnsupportedArtifactType.new(:procedure, SYSTEM_NAME)
end

#production_config_check_sql ⇒ Object 



383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
 
# File 'lib/xmigra/db_support/psql.rb', line 383

def production_config_check_sql
  return if production
  
  PgSQLSpecifics.in_plpgsql %Q{
    RAISE NOTICE 'Checking for production status:';
    
    IF EXISTS(
      SELECT * FROM temp$xmigra_migrations
      WHERE "MigrationID" = '#{@migrations[0].id}'
      AND "Install"
    ) THEN
      CREATE TABLE xmigra.development (
        info varchar(200) PRIMARY KEY
      );
    END IF;
    
    IF NOT EXISTS(
      SELECT * FROM information_schema.tables
      WHERE table_schema = 'xmigra' AND table_name = 'development'
    ) THEN
      RAISE EXCEPTION 'Development script cannot be applied to a production database.';
    END IF;
    
    RAISE NOTICE '    done';
  }
end

#quoted_name ⇒ Object 



609
610
611
 
# File 'lib/xmigra/db_support/psql.rb', line 609

def quoted_name
  formatted_name {|p| '""'.insert(1, p.gsub('"', '""'))}
end

#remove_access_artifacts_sql ⇒ Object 



410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
 
# File 'lib/xmigra/db_support/psql.rb', line 410

def remove_access_artifacts_sql
  PgSQLSpecifics.in_plpgsql({:AccessObject => 'RECORD'}, %Q{
    RAISE NOTICE 'Removing data access artifacts:';
    
    FOR AccessObject IN 
    SELECT "name", "type" 
    FROM xmigra.access_objects 
    ORDER BY "order" DESC 
    LOOP
      EXECUTE 'DROP ' || AccessObject."type" || ' ' || AccessObject."name" || ';';
    END LOOP;
    
    DELETE FROM xmigra.access_objects;
    
    RAISE NOTICE '    done';
  })
end

#remove_undesired_indexes_sql ⇒ Object 



428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
 
# File 'lib/xmigra/db_support/psql.rb', line 428

def remove_undesired_indexes_sql
  PgSQLSpecifics.in_plpgsql({:IndexName => 'varchar(150)'}, %Q{
    RAISE NOTICE 'Removing undesired indexes:';
    
    FOR IndexName IN
    SELECT xi."name"
    FROM xmigra.indexes xi
    WHERE xi."IndexID" NOT IN (
      SELECT "IndexID"
      FROM temp$xmigra_updated_indexes
    )
    LOOP
      EXECUTE 'DROP INDEX ' || IndexName || ';';
    END LOOP;
    
    DELETE FROM xmigra.indexes
    WHERE "IndexID" NOT IN (
      SELECT ui."IndexID"
      FROM temp$xmigra_updated_indexes ui
    );
    
    RAISE NOTICE '    done';
  })
end

#reversion_tracking_sql ⇒ Object 



578
579
580
 
# File 'lib/xmigra/db_support/psql.rb', line 578

def reversion_tracking_sql
  %Q{DELETE FROM xmigra.applied WHERE "MigrationID" = '#{id}';\n}
end

#revoke_previous_permissions_sql ⇒ Object 



517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
 
# File 'lib/xmigra/db_support/psql.rb', line 517

def revoke_previous_permissions_sql
  "-- ------------- REVOKING PREVIOUSLY GRANTED PERMISSIONS ------------- --\n\n" +
  PgSQLSpecifics.in_plpgsql({:PermissionGrant => 'RECORD'}, %Q{
    RAISE NOTICE 'Revoking previously granted permissions:';
    
    FOR PermissionGrant IN
    SELECT p.permissions, p."object", r.rolname AS "role"
    FROM xmigra.revokable_permissions p
    INNER JOIN pg_catalog.pg_roles r
      ON p."role" = r.oid
    LOOP
      EXECUTE 'REVOKE ' || PermissionGrant.permissions || ' ON ' || PermissionGrant."object" || ' FROM ' || PermissionGrant."role";
    END LOOP;
    
    RAISE NOTICE '    done';
  })
end

#select_for_install_sql ⇒ Object 



313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
 
# File 'lib/xmigra/db_support/psql.rb', line 313

def select_for_install_sql
  PgSQLSpecifics.in_plpgsql({:VersionBridge => "INT"}, %Q{
    RAISE NOTICE 'Selecting migrations to apply:';
    
    IF #{upgrading_to_new_branch_test_sql} THEN
      VersionBridge := (
        SELECT MAX(m."ApplicationOrder")
        FROM temp$xmigra_migrations m
        INNER JOIN xmigra.branch_upgrade bu
          ON m."MigrationID" = bu."CompletesMigration"
      );
      
      UPDATE temp$xmigra_migrations
      SET "Install" = TRUE
      WHERE "ApplicationOrder" > VersionBridge;
    ELSE
      VersionBridge := (
        SELECT COALESCE(MAX(m."ApplicationOrder"), 0)
        FROM xmigra.applied a
        INNER JOIN temp$xmigra_migrations m
          ON a."MigrationID" = m."MigrationID"
          WHERE a."VersionBridgeMark"
      );
      
      UPDATE temp$xmigra_migrations
      SET "Install" = TRUE
      WHERE "MigrationID" NOT IN (
        SELECT a."MigrationID" FROM xmigra.applied a
      );
    END IF;
    
    INSERT INTO xmigra.previous_states (
      "Changed",
      "MigrationApplicationOrder",
      "FromMigrationID",
      "ToRangeStartMigrationID",
      "ToRangeEndMigrationID"
    )
    SELECT
      CURRENT_TIMESTAMP,
      -- Application order of last installed migration --
      COALESCE(
        (
          SELECT "ApplicationOrder" FROM xmigra.applied
          ORDER BY "ApplicationOrder" DESC
          LIMIT 1
        ),
        0
      ),
      ( -- Last installed migration --
        SELECT "MigrationID" FROM xmigra.applied
        ORDER BY "ApplicationOrder" DESC
        LIMIT 1
      ),
      m."MigrationID",
      ( -- Last migration to install --
        SELECT "MigrationID" FROM temp$xmigra_migrations
        WHERE "Install"
        ORDER BY "ApplicationOrder" DESC
        LIMIT 1
      )
    FROM temp$xmigra_migrations m
    WHERE "Install"
    ORDER BY "ApplicationOrder" ASC
    LIMIT 1;
    
    RAISE NOTICE '    done';
  })
end

#unquoted_name ⇒ Object 



613
614
615
 
# File 'lib/xmigra/db_support/psql.rb', line 613

def unquoted_name
  formatted_name {|p| p}
end

#upgrading_to_new_branch_test_sql ⇒ Object 



642
643
644
645
646
647
648
649
650
651
652
 
# File 'lib/xmigra/db_support/psql.rb', line 642

def upgrading_to_new_branch_test_sql
  return "FALSE" unless respond_to? :branch_identifier
  
  XMigra.dedent %Q{
    (EXISTS (
      SELECT * FROM xmigra.branch_upgrade
      WHERE "Next" = #{branch_id_literal}
      LIMIT 1
    ))
  }
end

#view_definition_template_sql ⇒ Object 



717
718
719
720
721
722
 
# File 'lib/xmigra/db_support/psql.rb', line 717

def view_definition_template_sql
  XMigra.dedent(%Q{
    CREATE VIEW [{filename}]
    AS SELECT <<<query>>>;
  })
end