AFTER supported y/n?

Discussion on a SQL Parser for EasyMOD
Locked
User avatar
-=ET=-
Registered User
Posts: 213
Joined: Mon May 26, 2003 1:35 pm
Location: France

AFTER supported y/n?

Post by -=ET=- » Sun Dec 25, 2005 4:38 pm

Hi markus_petrux :)

AFTER is not supported?
SQL PROCESSING ERROR:
No SQL alterations will be performed. However, you may skip SQL processing, continue installing the MOD, and deal with the SQL manually

The following error occured:

Error:
Unexpected or unknown attribute "AFTER FORUM_LAST_POST_ID", in "forum_notify TINYINT(1) UNSIGNED DEFAULT ___3___ NOT NULL AFTER forum_last_post_id", statement: 2, table: "phpbb_forums"

SQL:
ALTER TABLE phpbb_forums ADD forum_notify TINYINT(1) UNSIGNED DEFAULT '1' NOT NULL AFTER forum_last_post_id
SQL PROCESSING ERROR:
No SQL alterations will be performed. However, you may skip SQL processing, continue installing the MOD, and deal with the SQL manually

The following error occured:

Error:
Unexpected or unknown attribute "AFTER USER_REGDATE", in "user_regip char(8) NOT NULL AFTER user_regdate", statement: 1, table: "phpbb_users"

SQL:
ALTER TABLE phpbb_users ADD user_regip char(8) NOT NULL AFTER user_regdate
Thanks :)
Eternal newbie

markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: AFTER supported y/n?

Post by markus_petrux » Mon Dec 26, 2005 9:56 am

Hi ...and Merry Christmas! :)

No, it is not because (even if it is not explicit in the MySQL manual or I do not recall where it is), afaik, it is a MySQL extension to the SQL standard. It is not supported by other SQL engines such as PostgreSQL.

User avatar
-=ET=-
Registered User
Posts: 213
Joined: Mon May 26, 2003 1:35 pm
Location: France

Re: AFTER supported y/n?

Post by -=ET=- » Mon Dec 26, 2005 10:08 am

OK.
And there is no other solution to put a new field in a specific place in a table?

Merry Christmas too!!! :D
Eternal newbie

markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: AFTER supported y/n?

Post by markus_petrux » Mon Dec 26, 2005 10:15 am

I'm curious... why would you do it?

For SQL engines that do not support this option, the only probably way would look something like:

* Extract the table defition and include the new column wherever you wish.
* CREATE newtable.
* INSERT INTO newtable SELECT colum-list FROM oldtable.
* DROP TABLE oldtable.

Ugh!

User avatar
-=ET=-
Registered User
Posts: 213
Joined: Mon May 26, 2003 1:35 pm
Location: France

Re: AFTER supported y/n?

Post by -=ET=- » Mon Dec 26, 2005 10:26 am

markus_petrux wrote: I'm curious... why would you do it?
Just to add new fields at a more logical place than at the end of the table.
And of course, I was asking if any other solution working on all SQL exist.

But ok, it seems not so the fields will be added at the end of the table. I'll not create a new table for this.

Thanks :)
Eternal newbie

wGEric
Registered User
Posts: 521
Joined: Wed Jun 11, 2003 2:07 am
Contact:

Re: AFTER supported y/n?

Post by wGEric » Mon Dec 26, 2005 10:02 pm

Markus, I've been thinking about this, could we have the parser support AFTER only in MySQL and in the other database types, remove it? Lots of people use AFTER so it would be good to support it.
Eric

markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: AFTER supported y/n?

Post by markus_petrux » Tue Dec 27, 2005 7:49 pm

Well, I don't wish to decide what's a good or bad practice. Probably this is something I have not enough knowledge to do. However, while working on the parser I figured there are some things that we all have been doing that aren't completely correct. Probably because, instead of looking at all the corresponding SQL manuals, we have just copy/pasted similar sentences from somewhere else thinking that someone with enough knowledge wrote them correctly.

IMHO, this is something the SQL parser should fight against, until we all find (and agree) a reasonable and reliable way to ofter cross SQL engine compatibility. The first SQL parser written by Nuttzy apart, no one tried to normalize the use of SQL before, so this is something we'll see here.

I believe the parser should tend to allow only options that it is able to translate to any SQL engine. If it accepts the AFTER clausule, it might lead to confusion to people not using MySQL, so this is something I would then classify as a bad practice. ...unless there's a reason to do it, that's why I asked -=ET=- "why would you do it?".

We may have a problem with existing MODs if they use such options, but SQL support was added recently to EM, and all MODs should install correctly using EM to be approved, so it seems a good oportunity to normalize things. MOD authors should make sure their MODs install on a plain phpBB board using EM, therefore they have the oportunity to see if their SQL statements work or not, or if they add new tables or columns, whether the column or table names are reserved keywords, etc. they also could check their SQL/DDL statements using the online conversion tool. The current implementation of the SQL parser tries to digest all pieces of the SQL/DDL statements, if something doesn't look ok, it reports warnings or errors, depending on the severity. Ideally, MOD authors should get no warnings. There might be bugs in the parser, and there are still some options to be implemented though.

asinshesq
Registered User
Posts: 156
Joined: Fri May 14, 2004 10:32 pm
Location: NYC

Re: AFTER supported y/n?

Post by asinshesq » Wed Dec 28, 2005 12:45 pm

I don't see anytihng wrong with allowing the after command for people using mysql and having easymod ignore it for sql servers that don't support it. A lot of existing mods in fact use AFTER and I see no reason to break them.

Having said that, if a decision is made NOT to support AFTER, I think it should be part of the validation process that mods get bounced if they try to use AFTER so that we have as validated mods only those that easymod will fully install (including making all sql changes).
Alan

User avatar
Nux
Registered User
Posts: 943
Joined: Tue Jun 14, 2005 5:09 pm
Location: 3cities, Poland
Contact:

Re: AFTER supported y/n?

Post by Nux » Wed Dec 28, 2005 3:00 pm

asinshesq wrote: Having said that, if a decision is made NOT to support AFTER, I think it should be part of the validation process that mods get bounced if they try to use AFTER so that we have as validated mods only those that easymod will fully install (including making all sql changes).
I agree with that. I think that when EM will be released, SQL parser should be integrated with the MOD validator.

wGEric
Registered User
Posts: 521
Joined: Wed Jun 11, 2003 2:07 am
Contact:

Re: AFTER supported y/n?

Post by wGEric » Wed Dec 28, 2005 6:39 pm

Markus, ok. That works for me and is a very good reason.
Eric

Locked