Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Missing DATETIME table field type! Several database tables affected

Quote Reply
Missing DATETIME table field type! Several database tables affected
Hi all!

Just started to work on Advanced Link Expirator plugin, but I've faced some problems.
I compute link expiration, based on second, not just on day. Therefore, I have to use DATETIME field type in the SQL table. This provides me the possibility to compute exact expiration time.
But LinksSQL uses DATE field types.

Info:
DATE is stored in database as: YYYY-MM-DD,
DATETIME is stored in database as: YYYY-MM-DD HH:MM:SS

However, I've faced 2 problems:
1) DATETIME table field type is missing (using MySQL database). It is not supported officially in GT/SQL/Admin.pm. It means nobody can create a table with a DATETIME field, using the admin interface.
The only officially supported types are: INT CHAR TEXT DATE ENUM.
LSQL should support creating DATETIME table field types in admin interface!

For plugin developers like me, this should not be a big problem, because it is very likely (as I've seen in docs), that GT/SQL/Creator.pm can create even DATETIME table field types, if DATETIME was given as field type (a new table is created in the install script).
Also SQL clients can be used to change field types, but we don't want to use clients, if not needed, right?

I do not remember, if DATETIME is common type in all SQL databases, or it is MySQL specific. If it is MySQL specific, I see the reason, why Alex did not implement it. Otherwise it should be implemented into Admin.pm.

2) LinksSQL uses DATE field in Add_Date, Mod_Date, Newest_Link, etc. fields.
It is installed this way by default.
Here are listed those fields, which should be stored as DATETIME instead of just DATE, but currently they are stored as DATE type:

You find default install values in admin/Links/SQL.pm:
# --------- Links Table ----------------
...
Add_Date => { pos => 5, type => 'DATE', not_null => 1, form_display => Links::language('prompt_Add_Date') },
Mod_Date => { pos => 6, type => 'DATE', not_null => 1, form_display => Links::language('prompt_Mod_Date') },
...
# --------- Category Table ----------------
...
Newest_Link => { pos => 14, type => 'DATE', default => '', form_display => Links::language('prompt_Newest_Link') },
...
# --------- Reviews Table ----------------
...
Review_Date => { pos => 5, type => 'DATE', not_null => 1, form_display => Links::language('prompt_Review_Date') },
...
# --------- Verify History Table ----------------
...
Date_Checked => { pos => 3, type => 'DATE' }
...
--------------------------------------------------
This means we lost the exact time of link additions, modifications, etc. If it would stored in full DATETIME format, we could still display it in short YYYY-MM-DD format, but the inverse is not possible.
In context of the Advanced Link Expirator plugin, this "feature" makes the expiration computing difficulter.

It means following:
a) if somebody wants exact expiration computing, it is needed to change some field types of some database table from DATE to DATETIME, and $CFG{date_db_format} and $CFG{date_user_format} at install time. Hopefully there will be no need other changes, but this is not 100% sure.
b) if no exact expiration computing is needed, then the expirator plugin should compute day based expirations.

I would like to discuss with Alex, GT staff, veterans, plugin developers, about missing DATETIME type, and about what effect does it have, or may have on our plugins?
It is needed to compute exact expiration at all? Personally, I very NEED to exact expiration for my service. I suppose others would need it, too.

Let me know your opinion.
(please do constructive criticism, if you feel you need to criticize)

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Subject Author Views Date
Thread Missing DATETIME table field type! Several database tables affected webmaster33 7166 Aug 28, 2002, 3:18 PM
Thread Re: [webmaster33] Missing DATETIME table field type! Several database tables affected
Alex 7037 Sep 5, 2002, 2:00 PM
Thread Re: [Alex] Missing DATETIME table field type! Several database tables affected
webmaster33 7051 Sep 6, 2002, 3:37 AM
Thread Re: [webmaster33] Missing DATETIME table field type! Several database tables affected
yogi 7022 Sep 6, 2002, 4:10 AM
Thread Re: [yogi] Missing DATETIME table field type! Several database tables affected
webmaster33 7029 Sep 6, 2002, 4:56 AM
Thread Re: [webmaster33] Missing DATETIME table field type! Several database tables affected
Paul 7059 Sep 6, 2002, 5:06 AM
Thread Re: [Paul] Missing DATETIME table field type! Several database tables affected
webmaster33 6980 Sep 6, 2002, 6:42 AM
Thread Re: [webmaster33] Missing DATETIME table field type! Several database tables affected
Paul 6992 Sep 6, 2002, 6:53 AM
Thread Re: [Paul] Missing DATETIME table field type! Several database tables affected
webmaster33 6999 Sep 6, 2002, 7:50 AM
Thread Re: [webmaster33] Missing DATETIME table field type! Several database tables affected
Paul 7013 Sep 6, 2002, 7:54 AM
Post Re: [Paul] Missing DATETIME table field type! Several database tables affected
webmaster33 6995 Sep 6, 2002, 7:59 AM
Thread Re: [webmaster33] Missing DATETIME table field type! Several database tables affected
pugdog 6982 Sep 6, 2002, 8:09 AM
Post Re: [pugdog] Missing DATETIME table field type! Several database tables affected
webmaster33 6961 Sep 6, 2002, 8:22 AM
Thread Re: [webmaster33] Missing DATETIME table field type! Several database tables affected
yogi 7019 Sep 6, 2002, 5:10 AM
Thread Re: [yogi] Missing DATETIME table field type! Several database tables affected
webmaster33 6958 Sep 7, 2002, 4:16 AM
Thread Re: [webmaster33] Missing DATETIME table field type! Several database tables affected
Paul 6957 Sep 7, 2002, 4:48 AM
Thread Re: [Paul] Missing DATETIME table field type! Several database tables affected
webmaster33 6932 Sep 7, 2002, 9:35 AM
Post Re: [webmaster33] Missing DATETIME table field type! Several database tables affected
Paul 6933 Sep 7, 2002, 10:12 AM
Thread Re: [Paul] Missing DATETIME table field type! Several database tables affected
webmaster33 6963 Sep 7, 2002, 9:42 AM
Post Off topic again
Ian 6958 Sep 7, 2002, 10:19 AM
Post Re: [Alex] Missing DATETIME table field type! Several database tables affected
webmaster33 6867 Dec 28, 2002, 8:17 AM
Thread Re: [Alex] Missing DATETIME table field type! Several database tables affected
webmaster33 6878 Dec 29, 2002, 5:54 AM
Thread Re: [webmaster33] Missing DATETIME table field type! Several database tables affected
Paul 6837 Dec 29, 2002, 7:32 AM
Post Re: [Paul] Missing DATETIME table field type! Several database tables affected
webmaster33 6831 Dec 29, 2002, 7:51 AM