News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

InnoDB table engine in smcFunc?

Started by Glyph, October 01, 2016, 03:32:52 PM

Previous topic - Next topic

Glyph

I am currently working on a plugin to add InnoDB support for table creation, i'm finding however that this could be a core feature....
It would only mainly help plugin developers, but it would make things alot easier for foreign key designs. (plugins are meant for general clients and this isn't really helpful for general clients...)

So, here's the proposal
In DbPackages-mysql.php

Replace: function smf_db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal')
With: function smf_db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal', $engine = 'MyISAM')

This way it defaults to MyISAM and there is the option for InnoDB or whatever else anyone wants to add later on.

around line 145 there's the area for adding foreign key options, constraints, etc.. Using the FK and constraints to the index array as optional parameters. and of course modifying line 164. $table_query .= ') ENGINE=MyISAM';


Also it looks like the functions "smf_db_list_indexes", "smf_db_remove_index", and "smf_db_add_index" would also need to be modified.

And then last but not least the wiki pages too. :)

What do you guys think? Should I make it a plugin or go straight for the core? I'm assuming I would never see it until 2.1 however...


It also just dawned on me that not all servers have innodb...

sooo something like this might help, eh?

db_extend('packages');

// Database Functions - We sail the seas!
global $smcFunc;

// Are we using MySQL, and do we support InnoDB?
if ($smcFunc['db_title'] === 'MySQL'){
$result = $smcFunc['db_query']('', '
SELECT SUPPORT
FROM INFORMATION_SCHEMA.ENGINES
WHERE ENGINE = \'InnoDB\''
);
// Fetch value - If not yes then t' Davy Jones' locker with ya'
while($row = $smcFunc['db_fetch_assoc']($result)){
$support = $row['SUPPORT'];
}
if ($support === 'YES'){
// Alter even if it's already InnoDB; It's a waste of CPU cycles checking.
$smcFunc['db_query']('', '
ALTER TABLE {db_prefix}members
ENGINE=InnoDB'
);
} else { // Davy Jones' locker
            $smcFunc['db_free_result']($result);
            die('<b>Plugin Error</b>: No InnoDB support found');
}
    $smcFunc['db_free_result']($result);
}
Personal TODO:

albertlast

The innodb support is already in place for version 2.1.
Or is here any feature missing from your side?

Glyph

Quote from: albertlast on October 02, 2016, 02:06:18 AM
The innodb support is already in place for version 2.1.
Or is here any feature missing from your side?

I have not yet tried 2.1; but this is good news! I'll take a look!
Personal TODO:

live627

Quote from: albertlast on October 02, 2016, 02:06:18 AM
The innodb support is already in place for version 2.1.
Or is here any feature missing from your side?
Just to be clear - not talking about the installer here.

Glyph

#4
I see the support is there. However I don't see anything for foreign key constraints. I think it would help the plugin community alot to have that feature there.

Although an afterthought is not too many plugin developers will be following it anyhow... but for those of us that do it would be nice.

i.e. This example shows where FK constraints would be beneficial in a design.


Without them it would not work as well; possible, but not ideal.

So, I could see something like a foreign key parameter in an array to choose one or more foreign keys, and then constraint options for each (ON UPDATE | ON DELETE) and then reference options along with that (RESTRICT | CASCADE | SET NULL | NO ACTION)

Then of course with add/remove index functions.
You can add a new foreign key constraint to an existing table by using ALTER TABLE. The syntax relating to foreign keys for this statement is shown here:
ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]



You can also use ALTER TABLE to drop foreign keys, using the syntax shown here:
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;



Now, one issue I can see is making sure to catch all of the errors. Even if foreign_key_checks = 0, MySQL does not permit the creation of a foreign key constraint where a column references a nonmatching column type. Also, if a table has foreign key constraints, ALTER TABLE cannot be used to alter the table to use another storage engine. To change the storage engine, you must drop any foreign key constraints first.

So first off this poses a problem if tables are mixed. So forcing one or the other might be a good idea to save headaches.
Personal TODO:

albertlast

Fk are good when you use natural pk,
but this is unlikly in thie project, every table use an ai pk field --> unnatural pk.
So ther is no update to change this pk.

Other thing is data quality,
that you can only insert id in detail table which exists in the main table,
but the question why should you try to insert a wrong fk_id?

The problem when you got a complex fk table construct you need to watch out to fill the table in right order (which can be hard!),
so in good database (not mysql) you can change the fk to be defferend https://www.postgresql.org/docs/9.6/static/sql-set-constraints.html
so that they checked after the transaction is commited.
In mysql your are lost, you can disable fk checks SET FOREIGN_KEY_CHECKS=0; --  SET FOREIGN_KEY_CHECKS=1;
but than is no check if the data are correct.

