From e76ab3a1ac2487e0dcb16ecdfe0c6e53c3f1e86c Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Fri, 29 Mar 2013 18:51:07 +0200 Subject: Add test for custom-mapping SQL Server SQL_VARIANT type --- mssql/custom/custom.sql | 42 ++++++++++++++++ mssql/custom/driver.cxx | 11 +++++ mssql/custom/makefile | 6 +-- mssql/custom/test.hxx | 44 +++++++++++++++++ mssql/custom/traits.cxx | 129 ++++++++++++++++++++++++++++++++++++++++++++++++ mssql/custom/traits.hxx | 61 ++++++++++++++++++++++- 6 files changed, 289 insertions(+), 4 deletions(-) create mode 100644 mssql/custom/custom.sql create mode 100644 mssql/custom/traits.cxx diff --git a/mssql/custom/custom.sql b/mssql/custom/custom.sql new file mode 100644 index 0000000..44ef512 --- /dev/null +++ b/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/mssql/custom/driver.cxx b/mssql/custom/driver.cxx index 142d35c..ebaa790 100644 --- a/mssql/custom/driver.cxx +++ b/mssql/custom/driver.cxx @@ -30,6 +30,10 @@ main (int argc, char* 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)); @@ -65,6 +69,13 @@ main (int argc, char* argv[]) { transaction t (db->begin ()); + // Variant comparison. + // + { + result r (db->query (query::v == o.v)); + assert (!r.empty ()); + } + #if !defined(MSSQL_SERVER_VERSION) || MSSQL_SERVER_VERSION >= 1000 // Point comparison. // diff --git a/mssql/custom/makefile b/mssql/custom/makefile index 94be7b2..03dfd7d 100644 --- a/mssql/custom/makefile +++ b/mssql/custom/makefile @@ -4,7 +4,7 @@ include $(dir $(lastword $(MAKEFILE_LIST)))../../build/bootstrap.make -cxx_tun := driver.cxx +cxx_tun := driver.cxx traits.cxx odb_hdr := test.hxx cxx_obj := $(addprefix $(out_base)/,$(cxx_tun:.cxx=.o) $(odb_hdr:.hxx=-odb.o)) cxx_od := $(cxx_obj:.o=.o.d) @@ -37,7 +37,7 @@ $(gen): odb := $(odb) $(gen) $(dist): export odb_options += --database mssql --default-database \ common --generate-schema --generate-query --hxx-prologue \ '\#include "traits.hxx"' --hxx-prologue '\#include "query.hxx"' \ ---table-prefix mssql_custom_ +--sql-interlude ":r $(src_base)/custom.sql" --table-prefix mssql_custom_ $(gen): cpp_options := -I$(src_base) $(gen): $(common.l.cpp-options) @@ -52,7 +52,7 @@ $(out_base)/: $(driver) $(dist): sources := $(cxx_tun) $(dist): headers := $(odb_hdr) $(dist): export extra_headers := traits.hxx query.hxx -$(dist): data_dist := test.std +$(dist): data_dist := test.std custom.sql $(dist): export name := $(subst /,-,$(subst $(src_root)/mssql/,,$(src_base))) $(dist): export extra_dist := $(data_dist) $(name)-vc9.vcproj \ $(name)-vc10.vcxproj $(name)-vc10.vcxproj.filters \ diff --git a/mssql/custom/test.hxx b/mssql/custom/test.hxx index 6f44a77..7e6e81c 100644 --- a/mssql/custom/test.hxx +++ b/mssql/custom/test.hxx @@ -10,6 +10,46 @@ #include +// 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). +// +#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). @@ -55,6 +95,9 @@ struct object #pragma db id unsigned long id; + variant v; + std::vector vv; + #if !defined(MSSQL_SERVER_VERSION) || MSSQL_SERVER_VERSION >= 1000 point p; std::vector pv; @@ -67,6 +110,7 @@ struct object 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 diff --git a/mssql/custom/traits.cxx b/mssql/custom/traits.cxx new file mode 100644 index 0000000..80fe42f --- /dev/null +++ b/mssql/custom/traits.cxx @@ -0,0 +1,129 @@ +// file : mssql/types/traits.cxx +// copyright : Copyright (c) 2009-2013 Code Synthesis Tools CC +// license : GNU GPL v2; see accompanying LICENSE file + +#include "traits.hxx" + +using namespace std; + +namespace odb +{ + namespace mssql + { + void value_traits:: + 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 (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:: + 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 (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 (tmp_buf), *size); + + *buffer = tmp_buf; + *size = tmp_capacity; + break; + } + case chunk_last: + { + v.str_val.append (static_cast (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/mssql/custom/traits.hxx b/mssql/custom/traits.hxx index 421f288..a35fa49 100644 --- a/mssql/custom/traits.hxx +++ b/mssql/custom/traits.hxx @@ -12,12 +12,71 @@ #include -#include "test.hxx" // point +#include "test.hxx" // variant, point namespace odb { namespace mssql { + template <> + class value_traits + { + 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 + { + 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 -- cgit v1.1