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...
Quote Reply
Re: [webmaster33] Missing DATETIME table field type! Several database tables affected In reply to
Hi,

No, Add_Date and Mod_Date are the correct type. We ignore the time as nothing in Links SQL cares about what time the link was added/modified, as the What's New is based purely on dates. It would slow things down to switch it to DATETIMES (as there are several queries that look for links on a specific date -- this would get changed to a range search instead).

If you want something like this, you should look at adding a custom column for Expriy_Datetime or something similiar. You may want to use INT's instead of DATETIMES as they are much easier to work with.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Missing DATETIME table field type! Several database tables affected In reply to
Quote:
No, Add_Date and Mod_Date are the correct type. We ignore the time as nothing in Links SQL cares about what time the link was added/modified, as the What's New is based purely on dates
Yes, I know. I just wanted to tell you, that only using date feature makes some limitations on the usage possibilities.
If the exact time is ignored, several services, are be not able to use the LinksSQL as their base cataloging software. And sorting, categorizing informations becomes the most important thing of the 21st century.
Alex, you know that catalogs and any kind of categorized information are not just related to site links, but almost anything can be categorized. And there are several services, that would need exact date-time, not just date. So by default you are closing out those users from LinksSQL users.

Quote:
It would slow things down to switch it to DATETIMES
Yes, possibly. So I understand that reason, if really slows down the process. I accept your opinion now, since I did not analyzed where, and how many queries would affected. You may know that better than me.
However for my personal ad service, date-time is necessary and essencial for me.

Quote:
(as there are several queries that look for links on a specific date -- this would get changed to a range search instead).
I do not see why would we need range query? And why would be that slow?
We could simply use wildcard based query, couldn't?
Code:
Wildcard query: SELECT * FROM TABLE lsql_Links WHERE ('Add_Date' = '2000-08-15%')
It is not working?

And how much performance loss would result between the following single query and range query?:
Code:
Single: SELECT * FROM TABLE lsql_Links WHERE (Add_Date = '2000-08-15')
Range: SELECT * FROM TABLE lsql_Links WHERE (
Add_Date >= '2000-08-15 00:00:00' AND
Add_Date < '2000-08-16 00:00:00' )

Additionally my experience with Links profiling is, that most of the time is spent in the template parsing, so the SQL queries are not the point, where we get considerable performance loss, compared to the full execution time (of course IMPO).


I use the following fields and field types in Links table:
Code:
Field name | Field type
Add_Date | DATETIME
Mod_Date | DATETIME
Expire_Date | DATETIME
ExpireAfterDays | ENUM (day based)

Back to the plugin.
The Advanced Link Expirator could be backward compatible with those LSQL copies, which use just DATE as add or modify.
So the Advanced Link Expirator could be able to expire by both DATE or by DATETIME. User could be able to choose to expire once a day or to expire continuously at exact times, as expiring time reached (that's the advantage of DATETIME usage: continous expiration).

But I would have easier job, if I would not need the convert necessary fields to DATETIME (and all dates belonging to).
Also if LSQL would get the add-modify time functionality, by using DATETIME for the mentioned tables and fields. This could expand usability range of LSQL.

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...
Quote Reply
Re: [webmaster33] Missing DATETIME table field type! Several database tables affected In reply to
I personally wouldn't touch the Add_Date and Mod_Date fields. Why don't you just add new fields into the plugin, Add_Datetime and Mod_Datetime, that store the information you need.

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] Missing DATETIME table field type! Several database tables affected In reply to
Yes, Yogi, it is a possible solution.
However it is not an optimal solution, since there will be 2 duplicated database columns.
Therefore I do not prefer that solution, especially because I do not want to force others to use an unoptimal solution, through my plugin.

Converting the Add_Date DATE type to DATETIME type is more optimal, than adding new columns. Also the user does not lost information, instead gets the time info into his service.

Is it possible, that DATETIME type occurs conflicts in any of your plugins?

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...
Quote Reply
Re: [webmaster33] Missing DATETIME table field type! Several database tables affected In reply to
Changing the attributes of add date and mod date is not a good idea. As it is, GT::Date can be used to manipulate the date, however if this was changed to DATETIME then the code would break.

Really you only want this added because you personally want to use it, the same with the $ADMIN global you wanted Alex to add....look at the bigger picture....you need to be writing flexible, scalable code, not code that relies on Alex adding extra bits to the core code.

