Advertisement:

Author Topic: Mod Authors: SMF 2.0 Database Functions  (Read 140137 times)

Offline jack

  • Jr. Member
  • **
  • Posts: 382
  • Gender: Male
    • Haxed
Re: Mod Authors: SMF 2.0 Database Functions
« Reply #20 on: April 26, 2008, 05:47:53 PM »
Inconsistency is always bad. You have a large number of functions that behave one way, and a few related ones (admittedly rarely used) that behave completely differently for no apparent reason.

I've had to use it in a mod install/upgrade script that renames database column names from uppercase to lowercase.

It doesn't say 'must' anywhere in the documentation, it says 'If you need to specify', it doesn't say that you actually need to specify it.

Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,287
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: Mod Authors: SMF 2.0 Database Functions
« Reply #21 on: April 28, 2008, 11:46:15 AM »
Well you don't need to specific the prefix.
You can use it without the database prefix as well. Thats what it is intended for.

If you want to question the consistency of the functions, you should do this in the feedback board where the developers are more likely to see it.
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline jack

  • Jr. Member
  • **
  • Posts: 382
  • Gender: Male
    • Haxed
Re: Mod Authors: SMF 2.0 Database Functions
« Reply #22 on: April 28, 2008, 12:51:33 PM »
Well you don't need to specific the prefix.
You can use it without the database prefix as well. Thats what it is intended for.

You can with the others as well, but in a different manner.

Quote
If you want to question the consistency of the functions, you should do this in the feedback board where the developers are more likely to see it.

Aye, you're right there. Actually, I'll be better off raising a bug ticket (hell, if typos in variable names are bugs, this is).
« Last Edit: April 28, 2008, 12:56:30 PM by jack »

Offline akalinder

  • Newbie
  • *
  • Posts: 4
Re: Mod Authors: SMF 2.0 Database Functions
« Reply #23 on: June 27, 2008, 04:41:18 PM »
hi

i want help when i was going in my profile or any other members's profile then profile shows this error

Unknown column 'com.time' in 'field list'
File: /home/vol1/byethost10.com/b10_2027440/merapind.isgreat.org/htdocs/Sources/Profile.php
Line: 1344

what should it means and what shoul i do? plsss hel me
hxxp:merapind.isgreat.org/index.php [nonactive]

Offline metallica48423

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,842
  • Gender: Male
  • Professional Multislacker!
    • Zentendo
Re: Mod Authors: SMF 2.0 Database Functions
« Reply #24 on: June 27, 2008, 04:55:32 PM »
please start a topic in the support boards.
Justin O'Leary
Ex-Project Manager
Ex-Lead Support Specialist

Quote
Microsoft wants us to "Imagine life without walls"...
I say, "If there are no walls, who needs Windows?"

Useful Links:
Online Manual!
How to Help us Help you   
Search
Settings Repair Tool
     

Offline Jim Yarbro

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 1,592
  • a.k.a. Cheschire
    • Warpstorm
Re: Mod Authors: SMF 2.0 Database Functions
« Reply #25 on: July 06, 2008, 01:12:26 AM »
$smcFunc['db_create_table'] needs some explanation on the syntax for indexes.

[edited]

Here's what I figured out, the basic syntax for the index array is:

Code: [Select]
// because indexes is an array
array(
   // a new array for each index
   array(
      // makes this a primary key (with no name, obviously)
      'type' => 'primary',
      // all the columns that will be primary keys
      'columns' => array (
         'mycol1',
         'mycol2'
      )
   )
   // a normal index
   array(
      //name of index
      'name' => 'my_unique_index',
      // type can be "unique" or "key" (defaults to "key")
      'type' => 'unique',
      // all the columns that will be unique keys
       'columns' => array (
          'mycol1',
          'mycol2'
       )     
   ),
   // a bare minimum index
   array(
      'name' => 'my_index',
      'columns' => array (
           'mycol1',
           'mycol2'
        )     
   )
 )
« Last Edit: July 06, 2008, 01:23:38 AM by Cheschire »
Just because SMF isn't as bloated as you want it from the start doesn't mean it can't be. -[Unknown]

^ This means keep Simple Machines SIMPLE, and let people make ****** as complex as they want on their own. Stop making ****** so complex in the core!!

Offline Jim Yarbro

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 1,592
  • a.k.a. Cheschire
    • Warpstorm
Re: Mod Authors: SMF 2.0 Database Functions
« Reply #26 on: July 06, 2008, 01:31:34 AM »
The below functions only exist when using db_extend('packages');. By Default this is automatically called in the Package Manager.

Also note that this is not entirely true. I had to specify db_extend('packages') in my install.php file when I called it using the "<code>" tag in package-info.xml.

