Geoff Jacobs April 17, 2009 code, sysadmin 0 comments full post »

mysql schema…. finally

I know this has taken waaay to long to post. But here it is if anyone still wants it.

# CREATE DATABASE voip;
# USE voip;

#
# TABLE STRUCTURE FOR TABLE 'allocation'
#

# DROP TABLE IF EXISTS allocation;
CREATE TABLE `allocation` (
`allocation_id` INT(11) NOT NULL AUTO_INCREMENT,
`asset_id` INT(11) NOT NULL DEFAULT '0',
`site_id` INT(11) NOT NULL DEFAULT '0',
`extension` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`allocation_id`)
) ENGINE=MyISAM AUTO_INCREMENT=58 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

#
# TABLE STRUCTURE FOR TABLE 'asset'
#

# DROP TABLE IF EXISTS asset;
CREATE TABLE `asset` (
`asset_id` INT(11) NOT NULL AUTO_INCREMENT,
`manufacturer` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`model` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`serial` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`mac` VARCHAR(12) COLLATE latin1_general_ci NOT NULL DEFAULT '000000000000',
`phone_ip` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`purchase_price` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`purchase_date` DATE NOT NULL DEFAULT '0000-00-00',
`purchase_from` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`invoice_number` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`asset_id`)
) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

#
# TABLE STRUCTURE FOR TABLE 'phoneline'
#

# DROP TABLE IF EXISTS phoneline;
CREATE TABLE `phoneline` (
`line_id` INT(11) NOT NULL AUTO_INCREMENT,
`phone_number` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`line_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

#
# TABLE STRUCTURE FOR TABLE 'server'
#

# DROP TABLE IF EXISTS server;
CREATE TABLE `server` (
`server_id` INT(11) NOT NULL AUTO_INCREMENT,
`site_id` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`server_ip` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`server_hostname` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`server_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

#
# TABLE STRUCTURE FOR TABLE 'site'
#

# DROP TABLE IF EXISTS site;
CREATE TABLE `site` (
`site_id` INT(11) NOT NULL AUTO_INCREMENT,
`site_name` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`street_number` INT(11) NOT NULL DEFAULT '0',
`street_name` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`city` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`state` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`country` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`site_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

#
# TABLE STRUCTURE FOR TABLE 'user'
#

# DROP TABLE IF EXISTS USER;
CREATE TABLE `user` (
`user_id` INT(11) NOT NULL AUTO_INCREMENT,
`samaccountname` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Geoff Jacobs October 3, 2008 linux, sysadmin, technical 2 comments full post »

Glintech integrates Asterisk, Jabber for open source UC platform

A week or so ago, my boss came to me asking if I could write something on what’d I’d done internally with XMPP and Asterisk for someone he knew at Computerworld. I was only too happy to have someone listen to my ramblings, so I wrote up some replies to some of the questions his friend had asked.
Last night my boss sent me a link to the article. Unfortunately, the article makes it look like he was the one who replied to the questions instead of me, but it’s nice to get some of my work out there anyway.
Here’s the full article: Glintech integrates Asterisk, Jabber for open source UC platform.
For those who actually understand what the article is about, you’ll probably realise that what I did here wasn’t anything particularly difficult or out of the ordinary. But hopefully for those who don’t, it might open a few eyes to some of the things that can be done with open source software.

Geoff Jacobs July 3, 2008 uncategorized 2 comments full post »

A few svn pre-commit hooks

I’ve recently been looking around for some pre-commit hooks for our subversion repositories. I figured that since I have them all up and running now, I might as well share them.
I can’t remember where I got all of these from, so if it’s one of yours, then I apologise. Feel free to contact me and I’ll give you credit for it.

Check for blank/empty commit messages and reject
This goes in hooks/pre-commit

REPOS="$1"
TXN="$2"

# Make sure that the log message contains some text.
SVNLOOK=/usr/bin/svnlook
SVNLOOKOK=1
$SVNLOOK log -t "$TXN" "$REPOS" | \
grep "[a-zA-Z0-9]" > /dev/null || SVNLOOKOK=0
if [ $SVNLOOKOK = 0 ]; then
echo Empty log messages are not allowed. Please provide a proper log message. 1>&2
exit 1
fi
exit 0

Check that the commit message has a reference to a JIRA issue
This goes in hooks/pre-commit, it also uses ‘check_log_message.sh’ which can be found down further.

REPOS="$1"
TXN="$2"

# Check log message for proper task/bug identification
if [ -x ${REPOS}/hooks/check_log_message.sh ]; then
${REPOS}/hooks/check_log_message.sh "${REPOS}" "${TXN}" 1>&2 || exit 1
fi
exit 0

check_log_message.sh

#!/bin/bash

REPOS="${1}"
TXN="${2}"

SVNLOOK=/usr/bin/svnlook

LOG_MSG_LINE1=`${SVNLOOK} log -t "${TXN}" "${REPOS}" | head -n1`

if (echo "${LOG_MSG_LINE1}" | egrep '^[a-zA-Z]+[-][1-9][0-9]*[:]?[\s]*.*$' > /dev/null;) \
|| (echo "${LOG_MSG_LINE1}" | egrep '^[nN][oO][jJ][iI][rR][aA][:]?[\s]*.*$' > /dev/null;) \
|| (echo "${LOG_MSG_LINE1}" | egrep '^\[maven-release-plugin\][\s]*.*$' > /dev/null;)
then
exit 0
else
echo ""
echo "Your log message does not contain a JIRA Issue identifier (or bad format used)"
echo "The JIRA Issue identifier must be the first item on the first line of the log message."
echo ""
echo "Proper JIRA format: 'AAA-000'"
echo "JIRA regex: '^[a-zA-Z]+[-][1-9][0-9]*[:]?[\s]*.*$'"
exit 1
fi

Check that commit message is more than 5 characters long
This goes in hooks/pre-commit

REPOS="$1"
TXN="$2"

SVNLOOK=/usr/bin/svnlook
LOGMSG=$($SVNLOOK log -t "$TXN" "$REPOS" | grep [a-zA-Z0-9] | wc -c)

if [ "$LOGMSG" -lt 5 ]; then
echo -e "Please provide a meaningful comment when committing changes." 1>&2
exit 1
fi
exit 0

Geoff Jacobs May 7, 2008 linux, sysadmin 0 comments full post »

Erratic Sendmail problems…

I’ve recently installed Drupal 6, and was having problems sending out emails (specifically for new users and password resets).
The problem was that sometimes the mail was coming through, and other times it wasn’t (it didn’t even appear in our mail server logs).
After lots of scouting around, I noticed that in /var/log/maillog (on FC6), the relay kept on changing.
On our network, we have 3 domain controllers, one of which is a mail server. Send mail was using it’s “Smart” relay to figure out where to relay the mail through, but unfortunately it kept finding whichever domain controller replied first, rather than just the mail server. This meant that sometimes, when it got the mail server, the email would come through, and other times it wouldn’t.
To fix the problem, I had to edit the “Smart” relay host in:

/etc/mail/sendmail.cf

I searched for “DS” and came across a section such as:

# "Smart" relay host (may be null)
DS

I changed this to read

# "Smart" relay host (may be null)
DSmail.domain.com

where mail.domain.com is the name of the mail server.
Then I restarted the sendmail service using:

service sendmail restart

and everything started working!

Geoff Jacobs November 6, 2007 misc 0 comments full post »

Contacting me

I was speaking to someone on the Asterisk IRC channel a little while ago who had read my blog, but couldn’t find my email address. For those who want to contact me, it’s geof…@gmail.com (click the dots). I’m on gtalk most of the day too – Sydney time that is.