Type conversions C++ vs Postgresql

[3t-dev]

Hi @mshepanski,

I'm looking for a ORM tool in C++ and interested in your Quince library. After read quince's doc site, I build and code some examples, It work well. Many thanks for you, Quince seem is what I need. But, I opposite a issue of type conversions. That's a property has type of std::time_t (expect to map with timetstamp in postgresql) but Quince not mapping it. I look at Basic conversions page (http://quince-lib.com/quince_postgresql/basic.html), Quince converts most of basic types but abstract data types (eg: std::time_t) is not.

Can you instruct me about convert and mapping not-basic types or how to insert new mapper modifiers into Quince source code?

Thanks and best regards.

[mshepanski]

Hi trinhthethanh,

Thanks for your interest in quince. Great to hear that it's working out for you.

I've had a quick look into what would be involved in providing a mapper for std::time_t. I think the best way would be if I did it, then the code additions would serve as a guide for the next person who wants to add a mapper for some type.

Could you give some indication of how urgent this is for you?

Cheers, --- Michael

Michael Shepanski Be a philosopher for a day: visit http://stepbackstepforward.com

[3t-dev]

Dear @mshepanski, thank for reply me. I''m seting up project skelaton for my company and Quince will used in all of projects.

If you can, please give me some quick guide to add new mappers for me.

Thank you very much.

[mshepanski]

On second thoughts, it isn't possible to do /exactly/ what you ask, because std::time_t is just a typedef name for some integral type.

I think the right thing to do is provide a mapper for boost::posix_time::ptime. I'm looking into that now. I hope that will suit your purposes.

[3t-dev]

I see, for the time type, it can either tm (in time.h) or boost::posix_time::ptime as you suggested. Yesterday, I try to add new column_type tm into and modified mapping sections in database.h and session.h in quince_postgresql. After that, I create a struct with tm type property and add to QUINCE_MAP_CLASS macro, but it compile errors in user_defined_class_mapper.h which's "most cryptic class" as you commented :D

[3t-dev]

Hi, After deeply ""hack" your Quince source code, I can add some mapper code sections for time type. Well, It builds ok and successfully to inserts into postgresl timestamp column. In Cell class, I convert time to std::string and getdata by std::string.c_str() function. I choose std::chrono::system_lock::timepoint for my solutions. But, in the case of SELECT statement, when we get timestamp data throught libpq C++ library, you get by binary way. In primary types and std::string cases, it's ok but for timestamp data, has occur a happen. I can't parse data get by PQgetvalue of timestamp column. Do you know the way to parse timestamp binary data? This issue only remain that happen, please intruct me if you can do that. When we resolved mapper abstract types, I think that Quince will give more power and useful. Many thanks.

[mshepanski]

I have been working on something similar. I use TIMESTAMP as the postgresql column type, and I have a mapper that converts to/from boost::posix_time::ptime. On sqlite I will have to map it differently, so I'm treating boost::posix_time::ptime as a polymorphically mapped type.

So far I'm just sketching out code, so I don't know whether I will hit the same problem as you did with binary formats. I'll let you know when I find out.

[mshepanski]

I have made a first attempt at the code. You can get it by checking out the dev branch of each of my repositories. So you should checkout the dev branches of quince and quince_postgresql. Anyone who uses sqlite should checkout the dev branches of quince and quince_sqlite.

This lets you use boost::posix_time::ptime as a polymorphically mapped type, in the same way you use int32_t or std::string. On postgresql, ptime is mapped to TIMESTAMP, and on sqlite it's mapped to TEXT.

I followed advice from the postgresql forums and always made sure that timestamps are sent as strings (not binary) in communication between the client and the postgresql server. (On sqlite there is no choice: datetimes are always represented as strings.)

No documentation yet. I'll do that at the same time as merging the changes to the head branch. But first I would like to know whether this code does what you need.

[3t-dev]

Ok, new dev version is good! Thanks you very much. So if you have free time, you should write a little document to guild for mapper abstract types. Thanks again.

[mshepanski]

I have merged the change to the main branch, and I've documented the mappings for boost::posix_time::ptime.

For now I'm not documenting the general technique for writing new mappers, because I'm not yet certain that that API is stable. But for anyone does want to implement a mapper for a new class, using the API as it currently stands, I can suggest that you look at the implementation of quince_sqlite::ptime_mapper (in quince_sqlite/src/database.cpp) as an example. Then, when you've written your mapper class, you can deploy it by following the documentation at http://quince-lib.com/custom/deployment.html .

(Note the change I have made is somewhat more complicated, because, for postgresql, I also added a new SQL column type.)