Mod Authors: SMF 2.0 Database Functions

[Joshua Dickerson]

Not sure if they are, so I just did it myself. I can put them some place better.

int - an integer. 0 included.
string/text - a string
array_int - an array of integers. Gets checked to make sure every element is an integer and can't be empty. Gets imploded to be a comma delimited list like "1, 2, 3, 4, 5, 6".
array_string - an array of strings. Gets escaped. Can't be empty. Gets imploded to be a comma delimited list like "a, b, c, d, e, f, g".
date - a date in the format of YYYY-MM-DD. If it isn't in that format, it throws an error.
float - a number.
identifier - removes backticks and periods and returns a string in the format of `string`
raw - nothing gets changed.

It defaults to an error. So, if you don't pick one of these, you're going to get errors.

[OutofOrder]

$smcFunc['db_affected_rows'] (connection)

• Will return exact same results as mysql_num_fields.

Uhh If you excuse the report:
I'm afraid that's a mistake in the first post. Hope you can fix it (it should say "...same exact results as mysql_affected_rows").

[Mick.]

thank you.  i was so looking for this to convert mods 1.1.x to rc2.

[Arantor]

BlueDevil: See the giant sticky in this board entitled "Important Customization Information"

[alevsu]

burda türk biri varmı

[grafitus]

burda türk biri varmı

alevsu, please use the Turkish board.

[butchs]

Need a little advice with adding indexes to new tables.

I am trying to convert this mysql:

Code Select Expand

$name = db_name($name);
return "CREATE TABLE IF NOT EXISTS {db_prefix}`$name` (
`id` INT(11) NOT NULL auto_increment,
`ip` TEXT NOT NULL,
`user_agent` TEXT NOT NULL,
`request_entity` TEXT NOT NULL,
`key` TEXT NOT NULL,
INDEX (`ip`(15)),
INDEX (`user_agent`(10)),
PRIMARY KEY (`id`) );"; // TODO: INDEX might need tuning

To SMF 2.0 RC2 format.  So far I have:

Code Select Expand

$smcFunc['db_create_table'](
'{db_prefix}' . $name,
array(
array('name' => 'id', 'type' => 'INT(11)', 'null' => 'NOT NULL', 'auto' => 'auto_increment'),
array('name' => 'ip', 'type' => 'TEXT', 'null' => 'NOT NULL'),
array('name' => 'user_agent', 'type' => 'TEXT', 'null' => 'NOT NULL'),
array('name' => 'request_entity', 'type' => 'TEXT', 'null' => 'NOT NULL'),
array('name' => 'key', 'type' => 'TEXT', 'null' => 'NOT NULL'),
),
array(
array(
'type' => 'primary',
'columns' => array('id')
)
),
'ignore'
);
$created_tables[] = '{db_prefix}' . $name;

I am not sure if the variable $name is correct and how to add

Code Select Expand


INDEX (`ip`(15)),
INDEX (`user_agent`(10)),.

I do not see a way to enter the name and size of the index for 'db_create_table'?

[Arantor]

I'd say your fundamental datastructure is hugely messed up if you require 64K characters on a string of 16 characters, for one. I'd strongly encourage you to make the IP column varchar(16) with an index of 7 characters, and user agent varchar 255, index(10)

Seriously, though you already have one index - the primary one. It's just another array in there.

[butchs]

The data structure is in a program I am looking at bridging with SMF.  I really have no intension getting into the other programs code since it is rather extensive.  All I really need to do is set up access to SMF database in a  DB access php file that will replace the normal mysql DB access file.   I can change the structure some.  This is the only function slowing me down.  Here is what I am looking at after you post (this is complete - the above post was not complete):

Code Select Expand


$name = $db_prefix .'module_name'

