Humble Trader

Monday, August 07, 2006

Get HTML - ctl_get_html_test.sql 

SET SERVEROUTPUT ON SIZE 1000000;

-- Test ctl_gen:
DECLARE
   l_mapping_1          VARCHAR2(30) := 'PAGE_ACCESS_TEST_1';
   l_mapping_2          VARCHAR2(30) := 'PARSE_PASSES_TEST_2';
   l_raw_run_no         NUMBER;
   l_parse_run_no       NUMBER;
   l_web_page_1         VARCHAR2(30) := 'PAGE_ACCESS_TEST_PAGE_1';
   l_row_count          NUMBER;
   l_row_data           VARCHAR2(4000);
   l_passed             BOOLEAN := TRUE;
BEGIN
   dbms_output.put_line('-');
   dbms_output.put_line('- Initialise...');

   -- Initialisation code.
   DELETE FROM html_parse_passes
   WHERE raw_run_no = 
     (SELECT run_no
      FROM html_page_access
      WHERE html_page_name = l_web_page_1);

   DELETE FROM html_page_access
   WHERE html_page_name = l_web_page_1;

   DELETE FROM mapping_histories
   WHERE mapping_library IN (l_mapping_1
                            ,l_mapping_2);

   DELETE FROM mapping_libraries
   WHERE mapping_name IN (l_mapping_1
                         ,l_mapping_2);

   DELETE FROM html_pages
   WHERE name = l_web_page_1;

   INSERT INTO mapping_libraries
   (
      mapping_name
     ,rep_from
     ,rep_to
   )
   VALUES
   (
      l_mapping_1
     ,'CONTROL'
     ,'CONTROL'
   );

   INSERT INTO mapping_libraries
   (
      mapping_name
     ,rep_from
     ,rep_to
   )
   VALUES
   (
      l_mapping_2
     ,'CONTROL'
     ,'CONTROL'
   );

   INSERT INTO mapping_histories
   (
      run_no
     ,mapping_library
     ,start_tsp
     ,end_tsp
   )
   VALUES
   (
      run_no_s.NEXTVAL
     ,l_mapping_1
     ,SYSDATE
     ,NULL
   );

   SELECT run_no_s.CURRVAL
   INTO l_raw_run_no
   FROM dual;

   INSERT INTO mapping_histories
   (
      run_no
     ,mapping_library
     ,start_tsp
     ,end_tsp
   )
   VALUES
   (
      run_no_s.NEXTVAL
     ,l_mapping_2
     ,SYSDATE
     ,NULL
   );

   SELECT run_no_s.CURRVAL
   INTO l_parse_run_no
   FROM dual;

   INSERT INTO html_pages
   (
      name
     ,url
     ,username
     ,password
   )
   VALUES
   (
      l_web_page_1
     ,'http://test/'
     ,NULL
     ,NULL
   );

   COMMIT;

   dbms_output.put_line('- Done.');
   dbms_output.put_line('-');

   dbms_output.put_line('+- PROCEDURE init_html_page_access test start:');
   dbms_output.put_line('|');

   dbms_output.put_line
      ('| Test  1: Pass : web_page = ' || l_web_page_1);
   dbms_output.put_line
      ('|               : run_no = ' || l_raw_run_no);
   dbms_output.put_line('|');
   dbms_output.put_line
      ('| Expected result: Inserts a row into HTML_PAGE_ACCESS.');
   dbms_output.put_line('|');

   ctl_get_html.init_html_page_access(l_web_page_1, l_raw_run_no);

   SELECT COUNT(*)
   INTO l_row_count
   FROM html_page_access
   WHERE run_no = l_raw_run_no;

   dbms_output.put_line('| Result: HTML_PAGE_ACCESS row count = ' ||
      l_row_count);
   dbms_output.put_line('|         HTML_PAGE_ACCESS data :');

   SELECT '| [' || run_no || ', ' || html_page_name || ', ' || status ||
          ', ' || TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') || ']'
   INTO l_row_data
   FROM html_page_access
   WHERE run_no = l_raw_run_no;

   dbms_output.put_line(l_row_data);
   dbms_output.put_line('|');

   dbms_output.put_line('| +----------+');
   IF l_row_count = 1
   THEN
      dbms_output.put_line('| |  PASSED  |');
   ELSE
      dbms_output.put_line('| |  FAILED  |');
      l_passed := FALSE;
   END IF;
   dbms_output.put_line('| +----------+');

   dbms_output.put_line('|');
   dbms_output.put_line('+- PROCEDURE init_html_page_access test end:');
   dbms_output.put_line('+');

   dbms_output.put_line('+- PROCEDURE html_page_access_update_status test ' ||
      'start:');
   dbms_output.put_line('|');

   dbms_output.put_line
      ('| Test  1: Pass : raw_run_no = ' || l_raw_run_no);
   dbms_output.put_line
      ('|               : new_status = FETCHED');
   dbms_output.put_line('|');
   dbms_output.put_line
      ('| Expected result: Updates the row status in previous test to ' ||
      'FETCHED.');
   dbms_output.put_line('|');

   ctl_get_html.html_page_access_update_status(l_raw_run_no, 'FETCHED');

   dbms_output.put_line('| Results: HTML_PAGE_ACCESS data :');

   SELECT '| [' || run_no || ', ' || html_page_name || ', ' || status ||
          ', ' || TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') || ']'
   INTO l_row_data
   FROM html_page_access
   WHERE run_no = l_raw_run_no;

   dbms_output.put_line(l_row_data);

   SELECT status
   INTO l_row_data
   FROM html_page_access
   WHERE run_no = l_raw_run_no;

   dbms_output.put_line('|          Row status = ' || l_row_data);

   dbms_output.put_line('|');
   dbms_output.put_line('| +----------+');
   IF l_row_data = 'FETCHED'
   THEN
      dbms_output.put_line('| |  PASSED  |');
   ELSE
      dbms_output.put_line('| |  FAILED  |');
      l_passed := FALSE;
   END IF;
   dbms_output.put_line('| +----------+');

   dbms_output.put_line('|');
   dbms_output.put_line(
      '+- PROCEDURE html_page_access_update_status test end:');
   dbms_output.put_line('+');

   dbms_output.put_line('+- PROCEDURE init_html_parse_passes test start:');
   dbms_output.put_line('|');

   dbms_output.put_line
      ('| Test  1: Pass : parse_run_no = ' || l_parse_run_no);
   dbms_output.put_line
      ('|               : raw_run_no = ' || l_raw_run_no);
   dbms_output.put_line('|');
   dbms_output.put_line
      ('| Expected result: Inserts a row into HTML_PARSE_PASSES.');
   dbms_output.put_line('|');

   ctl_get_html.init_html_parse_passes(l_parse_run_no, l_raw_run_no);

   SELECT COUNT(*)
   INTO l_row_count
   FROM html_parse_passes
   WHERE parse_run_no = l_parse_run_no
     AND raw_run_no = l_raw_run_no;

   dbms_output.put_line('| Result: HTML_PARSE_PASSES row count = ' ||
      l_row_count);
   dbms_output.put_line('|         HTML_PARSE_PASSES data :');

   SELECT '| [' || parse_run_no || ', ' || raw_run_no || ', ' || status ||
          ', ' || TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') || ']'
   INTO l_row_data
   FROM html_parse_passes
   WHERE parse_run_no = l_parse_run_no
     AND raw_run_no = l_raw_run_no;

   dbms_output.put_line(l_row_data);
   dbms_output.put_line('|');

   dbms_output.put_line('| +----------+');
   IF l_row_count = 1
   THEN
      dbms_output.put_line('| |  PASSED  |');
   ELSE
      dbms_output.put_line('| |  FAILED  |');
      l_passed := FALSE;
   END IF;
   dbms_output.put_line('| +----------+');

   dbms_output.put_line('|');
   dbms_output.put_line('+- PROCEDURE init_html_parse_passes test end:');
   dbms_output.put_line('+');

   dbms_output.put_line(
       '+- PROCEDURE html_parse_passes_update_stat test start:');
   dbms_output.put_line('|');

   dbms_output.put_line
      ('| Test  1: Pass : parse_run_no = ' || l_parse_run_no);
   dbms_output.put_line
      ('|               : raw_run_no = ' || l_raw_run_no);
   dbms_output.put_line
      ('|               : new_status = PARSED');
   dbms_output.put_line('|');
   dbms_output.put_line
      ('| Expected result: Updates the row status in previous test to ' ||
      'PARSED.');
   dbms_output.put_line('|');

   ctl_get_html.html_parse_passes_update_stat(l_parse_run_no, l_raw_run_no,
      'PARSED');

   dbms_output.put_line('| Results: HTML_PARSE_PASSES data :');

   SELECT '| [' || parse_run_no || ', ' || raw_run_no || ', ' || ', ' ||
      status || ', ' || TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') || ']'
   INTO l_row_data
   FROM html_parse_passes
   WHERE parse_run_no = l_parse_run_no
     AND raw_run_no = l_raw_run_no;

   dbms_output.put_line(l_row_data);

   SELECT status
   INTO l_row_data
   FROM html_parse_passes
   WHERE parse_run_no = l_parse_run_no
     AND raw_run_no = l_raw_run_no;

   dbms_output.put_line('|          Row status = ' || l_row_data);

   dbms_output.put_line('|');
   dbms_output.put_line('| +----------+');
   IF l_row_data = 'PARSED'
   THEN
      dbms_output.put_line('| |  PASSED  |');
   ELSE
      dbms_output.put_line('| |  FAILED  |');
      l_passed := FALSE;
   END IF;
   dbms_output.put_line('| +----------+');

   dbms_output.put_line('|');
   dbms_output.put_line(
      '+- PROCEDURE html_page_access_update_status test end:');

   dbms_output.put_line('-');

   dbms_output.put_line('- Overall result:');
   dbms_output.put_line('-');
   dbms_output.put_line('- +----------+');
   IF l_passed = TRUE
   THEN
      dbms_output.put_line('- |  PASSED  |');
   ELSE
      dbms_output.put_line('- |  FAILED  |');
   END IF;
   dbms_output.put_line('- +----------+');

   dbms_output.put_line('-');
   dbms_output.put_line('- Clean up...');

    -- Clean up code.
   DELETE FROM html_parse_passes
   WHERE raw_run_no = 
     (SELECT run_no
      FROM html_page_access
      WHERE html_page_name = l_web_page_1);

   DELETE FROM html_parse_passes
   WHERE parse_run_no = l_parse_run_no;

   DELETE FROM html_page_access
   WHERE html_page_name = l_web_page_1;

   DELETE FROM mapping_histories
   WHERE mapping_library IN (l_mapping_1
                            ,l_mapping_2);

   DELETE FROM mapping_libraries
   WHERE mapping_name IN (l_mapping_1
                         ,l_mapping_2);

   DELETE FROM html_pages
   WHERE name = l_web_page_1;

   COMMIT;

   dbms_output.put_line('- Done.');
   dbms_output.put_line('-');
END;
/

posted by Cat Play Crew at 8:05 pm

0 Comments:

Post a Comment

<< Home