Page "Proposals/BEP-0003" was changed by jure Diff URL: <https://issues.apache.org/bloodhound/wiki/Proposals/BEP-0003?action=diff&version=26> Revision 26 Comment: Move SQL proxy sample SQLs out of the main document Changes: -------8<------8<------8<------8<------8<------8<------8<------8<-------- Index: Proposals/BEP-0003 ========================================================================= --- Proposals/BEP-0003 (version: 25) +++ Proposals/BEP-0003 (version: 26) @@ -191,205 +191,7 @@ * queries targeted at 3rd party plugin tables are modified to prefix 3rd party plugin table names with product prefix * in addition to DML, DDL statements (CREATE TABLE/INDEX, ALTER TABLE/CONSTRAINT, DROP TABLE) are also translated -===== Trac tables #sql-tx-trac - -Some examples: - -**SELECT** - -A query targeted at productized trac tables: - -{{{#!sql -SELECT COUNT(*) -FROM - (SELECT t.id AS id, - t.summary AS summary, - t.owner AS OWNER, - t.status AS status, - t.priority AS priority, - t.milestone AS milestone, - t.time AS time, - t.changetime AS changetime, - priority.value AS priority_value - FROM ticket AS t - LEFT OUTER JOIN enum AS priority ON (priority.type='priority' - AND priority.name=priority) - LEFT OUTER JOIN milestone ON (milestone.name=milestone) - WHERE ((COALESCE(t.status,'')!=%s) - AND (COALESCE(t.OWNER,'')=%s)) - ORDER BY COALESCE(t.milestone,'')='', - COALESCE(milestone.completed,0)=0, - milestone.completed, - COALESCE(milestone.due,0)=0, - milestone.due, - t.milestone, - COALESCE(priority.value,'')='' DESC, - CAST(priority.value AS integer) DESC, - t.id) AS x -}}} - -... would be, when executed within 'MYPRODUCT' scope, translated into ... - -{{{#!sql -SELECT COUNT(*) -FROM - (SELECT t.id AS id, - t.summary AS summary, - t.owner AS OWNER, - t.status AS status, - t.priority AS priority, - t.milestone AS milestone, - t.time AS time, - t.changetime AS changetime, - priority.value AS priority_value - FROM - (SELECT * - FROM ticket - WHERE product="MYPRODUCT") AS t - LEFT OUTER JOIN - (SELECT * - FROM enum - WHERE product="MYPRODUCT") AS priority ON (priority.type='priority' - AND priority.name=priority) - LEFT OUTER JOIN - (SELECT * - FROM milestone - WHERE product="MYPRODUCT") AS milestone ON (milestone.name=milestone) - WHERE ((COALESCE(t.status,'')!=%s) - AND (COALESCE(t.OWNER,'')=%s)) - ORDER BY COALESCE(t.milestone,'')='', - COALESCE(milestone.completed,0)=0, - milestone.completed, - COALESCE(milestone.due,0)=0, - milestone.due, - t.milestone, - COALESCE(priority.value,'')='' DESC, - CAST(priority.value AS integer) DESC, - t.id) AS x -}}} - -**INSERT** - -INSERTs are translated by adding 'product' column with the proper value. - -{{{#!sql -INSERT INTO component(name,owner,description) -VALUES ('Joe', 'Johnny', 'Description') -}}} - -... translates to .... - -{{{#!sql -INSERT INTO component(product, name,owner,description) -VALUES ('MYPRODUCT', - 'Joe', - 'Johnny', - 'Description') -}}} - -**UPDATE** - -UPDATEs are translated by adding WHERE clause with the product: - -{{{#!sql -UPDATE component -SET name=%s,owner=%s, description=%s -WHERE name=%s -}}} - -... translates to ... - -{{{#!sql -UPDATE component -SET name=%s,owner=%s, description=%s -WHERE product='MYPRODUCT' - AND name=%s -}}} - -**DELETE** - -DELETEs are translated the same way as UPDATEs by adding WHERE clause: - -{{{#!sql -DELETE FROM component -WHERE name=%s -}}} -... translates to ... - -{{{#!sql -DELETE FROM component -WHERE product='MYPRODUCT' - AND name=%s -}}} - -===== 3rd party plugin tables #sql-tx-plugins - -Similar to trac tables, 3rd party plugin SQLs are translated before hitting the -SQL server. The difference is that in addition to productizing the trac tables, -the 3rd party plugin table names are prefixed with the current product prefix. - -Some examples: - -**SELECT** - -{{{#!sql -SELECT bt.bklg_id, t.status, count(*) as total -FROM backlog_ticket bt, ticket t -WHERE t.id = bt.tkt_id -AND (bt.tkt_order IS NULL OR bt.tkt_order > -1) -GROUP BY bklg_id, status -}}} - -... translates to ... - -{{{#!sql -SELECT bt.bklg_id, t.status, count(*) as total -FROM - (SELECT * - FROM MYPRODUCT_backlog_ticket) AS bt, - (SELECT * - FROM ticket - WHERE product="MYPRODUCT") AS t -WHERE t.id = bt.tkt_id -AND (bt.tkt_order IS NULL - OR bt.tkt_order > -1) -GROUP BY bklg_id, status -}}} - -**UPDATE, DELETE, INSERT** - -UPDATE, DELETE and INSERT statements are not modified for 3rd party plugin -tables, except for prefixing the table name with the active scope product -prefix. - -**CREATE, ALTER, DROP** - -For 3rd party plugin tables, DDL SQLs are also translated by prefixing table -names and constraint names. - -Some examples: - -{{{#!sql -CREATE TABLE holiday (date TEXT, description TEXT) -}}} - -... translates to ... - -{{{#!sql -CREATE TABLE MYPRODUCT_holiday (date TEXT, description TEXT) -}}} - -{{{#!sql -ALTER TABLE estimate ADD COLUMN diffcomment text -}}} - -... translates to ... - -{{{#!sql -ALTER TABLE MYPRODUCT_estimate ADD COLUMN diffcomment text -}}} - - +Examples of translated SQLs are available at [./LegacySchemaCompatibility legacy schema compatibility]. === Administration commands #admin-cmd -------8<------8<------8<------8<------8<------8<------8<------8<--------
-- Page URL: <https://issues.apache.org/bloodhound/wiki/Proposals/BEP-0003> Apache Bloodhound <https://issues.apache.org/bloodhound/> The Apache Bloodhound (incubating) issue tracker This is an automated message. Someone added your email address to be notified of changes on 'Proposals/BEP-0003' page. If it was not you, please report to .
