MySQL – Coding Friends

MySQL storage engines

In MySQL, there is a couple of different storage engines that can be used for different tables within a database. There is myiasm, innodb, cvs, memory, merge, BDB (BerkeleyDB) example, federated, archive and blackhole.

The main two are that are used are

The main differences between them both are speed, the myiasm is more designed for speed to access the database tables but does not support the rollback where you are able stop the current set of instructions and rollback the updates/inserts to where it was at a previous time, because of this the programmer is the person to make sure that what they want to do is and will happen, otherwise the programmer will have to sort out the tables that could out of stink. But compared to the innodb, where it supports the rollback function, it does have to take a hit somewhere and that is the speed.

The main database that I use is the myiasm, purely because of the speed of access, and also it appears that most web/applications appear to use that. But if you running some circuital data then of course I would use innodb database type purely for data.

MySQL – triggers

In MySQL version 5 onwards you can use Triggers, triggers are a nice was of checking values within a insert/update/delete process. A trigger happens either just before the insert actually happens into the database, or just after

Lets say that you create a table as below

CREATE TABLE testTable 
(id INT NOT NULL AUTO_INCREMENT, 
  guid VARCHAR(36), 
  name VARCHAR(100), 
PRIMARY KEY (id));

and then if you want to insert a name into the name column you could do

INSERT INTO testTable (name) VALUES ("thename");

but you are not inserting the anything into the guid column because you can force a value into that column with a trigger (the id is already creating a value with the primary key, auto_increment).

To create a trigger you need to set the delimiter to “//” because within sql you need to use the “;” which is the standard delimiter (when the MySQL will try and execute the query)

delimiter //
CREATE TRIGGER testTable_insert 
BEFORE INSERT ON 'testTable' 
FOR EACH ROW 
BEGIN 
    SET NEW.guid = uuid(); 
END;
delimiter ;

“CREATE TRIGGER” is just like creating a table, apart from you are creating a trigger, so the next value is the trigger name, normally it is the table name with what you are doing, e.g. inserting

“BEFORE INSERT ON” means before you actually insert the data into the database (it is in a hold area as such). the next value is the actual table that you are linking this trigger to.

“FOR EACH ROW” means each row of the insert, since you can insert x amount of lines into a table.

the sql code is between the “BEGIN” and “END”, and all is what is being set is the guid column within the “NEW” (this is the new row to be inserted) and setting that value to uuid() which is a mysql function.

so before you would have inserted into a table and the table would have looked like


ID	GUID	Name
1		thename

but once the trigger is in place and you did the same process again of inserting a new name

INSERT INTO testTable (name) VALUES ("a new name");


ID	GUID	Name
1		thename
1	3ace82c-2cf1-11df-b1c3-00a0d1a1240a	a new name

mysql – connection example

Mysql is a database, and to gain access to the data within C++ you will need to be able to “talk” to the database via queries (just like on the mysql command line interface e.g. select * from tablename), the connection process is very similar to the command line interface you will need to supply connection details as in hostname (localhost normally), username, password, database to use and also there are other details that you can pass e.g port number more information can be gained from the MYSQL API pages

To start with I created a struct that will hold the username, host etc details.

struct connection_details
{
    char *server;
    char *user;
    char *password;
    char *database;
};

In essence to connect to a database I have created a function that will connect and return a MYSQL pointer to the new connection using the structure above connection_details.

MYSQL* mysql_connection_setup(struct connection_details mysql_details)
{
     // first of all create a mysql instance and initialize the variables within
    MYSQL *connection = mysql_init(NULL);
 
    // connect to the database with the details attached.
    if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {
      printf("Conection error : %s\n", mysql_error(connection));
      exit(1);
    }
    return connection;
}

That is it, you are connected now, now you can perform some sql queries, once again I have created a function to accomplish this and it returns a MYSQL_RES (mysql result pointer)

MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)
{
   // send the query to the database
   if (mysql_query(connection, sql_query))
   {
      printf("MySQL query error : %s\n", mysql_error(connection));
      exit(1);
   }
 
   return mysql_use_result(connection);
}

since you are now connected and also preformed a query, lets say the query was

SHOW TABLES;

You can traverse the results with a while loop in C++ and use the mysql_fetch_row function from within the mysql library set where a row is a type of MYSQL_ROW.

MYSQL_ROW row;
  while ((row = mysql_fetch_row(res)) !=NULL)
      printf("%s\n", row[0]);

Here is the full code that will display all of the tables within the mysql database database, you will need to alter the username and password for the mysql access details.

#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>
 
// just going to input the general details and not the port numbers
struct connection_details
{
    char *server;
    char *user;
    char *password;
    char *database;
};
 
MYSQL* mysql_connection_setup(struct connection_details mysql_details)
{
     // first of all create a mysql instance and initialize the variables within
    MYSQL *connection = mysql_init(NULL);
 
    // connect to the database with the details attached.
    if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {
      printf("Conection error : %s\n", mysql_error(connection));
      exit(1);
    }
    return connection;
}
 
MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)
{
   // send the query to the database
   if (mysql_query(connection, sql_query))
   {
      printf("MySQL query error : %s\n", mysql_error(connection));
      exit(1);
   }
 
   return mysql_use_result(connection);
}
 
int main()
{
  MYSQL *conn;		// the connection
  MYSQL_RES *res;	// the results
  MYSQL_ROW row;	// the results row (line by line)
 
  struct connection_details mysqlD;
  mysqlD.server = "localhost";  // where the mysql database is
  mysqlD.user = "mysqlusername";		// the root user of mysql	
  mysqlD.password = "mysqlpassword"; // the password of the root user in mysql
  mysqlD.database = "mysql";	// the databse to pick
 
  // connect to the mysql database
  conn = mysql_connection_setup(mysqlD);
 
  // assign the results return to the MYSQL_RES pointer
  res = mysql_perform_query(conn, "show tables");
 
  printf("MySQL Tables in mysql database:\n");
  while ((row = mysql_fetch_row(res)) !=NULL)
      printf("%s\n", row[0]);
 
  /* clean up the database result set */
  mysql_free_result(res);
  /* clean up the database link */
  mysql_close(conn);
 
  return 0;
}

To compile up this program you will need to link to the mysql libraries and headers that are used within the program, e.g. mysql.h at the top of the program. To gain access to these, there is a nice mysql_config (you may need to install it via your package manager system if you do not have it already).

Here are my outputs of what is required on the command line for the g++ compiler

mysql_config --cflags
-I/usr/include/mysql  -DBIG_JOINS=1  -fno-strict-aliasing   -DUNIV_LINUX
mysql_config --libs
-Wl,-Bsymbolic-functions -rdynamic -L/usr/lib/mysql -lmysqlclient

These are the g++/c++ switches that tell the compiler where the libraries (-L) are and the headers (-I) and to insert into the g++/c++ compiler line within linux you can do something like

g++ -o mysqlconnect $(mysql_config --cflags) mysqlconnect.cpp $(mysql_config --libs)

Where the mysqlconnect.cpp is the c++ file of the above program and the output would be

./mysqlconnect
MySQL Tables in mysql database:
columns_priv
db
event
func
general_log
help_category
help_keyword
help_relation
help_topic
host
ndb_binlog_index
plugin
proc
procs_priv
servers
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user

Blob to store data in mysql database

To store data within a blob in a database can be a good thing at times because then you can just copy the database from one place to another and use the access rights on the database to restrict access to the “files” within the database.

There could be a few reasons why you want to store the data within a blob in the database, but here how the basics would work.

To start with you have to create a database and a table to store the data/file within the blob, of course if you have already created the database and/or the tables then alter as you think, but here is the basics.

  CREATE DATABASE phptestplace;
  CREATE TABLE storingData (id INT NOT NULL auto_incremenet, lblob BLOB, PRIMARY KEY (id));

And then within a php file you can access the database and a file.

  $link = mysql_connect('localhost', 'username', 'userpassword');
  if (!$link) {
      die('Could not connect: ' . mysql_error());
  }
// alter to your database name
  mysql_select_db("phptestplace", $link);

and now access the file and read in file

  $filename = "filetoload.txt";
  $handle = fopen($filename, "r");
  $contents = fread($handle, filesize($filename));
  fclose($handle);
 
// to insert into the database you need to add in the slashes for characters like / \ etc.
  $contents = addslashes($contents);

to insert into the blob you just, change the table and table name to what may have called it.

  $sqlquery = "insert into storingData(largeblob) values ('$contents')";
  mysql_query($sqlquery) or die("ERROR");*/

to get the data back (I am calling back the last inserted value into the table)

// get the data into a result variable
  $return = mysql_query ("select lblob from storingData where id = (select max(id) from storingData)") or die("LLL");
// get the contents of the blob from the return variable (it returns a array of data) and the list takes out the data from a array each part at time.
  list($newcontents) = mysql_fetch_array($return);

and then store the data from the database pull into a file, I have called it newfile.txt, but it is up to you.

  $fp = fopen('NEWFILE.txt', 'w');
  fwrite($fp, $newcontents);
  fclose($fp);

Of course can do it via a web page, using a HTML FORM enctype=”multipart/form-data” within the form tag otherwise it may not work.

SugarCRM add a new menu item

SugarCRM is a very nice and open source CRM (Customer relationship management) system. Being open source means that you are more than welcome to add/alter your own modules to it. I am going to do some modules which add in some basic information and how-to’s. This how to is how to add to a left menu item and in this case a account main menu left menu item.

Also going to be doing a module for it, so that you can upload to different SugarCRM’s that you may have, e.g. development version and live version.

The module consists of the main manifest.php file which holds all of the main details, module name author, description etc and also the install definitions.

Here is a basic manifest.php file that has the main details and install definitions for adding a menu item to the Accounts module.

