View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 4909 | Composr | core_database_drivers | public | 2022-08-25 20:40 | 2022-08-26 17:01 |
| Reporter | Chris Graham | Assigned To | Guest | ||
| Priority | normal | Severity | feature | ||
| Status | new | Resolution | open | ||
| Summary | 4909: Improve support for non-MySQL database drivers (holding issue) | ||||
| Description | This issue seeks to flag functionality/optimisation that is currently only implemented for MySQL. The following parts of our ecosystem are MySQL-specific: - Automatic database creation when installing, if installing using a MySQL admin account - Some non-bundled addons marked as requiring MySQL, at the time of writing: - trickstr (reason: third party code written specifically for MySQL) - composr_homesite (reason: Demostratr set up) - composr_homesite_support_credits (reason: direct SQL table creation/deletion for Mantis BT integration using MySQL syntax) - composr_release_build (reason: MySQL SQL dumps need building) - Parts of testing_platform (__installer, __installer_forum_drivers, ___demonstratr, ___database_integrity) - Esoteric performance optimisations for certain cases of large amounts of data are only for MySQL (the MySQL support is heavily optimised for a wide range of high load scenarios) - Delayed inserts (reason: MySQL-specific) - REPLACE INTO queries (reason: MySQL-specific) - Index hints (reason: MySQL-specific) - Prefixed string index (reason: MySQL-specific syntax). Other databases support expressions on indexes (which MySQL does not), so this feature could be added via a new abstraction: - https://stackoverflow.com/questions/21823949/how-to-add-a-prefix-length-index-in-postgresql - https://dba.stackexchange.com/questions/30032/what-are-the-alternatives-for-prefixed-indexes-in-oracle - https://www.db2tutorial.com/db2-index/db2-expression-based-index/ - (Not supported on SQL Server) - Checking table existence (in table_exists) by directly asking database rather than using Composr's meta tables (reason: could be implemented but would need a new abstraction; many databases support information schema though) - Loading data from multiple catalogue field tables at once using UNION queries (in _get_catalogue_entry_field) (reason: while UNION is widely supported, we are not currently assuming that is the case and there may be esoteric differences in how it works) - The sql_dump page in the meta_toolkit addon can do a fast dump mysqldump (reason: MySQL-specific but likely could be extended to other databases) - Disabling indexes during mass-import (in set_database_index_maintenance), then rebuilding later (reason: MySQL specific syntax but could be extended on more databases) - https://fle.github.io/temporarily-disable-all-indexes-of-a-postgresql-table.html - https://community.oracle.com/tech/developers/discussion/621318/how-to-disable-the-index-in-oracle - https://www.sqlservertutorial.net/sql-server-indexes/sql-server-disable-indexes/ - (Not supported on DB2) - Table meta-querying (note: these could abstracted alongside existing code in admin_phpinfo and db_table_sizes that do similar things): - Commandr-fs feature for listing when tables were last updated is only for MySQL (other database backends tend to not provide last-updated timestamps for tables) (reason: MySQL specific syntax but could be extended on more databases) - Health Check for very large database tables (reason: MySQL specific syntax but could be extended on more databases) - Documentation is written with MySQL in mind, particularly tutorials relating to installation, performance, and maintenance (however experienced system/database administrators will be able to adapt instructions to the systems they use without too much trouble) - Table repair and optimisation: - Database repair cleanup tool/Health Check (we use this to help test our upgrade code works perfectly, or to make it easier for 3rd-party developers who don't know how to correctly code to our database meta-system; it is not required and SQL structure dump comparison will work as a substitute in most cases) (reason: MySQL-specific deep table analysis, would need separate implementations for other databases) - Optimiser/repair cleanup tool/upgrader tool/Health Check (other database backends are better at doing automatic cleanup) (reason: MySQL-specific) - Cleanup of orphaned indexes in rebuild_all_cpf_indices - Basic checks: - Minimum version Health Check (MySQL is the only serious database backend that tends to not support basic stuff until recent versions!) (reason: MySQL-specific syntax, would need abstracting) - Basic configuration checks (MySQL is the only serious database backend that tends to not support basic stuff until recent versions!) (reason: MySQL-specific syntax, would need abstracting) - The bundled [tt]rootkit_detector[/tt] addon (reason: this is developed for experts and bakes in assumption of [tt]mysqli[/tt] due to running outside of Composr; the code can be customised to other backends as required) - Extra development mode security checks designed to help ensure amateur Composr developers write secure code - Security check to see if 'UNION' leaked into a query (reason: MySQL-specific query parsing) - dev-mode checks for non-escaped query components, unnecessary hand-written SQL (reason: MySQL-specific query parsing) - Laxness modes designed to help ensure amateur Composr developers write or port code without knowing too much about database portability, or to reduce fatality of errors during upgrades (reason: MySQL is the only database backend that has non-strictness, and in other cases we are just doing some niceties to smooth things over when developers assume MySQL) - Addon dependency checking can only check for MySQL (reason: it is unlikely any addon is going to require anything else, and it would be open-ended) - Automatic killing of slow database searches (reason: MySQL specific, and tends to be a bigger issue on MySQL due to MyISAM locking being inefficient) Nothing in the above list is likely to be of concern for the vast majority of Composr users who also who would want to use a non-MySQL database. MySQL string comparisons are case insensitive. Other databases are (probably all) case sensitive. The following parts of our ecosystem are Oracle-specific: - Fulltext search index synchronisation Cron hook (reason: Oracle doesn't automatically maintain fulltext indexes) The following parts of our ecosystem are supported by only some database drivers, but could be for more with better abstraction: - Showing database server version and active queries on the PHP-Info page (reason: non-standard syntax that is not used enough to warrant abstracting) - Commandr [tt]db_table_sizes[/tt] command (reason: non-standard syntax that is not used enough to warrant abstracting) - Proper Unicode support (so full-text search will search using knowledge of Unicode characters, so sorting will, and maximum column lengths will be based on character length) (reason: complexities to be worked out) The following are covered for all bundled database drivers, but will not work with non-bundled database drivers: - Installer bypassing listing database drivers where PHP extensions are missing for them (reason: hard-coded into install.php for performance) Not all databases have to support the following functionality, which is unavoidable by the core development team: - Full-text search - Third-parties writing code that assumes (intentionally or not) MySQL | ||||
| Tags | No tags attached. | ||||
| Attach Tags | |||||
| Time estimation (hours) | |||||
| Sponsorship open | |||||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2022-08-25 20:40 | Chris Graham | New Issue | |
| 2022-08-26 16:52 | Chris Graham | Description Updated | |
| 2022-08-26 17:01 | Chris Graham | Description Updated |
