text:
CREATE TABLE #Codesets (
  codeset_id int NOT NULL,
  concept_id bigint NOT NULL
)
;

INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 0 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (3015232,3027114,3019900,4198448,37393449,37397989,44809580,4008265)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (3015232,3027114,3019900,4198448,37393449,37397989,44809580,4008265)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 1 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4011133,4101713,37392938,37392562)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (4011133,4101713,37392938,37392562)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 2 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (3004249,4152194,21492239,3028737)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (3004249,4152194,21492239,3028737)
  and c.invalid_reason is null

) I
LEFT JOIN
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4153323,4217013)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (4153323,4217013)
  and c.invalid_reason is null

) E ON I.concept_id = E.concept_id
WHERE E.concept_id is null
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 3 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (1545958,1592180,1549686,1592085,40165636,1551860,1510813,1539403)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (1545958,1592180,1549686,1592085,40165636,1551860,1510813,1539403)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 5 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (372924,375557,376713,443454,441874,439847,43530727,4148906,432923)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (443454,439847)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 6 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (316139)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (316139)
  and c.invalid_reason is null

) I
LEFT JOIN
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (315295)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (315295)
  and c.invalid_reason is null

) E ON I.concept_id = E.concept_id
WHERE E.concept_id is null
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 7 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4068155,313217,314665,4194288,4323077)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (313217,314665,4194288,4323077)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 8 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (37109912,438172,4124687,4108678,4163874,45766212,4329847,37109911,4108680,37109910)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (37109912,438172,4124687,4108678,4163874,45766212,4329847,37109911,4108680,37109910)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 9 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (321318)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (321318)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 10 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (442793,201820,443238)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (442793,201820,443238)
  and c.invalid_reason is null

) I
LEFT JOIN
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4058243,435216,201254,40484648)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (4058243,435216,201254,40484648)
  and c.invalid_reason is null

) E ON I.concept_id = E.concept_id
WHERE E.concept_id is null
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 11 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (21600744)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (21600744)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 12 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (40769338,43021173,42539022,46270562)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (40769338,43021173,42539022,46270562)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 13 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (40758583,44786901,4197971,3034639,3004410,3003309,3005673,40762352,3007263,2212392)

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 14 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (2001516,4284104,4229433,4146972,4228305,4228304,4063237,4148030,4008625,4106548,4031996,4234990,45889469,4240486,4336464,4337056,4000733,4336467,4336465,4339629,4337737,4336466,4233421,4305509,4309432,4011931,4253805,45887879,2107242,2107243,2107244,2107231,45889898,2107223,2107224,2107226,2107227,2107228,2107222,45887862,2107217,2107218,2107219,2107220,2107221,2107216,2001515,4000732,4305852,4020216,2001514,4233420,45889467)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (2001516,4284104,4229433,4146972,4228305,4228304,4063237,4148030,4008625,4106548,4031996,4234990,45889469,4240486,4336464,4337056,4000733,4336467,4336465,4339629,4337737,4336466,4233421,4305509,4309432,4011931,4253805,45887879,2107242,2107243,2107244,2107231,45889898,2107223,2107224,2107226,2107227,2107228,2107222,45887862,2107217,2107218,2107219,2107220,2107221,2107216,2001515,4000732,2001514,4233420,45889467)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 15 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4171077,4328103,4216356,4238755,4214516,44789455,2001506,2001505,44511272,44511133,4337738,43531439,43531438,4264286,43533247,43533352,44512256,44784573,44511268,44511269,44511271,45770795,44511131,4020653,44511130,4181025,4006788,2000064,4264285,4265293,4225903,43533353,43533248,43531440,4175997,44511532,4139198,4178148,4329263,4283892,44511273)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (4337738,44512256,44784573,44511268,44511269,44511271,45770795,44511131,4020653,44511130,4181025,4006788,2000064,4264285,4265293,4225903,43533353,43533248,43531440,4175997,44511532,4139198,4178148,4329263,4283892,44511273)
  and c.invalid_reason is null

) I
) C;
INSERT INTO #Codesets (codeset_id, concept_id)
SELECT 16 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (44784623,38001109,38001108,38001110,38001112,38001111,38001113,38001139,40761421,443563,438172,4108215,4108678,4163874,4185932,40761359,319038,4108680)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (44784623,38001109,38001108,38001110,38001112,38001111,38001113,38001139,40761421,443563,438172,4108215,4108678,4163874,4185932,40761359,319038,4108680)
  and c.invalid_reason is null

) I
LEFT JOIN
(
  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (315830)
UNION  select c.concept_id
  from @vocabulary_database_schema.CONCEPT c
  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (315830)
  and c.invalid_reason is null

) E ON I.concept_id = E.concept_id
WHERE E.concept_id is null
) C;

with primary_events (event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id) as
(
-- Begin Primary Events
select P.ordinal as event_id, P.person_id, P.start_date, P.end_date, op_start_date, op_end_date, cast(P.visit_occurrence_id as bigint) as visit_occurrence_id
FROM
(
  select E.person_id, E.start_date, E.end_date,
         row_number() OVER (PARTITION BY E.person_id ORDER BY E.sort_date ASC) ordinal,
         OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date, cast(E.visit_occurrence_id as bigint) as visit_occurrence_id
  FROM
  (
  -- Begin Measurement Criteria
select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
       C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
       C.measurement_date as sort_date
from
(
  select m.*
  FROM @cdm_database_schema.MEASUREMENT m
JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 0))
) C

WHERE (C.value_as_number >= 130.0000 and C.value_as_number <= 320.0000)
AND C.unit_concept_id in (8840,8954,9028)
-- End Measurement Criteria

UNION ALL
-- Begin Measurement Criteria
select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
       C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
       C.measurement_date as sort_date
from
(
  select m.*
  FROM @cdm_database_schema.MEASUREMENT m
JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 0))
) C

WHERE (C.value_as_number >= 3.3700 and C.value_as_number <= 8.2900)
AND C.unit_concept_id in (8753)
-- End Measurement Criteria

UNION ALL
-- Begin Measurement Criteria
select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
       C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
       C.measurement_date as sort_date
from
(
  select m.*
  FROM @cdm_database_schema.MEASUREMENT m
JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 1))
) C

WHERE (C.value_as_number >= 20.0000 and C.value_as_number <= 100.0000)
AND C.unit_concept_id in (8840,8954,9028)
-- End Measurement Criteria

UNION ALL
-- Begin Measurement Criteria
select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
       C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
       C.measurement_date as sort_date
from
(
  select m.*
  FROM @cdm_database_schema.MEASUREMENT m
JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 1))
) C

WHERE (C.value_as_number >= 0.5200 and C.value_as_number <= 2.5900)
AND C.unit_concept_id in (8753)
-- End Measurement Criteria

UNION ALL
-- Begin Measurement Criteria
select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
       C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
       C.measurement_date as sort_date
from
(
  select m.*
  FROM @cdm_database_schema.MEASUREMENT m
JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 2))
) C

WHERE (C.value_as_number >= 90.0000 and C.value_as_number <= 200.0000)
-- End Measurement Criteria

  ) E
	JOIN @cdm_database_schema.observation_period OP on E.person_id = OP.person_id and E.start_date >=  OP.observation_period_start_date and E.start_date <= op.observation_period_end_date
  WHERE DATEADD(day,365,OP.OBSERVATION_PERIOD_START_DATE) <= E.START_DATE AND DATEADD(day,0,E.START_DATE) <= OP.OBSERVATION_PERIOD_END_DATE
) P

-- End Primary Events

)
SELECT event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id
INTO #qualified_events
FROM
(
  select pe.event_id, pe.person_id, pe.start_date, pe.end_date, pe.op_start_date, pe.op_end_date, row_number() over (partition by pe.person_id order by pe.start_date ASC) as ordinal, cast(pe.visit_occurrence_id as bigint) as visit_occurrence_id
  FROM primary_events pe

) QE

;

--- Inclusion Rule Inserts