However, when I changed the <code> tag to <database>, it automatically included db_extend('packages')
Just because SMF isn't as bloated as you want it from the start doesn't mean it can't be. -[Unknown]

^ This means keep Simple Machines SIMPLE, and let people make ****** as complex as they want on their own. Stop making ****** so complex in the core!!

Offline Jim Yarbro

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 1,592
  • a.k.a. Cheschire
    • Warpstorm
Re: Mod Authors: SMF 2.0 Database Functions
« Reply #27 on: July 06, 2008, 01:36:12 AM »
I apologize for the multi-post, however these are very separate points that need to be addressed.

It should also be noted that db_drop_table is NOT NECESSARY in the uninstall script of your mod, and should, in fact, be frowned upon. By using drop_table, you are basically forcing the admin to lose data. What is not explained very well is the fact that when create_table is used in the install script, it will add the new table to a database log, and then offer the option to remove the table AUTOMATICALLY when the package is uninstalled.

So don't use drop_table in a package unless you really are sure that you want to do it. Just let the admin check the little box when they go to remove their mod.
Just because SMF isn't as bloated as you want it from the start doesn't mean it can't be. -[Unknown]

^ This means keep Simple Machines SIMPLE, and let people make ****** as complex as they want on their own. Stop making ****** so complex in the core!!

Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,287
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: Mod Authors: SMF 2.0 Database Functions
« Reply #28 on: July 06, 2008, 03:27:32 PM »
How does this look for the index info:

