InnoDB - Foreign Key Constraints?

Discussion forum for Extension Writers regarding Extension Development.
Post Reply
Glyph
Registered User
Posts: 3
Joined: Mon Oct 03, 2016 1:51 am

InnoDB - Foreign Key Constraints?

Post by Glyph » Mon Oct 03, 2016 1:58 am

Hello, i'm new here and I am looking around at a bunch of different forum systems trying to figure out what works best for my project. The website looks nice, and I like the forums here. So... on to my question!

My extension project requirements:

InnoDB - with Foreign Key Constraints


Does PHPBB have a native function for creating InnoDB tables? Does it support foreign keys as well? And also reference options?

Thank you for your time and expertise! If you have any other questions or need any specifics I will get back to you as quickly as possible.
- Glyph

User avatar
gn#36
Translator
Posts: 185
Joined: Fri Oct 13, 2006 1:16 pm
Contact:

Re: InnoDB - Foreign Key Constraints?

Post by gn#36 » Sun Oct 09, 2016 2:36 pm

phpBB can use innodb tables. My local installation for testing purposes has nothing but innodb tables. phpBB supports transactions as well, so you can use rollbacks as well. You can have a look into the phpbb/db/ folder to see how it handles the database (especially check the tools.php file for table creation, seems to me it uses the "default" table type of the database server, it does not specify innodb explicitly).

But: It does not use foreign key constraints as far as I know. So it "manually" deletes (or moves) posts if the corresponding forum gets deleted. Since the order in which this is done in phpBB is not necessarily the order needed for foreign key constraints inside the db, I would also not advise changing this on existing tables without intense testing of the side effects (unit tests should cover most of this, though if you wish to do this).

However, if you write a custom extension for yourself, you can of course use foreign keys on your own stuff.

You will still be able to use the remainder of the database abstraction layer (dbal) in phpBB just fine. Of course that somewhat defeats the purpose of the dbal, but for your own custom extension, I think that is ok. You can obviously also reference existing tables with your foreign keys if you like. If that causes trouble on an existing function, you have likely found a bug in your extension then.

I am not sure whether you would still be permitted to enter your extension into the extension database though, since using foreign key constraints will defeat the dbal and will not work on any database except for mysql/mariadb (or whatever db you create them for - I guess they all support foreign keys in a different way). But I like the concept because it prevents some errors and data inconsistencies.
German Support Team Member • http://www.phpbb.deMy Extensions in the CDBMy Extensions on Github • Contact with caos is inavoidable but no catastrophy if you keep an overview.

Glyph
Registered User
Posts: 3
Joined: Mon Oct 03, 2016 1:51 am

Re: InnoDB - Foreign Key Constraints?

Post by Glyph » Tue Oct 11, 2016 2:31 am

Firstly, thanks for taking the time to reply here. I've never heard of the DataBase Abstraction Layer. After reading up on the concepts I know now that this is exactly what I was trying to express.

My goal here is modifying the DBAL to add more parameterized functionality to those database functions by adding an optional extra array of parameters to support all of the functions of foreign key relations. (mysql, postgresql, mariadb) My next issue would be in adding those parameters and being able to still make all dependencies function properly for each of those databases, and then making sure the security of the code and the database is correct by all standards in relation to the forum itself. (coding standards, etc, etc...)

It would be nice if a free forum software existed that already had this; to avoid errors and improve data integrity; and for those who rely heavily on ER models. I really don't want to add this myself, but it looks like it won't get done if not; So, now I have to figure out which is the best free forum software out there to devote this to! :)

User avatar
gn#36
Translator
Posts: 185
Joined: Fri Oct 13, 2006 1:16 pm
Contact:

Re: InnoDB - Foreign Key Constraints?

Post by gn#36 » Tue Oct 11, 2016 6:23 pm

The trouble with this would be that the forum software can always only use the features that are supported by all database backends unless there is a way to emulate the behavior for the remainder of the backends.

I guess most database backends nowadays support some sort of referential integrity by using foreign keys, but the handling may be different, which would need to be covered. So you would need to review not only mysql, postgres and mariadb, but also sqlite, sqlite3, mssql and oracle. I am not sure whether sqlite (aka sqlite 2) even supports this, I think this was introduced somewhere in sqlite3.

I do see the benefits of this approach for several applications:
  • Conversion from another forum software to phpBB should no longer cause invalid database entries with posts not belonging to any topics, topics not in any forum, PMs that are in no users folder, post references not actually pointing to a post or things like that. This would have saved me a lot of headaches in the past.
  • The database itself can take care of deletion of entries in several tables if the referenced element gets deleted. Thus, less SQL queries and no more accidentally forgotten entries in the db. Alternatively, one could forbid deletion if referenced objects are still there, in which case this acts somewhat as a debugging tool. This should help developers and extension developers alike if they use this correctly.
  • The database relations are explicitly clarified in the database structure.
Getting back at your change to the dbal: Since this is a feature of the database itself, I think you would mainly have to adjust the table creation and change process, not the every day interaction with the database. SQL selects, updates, inserts and deletes would still function the same way, except that the database will check for restrictions on the inserts and deletes.

I am not sure where you are going with your other points, though. phpBB has had a security audit and enforces its coding guidelines as well for every change that is made to the core. It uses unit tests and functional tests to prevent degradation of the code and every change is reviewed by someone else from the development team before it is merged into the core. I also don't know what parameters you want to "add". References between the tables are of course already present, the only difference is that no foreign key constraints are set. So for everyday database interaction of the forum, you shouldn't even notice the change, only additional constraints should be necessary.
German Support Team Member • http://www.phpbb.deMy Extensions in the CDBMy Extensions on Github • Contact with caos is inavoidable but no catastrophy if you keep an overview.

Glyph
Registered User
Posts: 3
Joined: Mon Oct 03, 2016 1:51 am

Re: InnoDB - Foreign Key Constraints?

Post by Glyph » Wed Oct 12, 2016 2:47 am

I would like to add the parameters to create foreign key relations (or constraints) (on update/delete, cascade, etcc) the other databases have something similair. (I.e. instead using triggers in the myisam engine in mysql)

Judging by the activity on phpbb, the rapid development, very few cve vulneravilities in the last 5 years that indicate good security practices (this ones controversial), spot on team members, and being one of the only places ive actually found someone interested in this feature; phpbb is the way to go!

I am also hooked on the fact that symfony is involved in all of this. MVC structure, database, design, and just everything is absolutely the best of all the free forums ive quickly gone over.

The only things im concerned about are.... Well nothing really.. unless because of its popularity its a huge hacking target like wordpress. Thats the possible gotcha i think. I digress though, thank you for your help. As soon as i get some time (currently in the process of moving) i will be getting some code started on this core feature!

User avatar
3Di
Former Team Member
Posts: 13662
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milan (IT) Frankfurt (DE)
Name: Marco
Contact:

Re: InnoDB - Foreign Key Constraints?

Post by 3Di » Wed Oct 12, 2016 3:29 am

The current DBAL will be moved probably to the Doctrine's one, there are climbs of hope.
Just a note, to be considered before to start something about that.

https://tracker.phpbb.com/browse/PHPBB3-14463
Please PM me only to request paid works. Thx.
Want to compensate me for my interest? Donate
My development's activity º PhpStorm's proud user
Extensions, Scripts, MOD porting, Update/Upgrades
👨‍🏫 | Take a tour to | The Studio | 👨‍🏫

Post Reply

Return to “Extension Writers Discussion”