XF 2.0 Using schemaManager over raw queries in setup

[S Thomas]

Is there a reason why we would use the schemaManager over raw queries in install and simple upgrade steps (read: no input data, just default data and/or structure changes)? Imho it's way too inconvenient to use the schemaManager, especially because any db manager prints out raw queries ready for copy-pasting.

 

[Lukas W.]

Queries can be written in a lot of ways. The schema manager eleminates a lot of these variables and streamlines and regulates the process. It does error handling a lot better than raw queries and can even mitigate some failure points.

In my personal opinion, you don't need to do manual database layout and then try to map that somehow to the existing schema manager. You want to start with the entity, then build your schema manager according to that. No need to touch your database manager anywhere in that process, let the system handle the database for you. It's well equipped for that task. The problem you're having - the inconvenience you called it - is that you're trying to do so much that the system does for you, then you have to trace your steps back and redo it in the way the system dictates.

 

[S Thomas]

Queries can be written in a lot of ways. The schema manager eleminates a lot of these variables and streamlines and regulates the process. It does error handling a lot better than raw queries and can even mitigate some failure points.

Yea, agreed on that one in general. But I'm specifically talking about simple queries, like creatings tables and columns (hence did not mention the uninstallSteps).
My question was more regarding compatibility with other addons and stuff like that. Like an actual reason why we would use the schmemaManager. I mean, I will rely on the query my db manager prints out, because, well, that's the query itself has used, hence already run once at least. Should be as safe as XF's query.

In my personal opinion, you don't need to do manual database layout and then try to map that somehow to the existing schema manager. [...] The problem you're having - the inconvenience you called it - is that you're trying to do so much that the system does for you, then you have to trace your steps back and redo it in the way the system dictates.

I think this is pretty much the point where we have different standings. For me it's more work when I have to use the schmemaManager - my db manager has a convenient gui and prints out the SQL anyways.
I do start with the entity structure, or with the layout, which makes no difference to me at all. Both are manually designing the table and both need to be done. I would rather start with the layout itself when there are multiple entities involved, but then again personal preference.
The only difference is, that clicking around is way less work then manually copy-pasting the same layout twice (entity + setup) and then manually calling the step.

 

[Lukas W.]

Yea, agreed on that one in general. But I'm specifically talking about simple queries, like creatings tables and columns (hence did not mention the uninstallSteps).

Creating a table is far more complex query than dropping one in my opinion.

My question was more regarding compatibility with other addons and stuff like that. Like an actual reason why we would use the schmemaManager. I mean, I will rely on the query my db manager prints out, because, well, that's the query itself has used, hence already run once at least. Should be as safe as XF's query.

Well what compatibility issues would you expect? In the end, both result in a MySQL table. The only moment you'll run into trouble when your query is not able to handle an unexpected situation like the schema manager could, such as a column or a table already existing. If you don't pay attention, you may also end up with the wrong charset for your database text column. It's all these things you always have to keep in mind, like use "IF EXISTS" where applicable, set integers to unsigned, etc., etc.

As a more exotic consideration, the schema manager would theoretically be compatible with different database types. Out of the box XenForo only offers support for MySQL, and I doubt that with the direct SQL queries used in a few places, this is really changeable that easily, but purely in theory, you could create a different database adapter to make XF compatible with a different database type, such as MongoDB. The more raw SQL queries are introduced, the nastier this task would get. So if XF or a 3rd party was to ever write a database adapter for a different database, you'd may have to come back and redo all your installers anyway.

 

[DragonByte Tech]

any db manager prints out raw queries ready for copy-pasting.

Why would you ever need to do that?

php cmd.php xf-addon:install-step addonId stepId
php cmd.php xf-addon:upgrade-step addonId versionId stepId

Example:

php cmd.php xf-addon:install-step DBTech/eCommerce 1
php cmd.php xf-addon:upgrade-step DBTech/eCommerce 1010054 1

Also, by using raw queries your system is much more prone to errors from partial upgrades. Have you ever tried applying an ALTER TABLE x ADD y if the column already exists? An exception is thrown, so the import can never possibly complete.

If a partial install or upgrade occurs, the schemaManager will be able to tell that the column you're adding already exists, and skips it. For this reason, you want to do as many operations as possible inside the SM, and make sure your operations outside the SM are resilient (e.g. INSERT IGNORE or REPLACE INTO rather than just INSERT INTO).

The Schema Manager is quite simply the best way of ensuring that
a) Your addon installs / resumes installation correctly
b) You can properly test the install / upgrade steps
c) Everything else Lukas said

There is literally not a single valid reason to not use the SM.

Also:

php cmd.php xf-dev:generate-schema-entity Vendor\\Addon:Entity

Example:

php cmd.php xf-dev:generate-schema-entity DBTech\\eCommerce:Product

This creates the SchemaManager code for you, for use in installers.


Fillip

 

[Chris D]

Personally, I tend to design and write the table in PHP using the schema manager, then I either run that upgrade/install step or echo out the resulting query and run that manually.

$this->getSchemaManager()->createTable('my_table', function(Create $table)
{
    $table->addColumn('key', 'int')->autoIncrement();
    \XF::dumpSimple($table->getQueries());
    exit;
});