select 0 as inclusion_rule_id, person_id, event_id
INTO #Inclusion_0
FROM
(
  select pe.person_id, pe.event_id
  FROM #qualified_events pe

JOIN (
-- Begin Criteria Group
select 0 as index_id, person_id, event_id
FROM
(
  select E.person_id, E.event_id
  FROM #qualified_events E
  INNER JOIN
  (
    -- Begin Demographic Criteria
SELECT 0 as index_id, e.person_id, e.event_id
FROM #qualified_events E
JOIN @cdm_database_schema.PERSON P ON P.PERSON_ID = E.PERSON_ID
WHERE P.race_concept_id in (38003598,38003599,38003600,38003601,38003602,38003603,38003604,38003605,38003606,38003607,38003608,38003609,8516) AND P.race_concept_id in (38003598,38003599,38003600,38003601,38003602,38003603,38003604,38003605,38003606,38003607,38003608,38003609,8516)
GROUP BY e.person_id, e.event_id
-- End Demographic Criteria

  ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
  GROUP BY E.person_id, E.event_id
  HAVING COUNT(index_id) = 1
) G
-- End Criteria Group
) AC on AC.person_id = pe.person_id AND AC.event_id = pe.event_id
) Results
;

select 1 as inclusion_rule_id, person_id, event_id
INTO #Inclusion_1
FROM
(
  select pe.person_id, pe.event_id
  FROM #qualified_events pe

JOIN (
-- Begin Criteria Group
select 0 as index_id, person_id, event_id
FROM
(
  select E.person_id, E.event_id
  FROM #qualified_events E
  INNER JOIN
  (
    -- Begin Demographic Criteria
SELECT 0 as index_id, e.person_id, e.event_id
FROM #qualified_events E
JOIN @cdm_database_schema.PERSON P ON P.PERSON_ID = E.PERSON_ID
WHERE P.gender_concept_id in (8507)
GROUP BY e.person_id, e.event_id
-- End Demographic Criteria

  ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
  GROUP BY E.person_id, E.event_id
  HAVING COUNT(index_id) = 1
) G
-- End Criteria Group
) AC on AC.person_id = pe.person_id AND AC.event_id = pe.event_id
) Results
;

select 2 as inclusion_rule_id, person_id, event_id
INTO #Inclusion_2
FROM
(
  select pe.person_id, pe.event_id
  FROM #qualified_events pe

JOIN (
-- Begin Criteria Group
select 0 as index_id, person_id, event_id
FROM
(
  select E.person_id, E.event_id
  FROM #qualified_events E
  INNER JOIN
  (
    -- Begin Correlated Criteria
SELECT 0 as index_id, p.person_id, p.event_id
FROM #qualified_events P
INNER JOIN
(
  -- Begin Measurement Criteria
select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
       C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
       C.measurement_date as sort_date
from
(
  select m.*
  FROM @cdm_database_schema.MEASUREMENT m
JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 0))
) C

WHERE (C.value_as_number >= 130.0000 and C.value_as_number <= 320.0000)
AND C.unit_concept_id in (8840,8954,9028)
-- End Measurement Criteria

) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-1825,P.START_DATE) AND A.START_DATE <= DATEADD(day,0,P.START_DATE)
GROUP BY p.person_id, p.event_id
HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1
-- End Correlated Criteria

UNION ALL
-- Begin Correlated Criteria
SELECT 1 as index_id, p.person_id, p.event_id
FROM #qualified_events P
INNER JOIN
(
  -- Begin Measurement Criteria
select C.person_id, C.measurement_id as event_id, C.measurement_date as start_date, DATEADD(d,1,C.measurement_date) as END_DATE,
       C.measurement_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
       C.measurement_date as sort_date
from
(
  select m.*
  FROM @cdm_database_schema.MEASUREMENT m
JOIN #Codesets codesets on ((m.measurement_concept_id = codesets.concept_id and codesets.codeset_id = 0))
) C

WHERE (C.value_as_number >= 3.3700 and C.value_as_number <= 8.2900)
AND C.unit_concept_id in (8753)
-- End Measurement Criteria

) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= DATEADD(day,-1825,P.START_DATE) AND A.START_DATE <= DATEADD(day,0,P.START_DATE)
GROUP BY p.person_id, p.event_id
HAVING COUNT(A.TARGET_CONCEPT_ID) >= 1

domain: sql


text:
DROP DATABASE IF EXISTS employeetracker_db;
CREATE DATABASE employeetracker_db;

USE employeetracker_db;

CREATE TABLE department (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE role (
    id INT NOT NULL PRIMARY KEY,
    title VARCHAR(30) NOT NULL,
    salary DECIMAL NOT NULL,
    department_id INT UNSIGNED NOT NULL,
  INDEX dep_ind (department_id),
  CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE CASCADE
);

CREATE TABLE employee (
    id INT NOT NULL PRIMARY KEY,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    role_id INT NOT NULL,
    manager_id INT,
    FOREIGN KEY (role_id)
    REFERENCES role(id)
    ON DELETE SET NULL,
    FOREIGN KEY (manager_id)
    REFERENCES employee(id)
    ON DELETE SET NULL
);


text:
USE BUDT758_DB_0504_10

CREATE TABLE [TerpEats.Customer] (
	customerId CHAR (10) NOT NULL UNIQUE,
	customerLastName VARCHAR (20),
	customerFirstName VARCHAR (20),
	customerEmail VARCHAR (30), -- Can make this unique, not sure if that messed with 3rd NF
	customerZipCode CHAR (9),
	customerDateOfBirth DATE,
	CONSTRAINT pk_Customer_customerId PRIMARY KEY (customerId) );

CREATE TABLE [TerpEats.Menu] (
	menuId CHAR(10) NOT NULL UNIQUE,
	menuURL VARCHAR(100),
	CONSTRAINT pk_Food_menuId PRIMARY KEY (menuId) );

CREATE TABLE [TerpEats.Location] (
	locationID CHAR (10) NOT NULL UNIQUE,
	locationName VARCHAR (30),
	locationLongitude FLOAT,
	locationLatitude FLOAT,
	CONSTRAINT pk_Location_locationID PRIMARY KEY (locationID) );

CREATE TABLE [TerpEats.Restaurant] (
	restaurantId CHAR (10) NOT NULL UNIQUE,
	restaurantName VARCHAR (50),
	restaurantStreet VARCHAR (50),
	restaurantCity VARCHAR (50),
	restaurantState CHAR (2),
	restaurantZipCode CHAR (9),
	restaurantPhoneNo CHAR (10),
	locationId CHAR (10),
	menuId CHAR(10),
	CONSTRAINT pk_Restaurant_restaurantId PRIMARY KEY (restaurantId),
	CONSTRAINT fk_Restaurant_locationId FOREIGN KEY (locationId)
		REFERENCES [TerpEats.Location] (locationId)
		ON DELETE NO ACTION ON UPDATE CASCADE,
	CONSTRAINT fk_Restaurant_menuId FOREIGN KEY (menuId)
		REFERENCES [TerpEats.Menu] (menuId)
		ON DELETE NO ACTION ON UPDATE CASCADE );

CREATE TABLE [TerpEats.Employee] (
	employeeId CHAR (12) NOT NULL UNIQUE,
	employeeLastName VARCHAR (20),
	employeeFirstName VARCHAR (20),
	employeePosition VARCHAR (20),
	employeeSalary NUMERIC,
	restaurantId CHAR (10),
	CONSTRAINT pk_Employee_employeeId PRIMARY KEY (employeeId),
	CONSTRAINT fk_Employee_restaurantId FOREIGN KEY (restaurantId)
		REFERENCES [TerpEats.Restaurant] (restaurantId)
		ON DELETE CASCADE ON UPDATE CASCADE );

CREATE TABLE [TerpEats.Dine] (
	restaurantId CHAR (10) NOT NULL,
	customerId CHAR (10) NOT NULL,
	CONSTRAINT pk_Dine_restaurantId_customerId PRIMARY KEY (restaurantId, customerId),
	CONSTRAINT fk_Dine_restaurantId FOREIGN KEY (restaurantId)
		REFERENCES [TerpEats.Restaurant] (restaurantId)
		ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT fk_Dine_customerId FOREIGN KEY (customerID)
		REFERENCES [TerpEats.Customer] (customerId)
		ON DELETE NO ACTION ON UPDATE CASCADE );

CREATE TABLE [TerpEats.Review] (
	reviewId CHAR (10) NOT NULL UNIQUE,
	reviewRating DECIMAL(2,1),
	reviewText NVARCHAR (MAX),
	CONSTRAINT pk_Review_reviewId PRIMARY KEY (reviewId) );

CREATE TABLE [TerpEats.Write] (
	restaurantId CHAR (10) NOT NULL,
	reviewId CHAR (10) NOT NULL UNIQUE,
	customerId CHAR (10) NOT NULL,
	CONSTRAINT pk_Write_reviewId PRIMARY KEY (reviewId),
	CONSTRAINT fk_Write_restaurantId FOREIGN KEY (restaurantId)
	REFERENCES [TerpEats.Restaurant] (restaurantId)
		ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT fk_Write_reviewId FOREIGN KEY (reviewId)
		REFERENCES [TerpEats.Review] (reviewId)
		ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT fk_Write_customerId FOREIGN KEY (customerId)
	REFERENCES [TerpEats.Customer] (customerId)
		ON DELETE NO ACTION ON UPDATE CASCADE );


text:
/*
 * treat.sql
 * Chapter 15, Oracle10g PL/SQL Programming
 * by Ron Hardman, Mike McLaughlin, Scott Urman
 *
 * This script demonstrates the TREAT function.
 */

SET SERVEROUTPUT ON SIZE 1000000

exec clean_schema.synonyms
exec clean_schema.tables
exec clean_schema.objects

CREATE OR REPLACE TYPE discount_price_obj AS OBJECT (
   discount_rate   NUMBER (10, 4),
   price           NUMBER (10, 2),
   MEMBER FUNCTION discount_price
      RETURN NUMBER
)
INSTANTIABLE FINAL;
/

CREATE OR REPLACE TYPE BODY discount_price_obj
AS
   MEMBER FUNCTION discount_price
      RETURN NUMBER
   IS
   BEGIN
      RETURN (SELF.price * (1 - SELF.discount_rate));
   END discount_price;
END;
/

CREATE OR REPLACE TYPE inventory_obj AS OBJECT (
   item_id          NUMBER (10),
   num_in_stock     NUMBER (10),
   reorder_status   VARCHAR2 (20 CHAR),
   price            NUMBER(10,2),
   CONSTRUCTOR FUNCTION inventory_obj (
      item_id        IN   NUMBER,
      num_in_stock   IN   NUMBER,
      price          IN   NUMBER
   )
      RETURN SELF AS RESULT,
   MEMBER PROCEDURE print_inventory,
   MEMBER PROCEDURE print_status,
   MEMBER PROCEDURE print_price
)
INSTANTIABLE NOT FINAL;
/

CREATE OR REPLACE TYPE BODY inventory_obj
AS
   CONSTRUCTOR FUNCTION inventory_obj (
      item_id        IN   NUMBER,
      num_in_stock   IN   NUMBER,
      price          IN   NUMBER
   )
      RETURN SELF AS RESULT
   IS
   BEGIN
      SELF.item_id := item_id;
      SELF.num_in_stock := num_in_stock;
      SELF.price := price;
      RETURN;
   END;
   MEMBER PROCEDURE print_inventory
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('INVENTORY FOR BOOKSTORE1');
      DBMS_OUTPUT.put_line ('========================');
      DBMS_OUTPUT.put_line (   'Item number '
                            || SELF.item_id
                            || ' has '
                            || SELF.num_in_stock
                            || ' in stock'
                           );
   END print_inventory;
   MEMBER PROCEDURE print_status
   IS
      v_status   VARCHAR2 (20);
   BEGIN
      IF SELF.num_in_stock > 0
      THEN
         v_status := 'IN STOCK';
      ELSE
         v_status := 'OUT OF STOCK';
      END IF;

      DBMS_OUTPUT.put_line ('INVENTORY STATUS FOR BOOKSTORE1');
      DBMS_OUTPUT.put_line ('===============================');
      DBMS_OUTPUT.put_line ('Item number ' || SELF.item_id || ' is '
                            || v_status
                           );
   END print_status;
   MEMBER PROCEDURE print_price
   IS
      v_discount_price   discount_price_obj
                                       := discount_price_obj (.1, SELF.price);
   BEGIN
      DBMS_OUTPUT.put_line ('BOOKSTORE1 PRICES');
      DBMS_OUTPUT.put_line ('=================');
      DBMS_OUTPUT.put_line ('Item number ' || SELF.item_id);
      DBMS_OUTPUT.put_line ('Retail cost: ' || SELF.price || ' US dollars');
      DBMS_OUTPUT.put_line (   'OUR LOW - LOW - LOW DISCOUNT PRICE: '
                            || v_discount_price.discount_price
                            || ' US dollars'
                           );
   END print_price;
END;
/

-- address_obj is at one end of the chain having no dependencies.

CREATE OR REPLACE TYPE address_obj AS OBJECT (
   address1   VARCHAR2 (30 CHAR),
   address2   VARCHAR2 (30 CHAR),
   city       VARCHAR2 (30 CHAR),
   state      CHAR (2 CHAR)
)
INSTANTIABLE FINAL;
/

-- person_obj is also at the end of the chain

CREATE OR REPLACE TYPE person_obj AS OBJECT (
   first_name   VARCHAR2 (20),
   last_name    VARCHAR2 (20)
)
INSTANTIABLE FINAL;
/

-- contact_obj links to the first two object types, and adds
--   one more attribute

CREATE OR REPLACE TYPE contact_obj AS OBJECT (
   NAME      person_obj,
   address   address_obj,
   phone     NUMBER (10)
)
INSTANTIABLE FINAL;
/

-- Finally, publisher_obj.contact_info is created to use contact_obj
--   for its datatype.

CREATE OR REPLACE TYPE publisher_obj AS OBJECT (
   pub_name       VARCHAR2 (30),
   contact_info   contact_obj,
   MEMBER PROCEDURE show_contact
)
INSTANTIABLE FINAL;
/

CREATE OR REPLACE TYPE book_obj
UNDER inventory_obj (
   isbn        CHAR (10 CHAR),
   CATEGORY    VARCHAR2 (20 CHAR),
   title       VARCHAR2 (100 CHAR),
   num_pages   NUMBER,
   publisher   publisher_obj,
   CONSTRUCTOR FUNCTION book_obj (
      item_id        NUMBER,
      num_in_stock   NUMBER,
      price          NUMBER,
      isbn           CHAR,
      title          VARCHAR2,
      num_pages      NUMBER
   )
      RETURN SELF AS RESULT,
   MEMBER PROCEDURE print_book_information,
   OVERRIDING MEMBER PROCEDURE print_price
)
INSTANTIABLE NOT FINAL;
/

CREATE OR REPLACE TYPE music_person_obj AS OBJECT (
   first_name   VARCHAR2 (50 CHAR),
   last_name    VARCHAR2 (50 CHAR)
)
FINAL INSTANTIABLE;
/

CREATE OR REPLACE TYPE music_obj
UNDER inventory_obj (
   style      VARCHAR2 (50 CHAR),
   composer   music_person_obj,
   artist     music_person_obj
)
NOT FINAL INSTANTIABLE;
/

CREATE TABLE inventory_tbl OF inventory_obj;

INSERT INTO inventory_tbl
     VALUES (music_obj (1,
                        10,
                        'IN STOCK',
                        11.99,
                        'Hip-Hop',
                        music_person_obj ('George', 'Instructor'),
                        music_person_obj ('George', 'Instructor')
                       ));

INSERT INTO inventory_tbl
     VALUES (book_obj (2,
                       13,
                       'IN STOCK',
                       54.95,
                       '72121203',
                       'TECHNICAL',
                       'Oracle DBA 101',
                       563,
                       publisher_obj ('Oracle Press',
                                      contact_obj (person_obj ('Susan',
                                                               'Publisher'
                                                              ),
                                                   address_obj ('123 Street',
                                                                'Suite 2',
                                                                'My City',
                                                                'CO'
                                                               ),
                                                   '5555555555'
                                                  )
                                     )
                      ));


text:
CREATE OR REPLACE FUNCTION test_organization_user_roles_table()
RETURNS SETOF TEXT
LANGUAGE plpgsql
AS $$
BEGIN

  RETURN QUERY SELECT has_table('organization_user_roles');

-- Columns
  RETURN QUERY SELECT columns_are('organization_user_roles', ARRAY['organization_id',
                                                                 'user_id',
                                                                 'role'
                                                                 ]);

  RETURN QUERY SELECT col_is_pk('organization_user_roles', ARRAY['organization_id','user_id','role']);

  RETURN QUERY SELECT col_not_null('organization_user_roles', 'organization_id');
  RETURN QUERY SELECT col_not_null('organization_user_roles', 'user_id');
  RETURN QUERY SELECT col_not_null('organization_user_roles', 'role');

END;
$$;


text:
SELECT
    SourceDB
    , [Status]
    , Beta
    , SSISInstanceID AS SSISIID
    , LoadStageDBStartDate
    , LoadStageDBEndDate
    , CONVERT(VARCHAR(12), DATEADD(ms,
                                   DATEDIFF(ms, LoadStageDBStartDate, LoadStageDBEndDate), 0),
              114) AS StageLoadTime
    , LoadReportDBStartDate
    , LoadReportDBEndDate
    , CONVERT(VARCHAR(12), DATEADD(ms,
                                   DATEDIFF(ms, LoadReportDBStartDate,
                                            LoadReportDBEndDate), 0),
              114) AS ReportLoadTime
FROM ClientConnection
GROUP BY Beta
    , Status
    , SSISInstanceID
    , SourceDB
    , LoadStageDBStartDate
    , LoadStageDBEndDate
    , LoadReportDBStartDate
    , LoadReportDBEndDate
ORDER BY Status
    ASC
, SSISInstanceID
ASC
, Beta
ASC
, LoadStageDBStartDate
ASC

-------------------------------------------

SELECT
    ClientID
    , Status
    , Beta
    , SourceServer
    , SourceDB
    , CDCReportServer
    , CDCReportDB
    , LSNPrevious
    , LSNMax
    , LSNFromPrevious
    , LSNFrom
    , StartTimeExtract
    , EndTimeExtract
    , LoanCurrentStartDate
    , LoanCurrentEndDate
    , LoanMasterStartDate
    , LoanMasterEndDate
    , LoanSecondaryStartDate
    , LoanSecondaryEndDate
FROM ClientConnection_test
ORDER BY Beta, SourceDB

UPDATE ClientConnection_test
SET Status = 0

UPDATE ClientConnection_test
SET Beta = '0'
--WHERE Beta = '1'
WHERE SourceDB IN
      ('PADemoDU', 'RLC', 'EliLillyFCU', 'GTE32')

SELECT
    ClientID
    , Status
    , Beta
    , SourceServer
    , SourceDB
    , CDCReportServer
    , CDCReportDB
    , LSNPrevious
    , LSNMax
    , LSNFromPrevious
    , LSNFrom
    , StartTimeExtract
    , EndTimeExtract
    , LoanCurrentStartDate
    , LoanCurrentEndDate
    , LoanMasterStartDate
    , LoanMasterEndDate
    , LoanSecondaryStartDate
    , LoanSecondaryEndDate
FROM ClientConnection_test
ORDER BY Beta, SourceDB

UPDATE ClientConnection
SET Beta = '1', SSISInstanceID = 0
WHERE SourceDB = 'PADemoDU'
-------------------------------------------
-- Sum of clients in each status by Beta number

SELECT
    CASE WHEN SSISInstanceID IS NULL
        THEN 'Total'
    ELSE SSISInstanceID END SSISInstanceID
    , SUM(OldStatus4) AS OldStatus4
    , SUM(Status0) AS Status0
    , SUM(Status1) AS Status1
    , SUM(Status2) AS Status2
    , SUM(Status3) AS Status3
    , SUM(Status4) AS Status4
    , SUM(OldStatus4 + Status0 + Status1 + Status2 + Status3 + Status4) AS InstanceTotal
FROM
    (
        SELECT
            CONVERT(VARCHAR, SSISInstanceID) AS SSISInstanceID
            , COUNT(CASE WHEN Status = 4 AND
                              CONVERT(DATE, LoadReportDBEndDate) < CONVERT(DATE, GETDATE())
            THEN Status
                    ELSE NULL END) AS OldStatus4
            , COUNT(CASE WHEN Status = 0
            THEN Status
                    ELSE NULL END) AS Status0
            , COUNT(CASE WHEN Status = 1
            THEN Status
                    ELSE NULL END) AS Status1
            , COUNT(CASE WHEN Status = 2
            THEN Status
                    ELSE NULL END) AS Status2
            , COUNT(CASE WHEN Status = 3
            THEN Status
                    ELSE NULL END) AS Status3
--, COUNT ( CASE WHEN Status = 4 THEN Status ELSE NULL END ) AS Status4
            , COUNT(CASE WHEN Status = 4 AND
                              DATEPART(DAY, LoadReportDBEndDate) = DATEPART(DAY, GETDATE())
            THEN Status
                    ELSE NULL END) AS Status4
        FROM dbo.ClientConnection
        GROUP BY SSISInstanceID
    ) AS StatusMatrix
GROUP BY SSISInstanceID
----------------------------------------------

SELECT
    CASE WHEN SSISInstanceID IS NULL
        THEN 'Total'
    ELSE SSISInstanceID END SSISInstanceID
    , SUM(OldStatus4) AS OldStatus4
    , SUM(Status0) AS Status0
    , SUM(Status1) AS Status1
    , SUM(Status2) AS Status2
    , SUM(Status3) AS Status3
    , SUM(Status4) AS Status4
    , SUM(OldStatus4 + Status0 + Status1 + Status2 + Status3 + Status4) AS InstanceTotal
FROM
    (
        SELECT
            CONVERT(VARCHAR, SSISInstanceID) AS SSISInstanceID
            , COUNT(CASE WHEN Status = 4 AND
                              CONVERT(DATE, LoadReportDBEndDate) < CONVERT(DATE, GETDATE())
            THEN Status
                    ELSE NULL END) AS OldStatus4
            , COUNT(CASE WHEN Status = 0
            THEN Status
                    ELSE NULL END) AS Status0
            , COUNT(CASE WHEN Status = 1
            THEN Status
                    ELSE NULL END) AS Status1
            , COUNT(CASE WHEN Status = 2
            THEN Status
                    ELSE NULL END) AS Status2
            , COUNT(CASE WHEN Status = 3
            THEN Status
                    ELSE NULL END) AS Status3
            , COUNT(CASE WHEN Status = 4 AND
                              DATEPART(DAY, LoadReportDBEndDate) = DATEPART(DAY, GETDATE())
            THEN Status
                    ELSE NULL END) AS Status4
        FROM dbo.ClientConnection
        GROUP BY SSISInstanceID
    ) AS StatusMatrix
GROUP BY SSISInstanceID

SELECT *
FROM DMartLogging
ORDER BY ErrorDateTime
    DESC
----------------------------------------------

SELECT *
FROM DMartLogging
WHERE DATEPART(day, ErrorDateTime) = DATEPART(day, GetDate())
      AND DATEPART(month, ErrorDateTime) = DATEPART(month, GetDate())
      AND DATEPART(year, ErrorDateTime) = DATEPART(year, GetDate())
ORDER BY ErrorDateTime
    DESC
----------------------------------------------

SELECT *
FROM dbo.DMartComponentLogging
ORDER BY ErrorDateTime
    ASC
----------------------------------------------

SELECT *
FROM dbo.DMartComponentLogging
WHERE DATEPART(day, ErrorDateTime) = DATEPART(day, GetDate())
      AND DATEPART(month, ErrorDateTime) = DATEPART(month, GetDate())
      AND DATEPART(year, ErrorDateTime) = DATEPART(year, GetDate())
      AND TaskName = 'Data Flow Task br_liability'
GROUP BY TaskName
    , ErrorDateTime
    , PackageName
    , DestDB
    , DestServer
    , SourceDB
    , SourceServer
    , ID
    , ClientId
    , ErrorMessage
ORDER BY ErrorDateTime
    ASC
----------------------------------------------

SELECT name
FROM sys.databases
WHERE Name LIKE '%Stage%'
----------------------------------------------

SELECT name
FROM sys.databases
WHERE Name LIKE '%Data%'
----------------------------------------------

SELECT *
FROM opsinfo_ops_dbo.clients
WHERE client_name LIKE '%Eli%'

----------------------------------------------------------------

SELECT *
FROM ClientConnection
WHERE Beta != '2'
ORDER BY 3 --DESC

----------------------------------------------------------------
----------------

SELECT *
FROM ClientConnection
WHERE ReportServer = 'PSQLRPT22'
ORDER BY Beta, 3
----------------

SELECT
    Client_id
    , SourceServer
    , SourceDB
    , Status
    , Beta
    , StageServer
    , StageDB
    , ReportServer
    , ReportDB
    , LoadStageDBStartDate
    , LoadStageDBEndDate
    , DATEDIFF(minute, LoadStageDBStartDate, LoadStageDBEndDate) AS StageLoadTime
    , LoadReportDBStartDate
    , LoadReportDBEndDate
    , DATEDIFF(minute, LoadReportDBStartDate,
               LoadReportDBEndDate) AS ReportLoadTime
FROM ClientConnection
ORDER BY Beta, 3
----------------

UPDATE ClientConnection
SET LoadStageDBStartDate = @Now, LoadStageDBEndDate = @Now, Status = 2
WHERE Beta = '1'

UPDATE ClientConnection
SET ReportServer = 'PSQLRPT22'
WHERE ReportServer = 'PSLQRPT22'
---------------------------------------------

UPDATE ClientConnection
SET LoadStageDBStartDate = '2010-03-09 01:10:33.200'
    , LoadStageDBEndDate = '2010-03-09 01:15:20.393'
    , LoadReportDBStartDate = '2010-03-09 02:55:12.807'
    , LoadReportDBEndDate = '2010-03-09 02:59:33.627'
WHERE Beta = '0'

UPDATE ClientConnection_test
SET SourceServer = 'IAPPBO510', CDCReportServer = 'IAPPBO510', Beta = '1'
WHERE SourceDB = 'PADemoDU'

UPDATE ClientConnection
SET LoadStageDBStartDate = '2010-03-09 01:10:33.200'
    , LoadStageDBEndDate = '2010-03-09 01:15:20.393'
    , LoadReportDBStartDate = '2010-03-09 02:55:12.807'
    , LoadReportDBEndDate = '2010-03-09 02:59:33.627'
    , Status = '0'
WHERE Beta = '0'

UPDATE dbo.ClientConnection_test
SET SourceServer = 'STGSQL613'
    , StageDB = 'DMart_PADemoLP_Stage'
    , CDCReportDB = 'DMart_CDCTest_Data'
    , SourceDB = 'PADemoLP2'
    , ClientID = '10024'
    , StageServer = 'STGSQLDOC710'
    , CDCReportServer = 'STGSQLDOC710'

UPDATE ClientConnection_test
SET Beta = 1
WHERE CDCReportDB = 'Dmart_GTE32_Data'

UPDATE ClientConnection_test
SET CDCReportDB = 'Dmart_GTECDC_Data'
WHERE CDCReportDB = 'Dmart_GTE32CDC_Data'

UPDATE ClientConnection
SET SSISInstanceID = 3
WHERE SourceDB = 'GTE32'

SELECT
    ClientID
    , Status
    , Beta
    , SourceServer
    , SourceDB
    , CDCReportServer
    , CDCReportDB
    , LSNPrevious
    , LSNMax
    , LSNFromPrevious
    , LSNFrom
    , StartTimeExtract
    , EndTimeExtract
    , LoanCurrentStartDate
    , LoanCurrentEndDate
    , LoanMasterStartDate
    , LoanMasterEndDate
    , LoanSecondaryStartDate
    , LoanSecondaryEndDate
FROM ClientConnection_test
ORDER BY Beta, SourceDB

UPDATE dbo.ClientConnection_test
SET LoanCurrentStartDate = '2013-04-21 14:39:24.897'
    , LoanCurrentEndDate = '2013-04-26 14:39:24.897'
    , LoanMasterStartDate = '2013-04-21 14:39:24.897'
    , LoanMasterEndDate = '2013-04-26 14:39:24.897'
    , LoanSecondaryStartDate = '2013-04-21 14:39:24.897'
    , LoanSecondaryEndDate = '2013-04-26 14:39:24.897'


text:
-- Upgrade MetaStore schema from 2.3.0 to 3.0.0
--RUN '041-HIVE-16556.derby.sql';
CREATE TABLE "APP"."METASTORE_DB_PROPERTIES" ("PROPERTY_KEY" VARCHAR(255) NOT NULL, "PROPERTY_VALUE" VARCHAR(1000) NOT NULL, "DESCRIPTION" VARCHAR(1000));

ALTER TABLE "APP"."METASTORE_DB_PROPERTIES" ADD CONSTRAINT "PROPERTY_KEY_PK" PRIMARY KEY ("PROPERTY_KEY");
--RUN '042-HIVE-16575.derby.sql';
-- Remove the NOT NULL constraint from the CHILD_INTEGER_IDX column
ALTER TABLE "APP"."KEY_CONSTRAINTS" ALTER COLUMN "CHILD_INTEGER_IDX" NULL;

CREATE INDEX "APP"."CONSTRAINTS_CONSTRAINT_TYPE_INDEX" ON "APP"."KEY_CONSTRAINTS"("CONSTRAINT_TYPE");
--RUN '043-HIVE-16922.derby.sql';
UPDATE SERDE_PARAMS
SET PARAM_KEY='collection.delim'
WHERE PARAM_KEY='colelction.delim';
--RUN '044-HIVE-16997.derby.sql';
ALTER TABLE "APP"."PART_COL_STATS" ADD COLUMN "BIT_VECTOR" BLOB;
--RUN '045-HIVE-16886.derby.sql';
INSERT INTO "APP"."NOTIFICATION_SEQUENCE" ("NNI_ID", "NEXT_EVENT_ID") SELECT * FROM (VALUES (1,1)) tmp_table WHERE NOT EXISTS ( SELECT "NEXT_EVENT_ID" FROM "APP"."NOTIFICATION_SEQUENCE");
--RUN '046-HIVE-17566.derby.sql';
CREATE TABLE "APP"."WM_RESOURCEPLAN" (RP_ID BIGINT NOT NULL, NAME VARCHAR(128) NOT NULL, QUERY_PARALLELISM INTEGER, STATUS VARCHAR(20) NOT NULL, DEFAULT_POOL_ID BIGINT);
CREATE UNIQUE INDEX "APP"."UNIQUE_WM_RESOURCEPLAN" ON "APP"."WM_RESOURCEPLAN" ("NAME");
ALTER TABLE "APP"."WM_RESOURCEPLAN" ADD CONSTRAINT "WM_RESOURCEPLAN_PK" PRIMARY KEY ("RP_ID");

CREATE TABLE "APP"."WM_POOL" (POOL_ID BIGINT NOT NULL, RP_ID BIGINT NOT NULL, PATH VARCHAR(1024) NOT NULL, ALLOC_FRACTION DOUBLE, QUERY_PARALLELISM INTEGER, SCHEDULING_POLICY VARCHAR(1024));
CREATE UNIQUE INDEX "APP"."UNIQUE_WM_POOL" ON "APP"."WM_POOL" ("RP_ID", "PATH");
ALTER TABLE "APP"."WM_POOL" ADD CONSTRAINT "WM_POOL_PK" PRIMARY KEY ("POOL_ID");
ALTER TABLE "APP"."WM_POOL" ADD CONSTRAINT "WM_POOL_FK1" FOREIGN KEY ("RP_ID") REFERENCES "APP"."WM_RESOURCEPLAN" ("RP_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "APP"."WM_RESOURCEPLAN" ADD CONSTRAINT "WM_RESOURCEPLAN_FK1" FOREIGN KEY ("DEFAULT_POOL_ID") REFERENCES "APP"."WM_POOL" ("POOL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE TABLE "APP"."WM_TRIGGER" (TRIGGER_ID BIGINT NOT NULL, RP_ID BIGINT NOT NULL, NAME VARCHAR(128) NOT NULL, TRIGGER_EXPRESSION VARCHAR(1024), ACTION_EXPRESSION VARCHAR(1024), IS_IN_UNMANAGED INTEGER NOT NULL DEFAULT 0);
CREATE UNIQUE INDEX "APP"."UNIQUE_WM_TRIGGER" ON "APP"."WM_TRIGGER" ("RP_ID", "NAME");
ALTER TABLE "APP"."WM_TRIGGER" ADD CONSTRAINT "WM_TRIGGER_PK" PRIMARY KEY ("TRIGGER_ID");
ALTER TABLE "APP"."WM_TRIGGER" ADD CONSTRAINT "WM_TRIGGER_FK1" FOREIGN KEY ("RP_ID") REFERENCES "APP"."WM_RESOURCEPLAN" ("RP_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE TABLE "APP"."WM_POOL_TO_TRIGGER"  (POOL_ID BIGINT NOT NULL, TRIGGER_ID BIGINT NOT NULL);
ALTER TABLE "APP"."WM_POOL_TO_TRIGGER" ADD CONSTRAINT "WM_POOL_TO_TRIGGER_PK" PRIMARY KEY ("POOL_ID", "TRIGGER_ID");
ALTER TABLE "APP"."WM_POOL_TO_TRIGGER" ADD CONSTRAINT "WM_POOL_TO_TRIGGER_FK1" FOREIGN KEY ("POOL_ID") REFERENCES "APP"."WM_POOL" ("POOL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "APP"."WM_POOL_TO_TRIGGER" ADD CONSTRAINT "WM_POOL_TO_TRIGGER_FK2" FOREIGN KEY ("TRIGGER_ID") REFERENCES "APP"."WM_TRIGGER" ("TRIGGER_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE TABLE "APP"."WM_MAPPING" (MAPPING_ID BIGINT NOT NULL, RP_ID BIGINT NOT NULL, ENTITY_TYPE VARCHAR(128) NOT NULL, ENTITY_NAME VARCHAR(128) NOT NULL, POOL_ID BIGINT, ORDERING INTEGER);
CREATE UNIQUE INDEX "APP"."UNIQUE_WM_MAPPING" ON "APP"."WM_MAPPING" ("RP_ID", "ENTITY_TYPE", "ENTITY_NAME");
ALTER TABLE "APP"."WM_MAPPING" ADD CONSTRAINT "WM_MAPPING_PK" PRIMARY KEY ("MAPPING_ID");
ALTER TABLE "APP"."WM_MAPPING" ADD CONSTRAINT "WM_MAPPING_FK1" FOREIGN KEY ("RP_ID") REFERENCES "APP"."WM_RESOURCEPLAN" ("RP_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "APP"."WM_MAPPING" ADD CONSTRAINT "WM_MAPPING_FK2" FOREIGN KEY ("POOL_ID") REFERENCES "APP"."WM_POOL" ("POOL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Upgrades for Schema Registry objects
ALTER TABLE "APP"."SERDES" ADD COLUMN "DESCRIPTION" VARCHAR(4000);
ALTER TABLE "APP"."SERDES" ADD COLUMN "SERIALIZER_CLASS" VARCHAR(4000);
ALTER TABLE "APP"."SERDES" ADD COLUMN "DESERIALIZER_CLASS" VARCHAR(4000);
ALTER TABLE "APP"."SERDES" ADD COLUMN "SERDE_TYPE" INTEGER;

CREATE TABLE "APP"."I_SCHEMA" (
  "SCHEMA_ID" bigint primary key,
  "SCHEMA_TYPE" integer not null,
  "NAME" varchar(256) unique,
  "DB_ID" bigint references "APP"."DBS" ("DB_ID"),
  "COMPATIBILITY" integer not null,
  "VALIDATION_LEVEL" integer not null,
  "CAN_EVOLVE" char(1) not null,
  "SCHEMA_GROUP" varchar(256),
  "DESCRIPTION" varchar(4000)
);

CREATE TABLE "APP"."SCHEMA_VERSION" (
  "SCHEMA_VERSION_ID" bigint primary key,
  "SCHEMA_ID" bigint references "APP"."I_SCHEMA" ("SCHEMA_ID"),
  "VERSION" integer not null,
  "CREATED_AT" bigint not null,
  "CD_ID" bigint references "APP"."CDS" ("CD_ID"),
  "STATE" integer not null,
  "DESCRIPTION" varchar(4000),
  "SCHEMA_TEXT" clob,
  "FINGERPRINT" varchar(256),
  "SCHEMA_VERSION_NAME" varchar(256),
  "SERDE_ID" bigint references "APP"."SERDES" ("SERDE_ID")
);

CREATE UNIQUE INDEX "APP"."UNIQUE_SCHEMA_VERSION" ON "APP"."SCHEMA_VERSION" ("SCHEMA_ID", "VERSION");

UPDATE "APP".VERSION SET SCHEMA_VERSION='3.0.0', VERSION_COMMENT='Hive release version 3.0.0' where VER_ID=1;

-- 048-HIVE-14498
-- create mv_creation_metadata table
CREATE TABLE "APP"."MV_CREATION_METADATA" (
  "MV_CREATION_METADATA_ID" BIGINT NOT NULL,
  "DB_NAME" VARCHAR(128) NOT NULL,
  "TBL_NAME" VARCHAR(256) NOT NULL,
  "TXN_LIST" CLOB
);

CREATE TABLE "APP"."MV_TABLES_USED" (
  "MV_CREATION_METADATA_ID" BIGINT NOT NULL,
  "TBL_ID" BIGINT NOT NULL
);

ALTER TABLE "APP"."MV_CREATION_METADATA" ADD CONSTRAINT "MV_CREATION_METADATA_PK" PRIMARY KEY ("MV_CREATION_METADATA_ID");

CREATE UNIQUE INDEX "APP"."MV_UNIQUE_TABLE" ON "APP"."MV_CREATION_METADATA" ("TBL_NAME", "DB_NAME");

ALTER TABLE "APP"."MV_TABLES_USED" ADD CONSTRAINT "MV_TABLES_USED_FK1" FOREIGN KEY ("MV_CREATION_METADATA_ID") REFERENCES "APP"."MV_CREATION_METADATA" ("MV_CREATION_METADATA_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE "APP"."MV_TABLES_USED" ADD CONSTRAINT "MV_TABLES_USED_FK2" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;

-- modify completed_txn_components table
ALTER TABLE "APP"."COMPLETED_TXN_COMPONENTS" ADD "CTC_TIMESTAMP" timestamp;

UPDATE "APP"."TBLS" SET "IS_REWRITE_ENABLED" = CURRENT_TIMESTAMP;

ALTER TABLE "APP"."COMPLETED_TXN_COMPONENTS" ALTER COLUMN "CTC_TIMESTAMP" SET DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE "APP"."COMPLETED_TXN_COMPONENTS" ALTER COLUMN "CTC_TIMESTAMP" NOT NULL;

CREATE INDEX "APP"."COMPLETED_TXN_COMPONENTS_IDX" ON "APP"."COMPLETED_TXN_COMPONENTS" ("CTC_DATABASE", "CTC_TABLE", "CTC_PARTITION");

-- 049-HIVE-18489.derby.sql
UPDATE FUNC_RU
  SET RESOURCE_URI = 's3a' || SUBSTR(RESOURCE_URI, 4)
  WHERE RESOURCE_URI LIKE 's3n://%' ;

UPDATE SKEWED_COL_VALUE_LOC_MAP
  SET LOCATION = 's3a' || SUBSTR(LOCATION, 4)
  WHERE LOCATION LIKE 's3n://%' ;

UPDATE SDS
  SET LOCATION = 's3a' || SUBSTR(LOCATION, 4)
  WHERE LOCATION LIKE 's3n://%' ;

UPDATE DBS
  SET DB_LOCATION_URI = 's3a' || SUBSTR(DB_LOCATION_URI, 4)
  WHERE DB_LOCATION_URI LIKE 's3n://%' ;

-- 050-HIVE-18192.derby.sql
CREATE TABLE TXN_TO_WRITE_ID (
  T2W_TXNID bigint NOT NULL,
  T2W_DATABASE varchar(128) NOT NULL,
  T2W_TABLE varchar(256) NOT NULL,
  T2W_WRITEID bigint NOT NULL
);

CREATE UNIQUE INDEX TBL_TO_TXN_ID_IDX ON TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_TXNID);
CREATE UNIQUE INDEX TBL_TO_WRITE_ID_IDX ON TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_WRITEID);

CREATE TABLE NEXT_WRITE_ID (
  NWI_DATABASE varchar(128) NOT NULL,
  NWI_TABLE varchar(256) NOT NULL,
  NWI_NEXT bigint NOT NULL
);

CREATE UNIQUE INDEX NEXT_WRITE_ID_IDX ON NEXT_WRITE_ID (NWI_DATABASE, NWI_TABLE);

RENAME COLUMN COMPACTION_QUEUE.CQ_HIGHEST_TXN_ID TO CQ_HIGHEST_WRITE_ID;

RENAME COLUMN COMPLETED_COMPACTIONS.CC_HIGHEST_TXN_ID TO CC_HIGHEST_WRITE_ID;

-- Modify txn_components/completed_txn_components tables to add write id.
ALTER TABLE TXN_COMPONENTS ADD TC_WRITEID bigint;
ALTER TABLE COMPLETED_TXN_COMPONENTS ADD CTC_WRITEID bigint;

-- HIVE-18726
-- add a new column to support default value for DEFAULT constraint
ALTER TABLE "APP"."KEY_CONSTRAINTS" ADD COLUMN "DEFAULT_VALUE" VARCHAR(400);

ALTER TABLE "APP"."HIVE_LOCKS" ALTER COLUMN "HL_TXNID" NOT NULL;


text:
source 1_set_vars.sql

DELETE from datadictionary where instance=@instance and scope=@scope and entity=@entity;

INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','1','dxmethod','1. Diagnosis method - Responses in this form are based on diagnosis by:','','','1','udsdiagnosis','DXMETHOD','1','smallint','','1','null','Diagnosis method - Responses in this form are based on diagnosis by:','1.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','2','normcog','2. Does the subject have normal cognition (global CDR=0 and/or neuropsychological testing withing normal range) and normal behavior (i.e., the subject does not exhibit behavior sufficient to diagnose MCI or dementia due to FTLD or LBD)?','','','1','udsdiagnosis','NORMCOG','2','smallint','','1','null','Does the subject have normal cognition (global CDR=0 and/or neuropsychological testing withing normal range) and normal behavior (i.e., the subject does not exhibit behavior sufficient to diagnose MCI or dementia due to FTLD or LBD)?','2.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','3','demented','3. Does the subject meet criteria for dementia?','','','1','udsdiagnosis','DEMENTED','3','smallint','','1','null','Does the subject meet criteria for dementia?','3.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','4','amndem','4a. Amnestic multidomain dementia syndrome','','','1','udsdiagnosis','AMNDEM','4','smallint','','1','null','Amnestic multidomain dementia syndrome','4a.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','5','pca','4b. Posterior cortical atrophy syndrome (or primary visual presentation)','','','1','udsdiagnosis','PCA','5','smallint','','1','null','Posterior cortical atrophy syndrome (or primary visual presentation)','4b.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','6','ppasyn','4c. Primary progressive aphasia (PPA) syndrome','','','1','udsdiagnosis','PPASYN','6','smallint','','1','null','Primary progressive aphasia (PPA) syndrome','4c.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','7','ppasynt','4c1. If PPA present','','','1','udsdiagnosis','PPASYNT','7','smallint','','1','null','If PPA present','4c1.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','8','ftdsyn','4d. Behavioral variant FTD (bvFTD) syndrome','','','1','udsdiagnosis','FTDSYN','8','smallint','','1','null','Behavioral variant FTD (bvFTD) syndrome','4d.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','9','lbdsyn','4e. Lewy body dementia syndrome','','','1','udsdiagnosis','LBDSYN','9','smallint','','1','null','Lewy body dementia syndrome','4e.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','10','namndem','4f. Non-amnestic multidomain dementia, not PCA, PPA, bvFTD, or DLB syndrome','','','1','udsdiagnosis','NAMNDEM','10','smallint','','1','null','Non-amnestic multidomain dementia, not PCA, PPA, bvFTD, or DLB syndrome','4f.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','11','mciamem','5a. Amnestic MCI, single domain (aMCI SD)','','','1','udsdiagnosis','MCIAMEM','11','smallint','','1','null','Amnestic MCI, single domain (aMCI SD)','5a.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','12','mciaplus','5b. Amnestic MCI, multiple domains (aMCI MD)','','','1','udsdiagnosis','MCIAPLUS','12','smallint','','1','null','Amnestic MCI, multiple domains (aMCI MD)','5b.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','13','mciaplan','5b1. Amnestic MCI, multiple domains - Language','','','1','udsdiagnosis','MCIAPLAN','13','smallint','','1','null','Amnestic MCI, multiple domains - Language','5b1.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','14','mciapatt','5b2. Amnestic MCI, multiple domains - Attention','','','1','udsdiagnosis','MCIAPATT','14','smallint','','1','null','Amnestic MCI, multiple domains - Attention','5b2.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','15','mciapex','5b3. Amnestic MCI, multiple domains - Executive','','','1','udsdiagnosis','MCIAPEX','15','smallint','','1','null','Amnestic MCI, multiple domains - Executive','5b3.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','16','mciapvis','5b4. Amnestic MCI, multiple domains - Visuospatial','','','1','udsdiagnosis','MCIAPVIS','16','smallint','','1','null','Amnestic MCI, multiple domains - Visuospatial','5b4.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','17','mcinon1','5c. Non-amnestic MCI, single domain (naMCI SD)','','','1','udsdiagnosis','MCINON1','17','smallint','','1','null','Non-amnestic MCI, single domain (naMCI SD)','5c.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','18','mcin1lan','5c1. Non-amnestic MCI, single domain - Language','','','1','udsdiagnosis','MCIN1LAN','18','smallint','','1','null','Non-amnestic MCI, single domain - Language','5c1.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','19','mcin1att','5c2. Non-amnestic MCI, single domain - Attention','','','1','udsdiagnosis','MCIN1ATT','19','smallint','','1','null','Non-amnestic MCI, single domain - Attention','5c2.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','20','mcin1ex','5c3. Non-amnestic MCI, single domain - Executive','','','1','udsdiagnosis','MCIN1EX','20','smallint','','1','null','Non-amnestic MCI, single domain - Executive','5c3.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','21','mcin1vis','5c4. Non-amnestic MCI, single domain - Visuospatial','','','1','udsdiagnosis','MCIN1VIS','21','smallint','','1','null','Non-amnestic MCI, single domain - Visuospatial','5c4.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','22','mcinon2','5d. Non-amnestic MCI, multiple domains (naMCI MD)','','','1','udsdiagnosis','MCINON2','22','smallint','','1','null','Non-amnestic MCI, multiple domains (naMCI MD)','5d.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','23','mcin2lan','5d1. Non-amnestic MCI, multiple domains - Language','','','1','udsdiagnosis','MCIN2LAN','23','smallint','','1','null','Non-amnestic MCI, multiple domains - Language','5d1.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','24','mcin2att','5d2. Non-amnestic MCI, multiple domains - Attention','','','1','udsdiagnosis','MCIN2ATT','24','smallint','','1','null','Non-amnestic MCI, multiple domains - Attention','5d2.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','25','mcin2ex','5d3. Non-amnestic MCI, multiple domains - Executive','','','1','udsdiagnosis','MCIN2EX','25','smallint','','1','null','Non-amnestic MCI, multiple domains - Executive','5d3.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','26','mcin2vis','5d4. Non-amnestic MCI, multiple domains - Visuospatial','','','1','udsdiagnosis','MCIN2VIS','26','smallint','','1','null','Non-amnestic MCI, multiple domains - Visuospatial','5d4.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','27','impnomci','5e. Cognitively impaired, not MCI','','','1','udsdiagnosis','IMPNOMCI','27','smallint','','1','null','Cognitively impaired, not MCI','5e.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','28','amylpet','6a. Biomarker findings - Abnormally elevated amyloid on PET','','','1','udsdiagnosis','AMYLPET','28','smallint','','1','null','Biomarker findings - Abnormally elevated amyloid on PET','6a.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','29','amylcsf','6b. Biomarker findings - Abnormally low amyloid in CSF','','','1','udsdiagnosis','AMYLCSF','29','smallint','','1','null','Biomarker findings - Abnormally low amyloid in CSF','6b.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','30','fdgad','6c. Biomarker findings - FDG-PET pattern of AD','','','1','udsdiagnosis','FDGAD','30','smallint','','1','null','Biomarker findings - FDG-PET pattern of AD','6c.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','31','hippatr','6d. Biomarker findings - Hippocampal atrophy','','','1','udsdiagnosis','HIPPATR','31','smallint','','1','null','Biomarker findings - Hippocampal atrophy','6d.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','32','taupetad','6e. Biomarker findings - Tau PET evidence for AD','','','1','udsdiagnosis','TAUPETAD','32','smallint','','1','null','Biomarker findings - Tau PET evidence for AD','6e.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','33','csftau','6f. Biomarker findings - Abnormally elevated CSF Tau or pTau','','','1','udsdiagnosis','CSFTAU','33','smallint','','1','null','Biomarker findings - Abnormally elevated CSF Tau or pTau','6f.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','34','fdgftld','6g. Biomarker findings - FDG-PET evidence for frontal or anterior temporal hypometabolism for FTLD','','','1','udsdiagnosis','FDGFTLD','34','smallint','','1','null','Biomarker findings - FDG-PET evidence for frontal or anterior temporal hypometabolism for FTLD','6g.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','35','tpetftld','6h. Biomarker findings - Tau PET evidence for FTLD','','','1','udsdiagnosis','TPETFTLD','35','smallint','','1','null','Biomarker findings - Tau PET evidence for FTLD','6h.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','36','mrftld','6i. Biomarker findings - Structural MR evidence for frontal or anterior temporal atrophy for FTLD','','','1','udsdiagnosis','MRFTLD','36','smallint','','1','null','Biomarker findings - Structural MR evidence for frontal or anterior temporal atrophy for FTLD','6i.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','37','datscan','6j. Biomarker findings - Dopamine transporter scan (DATscan) evidence for Lewy body disease','','','1','udsdiagnosis','DATSCAN','37','smallint','','1','null','Biomarker findings - Dopamine transporter scan (DATscan) evidence for Lewy body disease','6j.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','38','othbiom','6k. Biomarker findings - Other','','','1','udsdiagnosis','OTHBIOM','38','smallint','','1','null','Biomarker findings - Other','6k.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','39','othbiomx','6k1. Biomarker findings - Other (SPECIFY)','','','1','udsdiagnosis','OTHBIOMX','39','varchar','60','1','null','Biomarker findings - Other (SPECIFY)','6k1.');
INSERT INTO datadictionary (`instance`,`scope`,`entity`,`prop_order`,`prop_name`,`prop_description`,`data_values`,`data_calculation`,`required`,`db_table`,`db_column`,`db_order`,`db_datatype`,`db_datalength`,`db_nullable`,`db_default`,`notes`,`legacy_column`) VALUES ('lava','crms-nacc','udsdiagnosis3','40','imaglinf','7a. Imaging findings - Large vessel infarct(s)','','','1','udsdiagnosis','IMAGLINF','40','smallint','','1','null','Imaging findings - Large vessel infarct(s)','7a.');


text:
-- 规则表
CREATE TABLE `rule` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rule_key` varchar(255) NOT NULL DEFAULT '' COMMENT '规则编码',
  `version` varchar(255) NOT NULL DEFAULT '' COMMENT '规则编码',
  `content` varchar(2048) NOT NULL DEFAULT '' COMMENT '规则内容',
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rule_key` (`rule_key`) USING BTREE,
  KEY `uk_update_time` (`update_time`) USING BTREE,
  KEY `uk_version` (`version`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- 用户表
CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `address` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

-- 插入规则
INSERT INTO `rule` (`id`, `rule_key`, `version`, `content`, `create_time`, `update_time`) VALUES ('1', 'score', '1', 'package com.xiaolyuh.drools.address\r\n\r\nimport com.xiaolyuh.domain.model.Address;\r\nimport com.xiaolyuh.domain.fact.AddressCheckResult;\r\n\r\nrule \"address first 6\"\r\n    no-loop true\r\n    lock-on-active true\r\n    salience 9999999\r\n    when\r\n		eval(true);\r\n    then\r\n        System.out.println(\"执行了规则Address6文件\"); \r\nend\r\n\r\nrule \"Postcode 6 numbers\"\r\n\r\n    when\r\n        address : Address(postcode != null, postcode matches \"([0-9]{6})\")\r\n        checkResult : AddressCheckResult();\r\n    then\r\n        checkResult.setPostCodeResult(true);\r\n		System.out.println(\"规则6中打印日志：校验通过!\");\r\nend', '2018-01-02 15:58:46', '2018-01-02 15:58:46');
INSERT INTO `rule` (`id`, `rule_key`, `version`, `content`, `create_time`, `update_time`) VALUES ('3', 'score1', '2', 'package com.xiaolyuh.drools.address\r\n\r\nimport com.xiaolyuh.domain.model.Address;\r\nimport com.xiaolyuh.domain.fact.AddressCheckResult;\r\n\r\nrule \"address first 7\"\r\n    no-loop true\r\n    lock-on-active true\r\n    salience 9999999\r\n    when\r\n		eval(true);\r\n    then\r\n        System.out.println(\"执行了规则Address7文件\"); \r\nend\r\n\r\nrule \"Postcode 7 numbers\"\r\n\r\n    when\r\n        address : Address(postcode != null, postcode matches \"([0-9]{6})\")\r\n        checkResult : AddressCheckResult();\r\n    then\r\n        checkResult.setPostCodeResult(true);\r\n		System.out.println(\"规则7中打印日志：校验通过!\");\r\nend', '2018-01-02 16:01:55', '2018-01-02 16:01:55');
INSERT INTO `rule` (`id`, `rule_key`, `version`, `content`, `create_time`, `update_time`) VALUES ('4', 'hello world', '1', 'package com.xiaolyuh.drools\r\nimport com.xiaolyuh.domain.model.Message;\r\n\r\nrule \"message first\"\r\n    no-loop true\r\n    lock-on-active true\r\n    salience 9999999\r\n    when\r\n		eval(true);\r\n    then\r\n        System.out.println(\"执行了规则Message文件\"); \r\nend\r\n\r\nrule \"rule1\"\r\n	when\r\n		Message( status == 1, myMessage : msg )\r\n	then\r\n		System.out.println( 1+\":\"+myMessage );\r\nend\r\n', '2018-01-02 16:01:55', '2018-01-02 16:01:55');

-- 文件名修改成data.sql放到resources文件夹下才会执行
insert into person(name,age,address) values('wyf',32,'合肥');
insert into person(name,age,address) values('xx',31,'北京');
insert into person(name,age,address) values('yy',30,'上海');
insert into person(name,age,address) values('zz',29,'南京');
insert into person(name,age,address) values('aa',28,'武汉');
insert into person(name,age,address) values('bb',27,'合肥');


text:
CREATE TABLE IF NOT EXISTS "session" (
   "sid" varchar NOT NULL COLLATE "default",
   "sess" json NOT NULL,
   "expire" timestamp(6) NOT NULL
)
WITH (OIDS=FALSE);

ALTER TABLE "session" ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;

CREATE INDEX "IDX_session_expire" ON "session" ("expire");

CREATE TABLE IF NOT EXISTS consumer (
    mail_consumer VARCHAR(255) PRIMARY KEY,
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    blood_group VARCHAR(3) NOT NULL,
    gender VARCHAR(20) NOT NULL,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    phone_number VARCHAR(10) NOT NULL,
    password_user VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS admin (
    mail_admin VARCHAR(255) PRIMARY KEY,
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    phone_number VARCHAR(10) NOT NULL,
    password_admin VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS hospital (
    hospital_id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    mail VARCHAR(255) NOT NULL,
    phone_number VARCHAR(10) NOT NULL
);

CREATE TABLE IF NOT EXISTS comment (
    comment_id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    text_comment TEXT NOT NULL,
    mail_consumer VARCHAR(255) NOT NULL,
    date_publication timestamp NOT NULL DEFAULT now(),
    FOREIGN KEY(mail_consumer) REFERENCES consumer(mail_consumer) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS appointment (
    mail_consumer VARCHAR(255),
    hospital_id INT,
    date_appointment DATE NOT NULL,
    type_appointment VARCHAR(20) NOT NULL,
    status VARCHAR(20) NOT NULL,
    information VARCHAR(255),
    PRIMARY KEY (mail_consumer, hospital_id, date_appointment),
    FOREIGN KEY(mail_consumer) REFERENCES consumer(mail_consumer) ON DELETE CASCADE,
    FOREIGN KEY(hospital_id) REFERENCES hospital(hospital_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS blood_bag (
    id VARCHAR(255) PRIMARY KEY,
    hospital_id INT NOT NULL,
    blood_group VARCHAR(3) NOT NULL,
    date_donation DATE NOT NULL,
    reserved BOOLEAN NOT NULL,
    FOREIGN KEY(hospital_id) REFERENCES hospital(hospital_id) ON DELETE CASCADE
);


text:
CREATE TYPE public.civilite_type AS ENUM (
    'monsieur',
    'madame'
);
CREATE TYPE public.lieu_vie_type AS ENUM (
    'domicile',
    'etablissement',
    'etablissement_conservation_domicile',
    'sdf'
);
CREATE TYPE public.mesure_status_type AS ENUM (
    'en_attente',
    'en_cours',
    'eteinte'
);
CREATE TABLE public.access_tokens (
    id integer NOT NULL,
    user_id integer NOT NULL,
    access_token character varying(255) NOT NULL,
    editor_id integer NOT NULL,
    editor_url character varying(255) NOT NULL,
    expired boolean DEFAULT false,
    expired_on timestamp with time zone,
    refresh_token character varying(255),
    refresh_token_expires_on timestamp with time zone
);
CREATE SEQUENCE public.access_tokens_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.access_tokens_id_seq OWNED BY public.access_tokens.id;
CREATE TABLE public.api_logs (
    id integer NOT NULL,
    editor_id integer,
    token character varying(1200),
    request_url character varying(255),
    request_method character varying(255),
    request_params jsonb,
    response jsonb,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);
CREATE SEQUENCE public.api_logs_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.api_logs_id_seq OWNED BY public.api_logs.id;
CREATE TABLE public.authorization_codes (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    code character varying(255),
    redirect_uri character varying(255),
    client_id integer NOT NULL,
    user_id integer NOT NULL,
    expires_at timestamp with time zone
);
CREATE SEQUENCE public.authorization_codes_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.authorization_codes_id_seq OWNED BY public.authorization_codes.id;
CREATE TABLE public.cause_sortie_mesure (
    value text NOT NULL,
    label text NOT NULL
);
CREATE TABLE public.champ_mesure (
    value text NOT NULL,
    label text NOT NULL
);
CREATE TABLE public.civilite (
    value text NOT NULL
);
CREATE TABLE public.geolocalisation_code_postal (
    id integer NOT NULL,
    code_postal character varying(255),
    latitude real,
    longitude real,
    cities character varying(255),
    insee character varying(255)
);
CREATE SEQUENCE public."codePostalLatLngs_id_seq"
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public."codePostalLatLngs_id_seq" OWNED BY public.geolocalisation_code_postal.id;
CREATE TABLE public.commentaires (
    id integer NOT NULL,
    comment text,
    mandataire_id integer,
    ti_id integer,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    service_id integer
);
CREATE SEQUENCE public.commentaires_co_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.commentaires_co_id_seq OWNED BY public.commentaires.id;
CREATE TABLE public.departements (
    id integer NOT NULL,
    id_region integer,
    code character varying(255) NOT NULL,
    nom character varying(255) NOT NULL
);
CREATE SEQUENCE public.departements_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.departements_id_seq OWNED BY public.departements.id;
CREATE TABLE public.direction (
    id integer NOT NULL,
    region_id integer,
    user_id integer,
    department_id integer,
    type character varying(255)
);
CREATE SEQUENCE public.direction_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.direction_id_seq OWNED BY public.direction.id;
CREATE TABLE public.editor_token_requests (
    id integer NOT NULL,
    email character varying(255) NOT NULL,
    name character varying(255),
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);
CREATE SEQUENCE public.editor_token_requests_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.editor_token_requests_id_seq OWNED BY public.editor_token_requests.id;
CREATE TABLE public.editors (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    api_token character varying(255) NOT NULL,
    redirect_uris jsonb DEFAULT '[]'::jsonb
);
CREATE SEQUENCE public.editors_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.editors_id_seq OWNED BY public.editors.id;
CREATE TABLE public.enquete_reponses (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    submitted_at timestamp with time zone,
    enquete_id integer,
    mandataire_id integer,
    service_id integer,
    enquete_reponses_informations_mandataire_id integer,
    enquete_reponses_prestations_sociale_id integer,
    enquete_reponses_agrements_formations_id integer,
    enquete_reponses_activite_id integer,
    enquete_reponses_populations_id integer,
    enquete_reponses_modalites_exercice_id integer,
    enquete_reponses_financement_id integer,
    enquete_reponses_prepose_personel_formation_id integer,
    enquete_reponses_prepose_prestations_sociales_id integer,
    status character varying(255) DEFAULT 'draft'::character varying,
    uploaded_on timestamp with time zone,
    enquete_reponses_service_informations_id integer,
    user_type character varying(255) NOT NULL,
    enquete_reponses_service_personnel_formation_id integer,
    departement_id integer
);
CREATE TABLE public.enquete_reponses_activite (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    last_update timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    curatelle_renforcee_etablissement_debut_annee integer,
    curatelle_renforcee_etablissement_fin_annee integer,
    curatelle_renforcee_domicile_debut_annee integer,
    curatelle_renforcee_domicile_fin_annee integer,
    curatelle_simple_etablissement_debut_annee integer,
    curatelle_simple_etablissement_fin_annee integer,
    curatelle_simple_domicile_debut_annee integer,
    curatelle_simple_domicile_fin_annee integer,
    tutelle_etablissement_debut_annee integer,
    tutelle_etablissement_fin_annee integer,
    tutelle_domicile_debut_annee integer,
    tutelle_domicile_fin_annee integer,
    accompagnement_judiciaire_etablissement_debut_annee integer,
    accompagnement_judiciaire_etablissement_fin_annee integer,
    accompagnement_judiciaire_domicile_debut_annee integer,
    accompagnement_judiciaire_domicile_fin_annee integer,
    curatelle_biens_etablissement_debut_annee integer,
    curatelle_biens_etablissement_fin_annee integer,
    curatelle_biens_domicile_debut_annee integer,
    curatelle_biens_domicile_fin_annee integer,
    curatelle_personne_etablissement_debut_annee integer,
    curatelle_personne_etablissement_fin_annee integer,
    curatelle_personne_domicile_debut_annee integer,
    curatelle_personne_domicile_fin_annee integer,
    revisions_main_levee integer,
    revisions_masp integer,
    revisions_reconduction integer,
    revisions_changement integer,
    revisions_autre integer,
    sorties_main_levee integer,
    sorties_deces integer,
    sorties_masp integer,
    curatelle_renforcee_etablissement_mesures_nouvelles integer,
    curatelle_renforcee_etablissement_sortie_mesures integer,
    curatelle_renforcee_domicile_mesures_nouvelles integer,
    curatelle_renforcee_domicile_sortie_mesures integer,
    curatelle_simple_etablissement_mesures_nouvelles integer,
    curatelle_simple_etablissement_sortie_mesures integer,
    curatelle_simple_domicile_mesures_nouvelles integer,
    curatelle_simple_domicile_sortie_mesures integer,
    tutelle_etablissement_mesures_nouvelles integer,
    tutelle_etablissement_sortie_mesures integer,
    tutelle_domicile_mesures_nouvelles integer,
    tutelle_domicile_sortie_mesures integer,
    accompagnement_judiciaire_etablissement_mesures_nouvelles integer,
    accompagnement_judiciaire_etablissement_sortie_mesures integer,
    accompagnement_judiciaire_domicile_mesures_nouvelles integer,
    accompagnement_judiciaire_domicile_sortie_mesures integer,
    curatelle_biens_etablissement_mesures_nouvelles integer,
    curatelle_biens_etablissement_sortie_mesures integer,
    curatelle_biens_domicile_mesures_nouvelles integer,
    curatelle_biens_domicile_sortie_mesures integer,
    curatelle_personne_etablissement_mesures_nouvelles integer,
    curatelle_personne_etablissement_sortie_mesures integer,
    curatelle_personne_domicile_mesures_nouvelles integer,
    curatelle_personne_domicile_sortie_mesures integer,
    subroge_tuteur_createur_debut_annee integer,
    subroge_tuteur_createur_mesures_nouvelles integer,
    subroge_tuteur_createur_sortie_mesures integer,
    subroge_tuteur_createur_fin_annee integer,
    sauvegarde_justice_debut_annee integer,
    sauvegarde_justice_mesures_nouvelles integer,
    sauvegarde_justice_sortie_mesures integer,
    sauvegarde_justice_fin_annee integer,
    mandat_adhoc_majeur_debut_annee integer,
    mandat_adhoc_majeur_mesures_nouvelles integer,
    mandat_adhoc_majeur_sortie_mesures integer,
    mandat_adhoc_majeur_fin_annee integer
);
CREATE SEQUENCE public.enquete_reponses_activite_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.enquete_reponses_activite_id_seq OWNED BY public.enquete_reponses_activite.id;
CREATE TABLE public.enquete_reponses_agrements_formations (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    last_update timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    annee_agrement integer,
    nb_departements character varying(255),
    annee_debut_activite integer,
    cumul_delegue_service boolean,
    cumul_delegue_service_etp character varying(255),
    cumul_prepose boolean,
    cumul_prepose_etp character varying(255),
    debut_activite_avant_2009 boolean,
    niveau_qualification integer,
    secretaire_specialise boolean,
    nb_mesures_dep_finance integer,
    nb_mesures_dep_autres integer,
    cnc_annee_obtention integer,
    cnc_heures_formation real,
    secretaire_specialise_etp_n1 real,
    secretaire_specialise_etp_n2 real,
    secretaire_specialise_etp_n3 real,
    secretaire_specialise_etp_n4 real,
    secretaire_specialise_etp_n5 real,
    secretaire_specialise_etp_n6 real
);
CREATE SEQUENCE public.enquete_reponses_agrements_formations_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.enquete_reponses_agrements_formations_id_seq OWNED BY public.enquete_reponses_agrements_formations.id;
CREATE TABLE public.enquete_reponses_financement (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    last_update timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    charges_personnel numeric(16,8),
    charges_preposes numeric(16,8),
    charges_fonctionnement numeric(16,8),
    produits_bareme_prelevements numeric(16,8),
    autre_produits numeric(16,8),
    financement_public numeric(16,8),
    aide_sociale_conseil_departemental numeric(16,8)
);
CREATE SEQUENCE public.enquete_reponses_financement_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.enquete_reponses_financement_id_seq OWNED BY public.enquete_reponses_financement.id;
CREATE SEQUENCE public.enquete_reponses_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.enquete_reponses_id_seq OWNED BY public.enquete_reponses.id;
CREATE TABLE public.enquete_reponses_informations_mandataire (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    last_update timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    benevole boolean,
    forme_juridique character varying(255),
    sexe character varying(255),
    anciennete character varying(255),
    estimation_etp character varying(255),
    secretaire_specialise_etp real,
    local_professionnel boolean,
    departement character varying(255),
    region character varying(255),
    nom character varying(255),
    exerce_seul_activite boolean,
    exerce_secretaires_specialises boolean,
    tranche_age character varying(255)
);
CREATE SEQUENCE public.enquete_reponses_informations_mandataire_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.enquete_reponses_informations_mandataire_id_seq OWNED BY public.enquete_reponses_informations_mandataire.id;
CREATE TABLE public.enquete_reponses_modalites_exercice (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    last_update timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    departement character varying(255),
    region character varying(255),
    raison_sociale character varying(255),
    personnalite_juridique_etablissement character varying(255),
    total_mesures_etablissements integer,
    nombre_lits_journee_hospitalisation jsonb,
    actions_information_tuteurs_familiaux boolean,
    activite_exercee_par character varying(255),
    etablissements_type character varying(255)
);
CREATE SEQUENCE public.enquete_reponses_modalite_exercice_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.enquete_reponses_modalite_exercice_id_seq OWNED BY public.enquete_reponses_modalites_exercice.id;
CREATE TABLE public.enquete_reponses_populations (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    last_update timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    tutelle_age_inf_25_ans_homme integer,
    tutelle_age_inf_25_ans_femme integer,
    tutelle_age_25_39_ans_homme integer,
    tutelle_age_25_39_ans_femme integer,
    tutelle_age_40_59_ans_homme integer,
    tutelle_age_40_59_ans_femme integer,
    tutelle_age_60_74_ans_homme integer,
    tutelle_age_60_74_ans_femme integer,
    tutelle_age_sup_75_ans_homme integer,
    tutelle_age_sup_75_ans_femme integer,
    curatelle_age_inf_25_ans_homme integer,
    curatelle_age_inf_25_ans_femme integer,
    curatelle_age_25_39_ans_homme integer,
    curatelle_age_25_39_ans_femme integer,
    curatelle_age_40_59_ans_homme integer,
    curatelle_age_40_59_ans_femme integer,
    curatelle_age_60_74_ans_homme integer,
    curatelle_age_60_74_ans_femme integer,
    curatelle_age_sup_75_ans_homme integer,
    curatelle_age_sup_75_ans_femme integer,
    maj_age_inf_25_ans_homme integer,
    maj_age_inf_25_ans_femme integer,
    maj_age_25_39_ans_homme integer,
    maj_age_25_39_ans_femme integer,
    maj_age_40_59_ans_homme integer,
    maj_age_40_59_ans_femme integer,
    maj_age_60_74_ans_homme integer,
    maj_age_60_74_ans_femme integer,
    maj_age_sup_75_ans_homme integer,
    maj_age_sup_75_ans_femme integer,
    sauvegarde_justice_age_inf_25_ans_homme integer,
    sauvegarde_justice_age_inf_25_ans_femme integer,
    sauvegarde_justice_age_25_39_ans_homme integer,
    sauvegarde_justice_age_25_39_ans_femme integer,
    sauvegarde_justice_age_40_59_ans_homme integer,
    sauvegarde_justice_age_40_59_ans_femme integer,
    sauvegarde_justice_age_60_74_ans_homme integer,
    sauvegarde_justice_age_60_74_ans_femme integer,
    sauvegarde_justice_age_sup_75_ans_homme integer,
    sauvegarde_justice_age_sup_75_ans_femme integer,
    autre_mesures_age_inf_25_ans_homme integer,
    autre_mesures_age_inf_25_ans_femme integer,
    autre_mesures_age_25_39_ans_homme integer,
    autre_mesures_age_25_39_ans_femme integer,
    autre_mesures_age_40_59_ans_homme integer,
    autre_mesures_age_40_59_ans_femme integer,
    autre_mesures_age_60_74_ans_homme integer,
    autre_mesures_age_60_74_ans_femme integer,
    autre_mesures_age_sup_75_ans_homme integer,
    autre_mesures_age_sup_75_ans_femme integer,
    tutelle_anciennete_inf_1_an integer,
    tutelle_anciennete_1_3_ans integer,
    tutelle_anciennete_3_5_ans integer,
    tutelle_anciennete_5_10_ans integer,
    tutelle_anciennete_sup_10_ans integer,
    curatelle_anciennete_inf_1_an integer,
    curatelle_anciennete_1_3_ans integer,
    curatelle_anciennete_3_5_ans integer,
    curatelle_anciennete_5_10_ans integer,
    curatelle_anciennete_sup_10_ans integer,
    maj_anciennete_inf_1_an integer,
    maj_anciennete_1_3_ans integer,
    maj_anciennete_3_5_ans integer,
    maj_anciennete_5_10_ans integer,
    maj_anciennete_sup_10_ans integer,
    sauvegarde_justice_anciennete_inf_1_an integer,
    sauvegarde_justice_anciennete_1_3_ans integer,
    sauvegarde_justice_anciennete_3_5_ans integer,
    sauvegarde_justice_anciennete_5_10_ans integer,
    sauvegarde_justice_anciennete_sup_10_ans integer,
    autre_mesures_anciennete_inf_1_an integer,
    autre_mesures_anciennete_1_3_ans integer,
    autre_mesures_anciennete_3_5_ans integer,
    autre_mesures_anciennete_5_10_ans integer,
    autre_mesures_anciennete_sup_10_ans integer,
    tutelle_etablissement_personne_handicapee integer,
    tutelle_service_personne_handicapee integer,
    tutelle_ehpad integer,
    tutelle_autre_etablissement_personne_agee integer,
    tutelle_chrs integer,
    tutelle_service_hospitalier_soins_longue_duree integer,
    tutelle_service_psychiatrique integer,
    tutelle_autre_service integer,
    curatelle_etablissement_personne_handicapee integer,
    curatelle_service_personne_handicapee integer,
    curatelle_ehpad integer,
    curatelle_autre_etablissement_personne_agee integer,
    curatelle_chrs integer,
    curatelle_service_hospitalier_soins_longue_duree integer,
    curatelle_service_psychiatrique integer,
    curatelle_autre_service integer,
    maj_etablissement_personne_handicapee integer,
    maj_service_personne_handicapee integer,
    maj_ehpad integer,
    maj_autre_etablissement_personne_agee integer,
    maj_chrs integer,
    maj_service_hospitalier_soins_longue_duree integer,
    maj_service_psychiatrique integer,
    maj_autre_service integer,
    sauvegarde_justice_etablissement_personne_handicapee integer,
    sauvegarde_justice_service_personne_handicapee integer,
    sauvegarde_justice_ehpad integer,
    sauvegarde_justice_autre_etablissement_personne_agee integer,
    sauvegarde_justice_chrs integer,
    sauvegarde_justice_service_hospitalier_soins_longue_duree integer,
    sauvegarde_justice_service_psychiatrique integer,
    sauvegarde_justice_autre_service integer,
    autre_mesures_etablissement_personne_handicapee integer,
    autre_mesures_service_personne_handicapee integer,
    autre_mesures_ehpad integer,
    autre_mesures_autre_etablissement_personne_agee integer,
    autre_mesures_chrs integer,
    autre_mesures_service_hospitalier_soins_longue_duree integer,
    autre_mesures_service_psychiatrique integer,
    autre_mesures_autre_service integer
);
CREATE SEQUENCE public.enquete_reponses_populations_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.enquete_reponses_populations_id_seq OWNED BY public.enquete_reponses_populations.id;
CREATE TABLE public.enquete_reponses_prepose_personel_formation (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    last_update timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    nb_preposes_mjpm integer,
    nb_preposes_mjpm_etp real,
    formation_preposes_mjpm jsonb,
    niveaux_qualification jsonb,
    nb_preposes_homme integer,
    nb_preposes_femme integer,
    nb_autre_personnel integer,
    nb_autre_personnel_etp real