if (select top 1 0 from sys.databases where name = 'NucelarMonitor') is null create database NucelarMonitor collate Latin1_General_100_CI_AS_SC_UTF8 go use NucelarMonitor if object_id(N'dbo.tables_update', N'P') is not null drop procedure dbo.tables_update go create procedure dbo.tables_update as begin set nocount on declare @database varchar(64) = 'NucelarMonitor' -- Level 0. if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Machines' and column_name = 'key') is null alter table dbo.Machines add [key] varchar(32) not null, constraint machines_uk_key unique nonclustered ([key] asc) with (fillfactor = 90), constraint machines_ck_key check ([key] not like '%[^a-zA-Z0-9_]%' and [key] like '[a-zA-Z_]%') if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Machines' and column_name = 'description') is null alter table dbo.Machines add [description] varchar(512) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Types' and column_name = 'name') is null alter table dbo.Types add [name] varchar(32) not null, constraint types_uk_name unique nonclustered ([name] asc) with (fillfactor = 90), constraint types_ck_name check ([name] not like '%[^a-zA-Z0-9_]%' and [name] like '[a-zA-Z_]%') if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Types' and column_name = 'description') is null alter table dbo.Types add [description] varchar(512) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Domains' and column_name = 'domain') is null alter table dbo.Domains add domain varchar(64) not null, constraint domains_uk_domain unique nonclustered (domain asc) with (fillfactor = 90), constraint domains_ck_domain check (len(domain) > 0 and domain not like '%[^a-zA-Z0-9_-.]%') if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Domains' and column_name = 'description') is null alter table dbo.Domains add [description] varchar(512) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Hostnames' and column_name = 'name') is null alter table dbo.Hostnames add [name] varchar(32) not null, constraint hostnames_uk_name unique nonclustered ([name] asc) with (fillfactor = 90), constraint hostnames_ck_name check (len([name]) > 0 and [name] not like '%[^a-zA-Z0-9_-]%') if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Hostnames' and column_name = 'description') is null alter table dbo.Hostnames add [description] varchar(512) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Disks' and column_name = 'name') is null alter table dbo.Disks add [name] varchar(32) not null, constraint disks_ck_name check (( len([name]) > 0 and [name] not like '%[^a-zA-Z0-9_-]%' ) or [name] like '[A-Z]:') if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Disks' and column_name = 'size') is null alter table dbo.Disks add [size] bigint not null, constraint disks_ck_size check (size >= 0 and size < power(cast(2 as bigint), 53)) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Disks' and column_name = 'mountpoint') is null alter table dbo.Disks add mountpoint varchar(128) not null, constraint disks_ck_mountpoint check ( mountpoint like '/%' or mountpoint like '[a-zA-Z]:\%' or mountpoint like '\\%' ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Disks' and column_name = 'description') is null alter table dbo.Disks add [description] varchar(512) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Interfaces' and column_name = 'name') is null alter table dbo.Interfaces add [name] varchar(32) not null, constraint interfaces_ck_name check (len([name]) > 0 and [name] not like '%[^a-zA-Z0-9_-]%') if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Interfaces' and column_name = 'description') is null alter table dbo.Interfaces add [description] varchar(512) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'CandlesTimes' and column_name = 'from') is null alter table dbo.CandlesTimes add [from] datetime not null, constraint candles_times_ck_from check ([from] < [to]) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'CandlesTimes' and column_name = 'to') is null alter table dbo.CandlesTimes add [to] datetime not null, constraint candles_times_ck_to check ([from] < [to]) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'CandlesTimes' and column_name = 'iterations') is null alter table dbo.CandlesTimes add iterations integer not null, constraint candles_times_ck_iterations check (iterations > 0) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'BigData' and column_name = 'hash') is null alter table dbo.BigData add [hash] binary(64) not null, constraint big_data_uk_hash unique nonclustered ([hash] asc) with (fillfactor = 90) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'BigData' and column_name = 'value') is null alter table dbo.BigData add [value] varchar(max) not null if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Messages' and column_name = 'key') is null alter table dbo.Messages add [key] varchar(128) not null, constraint messages_uk_key unique nonclustered ([key] asc) with (fillfactor = 90), constraint messages_ck_key check ([key] not like '%[^a-zA-Z0-9_]%' and [key] like '[a-zA-Z_]%') if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Databases' and column_name = 'name') is null alter table dbo.Databases add [name] varchar(64) not null, constraint databases_uk_name unique nonclustered ([name] asc) with (fillfactor = 90), constraint databases_ck_name check ([name] not like '%[^a-zA-Z0-9_]%' and [name] like '[a-zA-Z_]%') -- Level 1. if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineCPU' and column_name = 'machine') is null alter table dbo.MachineCPU add machine integer not null, constraint machine_cpu_fk_machine foreign key (machine) references dbo.Machines(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineCPU' and column_name = 'candle_time') is null alter table dbo.MachineCPU add candle_time integer not null, constraint machine_cpu_fk_candle_time foreign key (candle_time) references dbo.CandlesTimes(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineCPU' and column_name = 'in') is null alter table dbo.MachineCPU add [in] float not null, constraint machine_cpu_ck_cpu_in check ( [in] between 0 and 100 and [in] between minimum and maximum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineCPU' and column_name = 'out') is null alter table dbo.MachineCPU add [out] float not null, constraint machine_cpu_ck_cpu_out check ( [out] between 0 and 100 and [out] between minimum and maximum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineCPU' and column_name = 'minimum') is null alter table dbo.MachineCPU add minimum float not null, constraint machine_cpu_ck_cpu_minimum check ( minimum between 0 and 100 and minimum <= maximum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineCPU' and column_name = 'maximum') is null alter table dbo.MachineCPU add maximum float not null, constraint machine_cpu_ck_cpu_maximum check ( maximum between 0 and 100 and maximum >= minimum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineCPU' and column_name = 'average') is null alter table dbo.MachineCPU add average float not null, constraint machine_cpu_ck_cpu_average check ( average between 0 and 100 and average between minimum and maximum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineRAM' and column_name = 'machine') is null alter table dbo.MachineRAM add machine integer not null, constraint machine_ram_fk_machine foreign key (machine) references dbo.Machines(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineRAM' and column_name = 'candle_time') is null alter table dbo.MachineRAM add candle_time integer not null, constraint machine_ram_fk_candle_time foreign key (candle_time) references dbo.CandlesTimes(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineRAM' and column_name = 'total') is null alter table dbo.MachineRAM add total float not null, constraint machine_ram_ck_ram_total check ( total between 0 and power(cast(2 as bigint), 53) - 1 ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineRAM' and column_name = 'in') is null alter table dbo.MachineRAM add [in] float not null, constraint machine_ram_ck_ram_in check ( [in] between 0 and power(cast(2 as bigint), 53) - 1 and [in] between minimum and maximum and [in] <= total ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineRAM' and column_name = 'out') is null alter table dbo.MachineRAM add [out] float not null, constraint machine_ram_ck_ram_out check ( [out] between 0 and power(cast(2 as bigint), 53) - 1 and [out] between minimum and maximum and [out] <= total ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineRAM' and column_name = 'minimum') is null alter table dbo.MachineRAM add minimum float not null, constraint machine_ram_ck_ram_minimum check ( minimum between 0 and power(cast(2 as bigint), 53) - 1 and minimum <= total and minimum <= maximum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineRAM' and column_name = 'maximum') is null alter table dbo.MachineRAM add maximum float not null, constraint machine_ram_ck_ram_maximum check ( maximum between 0 and power(cast(2 as bigint), 53) - 1 and maximum between minimum and total ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineRAM' and column_name = 'average') is null alter table dbo.MachineRAM add average float not null, constraint machine_ram_ck_ram_average check ( average between 0 and power(cast(2 as bigint), 53) - 1 and average between minimum and maximum and average <= total ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisks' and column_name = 'machine') is null alter table dbo.MachineDisks add machine integer not null, constraint machine_disks_fk_machine foreign key (machine) references dbo.Machines(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisks' and column_name = 'disk') is null alter table dbo.MachineDisks add [disk] integer not null, constraint machine_disks_fk_disk foreign key ([disk]) references dbo.Disks(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisks' and column_name = 'belongs') is null alter table dbo.MachineDisks add belongs bit not null constraint machine_disks_df_belongs default 1 if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisks' and column_name = 'mounted') is null alter table dbo.MachineDisks add mounted bit not null constraint machine_disks_df_mounted default 1 if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfaces' and column_name = 'machine') is null alter table dbo.MachineInterfaces add machine integer not null, constraint machine_interfaces_fk_machine foreign key (machine) references dbo.Machines(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfaces' and column_name = 'interface') is null alter table dbo.MachineInterfaces add [interface] integer not null, constraint machine_interfaces_fk_interface foreign key ([interface]) references dbo.Interfaces(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfaces' and column_name = 'belongs') is null alter table dbo.MachineInterfaces add belongs bit not null constraint machine_interfaces_df_belongs default 1 if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfaces' and column_name = 'mounted') is null alter table dbo.MachineInterfaces add mounted bit not null constraint machine_interfaces_df_mounted default 1 if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfaces' and column_name = 'is_ipv6') is null alter table dbo.MachineInterfaces add is_ipv6 bit not null constraint machine_interfaces_df_is_ipv6 default 0 if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfaces' and column_name = 'address') is null alter table dbo.MachineInterfaces add [address] varchar(45) not null if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfaces' and column_name = 'mask') is null alter table dbo.MachineInterfaces add mask tinyint not null if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Procedures' and column_name = 'database') is null alter table dbo.Procedures add [database] integer not null, constraint procedures_fk_database foreign key ([database]) references dbo.Databases(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Procedures' and column_name = 'name') is null alter table dbo.Procedures add [name] varchar(64) not null, constraint procedures_uk_name unique nonclustered ([database] asc, [name] asc) with (fillfactor = 90), constraint procedures_ck_name check ([name] not like '%[^a-zA-Z0-9_]%' and [name] like '[a-zA-Z_]%') -- Level 2. if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksSpace' and column_name = 'machine_disk') is null alter table dbo.MachineDisksSpace add machine_disk integer not null, constraint machine_disks_space_fk_machine_disk foreign key (machine_disk) references dbo.MachineDisks(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksSpace' and column_name = 'candle_time') is null alter table dbo.MachineDisksSpace add candle_time integer not null, constraint machine_disks_space_fk_candle_time foreign key (candle_time) references dbo.CandleTimes(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksSpace' and column_name = 'candle_time') is null alter table dbo.MachineDisksSpace add free bigint not null, constraint machine_disks_space_ck_free check (free between 0 and power(cast(2 as bigint), 53) - 1) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTraffic' and column_name = 'machine_interface') is null alter table dbo.MachineInterfacesTraffic add machine_interface integer not null, constraint machine_interfaces_traffic_fk_machine_interface foreign key (machine_interface) references dbo.MachineInterfaces(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTraffic' and column_name = 'candle_time') is null alter table dbo.MachineInterfacesTraffic add candle_time integer not null, constraint machine_interfaces_traffic_fk_candle_time foreign key (candle_time) references dbo.CandleTimes(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTraffic' and column_name = 'type') is null alter table dbo.MachineInterfacesTraffic add [type] integer not null, constraint machine_interfaces_traffic_fk_type foreign key ([type]) references dbo.Types(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTraffic' and column_name = 'bytes') is null alter table dbo.MachineInterfacesTraffic add bytes bigint not null, constraint machine_interfaces_traffic_ck_bytes check ( bytes between 0 and power(cast(2 as bigint), 53) - 1 ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTraffic' and column_name = 'packets') is null alter table dbo.MachineInterfacesTraffic add packets integer not null, constraint machine_interfaces_traffic_ck_packets check ( packets between 0 and power(2, 30) - 1 ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTraffic' and column_name = 'errors') is null alter table dbo.MachineInterfacesTraffic add errors integer not null, constraint machine_interfaces_traffic_ck_errors check ( errors between 0 and power(2, 30) - 1 ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Exceptions' and column_name = 'procedure') is null alter table dbo.Exceptions add [procedure] integer not null, constraint exceptions_fk_procedure foreign key ([procedure]) references dbo.Procedures(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Exceptions' and column_name = 'message') is null alter table dbo.Exceptions add [message] integer not null, constraint exceptions_fk_message foreign key ([message]) references dbo.Messages(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Exceptions' and column_name = 'parameters') is null alter table dbo.Exceptions add parameters integer not null, constraint exceptions_fk_parameters foreign key (parameters) references dbo.BigData(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Exceptions' and column_name = 'exception') is null alter table dbo.Exceptions add exception integer not null, constraint exceptions_fk_exception foreign key (exception) references dbo.BigData(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Exceptions' and column_name = 'status') is null alter table dbo.Exceptions add [status] varchar(16) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'Exceptions' and column_name = 'code') is null alter table dbo.Exceptions add [code] integer -- Plain 0. if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'key') is null alter table dbo.MachinePlain add [key] varchar(32) not null, constraint machine_plain_uk_key unique nonclustered ([key] asc) with (fillfactor = 90), constraint machine_plain_ck_key check ([key] not like '%[^a-zA-Z0-9_]%' and [key] like '[a-zA-Z_]%') if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'machine') is null alter table dbo.MachinePlain add machine integer not null, constraint machine_plain_fk_machine foreign key (machine) references dbo.Machines(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'candle_time') is null alter table dbo.MachinePlain add candle_time integer not null, constraint machine_plain_fk_candle_time foreign key (candle_time) references dbo.CandlesTimes(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'machine_ram') is null alter table dbo.MachinePlain add machine_ram integer not null, constraint machine_plain_fk_machine_ram foreign key (machine_ram) references dbo.MachinesRAM(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'machine_cpu') is null alter table dbo.MachinePlain add machine_cpu integer not null, constraint machine_plain_fk_machine_cpu foreign key (machine_cpu) references dbo.MachinesCPU(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'ram_total') is null alter table dbo.MachinePlain add ram_total float not null, constraint machine_plain_ck_ram_total check ( ram_total between 0 and power(cast(2 as bigint), 53) - 1 ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'ram_in') is null alter table dbo.MachinePlain add ram_in float not null, constraint machine_plain_ck_ram_in check ( ram_in between 0 and power(cast(2 as bigint), 53) - 1 and ram_in between ram_minimum and ram_maximum and ram_in <= ram_total ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'ram_out') is null alter table dbo.MachinePlain add ram_out float not null, constraint machine_plain_ck_ram_out check ( ram_out between 0 and power(cast(2 as bigint), 53) - 1 and ram_out between ram_minimum and ram_maximum and ram_out <= ram_total ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'ram_minimum') is null alter table dbo.MachinePlain add ram_minimum float not null, constraint machine_plain_ck_ram_minimum check ( ram_minimum between 0 and power(cast(2 as bigint), 53) - 1 and ram_minimum <= ram_total and ram_minimum <= ram_maximum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'ram_maximum') is null alter table dbo.MachinePlain add ram_maximum float not null, constraint machine_plain_ck_ram_maximum check ( ram_maximum between 0 and power(cast(2 as bigint), 53) - 1 and ram_maximum between ram_minimum and ram_total ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'ram_average') is null alter table dbo.MachinePlain add ram_average float not null, constraint machine_plain_ck_ram_average check ( ram_average between 0 and power(cast(2 as bigint), 53) - 1 and ram_average between ram_minimum and ram_maximum and ram_average <= ram_total ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'cpu_in') is null alter table dbo.MachinePlain add cpu_in float not null, constraint machine_plain_ck_cpu_in check ( cpu_in between 0 and 100 and cpu_in between cpu_minimum and cpu_maximum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'cpu_out') is null alter table dbo.MachinePlain add cpu_out float not null, constraint machine_plain_ck_cpu_out check ( cpu_out between 0 and 100 and cpu_out between cpu_minimum and cpu_maximum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'cpu_minimum') is null alter table dbo.MachinePlain add cpu_minimum float not null, constraint machine_plain_ck_cpu_minimum check ( cpu_minimum between 0 and 100 and cpu_minimum <= cpu_maximum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'cpu_maximum') is null alter table dbo.MachinePlain add cpu_maximum float not null, constraint machine_plain_ck_cpu_maximum check ( cpu_maximum between 0 and 100 and cpu_maximum >= cpu_minimum ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachinePlain' and column_name = 'cpu_average') is null alter table dbo.MachinePlain add cpu_average float not null, constraint machine_plain_ck_cpu_average check ( cpu_average between 0 and 100 and cpu_average between cpu_minimum and cpu_maximum ) -- Plain 1. if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesPlain' and column_name = 'plain') is null alter table dbo.MachineInterfacesPlain add plain integer not null, constraint machine_interfaces_plain_fk_plain foreign key (plain) references dbo.MachinePlain(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesPlain' and column_name = 'machine') is null alter table dbo.MachineInterfacesPlain add machine integer not null, constraint machine_interfaces_plain_fk_machine foreign key (machine) references dbo.Machines(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesPlain' and column_name = 'interface') is null alter table dbo.MachineInterfacesPlain add interface integer not null, constraint machine_interfaces_plain_fk_interface foreign key (interface) references dbo.Interfaces(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesPlain' and column_name = 'machine_interface') is null alter table dbo.MachineInterfacesPlain add machine_interface integer not null, constraint machine_interfaces_plain_fk_machine_interface foreign key (machine_interface) references dbo.MachineInterfaces(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesPlain' and column_name = 'mounted') is null alter table dbo.MachineInterfacesPlain add mounted bit not null constraint machine_interfaces_plain_df_mounted default 1 if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesPlain' and column_name = 'is_ipv6') is null alter table dbo.MachineInterfacesPlain add is_ipv6 bit not null constraint machine_interfaces_plain_df_is_ipv6 default 0 if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesPlain' and column_name = 'address') is null alter table dbo.MachineInterfacesPlain add [address] varchar(45) not null if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesPlain' and column_name = 'mask') is null alter table dbo.MachineInterfacesPlain add mask tinyint not null if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesPlain' and column_name = 'belongs') is null alter table dbo.MachineInterfacesPlain add belongs bit not null constraint machine_interfaces_plain_df_belongs default 1 if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksPlain' and column_name = 'plain') is null alter table dbo.MachineDisksPlain add plain integer not null, constraint machine_disks_plain_fk_plain foreign key (plain) references dbo.MachinePlain(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksPlain' and column_name = 'machine') is null alter table dbo.MachineDisksPlain add machine integer not null, constraint machine_disks_plain_fk_machine foreign key (machine) references dbo.Machines(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksPlain' and column_name = 'disk') is null alter table dbo.MachineDisksPlain add disk integer not null, constraint machine_disks_plain_fk_disk foreign key (disk) references dbo.Disks(id) on update no action on delete no action, constraint machine_disks_plain_uk_machine_disk unique nonclustered (machine asc, [disk] asc) with (fillfactor = 90) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksPlain' and column_name = 'candle_time') is null alter table dbo.MachineDisksPlain add candle_time integer not null, constraint machine_disks_plain_fk_candle_time foreign key (candle_time) references dbo.CandlesTimes(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksPlain' and column_name = 'name') is null alter table dbo.MachineDisksPlain add [name] varchar(32) not null, constraint machine_disks_plain_ck_name check (( len([name]) > 0 and [name] not like '%[^a-zA-Z0-9_-]%' ) or [name] like '[A-Z]:') if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksPlain' and column_name = 'size') is null alter table dbo.MachineDisksPlain add [size] bigint not null, constraint machine_disks_plain_ck_size check (size >= 0 and size < power(cast(2 as bigint), 53)) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksPlain' and column_name = 'mountpoint') is null alter table dbo.MachineDisksPlain add mountpoint varchar(128) not null, constraint machine_disks_plain_ck_mountpoint check ( mountpoint like '/%' or mountpoint like '[a-zA-Z]:\%' or mountpoint like '\\%' ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineDisksPlain' and column_name = 'free') is null alter table dbo.MachineDisksPlain add free bigint not null, constraint machine_disks_plain_ck_free check ( free between 0 and power(cast(2 as bigint), 53) - 1 and free <= [size] ) -- Plain 2. if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTrafficPlain' and column_name = 'plain') is null alter table dbo.MachineInterfacesTrafficPlain add plain integer not null, constraint machine_interfaces_traffic_plain_fk_plain foreign key (plain) references dbo.MachineInterfacesPlain(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTrafficPlain' and column_name = 'candle_time') is null alter table dbo.MachineInterfacesTrafficPlain add candle_time integer not null, constraint machine_interfaces_traffic_plain_fk_candle_time foreign key (candle_time) references dbo.CandlesTimes(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTrafficPlain' and column_name = 'type') is null alter table dbo.MachineInterfacesTrafficPlain add [type] integer not null, constraint machine_interfaces_traffic_plain_fk_type foreign key ([type]) references dbo.CandlesTimes(id) on update no action on delete no action if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTrafficPlain' and column_name = 'bytes') is null alter table dbo.MachineInterfacesTrafficPlain add bytes bigint not null, constraint machine_interfaces_traffic_plain_ck_bytes check ( bytes between 0 and power(cast(2 as bigint), 53) - 1 ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTrafficPlain' and column_name = 'packets') is null alter table dbo.MachineInterfacesTrafficPlain add packets bigint not null, constraint machine_interfaces_traffic_plain_ck_packets check ( packets between 0 and power(2, 30) - 1 ) if (select top 1 0 from information_schema.columns where table_catalog = @database and table_name = 'MachineInterfacesTrafficPlain' and column_name = 'errors') is null alter table dbo.MachineInterfacesTrafficPlain add errors bigint not null, constraint machine_interfaces_traffic_plain_ck_errors check ( errors between 0 and power(2, 30) - 1 ) end go execute dbo.tables_update