You should be adding columns, Add_Date can contain the date and you could then add a Add_Time or whatever for the time, this isn't duplication and will barely be noticable in terms of performance.
Quote Reply
Re: [webmaster33] Missing DATETIME table field type! Several database tables affected In reply to
No, it wouldn't conflict with my plugins, but it would conflict with the Links SQL core code (that's what Alex also said, if you carefully read his post above).

For example, the new links feature will be completely broken. Have a look at the code in Build.pm and you will see why.

Plus, you would also have to change the Newest_Link field in the Category table to DATETIME, or modify the code in Links/Link.pm.

I think that the definitions of the system columns should be left as they are. I personally would not want to buy a plugin that changes my existing columns, especially if it breaks core Links features. This would be very unoptimal, as you call it.

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [Paul] Missing DATETIME table field type! Several database tables affected In reply to
Quote:
Changing the attributes of add date and mod date is not a good idea. As it is, GT::Date can be used to manipulate the date, however if this was changed to DATETIME then the code would break.
I hope not. Alex is really a great mind and was so kind, that he gave us a 'date_db_format' config variable, so we can define how the dates are stored in the database.

It it is implemented everywhere correctly (why not to be), then dates will work and show correctly everywhere. Cool

BTW FYI: in my modified version of Links v2.0, I already used this way of date displaying technique (I did not know, what Alex implemented into LSQL, I was not LSQL owner at that time).
Well I had some date format related config variables, like: $date_db_format, $date_unix_format, $date_display_format, $date_file_format, $date_short_format, $date_whatsnew_format, $date_whatsnew_format, $date_long_format, $time_format
And I wrote a date converter script to have all these features.



Quote:
you need to be writing flexible, scalable code, not code that relies on Alex adding extra bits to the core code.
To have a scalable, flexible code, I need to ask sometimes special modifications from Alex, because it is missing.
Yes, while I do these developments personally for me, I'm developing it with the aim of creating a widely usable commercial plugin.
You can be sure the following:
1) I will test the plugin in details before I release it. I will try installing the plugin on originally downloaded and installed LinkSQL.
2) I'm good beta tester, I've more than 3 years beta testing experience.
3) I will not release a half-done plugin, just because the public needs it. I have releasing something which is not sure it is working


Quote:
You should be adding columns, Add_Date can contain the date and you could then add a Add_Time or whatever for the time, this isn't duplication and will barely be noticable in terms of performance.
But will in terms of database space. I saw ISP show sells database space 2x price compared to normal webspace...

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...
Quote Reply
Re: [webmaster33] Missing DATETIME table field type! Several database tables affected In reply to
The number of users using your plugin will be tiny compared to the number of Links SQL users.

You are asking Alex to make several changes to the core code to suit your plugin which has the potential to break many many websites. It is not a viable idea. Alex has told you why and Yogi too.
Quote Reply
Re: [Paul] Missing DATETIME table field type! Several database tables affected In reply to
Quote:
You are asking Alex to make several changes to the core code to suit your plugin
I suggested to Alex, not asked. And not for the reason to suit my plugin (a bit for my lazyness Wink), but mainly for the users and potential buyers, who would need that.
I wrote, I can solve this problem in my plugin, while I can keep compatibility with LSQL. So it is not problem for me anymore.

I suggested it, because:
1) I do not think, it will result result noticeable performance loss
2) It will expand potential user range, who can use LSQL as their cataloging software
3) Very likely will not affect (at least in not too many places) the core code, since we have the 'date_db_format' config variable, so we can define how the dates was stored in the database.
4) Very likely will not affect the performance (at least not really):
Code:
SELECT * FROM lsql_Links WHERE ( Add_Date LIKE '2002-08-10%' ) (corrected myself Blush)
Just tried that query an works like a charm! So what's the problem?


Also I do not worry about, how many people will use my plugins. If there will be a lot users, it will be worth do improve the plugins, if there will be a few, I will try to make them happy with my plugin, if nobody will use it, I will use my own. Cool


Reasons, why I will not worrying about the DATETIME furthermore:
1) I can live without default DATETIME type, since I do all changes I need fo myself, but I'm sure there several potential buyers, users, who are missing DATETIME as default.
2) For my personal website, I use DATETIME. Stop.
3) If Alex wants it, he will implement DATETIME. If not, he will not implement it. It is so easy.

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...
Quote Reply
Re: [webmaster33] Missing DATETIME table field type! Several database tables affected In reply to
>>
3) If Alex wants it, he will implement DATETIME. If not, he will not implement it. It is so easy.
<<

