Sharing Hashes between Mambo 4.x and SugarCRM 4.5.1 Using Views

Its been a while since i posted an article, busy months have passed and im still within timeframes and deadlines.. well most of the time real life intervenes eh? anyways heres my article about Sharing Hashes between Mambo 4.x and SugarCRM 4.5.1 Using MySQL Views

MySQL Views is(are) indeed a wonder, too bad its only available to MySQL versions 5 and up (which nowadays arent offered yet by most shared hostings). Its really a timesaver to be able to construct a table out of a select query without having to build one and have it updated for a function to refer on.

In this article, I will outline simple procedures to create a User Table from within a SugarCRM Database which will hook hashes from a Mambo user table, the aftereffect are the following:

• Both Mambo and SugarCRM systems will refer to the same user MD5 password hashes, causing passwords between to be the same
  
• When a User logs into either of the system, he/she will use the same password, and changing the password from the Mambo system will also change the password in SugarCRM without the need to keep it synced

Basically, the following are the prerequisites:

• MySQL 5
• Both Mambo and SugarCRM must reside on the same Database Server Account
• There should be a common user between the system with the same username, this means that there should be a user with the same username that exitst in both User Tables of SugarCRM and Mambo.

Procedures:

1. Set up the View using the following construct within the SugarCRM Database:
   

   CREATE VIEW users_auth
   AS
   SELECT
   `e.id` AS `id`,
   `d.username` AS `user_name`,
   `d.password` AS `user_hash`,
   `e.authenticate_id` AS `authenticate_id`,
   `e.sugar_login` AS `sugar_login`,
   `e.is_admin` AS is_admin,
   `e.status` AS `status`,
   `e.portal_only` AS `portal_only`,
   `e.is_group` AS `is_group`
   FROM
   mambo_database.mos_users d
   LEFT JOIN sugarcrm_database.users e
   on d.username = e.user_name;

   The above query creates a MySQL View named "users_auth" and gets username and password info from the Mambo database and creates it within the SugarCRM database. Examine the query and modify as to your needs
   


2. Next Step is to modify the "SugarAuthenticateUser.php" file located in the sugarcrm/modules/Users/authentication/SugarAuthenticate directory so all select queries pertaining to the sugarcrm.users table will point to sugarcrm.users_auth view you have previously created. These are lines 43 and and 63 and they should appear as follows

   43: $query = "SELECT * from users_auth where...

   and
   

   63: $query = "SELECT * from users_auth where...

Thats it, the concept is much self-explanatory. If you have further specific questions, feel free to post comments and ill cover it in detail

Issues when Tunneling MySQL

If the following error message ring a bell to you:

"ERROR 2013 (HY000): Lost connection to MySQL server during query"

And you have set up SSH tunnelling to secure your MySQL connection, check if you have complied with the following checklist:

1. Is "AllowTcpForwarding yes" set on the /etc/ssh/sshd_config of the MySQL Server? If not, set this directive to yes and restart SSH. It might be necessary to restart the server machine as shutting down SSHD alone will block you out from a remote server as you will have no access except by physically interfacing with it.
   
2. Is SSH on the originating server running on the background specifically the SSH tunneling command you executed? Check this using the command "top" or "ps -ef | grep "ssh"". If you dont see the process you ran to tunnel MySQL, then run it.
3. If you are using the root account to do the tunnel from the originating server, make sure that the "PermitRootLogin" is set to yes on the "/etc/ssh/sshd_config" of the MySQL Server. If this isnt set, set it and restart the SSHD daemon by restarting the server.

This is a sample SSH tunnel command to forward local(originating) MySQL connections to a remote MySQL Server:

"ssh -N -f -g -L 3306:localhost:3306 root@mysql_server.com"

The above command tells SSH to forward all local connections from port 3306 to mysql_server.com on port 3306 and use the root credential to connect. The above command will also run on the background so you may have to "kill" it if you no longer need it to be running.

For more info, visit the following link:
SSH Tunneling on Google.com

Get that "PERL" right!!

How many times have you stumbled upon an error message similar to this one:

foo: bar: not found

But when you checked:

which bar

It showed you that it exists and sitting there staring at you?

The "foo: bar: not found" error message does not indicate that bar could not be found, but rather bar exists but is calling something that could not be found. This is the case with Perl scripts when the script cannot find where Perl is. I have noticed that most of out "out-of-the-box" Perl scripts point to "/usr/local/bin/perl" whereas some of the OS pre-bundled Perl is preinstalled at "/bin/perl", hence, when you run a Perl script, the "foo: bar: not found" pops out the terminal.

If you compile Apache, PHP and PECL packages, then the following errors might ring a bell:

/usr/local/bin/autoconf: /usr/local/bin/autom4te: not found /usr/local/bin/autoconf: /usr/local/bin/autoheader: not found /usr/local/apache2/bin/apxs: not found

and many other variants of the same error.

Solution:

Edit the nearest command which invoked the error, for ex. "/usr/local/bin/autom4te: not found" - the nearest invoker is autom4te. This denotes that probably autom4te is the Perl Script. Edit the file by hand and at the beginning of the script, write down the correct path to Perl.

