Doesn't this need to be done for mssql and postgresql as well?

Possibly, but I don't know enough about mssql and postgresql to make those changes, if they are needed.

@brianteeman

• PostgreSQL and MS SQL do not support unsigned integer (there is apparently a complex work around to add a constraint to disallow negative values but I doubt it's worth the extra effort)

I do agree that the other consistency issues with the common column types should be checked for in the PostgreSQL and MS SQL files.

Possibly, but I don't know enough about mssql and postgresql to make those changes, if they are needed.

Generally, I try to find a column in the schema that matches my desired end state and copy that when making changes in the non-MySQL schemas. If you know something is "right" in MySQL, find the corresponding definition in the other schemas and see how its done there. I know there are some quirks like the unsigned integer thing, but beyond that rarely have I had to dig into the manuals to get usable schema; the core definitions have enough working examples to copy/paste.

Thanks, @mbabker I'll see if I can look at the non-MySQL schemas after work today.

There are a couple of things we may want to consider to be consistent.

• int vs bigint for PK and FK id columns (the non-MySQL schemas seem to lean towards bigint)
  • For consistency, I think bigint should be standardized across the schemas for PK and FK id columns
• MySQL tinyint should correspond to smallint for non-MySQL schemas ( PostgreSQL doesn't have a tinyint type and MS SQL tinyint is an unsigned type).
  • alternative for consistency across all schemas would be to standardize to smallint

shouldn't this change also be done on Joomla update?

Yes it will need to be done on an update as well.

Am I right in understanding that the "on update" database changes are covered by the update manifest sql files in com_admin (which also cover the database fix function)?

So we will need to add some update manifest files to cover the changes via alter table sql statements.

@photodude IIRC there is no one FK in the joomla schema, i can help you on postgres side

@photodude for updates we need a SQL file here: https://github.com/joomla/joomla-cms/tree/staging/administrator/components/com_admin/sql/updates

@alikon your help with improving the consistency of the postgres database table columns would be appreciated.

Thanks @zero-24, I will add the add update manifest SQL files for the changes to that location to cover updates.

So I'm getting a couple issues with the update manifest SQL file.

• Some columns are not adding the "Attributes" UNSIGNED
  • Same issues with applying the change with MODIFY or CHANGE
• Columns that did get modified with UNSIGNED are still throwing an error in Extensions: Database that the table ___ does not have column ___ with type ___
  • Same issues with applying the change with MODIFY or CHANGE
  • removing UNSIGNED from the columns throwing the errors does fix the warning

I find this behavior very strange... especially since changing the field directly in the database uses the same syntax for the change. either ALTER TABLE `#__contact_details` CHANGE `access` `access` int(10) UNSIGNED NOT NULL DEFAULT '0'; or ALTER TABLE `#__newsfeeds` MODIFY `catid` int(11) UNSIGNED NOT NULL DEFAULT '0';

@alikon I have attempted to improve the consistency of the postgres database table columns. Your help in checking those changes would be appreciated.

@mbabker,

I haven't been able to resolve the mysql update throwing an error in Extensions: Database about the table ___ does not have column ___ with type ___. It seems like something in the update script is having issues applying the "Attributes" UNSIGNED

Any suggestions on what's going wrong with the mysql update stuff?

Other than that, and review of the non-MySQL schemas, I think this is ready for consideration.
Unless we want to do the standardization to bigint and smallint in the mysql schema to improve the cross schema consistency between the different database types...

Your issue probably lies in https://github.com/joomla/joomla-cms/blob/staging/libraries/cms/schema/changeitem/mysql.php#L143-L164

Thanks @mbabker Looking at the code I think the issue is due to the fixinteger() function

I think it's having issues with things like adding unsigned to things like int(11), Should I try fixing that in this PR or open a separate PR? I lean towards a separate one.

Separate one, not related to this.

Patch #10538 is needed to test this PR with mysql

Is there any interest in moving forward with this PR?

Durring teststing of #10538 O incounter following error related to this PR
@photodude I retested now case with sampledata using following test procedure -
after each step checking whether the structure of the database is correct.

1. Install staging on commit 9e596d1 with Test English (GB) Sample Data

   Database table structure is up to date.

2. install patch tester v 2.0.1

   Database table structure is up to date.

3. Applay #10098 on commit f7c2ea3

   Table '#__contact_details' does not have column 'access' with type int(10). (From file 3.6.0-2016-05-16.sql.)
   Table '#__finder_links' does not have column 'access' with type int(10). (From file 3.6.0-2016-05-16.sql.)
   Table '#__finder_taxonomy' does not have column 'access' with type int(10). (From file 3.6.0-2016-05-16.sql.)
   Table '#__newsfeeds' does not have column 'id' with type int(10). (From file 3.6.0-2016-05-16.sql.)
   Table '#__newsfeeds' does not have column 'access' with type int(10). (From file 3.6.0-2016-05-16.sql.)
   

4. Click fix button

   Table '#__contact_details' does not have column 'access' with type int(10). (From file 3.6.0-2016-05-16.sql.)
   Table '#__finder_links' does not have column 'access' with type int(10). (From file 3.6.0-2016-05-16.sql.)
   Table '#__finder_taxonomy' does not have column 'access' with type int(10). (From file 3.6.0-2016-05-16.sql.)
   Table '#__newsfeeds' does not have column 'id' with type int(10). (From file 3.6.0-2016-05-16.sql.)
   Table '#__newsfeeds' does not have column 'access' with type int(10). (From file 3.6.0-2016-05-16.sql.)
   

5. Applay #10538 on commit 78798b2

   Table '#__banners' does not have column 'id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__banners' does not have column 'cid' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__banner_clients' does not have column 'id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__categories' does not have column 'id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__contact_details' does not have column 'id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__contact_details' does not have column 'user_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__contact_details' does not have column 'catid' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__content_frontpage' does not have column 'content_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__content_rating' does not have column 'content_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__contentitem_tag_map' does not have column 'content_item_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__extensions' does not have column 'extension_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__menu' does not have column 'id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__modules' does not have column 'id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__modules_menu' does not have column 'moduleid' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__modules_menu' does not have column 'menuid' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__newsfeeds' does not have column 'catid' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__overrider' does not have column 'id' with type int(10) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__postinstall_messages' does not have column 'extension_id' with type bigint(20) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__schemas' does not have column 'extension_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__session' does not have column 'userid' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__ucm_base' does not have column 'ucm_item_id' with type int(10) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__ucm_base' does not have column 'ucm_type_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__updates' does not have column 'update_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__updates' does not have column 'update_site_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__updates' does not have column 'extension_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__update_sites' does not have column 'update_site_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__update_sites_extensions' does not have column 'update_site_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__update_sites_extensions' does not have column 'extension_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__users' does not have column 'id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__user_profiles' does not have column 'user_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   

6. Click fix button

   An error has occurred.
   1062 Duplicate entry '19-0' for key 'PRIMARY' SQL=ALTER TABLE #__modules_menu MODIFY menuid int(11) UNSIGNED NOT NULL DEFAULT '0';

7. Check database status one more time

   Table '#__modules_menu' does not have column 'menuid' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__newsfeeds' does not have column 'catid' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__overrider' does not have column 'id' with type int(10) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__postinstall_messages' does not have column 'extension_id' with type bigint(20) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__schemas' does not have column 'extension_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__session' does not have column 'userid' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__ucm_base' does not have column 'ucm_item_id' with type int(10) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__ucm_base' does not have column 'ucm_type_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__updates' does not have column 'update_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__updates' does not have column 'update_site_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__updates' does not have column 'extension_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__update_sites' does not have column 'update_site_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__update_sites_extensions' does not have column 'update_site_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__update_sites_extensions' does not have column 'extension_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__users' does not have column 'id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   Table '#__user_profiles' does not have column 'user_id' with type int(11) unsigned. (From file 3.6.0-2016-05-16.sql.)
   

   More details in #10538 (comment) and later.

IMHO it should be int(10) UNSIGNED, not int(11) UNSIGNED for mysql.

+  `catid` int(11) UNSIGNED NOT NULL default '0',
+  `id` int(10) UNSIGNED NOT NULL auto_increment,

@csthomas I believe int(##) is just the mysql display width and does not constrain the stored value in any way. As such I would agree that to be consistent we should standardize the display width.

As mentioned in a previous comment, we should probably standardize to bigint and smallint in the mysql schema to improve the cross schema consistency between the different database types... so if the column is a PK or FK we would use bigint (which would be almost all of the int(10) and int(11) items).

The sample data files sample_learn.sql and sample_testing.sql have negative values for menuid this results in the following error 1062 Duplicate entry '19-0' for key 'PRIMARY' SQL=ALTER TABLE #__modules_menu

@wojsmol the sample data is for menuid is currently not compatible with an unsigned data type. We'll have to consider changing the sample data or excluding menuid from the list of unsigned types. I'm in favor of changing the sample data.

It might not be the sample data but rather how the option to exclude a
module from showing on a given menu item works.

On Wednesday, June 22, 2016, Walt Sorensen notifications@github.com wrote:

The sample data file sample_learn.sql
https://github.com/joomla/joomla-cms/blob/staging/installation/sql/mysql/sample_learn.sql
has negative values for menuid this results in the following error 1062
Duplicate entry '19-0' for key 'PRIMARY' SQL=ALTER TABLE #__modules_menu

@wojsmol https://github.com/wojsmol the sample data is for menuid is
currently not compatible with an unsigned data type. We'll have to consider
changing the sample data or excluding menuid from the list of unsigned
types. I'm in favor of changing the sample data.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#10098 (comment),
or mute the thread
https://github.com/notifications/unsubscribe/AAWfobQrBUCuKrM4wi1HhD-uY0IYUvLpks5qOfVEgaJpZM4IP9zw
.

Given @mbabker's comment, I'll revert menuid to a signed type.

@andrepereiradasilva would you consider reviewing/testing this?

About mssql, postresql, etc and unsigned.

http://stackoverflow.com/questions/2991405/what-is-the-difference-between-tinyint-smallint-mediumint-bigint-and-int-in-m

IMHO changing unsigned integer to bigint for fix inconsistency is too much.
That columns do not have to have that big type.

May be better way would be to forbid to use unsigned word for smallint, int, bigint, etc.

I would remove only unsigned "word" at all but probably someone say that it will break B/C.
Because it changes positive range to half.
What do you think?

Cross db language consistency is something I have considered. I haven't put too much thought or effort into trying to determine what would be the best design for cross db language consistency other than to suggest standardization to bigint and smallint. My original intent was just to attempt to fix the inconsistencies in the column types across the various tables.

Is there any interest in moving forward with this PR?

Closing as there doesn't seem to be enough interest in Addressing Inconsistency in the Database schema