Yay, at last!

Read what Alex said about 10 posts up:

Quote:
If you want something like this, you should look at adding a custom column for Expriy_Datetime or something similiar.
Quote Reply
Re: [Paul] Missing DATETIME table field type! Several database tables affected In reply to
Yes, I have Expire_Date, what you can find in one of my posts, several posts above.

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...
Quote Reply
Re: [webmaster33] Missing DATETIME table field type! Several database tables affected In reply to
I needed a datetime field in my postcards script. I added one. I also added a timestamp to the links table on several sites.

The overhead for adding one small "system" field (meaning it has a defined, fixed format in the MySQL core code) is almost trivial. Adding a couple of keywords (or just words to a title) to a link far outweighs the disk space and indexing requirements of this sort of field.

Also, by using several different fields, you can improve performance, and CPU is *MUCH* *MUCH* more expensive than disk space. It always has been, and always will be. You have one processor (usually) but you can attach an infinite number of disks (theoretically). Even on 4 processor systems, you have 4 CPUs, but you can install 12 RAID boxes with 3 terabytes of information.

If you need to sort links by the RELATIVE time they were added, just use SORT BY Add_Date, ID

The higher the ID, the later the link was added to the system.

This gets trickier with Mod_date, so you'd need to track the specific time a link was modified by using a separate field. Because "hits" get written to the link dynamically, you don't want to use a timestamp field.

Alex's suggestion of using an integer field, which stores the localtime() value is a good one. This can be converted to a date/time as needed, and it can also be sorted qickly to provide a relative relationship without incurring any special processing or conversions until needed.

Basically, you had a need or a desire, and asked a question. You got answers you didn't like like. Fortunately, there is a virtually trivial work around, adding a new field to the links table.

This is not the same as a request for a hook into the Admin system, a new field type "File" or configuration variable for "sort order". There was no quick/easy and portable work around for those. There is for Date/Time sorting -- especially if you add your own field which is managed by your own plugin.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Missing DATETIME table field type! Several database tables affected In reply to
Thank you for your experience you shared with us!

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...
Quote Reply
Re: [yogi] Missing DATETIME table field type! Several database tables affected In reply to
Yogi, sorry for the late reply, yesterday I was not able to answer your post, too.

