msgbartop
A chronological documentation test project, nothing serious, really!
msgbarbottom

30 Apr 2007 mySQL and Stored Triggers

This is an example on how you an use stored triggers in mySQL

/* test_workaround.sql */
USE test ;
DROP TABLE IF EXISTS `addresses`;
CREATE TABLE `addresses` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mac_address` CHAR(17) NOT NULL DEFAULT '00:16:DD:xx:xx:xx', /* see
http://anonsvn.wireshark.org/wireshark/trunk/manuf */
  `serial_number` VARCHAR(60) NOT NULL DEFAULT '',
  `comment` TEXT NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=400 DEFAULT CHARSET=utf8 COMMENT='Assigned MAC
Addresses' ;
/* DROP TRIGGER compute_inserted_mac_address ; */
/* DROP TRIGGER computed_updated_mac_address ; */
DELIMITER //
CREATE TRIGGER compute_inserted_mac_address BEFORE INSERT ON addresses
FOR EACH ROW
    BEGIN
        DECLARE next_id INT(11) UNSIGNED ;
        DECLARE tmp CHAR(6);
        DECLARE right_now TIMESTAMP;
        SELECT AUTO_INCREMENT FROM information_schema.tables
            WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'addresses'
            INTO next_id ;
        SET tmp = RIGHT(CONCAT('000000', UPPER(CONV(12582912 + next_id, 10,
16))), 6) ; /* 0 => ... :C0:00:00 */
        SET right_now = NOW() ;
        SET NEW.mac_address = CONCAT('00:16:DD:', SUBSTRING(tmp, 1, 2), ':',
SUBSTRING(tmp, 3, 2), ':', SUBSTRING(tmp, 5, 2)) ;
        SET NEW.created_on = right_now ;
        SET NEW.updated_on = right_now ;
    END ;
//
DELIMITER ;
DELIMITER //
CREATE TRIGGER computed_updated_mac_address BEFORE UPDATE ON addresses
FOR EACH ROW
    BEGIN
        DECLARE tmp CHAR(6) ;
        SET tmp = RIGHT(CONCAT('000000', UPPER(CONV(12582912 + OLD.id, 10,
16))), 6) ; /* 0 => ... :C0:00:00 */
        SET NEW.mac_address = CONCAT('00:16:DD:', SUBSTRING(tmp, 1, 2), ':',
SUBSTRING(tmp, 3, 2), ':', SUBSTRING(tmp, 5, 2)) ;
        SET NEW.created_on = OLD.created_on ;
        SET NEW.updated_on = NOW();
    END ;
//
DELIMITER ;

Show triggers and insert new data to the database

SHOW TRIGGERS
INSERT INTO `addresses` (serial_number, comment) VALUES ('B0001BKAP8', '');
INSERT INTO `addresses` (serial_number, comment) VALUES ('B0000089JE', '');
SELECT * FROM addresses ;

Tags: , ,

Posted by

30 Apr 2007 Change all entries in DHCP from 0: to 00:

This command have to be performed in the vim editor:

:%s/hardware ethernet 0:/hardware ethernet 00:/

Tags: ,

Posted by

30 Apr 2007 How to restrict users to SFTP only instead of SSH

Sometimes you want to have users, that have access to files on your server, but don’t want them to be able to log in and execute commands on your server.
(more…)

Tags: , ,

Posted by

30 Apr 2007 How to find out which process is listening upon a port

If you have the Apache webserver running on port 80 that will provide a suitable test candidate. If not you can choose another port you know is in use. (more…)

Tags:

Posted by

30 Apr 2007 Monitoring active network connections with tcptrack

There are many common scenarios where keeping track of open network connections is useful. General troubleshooting or fixing specific problems are two obvious cases which spring to mind. The most useful tool I’ve discovered for this purpose is tcptrack. (more…)

Posted by