
/******************************
*         Drop Tables by inverse order of creation:   z=import, y=no-dependency, m=foreign-keys, a=no foreign key
        
            DROP TABLE 
            configuration ,eventlog ,data_errors ,domain_values
            DROP TABLE 
            builds
        
******************************
*         Summarize min and max values for each column of each table
    
            SELECT 'builds', COUNT(*) as Count, MIN( codeline ) as min_codeline ,  MAX( codeline ) as max_codeline, MIN( build ) as min_build ,  MAX( build ) as max_build, MIN( build_date ) as min_build_date ,  MAX( build_date ) as max_build_date FROM builds
            SELECT 'configuration', COUNT(*) as Count, MIN( section ) as min_section ,  MAX( section ) as max_section, MIN( keyword ) as min_keyword ,  MAX( keyword ) as max_keyword, MIN( value ) as min_value ,  MAX( value ) as max_value FROM configuration
            SELECT 'data_errors', COUNT(*) as Count, MIN( ID ) as min_ID ,  MAX( ID ) as max_ID, MIN( build ) as min_build ,  MAX( build ) as max_build, MIN( field_name ) as min_field_name ,  MAX( field_name ) as max_field_name, MIN( table_name ) as min_table_name ,  MAX( table_name ) as max_table_name, MIN( reason ) as min_reason ,  MAX( reason ) as max_reason, MIN( row_num ) as min_row_num ,  MAX( row_num ) as max_row_num FROM data_errors
            SELECT 'domain_values', COUNT(*) as Count, MIN( tbl ) as min_tbl ,  MAX( tbl ) as max_tbl, MIN( col ) as min_col ,  MAX( col ) as max_col, MIN( val ) as min_val ,  MAX( val ) as max_val, MIN( cnt ) as min_cnt ,  MAX( cnt ) as max_cnt FROM domain_values
            SELECT 'eventlog', COUNT(*) as Count, MIN( logtime ) as min_logtime ,  MAX( logtime ) as max_logtime, MIN( event_type ) as min_event_type ,  MAX( event_type ) as max_event_type, MIN( event ) as min_event ,  MAX( event ) as max_event, MIN( value ) as min_value ,  MAX( value ) as max_value, MIN( message ) as min_message ,  MAX( message ) as max_message FROM eventlog

******************************
*        Count each ID values [varchar(100)]

        
        DROP TABLE domain_values
        CREATE TABLE  domain_values 
        (
            tbl varchar(100),
            col varchar(100),
            val varchar(100),
            cnt int,
        )
        
        insert into domain_values (tbl, col, val, cnt) select  'builds', 'codeline', codeline, count(*) from builds group by codeline
insert into domain_values (tbl, col, val, cnt) select  'builds', 'build', build, count(*) from builds group by build
insert into domain_values (tbl, col, val, cnt) select  'configuration', 'section', section, count(*) from configuration group by section
insert into domain_values (tbl, col, val, cnt) select  'configuration', 'keyword', keyword, count(*) from configuration group by keyword
insert into domain_values (tbl, col, val, cnt) select  'data_errors', 'build', build, count(*) from data_errors group by build
insert into domain_values (tbl, col, val, cnt) select  'data_errors', 'field_name', field_name, count(*) from data_errors group by field_name
insert into domain_values (tbl, col, val, cnt) select  'data_errors', 'table_name', table_name, count(*) from data_errors group by table_name

        SELECT col, val, sum(cnt) as count, min(tbl) as min_table, max(tbl) as max_table from domain_values where col > '' group by col, val order by col, val
        
*******************************/

                IF OBJECT_ID ( 'builds', 'U') IS NULL
                BEGIN
                CREATE TABLE  builds
                (
                    codeline varchar(100)  NOT NULL ,
                    build varchar(100)  NOT NULL ,
                    build_date smalldatetime  NOT NULL
                )
               CREATE UNIQUE CLUSTERED INDEX primary_index ON builds (codeline,build)  WITH (DROP_EXISTING=OFF)
               END

                IF OBJECT_ID ( 'configuration', 'U') IS NULL
                BEGIN
                CREATE TABLE  configuration
                (
                    section varchar(100)  NOT NULL ,
                    keyword varchar(100)  NOT NULL ,
                    value varchar(1000)  NOT NULL
                )
               CREATE UNIQUE INDEX section_index ON configuration (section,keyword)  WITH (DROP_EXISTING=OFF)
               END

                IF OBJECT_ID ( 'data_errors', 'U') IS NULL
                BEGIN
                CREATE TABLE  data_errors
                (
                    ID varchar(1000)  NOT NULL ,
                    build varchar(100)  NOT NULL ,
                    field_name varchar(100)  NOT NULL ,
                    table_name varchar(100)  NOT NULL ,
                    reason varchar(1000)  NOT NULL ,
                    row_num int  NOT NULL DEFAULT 0
                )
               END

                IF OBJECT_ID ( 'domain_values', 'U') IS NULL
                BEGIN
                CREATE TABLE  domain_values
                (
                    tbl varchar(1000)  NOT NULL ,
                    col varchar(1000)  NOT NULL ,
                    val varchar(1000)  NOT NULL ,
                    cnt int  NOT NULL DEFAULT 0
                )
               END

                IF OBJECT_ID ( 'eventlog', 'U') IS NULL
                BEGIN
                CREATE TABLE  eventlog
                (
                    logtime datetime  NOT NULL DEFAULT getdate() ,
                    event_type ERROR Undefined domain for event_type error() requires a QName literal!  NOT NULL  CONSTRAINT ev_event_type_ref REFERENCES ERROR Undefined domain for event_type error() requires a QName literal! (ERROR Undefined domain for event_type error() requires a QName literal!) ,
                    event ERROR Undefined domain for event error() requires a QName literal!  NOT NULL  CONSTRAINT ev_event_ref REFERENCES ERROR Undefined domain for event error() requires a QName literal! (ERROR Undefined domain for event error() requires a QName literal!) ,
                    value int ,
                    message varchar(max)
                )
               CREATE INDEX logtime_index ON eventlog (logtime,event_type)  WITH (DROP_EXISTING=OFF)
               END