If at the beginning of the file autom4te says:
#! /usr/local/bin/perl but "which perl" says "/bin/perl" then you have to update paths as necessary.

Well you can always symlink the Perl binary to where most Perl scripts expects it to be. ex:
"ln -s /bin/perl /usr/local/bin/perl"

FOSS Act passed to the Philippine Congress

Cong. Teodoro A. Casiño passed an Act to the 13th Congress which will promote the use Free Open Source Software in the Philippines. The said Act will also ammend the current Philippine Republic Act 8923 also known as the "Intellectual Property Code of the Phillippines". The Act which will be called the "Free Open Source Software(FOSS) Act of 2006" will definetly encourage Open Source practices among Government bodies, which will be a great jumpstart to let the masses recognize the importance/gains brought about using FOSS. By enforcing policies such as limiting the use proprietary software on Government owned offices, operations such as Research and Development must utilize in 1st priority Open Source solutions over propriety ones. These will in turn benefit the state because the Government will take charge of the Source codes and be in full control of the over-all implementaion of IT-related processes. The following was quoted from the original house bill:

7.1. Use of Open Standards – The Government shall use only ICT goods and services that comply with open standards as defined in section 4 of this Act;

7.2. Open Formats – all government communication and data intended for public consumption shall be encoded in open standard data formats;

7.3. Clarification on the Use of RAND License for Open Standards – open standards licensed under “Reasonable and Non-Discriminatory” (RAND) terms and hence are not free but are available for a reasonable fee shall be used only under extraordinary circumstances as defined in section 7.5 of this Act.

7.4. Use of FOSS – The government shall apply only FOSS or FOSS solutions, as defined in section 5 of this Act, in all ICT projects and activities;

The FOSS Act of 2006 of the Philippines will fully support the Open Source standards and criteria set by the world-known FOSS Advocate Body. This means that current widely recognized FOSS license and standards will be legally acknowledged by the Government of the Republic of the Phillippines.

This FOSS Act will enforce the migration from propriety software to Open Source software used by the Government institions in all levels with certain exceptions as quoted from the original house bill as follows:

7.1. Extraordinary Circumstances – The following are extraordinary circumstances which may exempt government from using open standards and FOSS:

7.5.1.Where there is no reasonably available ICT good or services supporting open standards and/or FOSS in the field, area or activity that the Government intends to enter or participate; or,

7.5.2.Where a particular government agency or office has an existing, widely-used and widely implemented proprietary ICT system and there are no reasonably available technology using open standards and/or FOSS that can be used with the said proprietary system.

7.2. Determination of Extraordinary Circumstances – The CICT, through the Office of FOSS Migration created under section 17 of this Act shall determine, through a public hearing, whether there exists extraordinary circumstances that will exempt a government agency or project from sections 7.1 to 7.4 of this Act.

I dearly hope that the above restrictions won't get abused by some government officials by resorting to propriety/commercial software and gain kickbacks (ok i'll shut my mouth, no politics.. :) ).

But being stated clearly, these exceptions on the enforcement of Open Source usage among IT-Related or IT-empowered Government instutions should not hinder the very goal of the Act itself. I am quite confident that the Philippine Commission on Information and Communication Technology(CICT) can handle this very well as even before the Commission has been promoting FOSS without direct intervention from the Philippine Congress. Once the bill is passed, the CICT will further have power promoting the application of Open Source in Government offices.

Basic: Preparing Solaris 10 to run Mambo CMS

Brief Backgrounder on Solaris 10:

Solaris 10 is the latest OS offered by Sun Microsystems. Unlike its predecessors – Solaris 9 and previous releases, this version of Solaris is free for use with no restriction of any kind. It also has a twin named OpenSolaris which is essentially an exact replica except that it is available in source and as the named implies, yes OpenSolaris is open source. More info here and here.

Preparing Solaris 10:

Preparing Solaris 10 environment to run Mambo is basically having to set up Apache, MySQL and PHP environment and nothing else, as you might know the system requirements of Mambo is any system that can run certain versions of Apache, MySQL and PHP – click here for details. Solaris 10 is bundled with Apache 2 and MySQL 5 already, its just a matter of configuring these which we will discuss in detail later. PHP 5 is a little different story though as we will need a source distribution to compile this ourselves. PHP 5 needs to be a module for Apache, it has to support MySQL so bear with as we dig deeper.

Configuring Apache:

The configuration file for Apache should be located in “/etc/apache2/”. From a fresh installation it is named as httpd.conf-example under the same directory. You have to rename it to httpd.conf so Apache will recognize it.

# mv /etc/apache2/httpd.conf-example /etc/apache2/httpd.conf

Using your text editor (gedit or vi), edit httpd.conf to your liking, take note of the following directives as you may need to set this as neccessary:

ServerName – The IP address or hostname of your machine (ex. 127.0.0.1 or example.com)

Listen – the port where Apache will listen to (ex. 80)

Next, is invoke the terminal and enable the service using the following command:

# svcadm enable apache2

Test Apache by opening the browser and pointing it to http://localhost.

