text:
WITH stock_financials_today AS (
    SELECT
        *
    FROM
        `is3107.lti_dwd.dwd_yfinance_stock_financials_daily` stock_financials
    LEFT JOIN (
        SELECT
            Stock_Code
        FROM
            `is3107.lti_dwd.dwd_stock_prices_daily`
        WHERE
            p_date = "{{ dag_run.logical_date.astimezone(dag.timezone) | ds }}"
    ) stock_prices
    ON
        stock_financials.Ticker = CONCAT(stock_prices.Stock_Code, '.SI')
    WHERE
        stock_financials.p_date = "{{ dag_run.logical_date.astimezone(dag.timezone) | ds }}"
),
stock_prices_today AS (
    SELECT
        *
    FROM
        `is3107.lti_dwd.dwd_stock_prices_daily` stock_prices
    WHERE
        stock_prices.p_date = "{{ dag_run.logical_date.astimezone(dag.timezone) | ds }}"
)

SELECT
    COALESCE(stock_financials.p_date, stock_prices.p_date) AS p_date,
    COALESCE(stock_prices.Stock_Code, stock_financials.Ticker) AS Stock_Code,
    COALESCE(stock_prices.Stock_Name, stock_financials.Ticker) AS Stock_Name, -- As y_finance table does not take in company name, substitute with Ticker if needed
    Open,
    Close,
    High,
    Low,
    Volume,
    Market_cap__intra_day_,
    Enterprise_value,
    Trailing_PE,
    Forward_PE,
    PEG_Ratio__5_yr_expected_,
    Pricesales__ttm_,
    Pricebook__mrq_
    Enterprise_valuerevenue,
    Enterprise_valueEBITDA,
    Beta__5Y_monthly_,
    _52_week_change_3,
    SnP_500__52_week_change_3,
    _52_week_high_3,
    _52_week_low_3,
    _50_day_moving_average_3,
    _200_day_moving_average_3,
    Avg_vol__3_month__3,
    Avg_vol__10_day__3,
    Shares_outstanding_5,
    Implied_shares_outstanding_6,
    Float_8,
    percent_held_by_insiders_1,
    percent_held_by_institutions_1,
    Shares_short_4,
    Short_ratio_4,
    Short_percent_of_float_4,
    Short_percent_of_shares_outstanding_4,
    Shares_short__prior_month___4,
    Forward_annual_dividend_rate_4,
    Forward_annual_dividend_yield_4,
    Trailing_annual_dividend_rate_3,
    Trailing_annual_dividend_yield_3,
    _5_year_average_dividend_yield_4,
    Payout_ratio_4,
    Last_split_factor_2,
    Profit_margin,
    Operating_margin__ttm_,
    Return_on_assets__ttm_,
    Return_on_equity__ttm_,
    Revenue__ttm_,
    Revenue_per_share__ttm_,
    Quarterly_revenue_growth__yoy_,
    Gross_profit__ttm_,
    EBITDA,
    Net_income_avi_to_common__ttm_,
    Diluted_EPS__ttm_,
    Quarterly_earnings_growth__yoy_,
    Total_cash__mrq_,
    Total_cash_per_share__mrq_,
    Total_debt__mrq_,
    Total_debtequity__mrq_,
    Current_ratio__mrq_,
    Book_value_per_share__mrq_,
    Operating_cash_flow__ttm_,
    Levered_free_cash_flow__ttm_
FROM
    stock_financials_today stock_financials
FULL OUTER JOIN
    stock_prices_today stock_prices
ON
    stock_financials.Ticker = CONCAT(stock_prices.Stock_Code, '.SI')


text:
INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Fred', 'McDonald', 39, '2002-09-20 00:00:00', 36.7200, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Michelle', 'Johnson', 35, '2004-03-05 00:00:00', 7.2700, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Paul', 'Rapaport', 50, '1998-09-29 00:00:00', 6.4800, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'William', 'Gellar', 22, '2004-02-24 00:00:00', 11.3600, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Sarah', 'Jones', 36, '1998-09-18 00:00:00', 10.5700, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Sally', 'Johnson', 16, '2000-04-12 00:00:00', 9.7700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'Fred', 'Costner', 23, '1998-09-08 00:00:00', 14.6500, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Sally', 'Gellar', 37, '2000-04-02 00:00:00', 13.8600, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'John', 'Jones', 17, '2001-10-26 00:00:00', 13.0700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Paul', 'Vincent', 24, '2000-03-23 00:00:00', 17.9500, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'John', 'Costner', 39, '2001-10-16 00:00:00', 17.1500, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Sarah', 'Johnson', 46, '2000-03-13 00:00:00', 22.0300, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'David', 'Rapaport', 25, '2001-10-06 00:00:00', 21.2400, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Mary', 'Doe', 40, '2003-05-01 00:00:00', 20.4500, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Sally', 'Jones', 47, '2001-09-26 00:00:00', 25.3300, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Jane', 'McDonald', 26, '2003-04-21 00:00:00', 24.5300, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'Michelle', 'Rapaport', 41, '2004-11-13 00:00:00', 23.7400, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'John', 'Douglas', 48, '2003-04-11 00:00:00', 28.6200, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'William', 'Jones', 27, '2004-11-03 00:00:00', 27.8300, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'David', 'Doe', 34, '2003-04-01 00:00:00', 32.7100, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'William', 'Costner', 49, '2004-10-24 00:00:00', 31.9200, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Fred', 'Smith', 28, '1999-05-20 00:00:00', 31.1200, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Jane', 'Rapaport', 36, '2004-10-14 00:00:00', 36.0000, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'Michelle', 'Doe', 15, '1999-05-10 00:00:00', 35.2100, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Paul', 'Gellar', 30, '2000-12-02 00:00:00', 34.4200, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Michelle', 'McDonald', 37, '1999-04-30 00:00:00', 39.3000, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Sarah', 'Vincent', 16, '2000-11-22 00:00:00', 38.5000, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'William', 'Smith', 23, '1999-04-20 00:00:00', 43.3800, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Fred', 'Johnson', 38, '2000-11-12 00:00:00', 42.5900, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Sally', 'McDonald', 17, '2002-06-07 00:00:00', 41.8000, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Fred', 'Gellar', 24, '2000-11-01 00:00:00', 46.6800, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Paul', 'Smith', 39, '2002-05-27 00:00:00', 45.8900, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'John', 'Johnson', 18, '2003-12-20 00:00:00', 45.0900, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Sarah', 'Doe', 25, '2002-05-17 00:00:00', 49.9700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'David', 'Gellar', 40, '2003-12-10 00:00:00', 49.1800, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Sarah', 'McDonald', 47, '2002-05-07 00:00:00', 9.0600, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'Sally', 'Vincent', 27, '2003-11-30 00:00:00', 8.2700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Mary', 'Costner', 41, '1998-06-25 00:00:00', 7.4700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Paul', 'Johnson', 48, '2003-11-20 00:00:00', 12.3500, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'John', 'Rapaport', 28, '1998-06-15 00:00:00', 11.5600, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Michelle', 'Vincent', 42, '2000-01-08 00:00:00', 10.7700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'David', 'Jones', 49, '1998-06-05 00:00:00', 15.6500, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'William', 'Johnson', 29, '1999-12-29 00:00:00', 14.8500, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'David', 'Costner', 36, '1998-05-26 00:00:00', 19.7400, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Mary', 'Gellar', 15, '1999-12-19 00:00:00', 18.9400, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Fred', 'Jones', 30, '2001-07-13 00:00:00', 18.1500, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'John', 'Doe', 37, '1999-12-09 00:00:00', 23.0300, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Paul', 'Douglas', 31, '2003-01-26 00:00:00', 21.4400, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'William', 'Rapaport', 38, '2001-06-23 00:00:00', 26.3200, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Sarah', 'Doe', 18, '2003-01-16 00:00:00', 25.5300, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Mary', 'Jones', 25, '2001-06-13 00:00:00', 30.4100, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Fred', 'McDonald', 39, '2003-01-06 00:00:00', 29.6200, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'Sally', 'Vincent', 19, '2004-07-31 00:00:00', 28.8200, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Michelle', 'Douglas', 26, '2002-12-27 00:00:00', 33.7000, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Paul', 'Johnson', 40, '2004-07-21 00:00:00', 32.9100, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'John', 'McDonald', 20, '1999-02-13 00:00:00', 32.1200, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Sarah', 'Gellar', 27, '2004-07-10 00:00:00', 37.0000, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'David', 'Smith', 41, '1999-02-03 00:00:00', 36.2000, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Fred', 'Vincent', 48, '2004-06-30 00:00:00', 41.0900, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Sally', 'Doe', 28, '1999-01-24 00:00:00', 40.2900, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Jane', 'Gellar', 43, '2000-08-18 00:00:00', 39.5000, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Paul', 'McDonald', 50, '1999-01-14 00:00:00', 44.3800, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'John', 'Vincent', 29, '2000-08-08 00:00:00', 43.5900, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Michelle', 'Costner', 44, '2002-03-03 00:00:00', 42.7900, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'David', 'Johnson', 16, '2000-07-29 00:00:00', 47.6700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'William', 'Rapaport', 30, '2002-02-21 00:00:00', 46.8800, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Sally', 'Gellar', 37, '2000-07-19 00:00:00', 6.7600, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Jane', 'Jones', 17, '2002-02-11 00:00:00', 5.9700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Fred', 'Johnson', 31, '2003-09-06 00:00:00', 5.1700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'John', 'Costner', 38, '2002-02-01 00:00:00', 10.0500, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Michelle', 'Gellar', 18, '2003-08-27 00:00:00', 9.2600, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Paul', 'Jones', 32, '1998-03-22 00:00:00', 8.4700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'William', 'Vincent', 39, '2003-08-17 00:00:00', 13.3500, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Sarah', 'Costner', 19, '1998-03-12 00:00:00', 12.5500, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Mary', 'Johnson', 26, '2003-08-07 00:00:00', 17.4400, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Fred', 'Rapaport', 41, '1998-03-02 00:00:00', 16.6400, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Sally', 'Doe', 20, '1999-09-25 00:00:00', 15.8500, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Michelle', 'Jones', 27, '1998-02-20 00:00:00', 20.7300, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Paul', 'McDonald', 42, '1999-09-15 00:00:00', 19.9400, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'John', 'Rapaport', 21, '2001-04-09 00:00:00', 19.1400, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Sarah', 'Douglas', 28, '1999-09-05 00:00:00', 24.0200, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'David', 'Jones', 43, '2001-03-29 00:00:00', 23.2300, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Fred', 'Doe', 50, '1999-08-25 00:00:00', 28.1100, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Sally', 'Costner', 29, '2001-03-19 00:00:00', 27.3200, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Jane', 'Douglas', 44, '2002-10-12 00:00:00', 26.5200, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'Paul', 'Rapaport', 16, '2001-03-09 00:00:00', 31.4000, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'John', 'Doe', 31, '2002-10-02 00:00:00', 30.6100, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'William', 'Gellar', 45, '2004-04-26 00:00:00', 29.8200, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'David', 'McDonald', 17, '2002-09-22 00:00:00', 34.7000, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'William', 'Vincent', 32, '2004-04-16 00:00:00', 33.9100, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Sally', 'Smith', 39, '2002-09-12 00:00:00', 38.7900, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Jane', 'Johnson', 18, '2004-04-06 00:00:00', 37.9900, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Michelle', 'McDonald', 33, '1998-10-31 00:00:00', 37.2000, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'John', 'Gellar', 40, '2004-03-27 00:00:00', 42.0800, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'Michelle', 'Smith', 19, '1998-10-21 00:00:00', 41.2900, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (5, 'Sarah', 'Johnson', 34, '2000-05-15 00:00:00', 40.4900, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (4, 'William', 'Doe', 41, '1998-10-11 00:00:00', 45.3700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (2, 'Fred', 'Gellar', 20, '2000-05-05 00:00:00', 44.5800, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (1, 'Jane', 'McDonald', 28, '1998-10-01 00:00:00', 49.4600, 0 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (3, 'Fred', 'Vincent', 42, '2000-04-25 00:00:00', 48.6700, 1 );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL );

INSERT INTO AggregateTest (DepartmentID, FirstName, LastName, Age, HireDate, Salary, IsActive )
 VALUES (0, '', '', 0, NULL, 0, NULL );

domain: sql


text:
-- phpMyAdmin SQL Dump
-- version 4.9.0.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Sep 16, 2019 at 07:06 AM
-- Server version: 10.4.6-MariaDB
-- PHP Version: 7.3.9

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `project`
--

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

--
-- Table structure for table `ad`
--

CREATE TABLE `ad` (
  `id` int(10) NOT NULL,
  `images` varchar(255) NOT NULL,
  `text` varchar(255) NOT NULL,
  `price` int(20) NOT NULL,
  `user` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ad`
--

INSERT INTO `ad` (`id`, `images`, `text`, `price`, `user`) VALUES
(1, '71WrHs7H1vL._SY355_.jpg', 'Nvidia GTX 1050 TI', 23000, ''),
(2, '1060.jpg', 'Nvidia GeForce GTX 1060', 36000, ''),
(3, '71Nubg808BL._AC_SR300,300_.jpg', 'Comfy Slippers', 1500, ''),
(4, 'earbuds.jpg', 'Wireless Earbuds with Charging Case', 8000, ''),
(5, '44-alu-space-sport-black-nc-s4-1up.png', 'The new Apple Watch', 55000, ''),
(9, 'USD00TOC041B021001.jpg', 'The new Toyota Corolla ', 3000000, '1'),
(11, '71V2q-2gXXL._SX679_.jpg', 'Kisses Chocolate Party Pack', 4000, '1');

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

--
-- Table structure for table `admin`
--

CREATE TABLE `admin` (
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `admin`
--

INSERT INTO `admin` (`username`, `password`) VALUES
('admin', 'admin');

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

--
-- Table structure for table `login`
--

CREATE TABLE `login` (
  `id` int(20) NOT NULL,
  `username` varchar(256) NOT NULL,
  `password` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `login`
--

INSERT INTO `login` (`id`, `username`, `password`) VALUES
(6, '1', '1');

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

--
-- Table structure for table `registeration`
--

CREATE TABLE `registeration` (
  `name` varchar(256) NOT NULL,
  `username` varchar(256) NOT NULL,
  `dob` varchar(20) NOT NULL,
  `email` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `registeration`
--

INSERT INTO `registeration` (`name`, `username`, `dob`, `email`, `password`) VALUES
('1', '1', '1', '1', '1');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ad`
--
ALTER TABLE `ad`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `login`
--
ALTER TABLE `login`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id` (`id`);

--
-- Indexes for table `registeration`
--
ALTER TABLE `registeration`
  ADD PRIMARY KEY (`name`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `ad`
--
ALTER TABLE `ad`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `login`
--
ALTER TABLE `login`
  MODIFY `id` int(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


text:
-- FUNCTION: _meta.sanitize(text, text, text)
-- DROP FUNCTION _meta.sanitize(text, text, text);
CREATE OR REPLACE FUNCTION _meta.sanitize(
	type text,
	value text,
	parameter text)
    RETURNS text
    LANGUAGE 'plv8'

    COST 100
    VOLATILE
AS $BODY$
  // init plv8 require
  plv8.execute( 'SELECT _meta.plv8_require();' );

  var validator = require('validator');
  // parse if parameter is json
  try {
    parameter = JSON.parse(parameter);
  } catch (e) {}

  return validator[type](value, parameter);

$BODY$;


text:
use inventory;

DROP TABLE IF EXISTS tb_ItemType;

-- inventory item type mapping
CREATE TABLE tb_ItemType
(

    -- mservice account identifier
    inbMserviceId BIGINT NOT NULL,
    -- inventory item type identifier
    intItemTypeId INT NOT NULL,
    -- creation date
    dtmCreated DATETIME NOT NULL,
    -- modification date
    dtmModified DATETIME NOT NULL,
    -- deletion date
    dtmDeleted DATETIME NOT NULL,
    -- has record been deleted?
    bitIsDeleted BOOL NOT NULL,
    -- version of this record
    intVersion INT NOT NULL,
    -- item type name
    chvItemTypeName VARCHAR(255) NOT NULL,

    PRIMARY KEY (inbMserviceId,intItemTypeId)
) ENGINE=InnoDB;


text:
BEGIN;

CREATE TYPE connection_direction AS ENUM (
    'UNKNOWN',
    'OUTBOUND',
    'INBOUND'
    );

CREATE TABLE connection_events
(
    id                             INT GENERATED ALWAYS AS IDENTITY,
    local_id                       BIGINT               NOT NULL,
    remote_id                      BIGINT               NOT NULL,
    connection_multi_address_id    BIGINT               NOT NULL,
    direction                      connection_direction NOT NULL,
    listens_on_relay_multi_address BOOLEAN              NOT NULL,
    supports_dcutr                 BOOLEAN              NOT NULL,
    opened_at                      TIMESTAMPTZ          NOT NULL,
    created_at                     TIMESTAMPTZ          NOT NULL,

    CONSTRAINT fk_connection_events_local_id FOREIGN KEY (local_id) REFERENCES peers (id) ON DELETE CASCADE,
    CONSTRAINT fk_connection_events_remote_id FOREIGN KEY (remote_id) REFERENCES peers (id) ON DELETE CASCADE,
    CONSTRAINT fk_connection_events_multi_address_id FOREIGN KEY (connection_multi_address_id) REFERENCES multi_addresses (id) ON DELETE CASCADE,

    PRIMARY KEY (id)
);

CREATE INDEX idx_connection_events_opened_at ON connection_events (opened_at);

-- End the transaction
COMMIT;


text:
USE AdventureworksDW2016CTP3
GO
SET STATISTICS TIME ON

SELECT P.EnglishProductName, F.OrderDate, sum(F.SalesAmount)
FROM FactResellerSalesXL_CI F
INNER JOIN DimProduct P on P.ProductKey = F.ProductKey
WHERE OrderDate BETWEEN '2011-01-01' and '2013-01-01'
GROUP BY P.EnglishProductName, F.OrderDate
ORDER BY Sum(F.SalesAmount) DESC

CREATE INDEX NCI_FactResellerSalesXL_SalesAmount ON FactResellerSalesXL_CI (SalesAmount, orderquantity)
CREATE INDEX NCI_FactResellerSalesXL_Covering ON FactResellerSalesXL_CI (ProductKey,OrderDate)
INCLUDE (salesAmount, orderquantity)


text:
CREATE TABLE test (path ltree);
INSERT INTO test VALUES ('Top');
INSERT INTO test VALUES ('Top.Science');
INSERT INTO test VALUES ('Top.Science.Astronomy');
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO test VALUES ('Top.Hobbies');
INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO test VALUES ('Top.Collections');
INSERT INTO test VALUES ('Top.Collections.Pictures');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);


text:
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--

create table standard_migration_location (value text);

insert into standard_migration_location (value) values ('inserted in migration script in the default location');


text:
SELECT Gerbang,
	SUM(CASE WHEN Metoda LIKE 'eToll%' AND Shift = 1 THEN Rupiah ELSE 0 END) AS eToll_shift1,
	SUM(CASE WHEN Metoda LIKE '%Tunai%' AND Shift = 1 THEN Rupiah ELSE 0 END) AS Tunai_shift1,
	SUM(CASE WHEN Metoda LIKE 'eToll%' AND Shift = 2 THEN Rupiah ELSE 0 END) AS eToll_shift2,
	SUM(CASE WHEN Metoda LIKE '%Tunai%' AND Shift = 2 THEN Rupiah ELSE 0 END) AS Tunai_shift2,
	SUM(CASE WHEN Metoda LIKE 'eToll%' AND Shift = 3 THEN Rupiah ELSE 0 END) AS eToll_shift3,
	SUM(CASE WHEN Metoda LIKE '%Tunai%' AND Shift = 3 THEN Rupiah ELSE 0 END) AS Tunai_shift3,
	SUM(CASE WHEN Metoda LIKE '%eToll%' THEN Rupiah ELSE 0 END) AS Total_eToll,
	SUM(CASE WHEN Metoda LIKE '%Tunai%' THEN Rupiah ELSE 0 END) AS Total_Tunai
FROM lalin
WHERE waktu >= '2018-07-15T00:00:00' AND waktu < '2018-07-16T00:00:00'
GROUP BY Gerbang


text:
/*

Most Profitable Companies
https://platform.stratascratch.com/coding/9680-most-profitable-companies?python=

Difficulty: Hard

Find the 3 most profitable companies in the entire world.
Output the result along with the corresponding company name.
Sort the result based on profits in descending order.

Tables:
forbes_global_2010_2014
company             varchar
sector              varchar
industry            varchar
continent           varchar
country             varchar
marketvalue         float
sales               float
profits             float
assets              float
rank                int
forbeswebpage       varchar

*/

-- objective:
-- FIND TOP 3 most profitable companies

-- assumption:
-- company can have same profits

-- output:
-- company name, profits
-- ORDER BY profits DESC

SELECT company,
       profits
FROM (
    SELECT company,
           profits,
           DENSE_RANK() OVER(ORDER BY profits DESC) profit_rank
    FROM forbes_global_2010_2014
) rank_profits
WHERE profit_rank <= 3


text:
INSERT INTO tweb_apbd(`lembaga_id`, `lembaga_kode`, `wilayah_kode`,`tahun`, `rekening_kode`, `uraian`, `nominal_sebelum`, `nominal_sesudah`, `nominal_perubahan`, `nominal_persen`, `keterangan`, `created_by`, `created_at`, `updated_by`, `updated_at` ) VALUES
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.00.00.5.','BELANJA DAERAH','759,243,300,538.61','643,354,439,664.00','(115,888,860,874.61)','(15.26)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.00.00.5.1.','BELANJA TIDAK LANGSUNG','642,912,497,638.61','583,401,839,096.00','(59,510,658,542.61)','(9.26)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.00.00.5.1.1.','BELANJA PEGAWAI','642,912,497,638.61','583,401,839,096.00','(59,510,658,542.61)','(9.26)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.00.00.5.1.2.','BELANJA LANGSUNG','116,330,802,900.00','59,952,600,568.00','(56,378,202,332.00)','(48.46)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.','PROGRAM PELAYANAN ADMINISTRASI PERKANTORAN','18,016,495,000.00','14,880,181,422.00','(3,136,313,578.00)','(17.41)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.01.','PENYEDIAAN JASA SURAT MENYURAT','97,613,000.00','88,456,900.00','(9,156,100.00)','(9.38)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.01.5.2.2.','BELANJA BARANG DAN JASA','97,613,000.00','88,456,900.00','(9,156,100.00)','(9.38)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.02','PENYEDIAAN JASA KOMUNKASI, SUMBER DAYA AIR DAN LISTRIK','2,085,721,000.00','1,616,168,561.00','(469,552,439.00)','(22.51)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.02.5.5.2.','BELANJA BARANG DAN JASA','2,085,721,000.00','1,616,168,561.00','(469,552,439.00)','(22.51)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.03.','PENYEDIAAN JASA PERALATAN DAN PERLENGKAPAN KANTOR','17,550,000.00','16,250,000.00','(1,300,000.00)','(7.41)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.03.5.5.2.','BELANJA BARANG DAN JASA','17,550,000.00','16,250,000.00','(1,300,000.00)','(7.41)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.06.','PENYEDIAAN JASA PEMELIHARAAN DAN PERIZINAN KENDARAAN DINAS/OPERASIONAL','17,806,000.00','14,062,100.00','(3,743,900.00)','(21.03)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.06.5.5.2','BELANJA BARANG DAN JASA','17,806,000.00','14,062,100.00','(3,743,900.00)','(21.03)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.07','PENYEDIAAN JASA ADMNISTRASI KEUANGAN','663,300,000.00','621,975,000.00','(41,325,000.00)','(6.23)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.07.5.2.1.','BELANJA PEGAWAI','663,300,000.00','621,975,000.00','(41,325,000.00)','(6.23)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.08.','PENYEDIAAN JASA KEBERSIHAN KANTOR','140,304,000.00','128,260,000.00','(12,044,000.00)','(8.58)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.08.5.2.2.','BELANJA BARANG DAN JASA','140,304,000.00','128,260,000.00','(12,044,000.00)','(8.58)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.09.','PENYEDIAAN JASA PERBAIKAN PERALATAN KERJA','533,822,500.00','458,946,250.00','(74,876,250.00)','(14.03)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.09.5.2.1.','BELANJA PEGAWAI','2,688,000.00','1,344,000.00','(1,344,000.00)','(50.00)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.09.5.2.2.','BELANJA BARANG DAN JASA','531,134,000.00','457,602,250.00','(73,531,750.00)','(13.84)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.10.','PENYEDIAAN ALAT TULIS KANTOR','1,300,070,750.00','1,262,065,342.00','(38,005,408.00)','(2.92)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.10.5.2.2.','BELANJA BARANG DAN JASA','1,300,070,750.00','1,262,065,342.00','(38,005,408.00)','(2.92)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.11.','PENYEDIAANBARANG CETAKAN DAN PENGGANDAAN','641,136,250.00','585,339,732.00','(55,796,518.00)','(8.70)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.11.5.2.2.','BELANJA BARANG DAN JASA','641,136,250.00','585,339,732.00','(55,796,518.00)','(8.70)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.12.','PENYEDIAAN KOMPONEN INSTALASI LISTRIK/PENERANGAN BANGUNAN KANTOR','352,668,000.00','342,094,330.00','(10,573,670.00)','(3.00)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.12.5.2.2.','BELANJA BARANG DAN JASA','352,668,000.00','342,094,330.00','(10,573,670.00)','(3.00)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.13.','PENYEDIAAN PERALATAN DAN PERLENGKAPAN KANTOR','1,732,062,000.00','1,684,842,250.00','(47,219,750.00)','(2.73)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.13.5.2.1.','BELANJA PEGAWAI','1,650,000.00','900,000.00','(750,000.00)','(45.45)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.13.5.2.2.','BELANJA BARANG DAN JASA','60,910,000.00','60,438,250.00','(471,750.00)','(0.77)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.13.5.2.3.','BELANJA MODAL','1,669,502,000.00','1,623,504,000.00','(45,998,000.00)','(2.76)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.14.','PENYEDIAAN PERALATAN RUMAH TANGGA','406,427,500.00','394,236,195.00','(12,191,305.00)','(3.00)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.14.5.2.2.','BELANJA BARANG DAN JASA','397,277,500.00','385,526,195.00','(11,751,305.00)','(2.96)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.14.5.2.3.','BELANJA MODAL','9,150,000.00','8,710,000.00','(440,000.00)','(4.81)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.15.','PENYEDIAAN BAHAN BACAAN DAN PERATURAN PERUNDANG-UNDANGAN','198,345,000.00','165,735,000.00','(32,610,000.00)','(16.44)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.15.5.2.2.','BELANJA BARANG DAN JASA','176,680,000.00','154,525,000.00','(22,155,000.00)','(12.54)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.15.5.2.3.','BELANJA MODAL','21,665,000.00','11,210,000.00','(10,455,000.00)','(48.26)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.17.','PENYEDIAAN MAKANAN DAN MINUMAN','389,659,000.00','300,678,500.00','(88,980,500.00)','(22.84)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.17.5.2.2.','BELANJA BARANG DAN JASA','389,659,000.00','300,678,500.00','(88,980,500.00)','(22.84)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.18.','RAPAT-RAPAT KOORDINASI DAN KONSULTASI KE LUAR DAERAH','527,010,000.00','366,871,262.00','(160,138,738.00)','(30.39)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.18.5.2.2.','BELANJA BARANG DAN JASA','527,010,000.00','366,871,262.00','(160,138,738.00)','(30.39)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.19.','PENYEDIAAN JASA TENAGA ADMINISTRASI/TEKNIK PERKANTORAN','8,913,000,000.00','6,834,200,000.00','(2,078,800,000.00)','(23.32)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.01.19.5.2.1.','BELANJA PEGAWAI','8,913,000,000.00','6,834,200,000.00','(2,078,800,000.00)','(23.32)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.','PROGRAM PENINGKATAN SARANAN DAN PRASARANA APARATUR','3,235,019,400.00','2,973,845,325.00','(261,174,075.00)','(8.07)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.03.','PEMBANGUNAN GEDUNG KANTOR','523,700,000.00','436,197,000.00','(87,503,000.00)','(16.71)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.03.5.2.3','BELANJA MODAL','523,700,000.00','436,197,000.00','(87,503,000.00)','(16.71)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.07.','PENGADAAN PERLENGKAPAN GEDUNG KANTOR','493,557,000.00','449,442,900.00','(44,114,100.00)','(8.94)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.07.5.2.2.','BELANJA BARANG DAN JASA','9,000,000.00','9,000,000.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.07.5.2.3.','BELANJA MODAL','484,557,000.00','440,442,900.00','(44,114,100.00)','(9.10)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.10.','PENGADAAN MEBELAIR','742,736,400.00','692,343,500.00','(50,392,900.00)','(6.78)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.10.5.2..3.','BELANJA MODAL','742,736,400.00','692,343,500.00','(50,392,900.00)','(6.78)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.22.','PEMELIHARAAN RUTIN/BERKALA GEDUNG KANTOR','713,082,000.00','693,545,800.00','(19,536,200.00)','(2.74)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.22.5.2.1.','BELANJA PEGAWAII','117,637,000.00','108,087,000.00','(9,550,000.00)','(8.12)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.22.5.2.2.','BELANJA BARANG DAN JASA','595,445,000.00','585,458,800.00','(9,986,200.00)','(1.68)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.24.','PEMELIHARAAN RUTIN/BERKALA KENDARAAN DINAS/OPERASIONAL','228,625,000.00','180,725,125.00','(47,899,875.00)','(20.95)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.24.5.2.2.','BELANJA BARANG DAN JASA','228,625,000.00','180,725,125.00','(47,899,875.00)','(20.95)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.26.','PEMELIHARAAN RUTIN /BERKALA PERLENGKAPAN GEDUNG KANTOR','14,250,000.00','8,565,000.00','(5,685,000.00)','(39.89)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.26.5.2.2.','BELANJA BARANG DAN JASA','14,250,000.00','8,565,000.00','(5,685,000.00)','(39.89)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.29.','PEMELIHARAAN RUTIN/BERKALA MEBELAIR','96,269,000.00','95,213,000.00','(1,056,000.00)','(1.10)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.29.5.2.1.','BELANJA PEGAWAI','12,054,000.00','11,390,000.00','(664,000.00)','(5.51)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.29.5.2.2.','BELANJA BARANG DAN JASA','84,215,000.00','83,823,000.00','(392,000.00)','(0.47)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.42.','REHABILITASI SEDANGNERAT GEDIG LAMTPR','422,800,000.00','417,813,000.00','(4,987,000.00)','(1.18)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.02.42.5.2.3.','BELANJA MODAL','422,800,000.00','417,813,000.00','(4,987,000.00)','(1.18)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.03.','PROGRAM PENINGKATAN DISIPLIN APARATUR','5,000,000.00','3,602,640.00','(1,397,360.00)','(27.95)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.03.01.','PENGADAAN MESIN/KARTU ABSENSI','5,000,000.00','3,602,640.00','(1,397,360.00)','(27.95)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.03.01.5.2.3.','BELANJA MODAL','5,000,000.00','3,602,640.00','(1,397,360.00)','(27.95)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.05.','PROGRAM PENINGKATAN KAPASITAS SUMBER DAYA APARATUR','225,790,000.00','206,439,700.00','(19,350,300.00)','(8.57)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.05.01.','PENDIDIKAN DAN PELATIHAN FORMAL','3,760,000.00','','(3,760,000.00)','(100.00)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.05.01.5.2.1.','BELANJA PEGAWAI','3,285,000.00','','(3,285,000.00)','(100.00)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.05.01.5.2.2.','BELANJA BARANG DAN JASA','475,000.00','','(475,000.00)','(100.00)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.05.02.','SOSIALISASI PERATURAN PERUNDANG-UNDANGAN','4,905,000.00','','(4,905,000.00)','(100.00)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.05.02.5.2.1.','BELANJA PEGAWAI','635,000.00','','(635,000.00)','(100.00)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.05.02.5.2.1.','BELANJA BARANG DAN JASA','4,270,000.00','','(4,270,000.00)','(100.00)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.05.03.','PEMBIMBINGAN TEKNIS IMPLEMENTASI PERATURAN PERUNDANG-UNDANGAN','217,125,000.00','206,439,700.00','(10,685,300.00)','(4.92)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.05.03.5.2.1.','BELANJA PEGAWAI','11,095,000.00','10,095,000.00','(1,000,000.00)','(9.01)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.05.03.5.2.2.','BELANJA BARANG DAN JASA','206,030,000.00','196,344,700.00','(9,685,300.00)','(4.70)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.','PROGRAM PENINGKATAN PENGEMBANGN SISTEM PELAPORAN CAPAIAN KINERJA DAN KEUANGAN','202,584,000.00','153,836,751.00','(48,747,249.00)','(24.06)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.01.','PENYUSUNAN LAPORAN CAPAIAN KINERJA DAN IKHTISAR REALISASI KINERJA SKPD','116,749,000.00','71,789,600.00','(44,959,400.00)','(38.51)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.01.5.2.1.','BELANJA PEGAWAI','28,970,000.00','21,090,000.00','(7,880,000.00)','(27.20)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.01.5.2.2.','BELANJA BARANG DAN JASA','87,779,000.00','50,699,600.00','(37,079,400.00)','(42.24)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.02.','PENYUSUNAN PELAPORAN KEUANGAN SEMESTERAN','9,730,000.00','9,730,000.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.02.5.2.1.','BELANJA PEGAWAI','1,120,000.00','1,120,000.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.02.5.2.2.','BELANJA BARANG DAN JASA','8,610,000.00','8,610,000.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.04.','PENYUSUNAN PELAPORAN KEUANGAN AKHIR TAHUN','7,630,000.00','7,630,000.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.04.5.2.1.','BELANJA PEGAWAI','3,595,000.00','3,595,000.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.04.5.2.2.','BELANJA BARANG DAN JASA','4,035,000.00','4,035,000.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.05.','PENYUSUNAN LPELAPORAN KEUANGAN BULANAN/SPJ','68,475,000.00','64,687,151.00','(3,787,849.00)','(5.53)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.04.5.2.1.','BELANJA PEGAWAI','14,000,000.00','13,770,000.00','(230,000.00)','(1.64)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.06.04.5.2.2.','BELANJA BARANG DAN JASA','54,475,000.00','50,917,151.00','(3,557,849.00)','(6.53)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.','PROGRAM PENINGKATAN KUALITAS PELAYANAN PUBLIK','1,222,587,500.00','1,047,825,300.00','(174,762,200.00)','(14.29)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.01.','PENGELO9LAAN DATA DAN SISTEM INFORMASI SKPD','439,427,500.00','401,328,600.00','(38,098,900.00)','(8.67)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.5.2.1.','BELANJA PEGAWAI','23,350,000.00','23,150,000.00','(200,000.00)','(0.86)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.5.2.2.','BELANJA BARANG DAN JASA','297,677,500.00','276,616,700.00','(21,060,800.00)','(7.08)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.5.2.3.','BELANJA MODAL','118,400,000.00','101,561,900.00','(16,838,100.00)','(14.22)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.02.','PENYUSUNAN RENCANA STRATEGIS SKPD','96,790,500.00','96,790,500.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.5.2.1.','BELANJA PEGAWAI','23,180,000.00','19,980,000.00','(3,200,000.00)','(13.81)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.5.2.2.','BELANJA BARANG DAN JASA','87,932,500.00','76,810,500.00','(11,122,000.00)','(12.65)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.03.','PENYUSUNAN RENCANA KERJA SKPD','302,696,000.00','274,417,500.00','(28,278,500.00)','(9.34)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.03.5.2.1.','BELANJA PEGAWAI','128,632,500.00','123,067,500.00','(5,565,000.00)','(4.33)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.03.5.2.2.','BELANJA BARANG DAN JASA','174,063,500.00','151,350,000.00','(22,713,500.00)','(13.05)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.05.','MONINTORING, EVALUASI, DAN PENGENDALIAN PROGRAM KEGIATAN SKPD','186,930,000.00','114,707,500.00','(72,222,500.00)','(38.64)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.05.5.2.1.','BELANJA PEGAWAI','9,225,000.00','9,225,000.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.05.5.2.2.','BELANJA BARANG DAN JASA','177,705,000.00','105,482,500.00','(72,222,500.00)','(40.64)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.06.','PENYELENGARAAN FORUM SKPD','12,200,000.00','10,055,000.00','(2,145,000.00)','(17.58)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.06.5.2.1.','BELANJA PEGAWAI','700,000.00','700,000.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.06.5.2.2.','BELANJA BARANG DAN JASA','11,500,000.00','9,355,000.00','(2,145,000.00)','(18.65)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.16.','PENYUSUNAN STANDARTD OPERATION PROCEDURE (SOP)','54,855,000.00','48,834,500.00','(6,020,500.00)','(10.98)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.16.5.2.1.','BELANJA PEGAWAI','18,155,000.00','15,955,000.00','(2,200,000.00)','(12.12)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.16.5.2.2.','BELANJA BARANG DAN JASA','36,700,000.00','32,879,500.00','(3,820,500.00)','(10.41)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.17.','PENYUSUNAN INDEKS KEPUASAN MASYARAKAT (IKM) SKPD','69,220,500.00','58,255,700.00','(10,964,800.00)','(15.84)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.17.5.2.1.','BELANJA PEGWAI','20,105,000.00','18,140,000.00','(1,965,000.00)','(9.77)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.17.5.2.2.','BELANJA BARANG DAN JASA','49,115,500.00','40,115,700.00','(8,999,800.00)','(18.32)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.18.','EVALUASI STANDAR PELAYANAN MINIMAL (SPM)SKPD','46,146,000.00','43,436,000.00','(2,710,000.00)','(5.87)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.18.5.2.1.','BELANJA PEGWAI','7,637,500.00','7,637,500.00','0.00','0.00',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.09.18.5.2.2.','BELANJA BARANG DAN JASA','38,508,500.00','35,798,500.00','(2,710,000.00)','(7.04)',''),
('5','\"1.01.01. - Dinas Pendidikan','3403','2014','1.01.1.01.01.15.','PROGRAM PENDIDIKAN ANAK USIA DINI','5,288,233,500.00','5,092,175,868.00','(196,057,632.00)','(3.71)',''),


text:
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

CREATE SCHEMA consent;

GRANT ALL ON SCHEMA consent to postgres;
GRANT USAGE ON SCHEMA consent TO auth;

CREATE TYPE consent.status_enum AS ENUM ('rejected', 'pending', 'approved');
CREATE TYPE consent.role_enum 	AS ENUM ('consumer', 'data ingester', 'onboarder', 'delegate', 'provider', 'admin');
CREATE TYPE consent.access_item AS ENUM ('resourcegroup', 'catalogue', 'provider-caps');
CREATE TYPE consent.capability_enum AS ENUM ('temporal', 'complex', 'subscription', 'download');
CREATE TYPE consent.access_status_enum AS ENUM ('active', 'deleted');
CREATE TYPE consent.token_status_enum AS ENUM ('active', 'deleted');
-- If policy deleted by provider, then token_access status is set to revoked
-- If consumer removes resource from token, then token_access status is deleted
CREATE TYPE consent.token_access_status_enum AS ENUM ('active', 'deleted','revoked');

CREATE TABLE consent.organizations (

	id		integer GENERATED ALWAYS AS IDENTITY	PRIMARY KEY,
	name		character varying			NOT NULL,
	website 	character varying			NOT NULL,
	city		character varying			NOT NULL,
	state		character varying(2)			NOT NULL,
	country		character varying(2)			NOT NULL,
	created_at	timestamp without time zone		NOT NULL,
	updated_at	timestamp without time zone		NOT NULL
);

CREATE UNIQUE INDEX idx_organizations_website ON consent.organizations(website);

CREATE TABLE consent.users (

	id		integer GENERATED ALWAYS AS IDENTITY		PRIMARY KEY,
	title		character varying				NOT NULL,
	first_name 	character varying				NOT NULL,
	last_name	character varying				NOT NULL,
	email		character varying				NOT NULL,
	phone		character varying(10)				NOT NULL,
	organization_id	integer REFERENCES consent.organizations(id)		,
	created_at	timestamp without time zone			NOT NULL,
	updated_at	timestamp without time zone			NOT NULL
);

CREATE UNIQUE INDEX idx_users_email ON consent.users(email);

CREATE TABLE consent.role (

	id		integer GENERATED ALWAYS AS IDENTITY		PRIMARY KEY,
	user_id		integer NOT NULL REFERENCES consent.users(id)	ON DELETE CASCADE,
	role		consent.role_enum				NOT NULL,
	status		consent.status_enum				NOT NULL,
	created_at	timestamp without time zone			NOT NULL,
	updated_at	timestamp without time zone			NOT NULL
);

CREATE INDEX idx_role_user_id ON consent.role(user_id);

CREATE TABLE consent.certificates (

	id		integer GENERATED ALWAYS AS IDENTITY		PRIMARY KEY,
	user_id		integer NOT NULL REFERENCES consent.users(id)	ON DELETE CASCADE,
	csr		character varying				NOT NULL,
	cert		character varying					,
	created_at	timestamp without time zone			NOT NULL,
	updated_at	timestamp without time zone			NOT NULL
);

CREATE TABLE consent.access (

	id			integer GENERATED ALWAYS AS IDENTITY		PRIMARY KEY,
	provider_id		integer NOT NULL REFERENCES consent.users(id) 	ON DELETE CASCADE,
	role_id			integer REFERENCES consent.role(id)		ON DELETE CASCADE,
	policy_text		character varying				NOT NULL,
	policy_json		jsonb						NOT NULL,
	access_item_id		integer 						,
	access_item_type	consent.access_item					,
	expiry              	timestamp without time zone         		NOT NULL,
    	status              	consent.access_status_enum          		NOT NULL,
	created_at		timestamp without time zone			NOT NULL,
	updated_at		timestamp without time zone			NOT NULL
);

CREATE INDEX idx_access_provider_id ON consent.access(provider_id);
CREATE INDEX idx_access_role_id ON consent.access(role_id);

CREATE TABLE consent.resourcegroup (

	id			integer GENERATED ALWAYS AS IDENTITY			PRIMARY KEY,
	provider_id		integer NOT NULL REFERENCES consent.users(id) 		ON DELETE CASCADE,
	cat_id			character varying					NOT NULL,
	created_at		timestamp without time zone				NOT NULL,
	updated_at		timestamp without time zone				NOT NULL
);

CREATE INDEX idx_resourcegroup_provider_id ON consent.resourcegroup(provider_id);
CREATE UNIQUE INDEX idx_resourcegroup_cat_id ON consent.resourcegroup(cat_id);

CREATE TABLE consent.capability (

	id			integer GENERATED ALWAYS AS IDENTITY			PRIMARY KEY,
	access_id		integer NOT NULL REFERENCES consent.access(id)		ON DELETE CASCADE,
	capability		consent.capability_enum					NOT NULL,
	status          	consent.access_status_enum          			NOT NULL,
	created_at		timestamp without time zone				NOT NULL,
	updated_at		timestamp without time zone				NOT NULL,
	UNIQUE (access_id, capability)
);

CREATE INDEX idx_capability_access_id ON consent.capability(access_id);

CREATE TABLE consent.session (

	id			integer GENERATED ALWAYS AS IDENTITY		PRIMARY KEY,
	session_id		character varying(6),
	endpoints      		json,
	user_id			integer REFERENCES consent.users(id) 	ON DELETE CASCADE,
	expiry_time		timestamp without time zone				NOT NULL,
	retry_time		timestamp without time zone				NOT NULL,
	created_at		timestamp without time zone				NOT NULL,
	updated_at		timestamp without time zone				NOT NULL
);

CREATE TABLE consent.token (

	id			integer GENERATED ALWAYS AS IDENTITY		PRIMARY KEY,
	token		    	character varying                   		NOT NULL,
  	uuid			uuid 						NOT NULL,
	user_id			integer NOT NULL REFERENCES consent.users(id) ON DELETE CASCADE	,
	resource_server 	character varying                   		NOT NULL,
	expiry          	timestamp without time zone         		NOT NULL,
	status          	consent.token_status_enum           		NOT NULL,
	created_at		timestamp without time zone			NOT NULL,
	updated_at		timestamp without time zone			NOT NULL
);

CREATE INDEX idx_token_user_id ON consent.token(user_id);
CREATE INDEX idx_token ON consent.token(uuid);

CREATE TABLE consent.token_access (

	id			integer GENERATED ALWAYS AS IDENTITY	PRIMARY KEY,
	token_id		integer NOT NULL REFERENCES consent.token(id) ON DELETE CASCADE,
	access_id		integer NOT NULL REFERENCES consent.access(id) ON DELETE CASCADE,
	cat_id			character varying				NOT NULL,
    	status          	consent.token_access_status_enum    		NOT NULL,
	created_at		timestamp without time zone			NOT NULL,
	updated_at		timestamp without time zone			NOT NULL
);

CREATE INDEX idx_token_access_token_id ON consent.token_access(token_id);
CREATE INDEX idx_token_access_access_id ON consent.token_access(access_id);

ALTER TABLE consent.organizations	OWNER TO postgres;
ALTER TABLE consent.users		OWNER TO postgres;
ALTER TABLE consent.role		OWNER TO postgres;
ALTER TABLE consent.certificates	OWNER TO postgres;
ALTER TABLE consent.access		OWNER TO postgres;
ALTER TABLE consent.resourcegroup	OWNER TO postgres;
ALTER TABLE consent.capability		OWNER TO postgres;
ALTER TABLE consent.session		OWNER TO postgres;
ALTER TABLE consent.token		OWNER TO postgres;
ALTER TABLE consent.token_access	OWNER TO postgres;

GRANT SELECT,INSERT,UPDATE 		ON TABLE consent.organizations	 TO auth;
GRANT SELECT,INSERT,UPDATE,DELETE 	ON TABLE consent.users		 TO auth;
GRANT SELECT,INSERT,UPDATE 		ON TABLE consent.certificates	 TO auth;

GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE consent.role		 TO auth;
GRANT SELECT,INSERT,UPDATE ON TABLE consent.resourcegroup TO auth;
GRANT SELECT,INSERT,UPDATE ON TABLE consent.access	 TO auth;
GRANT SELECT,INSERT,UPDATE ON TABLE consent.capability	 TO auth;
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE consent.session	 TO auth;
GRANT SELECT,INSERT,UPDATE ON TABLE consent.token	 	 TO auth;
GRANT SELECT,INSERT,UPDATE ON TABLE consent.token_access	 TO auth;


text:
-- SQL to clean CISpaces database tables
-- kx1n17@soton.ac.uk

connect 'jdbc:derby://localhost:1527/gaiandb;create=true;user=gaiandb;password=passw0rd';

DELETE FROM CISPACES_EDGE;
DELETE FROM CISPACES_EDGE_HISTORY;
DELETE FROM CISPACES_EDGE_PROV;
DELETE FROM CISPACES_GRAPH;
DELETE FROM CISPACES_GRAPH_AUTHORITY;
DELETE FROM CISPACES_GRAPH_HISTORY;
DELETE FROM CISPACES_GRAPH_PROV;
DELETE FROM CISPACES_INFOPROV;
DELETE FROM CISPACES_INFOTMP;
DELETE FROM CISPACES_NODE;
DELETE FROM CISPACES_NODE_HISTORY;
DELETE FROM CISPACES_NODE_PROV;
DELETE FROM CISPACES_PROJECT;
DELETE FROM CISPACES_PROJECT_AUTHORITY;


text:
-- ----------------------------------------------------------------------------
--  Copyright 2015,2006 WSO2, Inc. http://www.wso2.org
--
--  Licensed under the Apache License, Version 2.0 (the "License");
--  you may not use this file except in compliance with the License.
--  You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
--  Unless required by applicable law or agreed to in writing, software
--  distributed under the License is distributed on an "AS IS" BASIS,
--  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--  See the License for the specific language governing permissions and
--  limitations under the License.

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

CREATE TABLE REG_CLUSTER_LOCK(
    REG_LOCK_NAME VARCHAR(20) NOT NULL,
    REG_LOCK_STATUS VARCHAR(20),
    REG_LOCKED_TIME TIMESTAMP,
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0,
    CONSTRAINT PK_REG_CLUSTER_LO1 PRIMARY KEY(REG_LOCK_NAME)
)/

CREATE TABLE REG_LOG(
    REG_LOG_ID DECIMAL(31,0) NOT NULL,
    REG_PATH VARCHAR(750),
    REG_USER_ID VARCHAR(31) NOT NULL,
    REG_LOGGED_TIME TIMESTAMP NOT NULL,
    REG_ACTION DECIMAL(31,0) NOT NULL,
    REG_ACTION_DATA VARCHAR(500),
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_REG_LOG PRIMARY KEY(REG_LOG_ID,REG_TENANT_ID)
)/

CREATE SEQUENCE REG_LOG_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER REG_LOG_TRIGGER NO CASCADE BEFORE INSERT ON REG_LOG
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.REG_LOG_ID)
       = (NEXTVAL FOR REG_LOG_SEQUENCE);

END/

CREATE TABLE REG_PATH(
    REG_PATH_ID DECIMAL(31,0)  NOT NULL,
    REG_PATH_VALUE VARCHAR(750) NOT NULL,
    REG_PATH_PARENT_ID DECIMAL(31,0),
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_PATH PRIMARY KEY(REG_PATH_ID,REG_TENANT_ID)
)/

-- CREATE INDEX REG_PATH_IND_BY_P1
  --  ON REG_PATH(REG_PATH_VALUE,REG_TENANT_ID)/

CREATE INDEX REG_PATH_IND_BY_P2
    ON REG_PATH(REG_PATH_PARENT_ID,REG_TENANT_ID)/

CREATE SEQUENCE REG_PATH_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER REG_PATH_TRIGGER NO CASCADE BEFORE INSERT ON REG_PATH
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.REG_PATH_ID)
       = (NEXTVAL FOR REG_PATH_SEQUENCE);

END/

CREATE TABLE REG_CONTENT(
    REG_CONTENT_ID DECIMAL(31,0) NOT NULL,
    REG_CONTENT_DATA BLOB(2G) NOT LOGGED,
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID,REG_TENANT_ID)
)/

CREATE SEQUENCE REG_CONTENT_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER REG_CONTENT_TRIGG1 NO CASCADE BEFORE INSERT ON REG_CONTENT
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.REG_CONTENT_ID)
       = (NEXTVAL FOR REG_CONTENT_SEQUENCE);

END/

CREATE TABLE REG_CONTENT_HISTORY(
    REG_CONTENT_ID DECIMAL(31,0) NOT NULL,
    REG_CONTENT_DATA BLOB(2G) NOT LOGGED,
    REG_DELETED SMALLINT,
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_REG_CONTENT_HI1 PRIMARY KEY(REG_CONTENT_ID,REG_TENANT_ID)
)/

CREATE TABLE REG_RESOURCE(
    REG_PATH_ID DECIMAL(31,0) NOT NULL,
    REG_NAME VARCHAR(256),
    REG_VERSION DECIMAL(31,0) NOT NULL,
    REG_MEDIA_TYPE VARCHAR(500),
    REG_CREATOR VARCHAR(31) NOT NULL,
    REG_CREATED_TIME TIMESTAMP NOT NULL,
    REG_LAST_UPDATOR VARCHAR(31),
    REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL,
    REG_DESCRIPTION VARCHAR(1000),
    REG_CONTENT_ID DECIMAL(31,0),
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    REG_UUID VARCHAR(100) NOT NULL,
    CONSTRAINT FK_REG_RES_PATH FOREIGN KEY(REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH(REG_PATH_ID,REG_TENANT_ID),
    CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION,REG_TENANT_ID)
)/

CREATE SEQUENCE REG_RESOURCE_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER REG_RESOURCE_TRIG1 NO CASCADE BEFORE INSERT ON REG_RESOURCE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.REG_VERSION)
       = (NEXTVAL FOR REG_RESOURCE_SEQUENCE);

END/

CREATE INDEX REG_RESOURCE_IND_1
    ON REG_RESOURCE(REG_NAME,REG_TENANT_ID)/

CREATE INDEX REG_RESOURCE_IND_2
    ON REG_RESOURCE(REG_PATH_ID,REG_NAME,REG_TENANT_ID)/

CREATE TABLE REG_RESOURCE_HISTORY(
    REG_PATH_ID DECIMAL(31,0) NOT NULL,
    REG_NAME VARCHAR(256),
    REG_VERSION DECIMAL(31,0) NOT NULL,
    REG_MEDIA_TYPE VARCHAR(500),
    REG_CREATOR VARCHAR(31) NOT NULL,
    REG_CREATED_TIME TIMESTAMP NOT NULL,
    REG_LAST_UPDATOR VARCHAR(31),
    REG_LAST_UPDATED_TIME TIMESTAMP NOT NULL,
    REG_DESCRIPTION VARCHAR(1000),
    REG_CONTENT_ID DECIMAL(31,0),
    REG_DELETED SMALLINT,
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    REG_UUID VARCHAR(100) NOT NULL,
    FOREIGN KEY(REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH(REG_PATH_ID,REG_TENANT_ID),
    FOREIGN KEY(REG_CONTENT_ID,REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY(REG_CONTENT_ID,REG_TENANT_ID),
    CONSTRAINT PK_REG_RESOURCE_H1 PRIMARY KEY(REG_VERSION,REG_TENANT_ID)
)/

CREATE INDEX REG_RES_HIST_IND_1
    ON REG_RESOURCE_HISTORY(REG_NAME,REG_TENANT_ID)/

CREATE INDEX REG_RES_HIST_IND_2
    ON REG_RESOURCE_HISTORY(REG_PATH_ID,REG_NAME,REG_TENANT_ID)/

CREATE TABLE REG_COMMENT(
    REG_ID DECIMAL(31,0) NOT NULL,
    REG_COMMENT_TEXT VARCHAR(500) NOT NULL,
    REG_USER_ID VARCHAR(31) NOT NULL,
    REG_COMMENTED_TIME TIMESTAMP NOT NULL,
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID,REG_TENANT_ID)
)/

CREATE SEQUENCE REG_COMMENT_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER REG_COMMENT_TRIGG1 NO CASCADE BEFORE INSERT ON REG_COMMENT
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.REG_ID)
       = (NEXTVAL FOR REG_COMMENT_SEQUENCE);

END/

CREATE TABLE REG_RESOURCE_COMMENT(
    REG_COMMENT_ID DECIMAL(31,0) NOT NULL,
    REG_VERSION DECIMAL(31,0),
    REG_PATH_ID DECIMAL(31,0),
    REG_RESOURCE_NAME VARCHAR(256),
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0,
    FOREIGN KEY(REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH(REG_PATH_ID,REG_TENANT_ID),
    FOREIGN KEY(REG_COMMENT_ID,REG_TENANT_ID) REFERENCES REG_COMMENT(REG_ID,REG_TENANT_ID)
)/

CREATE INDEX REG_RES_COMM_BY_P1
    ON REG_RESOURCE_COMMENT(REG_PATH_ID,REG_RESOURCE_NAME,REG_TENANT_ID)/

CREATE INDEX REG_RES_COMM_BY_V1
    ON REG_RESOURCE_COMMENT(REG_VERSION,REG_TENANT_ID)/

CREATE TABLE REG_RATING(
    REG_ID DECIMAL(31,0) NOT NULL,
    REG_RATING DECIMAL(31,0) NOT NULL,
    REG_USER_ID VARCHAR(31) NOT NULL,
    REG_RATED_TIME TIMESTAMP NOT NULL,
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID,REG_TENANT_ID)
)/

CREATE SEQUENCE REG_RATING_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER REG_RATING_TRIGGER NO CASCADE BEFORE INSERT ON REG_RATING
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.REG_ID)
       = (NEXTVAL FOR REG_RATING_SEQUENCE);

END/

CREATE TABLE REG_RESOURCE_RATING(
    REG_RATING_ID DECIMAL(31,0) NOT NULL,
    REG_VERSION DECIMAL(31,0),
    REG_PATH_ID DECIMAL(31,0),
    REG_RESOURCE_NAME VARCHAR(256),
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0,
    FOREIGN KEY(REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH(REG_PATH_ID,REG_TENANT_ID),
    FOREIGN KEY(REG_RATING_ID,REG_TENANT_ID) REFERENCES REG_RATING(REG_ID,REG_TENANT_ID)
)/

CREATE INDEX REG_RATING_IND_BY1
    ON REG_RESOURCE_RATING(REG_PATH_ID,REG_RESOURCE_NAME,REG_TENANT_ID)/

CREATE INDEX REG_RATING_IND_BY2
    ON REG_RESOURCE_RATING(REG_VERSION,REG_TENANT_ID)/

CREATE TABLE REG_TAG(
    REG_ID DECIMAL(31,0) NOT NULL,
    REG_TAG_NAME VARCHAR(500) NOT NULL,
    REG_USER_ID VARCHAR(31) NOT NULL,
    REG_TAGGED_TIME TIMESTAMP NOT NULL,
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID,REG_TENANT_ID)
)/

CREATE SEQUENCE REG_TAG_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER REG_TAG_TRIGGER NO CASCADE BEFORE INSERT ON REG_TAG
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.REG_ID)
       = (NEXTVAL FOR REG_TAG_SEQUENCE);

END/

CREATE TABLE REG_RESOURCE_TAG(
    REG_TAG_ID DECIMAL(31,0) NOT NULL,
    REG_VERSION DECIMAL(31,0),
    REG_PATH_ID DECIMAL(31,0),
    REG_RESOURCE_NAME VARCHAR(256),
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0,
    FOREIGN KEY(REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH(REG_PATH_ID,REG_TENANT_ID),
    FOREIGN KEY(REG_TAG_ID,REG_TENANT_ID) REFERENCES REG_TAG(REG_ID,REG_TENANT_ID)
)/

CREATE INDEX REG_TAG_IND_BY_PA1
    ON REG_RESOURCE_TAG(REG_PATH_ID,REG_RESOURCE_NAME,REG_TENANT_ID)/

CREATE INDEX REG_TAG_IND_BY_VE1
    ON REG_RESOURCE_TAG(REG_VERSION,REG_TENANT_ID)/

CREATE TABLE REG_PROPERTY(
    REG_ID DECIMAL(31,0) NOT NULL,
    REG_NAME VARCHAR(100) NOT NULL,
    REG_VALUE VARCHAR(1000),
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID,REG_TENANT_ID)
)/

CREATE SEQUENCE REG_PROPERTY_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER REG_PROPERTY_TRIG1 NO CASCADE BEFORE INSERT ON REG_PROPERTY
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.REG_ID)
       = (NEXTVAL FOR REG_PROPERTY_SEQUENCE);

END/

CREATE TABLE REG_RESOURCE_PROPERTY(
    REG_PROPERTY_ID DECIMAL(31,0) NOT NULL,
    REG_VERSION DECIMAL(31,0),
    REG_PATH_ID DECIMAL(31,0),
    REG_RESOURCE_NAME VARCHAR(256),
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0,
    FOREIGN KEY(REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH(REG_PATH_ID,REG_TENANT_ID),
    FOREIGN KEY(REG_PROPERTY_ID,REG_TENANT_ID) REFERENCES REG_PROPERTY(REG_ID,REG_TENANT_ID)
)/

CREATE INDEX REG_RESC_PROP_BY_1
    ON REG_RESOURCE_PROPERTY(REG_PROPERTY_ID,REG_VERSION,REG_TENANT_ID)/

CREATE INDEX REG_RESC_PROP_BY_2
    ON REG_RESOURCE_PROPERTY(REG_PROPERTY_ID,REG_PATH_ID,REG_RESOURCE_NAME,REG_TENANT_ID)/

CREATE TABLE REG_ASSOCIATION(
    REG_ASSOCIATION_ID DECIMAL(31,0) NOT NULL,
    REG_SOURCEPATH VARCHAR(750) NOT NULL,
    REG_TARGETPATH VARCHAR(750) NOT NULL,
    REG_ASSOCIATION_TYPE VARCHAR(2000) NOT NULL,
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_REG_ASSOCIATION PRIMARY KEY(REG_ASSOCIATION_ID,REG_TENANT_ID)
)/

CREATE SEQUENCE REG_ASSOCIATION_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER REG_ASSOCIATION_T1 NO CASCADE BEFORE INSERT ON REG_ASSOCIATION
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.REG_ASSOCIATION_ID)
       = (NEXTVAL FOR REG_ASSOCIATION_SEQUENCE);

END/

CREATE TABLE REG_SNAPSHOT(
    REG_SNAPSHOT_ID DECIMAL(31,0) NOT NULL,
    REG_PATH_ID DECIMAL(31,0) NOT NULL,
    REG_RESOURCE_NAME VARCHAR(256),
    REG_RESOURCE_VIDS BLOB(2G) NOT LOGGED NOT NULL,
    REG_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    FOREIGN KEY(REG_PATH_ID,REG_TENANT_ID) REFERENCES REG_PATH(REG_PATH_ID,REG_TENANT_ID),
    CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID,REG_TENANT_ID)
)/

CREATE INDEX REG_SNAPSHOT_PATH1
    ON REG_SNAPSHOT(REG_PATH_ID,REG_RESOURCE_NAME,REG_TENANT_ID)/

CREATE SEQUENCE REG_SNAPSHOT_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER REG_SNAPSHOT_TRIG1 NO CASCADE BEFORE INSERT ON REG_SNAPSHOT
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.REG_SNAPSHOT_ID)
       = (NEXTVAL FOR REG_SNAPSHOT_SEQUENCE);

END/

CREATE TABLE UM_TENANT(
    UM_ID DECIMAL(31,0) NOT NULL,
    UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
    UM_EMAIL VARCHAR(255),
    UM_ACTIVE SMALLINT DEFAULT 0,
    UM_CREATED_DATE TIMESTAMP NOT NULL,
    UM_USER_CONFIG BLOB(2G) NOT LOGGED,
    PRIMARY KEY(UM_ID),
    UNIQUE(UM_DOMAIN_NAME)
)/

CREATE SEQUENCE UM_TENANT_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_TENANT_TRIGGER NO CASCADE BEFORE INSERT ON UM_TENANT
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_TENANT_SEQUENCE);

END/

CREATE TABLE UM_USER(
    UM_ID DECIMAL(31,0) NOT NULL,
    UM_USER_NAME VARCHAR(255) NOT NULL,
    UM_USER_PASSWORD VARCHAR(255) NOT NULL,
    UM_SALT_VALUE VARCHAR(31),
    UM_REQUIRE_CHANGE SMALLINT DEFAULT 0,
    UM_CHANGED_TIME TIMESTAMP NOT NULL,
    UM_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    PRIMARY KEY(UM_ID,UM_TENANT_ID),
    UNIQUE(UM_USER_NAME,UM_TENANT_ID)
)/

CREATE SEQUENCE UM_USER_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_USER_TRIGGER NO CASCADE BEFORE INSERT ON UM_USER
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_USER_SEQUENCE);

END/

CREATE TABLE UM_USER_ATTRIBUTE(
    UM_ID DECIMAL(31,0) NOT NULL,
    UM_ATTR_NAME VARCHAR(255) NOT NULL,
    UM_ATTR_VALUE VARCHAR(255),
    UM_PROFILE_ID VARCHAR(255),
    UM_USER_ID DECIMAL(31,0),
    UM_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    FOREIGN KEY(UM_USER_ID,UM_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID) ON DELETE CASCADE ,
    PRIMARY KEY(UM_ID,UM_TENANT_ID)
)/

CREATE SEQUENCE UM_USER_ATTRIBUTE_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_USER_ATTRIBUTE1 NO CASCADE BEFORE INSERT ON UM_USER_ATTRIBUTE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_USER_ATTRIBUTE_SEQUENCE);

END/

CREATE TABLE UM_ROLE(
    UM_ID DECIMAL(31,0) NOT NULL,
    UM_ROLE_NAME VARCHAR(255) NOT NULL,
    UM_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    UM_SHARED_ROLE SMALLINT DEFAULT 0,
    PRIMARY KEY(UM_ID,UM_TENANT_ID),
    UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
)/

CREATE SEQUENCE UM_ROLE_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_ROLE_TRIGGER NO CASCADE BEFORE INSERT ON UM_ROLE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_ROLE_SEQUENCE);

END/

CREATE TABLE UM_MODULE(
	UM_ID INTEGER  NOT NULL,
	UM_MODULE_NAME VARCHAR(100),
	UNIQUE(UM_MODULE_NAME),
	PRIMARY KEY(UM_ID)
);

CREATE SEQUENCE UM_MODULE_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_MODULE_TRIGGER NO CASCADE BEFORE INSERT ON UM_MODULE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_MODULE_SEQUENCE);

END/

CREATE TABLE UM_MODULE_ACTIONS(
	UM_ACTION VARCHAR(255) NOT NULL,
	UM_MODULE_ID INTEGER NOT NULL,
	PRIMARY KEY(UM_ACTION, UM_MODULE_ID),
	FOREIGN KEY (UM_MODULE_ID) REFERENCES UM_MODULE(UM_ID) ON DELETE CASCADE
);

CREATE TABLE UM_PERMISSION(
    UM_ID DECIMAL(31,0) NOT NULL,
    UM_RESOURCE_ID VARCHAR(255) NOT NULL,
    UM_ACTION VARCHAR(255) NOT NULL,
    UM_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    UM_MODULE_ID INTEGER DEFAULT 0,
    PRIMARY KEY(UM_ID,UM_TENANT_ID)
)/

CREATE SEQUENCE UM_PERMISSION_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_PERMISSION_TRI1 NO CASCADE BEFORE INSERT ON UM_PERMISSION
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_PERMISSION_SEQUENCE);

END/

CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION
    ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID)/

CREATE TABLE UM_ROLE_PERMISSION(
    UM_ID DECIMAL(31,0) NOT NULL,
    UM_PERMISSION_ID DECIMAL(31,0) NOT NULL,
    UM_ROLE_NAME VARCHAR(255) NOT NULL,
    UM_IS_ALLOWED SMALLINT NOT NULL,
    UM_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    UNIQUE(UM_PERMISSION_ID,UM_ROLE_NAME,UM_TENANT_ID),
    FOREIGN KEY(UM_PERMISSION_ID,UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID,UM_TENANT_ID) ON DELETE CASCADE ,
    PRIMARY KEY(UM_ID,UM_TENANT_ID)
)/

CREATE SEQUENCE UM_ROLE_PERMISSION_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_ROLE_PERMISSIO1 NO CASCADE BEFORE INSERT ON UM_ROLE_PERMISSION
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_ROLE_PERMISSION_SEQUENCE);

END/

CREATE TABLE UM_USER_PERMISSION(
    UM_ID DECIMAL(31,0) NOT NULL,
    UM_PERMISSION_ID DECIMAL(31,0) NOT NULL,
    UM_USER_NAME VARCHAR(255) NOT NULL,
    UM_IS_ALLOWED SMALLINT NOT NULL,
    UM_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    UNIQUE(UM_PERMISSION_ID,UM_USER_NAME,UM_TENANT_ID),
    FOREIGN KEY(UM_PERMISSION_ID,UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID,UM_TENANT_ID) ON DELETE CASCADE ,
    PRIMARY KEY(UM_ID,UM_TENANT_ID)
)/

CREATE SEQUENCE UM_USER_PERMISSION_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_USER_PERMISSIO1 NO CASCADE BEFORE INSERT ON UM_USER_PERMISSION
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_USER_PERMISSION_SEQUENCE);

END/

CREATE TABLE UM_USER_ROLE(
    UM_ID DECIMAL(31,0) NOT NULL,
    UM_ROLE_ID DECIMAL(31,0) NOT NULL,
    UM_USER_ID DECIMAL(31,0) NOT NULL,
    UM_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    UNIQUE(UM_USER_ID,UM_ROLE_ID,UM_TENANT_ID),
    FOREIGN KEY(UM_ROLE_ID,UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID,UM_TENANT_ID) ON DELETE CASCADE ,
    FOREIGN KEY(UM_USER_ID,UM_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID) ON DELETE CASCADE ,
    PRIMARY KEY(UM_ID,UM_TENANT_ID)
)/

CREATE SEQUENCE UM_USER_ROLE_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_USER_ROLE_TRIG1 NO CASCADE BEFORE INSERT ON UM_USER_ROLE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_USER_ROLE_SEQUENCE);

END/

CREATE TABLE UM_ACCOUNT_MAPPING(
	UM_ID INTEGER NOT NULL,
	UM_USER_NAME VARCHAR(255) NOT NULL,
	UM_TENANT_ID INTEGER NOT NULL,
	UM_USER_STORE_DOMAIN VARCHAR(100),
	UM_ACC_LINK_ID INTEGER NOT NULL,
	UNIQUE(UM_USER_NAME, UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID),
	FOREIGN KEY (UM_TENANT_ID) REFERENCES UM_TENANT(UM_ID) ON DELETE CASCADE,
	PRIMARY KEY (UM_ID)
)/

CREATE SEQUENCE UM_ACCOUNT_MAPPING_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER UM_ACCOUNT_MAPPING_TRIG1 NO CASCADE BEFORE INSERT ON UM_ACCOUNT_MAPPING
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR UM_ACCOUNT_MAPPING_SEQUENCE);

END/

CREATE TABLE UM_SHARED_USER_ROLE(
    UM_ROLE_ID DECIMAL(31,0) NOT NULL,
    UM_USER_ID DECIMAL(31,0) NOT NULL,
    UM_USER_TENANT_ID DECIMAL(31,0) NOT NULL,
    UM_ROLE_TENANT_ID DECIMAL(31,0) NOT NULL,
    UNIQUE(UM_USER_ID,UM_ROLE_ID,UM_USER_TENANT_ID, UM_ROLE_TENANT_ID),
    FOREIGN KEY(UM_ROLE_ID,UM_ROLE_TENANT_ID) REFERENCES UM_ROLE(UM_ID,UM_TENANT_ID) ON DELETE CASCADE ,
    FOREIGN KEY(UM_USER_ID,UM_USER_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID) ON DELETE CASCADE
)/

CREATE TABLE HYBRID_ROLE(
    UM_ID DECIMAL(31,0) NOT NULL,
    UM_ROLE_ID VARCHAR(255) NOT NULL,
    UM_TENANT_ID DECIMAL(31,0) DEFAULT 0 NOT NULL,
    PRIMARY KEY(UM_ID,UM_TENANT_ID),
    UNIQUE(UM_ROLE_ID,UM_TENANT_ID)
)/

CREATE SEQUENCE HYBRID_ROLE_SEQUENCE AS DECIMAL(27,0)
    INCREMENT BY 1
    START WITH 1
    NO CACHE/

CREATE TRIGGER HYBRID_ROLE_TRIGG1 NO CASCADE BEFORE INSERT ON HYBRID_ROLE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.UM_ID)
       = (NEXTVAL FOR HYBRID_ROLE_SEQUENCE);

END/

CREATE TABLE HYBRID_USER_ROLE(
    UM_ID DECIMAL(31,0) NOT NULL,
    UM_USER_ID VARCHAR(255),


text:
USE [BuyGrandSellerReadReplica]
GO
/****** Object:  StoredProcedure [dbo].[sp_getItemSubReview]    Script Date: 12/26/2020 13:08:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Aditya Abeysinghe
-- Create date: 2020/12/26
-- Description:	Stored procedure for getting item sub reviews in seller view
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[sp_getItemSubReview]
(
    @productId int,
    @reviewID int
)
AS
BEGIN

	SET XACT_ABORT, NOCOUNT ON;
	DECLARE @transactionCount int;
	SET @transactionCount = @@TRANCOUNT;
	BEGIN TRY
		IF @transactionCount=0
			BEGIN TRANSACTION;
		ELSE
			SAVE TRANSACTION sp_getItemSubReview

		select reviewID, message as 'description',
      (select imagePath from dbo.login as l
        inner join dbo.review as r
          on l.username=r.originalUsername),
            submittedDate,
            (select concat(firstName,'',lastName) as 'name'  from dbo.login as l
              inner join dbo.review as r
                on l.username=r.originalUsername)
                    from dbo.review where itemId=@productId and originalReviewID=@reviewId;

		IF @transactionCount=0
			COMMIT TRANSACTION;

	END TRY
	BEGIN CATCH
		DECLARE @errorNo int, @message varchar(max), @xstate int
		SELECT @errorNo = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
		IF @transactionCount = 0
			ROLLBACK TRANSACTION;
		ELSE
			IF @xstate = 1
				ROLLBACK TRANSACTION sp_getItemSubReview;
			ELSE
				ROLLBACK TRANSACTION;
	END CATCH

END