Extension install scripts without raw sql

Question

I am new to Joomla development, coming from Magento. Working through the tutorials with a certain extension in mind I stumbled upon a little problem.

By now Joomla supports MySql and MSSQL and more might come.

From Magento I am used to build Extensions with Database agnostic install scripts, since I have no influence on which database the end user will run.

In the Joomla tutorials I only get told to use raw sql to create tables and stuff, but there are some distinction in Syntax between MySQL and MSSQL and maybe even more differences to other sql implementations which joomly might support soon.

In my php files I can simply use the Database adapters Joomla provides in order to keep things abstract, is there a way to use .php files as install scripts instead of .sql files? Or is there another way to create install scripts which work with any database? Or is joomla actually interpreting the MySQL syntax of the .sql files itself?

Thanks for any help.

Answer 1

You can define multiple database install scripts for every database driver like MySQL or MSSQL that you like to support in your installation XML file. Have a look at the example XML here in the official Joomla Docs: https://docs.joomla.org/J3.x:Developing_a_MVC_Component/Using_the_database#helloworld.xml

Your XML file could look like this then:

<install> <!-- Runs on install -->
    <sql>
        <file driver="mysql" charset="utf8">sql/install.mysql.utf8.sql</file>
        <file driver="mssql" charset="utf8">sql/install.mssql.utf8.sql</file>
    </sql>
</install>
<uninstall> <!-- Runs on uninstall -->
    <sql>
        <file driver="mysql" charset="utf8">sql/uninstall.mysql.utf8.sql</file>
        <file driver="mssql" charset="utf8">sql/uninstall.mssql.utf8.sql</file>
    </sql>
</uninstall>

Answer 2

What you can do is have a separate file for each database drive type, for example:

<install>
    <sql>
        <file driver="mysql" charset="utf8">path/to/sql/file</file>
        <file driver="mssql" charset="utf8">path/to/mssql/file</file>
    </sql>
</install>

Simply define the driver type like so:

driver="mysql"

Hope this helps