I've been trying to discern the correct procedure to handle a MySQL datetime column for about 2 weeks now and although I take a step each day it is still slow and confusing.
Perhaps I should explain the bigger picture.
A third party application is storing its data in a MySQL database. The data is updated very frequently (say every 30 seconds). There are lots of tables involved and lots of data. I have no control whatsoever over the structure of the tables.
My ultimate goal is to write a module specific to the application but I first need to prove the concept.
My plan is to use existing modules to prove the concept (also demonstrate the power of drupal) and ease the development of the final module.
So here is what I'm doing so far.
I have arranged for the application to write its tables directly into the Drupal database to keep things simple.
I'm using the Data module to adopt those tables so I can create Views for them.
This should be simple enough but as is always the case with such things it isn't.
The first problem I've come across is caused by the fact that the third party application creates tables containing 'datetime' columns.
This broke the Data module when trying to create a View for the table. I've fixed that and posted a patch on the relevant issue. Its not 'datetime' specific, in fact its handling, what appear to be, modules that aren't obeying the rules properly.
The next problem is what are the correct views handlers to use?
All the ones I choose come back with an error when the table is viewed. I thought the Date module would help with this but it seems not.
So I have the following questions.
Are there existing Views handlers to deal with 'datetime' fields from a MySQL table?
If there are where are they and I'll find out why they don't show up in the Data module and post patches for that.
If there are no such handlers in existence I will create them. I'm going to need a little help getting started and doing it the "Drupal way". I'll even create handlers for any other un-supported MySQL types as I go.
I think this covers a number of use cases where data needs to be incorporated into Drupal.
In my case the ultimate goal is a module and the Data and Views modules will go a long way to creating the code for that module by creating the templates for the schema and the views thereby saving loads of work.
Comments
An idea
Hello,
Sorry for the brevity of my answer (I'm on the road). First, the main reason you would use the contributed modules "views" is for ad-hoc reporting. Since you didn't mention ad-hoc reporting as a requirement, I wanted to pose a much simpler strategy.
If you've added tables to the Drupal (mysql) database, you don't need any contributed modules to report (display) data from those tables. Drupal 7 uses PDO to access the database. As long as the table is in same database, PDO will recognize it automatically. PDO makes a connection to the database, the database describes the tables to PDO. Drupal's default database layer calls PDO for database operations. So you can use db_query to select records from your table : http://api.drupal.org/api/drupal/includes%21database%21database.inc/function/db_query/7 (or db_select actually most if not all of the database methods).
You would use something like theme_table to render a grid of your data http://api.drupal.org/api/drupal/includes%21theme.inc/function/theme_table/7
Drupal stores dates an integer. Drupal is not dedicated to MySql. Thus an integer works much better cross database platforms :)
See http://api.drupal.org/api/drupal/modules%21node%21node.install/7 For how the base "node" type stores dates like "created".
You can look at the format_date method to help you further in displaying dates :
http://api.drupal.org/api/drupal/includes%21common.inc/function/format_date/7
Ok sorry that wasn't brief :) Hopefully it gives you something to think about ;)
Thinking is the best way to travel.
Thanks goofus. I'll look into
Thanks goofus.
I'll look into those things. Quite a bit of learning still to do.
Initially I see one major problem with your suggestions.
The date stored as an int in the DB.
In my use case the date is stored as a MySQL datetime. I can not change that nor would I want to since I want my date portable between platforms. Storing the date as an int from some epoch is not portable because any external application reading / writing that field may well have a different epoch. In this case the MySQL datetime field is far more portable.
The next point I'll make is that your method requires me to write code for all of it. Using the Data and Views modules I don't need to write a single line to get a proof of concept going and then they will give me big chunks of code to get my module off the ground when I'm ready to add the extra features.
So, I guess, at first I'm ad hoc and then moving into a more defined solution.
I'm sure there's an acronym for that or there should be. I guess its almost RAD :-)
FYI
Hello,
I thought you wanted to know how Drupal stores dates. It was FYI. I was not suggesting you have to change your data. How Drupal stores it's data is not the same thing as "you are required to ......".
Next, you have posted your question in the wrong forum :( See the top of this forum, that's the description, here is the link:
http://drupal.org/node/644164. To summarize, you aren't coding (programming), you are in the wrong forum.
Please consider moving your post to either the Post Installation forum, or, the issue queue(s) of the contributed modules you are trying to configure. Sorry, but I don't see how your post fits here.
Good Luck
Thinking is the best way to travel.
I appreciate your help and I
I appreciate your help and I can see why this might be the wrong forum.
However, given the scope of the other forums this seems the most appropriate place to get correct answers to my questions. Especially since these questions need to be answered before I can write a single line of code. My ultimate goal is to write a module but before writing code I'm going to determine whether or not there are modules I can leverage (if they're broken or need enhancing I'll do that) and what the base system supports.
Now that we have begun this dialogue my understanding has moved forward and a picture of what I need to do is beginning to form. The list of things I need to look into has grown and my understanding improved.
My ability to contribute to the Drupal code base has increased and soon I will be happily hacking my module together and contributing patches to the modules I will be depending on.
This all brings me to my initial question which I feel I didn't properly specify.
The crucial question of the moment is:
How does Drupal handle the MySQL column type 'datetime'?
if the answer is "it doesn't" then I need someone to point me in the direction of docs that explain where I should start with coding to handle them.
I'm also needing some clarity of the policy for such database type specific things. For example I know this was taken out of the core and the Date module altered to reflect that. So what is the policy in adding it as a module.
I ask because I need it and my method of using existing modules to prove my concept failed. Indicating support is not totally complete. If it is supposed to be supported I will go find and fix where its broken before writing my module.
I know this all sounds a little confused and I am. Determining the details of the "Drupal way" of coding is quite a large undertaking.
I'm sorry if this sounds a little erm nasty, hard, offish or something. Its not my intention. I'm getting a little tired and I'm still recovering from surgery on a brain aneurysm. Honestly, I am, it was back in march and it still aches all day but at least my brain doesn't rattle around any more :-)
As far as I know, Drupal
As far as I know, Drupal doesn't use datetime because the goal is for the code is meant to be database agnostic, and I think postgresql doesn't support datetime.
I have to agree with goofus that you are posting in the wrong forum if you are trying to do this without code. Until you decide to do it with code, this thread should probably be in the Post Installation forum.
Jaypan We build websites
> As far as I know, Drupal
> As far as I know, Drupal doesn't use datetime because the goal is for the code is meant to be database agnostic, and I think postgresql doesn't support datetime.
Great. This is the kind of answer I'm looking for except I would like it to be a little more definitive.
The reason I'm posting in here is because I know I've got to code and I'm going to. Also it seemed to me that there is some sort of policy issue around this which is way beyond the scope of post-install. I know my original query didn't make that clear and I apologise. That said, the thread is developing, as I intended, into a discussion of how this database type is handled in the code.
From my perspective something is broken when dealing with the MySQL datetime fields in the database.
Is that by design?
If it is, fine, point me in the direction of documentation how to add support the drupal way so I put the code in the correct places and not get told off for doing it.
If it is not by design and the existing system (with or without module help) should support it then that's fine too. I'll go find out where its broken and fix it.
You will find that the more
You will find that the more specific your needs, the less documentation you will find. Most of what I know about lesser-used systems in Drupal has been from dissecting existing code, and reading through the API functions on the API site.
Which is to say, you aren't likely to find too much documentation on this. Your best bet is to pull apart the existing database interfaces, and try to see if you can figure out what's going on.
As for the Drupal way, it's to use UNIX timestamps rather than mysql datetime.
Jaypan We build websites
@jaypan: Thanks. This is what
@jaypan: Thanks.
This is what I had suspected. I had sort of hoped someone that knew all about the database API and more would be lurking and drop something in that would turn on the light for me.
With what has been said here and discussions in various module issue lists the picture is now much clearer.
Basically Drupal can only display structures of data that Drupal creates.
That's OK. I can live with that.
> As for the Drupal way, it's to use UNIX timestamps rather than mysql datetime.
I can handle that. Make sense when one considers that PHP has functions to manipulate them built in. Unfortunately they are not portable across platforms especially if different applications are creating the data and PHP isn't involved in the data creation process.
Unless I've missed something the first thing I need to do is create a module that will implement Views handlers for the datetime fields in my tables. Then I can quickly get my proof of concept off the ground, hopefully to a point where it will start generating income sufficient to allow the further development of the module and overall product.
Thanks everyone for your input.
Another Idea
Hello.
First,
That's 100% wrong.
Please, re-read my first comment regarding Drupal and PDO. Since you moved tables into the same database as Drupal, PDO is already using resources to process those tables. Drupal uses PDO, thus Drupal is ready to report from those tables right now.
Your issue is with the contributed modules, not Drupal's core. You don't have to use a contributed module to "convert" a Mysql date-time. Why not just use MySql to mask the date-time as either a string, a series of numbers (e.g. 12 for December), etc. Just like you can create tables from within mysql. You can create table "views" from within mysql.
Conceptually a "table view" is simply a named query.
To PDO and Drupal a table view is a read-only table. See here: https://dev.mysql.com/doc/refman/5.0/en/create-view.html In certain cases, you can create writable table views. However, that's just an FYi (out of scope for your requirements).
Don't confuse a sql table view with the Drupal contribute module "views". An sql table view is an SQL standard object (like tables and indexes).
To me, that's going to be quickest way to getting Drupal to report you table data. You can even use the contributed Drupal module by simply implementing it's sample "hook_views_data()". I would at least consider it.
To me, that's a more "rapid" (as in RAD) strategy. At least something to ponder :)
Thinking is the best way to travel.