Mod Authors: SMF 2.0 Database Functions

Started by SleePy, February 21, 2008, 05:57:21 PM

Previous topic - Next topic

jack

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.

SleePy

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 ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

jack

#22
Quote from: SleePy 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.

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

QuoteIf 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).

akalinder

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]

metallica48423

please start a topic in the support boards.
Justin O'Leary
Ex-Project Manager
Ex-Lead Support Specialist

QuoteMicrosoft 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

Cccddd

#25
$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:


// 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'
        )     
   )
)

Cccddd

Quote from: SleePy on February 21, 2008, 05:57:21 PM
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')

Cccddd

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.

SleePy

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 ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

X3mE

    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:

    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



    Fustrate

    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

    X3mE

    Thank you for your reply, I will try that.

    Quote from: YodaOfDarkness on November 20, 2008, 10:38:30 PM
    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



    Fustrate

    Steven Hoffman
    Former Team Member, 2009-2012

    X3mE

    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



    X3mE

    Okay, I've tried the code mentioned above, and it doesn't work. Here it is again:

    Quoteif (!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



    [SiNaN]

    Can you try it with removing 'size' and 'default' indexes?
    Former SMF Core Developer | My Mods | SimplePortal

    X3mE

    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



    M-DVD

    #37
    Hi.


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


    Now is equivalent to:


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


    Ok, But?


       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.


    niko

    Quote from: M-DVD on November 26, 2008, 10:49:28 PM
    Hi.


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


    Now is equivalent to:


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


    Ok, But?


       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.


            $smcFunc['db_insert']('insert',
                '{db_prefix}table',
                array( 'col1' => 'type1', 'col2' => 'type2' ),
                array(
                       array(data1, data2),
                       array(data3, data4),
                )
            );
    Websites: Madjoki || (2 links retracted by team, links out of date and taken over.)
    Mods: SMF Arcade, Related topics, SMF Project Tools, Post History

    WIP Mods: Bittorrent Tracker || SMF Wiki

    M-DVD

    Quote from: Niko on November 27, 2008, 05:06:00 AM

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


    :o Thanks, it works

    Advertisement: