Overview
Features
Download
Documentation
Community
Add-Ons & Services

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

Please post support and help requests here.

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

Postby bradphelan » 04 Dec 2009, 17:23

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
 
Posts: 23
Joined: 03 Dec 2009, 11:26

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

Postby bradphelan » 04 Dec 2009, 17:39

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
bradphelan
 
Posts: 23
Joined: 03 Dec 2009, 11:26

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

Postby alex » 04 Dec 2009, 17:43

bradphelan wrote: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
 
Posts: 1047
Joined: 11 Jul 2006, 16:27
Location: United_States

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

Postby alex » 04 Dec 2009, 17:48

bradphelan wrote:Does this seem like a bad thing?


If you ever attempt to extract into Any, you'll get UnknownTypeException.
alex
 
Posts: 1047
Joined: 11 Jul 2006, 16:27
Location: United_States

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

Postby bradphelan » 04 Dec 2009, 17:53

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
bradphelan
 
Posts: 23
Joined: 03 Dec 2009, 11:26


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

Postby bradphelan » 07 Dec 2009, 10:41

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
bradphelan
 
Posts: 23
Joined: 03 Dec 2009, 11:26

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

Postby alex » 07 Dec 2009, 13:28

bradphelan wrote: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
alex
 
Posts: 1047
Joined: 11 Jul 2006, 16:27
Location: United_States

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

Postby bradphelan » 07 Dec 2009, 16:25

alex wrote:
bradphelan wrote: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
bradphelan
 
Posts: 23
Joined: 03 Dec 2009, 11:26

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

Postby alex » 07 Dec 2009, 18:17

bradphelan wrote: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.
alex
 
Posts: 1047
Joined: 11 Jul 2006, 16:27
Location: United_States

Next

Return to Support

Who is online

Users browsing this forum: No registered users and 1 guest

cron