$request = $smcFunc['db_create_table']($name,
array(
array('name' => 'id', 'type' => 'INT(11)', 'null' => 'NOT NULL', 'auto' => 'auto_increment'),
array('name' => 'ip', 'type' => 'varchar(16)', 'null' => 'NOT NULL'),
array('name' => 'date', 'type' => 'DATETIME', 'null' => 'NOT NULL', 'default' => '0000-00-00 00:00:00'),
array('name' => 'request_method', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
array('name' => 'request_uri', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
array('name' => 'server_protocol', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
array('name' => 'http_headers', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
array('name' => 'user_agent', 'type' => 'varchar 255', 'null' => 'NOT NULL'),
array('name' => 'request_entity', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
array('name' => 'key', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
),
array(
array(
'type' => 'primary',
'columns' => array('id')
)
array(
'type' => 'indexes',
'ip' => array(array('ip', 7)),
'user_agent' => array(array('user_agent', 10))
)
),
'ignore'
);

It is my best guess at this moment.  I am not an expert with no formal training on this stuff so please let me know if you think it will work or what changes I need to make to work with SMF 2?

[Arantor]

You'd be best looking at some other examples, even in the OP, since there's a whole lot of stuff missing. Let me just completely rewrite it for you. Without wishing to be rude, when the example for an index states it has a 'type' and a 'columns', it is a fair bet to say that any others you make will likely have the same...

Code Select Expand

$name = $db_prefix .'module_name'

$request = $smcFunc['db_create_table']($name,
array(
array(
'name' => 'id',
'type' => 'int',
'size' => 10,
'null' => false,
'unsigned' => true,
'auto' => true
),
array(
'name' => 'ip',
'type' => 'varchar',
'size' => 16,
'null' => false,
'default' => '', // and yes before anyone whines, this won't work in non MySQL, but that's an SMF bug
),
array(
'name' => 'date',
'type' => 'datetime',
'null' => false,
'default' => '0000-00-00 00:00:00', // without going into the belly of Subs-Db-Mysql.php I can't remember if SMF supports this type, assume it does for now
),
array(
'name' => 'request_method',
'type' => 'varchar',
'size' => 4, // since you're only ever handling GET or POST, save yourself the effort
'null' => false,
'default' => '',
),
array(
'name' => 'request_uri',
'type' => 'varchar',
'size' => 255,
'null' => false,
'default' => '',
),
array(
'name' => 'server_protocol',
'type' => 'varchar',
'size' => 15, // 5 is probably enough here, for http and https but you never know
'null' => false,
'default' => '',
),
array(
'name' => 'http_headers',
'type' => 'varchar',
'size' => 255, // depending on what you're doing, this may need to be text
'null' => false,
'default' => '',
),
array(
'name' => 'user_agent',
'type' => 'varchar',
'size' => 255,
'null' => false,
'default' => '',
),
array(
'name' => 'request_entity',
'type' => 'varchar',
'size' => 255,
'null' => false,
'default' => '',
),
array(
'name' => 'key',
'type' => 'varchar',
'size' => 255,
'null' => false,
'default' => '',
),
),
array(
array(
'type' => 'primary',
'columns' => array('id')
)
array(
'type' => 'index',
'columns' => array('ip'),
'size' => 7,
),
array(
'type' => 'index',
'columns' => array('user_agent'),
'size' => 10,
)
),
'ignore'
);

I haven't actually specified a size on indexes during installation in a mod before, simply because I've never indexed anything that's actually text before in this manner, usually because it's never very pretty. Better solution would be to do some kind of processing in your application and convert it to an integer for which indexing is a LOT faster to save, to load, to reindex and to lookup/search against later.

[butchs]

WOW, I was off base.  Thank you.

[Arwym]

In $smcFunc['db_insert']:

Columns: An array ( column_name => input_type) set that holds all column names that will be changed and their expected input type.

How would I specify an input type of boolean?  Is there any reference for input types I can look at?

[Akyhne]

Hmm, why can't I run this script in a stand-alone file in the same folder as SSI.php?

Code Select Expand


<?php

if (!defined('SMF') && file_exists(dirname(__FILE__) . '/SSI.php'))
{
require_once(dirname(__FILE__) . '/SSI.php');
}
elseif (!defined('SMF'))
die('Error!');

global $smcFunc, $db_prefix;

echo 'Installing into '. $db_prefix;

if($smcFunc)
$smcFunc['db_create_table'](
$db_prefix . 'collection',
array(
array('name' => 'item', 'type' => 'INT', 'size' => 11, 'default' => ''),
array('name' => 'name', 'type' => 'VARCHAR', 'size' => 60, 'default' => ''),
array('name' => 'pop_name', 'type' => 'VARCHAR', 'size' => 30, 'default' => ''),
array('name' => 'category', 'type' => 'INT', 'size' => 11, 'default' => ''),
array('name' => 'thumb', 'type' => 'VARCHAR', 'size' => 60, 'default' => ''),
array('name' => 'official_site', 'type' => 'TEXT', 'default' => ''),
array('name' => 'other_site', 'type' => 'TEXT', 'default' => ''),
),
'ignore'
);
?>


I keep getting the error: Fatal error: Function name must be a string in xxx.

[Oya]

u need db_extend('packages'); just like all the mods do

[Akyhne]

But I'm not installing a package.

[Oya]

yes but the db_create_table function isn't available unless you call the packages extension

funnily enough like the first post says

Database Package functions were also introduced in SMF 2.0. These special functions allow customization creators to easily modify a database that will support multiple database types. The below functions only exist when using db_extend('packages');. By Default this is automatically called in the Package Manager.
$smcFunc['db_add_column']
$smcFunc['db_add_index']
$smcFunc['db_calculate_type']
$smcFunc['db_change_column']
$smcFunc['db_create_table']
$smcFunc['db_drop_table']
$smcFunc['db_table_structure']
$smcFunc['db_list_columns']
$smcFunc['db_list_indexes']
$smcFunc['db_remove_column']
$smcFunc['db_remove_index']

[Akyhne]

Hmm, thx. My ignore doesn't seem to work though.

Code Select Expand


<?php
$smcFunc['db_create_table'](
$db_prefix . 'collection',
array(
array('name' => 'item', 'type' => 'INT', 'size' => 11, 'default' => 0, 'auto' => 'true'),
array('name' => 'name', 'type' => 'VARCHAR', 'size' => 60, 'default' => ''),
array('name' => 'pop_name', 'type' => 'VARCHAR', 'size' => 30, 'default' => ''),
array('name' => 'category', 'type' => 'INT', 'size' => 11, 'default' => 0),
array('name' => 'thumb', 'type' => 'VARCHAR', 'size' => 60, 'default' => ''),
array('name' => 'official_site', 'type' => 'TEXT', 'default' => ''),
array('name' => 'other_site', 'type' => 'TEXT', 'default' => ''),
),
array(
array(
'type' => 'primary',
'columns' => array('item')
)
),
'ignore'
);
?>
It's probably placed wrong, but I compared with other $smcFunc['db_create_table']  examples, and they look the same.

[Oya]

it doesnt matter, please read the posted quote again

until you run db_extend('packages'), $smcFunc['db_create_table'] is NOT DECLARED. so $smcFunc['db_create_table'] doesn't exist so you get the error you did.

[Akyhne]

I do, just didn't put it in the example.

[Oya]

so what is it doing versus what you think it's supposed to be doing?