Меню / Menu

Tips feature for Fitnesse

tips.zip

This is menu with templates of commands and tests' displayed in edit mode.

New feature 'Test Templates for standard requirements':

• Standard Tests Dim
• Standard Tests Dim Scd
• Standard Tests Fct

Setup:

1. Unpack into FitNesseRootfilesjavascripttips
2. Copy all from FitNesseRootfilesjavascripttipssetup.txt and paste at the end of FitNesseRootfilesjavascripttextareaWrapSupport.js
3. Test it:

 

---

 

Listing for New Feature 'Test Templates for standard requirements':

For Dimensions:

!define t1name {DIM_AGENTS_SCD}
!define c1name {AGENT_ID}

'''Set parameters''' -----
${c1name} Search for default values
|Query|SELECT SUM(1) AS is_filled FROM(SELECT DISTINCT ${c1name} AS total FROM ${t1name} WHERE ${c1name} IN(-98,-99))|
|is_filled|
|2|

${c1name} Search for loaded data
|Query|SELECT CASE WHEN SUM(1)>2 THEN 'OK' ELSE 'ERROR' END AS is_filled FROM(SELECT DISTINCT ${c1name} AS total FROM ${t1name})|
|is_filled|
|OK|
-----

TABLE COMMENTS
|Query|SELECT SUM(1) AS is_filled FROM(SELECT COMMENTS FROM all_tab_comments WHERE table_name='${t1name}' AND owner=(SELECT USER FROM DUAL))|
|is_filled|
|1|

COLUMN COMMENTS
|Query|SELECT CASE WHEN SUM(1)>1 THEN 'ERROR' ELSE 'OK' END AS is_filled FROM(SELECT 1 FROM all_tab_columns a, all_col_comments b WHERE     a.column_name=b.column_name(+) AND a.table_name=b.table_name AND a.table_name='${t1name}' AND a.owner=(SELECT USER FROM DUAL) AND b.comments is null)|
|is_filled|
|OK|

COLUMN NULLABLE
|Query|SELECT CASE WHEN SUM(1)>1 THEN 'ERROR' ELSE 'OK' END AS is_nulls FROM(SELECT 1 FROM all_tab_columns a WHERE a.table_name='${t1name}' AND     owner=(SELECT USER FROM DUAL) AND a.nullable = 'Y')|
|is_nulls|
|OK|

For specific of SCD dimensions:

!define t1name {DIM_AGENTS_SCD}
!define v1name {DIM_AGENTS_ACT}
!define surr1name {AGENT_SURR_ID}
!define id1name {AGENT_ID}
!define flag1name {IS_ACTIVE}
!define val1name {VALID_FROM}
!define val2name {VALID_TO}

'''Set parameters''' -----
${val1name}, ${val2name} Search for default date values
|Query|SELECT SUM(1) AS is_filled FROM(SELECT DISTINCT ${id1name},${val1name},${val2name} FROM ${t1name} WHERE ${id1name} IN(-98,-99) AND ${val1name}=to_date('01.01.1900','dd.mm.yyyy') AND ${val2name}=to_date('31.12.9999','dd.mm.yyyy'))|
|is_filled|
|2|

${t1name} Search for lost periods
|Query|SELECT SUM(CASE WHEN valid_to!=NVL(v_f_next,TO_DATE('31.12.9999','DD.MM.YYYY')) THEN 1 ELSE 0 END) AS is_lost FROM (SELECT ${surr1name},${id1name},valid_to,LEAD(valid_from)OVER(PARTITION BY ${id1name} ORDER BY valid_from ASC) AS v_f_next FROM ${t1name} WHERE ${id1name} NOT IN(-99,-98))|
|is_lost|
|0|

${t1name} N flag correct
|Query|SELECT COUNT(*) AS is_flag FROM ${t1name} WHERE valid_to=TO_DATE('31.12.9999','DD.MM.YYYY') AND ${flag1name}='N' AND ${surr1name} NOT IN (-98,-99)|
|is_flag|
|0|

${t1name} Y flag correct
|Query|SELECT COUNT(*) AS is_flag FROM (SELECT * FROM ${t1name} WHERE valid_to!=TO_DATE('31.12.9999','DD.MM.YYYY') AND ${flag1name}='Y' AND ${surr1name} NOT IN (-98,-99))|
|is_flag|
|0|
${t1name} active view exists
|Query|SELECT COUNT(*) AS is_filled FROM ${v1name} WHERE ${flag1name}='N' AND 1>=ROWNUM|
|is_filled|
|0|

For fact tables:

!define t1name {FCT_AGENT_GROSS_ADDS}
!define d1name {DIM_CONTRACT_TYPES}
!define c1name {CONTRACT_TYPE_ID}
!define d2name {DIM_CONTRACT_PAYMENTS}
!define c2name {CONTRACT_PAYMENT_ID}

'''Set parameters''' -----
${c1name} Search for loaded data
|Query|SELECT CASE WHEN COUNT(*)>0 THEN 'OK' ELSE 'ERROR' END AS is_filled FROM (SELECT ${c1name} AS total FROM ${t1name} WHERE ${c1name} NOT IN (-98,-99))|
|is_filled|
|OK|

${d1name} Search for not assigned data
|Query|SELECT COUNT(*) AS is_filled FROM (SELECT a.${c1name}, b.${c1name} FROM ${t1name} a, ${d1name} b WHERE a.${c1name}=b.${c1name}(+) AND b.${c1name} IS NULL AND 1>=ROWNUM)|
|is_filled|
|0|

${c2name} Search for loaded data
|Query|SELECT CASE WHEN COUNT(*)>0 THEN 'OK' ELSE 'ERROR' END AS is_filled FROM (SELECT ${c2name} AS total FROM ${t1name} WHERE ${c2name} NOT IN (-98,-99))|
|is_filled|
|OK|

${d2name} Search for not assigned data
|Query|SELECT COUNT(*) is_filled FROM (SELECT a.${c2name}, b.${c2name} FROM ${t1name} a, ${d2name} b WHERE a.${c2name}=b.${c2name}(+) AND b.${c2name} IS NULL AND 1>=ROWNUM)|
|is_filled|
|0|
-----

COLUMN NULLABLE
|Query|SELECT COUNT(*) AS is_nulls FROM(SELECT 1 FROM all_tab_columns a WHERE a.table_name='${t1name}' AND a.nullable = 'Y' AND a.column_name NOT LIKE 'FCT_%' AND a.owner=USER)|
|is_nulls|
|0|

COLUMN event_dt, insert_dt, update_dt
|Query|SELECT COUNT(*) AS is_filled FROM(SELECT 1 FROM all_tab_columns a WHERE a.table_name='${t1name}' AND a.owner=USER AND (a.column_name='EVENT_DT' OR a.column_name='INSERT_DT' OR a.column_name='UPDATE_DT'))|
|is_filled|
|3|