Hi guys,
To what extent is Data supposed to support the MySQL datetime field.

Specifically consider this situation.

A table exists in the Drupal database.
The table was NOT created by Drupal.
The table contains data that the user wishes to display in Drupal.
The user can not alter the table in any way.
The table contains columns of type 'datetime'

The user uses Data to adopt the table.
The user then clicks on edit to edit/view the tables schema.

Here the datetime columns are marked as int and a save will try to change them. The question is should they be marked as int, I don't think they should.

The user clicks on Configure Views to create a rudimentary View for the table.

This tries to assign view handlers appropriate to the type and doesn't do too bad in general and may even get it right if there were a view handler for the MySQL datetime.

Given that Data relies on the MySQL specific capabilities to get the table description is it acceptable to add the missing bits for the MySQL specific data types? Extrapolating this further, is it acceptable to add view handlers for the MySQL specific types?

I'll do the work by creating patches and submitting them. Just tell me which branch to work from and I'll run with it. :-)

I'm trying to get comments from KarenS over at the Date module but I think she's got a lot on at the moment. I'm also thinking that the Schema module guys should have a comment too.

Thanks.

CommentFileSizeAuthor
#18 1792430.data_.date-views-support.patch12.63 KBjoachim

Comments

joachim’s picture

joachim commented

AFAIK Date module currently supports 3 database types: timestamps, ISO, and datetime. But it doesn't call them that, just to add to the confusion :)

davew1970’s picture

davew1970 commented

OK.

So what exactly are you saying here?

Is the following summary correct?

Data should not be providing any View handlers.
Data should fully recognise the MySQL datetime type.
Data should be able to create tables containing the MySQL datetime type.

I'll not hold you to this next one :-)
In your opinion Date should provide view handlers for the MySQL datetime type.

joachim’s picture

joachim commented

> In your opinion Date should provide view handlers for the MySQL datetime type.

AFAIK it does already. When you create a date field you have 3 options for the storage type. All three get views handlers.

davew1970’s picture

davew1970 commented

> AFAIK it does already. When you create a date field you have 3 options for the storage type. All three get views handlers.

Brilliant. So on first sight it looks like they are broken.

A minor point I want to clarify though.

How, exactly, would I create this date field you mention? Just so I can replicate the procedure and prove to myself that it works, at least one way.

joachim’s picture

joachim commented

> How, exactly, would I create this date field you mention

In the regular field UI. The way you'd add a field to a node.

davew1970’s picture

davew1970 commented

Aha.
I've just played with all that and looked at what happens in the DB.

That method doesn't provide what I need or at least my understanding of it.

For that method each field is attached to a content type (node definition).
I would have to define a content type for each table. Then create content of each content type that directly duplicates every row of my existing tables. That's millions of entries and will double my disk storage requirements, not to mention my backup storage.
Add to that the fact that several rows of data are added to each table every 30 seconds it becomes an unmanageable task to keep node production up to date.

Am I missing something?
Is there a way to define a single node that will map to a specified row of a given table and not require a new node for each row.
It seems to me that views do this but nodes do not.

joachim’s picture

joachim commented

Just had a look at the Date module code.

It doesn't actually have field handlers for Views AFAICT.

So actually the only field handler for date fields is views_handler_field_date. What does that do, or not do?

