[fdo-trac] #641: SQLite Can't create a datetime column

[FDO]

#641: SQLite Can't create a datetime column
-----------------------------+----------------------------------------------
 Reporter:  bscott           |         Owner:  traianstanev
     Type:  enhancement      |        Status:  assigned    
 Priority:  blocker          |     Milestone:  3.5.0       
Component:  SQLite Provider  |       Version:  3.5.0       
 Severity:  1                |    Resolution:              
 Keywords:                   |   External_id:              
-----------------------------+----------------------------------------------
Comment (by romicadascalescu):

 HI,

 Let’s take a look at the provider and what can do if we also do the right
 thing.
 A) Views can be of two types:
   1) Well defined views. These types of views have defined the primary key
 (PK) and the geometry key from the main class (that means those columns
 are added into fdo_columns) – See attached sqlite file. The PK must be
 declared as read-only. In case a view is well defined at describe schema
 you will not see rowid as PK. All additional properties which should keep
 the type need to be added to the fdo_columns table (see 'CLASS_TYPE_DATA'
 as ClassType).
 Provider will not try to parse the view definition to get the right type,
 it will call describe on the view and get the basic types returned by
 sqlite engine. So properties you really need to keep the type you need to
 add them in fdo_columns (for view same as for tables), and in case you
 have an alias you need to use the alias.
 Advantages for using well defined views:
     - Use Spatial Index from the main table and we do not build an extra
 one.
     - Views can be updated, so insert, delete and update works.
     - All changes from based classes will reflect into the view records

   2) Normal views. We cache these views as temp tables and we use as PK
 rowid, also those views will not ‘see’ changes done in the dependent
 tables. Ugly but I don’t see how we can do it in other way.
 Disadvantages for using well defined views:
     - Separated Spatial Index.
     - Views cannot be updated.
     - All changes from based classes will NOT reflect into the view
 records

 B) Queries: The class definition returned by SQL commands readers depends
 of the SQL definition. So in case you do not use an alias provider will
 find the right definition for that property, however in case you will use
 aliases provider will return the best match found by calling sqlite engine
 API. Let’s take an example based on attached sqlite:
 SELECT MainClass.*, SecondClass.FeatId as SeFeatId, SecondClass.DTimeProp,
 'CLASS_TYPE_DATA' as ClassType FROM  MainClass, SecondClass WHERE
 MainClass.LinkId = SecondClass.LinkId;
 Looking at properties we have:
    - All properties coming from MainClass will be returned as are defined
 in that class (keeping the types e.g. date time)
    - SecondClass.FeatId as SeFeatId will be returned as int64 since we
 used an alias and provider could not localize the property in the base
 class
    - SecondClass.DTimeProp will be returned as date-time since no alias
 was used
    - 'CLASS_TYPE_DATA' as ClassType will be returned as string which is
 the default when sqlite engine returns “SQLITE_NULL”

 Regards,
 Romy.

-- 
Ticket URL: <http://trac.osgeo.org/fdo/ticket/641#comment:10>
FDO <http://fdo.osgeo.org/>
Feature Data Objects