diff options
Diffstat (limited to 'odb-tests/mssql/custom')
| -rw-r--r-- | odb-tests/mssql/custom/custom.sql | 42 | ||||
| -rw-r--r-- | odb-tests/mssql/custom/driver.cxx | 135 | ||||
| -rw-r--r-- | odb-tests/mssql/custom/query.hxx | 183 | ||||
| -rw-r--r-- | odb-tests/mssql/custom/test.hxx | 121 | ||||
| -rw-r--r-- | odb-tests/mssql/custom/traits.cxx | 128 | ||||
| -rw-r--r-- | odb-tests/mssql/custom/traits.hxx | 148 |
6 files changed, 757 insertions, 0 deletions
diff --git a/odb-tests/mssql/custom/custom.sql b/odb-tests/mssql/custom/custom.sql new file mode 100644 index 0000000..44ef512 --- /dev/null +++ b/odb-tests/mssql/custom/custom.sql @@ -0,0 +1,42 @@ +/* This file contains helper functions. + */ + +IF OBJECT_ID('dbo.variant_to_string', 'FN') IS NOT NULL + DROP FUNCTION dbo.variant_to_string; +GO + +IF OBJECT_ID('dbo.string_to_variant', 'FN') IS NOT NULL + DROP FUNCTION dbo.string_to_variant; +GO + +CREATE FUNCTION dbo.variant_to_string (@val SQL_VARIANT) RETURNS VARCHAR(max) +AS +BEGIN + RETURN CAST(SQL_VARIANT_PROPERTY(@val, 'BaseType') AS SYSNAME) + ' ' + + CAST(@val AS VARCHAR(max)) +END; +GO + +CREATE FUNCTION dbo.string_to_variant (@val VARCHAR(max)) RETURNS SQL_VARIANT +AS +BEGIN + DECLARE @ret SQL_VARIANT + + DECLARE @pos BIGINT + DECLARE @vtype SYSNAME + DECLARE @vtext VARCHAR(max) + + SET @pos = CHARINDEX(' ', @val) + SET @vtype = SUBSTRING(@val, 1, @pos - 1) + SET @vtext = SUBSTRING(@val, @pos + 1, LEN(@val)) + + IF @vtype = 'tinyint' SET @ret = CAST(@vtext AS TINYINT) + ELSE IF @vtype = 'smallint' SET @ret = CAST(@vtext AS SMALLINT) + ELSE IF @vtype = 'int' SET @ret = CAST(@vtext AS INT) + ELSE IF @vtype = 'bigint' SET @ret = CAST(@vtext AS BIGINT) + ELSE IF @vtype = 'char' SET @ret = CAST(@vtext AS CHAR(8000)) + ELSE IF @vtype = 'varchar' SET @ret = CAST(@vtext AS VARCHAR(8000)) + + RETURN @ret +END; +GO diff --git a/odb-tests/mssql/custom/driver.cxx b/odb-tests/mssql/custom/driver.cxx new file mode 100644 index 0000000..bde7eb6 --- /dev/null +++ b/odb-tests/mssql/custom/driver.cxx @@ -0,0 +1,135 @@ +// file : mssql/custom/driver.cxx +// license : GNU GPL v2; see accompanying LICENSE file + +// Test custom database type mapping in SQL Server. +// + +#include <memory> // std::auto_ptr +#include <cassert> +#include <iostream> + +#include <odb/mssql/database.hxx> +#include <odb/mssql/transaction.hxx> + +#include <common/common.hxx> + +#include "test.hxx" +#include "test-odb.hxx" + +using namespace std; +namespace mssql = odb::mssql; +using namespace mssql; + +int +main (int argc, char* argv[]) +{ + try + { + auto_ptr<database> db (create_specific_database<database> (argc, argv)); + + object o (1); + + o.v = variant (123); + o.vv.push_back (variant (string (1024, 'a'))); + o.vv.push_back (variant (123)); + +#if !defined(MSSQL_SERVER_VERSION) || MSSQL_SERVER_VERSION >= 1000 + o.p = point (1.1111, 2222222222.2); + o.pv.push_back (point (1.1234, 2.2345)); + o.pv.push_back (point (3.3456, 4.4567)); + o.pv.push_back (point (0.0000001, 0.000000001)); // Scientific notation. +#endif + + o.xml = "<root x=\"1\"><a>AAA</a><b>BBB</b><c>CCC</c></root>"; + + // Persist. + // + { + transaction t (db->begin ()); + db->persist (o); + t.commit (); + } + + // Load. + // + { + transaction t (db->begin ()); + auto_ptr<object> o1 (db->load<object> (1)); + t.commit (); + + assert (o == *o1); + } + + // Query. + // + typedef mssql::query<object> query; + typedef odb::result<object> result; + + { + transaction t (db->begin ()); + + // Variant comparison. + // + { + result r (db->query<object> (query::v == o.v)); + assert (!r.empty ()); + } + +#if !defined(MSSQL_SERVER_VERSION) || MSSQL_SERVER_VERSION >= 1000 + // Point comparison. + // + { + result r (db->query<object> (query::p == o.p)); + assert (!r.empty ()); + } + + // Point comparison using native query. + // + { + result r (db->query<object> ( + query::p + ".STEquals(" + query::_val (o.p) + ") = 1")); + assert (!r.empty ()); + } + + // Access to individual members. + // + { + result r (db->query<object> (query::p.x == o.p.x)); + assert (!r.empty ()); + } +#endif + + t.commit (); + } + + // Update. + // +#if !defined(MSSQL_SERVER_VERSION) || MSSQL_SERVER_VERSION >= 1000 + o.p.x++; + o.p.y--; + o.pv[1].x--; + o.pv[1].y++; +#endif + + o.xml = "<root x=\"2\"><a>BBB</a><b>CCC</b><c>DDD</c></root>"; + + { + transaction t (db->begin ()); + db->update (o); + t.commit (); + } + + { + transaction t (db->begin ()); + auto_ptr<object> o1 (db->load<object> (1)); + t.commit (); + + assert (o == *o1); + } + } + catch (const odb::exception& e) + { + cerr << e.what () << endl; + return 1; + } +} diff --git a/odb-tests/mssql/custom/query.hxx b/odb-tests/mssql/custom/query.hxx new file mode 100644 index 0000000..fc63378 --- /dev/null +++ b/odb-tests/mssql/custom/query.hxx @@ -0,0 +1,183 @@ +// file : mssql/custom/query.hxx +// license : GNU GPL v2; see accompanying LICENSE file + +#ifndef QUERY_HXX +#define QUERY_HXX + +#include <string> + +#include <odb/mssql/query.hxx> + +#include "test.hxx" // point + +namespace odb +{ + namespace mssql + { +#if !defined(MSSQL_SERVER_VERSION) || MSSQL_SERVER_VERSION >= 1000 + template <> + struct query_column<point, id_string> + { + private: + const char* table_; + const char* column_; + const char* conversion_; + + unsigned short prec_; + unsigned short scale_; + + std::string x_column_; + std::string y_column_; + + // Sub-columns for individual members. + // + public: + query_column<double, id_float8> x, y; + + // is_null, is_not_null + // + public: + query_base + is_null () const + { + query_base q (table_, column_); + q += "IS NULL"; + return q; + } + + query_base + is_not_null () const + { + query_base q (table_, column_); + q += "IS NOT NULL"; + return q; + } + + // = + // + public: + query_base + equal (const point& v) const + { + return equal (val_bind<point> (v)); + } + + query_base + equal (val_bind<point> v) const + { + query_base q (table_, column_); + q += ".STEquals("; + q.append<point, id_string> (v, conversion_); + q += ") = 1"; + return q; + } + + query_base + equal (ref_bind<point> r) const + { + query_base q (table_, column_); + q += ".STEquals("; + q.append<point, id_string> (r, conversion_); + q += ") = 1"; + return q; + } + + friend query_base + operator== (const query_column& c, const point& v) + { + return c.equal (v); + } + + friend query_base + operator== (const point& v, const query_column& c) + { + return c.equal (v); + } + + friend query_base + operator== (const query_column& c, val_bind<point> v) + { + return c.equal (v); + } + + friend query_base + operator== (val_bind<point> v, const query_column& c) + { + return c.equal (v); + } + + friend query_base + operator== (const query_column& c, ref_bind<point> r) + { + return c.equal (r); + } + + friend query_base + operator== (ref_bind<point> r, const query_column& c) + { + return c.equal (r); + } + + // Column comparison. + // + public: + query_base + operator== (const query_column<point, id_string>& c) const + { + query_base q (table_, column_); + q += ".STEquals("; + q.append (c.table (), c.column ()); + q += ") = 1"; + return q; + } + + public: + query_column (const char* table, + const char* column, + const char* conv, + unsigned short prec = 0, + unsigned short scale = 0xFFFF) + : table_ (table), column_ (column), conversion_ (conv), + prec_ (prec), scale_ (scale), + x_column_ (std::string (column) + ".STX"), + y_column_ (std::string (column) + ".STY"), + x (table, x_column_.c_str (), 0), + y (table, y_column_.c_str (), 0) + { + } + + const char* + table () const + { + return table_; + } + + const char* + column () const + { + return column_; + } + + const char* + conversion () const + { + return conversion_; + } + + unsigned short + prec () const + { + return prec_; + } + + unsigned short + scale () const + { + return scale_; + } + }; +#endif // SQL Server > 2005 + } +} + +#endif // QUERY_HXX diff --git a/odb-tests/mssql/custom/test.hxx b/odb-tests/mssql/custom/test.hxx new file mode 100644 index 0000000..4b8a5d7 --- /dev/null +++ b/odb-tests/mssql/custom/test.hxx @@ -0,0 +1,121 @@ +// file : mssql/types/test.hxx +// license : GNU GPL v2; see accompanying LICENSE file + +#ifndef TEST_HXX +#define TEST_HXX + +#include <string> +#include <vector> + +#include <odb/core.hxx> + +// Map SQL Server SQL_VARIANT type to our variant C++ class that is capable +// of storing either an integer or a string (QVariant and boost::variant +// would be natural alternatives to our own type). The SQL Server functions +// that are used in the 'to' and 'from' expressions below are defined in +// the custom.sql file. The other half of this mapping is in traits.hxx +// (value_traits<variant, id_long_string>). +// +#pragma db map type("SQL_VARIANT") \ + as("VARCHAR(max)") \ + to("dbo.string_to_variant((?))") \ + from("dbo.variant_to_string((?))") + +#pragma db value type("SQL_VARIANT") +struct variant +{ + variant (unsigned long v = 0): val_type (type_int), int_val (v) {} + variant (const std::string& v): val_type (type_str), str_val (v) {} + + enum {type_int, type_str} val_type; + unsigned long int_val; + std::string str_val; +}; + +inline bool +operator== (const variant& a, const variant& b) +{ + if (a.val_type != b.val_type) + return false; + + switch (a.val_type) + { + case variant::type_int: + return a.int_val == b.int_val; + case variant::type_str: + return a.str_val == b.str_val; + } + + return false; +} + +#if !defined(MSSQL_SERVER_VERSION) || MSSQL_SERVER_VERSION >= 1000 +// Map GEOMETRY SQL Server type to the point C++ struct. The other half +// of this mapping is in traits.hxx (value_traits<point, id_string>). +// Note that GEOMETRY is not available in SQL Server 2005. +// +#pragma db map type("GEOMETRY") \ + as("VARCHAR(256)") \ + to("GEOMETRY::STGeomFromText((?), 0)") \ + from("(?).STAsText()") + +#pragma db value type("GEOMETRY") +struct point +{ + point () {} + point (double x_, double y_): x (x_), y (y_) {} + + double x; + double y; +}; + +inline bool +operator== (const point& a, const point& b) +{ + return a.x == b.x && a.y == b.y; +} +#endif // SQL Server > 2005 + +// Map XML SQL Server type to std::string (or any other type that provides +// the value_traits<?, id_long_string> specialization). Note also that +// another alternative would be to interface with the XML data type using +// VARBINARY or NVARCHAR. Here we use implicit string to/from XML conversion, +// however, CAST/CONVERT can be used instead for greater control over +// whitespace handling, etc. +// +#pragma db map type("XML *(\\(.+\\))?") as("VARCHAR(max)") + +#pragma db object +struct object +{ + object () {} + object (unsigned long id_) : id (id_) {} + + #pragma db id + unsigned long id; + + variant v; + std::vector<variant> vv; + +#if !defined(MSSQL_SERVER_VERSION) || MSSQL_SERVER_VERSION >= 1000 + point p; + std::vector<point> pv; +#endif + + #pragma db type("XML") + std::string xml; + + bool + operator== (const object& y) const + { + return id == y.id + && vv == y.vv +#if !defined(MSSQL_SERVER_VERSION) || MSSQL_SERVER_VERSION >= 1000 + && p == y.p + && pv == y.pv +#endif + && xml == y.xml; + } +}; + +#endif // TEST_HXX diff --git a/odb-tests/mssql/custom/traits.cxx b/odb-tests/mssql/custom/traits.cxx new file mode 100644 index 0000000..3f14ae7 --- /dev/null +++ b/odb-tests/mssql/custom/traits.cxx @@ -0,0 +1,128 @@ +// file : mssql/types/traits.cxx +// license : GNU GPL v2; see accompanying LICENSE file + +#include "traits.hxx" + +using namespace std; + +namespace odb +{ + namespace mssql + { + void value_traits<variant, id_long_string>:: + param_callback (const void* context, + size_t*, + const void** buffer, + size_t* size, + chunk_type* chunk, + void* tmp_buf, + size_t tmp_capacity) + { + const variant& v (*static_cast<const variant*> (context)); + string str; + + switch (v.val_type) + { + case variant::type_int: + { + ostringstream os; + os << v.int_val; + + str = "bigint "; + str += os.str (); + break; + } + case variant::type_str: + { + str = "varchar "; + str += v.str_val; + break; + } + } + + // Here we assume that the temoprary buffer is large enough to fit + // the whole string in one go. If that were not the case, then we + // would have had to chunk it. + // + assert (tmp_capacity >= str.size ()); + memcpy (tmp_buf, str.c_str (), str.size ()); + + *buffer = tmp_buf; + *size = str.size (); + *chunk = chunk_one; + } + + void value_traits<variant, id_long_string>:: + result_callback (void* context, + size_t*, + void** buffer, + size_t* size, + chunk_type chunk, + size_t, + void* tmp_buf, + size_t tmp_capacity) + { + variant& v (*static_cast<variant*> (context)); + + switch (chunk) + { + case chunk_null: + case chunk_one: + { + assert (false); // The value cannot be NULL or empty. + break; + } + case chunk_first: + { + // Use the variant's string value as a temporary buffer. If this + // were not possible, we could have allocated one as part of + // context. + // + v.str_val.clear (); + + *buffer = tmp_buf; + *size = tmp_capacity; + break; + } + case chunk_next: + { + v.str_val.append (static_cast<char*> (tmp_buf), *size); + + *buffer = tmp_buf; + *size = tmp_capacity; + break; + } + case chunk_last: + { + v.str_val.append (static_cast<char*> (tmp_buf), *size); + + // Figure out what we've got. + // + string::size_type p (v.str_val.find (' ')); + assert (p != string::npos); // Must have type followed by value. + string type (v.str_val, 0, p); + string text (v.str_val, p + 1, string::npos); + + if (type == "tinyint" || + type == "smallint" || + type == "int" || + type == "bigint") + { + istringstream is (text); + is >> v.int_val; + v.val_type = variant::type_int; + } + else if (type == "char" || type == "varchar") + { + v.str_val = text; + v.val_type = variant::type_str; + } + else + assert (false); // Unknown type. + + break; + } + } + } + } +} diff --git a/odb-tests/mssql/custom/traits.hxx b/odb-tests/mssql/custom/traits.hxx new file mode 100644 index 0000000..2bd99cb --- /dev/null +++ b/odb-tests/mssql/custom/traits.hxx @@ -0,0 +1,148 @@ +// file : mssql/types/traits.hxx +// license : GNU GPL v2; see accompanying LICENSE file + +#ifndef TRAITS_HXX +#define TRAITS_HXX + +#include <limits> // std::numeric_limits +#include <sstream> +#include <cstring> // std::memcpy +#include <cassert> + +#include <odb/mssql/traits.hxx> + +#include "test.hxx" // variant, point + +namespace odb +{ + namespace mssql + { + template <> + class value_traits<variant, id_long_string> + { + public: + typedef variant value_type; + typedef variant query_type; + typedef long_callback image_type; + + static void + set_value (variant& v, + result_callback_type& cb, + void*& context) + { + cb = &result_callback; + context = &v; + } + + static void + set_image (param_callback_type& cb, + const void*& context, + bool& is_null, + const variant& v) + { + is_null = false; + cb = ¶m_callback; + context = &v; + } + + static void + param_callback (const void* context, + std::size_t* position, + const void** buffer, + std::size_t* size, + chunk_type* chunk, + void* tmp_buffer, + std::size_t tmp_capacity); + + static void + result_callback (void* context, + std::size_t* position, + void** buffer, + std::size_t* size, + chunk_type chunk, + std::size_t size_left, + void* tmp_buffer, + std::size_t tmp_capacity); + }; + + template <> + struct type_traits<variant> + { + static const database_type_id db_type_id = id_long_string; + + struct conversion + { + static const char* to () {return "dbo.string_to_variant((?))";} + }; + }; + +#if !defined(MSSQL_SERVER_VERSION) || MSSQL_SERVER_VERSION >= 1000 + template <> + class value_traits<point, id_string> + { + public: + typedef point value_type; + typedef point query_type; + + typedef char* image_type; + + static void + set_value (point& v, + const char* b, + std::size_t n, + bool is_null) + { + if (is_null) + v = point (); + else + { + // Point format is "POINT (x y)". + // + std::istringstream is (std::string (b + 7, n - 7)); + + is >> v.x; + is >> v.y; + } + } + + static void + set_image (char* b, + std::size_t c, + std::size_t& n, + bool& is_null, + const point& v) + { + is_null = false; + std::ostringstream os; + + // The formula for the number of decimla digits required is given in: + // + // http://www.open-std.org/JTC1/SC22/WG21/docs/papers/2005/n1822.pdf + // + os.precision (std::numeric_limits<double>::digits10); + // os.precision (2 + std::numeric_limits<double>::digits * 301/1000); + + os << "POINT (" << v.x << ' ' << v.y << ')'; + + const std::string& s (os.str ()); + n = s.size (); + assert (n <= c); + std::memcpy (b, s.c_str (), n); + } + }; + + template <> + struct type_traits<point> + { + static const database_type_id db_type_id = id_string; + + struct conversion + { + static const char* to () {return "GEOMETRY::STGeomFromText((?), 0)";} + }; + }; +#endif // SQL Server > 2005 + } +} + +#endif // TRAITS_HXX |