Note that depending with your Desktop GUI, invoking the Terminal is done differently. Under the Java Desktop System 3, you do this by right clicking the desktop and selecting Open Terminal. Under CDE, you do this by clicking the Tools menu (the arrow on top of Performance Meter) and clicking console.

Configuring MySQL:

The MySQL database tables need to be created 1^st. It will be the physical representation of your would-be MySQL tables as you create them. Moreover, the physical storage for your MySQL databases. Invoke the terminal and do the following command:

# /usr/sfw/bin/mysql_install_db

You should see several messages. MySQL Daemon by default does not allow root to run it. In such case a user needs to be set up specifically to run the MySQL server. Proper permissions are also mandatory. Invoke the terminal and issue the following commands:

# groupadd mysql
# useradd –g mysql mysql
# chown root /var/mysql
# chgrp –R mysql /var/mysql
# chmod –R 770 /var/mysql

What the above commands do are:

1. Creates a group called MySQL,


2. Creates a User named MySQL and makes it a member for the MySQL Group,


3. Sets the ownership of the MySQL data directory to root,


4. Sets the group of the MySQL data directory to MySQL group and


5. Sets Read write access for the Group and Owner designated to the MySQL data directory while leaving others with no access.

Next is to set the MySQL Configuration File: Using the terminal, create a copy of a sample MySQL configuration file to the /etc directory as follows:

# cp /usr/sfw/share/mysql/my-medium.cnf /etc/my.cnf

Now to start the MySQL Daemon, use the following command:

# cd /usr/sfw/sbin/
# ./mysqld_safe --user=mysql &

To set the MySQL Password, the following commands need to be executed:

# cd /usr/sfw/bin
#./mysqladmin –u root password new-password
#./mysqladmin –u root –h localhost password new-password

To start the MySQL Daemon automically when Solaris starts, you need to bootstrap the startup files to rcs.d, rc1.d, rc2.d and rc3.d under the /etc directory. Issue the following commands using the terminal:

# ln /etc/sfw/mysql/mysql.server /etc/rcS.d/k00mysql
# ln /etc/sfw/mysql/mysql.server /etc/rc0.d/K00mysql
# ln /etc/sfw/mysql/mysql.server /etc/rc1.d/K00mysql
# ln /etc/sfw/mysql/mysql.server /etc/rc2.d/K00mysql
# ln /etc/sfw/mysql/mysql.server /etc/rc3.d/S99mysql

Compiling PHP 5

If you have gone this far without errors - congratulations, but we are still at the half. Since we will need to compile PHP from source, we will need to set Solaris for compiling environment and will install a bunch of applications.

Note that the variable $PATH which is an array of location where Solaris searches for applications is not set properly on a fresh installation. Consider setting up your path as follows
On TCSH:
# setenv PATH {$PATH}: /opt/csw/bin:/usr/sfw/bin:/usr/dt/bin:/usr/css/bin
On SH:
# set PATH=$PATH:/opt/csw/bin:/usr/sfw/bin:/usr/dt/bin:/usr/css/bin
# export PATH

A default install of PHP requires the following:

• gcc
• make
• flex
• m4
• autoconf
• automake
• perl
• gzip
• GNU tar
• GNU sed

However a standard install of Solaris is already bundled with most of the listed above, what you'll need is only autoconf, automake and GNU sed, you can download them directly by invoking the following commands from the terminal (you should be in a directory where you intend to store the packages). The full is at http://www.sunfreeware.com/programlistintel10.html

# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/autoconf-2.60-sol-10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/8/automake-1.5-sol8-intel-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/sed-4.1.5-sol10-x86-local.gz

Note that the Automake version is for Solaris 8 but should also work, links to the Solaris 9 and 10 versions are dead on the time of this writing as reffered here.

Once you have downloaded the above packages, uncompress them using gunzip

# gunzip *.gz

Then install them using pkgadd -d

# pkgadd -d autoconf-2.60-sol-10-x86-local.gz
# pkgadd -d automake-1.5-sol8-intel-local.gz
# pkgadd -d sed-4.1.5-sol10-x86-local.gz

After successfully doing the steps above which have set up a compiling environment for PHP, there are still a few applications that needs to be installed similar to the procedure above. The following applications are needed for a fully functional and optimal Mambo installation later: Download the following files from http://www.sunfreeware.com/programlistintel10.html or use wget for direct download

# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/zlib-1.2.3-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/xpm-3.4k-sol10-intel-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/freetype-2.2.1-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/freetype-2.2.1-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/fontconfig-2.2.98-sol10-intel-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/expat-1.95.5-sol10-intel-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/libiconv-1.11-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/libpng-1.2.12-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/jpeg-6b-sol10-intel-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/openssl-0.9.8d-sol10-x86-local.gz
# wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/curl-7.15.4-sol10-x86-local.gz
#wget ftp://ftp.sunfreeware.com/pub/freeware/intel/10/ncurses-5.5-sol10-x86-local.gz

Install the above application in exact order as downloaded to stay safe. Certain dependencies might not be fullfilled if installed in random..

..TO BE CONTINUED