Class: Mkxms::Mssql::AdoptionScriptWriter::DmlTriggerAdoptionChecks

Inherits:
IndentedStringBuilder show all
Includes:
SqlStringManipulators
Defined in:
lib/mkxms/mssql/adoption_script_writer.rb

Constant Summary

Constants included from SqlStringManipulators

SqlStringManipulators::MSSQL

Constants inherited from IndentedStringBuilder

IndentedStringBuilder::NAMED_SUBSTITUTIONS

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from SqlStringManipulators

#bit_test, #boolean_desc, #dedent, #stresc, #strlit, #unquoted_identifier

Methods inherited from IndentedStringBuilder

dsl, #dsl, #each, #indented, #puts, #to_s

Constructor Details

#initialize(trigger, tools) ⇒ DmlTriggerAdoptionChecks

Returns a new instance of DmlTriggerAdoptionChecks.



1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1321

def initialize(trigger, tools)
  super()
  
  @tools = tools
  @trigger = trigger
  
  add_trigger_tests
  if trigger.clr_impl
    add_clr_impl_test
  else
    add_definition_test
  end
end

Instance Attribute Details

#trigger ⇒ Object (readonly)

Returns the value of attribute trigger.



1335
1336
1337
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1335

def trigger
  @trigger
end

Instance Method Details

#add_clr_impl_test ⇒ Object 



1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1482

def add_clr_impl_test
  dsl {
    # Check CLR implementation
    puts "IF NOT EXISTS(%s)" do
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        JOIN sys.assembly_modules asmmod ON tgr.object_id = asmmod.object_id
        JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND QUOTENAME(asm.name) = #{trigger.clr_impl.assembly.sql_quoted}
        AND QUOTENAME(asmmod.assembly_class) = #{trigger.clr_impl.asm_class.sql_quoted}
        AND QUOTENAME(asmmod.assembly_method) = #{trigger.clr_impl.method.sql_quoted}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "Trigger #{trigger.qualified_name} does not invoke #{trigger.clr_impl.full_specifier}."
    end
  }
end

#add_definition_test ⇒ Object 



1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1505

def add_definition_test
  dsl {
    # Check definition
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        JOIN sys.sql_modules sql ON tgr.object_id = sql.object_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND #{check_definition_is("sql.definition", trigger.definition)}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "Trigger #{trigger.qualified_name} does not have the expected definition."
    end
  }
end

#add_trigger_tests ⇒ Object 



1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1345

def add_trigger_tests
  dsl {
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "Expected trigger #{trigger.qualified_name} does not exist."
    end
    
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        JOIN sys.tables t ON tgr.parent_id = t.object_id
        JOIN sys.schemas ts ON t.schema_id = ts.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND QUOTENAME(ts.name) = #{trigger.table.schema.sql_quoted}
        AND QUOTENAME(t.name) = #{trigger.table.name.sql_quoted}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "Trigger #{trigger.qualified_name} does not apply to table #{trigger.table.qualified_name}."
    end
    
    puts "IF NOT EXISTS (%s)" do
      execution_identity_test = (if trigger.execute_as == 'OWNER'
        "COALESCE(sql.execute_as_principal_id, asmmod.execute_as_principal_id) = -2"
      elsif trigger.execute_as
        "(QUOTENAME(p.name) = #{trigger.execute_as.sql_quoted} OR QUOTENAME(p2.name) = #{trigger.execute_as.sql_quoted})"
      else
        "COALESCE(sql.execute_as_principal_id, asmmod.execute_as_principal_id) IS NULL"
      end)
      
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        LEFT JOIN sys.sql_modules sql ON tgr.object_id = sql.object_id
        LEFT JOIN sys.database_principals p ON p.principal_id = sql.execute_as_principal_id
        LEFT JOIN sys.assembly_modules asmmod ON tgr.object_id = asmmod.object_id
        LEFT JOIN sys.database_principals pa ON pa.principal_id = asmmod.execute_as_principal_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND #{execution_identity_test}
      }
    end
    puts "BEGIN".."END" do
      if trigger.execute_as == 'OWNER'
        puts error_sql "Trigger #{trigger.qualified_name} does not execute as its owner."
      elsif trigger.execute_as
        puts error_sql "Trigger #{trigger.qualified_name} does not execute as #{trigger.execute_as}."
      else
        puts error_sql "Trigger #{trigger.qualified_name} does not execute as caller."
      end
    end
    
    puts "IF NOT EXISTS (%s)" do
      is_instead_of_trigger_value = (trigger.timing.downcase == "after") ? 0 : 1
      
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND tgr.is_instead_of_trigger = #{is_instead_of_trigger_value}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql %Q{Trigger #{trigger.qualified_name} must occur #{trigger.timing} the handled event(s).}
    end
    
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
      }
      trigger.events.each do |ev|
        puts dedent %Q{
          AND EXISTS (
            SELECT *
            FROM sys.events ev
            WHERE ev.object_id = tgr.object_id
            AND ev.type_desc = #{ev.sql_quoted}
          )
        }
      end
    end
    puts "BEGIN".."END" do
      puts error_sql %Q{Trigger #{trigger.qualified_name} must occur #{trigger.timing.downcase} #{trigger.events.join(' and ')}.}
    end
    
    puts "IF NOT EXISTS (%s)" do
      is_not_for_replication_value = trigger.not_replicable ? 1 : 0
      
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND tgr.is_not_for_replication = #{is_not_for_replication_value}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql %Q{Trigger #{trigger.qualified_name} must#{' not' unless trigger.not_replicable} be configured "NOT FOR REPLICATION".}
    end
    
    puts "IF NOT EXISTS (%s)" do
      is_disabled_value = trigger.disabled ? 1 : 0
      
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND tgr.is_disabled = #{is_disabled_value}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "Trigger #{trigger.qualified_name} must#{' not' unless trigger.disabled} be disabled."
    end
  }
end

#check_definition_is(*args) ⇒ Object 



1341
1342
1343
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1341

def check_definition_is(*args)
  @tools.definition_matches_by_hash(*args)
end

#error_sql(s) ⇒ Object 



1337
1338
1339
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1337

def error_sql(s)
  @tools.adoption_error_sql(s)
end