I actually find it much easier to write it that way and it almost totally eliminates syntax mistakes.

If you have a GUI database tool like Sequel Pro or MySQL Workbench or Heidi SQL then certainly that will always generate a correct query but there's still things to remember that are easily missed that you don't really need to worry about with the schema manager.

It's very easy to forget things like setting an integer column to unsigned. That actually happens automatically with the schema manager which is the most common type of integer field throughout XF. Setting autoIncrement automatically sets it as the primary key

Using the schema manager to create the table will always create the table with the most supported character set and collation (XF1 to XF2 upgrades may not have utf8mb4 unless they've converted their tables). If we ever change the default charset/collation one day then there would be no reason to go back and review your queries to update them.

I also really like how reusable schema manager code is. If you're adding a column to an existing table, you likely need to make that change in two places. The first in your install code so new installs get it, and the second in your upgrade code so existing installs get it. The $table->addColumn() bit you can just copy and paste from the create table closure to your alter table closure.

 

[Jeremy P]

Not sure if it's applicable to your preferred workflow, but you can generate the schema manager code automatically from your entity structure:

php cmd.php xf-dev:generate-schema-entity Vendor\\AddOn:Entity

You still have to copy/paste the generated code into your setup file and run the install step from the CLI, but that's not so bad in my opinion.

 

[Lukas W.]

Why would you ever need to do that?

I think what he meant with that is that he can create/design his table in his database manager, then let the manager generate the create table query for him and paste that into the setup file.

 

[DragonByte Tech]

I think what he meant with that is that he can create/design his table in his database manager, then let the manager generate the create table query for him and paste that into the setup file.

Fair enough, but that's such a roundabout way of doing it when you can design it in the entity and have XF2 do all the work for you creating the installer code that you can then execute to create the table


Fillip

 

[Chris D]

Different workflows work for different people. Personally, I don't particularly like designing the entity first. Either way, the end result should ideally be SchemaManager code.

The question is sort of a moot point, by the way. Please use the SchemaManager where possible as described in the Resource standards:

10. Where possible, the Schema Manager should be used for all schema changes, though using the database adapter to perform these queries is reasonable but not recommended.

 

[S Thomas]

Not sure if it's applicable to your preferred workflow, but you can generate the schema manager code automatically from your entity structure:

php cmd.php xf-dev:generate-schema-entity Vendor\\AddOn:Entity

You still have to copy/paste the generated code into your setup file and run the install step from the CLI, but that's not so bad in my opinion.

Didn't know that! That actually is a great help. Thanks for the hint. I should look through the CLI commands I guess. This would actually be a reason for me to switch to the schemaManager. I really was thinking until now that you guys manually create the setup code

As a more exotic consideration, the schema manager would theoretically be compatible with different database types. [...]

That makes sense.

It's very easy to forget things like setting an integer column to unsigned.

Yep, and just noticed that this is indeed the case for me aswell.

Thanks for your opinion guys, appreciate it.

 

[Xon]

I build the full table schema and use that on both the install and upgrade code paths. Most of the time that is sufficient, and I can then write manual SQL if data migration is needed or weird adaptive table changes.

The key part is that I use a helper addOrChangeColumn which enables the same closure/lamba to be passed to alterTable or createTable

My installer/upgrade code path then looks something like this;

public function installStep1()
{
    $sm = $this->schemaManager();

    foreach ($this->getTables() as $tableName => $callback)
    {
        $sm->createTable($tableName, $callback);
        $sm->alterTable($tableName, $callback);
    }
}

public function upgrade2000000Step1()
{
    $this->installStep1();
}

public function uninstallStep1()
{
    $sm = $this->schemaManager();

    foreach ($this->getTables() as $tableName => $callback)
    {
        $sm->dropTable($tableName);
    }
}

protected function getTables()
{
    $tables = [];

    $tables['xf_sv_unc_cron'] = function ($table) {
        /** @var Create|Alter $table */
        $this->addOrChangeColumn($table, 'user_id', 'int');
        $this->addOrChangeColumn($table, 'date', 'int');
        $this->addOrChangeColumn($table, 'old_custom_title', 'varchar', 50)->setDefault('');
        $this->addOrChangeColumn($table, 'new_custom_title', 'varchar', 50)->setDefault('');
        $table->addPrimaryKey('user_id');
    };

   return $tables;
}

This way all my table definitions are in one spot, and the schemeManager is generally smart enough that it can handle most sane transformations, so I just use the one definition for install vs upgrades, and use that to drive the uninstall to boot.

This is very helpful for XF1 => XF2 upgrades where people remove the legacy stub, but the tables are still there. And saves a ton of special casing table transformations from version to very. Most of the time I just increment the version id on the upgrade function, and everything keeps working.

Renaming/dropping columns and then adding a replacement with the same name is technically valid SQL, but schemaManager has some trouble with that from memory. But the callback is a function so you can do logic around that.

 

[S Thomas]

Not sure if it's applicable to your preferred workflow, but you can generate the schema manager code automatically from your entity structure:

php cmd.php xf-dev:generate-schema-entity Vendor\\AddOn:Entity

You still have to copy/paste the generated code into your setup file and run the install step from the CLI, but that's not so bad in my opinion.

Looks like on Windows + cmd.exe you don't need to escape \.