Sql join query

select  package,end_on from did_manager d, user_packages up where d.did_number='15304364061' and d.cust_id=up.user_id;

Solution For Opensips MYSQL module in Ubuntu 12.04 | libmysqlclient16 Installation in Ubuntu 12.04

First run the following:

sudo dpkg -i mysql-workbench-gpl-5.2.40-1ubu1104-i386.deb

As you know this will fail about the dependencies. Then wget and install the following:

wget https://launchpad.net/ubuntu/+archive/primary/+files/libzip1_0.9.3-1_i386.deb wget http://launchpadlibrarian.net/94563300/libmysqlclient16_5.1.58-1ubuntu5_i386.deb 

Next install:

sudo dpkg -i libzip1_0.9.3-1_i386.deb sudo dpkg -i libmysqlclient16_5.1.58-1ubuntu5_i386.deb

• If you're using 64bit, you'll want this instead:

  wget https://launchpad.net/ubuntu/+archive/primary/+files/libzip1_0.9.3-1_amd64.deb

  sudo dpkg -i libzip1_0.9.3-1_amd64.deb 

  wget http://launchpadlibrarian.net/94808408/libmysqlclient16_5.1.58-1ubuntu5_amd64.deb 

  sudo dpkg -i libmysqlclient16_5.1.58-1ubuntu5_amd64.deb

Opensips Installation on Ubuntu | Opensips installation and Configuration for MYSQL

Starting:

apt-get update && apt-get upgrade
apt-get install build-essential openssl bison flex
apt-get install mysql-server libmysqlclient15off libmysqlclient-dev

Remember to create your sql root password

Now download the last version of opensips in the following site http://opensips.org/
cd /usr/src
wget http://opensips.org/pub/opensips/latest/src/opensips-1.4.4-tls_src.tar.gz #opensips-1.4.4-tls_src.tar.gz (atual version: 11/03/2009)

tar xvzf opensips-1.4.4-tls_src.tar.gz

cd opensips-1.4.4-tls

Compile the Opensips
we will compile Opensips with all available modules:

make all include_modules="db_mysql" modules

make install include_modules="db_mysql" modules

Now copy the files from the extracted directory to the followings:

cp /usr/src/opensips-1.4.4-tls/packaging/debian-etch/opensips.default /etc/default/opensips
cp /usr/src/opensips-1.4.4-tls/packaging/debian-etch/opensips.init /etc/init.d/opensips



Make the following changes in the files /etc/default/opensips:

vim /etc/default/opensips

RUN_OPENSIPS=yes

1. User to run as

USER=opensips

1. Group to run as

GROUP=opensips

1. Amount of memory to allocate for the running OpenSIPS server (in Mb)

MEMORY=128

Change also the file /etc/init.d/opensips:

vim /etc/init.d/opensips

Replace the line DAEMON=/usr/sbin/opensips to:
DAEMON=/usr/local/sbin/opensips

Give execution permission to the file:
chmod +x /etc/init.d/opensips

create the opensips user:
adduser opensips

Now create the dir:
mkdir /var/run/opensips

Configuring the Opensips:

We must change the file in /usr/local/etc/opensips/opensipsctlrc
vim /usr/local/etc/opensips/opensipsctlrc

And make the following change:
SIP_DOMAIN=localhost
DBENGINE=MYSQL
DBHOST=localhost
DBNAME=opensips
DBRWUSER=opensips
DBRWPW="opensipsrw"
DBROUSER=opensipsro
DBROPW=opensipsro
DBROOTUSER="root"
USERCOL="username"
INSTALL_EXTRA_TABLES=ask
INSTALL_PRESENCE_TABLES=ask
INSTALL_SERWEB_TABLES=ask
CTLENGINE="FIFO"
OSIPS_FIFO="/tmp/opensips_fifo"
PID_FILE=/var/run/opensips/opensips.pid


Now create the database:
opensipsdbctl create
This command will ask for the root mysql password.

Configuring Mysql support:

make the following changes in the opensips file configuration /usr/local/etc/opensips/opensips.cfg/:

vim /usr/local/etc/opensips/opensips.cfg
loadmodule "db_mysql.so"
loadmodule "auth.so"
loadmodule "auth_db.so"

1. modparam("usrloc", "db_mode", 0); #THIS LINE MUST TO BE COMMENTED

modparam("usrloc", "db_mode", 2)
modparam("usrloc", "db_url", "mysql://opensips:opensipsrw@localhost/opensips")

1. ----- auth_db params -----

/* uncomment the following lines if you want to enable the DB based authentication */
modparam("auth_db", "calculate_ha1", yes)
modparam("auth_db", "password_column", "password")
modparam("auth_db", "db_url", "mysql://opensips:opensipsrw@localhost/opensips")


Create the softphone user in the database:
opensipsctl add 1234 1234

opensipsctl add 5678 5678

Create the Opensips user in the Mysql:

mysql> GRANT ALL PRIVILEGES ON *.* TO opensips@localhost IDENTIFIED BY 'opensips';

GRANT ALL PRIVILEGES ON *.* TO opensips@127.0.0.1 IDENTIFIED BY 'opensips';

Starting OpenSIPS

There are two ways tho start the opensips:
Use just one of them;

1. /etc/init.d/opensips start | stop
2. opensipsctl start | stop

Finished instalation.

Now you can test using X-lite or your softphone registering with the created opensips user.

eg: the created opensips users;

Create teh softphone user in the database:
opensipsctl add 1234 1234 ## user and pass

opensipsctl add 5678 5678