Uploaded image for project: 'Red Hat Fuse'
  1. Red Hat Fuse
  2. ENTESB-12977

Based on LSP work provide the code templates for SQL Editor

    XMLWordPrintable

Details

    • fuse-7.7-build2
    • DV Sprint 63

    Description

      The templates should assist with most common view creation structures such as:

      • Explore how to glue these use cases from SQL editor point of view:
      Create a view from a single datasource and single table with all fields (DONE)
      CREATE VIEW Contacts (first_name, last_name, company, lead_source, create_date) OPTIONS (ANNOTATION 'Contacts all fields') AS
      SELECT first_name, last_name, company, lead_source, create_date
      FROM PostgresDB.contact;
      
      • Create a view from a single datasource and single table with selected fields and changed fields names
      CREATE VIEW Contacts (first_name, last_name, company) AS
      SELECT first_name, last_name, company
      FROM PostgresDB.contact;
      
      • Create a view from a single datasource and join of two tables
      CREATE VIEW CustomerZip(id bigint PRIMARY KEY, name string, ssn string, zip string) AS
      SELECT c.ID as id, c.NAME as name, c.SSN as ssn, a.ZIP as zip
      FROM accounts.CUSTOMER c LEFT OUTER JOIN accounts.ADDRESS a
      ON c.ID = a.CUSTOMER_ID;
      
      • Create a view from a single datasource and union of two tables
        CREATE VIEW Contacts (names) AS
        SELECT first_name FROM PostgresDB.contact
        UNION
        SELECT last_name FROM PostgresDB.contact
        
      • Add filtering to an existing view
        CREATE VIEW Winelist (id, wineName, price, "year", PRIMARY KEY(id)) AS
        SELECT id, wine, price, "year"
        FROM PostgresDB.winelist
        WHERE price > 500;
        
      • Add authorization on a view
        CREATE ROLE ReadRole WITH FOREIGN ROLE ReadRole;
        CREATE VIEW CustomerZip(id bigint PRIMARY KEY, name string, ssn string, zip string) AS 
        SELECT c.ID as id, c.NAME as name, c.SSN as ssn, a.ZIP as zip
        FROM accounts.CUSTOMER c LEFT OUTER JOIN accounts.ADDRESS a ON c.ID = a.CUSTOMER_ID;	 
        GRANT SELECT ON TABLE "portfolio.CustomerZip" TO ReadRole;
        
      • Add transformation/masking to an existing view field
      • Create a view that references and uses another existing view as source

      Attachments

        Issue Links

          Activity

            People

              blafond Barry LaFond
              rareddy_mk-kafka Ramesh Reddy
              Martin Muzikar Martin Muzikar
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: