The purpose of Poco::Data::SqLite::Utility ??

[bradphelan]

I don't fully understand the purpose of the below

Code: Select all


----------------------
external/libpoco/Data/SQLite/src/Utility.cpp
----------------------
55 Utility::Utility()
56 {
57     Poco::FastMutex::ScopedLock l(_mutex);
58 
59     if (_types.empty())
60     {
61         _types.insert(TypeMap::value_type("", MetaColumn::FDT_STRING));
62         _types.insert(TypeMap::value_type("BOOL", MetaColumn::FDT_BOOL));
63         _types.insert(TypeMap::value_type("BOOLEAN", MetaColumn::FDT_BOOL));
64         _types.insert(TypeMap::value_type("BIT", MetaColumn::FDT_BOOL));
65         _types.insert(TypeMap::value_type("UINT8", MetaColumn::FDT_UINT8));
66         _types.insert(TypeMap::value_type("UTINY", MetaColumn::FDT_UINT8));
67         _types.insert(TypeMap::value_type("UINTEGER8", MetaColumn::FDT_UINT8));
68         _types.insert(TypeMap::value_type("INT8", MetaColumn::FDT_INT8));
69         _types.insert(TypeMap::value_type("TINY", MetaColumn::FDT_INT8));
70         _types.insert(TypeMap::value_type("INTEGER8", MetaColumn::FDT_INT8));
71         _types.insert(TypeMap::value_type("UINT16", MetaColumn::FDT_UINT16));
72         _types.insert(TypeMap::value_type("USHORT", MetaColumn::FDT_UINT16));
73         _types.insert(TypeMap::value_type("UINTEGER16", MetaColumn::FDT_UINT16));
74         _types.insert(TypeMap::value_type("INT16", MetaColumn::FDT_INT16));
75         _types.insert(TypeMap::value_type("SHORT", MetaColumn::FDT_INT16));
76         _types.insert(TypeMap::value_type("INTEGER16", MetaColumn::FDT_INT16));
77         _types.insert(TypeMap::value_type("UINT", MetaColumn::FDT_UINT32));
78         _types.insert(TypeMap::value_type("UINT32", MetaColumn::FDT_UINT32));
79         _types.insert(TypeMap::value_type("UINTEGER32", MetaColumn::FDT_UINT32));
80         _types.insert(TypeMap::value_type("INT", MetaColumn::FDT_INT32));
81         _types.insert(TypeMap::value_type("INT32", MetaColumn::FDT_INT32));
82         _types.insert(TypeMap::value_type("INTEGER", MetaColumn::FDT_INT32));
83         _types.insert(TypeMap::value_type("INTEGER32", MetaColumn::FDT_INT32));
84         _types.insert(TypeMap::value_type("UINT64", MetaColumn::FDT_UINT64));
85         _types.insert(TypeMap::value_type("ULONG", MetaColumn::FDT_INT64));
86         _types.insert(TypeMap::value_type("UINTEGER64", MetaColumn::FDT_UINT64));
87         _types.insert(TypeMap::value_type("INT64", MetaColumn::FDT_INT64));
88         _types.insert(TypeMap::value_type("LONG", MetaColumn::FDT_INT64));
89         _types.insert(TypeMap::value_type("INTEGER64", MetaColumn::FDT_INT64));
90         _types.insert(TypeMap::value_type("COUNTER", MetaColumn::FDT_UINT64));
91         _types.insert(TypeMap::value_type("AUTOINCREMENT", MetaColumn::FDT_UINT64));
92         _types.insert(TypeMap::value_type("REAL", MetaColumn::FDT_DOUBLE));
93         _types.insert(TypeMap::value_type("FLOA", MetaColumn::FDT_DOUBLE));
94         _types.insert(TypeMap::value_type("FLOAT", MetaColumn::FDT_DOUBLE));
95         _types.insert(TypeMap::value_type("DOUB", MetaColumn::FDT_DOUBLE));
96         _types.insert(TypeMap::value_type("DOUBLE", MetaColumn::FDT_DOUBLE));
97         _types.insert(TypeMap::value_type("CHAR", MetaColumn::FDT_STRING));
98         _types.insert(TypeMap::value_type("CLOB", MetaColumn::FDT_STRING));
99         _types.insert(TypeMap::value_type("TEXT", MetaColumn::FDT_STRING));
100         _types.insert(TypeMap::value_type("VARCHAR", MetaColumn::FDT_STRING));
101         _types.insert(TypeMap::value_type("BLOB", MetaColumn::FDT_BLOB));
102     }
103 }
104 
105 


It doesn't seem to be used in the Extractor.cpp to detect the datatype.

Code: Select all

----------------------
external/libpoco/Data/SQLite/src/Extractor.cpp
----------------------
62 bool Extractor::extract(std::size_t pos, Poco::Int32& val)
63 {
64     if (isNull(pos))
65         return false;
66     val = sqlite3_column_int(_pStmt, (int) pos);
67     return true;
68 }


which is called from the type handlers with no reference to the above table ( that I can see ).

The problem I am having is that I am trying to achieve a compatible schema between a MySQL db and a SQLite db. SQLite uses dynamic typing but is happy to accept any column type that has the string INT in it as nominally INTEGER. MySQL is strictly typed and if I want 64bits I need to set the column type as

BIGINT

Because this has INT in it SQLite is happy. However as BIGINT does not appear in the above table I get a cryptic Poco::NotFoundException thrown exception.

Should I add all the MySQL datatypes to the above table? Or should I do something else?

Brad

[bradphelan]

I think the problem is that the system throws an exception when there is an alternative. In the equivalent MySQL code

Code: Select all

----------------------
external/libpoco/Data/MySQL/src/ResultMetadata.cpp
----------------------
170         return Poco::Data::MetaColumn::FDT_UNKNOWN;


is thrown. However in the SQLite code

Code: Select all

----------------------
external/libpoco/Data/SQLite/src/Utility.cpp
----------------------
112 MetaColumn::ColumnDataType Utility::getColumnType(sqlite3_stmt* pStmt, std::size_t pos)
113 {
114     poco_assert_dbg (pStmt);
115 
116     static Utility u;
117     
118     const char* pc = sqlite3_column_decltype(pStmt, (int) pos);
119     std::string sqliteType = pc ? pc : "";
120     Poco::toUpperInPlace(sqliteType);
121     sqliteType = sqliteType.substr(0, sqliteType.find_first_of(" ("));
122 
123     TypeMap::const_iterator it = _types.find(Poco::trimInPlace(sqliteType));
124     if (_types.end() == it) throw Poco::NotFoundException();
125 
126     return it->second;
127 }


Poco::NotFoundException is thrown.

Given that the getColumnType method with SQLite is completely unreliable with SQlite given that sqlite is dynamically typed
I will changed the above code to

Code: Select all

----------------------
external/libpoco/Data/SQLite/src/Utility.cpp
----------------------
122 
123     TypeMap::const_iterator it = _types.find(Poco::trimInPlace(sqliteType));
124     if (_types.end() == it) return Poco::Data::MetaColumn::FDT_UNKNOWN;
125 
126     return it->second;
127 }


Does this seem like a bad thing?

Brad

[alex]

It doesn't seem to be used in the Extractor.cpp to detect the datatype.

it is used in

Code: Select all

bool Extractor::extract(std::size_t pos, Poco::Any& val)

[alex]

Does this seem like a bad thing?

If you ever attempt to extract into Any, you'll get UnknownTypeException.

[bradphelan]

Ok. I see. But how did you come up with the list of valid data types? Are they documented somewhere other than that source file. If I google

SQL UINTEGER64

I don't get any relevant hits so it doesn't seem to be any standard SQL naming convention. Perhaps it would be better to use a list of MySQL datatypes as far as they are compatible with the nominal column typing for SQLite or just add the MySQL data types to the list. Given I need BIGINT for MySQL and anything with INT in it will do for SQLite, UINTEGER64, INT64, UINT64, LONG don't make me compatible.

B

[alex]

http://sourceforge.net/tracker/index.ph ... tid=725712
http://sourceforge.net/tracker/index.ph ... tid=725712

[bradphelan]

Sorry but I don't fully understand the outcomes of the two links given. I can't figure out whether it will occur that there will be a common datatype specification between all drivers or that that the proposal has been rejected.

B

[alex]

I can't figure out whether it will occur that there will be a common datatype specification between all drivers or that that the proposal has been rejected.

Neither. They both refer to SQLite only

[bradphelan]

I can't figure out whether it will occur that there will be a common datatype specification between all drivers or that that the proposal has been rejected.

Neither. They both refer to SQLite only

This seems to be part of the problem then. The Poco::Data abstraction of the
database is not really complete without datatype abstraction as well. There
should be an abstract mapping between the primitive types supported by
AbstractBinding, AbstractExtraction and AbstractPreparation that work across
all drivers, SQLite, MySQL and ODBC. As a starter for ideas.

class AbstractDatatypes
{
virtual std::string get(Poco::UInt64 *) = 0;
virtual std::string get(Poco::Int64 *) = 0;
<snip>
};

class SQliteDatatypes : public AbstractDatatypes
{
virtual std::string get(Poco::UInt64 *){ return "UINTEGER64";}
virtual std::string get(Poco::Int64 *){ return "INTEGER64";}
<snip>

}

class MySQLDatatypes : public AbstractDatatypes
{
virtual std::string get(Poco::UInt64 *){ return "BIGINT UNSIGNED";}
virtual std::string get(Poco::Int64 *){ return "BIGINT";}
<snip>
}

These datatype lookups can be used when users are generating their schemas.

Brad

[alex]

This seems to be part of the problem then. The Poco::Data abstraction of the
database is not really complete without datatype abstraction as well. There
should be an abstract mapping between the primitive types supported by
AbstractBinding, AbstractExtraction and AbstractPreparation that work across
all drivers, SQLite, MySQL and ODBC.

Poco::Data::MetaColumn does that. Type can be detected through Poco::Data::Column::type(), it's just that it's not on the SQL level, i.e. does not tell you what the datatype name string exactly is.