Quote:
but it would conflict with the Links SQL core code (that's what Alex also said, if you carefully read his post above).

For example, the new links feature will be completely broken. Have a look at the code in Build.pm and you will see why.
Yes, you are right, we will have problem in new links feature. It is my responsibility to manage these and other problems, what may occured by a db upgrade.
(the database upgrade we are talking is not very serious, just a field type change, what can be solved without data loss, and can be restored without data loss!)

Further into from one of my earlier posts: "Very likely will not affect (at least in not too many places) the core code, since we have the 'date_db_format' config variable, so we can define how the dates was stored in the database."
Hopefully all these problems can be managed down.
Of course and upgrade only worths, if all problems what it caused can be managed, and corrected.


Quote:
Plus, you would also have to change the Newest_Link field in the Category table to DATETIME
Yes, I'm aware of this, and I change the type of Newest_Link field, too.


Quote:
I think that the definitions of the system columns should be left as they are.
I don't think so. My opinion is, that if a database structure is not good for a specific task, then it should be changed, upgraded. And current database is not suitable for me, and supposedly for a few other users, too.
And Yes, it is the responsibility of the developer (me), to make that db upgrade optional, and restorable if possible. If db upgrade modifications can not be restored, then warn the user before they buy and use such product.


Quote:
I personally would not want to buy a plugin that changes my existing columns, especially if it breaks core Links features
I aggree. A plugin should not break core Links features, only improving them! If it would break the core Links features, it would mean it is simply not worth to release it...
I can promise, I will only release it, if it will not break core Links features, right?

In case if Alex would aggree to upgrade to date-time usage, then would need a bit modification (adding a few lines) for the new links feature and maybe at a few other places, too.
But as I stated earlier, I do not ask Alex to do that, just suggested to make LSQL more universal in some respects.

In case, if I do database upgrade, I have to be very careful, to take care of the needed changes, what a such database upgrade would need to keep all LSQL features working well.
If I would do such database upgrade in plugin installation, then I do it OPTIONAL, and I do it reversible. When the user uninstalls the plugin, it would restore the previous type, and change data back to yyyy-mm-dd format. If you think about in details, you will find that upgrade & degrade is not really difficult, and can be done without user data loss.

So if I would do any upgrades, I would make it optional, uninstallable, and non-destructive.

I hope this clarification, will calm down upgrade related worries.

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...
Quote Reply
Re: [webmaster33] Missing DATETIME table field type! Several database tables affected In reply to
Sigh. You are still missing the point.

The table editor is there specifically for you to add new columns to your tables and Links SQL is setup to be able to utilize the new columns you add, Links SQL was set up to be flexible and suitable for almost every type of scenario.

The irony is that what you are suggesting is inflexible and constricting. You simply need to use $DB->editor and $DB->creator to create and manage new columns and the default columns should not be touched.

That is how myself and Yogi and every other plugin author writes their plugins and that is how you should do it too. You really need to listen to people with more experience than yourself, especially Alex who has already suggested to add new columns.

Quote:
And current database is not suitable for me

Like I've said a few times, this is what it _really_ boils down to. It is not suitable for *you* and you are unwilling to make an effort to find an alternative route. I'm sure plenty of things are not suitable for me too, but I work with what I have and there is almost always a different route I can take which doesn't involve me asking Alex to re-write code.

I guarantee I could write a plugin that does what you want without Alex needing to change anything.

I'm sure you'll start telling me I'm horrible and mean but the reason I'm getting really annoyed here is because I can see what direction your plugin is heading and it isn't the right direction and I'm only trying to help you get back on track.

If you still insist that Alex re-writes the code (I'd be _very_ surprised if he did)....then I guess we have all been wasting our breath and I will put my energies elsewhere.
Quote Reply
Re: [Paul] Missing DATETIME table field type! Several database tables affected In reply to
I will not add duplicate columns into a database. Our database already has the needed fields, but it is not correctly designed (or it is sub-designed, if we accept that ``YYYY-MM-DD HH:MM:SS'' would already contain all the informations the ``YYYY-MM-DD'' format has).

Bill Gates said: "640KB are enough for everyone".

Some of you say, it is enough to use ``YYYY-MM-DD'' as add date, and nobody will ever need the time information of this format: ``YYYY-MM-DD HH:MM:SS''.
Really nobody will need the time element?
I'm the first example, that it is required. And maybe there are a few others, who do not read that thread...

Alex, please excuse me, and do not misunderstand me. I do not want to hurt you, or to criticize you. Just want to convince Paul, that he is sticking to something, that could be solved without real big problems (but it seems I can not convince him). Unfortunately the subject is affecting your current database structure a bit (at least Add_Date & Mod_Date fields). Sorry.

Paul, I know, there could be possible to add (better say duplicate) 2 database columns, but I do NOT prefer doing such things, to have add date and mod date fields duplicated in the database.
It seems the database structure was subdesigned (IMPO).

Let me ask opinions about that, on some IRC (database related) channels...
Question:
- I have following database fields: Add_Date (DATE type) and Mod_Date (DATE type). I got suggested to add 2 new columns Add_Date2 (DATETIME type), and Mod_Date2 (DATETIME type), just to avoid upgrading Add_Date and Mod_Date column types from DATE to DATETIME, and change of the perl script which is managing the database.
What do you think, what should I do? Should I do database upgrade and change types from DATE to DATETIME, or should I add Add_Date2 and Mod_Date2 new fields with DATETIME type?


Answer1:
- If you had a lot of code that cared what the format wuz, then maybe you would want to just add more columns.
But, if you don't want to fill up your database with duplicate data, just alter your table and fix it and any code that might have to change.
So why are you asking us? To support you wanting to be lazy?
- Nope. While I say the database, should be not have duplicated data, the other guy who suggested me to add 2 duplicate columns, is sticking to his opinion, that: better to have duplicates, that to need to do the changes in the perl script, which is treating the database.
- oh i see.. you want us to support your side in an argument?
- yep, exactly :)
- yes, W33, i agree with you in principle


Answer2 (another channel):
- We have following database fields: Add_Date (DATE type) and Mod_Date (DATE type).
An another developer says, that we should not upgrade it from DATE to DATETIME, but instead we should add 2 new columns Add_Date2 (DATETIME type), and Mod_Date2 (DATETIME type).
- just use wotever data type is appropriate and ditch the other
- An another developer says, that we should not upgrade it from DATE to DATETIME, but instead we should add 2 new columns Add_Date2 (DATETIME type), and Mod_Date2 (DATETIME type).
- So we will have duplicate data in the database just to avoid upgrade of the Add_Date and Mod_Date fields, and change of the perl script which is managing the database.
- storing the same info more than once is just plain stupid


I bolded the final conclusion of discussions. It seems there are several opinions beside mine.
Paul, I think you failed to prove that you are right. Cool

I do not think, that Alex will do the suggested change to DATETIME, because (likely thanks to your influence) now several users may think, that my suggestion is wrong, and my plugin would destroy their databases.
No, this is not true. A correct upgrade solution could seamlessly correct the problem, without affecting any feature or core functionality of LinksSQL.

Quote:
If you still insist that Alex re-writes the code (I'd be _very_ surprised if he did)....then I guess we have all been wasting our breath and I will put my energies elsewhere.
Finally it seems, you are extorting Alex, that you will leave the forum, if he does that change.
Hmmm. That explains itself.

Webmaster33
Quote Reply
Re: [Paul] Missing DATETIME table field type! Several database tables affected In reply to
Leaving the thread now. I would like to stop that arguement, so let me try to stop it, if possible. Ok?
Have to go now. Bye,
Webmaster33
Quote Reply
Re: [webmaster33] Missing DATETIME table field type! Several database tables affected In reply to
Quote:
Paul, I think you failed to prove that you are right.

No you just managed to prove that people on IRC channels have no idea how Links SQL works.

Quote:
I do not think, that Alex will do the suggested change to DATETIME, because (likely thanks to your influence) now several users may think, that my suggestion is wrong, and my plugin would destroy their databases.

That's a silly remark. If Alex thought it was a good idea he would implement it, my posts don't affect his decisions.

Quote:
Finally it seems, you are extorting Alex, that you will leave the forum, if he does that change.
Hmmm. That explains itself.

What on earth are you talking about?....please enlighten me as to when I said I would leave the forum. I really wouldn't leave over your plugins Laugh

Ugh, I give up. Good luck with your plugins.
Quote Reply
Off topic again In reply to
Hi guys, it looks like there is an ensuing argument going on here. Please stick to the topic and be happy.

Although it looks like you are both finished discussing the matter anyways.

Last edited by:

Ian: Sep 7, 2002, 10:27 AM
Quote Reply
Re: [Alex] Missing DATETIME table field type! Several database tables affected In reply to
Just compared a few database features on following URL:
http://www.mysql.com/...rmation/crash-me.php

You can see, that:
DATETIME type is implemented in MySQL, Microsoft SQL, and PostgreSQL. So they supports it.
Oracle also has DATETIME feature, but it is named as DATE type.

As stated on http://www.gossamer-threads.com/...nks-sql/features.htm page, Links SQL is supported on major SQL databases, like Microsoft SQL Server, Oracle, Postgres or MySQL.
Basically all supported databases supports DATETIME. Oracle supports same functionality as DATETIME, but through DATE type. This can be handled through the database driver (or it is already handled by DBI, since Links SQL is based on DBI).

Alex, GT Staff, what do you think?
Could be possible to have DATETIME type implemented to Links SQL?

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

Last edited by:

webmaster33: Dec 28, 2002, 8:57 AM
Quote Reply
Re: [Alex] Missing DATETIME table field type! Several database tables affected In reply to
By the way:
VARCHAR type is also missing from available field types.

VARCHAR is ANSI SQL 92 standard type, and is supported by all major databases, including Microsoft SQL Server, Oracle, Postgres and MySQL (except mSQL, which is basically not supported by Links SQL).

Would be fine to add that type, too.

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...
Quote Reply
Re: [webmaster33] Missing DATETIME table field type! Several database tables affected In reply to
You can use CHAR

CHAR and VARCHAR are identical in terms of their use. The only difference is how they are stored and CHAR takes up a bit more space due to the padding.

Also see:

http://www.mysql.com/..._column_changes.html

Last edited by:

Paul: Dec 29, 2002, 7:33 AM
Quote Reply
Re: [Paul] Missing DATETIME table field type! Several database tables affected In reply to
Yep, this is true.
Quote:
all CHAR columns longer than three characters are changed to VARCHAR columns
I tried this out, and yes, when I Resync-ed the db, I was it was changed automatically to VARCHAR.
I accept your reason, that there is no need for separate VARCHAR type, once MySQL does that change.

But what is the situation with the other databases?
Do they also do that silent change?

I still keep my opinion that DATETIME type is required.

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