Quote
$smcFunc['db_create_table'] (table_name, columns, indexes, parameters, if_exists, error) - with db_extend('packages');
  • This function allows for creating a table. You can not create a SMF default table.
  • Table_name can have a database prefix. If you specific a database prefix, add 'no_prefix' to the paramaters.
  • columns is a multi-dimensional array containing the columns to be in the table. This is passed to $smcFunc['db_add_column function'] for handling.
  • The indexes is a multi-dimensional array containing the indexes for the table. The basic syntax is: array('name' => 'index_name', 'type' = > 'primary', 'columns => array('column_name', 'another_column'))
  • if_exists is by default using "update". Other options are 'overwrite', 'ignore', 'error', 'update_remove',
  • Parameters contains special items such in an array such as 'no_prefix' to not auto add a the database prefix.
  • if_exists controls what to do if the column exists, by default it updates the column

Yes you only need to specify the db_extend('packages') if you are using it outside of the Packages <database> tag. It should still be in your install file though for manual installations.

I believe its in the coding guidelines to not drop tables and such.. I have to many windows open to try and pull it up, but its in the sticky above/below this one :P
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline X3mE

  • Full Member
  • ***
  • Posts: 476
  • Gender: Male
  • The more I C, the less I see.
    • Lolmao.info - The site that makes you laugh!
Re: Mod Authors: SMF 2.0 Database Functions
« Reply #29 on: November 20, 2008, 06:54:16 PM »
    Quote
    $smcFunc['db_add_column'] (table_name, column_into, parameters, if_exists, error) - with db_extend('packages');

        * This function allows for adding a column to a table.
        * Table name should be an already existing table. If you specific a database prefix, add 'no_prefix' to the paramaters.

    Column Info should be an array of data containing with keys

        * 'name' of the column
        * 'type' of the column
        * 'size' of the column if required by type.
        * 'null', wether to use "null" or "not null"
        * 'default' should contain the default value for the column
        * 'auto' tells wether the column uses auto_increment or not.

    • Parameters contains special items such in an array such as 'no_prefix' to not auto add a the database prefix.
    • if_exists controls what to do if the column exists, by default it updates the column
    [/list]

    I am not quite sure I understand how to use this funcion...

    This is how I currently add a new column to the database:

    Code: [Select]
    global $smcFunc, $db_prefix;

    //check if columns exist
    $request = $smcFunc['db_query']('', "
    SHOW COLUMNS FROM {$db_prefix}messages
    ",
    array()
    );

    $found = 0; //initialize search
    while($c = mysql_fetch_assoc($request)){
    if($c['Field'] == 'ua_os'){
    $found = 1; //we have a winner
    break;
    }
    }

    if ($found == 0) { //insert column
    $smcFunc['db_query']('', "
    ALTER TABLE {$db_prefix}messages
    ADD COLUMN ua_os TINYTEXT NOT NULL
    ",
    array()
    );
    }

    Now, I'm not quite sure how to convert this code to use the db_add_column function... This is what I think it should look like:

    Quote
    global $smcFunc;

    $smcFunc['db_add_column']("messages",
                    array(
                         'name' => 'ua_os', 'type' => 'tinytext', 'size' => '', 'null' => 'not null', 'default' => '', 'auto' => '',
                    ),
                    array(),
                    if_exists???,
                    ''
          );

    I have no idea what to put in the if_exists parameter, I want to just skip the whole thing if the column is already there...


    Oh, and what exactly does "with db_extend('packages')" mean?
    Kids, you tried your best and you failed miserably. The lesson is - never try.

    My mods:
    OS & Browser Detection (1.5 is out!) | Skype Profile Field | GTalk Profile Field | AllCaps Blocker | SMF Syntax Highlighter (Beta) + 2 in development!

    Personal websites:
    Mobilize.rs (and forum) | Lolmao.info



    Offline Fustrate

    • SMF Friend
    • SMF Hero
    • *
    • Posts: 6,474
    • Gender: Male
    • Controller of the rum budget
      • Fustrate on GitHub
      • @Fustrate on Twitter
      • Fustrate
    Re: Mod Authors: SMF 2.0 Database Functions
    « Reply #30 on: November 20, 2008, 10:38:30 PM »
    if_exists defaults to 'update'. It'll just use db_change_column for you, and overwrite the old column. Use any other value, and if it already exists, it will return false.

    db_extend() adds extra $smcFunc['db_...'] functions. Default parameter is 'extra'.

    db_extend('packages');
       'db_add_column'
       'db_add_index'
       'db_calculate_type'
       'db_change_column'
       'db_create_table'
       'db_drop_table'
       'db_table_structure'
       'db_list_columns'
       'db_list_indexes'
       'db_remove_column'
       'db_remove_index'

    db_extend('search');
       'db_search_query'
       'db_search_support'
       'db_create_word_search'
       'db_support_ignore'

    db_extend();
       'db_backup_table'
       'db_optimize_table'
       'db_insert_sql'
       'db_table_sql'
       'db_list_tables'
       'db_get_version'
    Steven Hoffman
    Former Team Member, 2009-2012

    Offline X3mE

    • Full Member
    • ***
    • Posts: 476
    • Gender: Male
    • The more I C, the less I see.
      • Lolmao.info - The site that makes you laugh!
    Re: Mod Authors: SMF 2.0 Database Functions
    « Reply #31 on: November 20, 2008, 10:43:16 PM »
    Thank you for your reply, I will try that.

    db_extend() adds extra $smcFunc['db_...'] functions. Default parameter is 'extra'.

    So basically I should call db_extend('packages'); before $smcFunc['db_add_column'] in my code?
    Kids, you tried your best and you failed miserably. The lesson is - never try.

    My mods:
    OS & Browser Detection (1.5 is out!) | Skype Profile Field | GTalk Profile Field | AllCaps Blocker | SMF Syntax Highlighter (Beta) + 2 in development!

    Personal websites:
    Mobilize.rs (and forum) | Lolmao.info



    Offline Fustrate

    • SMF Friend
    • SMF Hero
    • *
    • Posts: 6,474
    • Gender: Male
    • Controller of the rum budget
      • Fustrate on GitHub
      • @Fustrate on Twitter
      • Fustrate
    Re: Mod Authors: SMF 2.0 Database Functions
    « Reply #32 on: November 20, 2008, 10:52:28 PM »
    Yep :)
    Steven Hoffman
    Former Team Member, 2009-2012

    Offline X3mE

    • Full Member
    • ***
    • Posts: 476
    • Gender: Male
    • The more I C, the less I see.
      • Lolmao.info - The site that makes you laugh!
    Re: Mod Authors: SMF 2.0 Database Functions
    « Reply #33 on: November 20, 2008, 11:12:20 PM »
    Thanks again :)
    Kids, you tried your best and you failed miserably. The lesson is - never try.

    My mods:
    OS & Browser Detection (1.5 is out!) | Skype Profile Field | GTalk Profile Field | AllCaps Blocker | SMF Syntax Highlighter (Beta) + 2 in development!

    Personal websites:
    Mobilize.rs (and forum) | Lolmao.info



    Offline X3mE

    • Full Member
    • ***
    • Posts: 476
    • Gender: Male
    • The more I C, the less I see.
      • Lolmao.info - The site that makes you laugh!
    Re: Mod Authors: SMF 2.0 Database Functions
    « Reply #34 on: November 25, 2008, 10:23:41 AM »
    Okay, I've tried the code mentioned above, and it doesn't work. Here it is again:

    Quote
    if (!defined('SMF'))
       die('Hacking attempt...');

    global $smcFunc;

    db_extend('packages');

    $smcFunc['db_add_column']("messages",
                    array(
                         'name' => 'column_name', 'type' => 'tinytext', 'size' => '', 'null' => 'not null', 'default' => '', 'auto' => '',
                    ),
                    array(),
                    'do_nothing',
                    ''
          );

    It doesn't report any errors, but also adds nothing to the table, so I guess some of these parameters are wrong. Any ideas?
    Kids, you tried your best and you failed miserably. The lesson is - never try.

    My mods:
    OS & Browser Detection (1.5 is out!) | Skype Profile Field | GTalk Profile Field | AllCaps Blocker | SMF Syntax Highlighter (Beta) + 2 in development!

    Personal websites:
    Mobilize.rs (and forum) | Lolmao.info



    Offline [SiNaN]

    • SMF Super Hero
    • *******
    • Posts: 11,526
    • Young and Foolish
      • SimplePortal
    Re: Mod Authors: SMF 2.0 Database Functions
    « Reply #35 on: November 25, 2008, 10:56:01 AM »
    Can you try it with removing 'size' and 'default' indexes?
    Former SMF Core Developer | My Mods | SimplePortal

    Offline X3mE

    • Full Member
    • ***
    • Posts: 476
    • Gender: Male
    • The more I C, the less I see.
      • Lolmao.info - The site that makes you laugh!
    Re: Mod Authors: SMF 2.0 Database Functions
    « Reply #36 on: November 25, 2008, 11:44:51 AM »
    I've removed size, auto and default (which were all blank), and now it works. Thank you ;)
    Kids, you tried your best and you failed miserably. The lesson is - never try.

    My mods:
    OS & Browser Detection (1.5 is out!) | Skype Profile Field | GTalk Profile Field | AllCaps Blocker | SMF Syntax Highlighter (Beta) + 2 in development!

    Personal websites:
    Mobilize.rs (and forum) | Lolmao.info



    Offline M-DVD

    • SMF Hero
    • ******
    • Posts: 1,650
    • Gender: Male
    • Step by step will update the mods to SMF 2RC2
    Re: Mod Authors: SMF 2.0 Database Functions
    « Reply #37 on: November 26, 2008, 10:49:28 PM »
    Hi.

    Code: [Select]
       db_query("INSERT INTO
    {$db_prefix}table
    (col1, col2)
    VALUES (data1,data2)
    , __FILE__, __LINE__
    );

    Now is equivalent to:

    Code: [Select]
            $smcFunc['db_insert']('insert',
                '{db_prefix}table',
                array( 'col1' => 'type1', 'col2' => 'type2' ),
                array( data1, data2 )
            );


    Ok, But?

    Code: [Select]
       db_query("INSERT INTO
    {$db_prefix}table
    (col1, col2)
    VALUES (data1a,data2a), (data1b,data2b), (data1c,data2c), (data1d,data2d)
    , __FILE__, __LINE__
    );

    Which should do? Several queries? ($smcFunc['db_insert']...)  :-\

    Thanks.

    « Last Edit: November 26, 2008, 10:51:10 PM by M-DVD »

    Offline niko

    • SMF Friend
    • SMF Hero
    • *
    • Posts: 2,003
    • Gender: Male
      • Madjoki
    Re: Mod Authors: SMF 2.0 Database Functions
    « Reply #38 on: November 27, 2008, 05:06:00 AM »
    Hi.

    Code: [Select]
       db_query("INSERT INTO
    {$db_prefix}table
    (col1, col2)
    VALUES (data1,data2)
    , __FILE__, __LINE__
    );

    Now is equivalent to:

    Code: [Select]
            $smcFunc['db_insert']('insert',
                '{db_prefix}table',
                array( 'col1' => 'type1', 'col2' => 'type2' ),
                array( data1, data2 )
            );


    Ok, But?

    Code: [Select]
       db_query("INSERT INTO
    {$db_prefix}table
    (col1, col2)
    VALUES (data1a,data2a), (data1b,data2b), (data1c,data2c), (data1d,data2d)
    , __FILE__, __LINE__
    );

    Which should do? Several queries? ($smcFunc['db_insert']...)  :-\

    Thanks.

    Code: [Select]
            $smcFunc['db_insert']('insert',
                '{db_prefix}table',
                array( 'col1' => 'type1', 'col2' => 'type2' ),
                array(
                       array(data1, data2),
                       array(data3, data4),
                )
            );

    Offline M-DVD

    • SMF Hero
    • ******
    • Posts: 1,650
    • Gender: Male
    • Step by step will update the mods to SMF 2RC2
    Re: Mod Authors: SMF 2.0 Database Functions
    « Reply #39 on: November 27, 2008, 08:51:41 PM »
    Code: [Select]
            $smcFunc['db_insert']('insert',
                '{db_prefix}table',
                array( 'col1' => 'type1', 'col2' => 'type2' ),
                array(
                       array(data1, data2),
                       array(data3, data4),
                )
            );

     :o Thanks, it works