[FLOW3-general] DateTime limited to Unix timestamp?

[Michael Feher]

>> If on the other hand, you want to have an abstracted way of dealing with
>> dates (this way really making use of a specific date/time datatype), you
>> have to provide the abstraction for all backends, which IMHO will be
>> really difficult to do.
>
> If you use real abstraction with the benefit of being completely
> independent of the backend used for actual storage you need to build a
> query "language" of your own (the "language" can consist of a series of
> functions, or a more complex API, or a textual language, or some other
> format which fits the application). Depending on the features you want
> to include it can be more or less complex to do.
>
> I really expect FLOW3 to have such an abstract implementation which is
> completely independent from the actual storage type.

I would say that the current FLOW3 implementation provides the required 
abstraction in the way that application are supposed to use the common 
repository interface and not have to deal with SQL queries. The question 
of this thread boils down to me as, whether there should be a common SQL 
backend which can be used for all SQL databases.

The answer is simply NO.

If you have such an common SQL backend it will be restricted to the 
intersection of the features available in all supported SQL databases. 
This means if you want to support a Database which does not support 
binary blob the common backend has to store all binary data eg. in 
Strings even though most databases support binary blobs.

So what we need is an abstract SQL backend from which the specific 
Database backend can inherit. This abstract SQL backend will take 
advantage of all features which are available in the majority of the 
supported databases. The database specific implementation will override 
all functions which are not supported by the specific database with a 
workaround and will provide additional optimization by taking advantage 
of features which are only available in the specific database.

In brief what we need is an abstract implementation of the SQL backend 
which stores DateTime object in DateTime data field and a specific 
implementation for SQLite which overrides the DateTime part and provides 
a workaround.

This workaround could be e.g. to store the DateTime as 64Bit UTC unix 
timestamp and in a separate field the timezone information. AFAIK does 
SQLite support 64Bit Integers. This would allow sorting and the complete 
reconstruction of the DateTime object. The drawback is that the 
implementation can not directly use the native PHP function for the 
conversion as they fail on 32Bit systems. But it could use the SQLite 
specific date and time functions. 
(http://www.sqlite.org/lang_datefunc.html). I am not sure if this would 
work as I am not really familiar with SQLite, but I think it is worth a try.

> If FLOW3 will support raw queries there will be less need to make a
> feature rich API, because problems can easily be solved with raw queries.
>
I am personally in favor of the possibility to run raw SQL queries on 
the database even though they should be discouraged but I think this 
should be discussed in a separate thread.

Michael