db_query DROP COLUMN

[0mW]

Hello all,

I try to create a package for SMF (testing and fun).

So I create the package-info.xml with install and uninstall balise for modification.

The objective and the creation and deletion of column in the member table.

I know there exists the function "db_add_column" but I try to do it with db_query.

So I have this to add a column to the table Members (during installation -> db_add_column.php) :

$smcFunc['db_query']('',
   'ALTER TABLE {db_prefix}members ADD foo int NOT NULL'
);

The FOO column is added when installing the package.

Now when deleting the package, how to remove the foo column from the member table ?

I try this (when deleting -> db_remove_column.php) :

$smcFunc['db_query']('',
   'ALTER TABLE {db_prefix}members DROP COLUMN foo'
);

But that does not delete the column ?

I have no error to return.

I tried with a terminal, the SQL code works though ...

How does it work ?

Thanks.
Best Regards.

[Kindred]

Since you know it exsists, why are you not using the defined SMF function?

also, most mods DO NOT delete on uninstall, unless you tell it to -- because uninstall-reinistall is the general path for upgrading a mod and you don't want all of the old data lost every time.

[0mW]

Hello Kindred,

Because as experimentation, I try the different SMF functions.

I do not understand why the code does not work, I tell him how to follow during the uninstallation :

<?xml version="1.0"?>
<!DOCTYPE package-info SYSTEM "http://www.simplemachines.org/xml/package-info">
<package-info xmlns="http://www.simplemachines.org/xml/package-info" xmlns:smf="http://www.simplemachines.org/">
   <id>0mW:FOO</id>
   <name>FOO</name>
   <version>1.0</version>
   <type>modification</type>
   <install for="2.0 - 2.99.99">
      <readme type="file" parsebbc="true">readme.txt</readme>
      <database type="file">db_add_column.php</database>
   </install>
   <uninstall for="2.0 - 2.99.99">
      <readme type="inline">This will uninstall Mod.</readme>
      <database type="file">db_remove_column.php</database>
   </uninstall>
</package-info>

Add works perfectly but not deletion ? why ?

[shawnb61]

When testing & debugging db calls, it can help to add:

Code Select Expand

$db_show_debug = true;

... to your Settings.php file.

You can then see all queries by clicking on a link at the bottom of your screen in SMF.  This will help you see exactly which calls are being made & may help you narrow down your issue.  Works in 2.0 & 2.1. 

And yes, there are predefined functions for db_add_column & db_remove_column.

[0mW]

Hi shawnb61,

Thanks for the information.

db_remove_column does not work either if the column has been added with the db_query function ...

I guess the cause would be the missing checkbox specifying data deletion ?

[shawnb61]

Yes, I think you are correct. Without an explicit OK, it won't execute the DB changes on uninstall.

Code tags would work, too, I think... 

[shawnb61]

The way to let it know you have such db changes on uninstall is to use the proper smf calls specified above upon install. 

I believe it's all automated - if you add the column using db_add_column, you don't even need to code up DB calls to uninstall.  It already knows...


So, your two options are:
- code tags, total control, you code up add & remove logic, but it just executes without a 'remove db changes' prompt (dangerous as Kindred notes above, the user may be doing an upgrade & want to keep the values)
- database tags, you use the SMF db_add_column call; the 'remove db changes' prompt & removal of the column on uninstall are automated for you

[0mW]

Yes you are right, the only call of the db_add_column function is enough, it automatically offers uninstallation.

But it does not seem to be proposed for all functions ...

Example if I use only db_insert to insert a row in a table, it does not propose uninstallation ...

$smcFunc['db_query']('',
   'DELETE FROM {db_prefix}settings WHERE variable = foo'
);

I do not understand this way of acting.

[shawnb61]

I do not understand this way of acting.

It will definitely reverse DDL properly (e.g., new tables & columns), because those are absolutely within the domain of the mod.

I believe it was deemed risky to automate deletion of rows in standard SMF tables.  Plus, not all actions are reversible, e.g., rows could have been updated, not inserted.  It is safest to leave them alone. 

[0mW]

Thanks for your answer.

Ok I understand better, the previous code works with the <code/> balise.

I will try to create a checkbox to validate data deletion.