schema API add column after
Hello all, I have an alter statement where I add a column at a specified location "ALTER TABLE {example} ADD COLUMN def TEXT AFTER abc" in drupal 5. Checking the schema API I can't see an equivalent to specify the position of the new field, I guess it just appends it at the end of the table. Any way to work around this? -- Ashraf Amayreh http://blogs.aamayreh.org
hello, why do you need to have table column at specified position? On Mon, 2008-06-09 at 14:48 +0300, Ashraf Amayreh wrote:
Hello all,
I have an alter statement where I add a column at a specified location "ALTER TABLE {example} ADD COLUMN def TEXT AFTER abc" in drupal 5.
Checking the schema API I can't see an equivalent to specify the position of the new field, I guess it just appends it at the end of the table.
Any way to work around this?
-- Ashraf Amayreh http://blogs.aamayreh.org
I'm a little confused here, the upgrade guide says how hook_update_N should use the schema API, does this apply to the already existing update hooks or to new ones only? Same goes for the update hook naming conventions that contain drupal major version and so forth, how does this affect the already existing update hooks from the module's 5.x version? On Mon, Jun 9, 2008 at 6:09 PM, Matej Svetlík <matej.svetlik@cetelem.sk> wrote:
hello,
why do you need to have table column at specified position?
On Mon, 2008-06-09 at 14:48 +0300, Ashraf Amayreh wrote:
Hello all,
I have an alter statement where I add a column at a specified location "ALTER TABLE {example} ADD COLUMN def TEXT AFTER abc" in drupal 5.
Checking the schema API I can't see an equivalent to specify the position of the new field, I guess it just appends it at the end of the table.
Any way to work around this?
-- Ashraf Amayreh http://blogs.aamayreh.org
-- Ashraf Amayreh http://blogs.aamayreh.org
Hi, I'd imagine that you could only use the schema API when it's available, so on your 6.x branch of your module. But isn't that when the numbering format changed for update functions too? You should always leave update hooks as is, unless they're buggy, right? On Mon, Jun 9, 2008 at 7:12 PM, Ashraf Amayreh <mistknight@gmail.com> wrote:
I'm a little confused here, the upgrade guide says how hook_update_N should use the schema API, does this apply to the already existing update hooks or to new ones only? Same goes for the update hook naming conventions that contain drupal major version and so forth, how does this affect the already existing update hooks from the module's 5.x version?
On Mon, Jun 9, 2008 at 6:09 PM, Matej Svetlík <matej.svetlik@cetelem.sk> wrote:
hello,
why do you need to have table column at specified position?
On Mon, 2008-06-09 at 14:48 +0300, Ashraf Amayreh wrote:
Hello all,
I have an alter statement where I add a column at a specified location "ALTER TABLE {example} ADD COLUMN def TEXT AFTER abc" in drupal 5.
Checking the schema API I can't see an equivalent to specify the position of the new field, I guess it just appends it at the end of the table.
Any way to work around this?
-- Ashraf Amayreh http://blogs.aamayreh.org
-- Ashraf Amayreh http://blogs.aamayreh.org
-- Regards Steven Jones
Matej Svetlík wrote:
why do you need to have table column at specified position?
I was trying to keep quiet, even though I have this same issue, but I can't now. Think about a new installation as opposed to an updated ("add column") installation. In the new installation, my schema says the columns come in the order, a, b, c, etc. but let's say we now want to add column d. For a new installation, it would look nicer to have it between b and c. The hook_schema can be done this way. But how does one tell MySql that we want it between b and c? In straight MySql, this is easy with the "AFTER COLUMN ..." cause. So, now you're going to ask, "What do you mean by 'it would look nicer'?" So I'll give you an example that I'm actually working on right now. I have a node extension table that has nid, vid, name, and more text fields. I am changing the name column from text to integer so that it can point to new table. I really prefer to keep integer columns (pointers) together. And, in this case, I am replacing one column with another (add new column, insert data, delete old column), so it would be nice to have the new column be in the "same place" the old one was before the update. Make sense? NancyDru No virus found in this outgoing message. Checked by AVG. Version: 8.0.100 / Virus Database: 270.1.0/1492 - Release Date: 6/9/2008 10:29 AM
it would be nice if schema API handled this automatically based on index order in the schema array... That's what I would hope happens... If not maybe someone who wants this feature can write a patch for schema API. On Mon, Jun 9, 2008 at 2:43 PM, Nancy Wichmann <nan_wich@bellsouth.net> wrote:
Matej Svetlík wrote:
why do you need to have table column at specified position?
I was trying to keep quiet, even though I have this same issue, but I can't now.
Think about a new installation as opposed to an updated ("add column") installation.
In the new installation, my schema says the columns come in the order, a, b, c, etc. but let's say we now want to add column d. For a new installation, it would look nicer to have it between b and c. The hook_schema can be done this way. But how does one tell MySql that we want it between b and c? In straight MySql, this is easy with the "AFTER COLUMN ..." cause.
So, now you're going to ask, "What do you mean by 'it would look nicer'?" So I'll give you an example that I'm actually working on right now. I have a node extension table that has nid, vid, name, and more text fields. I am changing the name column from text to integer so that it can point to new table. I really prefer to keep integer columns (pointers) together. And, in this case, I am replacing one column with another (add new column, insert data, delete old column), so it would be nice to have the new column be in the "same place" the old one was before the update.
Make sense?
NancyDru
No virus found in this outgoing message. Checked by AVG. Version: 8.0.100 / Virus Database: 270.1.0/1492 - Release Date: 6/9/2008 10:29 AM
It would be nice to patch it, but I'm sort of worried about upgrading to 6.x for the time being. I'm tempted to think the guidelines for the update hooks only apply to new update hooks and that users upgrading their installations must upgrade to the latest 5.x version of the module before upgrading to 6.x, is this accurate? On Mon, Jun 9, 2008 at 10:26 PM, Darrel O'Pry <darrel.opry@gmail.com> wrote:
it would be nice if schema API handled this automatically based on index order in the schema array... That's what I would hope happens... If not maybe someone who wants this feature can write a patch for schema API.
On Mon, Jun 9, 2008 at 2:43 PM, Nancy Wichmann <nan_wich@bellsouth.net> wrote:
Matej Svetlík wrote:
why do you need to have table column at specified position?
I was trying to keep quiet, even though I have this same issue, but I can't now.
Think about a new installation as opposed to an updated ("add column") installation.
In the new installation, my schema says the columns come in the order, a, b, c, etc. but let's say we now want to add column d. For a new installation, it would look nicer to have it between b and c. The hook_schema can be done this way. But how does one tell MySql that we want it between b and c? In straight MySql, this is easy with the "AFTER COLUMN ..." cause.
So, now you're going to ask, "What do you mean by 'it would look nicer'?" So I'll give you an example that I'm actually working on right now. I have a node extension table that has nid, vid, name, and more text fields. I am changing the name column from text to integer so that it can point to new table. I really prefer to keep integer columns (pointers) together. And, in this case, I am replacing one column with another (add new column, insert data, delete old column), so it would be nice to have the new column be in the "same place" the old one was before the update.
Make sense?
NancyDru
No virus found in this outgoing message. Checked by AVG. Version: 8.0.100 / Virus Database: 270.1.0/1492 - Release Date: 6/9/2008 10:29 AM
-- Ashraf Amayreh http://blogs.aamayreh.org
If you care that much, you're probably looking at the table in something like phpMyAdmin, in which case you can do this: http://dev.mysql.com/doc/refman/5.0/en/change-column-order.html otherwise just query the columns in a different order as chx says. On Mon, Jun 9, 2008 at 9:43 PM, Karoly Negyesi <karoly@negyesi.net> wrote:
Make sense?
Nope. I try to refrain from being a maths egghead but SQL does not need or rely on column order.
Regards
NK
-- Regards Steven Jones
On Mon Jun 9 2008 1:48:02 pm Steven Jones wrote:
If you care that much, you're probably looking at the table in something like phpMyAdmin, in which case you can do this: http://dev.mysql.com/doc/refman/5.0/en/change-column-order.html
otherwise just query the columns in a different order as chx says.
No. What he said was that it doesn't matter what order the columns are in. He didn't say to query them in a different order. They can be queried in any order.
On Mon, Jun 9, 2008 at 9:43 PM, Karoly Negyesi <karoly@negyesi.net> wrote:
Make sense?
Nope. I try to refrain from being a maths egghead but SQL does not need or rely on column order.
Regards
NK
-- Jason Flatt http://www.oadaeh.net/ Father of Six: http://www.flattfamily.com/ (Joseph, 15; Cramer, 13; Travis, 11; Angela; Harry, 7; and William, 2) Linux User: http://www.kubuntu.org/ Drupal Fanatic: http://drupal.org/
I guess that as long as I could insert in any order I want there shouldn't be a problem, although having some users with a table that has columns with a different order than others seems a little awkward. On Tue, Jun 10, 2008 at 2:07 AM, Jason Flatt <drupal@oadaeh.net> wrote:
On Mon Jun 9 2008 1:48:02 pm Steven Jones wrote:
If you care that much, you're probably looking at the table in something like phpMyAdmin, in which case you can do this: http://dev.mysql.com/doc/refman/5.0/en/change-column-order.html
otherwise just query the columns in a different order as chx says.
No. What he said was that it doesn't matter what order the columns are in. He didn't say to query them in a different order. They can be queried in any order.
On Mon, Jun 9, 2008 at 9:43 PM, Karoly Negyesi <karoly@negyesi.net> wrote:
Make sense?
Nope. I try to refrain from being a maths egghead but SQL does not need or rely on column order.
Regards
NK
-- Jason Flatt http://www.oadaeh.net/ Father of Six: http://www.flattfamily.com/ (Joseph, 15; Cramer, 13; Travis, 11; Angela; Harry, 7; and William, 2) Linux User: http://www.kubuntu.org/ Drupal Fanatic: http://drupal.org/
-- Ashraf Amayreh http://blogs.aamayreh.org
On Mon, 09 Jun 2008 22:43:34 +0200 (CEST) "Karoly Negyesi" <karoly@negyesi.net> wrote:
Make sense?
Nope. I try to refrain from being a maths egghead but SQL does not need or rely on column order.
+1 Relying on column order is generally considered bad design and adding columns at a specified place is not supported on all DBs. I'm not sure being able to add a column in a specified place is part of the standard... but this doesn't make it a good design practice. Supporting bad design that risk to break stuff for other DB doesn't look as a good idea. Relaying on column order is hard to maintain and it is always accompanied by "select *" that may lead to very poor performances. eg. you add a column there but you don't tell your co-developer, you add a column there but you forget to add it to all your statements. Result: all your code will break or your statement will return an extra 20Kb of TEXT where it is not needed. You explicitly name columns and there will be higher chances that: a) your code will continue to work in most of the places b) you'll spot the places where you've to change it easier Once you support it in the API people will start to use it. There *may* be performance improvements having fixed size columns at the beginning of the table but this heavily depends on the implementation and well... most of the times when you're looking for performance improvements there... it is like to hope in extra boost adding some more stickers to your Corolla. Not all the times having one more weapon means being safer. -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Mon, Jun 9, 2008 at 4:43 PM, Karoly Negyesi <karoly@negyesi.net> wrote:
Make sense?
Nope. I try to refrain from being a maths egghead but SQL does not need or rely on column order.
Agreed. If you are saying: INSERT INTO tablename VALUES ('a', 1, 2, 3, 'b'); Instead of INSERT INTO tablename (col1, col4, col2, col5, col3) VALUES ('a', 1, 2, 3, 'b'); Then you should change the former to the latter for maintenance's sake. (If you are coding for Drupal 6, better use drupal_write_record() anyways). But, there is a good reason to be able to control the column order: performance. If you have numeric or short columns following varchar or text columns, MySQL has to work more if you do a WHERE on the short columns vs. if the shorter and numeric columns are before the text columns. So, yes, don't rely on column positions from the programming point of view, but from a DBA point of you, they can make a difference. In fact NOT relying on the order when coding makes the life of a DBA easier, since he can move columns around as needed, without breaking code, whereas it would be impossible to do so if the code relies on position. -- Khalid M. Baheyeldin 2bits.com, Inc. http://2bits.com Drupal optimization, development, customization and consulting.
participants (9)
-
Ashraf Amayreh -
Darrel O'Pry -
Ivan Sergio Borgonovo -
Jason Flatt -
Karoly Negyesi -
Khalid Baheyeldin -
Matej Svetlík -
Nancy Wichmann -
Steven Jones