From 1f14eecfed1303d5d7bf5febcba29e06c2d19d9e Mon Sep 17 00:00:00 2001 From: Constantin Michael Date: Wed, 26 Oct 2011 10:05:25 +0200 Subject: Quote schema object identifiers for Oracle where necessary Oracle converts all alphabetical characters in unquoted schema object identifiers to uppercase. This behaviour cannot be disabled. --- common/callback/driver.cxx | 7 +++ common/default/driver.cxx | 6 +++ common/erase-query/driver.cxx | 15 +++++++ common/query/driver.cxx | 20 +++++---- common/view/driver.cxx | 42 ++++++++++++++++-- common/view/test.hxx | 99 +++++++++++++++++++++++++++++++++++-------- 6 files changed, 161 insertions(+), 28 deletions(-) diff --git a/common/callback/driver.cxx b/common/callback/driver.cxx index 5f2c258..76dbda4 100644 --- a/common/callback/driver.cxx +++ b/common/callback/driver.cxx @@ -14,6 +14,7 @@ #include #include +#include // DATABASE_XXX #include "test.hxx" #include "test-odb.hxx" @@ -93,8 +94,14 @@ main (int argc, char* argv[]) typedef odb::result result; transaction t (db->begin ()); + +#ifndef DATABASE_ORACLE result r (db->query ( (query::id < 3) + "ORDER BY callback_object.id")); +#else + result r (db->query ( + (query::id < 3) + "ORDER BY \"callback_object\".\"id\"")); +#endif for (result::iterator i (r.begin ()); i != r.end (); ++i) { diff --git a/common/default/driver.cxx b/common/default/driver.cxx index 846619b..5fef85c 100644 --- a/common/default/driver.cxx +++ b/common/default/driver.cxx @@ -14,6 +14,7 @@ #include #include +#include // DATABASE_XXX #include "test.hxx" #include "test-odb.hxx" @@ -33,7 +34,12 @@ main (int argc, char* argv[]) // { transaction t (db->begin ()); + +#ifndef DATABASE_ORACLE db->execute ("INSERT INTO default_object (obj_id) VALUES (1)"); +#else + db->execute ("INSERT INTO \"default_object\" (\"obj_id\") VALUES (1)"); +#endif t.commit (); } diff --git a/common/erase-query/driver.cxx b/common/erase-query/driver.cxx index 40290ec..a80a252 100644 --- a/common/erase-query/driver.cxx +++ b/common/erase-query/driver.cxx @@ -13,6 +13,7 @@ #include #include +#include // DATABASE_XXX #include #include "test.hxx" @@ -62,8 +63,13 @@ main (int argc, char* argv[]) { transaction t (db->begin ()); +#ifndef DATABASE_ORACLE assert (db->erase_query ( "erase_query_object.id < 3") == 2); +#else + assert (db->erase_query ( + "\"erase_query_object\".\"id\" < 3") == 2); +#endif db->erase_query (); t.commit (); } @@ -155,12 +161,21 @@ main (int argc, char* argv[]) t.commit (); } +#ifndef DATABASE_ORACLE { transaction t (db->begin ()); assert (db->execute ("SELECT * FROM erase_query_object_v " "WHERE object_id = 1") == 0); t.commit (); } +#else + { + transaction t (db->begin ()); + assert (db->execute ("SELECT * FROM \"erase_query_object_v\" " + "WHERE \"object_id\" = 1") == 0); + t.commit (); + } +#endif } catch (const odb::exception& e) { diff --git a/common/query/driver.cxx b/common/query/driver.cxx index 2f97f2e..283ea12 100644 --- a/common/query/driver.cxx +++ b/common/query/driver.cxx @@ -108,7 +108,7 @@ main (int argc, char* argv[]) cout << "test 002" << endl; { transaction t (db->begin ()); - result r (db->query ("ORDER BY age")); + result r (db->query ("ORDER BY" + query::age)); for (result::iterator i (r.begin ()); i != r.end (); ++i) { @@ -132,7 +132,10 @@ main (int argc, char* argv[]) cout << "test 003" << endl; { transaction t (db->begin ()); - result r (db->query ("age >= 30 AND last = 'Doe'")); + result r ( + db->query (query::age + " >= 30 AND " + + query::last_name + " = 'Doe'")); + print (r); t.commit (); } @@ -146,9 +149,8 @@ main (int argc, char* argv[]) const char* name = "Doe"; result r ( - db->query ( - "age >= " + query::_ref (30) + "AND" + - "last = " + query::_val (name))); + db->query (query::age + " >= " + query::_ref (30) + "AND " + + query::last_name + " = " + query::_val (name))); print (r); t.commit (); @@ -163,8 +165,8 @@ main (int argc, char* argv[]) string name; unsigned short age; - query q ("age >= " + query::_ref (age) + "AND" + - "last = " + query::_ref (name)); + query q (query::age + " >= " + query::_ref (age) + "AND" + + query::last_name + " = " + query::_ref (name)); name = "Doe"; age = 30; @@ -343,7 +345,9 @@ main (int argc, char* argv[]) // + // - r = db->query ((query::last_name == "Doe") + "ORDER BY age"); + r = db->query ((query::last_name == "Doe") + + "ORDER BY" + + query::age); print (r); t.commit (); diff --git a/common/view/driver.cxx b/common/view/driver.cxx index 2ede99c..b671e46 100644 --- a/common/view/driver.cxx +++ b/common/view/driver.cxx @@ -14,6 +14,7 @@ #include #include +#include // DATABASE_XXX #include "test.hxx" #include "test-odb.hxx" @@ -58,7 +59,8 @@ view2_test (const auto_ptr& db) } { - result r (db->query ("age < 31")); + result r (db->query (query::age + " < 31")); + iterator i (r.begin ()); assert (i != r.end ()); assert (i->count == 2); @@ -85,7 +87,10 @@ view4_test (const auto_ptr& db) transaction t (db->begin ()); { - result r (db->query ("age > 30 ORDER BY age")); + result r (db->query ( + (query::person::age > 30) + + "ORDER BY " + + query::person::age)); iterator i (r.begin ()); @@ -102,7 +107,9 @@ view4_test (const auto_ptr& db) { result r (db->query ( - (query::person::age > 30) + "ORDER BY age")); + (query::person::age > 30) + + "ORDER BY " + + query::person::age)); iterator i (r.begin ()); @@ -229,12 +236,20 @@ main (int argc, char* argv[]) } { +#ifndef DATABASE_ORACLE result r (db->query ("ORDER BY age")); +#else + result r (db->query ("ORDER BY \"age\"")); +#endif assert (size (r) == 4); } { +#ifndef DATABASE_ORACLE result r (db->query ("age < 31 ORDER BY age")); +#else + result r (db->query ("\"age\" < 31 ORDER BY \"age\"")); +#endif view1_check (r); } @@ -277,7 +292,12 @@ main (int argc, char* argv[]) { transaction t (db->begin ()); +#ifndef DATABASE_ORACLE result r (db->query ("first = " + query::_val ("Jane"))); +#else + result r (db->query ("\"first\" = " + query::_val ("Jane"))); +#endif + result::iterator i (r.begin ()); assert (i != r.end ()); @@ -298,11 +318,19 @@ main (int argc, char* argv[]) { transaction t (db->begin ()); +#ifndef DATABASE_ORACLE result r ( db->query ( "SELECT first, last, age " "FROM t_view_person " "WHERE age < 31 ORDER BY age")); +#else + result r ( + db->query ( + "SELECT \"first\", \"last\", \"age\" " + "FROM \"t_view_person\" " + "WHERE \"age\" < 31 ORDER BY \"age\"")); +#endif view1_check (r); @@ -320,7 +348,11 @@ main (int argc, char* argv[]) transaction t (db->begin ()); { +#ifndef DATABASE_ORACLE result r (db->query ("age < 31 ORDER BY age")); +#else + result r (db->query ("\"age\" < 31 ORDER BY \"age\"")); +#endif view1_check (r); } @@ -438,7 +470,11 @@ main (int argc, char* argv[]) db, odb::query::employer::name == "Simple Tech, Inc"); view6_test ( +#ifndef DATABASE_ORACLE db, "e.name = " + odb::query::_val ("Simple Tech, Inc")); +#else + db, "\"e\".\"name\" = " + odb::query::_val ("Simple Tech, Inc")); +#endif // view7 // diff --git a/common/view/test.hxx b/common/view/test.hxx index 5efbd1c..dee121c 100644 --- a/common/view/test.hxx +++ b/common/view/test.hxx @@ -13,6 +13,8 @@ #include #include +#include // DATABASE_XXX + struct employer; #pragma db object @@ -130,7 +132,12 @@ struct employer // Complete suffix query template. // -#pragma db view query("SELECT first, last, age FROM t_view_person") +#ifndef DATABASE_ORACLE +# pragma db view query("SELECT first, last, age FROM t_view_person") +#else +# pragma db view query("SELECT \"first\", \"last\", \"age\" " \ + "FROM \"t_view_person\"") +#endif struct view1 { std::string first; @@ -140,9 +147,15 @@ struct view1 // Complete query. // -#pragma db view query("SELECT first, last, age " \ - "FROM t_view_person " \ - "WHERE age < 31 ORDER BY age") +#ifndef DATABASE_ORACLE +# pragma db view query("SELECT first, last, age " \ + "FROM t_view_person " \ + "WHERE age < 31 ORDER BY age") +#else +# pragma db view query("SELECT \"first\", \"last\", \"age\" " \ + "FROM \"t_view_person\" " \ + "WHERE \"age\" < 31 ORDER BY \"age\"") +#endif struct view1a { std::string first; @@ -152,9 +165,15 @@ struct view1a // Complete placeholder query template. // -#pragma db view query("SELECT first, last, age " \ - "FROM t_view_person " \ - "WHERE age < 31 AND (?) ORDER BY age") +#ifndef DATABASE_ORACLE +# pragma db view query("SELECT first, last, age " \ + "FROM t_view_person " \ + "WHERE age < 31 AND (?) ORDER BY age") +#else +# pragma db view query("SELECT \"first\", \"last\", \"age\" " \ + "FROM \"t_view_person\" " \ + "WHERE \"age\" < 31 AND (?) ORDER BY \"age\"") +#endif struct view1b { std::string first; @@ -193,8 +212,13 @@ struct view1d // Complete suffix query. // -#pragma db view object(person) \ +#ifndef DATABASE_ORACLE +# pragma db view object(person) \ query("SELECT count(id) FROM t_view_person") +#else +# pragma db view object(person) \ + query("SELECT count(\"id\") FROM \"t_view_person\"") +#endif struct view2 { std::size_t count; @@ -205,7 +229,11 @@ struct view2 #pragma db view object(person) struct view2a { +#ifndef DATABASE_ORACLE #pragma db column("count(id)") +#else + #pragma db column("count(\"id\")") +#endif std::size_t count; }; @@ -214,7 +242,11 @@ struct view2a #pragma db view object(person) struct view2b { +#ifndef DATABASE_ORACLE #pragma db column("count(t_view_person.id)") +#else + #pragma db column("count(\"t_view_person\".\"id\")") +#endif std::size_t count; }; @@ -233,10 +265,17 @@ struct view2c // Complete suffix query. // -#pragma db view object(person = test) \ - query("SELECT last, count(last) " \ - "FROM t_view_person " \ +#ifndef DATABASE_ORACLE +# pragma db view object(person = test) \ + query("SELECT last, count(last) " \ + "FROM t_view_person " \ "GROUP BY last") +#else +# pragma db view object(person = test) \ + query("SELECT \"last\", count(\"last\") " \ + "FROM \"t_view_person\" " \ + "GROUP BY \"last\"") +#endif struct view3 { std::string last_name; @@ -263,11 +302,19 @@ struct view3a // Complete suffix query. // -#pragma db view object(person) object(country = residence) \ - query("SELECT first, last, residence.name " \ - "FROM t_view_person " \ - "LEFT JOIN t_view_country AS residence " \ +#ifndef DATABASE_ORACLE +# pragma db view object(person) object(country = residence) \ + query("SELECT first, last, residence.name " \ + "FROM t_view_person " \ + "LEFT JOIN t_view_country AS residence " \ "ON t_view_person.residence = residence.code") +#else +# pragma db view object(person) object(country = residence) \ + query("SELECT \"first\", \"last\", \"residence\".\"name\" " \ + "FROM \"t_view_person\" " \ + "LEFT JOIN \"t_view_country\" \"residence\" " \ + "ON \"t_view_person\".\"residence\" = \"residence\".\"code\"") +#endif struct view4 { std::string first_name; @@ -347,18 +394,36 @@ struct view6b // The same using tables. // -#pragma db view table("t_view_person" = "p") \ +#ifndef DATABASE_ORACLE +# pragma db view table("t_view_person" = "p") \ table("t_view_employer_employees" = "ee": "ee.value = p.id") \ table("t_view_employer" = "e": "ee.object_id = e.name") +#else +# pragma db view table("t_view_person" = "p") \ + table("t_view_employer_employees" = "ee": "\"ee\".\"value\" = \"p\".\"id\"")\ + table("t_view_employer" = "e": "\"ee\".\"object_id\" = \"e\".\"name\"") +#endif struct view6c { +#ifndef DATABASE_ORACLE #pragma db column("p.first") +#else + #pragma db column("\"p\".\"first\"") +#endif std::string first_name; - #pragma db column("p"."last") +#ifndef DATABASE_ORACLE + #pragma db column("p.last") +#else + #pragma db column("\"p\".\"last\"") +#endif std::string last_name; +#ifndef DATABASE_ORACLE #pragma db column("e.name") +#else + #pragma db column("\"e\".\"name\"") +#endif std::string employer; }; -- cgit v1.1