(BTW:

> That method doesn't provide what I need or at least my understanding of it.
> For that method each field is attached to a content type (node definition).

Not what I meant at all -- but that's academic now.)

davew1970’s picture

davew1970 commented

> So actually the only field handler for date fields is views_handler_field_date. What does that do, or not do?
Same errors as noted before.

> Not what I meant at all -- but that's academic now.)

Well actually that was quite helpful. It provided a demonstration that Drupal can access the datetime type and gives me a pointer of where to look for an example. I also created a view of the node I created which does use a datetime and the available view handler works for that.

It seems the available view handlers for dates all access a date field as a 'field'. That is they only work if it was created as a field by Drupal. They don't work if they access a raw MySQL datetime in a table. I think the field implementation stores extra data specific to options for display. They of course aren't present in raw tables so the view handler fails.

joachim’s picture

joachim commented

Yup, that's my suspicion too.

The trick would be to either supply the views handler with the information it needs, or make a subclass for Data module that fills in the gaps.

davew1970’s picture

davew1970 commented

Well, I think we're on the same wavelength here.

Are you happy for me to come up with a patch for Data to handle this?

Although, I'm not so sure that Data is the right place to put this. I mean its effectively implementing a view handler and Data doesn't have any of those and it will set a precedent whereby Data might be expected to provide view handlers for all sorts of database engine specific data types that aren't handled anywhere else. I can see that bringing all sorts of difficulties when people start importing tables from Postgres, MS SQL and fire thingy and god knows what else.

Come to think of it, why Data and not Schema. I mean Data relies on Schema but Schema doesn't even acknowledge the existence of the datetime database type when surely it should.

joachim’s picture

joachim commented

Looking at the code for views_handler_field_date, it *only* handles timestamp dates -- it passes the DB value straight to http://api.drupal.org/api/drupal/includes!common.inc/function/format_date/7 for formatting.

So it's got to be Date module. Which does appear to support non-FieldAPI database fields:

/**
 * Implements hook_date_views_fields().
 *
 * All modules that create custom fields that use the
 * 'views_handler_field_date' handler can provide
 * additional information here about the type of
 * date they create so the date can be used by
 * the Date API views date argument and date filter.
 */
function date_views_date_views_fields($field) {

-- but that only mentions arguments and filters, not field handlers.

So what do date FieldAPI fields do? A bit of experimentation shows that they use views_handler_field_field, which is the generic handler for FieldAPI fields. It relies on FieldAPI's formatters via field_view_field(), so no knowledge of dates or anything is needed by views: it loads the data, just passes it on to FieldAPI to render it. ...which is no use to us here at all.

Hence, yes, we're scuppered here.

- views has handling for Drupal core timestamp database fields
- date lets the generic views field handler deal with showing dates as a field

> Although, I'm not so sure that Data is the right place to put this. I mean its effectively implementing a view handler and Data doesn't have any of those and it will set a precedent whereby Data might be expected to provide view handlers for all sorts of database engine specific data types that aren't handled anywhere else.

True, but I'm not sure where else they would belong.

That said though, Data is not very actively maintained these days. It may be you're better off starting a new project for these.

davew1970’s picture

davew1970 commented

> That said though, Data is not very actively maintained these days. It may be you're better off starting a new project for these.

I had noticed that and I think its a shame.
Data has all the makings of a great RAD tool for stuff like mine. I don't understand why this hasn't come up before, maybe I'm the only person ever to want to do it this way :-)

I could just jump in and write my module but it seems a waste. If everything worked as expected I'd have a working proof of concept and, as a side effect, be able to generate lots of template code for my module. I'm persisting with this because I believe it would help others and ease the development of new modules.

Do you have any objections to me bringing Data up to date (regarding other bugs and updating to D7) and adding features? Or do you think I'd be better off using Data as a template for a different module that could, perhaps, supersede Data?

Thanks.

joachim’s picture

joachim commented

> Do you have any objections to me bringing Data up to date (regarding other bugs and updating to D7) and adding features?

Bug fixes: yes please!
Features: depends on the feature I guess. But certainly making Data module able to work with adopted data tables is a goal of this module, so anything that helps that should be fine.

One thing to note is that I no longer have an active install of Data I'm using for my own projects, so I'm not often going to find time to review patches. You may have to find other users to review your patches.

It might be time to find new co-maintainers for this project.

davew1970’s picture

davew1970 commented

Great.
I'll post patches etc.

I've been browsing the code and couldn't see why views_handler_field wouldn't work for this so I tested again. It does work. Well, it renders the datetime anyway. More testing to do on that before I can say it works properly.

I'll work on a patch to have it select this automatically and any other fixes revolving around the datetime type.

> It might be time to find new co-maintainers for this project.
Would it help if I apply for a short term position? :-)

joachim’s picture

joachim commented

> Would it help if I apply for a short term position? :-)

It would, although I don't have the access rights to add maintainers. We'd have to ask the project owner.

I can say though that the way I do it with my own projects is I ask to see some patches first, so I can review your code and check for things like coding standards. More details on the Best practices for co-maintaining projects at http://drupal.org/node/363367

I'll open a new issue here to discuss the need for new co-maintainers.

joachim’s picture

joachim commented

While the Date module's views handlers are field API specific, I think a case could be made for Date having generic handlers too. After all, the Date project contains the DateAPI module which provides generic date form elements that you can put on anything.

davew1970’s picture

davew1970 commented

Yes, good point.
I'll work my way up to that bit.

I just posted a patch to Schema to get support for datetime (and hopefully other types) there and I'm going to work my way upwards through the chain until this works properly.

joachim’s picture

joachim commented
Category: Support request » Feature request
Issue summary: View changes
Status: Active » Needs review
StatusFileSize
new 1792430.data_.date-views-support.patch12.63 KB

Here's a fix in several parts:

First, for the Views field, a patch to Views to extend the 'views_handler_field_date' handler for the other date formats: #2178287: allow views_handler_field_date to work with SQL datetime format. This allows it to work with ISO dates and DATETIME dates.

Second, a patch that allows the admin user to declare Data table fields as being date fields.

This allows Date module's filter and argument compound handlers to recognize these table fields and work with them correctly.

To enable a field on your data table to be used by Date module's Views handlers, you need to set it up in a new UI tab.

Note: patch is cumulative on #1952884: Fix integration with Field UI and #1671380: Data Entity allows entities to be created with entity ids that are not integers.

joachim’s picture

joachim commented
Title: Extent of support for MySQL datetime field » support for MySQL datetime field
Status: Needs review » Fixed

Committed. New alpha release shortly!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.