def test_it_should_parse_sql_statements_with_html_inside(self):
sql = u"""
create table eggs;
INSERT INTO widget_parameter_domain (widget_parameter_id, label, value)
VALUES ((SELECT MAX(widget_parameter_id)
FROM widget_parameter), "Carros", '<div class="box-zap-geral">
<div class="box-zap box-zap-autos">
<a class="logo" target="_blank" title="ZAP" href="http://www.zap.com.br/Parceiros/g1/RedirG1.aspx?CodParceriaLink=42&URL=http://www.zap.com.br">');
drop table spam;
"""
statements = Oracle._parse_sql_statements(sql)
expected_sql_with_html = """INSERT INTO widget_parameter_domain (widget_parameter_id, label, value)
VALUES ((SELECT MAX(widget_parameter_id)
FROM widget_parameter), "Carros", '<div class="box-zap-geral">
<div class="box-zap box-zap-autos">
<a class="logo" target="_blank" title="ZAP" href="http://www.zap.com.br/Parceiros/g1/RedirG1.aspx?CodParceriaLink=42&URL=http://www.zap.com.br">')"""
self.assertEqual(3, len(statements))
self.assertEqual('create table eggs', statements[0])
self.assertEqual(expected_sql_with_html, statements[1])
self.assertEqual('drop table spam', statements[2])
def test_it_should_parse_sql_statements(self):
#TODO include other types of sql
sql = "create table eggs; drop table spam; ; ;\
CREATE OR REPLACE FUNCTION simple \n\
RETURN VARCHAR2 IS \n\
BEGIN \n\
RETURN 'Simple Function'; \n\
END simple; \n\
/ \n\
drop table eggs; \n\
create or replace procedure proc_db_migrate(dias_fim_mes out number) \n\
as v number; \n\
begin \n\
SELECT LAST_DAY(SYSDATE) - SYSDATE \"Days Left\" \n\
into v \n\
FROM DUAL; \n\
dias_fim_mes := v; \n\
end; \n\
\t/ \n\
create OR RePLaCe TRIGGER \"FOLDER_TR\" \n\
BEFORE INSERT ON \"FOLDER\" \n\
FOR EACH ROW WHEN \n\
(\n\
new.\"FOLDER_ID\" IS NULL \n\
)\n\
BEGIN\n\
SELECT \"FOLDER_SQ\".nextval\n\
INTO :new.\"FOLDER_ID\"\n\
FROM dual;\n\
EnD;\n\
/\n\
CREATE OR REPLACE\t PACKAGE pkg_dbm \n\
AS \n\
FUNCTION getArea (i_rad NUMBER) \n\
RETURN NUMBER;\n\
PROCEDURE p_print (i_str1 VARCHAR2 := 'hello',\n\
i_str2 VARCHAR2 := 'world', \n\
i_end VARCHAR2 := '!');\n\
END;\n\
/ \n\
CREATE OR REPLACE\n PACKAGE BODY pkg_dbm \n\
AS \n\
FUNCTION getArea (i_rad NUMBER) \n\
RETURN NUMBER \n\
IS \n\
v_pi NUMBER := 3.14; \n\
BEGIN \n\
RETURN v_pi * (i_rad ** 2); \n\
END; \n\
PROCEDURE p_print (i_str1 VARCHAR2 := 'hello', i_str2 VARCHAR2 := 'world', i_end VARCHAR2 := '!') \n\
IS \n\
BEGIN \n\
DBMS_OUTPUT.put_line (i_str1 || ',' || i_str2 || i_end); \n\
END; \n\
END; \n\
/ \n\
DECLARE\n\
counter NUMBER(10,8) := 2; \r\n\
pi NUMBER(8,7) := 3.1415926; \n\
test NUMBER(10,8) NOT NULL := 10;\n\
BEGIN \n\
counter := pi/counter; \n\
pi := pi/3; \n\
dbms_output.put_line(counter); \n\
dbms_output.put_line(pi); \n\
END; \n\
/ \n\
BEGIN \n\
dbms_output.put_line('teste de bloco anonimo'); \n\
dbms_output.put_line(select 1 from dual); \n\
END; \n\
/ "
statements = Oracle._parse_sql_statements(sql)
self.assertEqual(10, len(statements))
self.assertEqual('create table eggs', statements[0])
self.assertEqual('drop table spam', statements[1])
self.assertEqual("CREATE OR REPLACE FUNCTION simple \n\
RETURN VARCHAR2 IS \n\
BEGIN \n\
RETURN 'Simple Function'; \n\
END simple;", statements[2])
self.assertEqual('drop table eggs', statements[3])
self.assertEqual('create or replace procedure proc_db_migrate(dias_fim_mes out number) \n\
as v number; \n\
begin \n\
SELECT LAST_DAY(SYSDATE) - SYSDATE \"Days Left\" \n\
into v \n\
FROM DUAL; \n\
dias_fim_mes := v; \n\
end;', statements[4])
self.assertEqual('create OR RePLaCe TRIGGER \"FOLDER_TR\" \n\
BEFORE INSERT ON \"FOLDER\" \n\
FOR EACH ROW WHEN \n\
(\n\
new.\"FOLDER_ID\" IS NULL \n\
)\n\
BEGIN\n\
SELECT \"FOLDER_SQ\".nextval\n\
INTO :new.\"FOLDER_ID\"\n\
FROM dual;\n\
EnD;', statements[5])
self.assertEqual("CREATE OR REPLACE\t PACKAGE pkg_dbm \n\
AS \n\
FUNCTION getArea (i_rad NUMBER) \n\
RETURN NUMBER;\n\
PROCEDURE p_print (i_str1 VARCHAR2 := 'hello',\n\
i_str2 VARCHAR2 := 'world', \n\
i_end VARCHAR2 := '!');\n\
END;", statements[6])
self.assertEqual("CREATE OR REPLACE\n PACKAGE BODY pkg_dbm \n\
AS \n\
FUNCTION getArea (i_rad NUMBER) \n\
RETURN NUMBER \n\
IS \n\
v_pi NUMBER := 3.14; \n\
BEGIN \n\
RETURN v_pi * (i_rad ** 2); \n\
END; \n\
PROCEDURE p_print (i_str1 VARCHAR2 := 'hello', i_str2 VARCHAR2 := 'world', i_end VARCHAR2 := '!') \n\
IS \n\
BEGIN \n\
DBMS_OUTPUT.put_line (i_str1 || ',' || i_str2 || i_end); \n\
END; \n\
END;", statements[7])
self.assertEqual("DECLARE\n\
counter NUMBER(10,8) := 2; \r\n\
pi NUMBER(8,7) := 3.1415926; \n\
test NUMBER(10,8) NOT NULL := 10;\n\
BEGIN \n\
counter := pi/counter; \n\
pi := pi/3; \n\
dbms_output.put_line(counter); \n\
dbms_output.put_line(pi); \n\
END;", statements[8])
self.assertEqual("BEGIN \n\
dbms_output.put_line('teste de bloco anonimo'); \n\
dbms_output.put_line(select 1 from dual); \n\
END;", statements[9])
