Module: Lims::Core::Persistence::Sequel::Migrations::AddAuditTables

Defined in:
lib/lims-core/persistence/sequel/migrations/add_audit_tables.rb

Class Method Summary collapse

Class Method Details

.insert_into_revision(table, revision_table, type) ⇒ Object 



132
133
134
135
136
137
138
139
140
141
142
143
144
145
 
# File 'lib/lims-core/persistence/sequel/migrations/add_audit_tables.rb', line 132

def self.insert_into_revision(table, revision_table, type)
  %Q{ INSERT INTO #{revision_table}                                 
  SET #{
    if type == :delete
      'id = OLD.id, revision = OLD.revision+1'
    else
      table.columns.map { |c| "`#{c}` = NEW.#{c}" }.join(', ')
    end
  },
  `action` = '#{type}',
  `session_id` = @current_session_id;
  END;
  }
end

.migration(exclude_tables = {}, additional_tables_to_update = []) ⇒ 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
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
100
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
 
# File 'lib/lims-core/persistence/sequel/migrations/add_audit_tables.rb', line 3

def self.migration(exclude_tables={}, additional_tables_to_update=[])
  [:schema_info, :sessions, :primary_keys].each do |table|
    exclude_tables[table] = true
  end
  this = self
  Proc.new do
    next unless defined?(DB)
    table_names = []
    change do
      if additional_tables_to_update.size == 0
        # Create session table
        create_table :sessions do
          primary_key :id
          String :user
          String :backend_application_id
          String :parameters, :text => true
          boolean :success
          timestamp :start_time
          DateTime :end_time
        end
      end

      #create migration session
      self << <<-EOS
      INSERT INTO sessions(user, backend_application_id)
      VALUES('admin', 'lims-core');
      EOS

      session_id = DB[:sessions].order(:id).last[:id]

      tables_to_update = additional_tables_to_update.size == 0 ? DB.tables : additional_tables_to_update
      tables_to_update.each do |table_name|
        next if exclude_tables.include?(table_name)  
        table_names << table_name
        table = DB[table_name]

        # extend all tables with revision_id
        if DB[table].columns.include?(:revision)  == false
          alter_table table_name do             
            add_column :revision,  Integer, :default => 1
          end
        end


        # create history table
        revision_table = "#{table_name}_revision"
        self << <<-EOS
        CREATE TABLE #{revision_table} AS
        SELECT *, 'initial' AS `action`, #{session_id} as session_id
        FROM #{table_name}
        EOS

        puts "adding key to #{revision_table}"
        alter_table revision_table do
          add_primary_key :internal_id
          add_index [:id, :revision], :unique => true
          add_index [:id, :session_id], :unique => true
          add_foreign_key [:session_id], :sessions, :key => :id
        end


        # Create trigger              
        trigger_name = "maintain_#{table_name}_on_insert"
        self  << "DROP TRIGGER IF EXISTS #{trigger_name};"
        trigger_code = <<-EOT

        CREATE TRIGGER #{trigger_name}  AFTER INSERT ON  #{table_name}
        FOR EACH ROW
        BEGIN
        #{this.insert_into_revision(table, revision_table, :insert)}
        EOT

        puts trigger_code
        self << trigger_code

        trigger_name = "maintain_#{table_name}_on_update"
        self  << "DROP TRIGGER IF EXISTS #{trigger_name};"
        trigger_code = <<-EOT

        CREATE TRIGGER #{trigger_name}  BEFORE UPDATE ON  #{table_name}
        FOR EACH ROW
        BEGIN
        # Update the revision number
        SET NEW.revision = OLD.revision+1;
        # Update the revision table
        #{this.insert_into_revision(table, revision_table, :update)}
        EOT

        puts trigger_code
        self << trigger_code

        trigger_name = "maintain_#{table_name}_on_delete"
        self  << "DROP TRIGGER IF EXISTS #{trigger_name};"
        trigger_code = <<-EOT

        CREATE TRIGGER #{trigger_name}  BEFORE DELETE ON  #{table_name}
        FOR EACH ROW
        BEGIN
        #{this.insert_into_revision(table, revision_table, :delete)}
        EOT

        puts trigger_code
        self << trigger_code
      end

      if additional_tables_to_update.size > 0
        self << "DROP VIEW IF EXISTS revisions"
      end

      revision_tables = DB.tables.map { |table| table unless table.match(/revision/) }.compact
      revision_tables -= exclude_tables.keys

      view_code = "CREATE VIEW revisions AS " + revision_tables.map do |table_name|
        revision_table = "#{table_name}_revision"
        %Q{ SELECT '#{table_name}' AS revision_table,
        id,
        action,
        session_id
        FROM #{revision_table}

      }
      end.join(' UNION ')

      puts view_code
      self << view_code
    end
  end
end