News:

Wondering if this will always be free?  See why free is better.

Main Menu

[WIP] best way to add column to existing table?

Started by Deprecated, September 28, 2015, 07:08:24 PM

Previous topic - Next topic

Deprecated

I'm currently writing a mod that requires a new column in the smf_messages table. I'm intending to use SSI.php to execute the code that adds the column.

I have the rest of the code worked out in Post-Subs.php createPost() and modifyPost() and can of course manually add the column, but I know there is an easy way to do it via SSI.php.

I wonder if anybody could be kind enough to either gimme the code ;) or point me to an existing mod that does that and I'll just rip off emulate the nice example using my own needed changes.

Thanks!

margarett

If you just need to create the column (and then use it throughout the code) just use $smcFunc with the add column option at the install code (called from package-info.xml in database tags)
http://wiki.simplemachines.org/smf/Db_add_column
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Deprecated

Thanks! That was exactly what I needed.

Of course adding the column with plain PHP code is child's play, but I wanted to do it using SMF's interface code.

I wasn't aware of the documentation and seem to have a problem navigating through SMF's documentation to get the answers I need.

Thanks again!

Deprecated

It wasn't quite that simple, didn't work until I added a few more frills. Not sure if they are all required but didn't work with the basic call:

global $smcFunc;

if(!array_key_exists('db_add_column', $smcFunc))
   db_extend('packages');

$smcFunc['db_add_column'](
   '{db_prefix}messages',
   array(
      'name' => 'my_new_column',
      'type' => 'varchar',
      'size' => 16,
      'null' => false
   )
);


The problem is when I uninstall the mod, then reinstall, all the previous data stored in that column is gone. I don't have any uninstall routine, so I presume calling the above db_add_column a second time must wipe out any data.

I will appreciate any advice on the best way to handle this. I suppose I can check for the column existing and not add it if it's already there, but there must be a better way. Is there?

margarett

If you do that and call the PHP file from "database" tags in package-info.xml, SMF will record the operation and offer the user the possibility to remove that from the database (or not :P )

If you want to be on the safe side, you'll have to check if it exists alread before creating  it ;)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Chen Zhen


$smcFunc['db_add_column'](
   '{db_prefix}messages',
   array(
      'name' => 'my_new_column',
      'type' => 'varchar',
      'size' => 16,
      'null' => false
   ),
  array(),
  'ignore',
  'fatal'
);

My SMF Mods & Plug-Ins

WebDev

"Either you repeat the same conventional doctrines everybody is saying, or else you say something true, and it will sound like it's from Neptune." - Noam Chomsky

Deprecated

Quote from: margarett on September 29, 2015, 08:37:11 PM
If you do that and call the PHP file from "database" tags in package-info.xml, SMF will record the operation and offer the user the possibility to remove that from the database (or not :P )

If you want to be on the safe side, you'll have to check if it exists alread before creating  it ;)

I don't even know what you mean by "calling the PHP file from database tags in package-info.xml." Maybe I've been out of writing mods too long. I'm calling an add_settings.php file from my package-info.xml to do the dirty work. Yes I was considering an "if exists" abort situation.

And thanks Chen Zhen -- somehow I just have the feeling your code will solve the problem. It just looks right from what I remember reading the reference cited in reply #2.

Jeez there was a time when I was high on the top 10 list. Maybe I shouldn't have taken a 2 year vacation, eh? :D

Just for the record, I want my mod to be bullet-proof and just leave old data alone. Uninstall, reinstall, I don't want to lose the data. I don't even want any option to lose, erase or delete the data. Anybody with a brain can drop or truncate the table via phpMyAdmin. It's not that kind of data that you'd want to lose unless you never ever want to use my mod again.

margarett

:P

How are you calling "add_settings.php" to be executed? Inside "code" or "database" tags? Use the latter ;)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Chen Zhen

Deprecated,

Quote from: DeprecatedJust for the record, I want my mod to be bullet-proof and just leave old data alone. Uninstall, reinstall, I don't want to lose the data. I don't even want any option to lose, erase or delete the data. Anybody with a brain can drop or truncate the table via phpMyAdmin. It's not that kind of data that you'd want to lose unless you never ever want to use my mod again.

For what you describe then you should use the code tag to execute your php file for the install.
What I previously posted will work for you as it ignores the column creation if it exists.
The wiki instruction should elaborate on the if_exists and error reference with specific accepted values and an example.
Perhaps I will update it when I have some spare time.

Regards.

My SMF Mods & Plug-Ins

WebDev

"Either you repeat the same conventional doctrines everybody is saying, or else you say something true, and it will sound like it's from Neptune." - Noam Chomsky

margarett

Quote from: Deprecated on September 29, 2015, 10:25:54 PM
Just for the record, I want my mod to be bullet-proof and just leave old data alone.
That's not a good policy and it will prevent it from being approved, should you submit it to our modsite.

You shouldn't (by principle) leave content behind on the database (other than $modSettings variables). If the user chooses to remove your MOD, it's up to him/her to decide whether or not the MOD's data should be kept, which SMF allows easily if you add the database structure changes through "database" tags.
Like Chen Zhen said, if you use "code" tags, SMF will not track these changes and the content will not be removed. But again, this is not a good principle IMO
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Deprecated

Well this is very conflicting information. Do this not that. Do that not this. Although I really do appreciate the advice.

To be honest I haven't made up my mind whether to submit it for official use. Part of the equation is for me to determine how useful this may be for other forum operators. I'm tending towards it would be useful. But militating that is the amount of work necessary beyond getting it working to suit my needs to get it into the area that the Mod Squad would approve it.

In other words I think it's a good mod but it may be more trouble than it's worth to get it approved.

And again we have another militating factor that I'm a bit rusty and might just be persnickety enough to do the extra work and get it approved, just to show myself and others I can still do it.

I used to be about #4 on the top 10 mod authors' list and I must be somewhere in the top 20 now, and it could be fun to be back on the top 10 list. I'm creating a new forum with very unique performance, and there's bound to be several mods there that could be candidates for release to the public.

I figure the best strategy is to get my mods working first, then analyze what it would take to upgrade them into some semblance that might be approved by the Mod Squad.

I think it is likely I will be releasing a few of them just to satisfy myself that I'm paying back to the community what I get from being allowed to use the SMF code to power my forum.

So to sum it up I just don't know what to do. But this one here is bound to be of interest to many forums, it adds some great functionality, and it leverages a free version of a commercial service into being very useful to ordinary people who can't afford pro versions of some services.

It's sort of like my IP2Location mod (see the list in my signature) where it enhances SMF's use of the free version of a commercial service.

I'm going to have to come back and read this topic again. As I said I'm conflicted. But the best possible solution would be to have a user choice pop up during removal asking if you want to preserve the mod settings (and extra table column in the database) or delete them.

I'm sorry if I seem so mysterious about what the mod actually does, but if it gets released I want it released under my name, not under somebody who ran with my idea and developed it quicker than me. But it's #1 on my list right now, so this mod is one of the most likely of my new dozen mods to be released to the public.

Advertisement: