SQLite Can't create a datetime column

[bscott]

When i create a table with a FdoDataType_DateTime column, Sqlite provider create a string column.

If i create the same table directly in sqlLite using DATE or DATETIME Sqlite provider ignore these column with a describe command

[traianstanev]

Just a note: the provider uses a string column for date time and stores dates in ISO 8601 format. You can use string comparison to compare the dates/times.

[bscott]

My problem is that we are building database form dynamically. To do so, we first do a describe on the table to know how we will format the user input. So there is no way my program can guess what type it is (string or date)

[traianstanev]

When you create the FDO connection, try adding the following connnection property to your connection string:

"UseFdoMetadata=TRUE"

This will make the provider remember that you created this table with DateTime type, even though physically it's a string table. Then, when you DescribeSchema it will return DateTime, so you will know.

[traianstanev]

Sorry, wiki formatting broke the string above:

"UseFdoMetadata=TRUE"

[bscott]

I know that option would work but it's not a solution for us.

I want my clients to be able to manage there database with standard SqlLite tools. When using UseFdoMetadata=TRUE it create a bunch of fdo metadata tables that make database administration a nightmare. Even if we have a AutoCAD Map to edit the SqlLite schema, it's pretty difficult to use views as source of data for a featureclass

As quoted in the ​http://www.sqlite.org/datatypes.html document

"Even though SQLite allows the datatype to be omitted, it is still a good idea to include it in your CREATE TABLE statements, since the data type often serves as a good hint to other programmers about what you intend to put in the column"

So even if you store the date as string internally, it should not be very difficult to include DATE or TIMESTAMP in the CREATE TABLE statement

[traianstanev]

OK, I see your point. I'll convert this to an enhancement and check if we can add it without affecting existing stuff which relies on dates coming from a string column.

[traianstanev]

I checked with Romy -- supporting this would require parsing the SQL create table string manually to detect this.

Really, we recommend using the metadata table -- in FDO-SQLite it's actually not like the FDO tables in other providers -- it is just one extra table, and you can still manage the database without messing with that table (and without using FDO). If you delete that table, the database will still work.

Also, you can only add the properties whose types you want to be reported correctly to that table, and not all the properties. This way you get your type hint.

[bscott]

I've made a try with UseFdoMetadata=TRUE and with fdo_columns table well populated.

So when using FdoIFeatureReader and FdoIDataReader ( for simple query ) everything works well,a describe on the table returns a datetime type.

For Complex Query ( on multiple tables with joins ) using FdoISQLDataReader I still have the date as string problem

Now just imagine an application with more than 200 tables and 50 views. This is a common situation for our client. Maintaining the fdo_columns table is lot of overhead and will make sqlite less attractive

[traianstanev]

Are you using specific parts of the date time in your joins or just some kind of before/after comparison of the date field?

Also, what is stopping you from calling GetDateTime() on the FdoISQLDataReader for the properties which are dates? Is it because you do it based on a dynamically populated feature class?

Yes, I can imagine 200 tables and 50 views, we have a database with 600 tables and 200 views. :)

[romicadascalescu]

Sample data base with well defined view

[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.

[romicadascalescu]

I hope you are using the provider from trunk (3.6), which has below improvements (view update, and a few other), versions are API compatible. Also having 500 up classes (tables + views) I would really advice you to avoid all the time calling describe schema for the full schema. Lately in the provider I added support in Describe schema to get only definition for certain class/classes, see FdoIDescribeSchema::SetClassNames(FdoStringCollection* value);

As Traian said we do have databases with 600+ class 50+ views and 1200 indexes :) so we had to fix some issues also. The speed difference between having fdo_columns and not is not huge.

Regards, Romy.

[bscott]

Thanks Romy for the clarifation on the internal algo. My problem with queries is due to the use of aliases. And on a datetime column it returns Text. I don't have a lot of controls on the dynamic query itself. I'm a bit stuck

I've follwed your advise and tried with the trunk(3.6) but it does make any difference.

Bruno

[romicadascalescu]

Hi Bruno,

I just dropped a fix in trunk(3.6) which should solve your problems. It will not solve all the issues but in case you are using an alias for a property you will get the type as it should back. Below you can find my change:

Author: romicadascalescu Date: 2010-05-07 12:14:00 -0400 (Fri, 07 May 2010) New Revision: 5495

Modified:

trunk/Providers/SQLite/Src/Provider/SltReader.cpp

Log: #504 SQLite provider. Improvements:

• SQLite: Resolve the property type when caller uses aliases for properties

I hope it helps you. Regards, Romy.

[bscott]

Thanks a lot It solves my problem. works great :)

Bruno