Asking support for MySQL create/alter column "AFTER" keyword

[Alfonso Serra]

Hi everybody.

I would like to ask for support for the create column "AFTER" another column keyword so whenever we change something in our models the order of the columns in the database is respected. I know this is a cosmetic change but migrations wont mess up column order this way. In large databases with many tables and more columns this may become a reason to disable automatic migrations.

Example:

ALTER TABLE `bookings` ADD COLUMN `total_invoiced` DOUBLE NULL AFTER `rooms`;

Please let us know if its worth the new feature.

Thanks
King Regards.

[Massimo Di Pierro]

I would support to change. Can you help make it happen?

[Alfonso Serra]

I would love to, have some time in December to spend in personal projects.
Any directions or shall i start researching the source code?

[Massimo Di Pierro]

look in web2py/gluon/packages/dal/pydal and search for "ALTER" in all files there and subfolders. It wiil give you and idea of how the logic is implemented.

[Alfonso Serra]

This works only on newly created fields over MySQL. Moving fields around on your model wont reorder them.

Version 2.15.3-stable+timestamp.2017.08.07.12.51.45
migrator.py line 364

                    field_position = sql_fields[key]['sortable']
                    after = "FIRST"
                    if field_position > 1:
                        for f in sql_fields:
                            if sql_fields[f]['sortable'] == field_position - 1:
                                after = "AFTER `%s`" % f
                                break
                    query = ['ALTER TABLE %s ADD %s %s %s;' % (
                        table._rname, sql_fields[key]['rname'],
                        sql_fields_aux[key]['sql'].replace(', ', new_add), after)]

Theres no way to implement column reorder because the db is not being asked for the table schema, this information is read from the migration files. This function only checks whther the fields on your model are in the migration files, in no particular order. It would be nice if there were a way.

[Alfonso Serra]

Hi Massimo

I have created standalone functions that reorders table fields and support for indexing regular fields by adding "indexed" to the Field signature at objects.py

Before trying the script please add indexed=False to the Field signature and self.indexed = indexed in objects.py, or overwrite with the attached objects.py file.

Its naive and in progress as it only supports common mysql types.

Please let me know how it looks, ill try to integrate this into pydal on my own but i still have to study how.

Thanks

Best Regards.