<?php
$manifest = array(
  'acceptable_sugar_flavors' => array(
          0 => 'CE',
          1 => 'PRO',
          2 => 'ENT',
          3 => 'DEV'
        ),
    'acceptable_sugar_versions' => array (
        'regex_matches' => array (
            0 => "5\.*\.*.*"
        ),
    ),
 
    'name'              => 'Accounts insert left menu addition',
    'description'       => 'This module inserts a left menu addition',
    'author'            => 'Ian Porter',
    'published_date'    => '2009/06/01',
    'version'           => '0.1',
    'type'              => 'module',
    'icon'              => '',
    'is_uninstallable'   => 1,
    'silent' => true,
);
 
$installdefs = array (
  'id' => 'AccountsLeftMenu',
    'vardefs'=> array( ),
  'custom_fields' =>  array (  ),
  'copy' =>
  array ( ),
'menu'=> array(
array('from'=> '<basepath>/Menu.php',
'to_module'=> 'Accounts', ),
),
       'beans'=> array (
                 ),
  'language' => 
  array (
  ),
);
?>

As you can see from the above code, the acceptable sugar flavors means any of the version types of sugar, development, pro etc.. and the acceptable sugar versions means which version of sugar e.g. version 4.1.2 or 5.etc. the name etc speaks for itself really.

The installdefs are what happens with the files and such when the module is installed, id is the name of the module, the one that we are focusing in on is menu, this will insert the code below into the a set menu module (this case the Accounts menu structure).

Here is the Menu.php file

<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point'); 
 
/* licence and you stuff */
 
global $mod_strings, $app_strings, $sugar_config, $current_user;
 
/* you need to create the DetailViewPersonal.php file to communicate with */
 
if(ACLController::checkAccess('Accounts', 'edit', true)) {
    $module_menu[]=Array("index.php?module=Accounts&action=DetailViewPersonal&return_module=Accounts&return_action=DetailView&record=".$current_user->id, "Personal View",  'Accounts');
}
?>

The ACLController will check the access level of the user for editable, ACL(Access Control Level), and if so place a new menu below called “Personal View”, the action in the module_menu array is what is called and thus you will need to have a DetialViewPersonal.php in the modules/Accounts directory, but this was just a how to, of how to insert a menu item and not the underlying code.

There is more to come!.

Grab data from tables

Alter the variable @tableName to the table and also the @tableWhere for the where condition, I found that if you use the standard sql dumps that you was taking allot of other crap with you as well.

The code

DECLARE @colName VARCHAR(100), @colSql VARCHAR(500), @colSQLInsert VARCHAR(500), @TYPE INT, @auto INT, @tableName VARCHAR(50), @tableWhere VARCHAR(500);
SET @tableName = 'tablename';
SET @tableWhere = 'the where condition';
 
-- grab the table column names
DECLARE tablecol cursor FOR
SELECT name, typestat, autoval FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = @tableName);
 
--The @auto is the auto generated fields e.g. primary key. </b>
SET @colSql = '';
SET @colSQLInsert = '';
OPEN tablecol;
fetch tablecol INTO @colName, @TYPE, @auto;
while (@@fetch_status = 0)
BEGIN
       IF (@auto IS NULL)
       BEGIN
              IF (charindex('.',@colName) > 0) SET @colName = '['+@colName+']';
              SET @colSQL = @colName + ',' +@colSQL;
              IF (@TYPE = 2)
                     SET @colSQLInsert = '''''''+isnull(' + @colName + ',0)+'''''',' + @colSQLInsert;
              ELSE
                     SET @colSQLInsert = '''+str(isnull(' + @colName + ',''''))+'',' + @colSQLInsert;
       END
       fetch NEXT FROM tablecol INTO @colName, @TYPE, @auto;
END
close tablecol;
deallocate tablecol;
-- to build the sql statement, since it stops at 255 charactes split the outputs</b>
SELECT 'select (''insert into '+@tableName+' (';
DECLARE @loopingVal INT;
SET @loopingVal =0;
while (len(@colSQL) > @loopingVal)
BEGIN
       SELECT SUBSTRING(@colSQL, @loopingVal, 255);
       SET @loopingVal = @loopingVal + 255;
END
SELECT ') values (';
 
SET @loopingVal =0;
while (len(@colSQLInsert) > @loopingVal)
BEGIN
       SELECT SUBSTRING(@colSQLInsert, @loopingVal, 255);
       SET @loopingVal = @loopingVal + 255;
END
SELECT ')'') from '+@tableName + ' ' + @tableWhere;

The output will be x lines, and if you just copy them into a single line and this will display (once executed) the insert lines.

If anyone has any better methods, please comment 🙂

Hello World!!

Well, might as well do a Hello world in all of the languages. Once you have a database installed, e.g. MySQL, start up the database and goto the query line so that you are able to *talk* to the database. Type in

SELECT 'Hello World';

This should show Hello World in the output of the database.

I shall have to do Hello World in all languages now 🙂