the cascading delete is in most case not wanted,
example someone harm you board but this is year ago.
When his account is delete you only got his id or even the log entry is deletet so you got nothing from this user.

Another important note smf 2.1 is not only support mysql, the postgresql support is heavy extended so
in the best case what ever you do should work in both.

Glyph

#6
Unfortunately my host does not support postgreSQL. :(

What I meant earlier is for _my_ specific project that is using Foreign Keys should have an SMF function support it. Because it would assist in my development. (i won't have to craft custom sql queries) I'm not going to convert the entire core SMF database over to FK though.

To re-iterate: If there was a function to create FK constraints through an array in the "$smcfunc (create_table, blah)" it would make life easier for me or devs; i think. Not to mention this huge discussion on the subject: http://stackoverflow.com/questions/5493780/do-you-absolutely-need-foreign-keys-in-a-database

Quotebut the question why should you try to insert a wrong fk_id?
I don't know what you mean by this; am i inserting an incorrect fk?

CREATE TABLE IF NOT EXISTS `mydb`.`characters` (
  `idcharacters` INT NOT NULL AUTO_INCREMENT,
  `classes_idclasses` INT UNSIGNED NOT NULL,
  `members_idmembers` INT NOT NULL,
  PRIMARY KEY (`idcharacters`),
  INDEX `fk_characters_classes_idx` (`classes_idclasses` ASC),
  INDEX `fk_characters_members1_idx` (`members_idmembers` ASC),
  CONSTRAINT `fk_characters_classes`
    FOREIGN KEY (`classes_idclasses`)
    REFERENCES `mydb`.`classes` (`idclasses`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_characters_members1`
    FOREIGN KEY (`members_idmembers`)
    REFERENCES `mydb`.`members` (`idmembers`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)


This would be the SQL Pseudocode


http://www.w3schools.com/sql/sql_foreignkey.asp (good source for basic mysql FK)
So I think this would be a good addition to SMF still..
Personal TODO:

albertlast

And why you do this?

btw this is no sql code this is mysql code.

Arantor

If you want to add such things there's nothing stopping you issuing the query to add FKs. The practical reality is that it simply isn't worth the dev time to implement the functions required for adding and removing FKs in both MySQL and PostgreSQL for the handful of mod authors who would ever use them.

That reminds me, I need to check sometime if 2.1 will actually work correctly if you make all its tables InnoDB. Certainly 2.0 for a long time didn't work correctly if you made certain tables InnoDB though the biggest place was patched. Not sure whether 2.1 has the same issue or whether it was fixed and if so how.

Glyph

Quote from: Arantor on October 05, 2016, 03:04:20 AM
If you want to add such things there's nothing stopping you issuing the query to add FKs. The practical reality is that it simply isn't worth the dev time to implement the functions required for adding and removing FKs in both MySQL and PostgreSQL for the handful of mod authors who would ever use them.

That reminds me, I need to check sometime if 2.1 will actually work correctly if you make all its tables InnoDB. Certainly 2.0 for a long time didn't work correctly if you made certain tables InnoDB though the biggest place was patched. Not sure whether 2.1 has the same issue or whether it was fixed and if so how.

I see. Will it be frowned on using straight Foreign Key MySQL queries through $smcFunc(db_query) in a plugin?

I'de love to submit this feature to the core of SMF myself but I don't think i'de be able to cover all the bases (I know little MySQL-Fu) So things like security issues may come up that I just can't see yet. I don't know a whole lot about PostgreSQL either so i'de be restricted to MySQL; here we have inconsistency with PostgreSQL (the plugin would break on PostgreSQL unless there was a check in core before using that particular feature in $smcFunc(db_query) to make sure that the persons forum was using MySQL; with say $smcFunc['db_title'] for example.)


This would of course save database design time for people stuck with MySQL. But in the future I think more hosts will be supporting PostgreSQL... even I will be getting a host in the future with PostgreSQL support; which means even making this for MySQL is going to be a waste of time... I guess there's nothing more to really discuss i'm just rambling at this point. Thank you guys for the insight. This is a very interesting time to be involved with web development with all the new technology emerging (mostly the new frameworks and maturing databases) So many choices!!
Personal TODO:

Arantor

If you're planning to release it here it might raise some questions, but other than that, go nuts.

Also, "saving database design time"? Uh... It hasn't, at any point in the last 15 years of SQL, helped me save any time to formally declare key constraints. But I may be an outlier.

Glyph

I mean as far as creating an ER diagram to make things alot easier to understand and impliment (like i said im no mysql master) so having that diagram helps me visualize how to put everything together. (Like coloring in a coloring book with the sections labeled with colors instead of actually thinking about it heh)
Personal TODO:

Advertisement: