1 / 21

M.Sc. Seminar - Keren Lenz Supervisor - Dr. Yossi Gil July 1 st 2007

AraRat -. Simple and Safe SQL Queries with C++ Templates. M.Sc. Seminar - Keren Lenz Supervisor - Dr. Yossi Gil July 1 st 2007. Motivation. A relational DB with an Employee table Field 1: EMPNUM (integer) ‏ Field 2: DEPT ( smallint ) ‏ Field 3: FIRST_N (string) ‏

seda
Télécharger la présentation

M.Sc. Seminar - Keren Lenz Supervisor - Dr. Yossi Gil July 1 st 2007

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. AraRat- Simple and Safe SQL Queries with C++ Templates M.Sc. Seminar - Keren Lenz Supervisor - Dr. Yossi Gil July 1st 2007

  2. Motivation • A relational DB with an Employee table • Field 1: EMPNUM (integer)‏ • Field 2: DEPT (smallint)‏ • Field 3: FIRST_N (string)‏ • Field 4: LAST_N (string)‏ • Field 5: SALARY (double) • Mission: refer to the DB from a high level language application.

  3. SQL Queries – The Old waycan you spot the bugs? char* get_employees(int dept, char* first) { bool first_cond = true; string s(“SELECT FIRST_N, LAST_N FROM EMPLOYEES “); if (dept > 0){ // valid dept number s.append( “WHERE DEPT = ‘ “); s.append(itoa(dept)); s.append(“’”); first_cond = false; } if (first == null) return s; if (first_cond) s.append(“WHERE “); else s.append(“AND”); s.append(“FIRST_N= ‘ “); s.append(first); s.append(“’”); return s; } 1. Misspelled Name 3. Type Mismatch 2. Syntax error 4. Security Vulnerability , 5. Code Coverage ,6. Maintnance cost

  4. Short and elegant code SQL Queries - AraRat’s Way Schema awareness – No misspelling 1 char* get_employees(shortdept, char* first) { 2 DEF_V(e,EMPLOYEE[FIRST_N,LAST_N]); 3 if(first != null) e /= (FIRST_N == first); 4 if(dept > 0) e /= (DEPT == dept); 5 returne.asSQL(); 6 } Type safe Natural C++ Syntax Automatic generation of queries – no syntax errors Security Vulnerability, Code Coverage , Maintenance cost Problems Solved

  5. AraRat’s Overview • GOAL : safe production of SQL queries. • AraRat = Ara + Rat: • Ara is Augmented Relational Algebra. • Rat is Relational AlgebraTemplates. • In a sense, Rat is Ara’s compiler.

  6. Ara’s Operators RA Operator Ara Operator SQL equivalent selection R/c select * from R where c projection R[f1,f2] select f1,f2 from R union R1+R2 R1 union R2 difference R1-R2 R1 - R2 natural join R1*R2 R1 join R2 left join R1<<R2 R1 left join R2 right join R1>>R2 R1 right join R2 rename b(a)‏ a as b

  7. Ara's Principles • Queries are composed via Query Objects • C++ first class objects. • A query object has: • Type – Encodes scheme of the resulting relation • Same set of fields => same type • Content – the procedure for constructing the query

  8. Primitive Query Objects • A Primitive QO is defined for each DB table • Option 1: Manually, using macros • Option 2: Automatically by the DB2ARA tool • Represent a query that returns all fieldsSELECT * FROM <RELATION_NAME> DEF_F(EMPNUM)‏ DEF_F(DEPT)‏ DEF_F(FIRST_N)‏ DEF_F(LAST_N)‏ DEF_F(SALARY)‏ DEF_R(EMPLOYEE, (EMPNUM/integer, FIRST_N/String, LAST_N/String, DEPT/SmallInt, SALARY/double));

  9. Composite Query Objects • Constructed using Ara's operators • The operands: • QOs (either primitive or composite)‏ • fields • variables • literals DEF_V(e,EMPLOYEE[FIRST_N,LAST_N] / (DEPT > 3));

  10. A QO Can ... • ... be stored in a variable • ... receive the asSQL()message • ... be used in an Ara expression • ... be passed to the TUPLE_T macro • returns the type of tuples that the query return DEF_V(e,EMPLOYEE); e = EMPLOYEE / (DEPT == 3); string s = e.asSQL(); DEF_V(e1,e[FIRST_N, LAST_N]); e1 /= (EMPNUM < 100); TUPLE_T(e)** res = new TUPLE_T(e)*[100];

  11. Using Ara’s Syntax #include"rat" // Global RAT declarations and macros #include"employee.h" // Primitive query objects and other DEF_F(FULL_N); DEF_F(ID); intmain(intargc, char* argv[]) { conststring s = ( (EMPLOYEE / (DEPT > 3 && SALARY < 3.14))‏ [FIRST_N, LAST_N, FULL_N(cat(LAST_N, ", ", FIRST_N)), ID(EMPNUM)] ).asSQL(); // ... execute the SQL query in s using e.g., ADO. return0; }

  12. So Far… Ara provides an elegant way for composing queries… How does it do that? Do we need to change the C++ compiler ?

  13. Template Programming • C++ templates are Turing-complete • Can compute anything at compile-time template <int N> struct Factorial { enum { value = N * Factorial<N - 1>::value }; }; template <> struct Factorial<0> { enum { value = 1 }; }; // Factorial<4>::value == 24 // Factorial<0>::value == 1 void foo() { int x = Factorial<4>::value; // == 24 int y = Factorial<0>::value; // == 1 }

  14. A Compile-time List struct Nil { static const int length = 0; }; template<class F, class R=Nil> struct List { typedef F First; typedef R Rest; static const int length = 1 + Rest::length; }; typedef List<int, List<short, List<double, List<float, Nil> > > > typesList;

  15. Implementation–Take I • Both scheme and content are compile-time constants: • Problem: • Every query has its own type • Query objects are immutable template<typenameFIELDS, typenameCOND …> classQueryObject { public: typedefFIELDS fields; typedefCOND cond; … }; QueryObject< LIST(EMPNUM, DEPT, FIRST_N, LAST_N), GREATER(DEPT, 3)‏ > q;

  16. Implementation–Take II • Dual query representation • Only the result scheme is encoded at compile time • Allows: e /= (DEPT < 3)‏; • Problem: • Projected-out fields are not part of the type • Cant be used in selection conditions template<typenameFIELDS > classQueryObject { public: typedefFIELDS fields; R_TREE * t; }; Stores the evaluation procedure. This isa run-time value

  17. Implementation–Take III • Projection Resilience • Allows SELECT criteria with fields that were projected out template<typenameFIELDS , typename DICTIONARY> classQueryObject{ public: typedefFIELDS fields; R_TREE * t; typedefDICTIONARY dict; ... }; Saves only Active Fields that dictate the result relation type Saves all the FIELDs we had so far DEF_V(e,EMPLOYEE[FIRST_N,LAST_N]); e /= (DEPT == dept);

  18. Type Checking template<typename Q, typename E> Qoperator/(Q& q, const E& e); • Make sure all of E’s FIELDS exist in Q’s dictionary • Fetch E’s FIELD types from Q. • Enables using the same field name with distinct types in different tables • Resolve e’stype. • It must be bool • Incorporate e’scontent, e.t, into q.t • Changes q’scontent but not it’s type • e has no compile time representation in the q

  19. Type Equivalence • Rat builds a Type for every query • This type relies on the FIELDs in the return relation • Problem: Schema FIELD order • Solution: __COUNTER__ • a unique ID for every FIELD in order to sort and achieve relation equivalence.

  20. Further Research • Extend expressive power • Updates to the database • Allow GroupBy • Challenge: nested relations • Embedding of other little languages in C++ • XML • Challenge: recursive types • AraRat in Java • Require compiler modifications • Relational Algebra calculus for collections of objects.

  21. Questions?

More Related