Custom auto increment values

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

001-000034
001-000035
001-000036
...

To make it possible we have an option based on past article MySQL Sequences:

  1. Create the table to store the current sequence values:
    CREATE TABLE _sequence
    (
        seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
        seq_group VARCHAR(10) NOT NULL,
        seq_val INT UNSIGNED NOT NULL
    );
  2. Create a function to get and increment the current value:
    delimiter //
    DROP FUNCTION IF EXISTS getNextCustomSeq//
    CREATE FUNCTION getNextCustomSeq
    (
        sSeqName VARCHAR(50),
        sSeqGroup VARCHAR(10)
    ) RETURNS VARCHAR(20)
    BEGIN
        DECLARE nLast_val INT; 
     
        SET nLast_val =  (SELECT seq_val
                              FROM _sequence
                              WHERE seq_name = sSeqName
                                    AND seq_group = sSeqGroup);
        IF nLast_val IS NULL THEN
            SET nLast_val = 1;
            INSERT INTO _sequence (seq_name,seq_group,seq_val)
            VALUES (sSeqName,sSeqGroup,nLast_Val);
        ELSE
            SET nLast_val = nLast_val + 1;
            UPDATE _sequence SET seq_val = nLast_val
            WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
        END IF; 
     
        SET @ret = (SELECT concat(sSeqGroup,'-',lpad(nLast_val,6,'0')));
        RETURN @ret;
    END// 
     
    delimiter ;
  3. Create a stored procedure to modify a current sequence value:
    delimiter //
    DROP PROCEDURE IF EXISTS sp_setSeqCustomVal//
    CREATE PROCEDURE sp_setCustomVal(sSeqName VARCHAR(50),  
                  sSeqGroup VARCHAR(10), nVal INT UNSIGNED)
    BEGIN
        IF (SELECT COUNT(*) FROM _sequence  
                WHERE seq_name = sSeqName  
                    AND seq_group = sSeqGroup) = 0 THEN
            INSERT INTO _sequence (seq_name,seq_group,seq_val)
            VALUES (sSeqName,sSeqGroup,nVal);
        ELSE
            UPDATE _sequence SET seq_val = nVal
            WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
        END IF;
    END//
    delimiter ;

Testing the new functions:

  1. Create a table:
    CREATE TABLE custom_autonums
    (
       id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       seq_1 VARCHAR(20), -- custom sequence 1
       seq_2 VARCHAR(20), -- custom sequence 2
       UNIQUE(seq_1),
       UNIQUE(seq_2)
    );
  2. Create trigger:
    delimiter //
    DROP TRIGGER IF EXISTS custom_autonums_bi//
     
    CREATE TRIGGER custom_autonums_bi BEFORE INSERT ON custom_autonums
    FOR each ROW
    BEGIN
       SET NEW.seq_1 = getNextCustomSeq("seq_1","001");
       SET NEW.seq_2 = getNextCustomSeq("seq_2","DBA");
    END//
     
    delimiter ;
  3. Insert some values:
    INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
    SELECT * FROM custom_autonums;
    +----+------------+------------+
    | id | seq_1      | seq_2      |
    +----+------------+------------+
    |  4 | 001-000001 | DBA-000001 |
    |  5 | 001-000002 | DBA-000002 |
    |  6 | 001-000003 | DBA-000003 |
    +----+------------+------------+
    3 ROWS IN SET (0.00 sec)
  4. Altering current values:
    CALL sp_setCustomVal('seq_1','001',675);
     
    INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
    SELECT * FROM custom_autonums;
    +----+------------+------------+
    | id | seq_1      | seq_2      |
    +----+------------+------------+
    |  4 | 001-000001 | DBA-000001 |
    |  5 | 001-000002 | DBA-000002 |
    |  6 | 001-000003 | DBA-000003 |
    |  7 | 001-000676 | DBA-000004 |
    |  8 | 001-000677 | DBA-000005 |
    |  9 | 001-000678 | DBA-000006 |
    +----+------------+------------+
    6 ROWS IN SET (0.00 sec)

Enjoy!

CMS based on phpBB posts

phpBBIf you are using phpBB maybe you need to show a individual phpBB post on an external page (outside of our phpBB forum) but it can be frustrating because phpBB uses a key in each bbcode (bitfield), but when I explore the source code I found the solution to extract parsed HTML text from phpBB posts.

Also I try to use a phpBB CMS Mod but I leave it because is still in development and now I’m trying to make my own CMS from phpBB posts, I use some code from phpBB tutorials and now I have a very basic CMS using phpBB Posts.

This is an overview:

  • I use apache mod_rewrite to make search engine friendly URL’s. (like this post)
  • The phpBB posts are located in a hide moderated forum, it also can use as a section to allow phpBB user post articles.
  • The media content (images, attachments, etc) is stored in phpBB files section.
  • I make custom .tpl files to sections: frontpage, article listings, articles, etc.
  • Also I defined custom bbcodes to show: head sections (h1,h2,h3…), highlighted code, HTML  tables, notes, comments, etc.

But, is still in development I need to finish the resources section and multi-language content, from now all content will be based on phpBB.

You can view the CMS running on Latindevelopers.com (spanish only) when I finish this custom phpBB CMS I will test the security, and maybe publish under GPL.

Install bmon as service

bmon is a useful network monitoring tool, the most useful feature is that bmon store historical data from las 60 seconds, minutes, hours and days, the “problem” is that bmon needs to be running all time.

If you have a network server with Linux as proxy, you will probably need to see the current bandwidth usage or see what happens 60 minutes ago.

We can use screen with a running bmon process to generate HTML result of current bandwidth usage, and we will see generated HTML pages from Apache.

Steps to install and configure

Pasos para instalar y ejecutar:

  1. Install bmon and screen:

    sudo apt-get install screen bmon
     
  2. Create the directory: (you can change this option)/var/www/bmon-html
  3. Create the file /etc/init/bmon.conf  with follow content:
    description "bmon bandwidth monitoring process"
    
    start on (local-filesystems and net-device-up and runlevel [2345])
    stop on runlevel [016]
    
    exec screen -d -m -S bmon bmon -O html:path=/var/www/bmon-html
  4. Start the new service:sudo service bmon start

     

  5. Then the web page  http://localhost/bmon-html/ will be availbale and updated constantly.

Enjoy!

 

A better SHOW TABLE STATUS

From command line we have the entire MySQL server on hands (if we have privileges too of course) but we don’t have a overall overview, at this point the show table status command is every useful, or not?.

This is what we get when run show table status in a standard 80×25 terminal screen:

We can maximize the terminal window and decrease font size, but not all the time we need that lots of info. Some time ago I develop a stored procedure to get a global overview including functions and stored procedures. The result is pretty comprehensible:

call tools.sp_status(database());
+----------------------------+--------+-------+---------+-----------------+
| Table Name                 | Engine | Rows  | Size    | Collation       |
+----------------------------+--------+-------+---------+-----------------+
| actor                      | InnoDB | 200   | 0.03 Mb | utf8_general_ci |
| actor_info                 | [VIEW] | -     | -       | -               |
| address                    | InnoDB | 589   | 0.09 Mb | utf8_general_ci |
| category                   | InnoDB | 16    | 0.02 Mb | utf8_general_ci |
| city                       | InnoDB | 427   | 0.06 Mb | utf8_general_ci |
| country                    | InnoDB | 109   | 0.02 Mb | utf8_general_ci |
| customer                   | InnoDB | 541   | 0.12 Mb | utf8_general_ci |
| customer_list              | [VIEW] | -     | -       | -               |
| film                       | InnoDB | 1131  | 0.27 Mb | utf8_general_ci |
| film_actor                 | InnoDB | 5143  | 0.27 Mb | utf8_general_ci |
| film_category              | InnoDB | 316   | 0.08 Mb | utf8_general_ci |
| film_list                  | [VIEW] | -     | -       | -               |
| film_text                  | MyISAM | 1000  | 0.31 Mb | utf8_general_ci |
| inventory                  | InnoDB | 4673  | 0.36 Mb | utf8_general_ci |
| language                   | InnoDB | 6     | 0.02 Mb | utf8_general_ci |
| nicer_but_slower_film_list | [VIEW] | -     | -       | -               |
| payment                    | InnoDB | 15422 | 2.12 Mb | utf8_general_ci |
| rental                     | InnoDB | 15609 | 2.72 Mb | utf8_general_ci |
| sales_by_film_category     | [VIEW] | -     | -       | -               |
| sales_by_store             | [VIEW] | -     | -       | -               |
| staff                      | InnoDB | 1     | 0.09 Mb | utf8_general_ci |
| staff_list                 | [VIEW] | -     | -       | -               |
| store                      | InnoDB | 2     | 0.05 Mb | utf8_general_ci |
+----------------------------+--------+-------+---------+-----------------+
23 rows in set (0.04 sec)
 
+----------------------------+-----------+---------------------+
| Routine Name               | Type      | Comment             |
+----------------------------+-----------+---------------------+
| get_customer_balance       | FUNCTION  |                     |
| inventory_held_by_customer | FUNCTION  |                     |
| inventory_in_stock         | FUNCTION  |                     |
| film_in_stock              | PROCEDURE |                     |
| film_not_in_stock          | PROCEDURE |                     |
| rewards_report             | PROCEDURE |                     |
| customer_create_date       | TRIGGER   | On INSERT: customer |
| del_film                   | TRIGGER   | On DELETE: film     |
| ins_film                   | TRIGGER   | On INSERT: film     |
| payment_date               | TRIGGER   | On INSERT: payment  |
| rental_date                | TRIGGER   | On INSERT: rental   |
| upd_film                   | TRIGGER   | On UPDATE: film     |
+----------------------------+-----------+---------------------+
12 rows in set (0.04 sec)
 
Query OK, 0 rows affected (0.04 sec)

There is the procedure source code:

DELIMITER $$
DROP PROCEDURE IF EXISTS `tools`.`sp_status` $$
CREATE PROCEDURE `tools`.`sp_status`(dbname VARCHAR(50))
BEGIN 
-- Obtaining tables and views
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     ENGINE AS `Engine`,
     TABLE_ROWS AS `Rows`,
     CONCAT(
        (FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2))
        , ' Mb')
       AS `Size`,
     TABLE_COLLATION AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname
          AND TABLES.TABLE_TYPE = 'BASE TABLE'
)
UNION
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     '[VIEW]' AS `Engine`,
     '-' AS `Rows`,
     '-' `Size`,
     '-' AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname 
          AND TABLES.TABLE_TYPE = 'VIEW'
)
ORDER BY 1;
-- Obtaining functions, procedures and triggers
(
    SELECT ROUTINE_NAME AS `Routine Name`, 
     ROUTINE_TYPE AS `Type`,
     '' AS `Comment`
    FROM information_schema.ROUTINES
    WHERE ROUTINE_SCHEMA = dbname
    ORDER BY ROUTINES.ROUTINE_TYPE, ROUTINES.ROUTINE_NAME
)
UNION
(
    SELECT TRIGGER_NAME,'TRIGGER' AS `Type`, 
    concat('On ',EVENT_MANIPULATION,': ',EVENT_OBJECT_TABLE) AS `Comment`
    FROM information_schema.TRIGGERS
    WHERE EVENT_OBJECT_SCHEMA = dbname
)
ORDER BY 2,1;
END$$
DELIMITER ;

To use in your place you must call as:

mysql> call tools.sp_status(database());

Note the stored procedure has created in tools database (you can use another db), the goal of this is to call that useful procedure from any database, and it receives the name of database as parameter because is not possible obtain the current database from inside of stored procedure.

I published that code on  MySQL custom show table status entry at forge.mysql.com.

Any ideas to improving the source code to make it more useful?

 

`

Creating sequences in MySQL

Those who have used PostgresSQL usually gets lazy when write the SQL code to auto-numeric fields, but we miss that feature when we are in a MySQL database.

As we know, MySQL have auto-increment fields, that are very useful too, but we miss that feature when we are in a PostgreSQL database.

I will try to explain how to simulate sequences like PostgreSQL in MySQL. But in what situations we would need sequences in MySQL? some ideas:

  • When we need more than one auto-numeric field in the same table.
  • When we need a global counter to use in more than one table, procedures, etc.
  • I can’t think more situations, but I’m sure at some point we can found one.

OK let’s start:

We need to create a table to store the current sequence value (if this table is created inside mysql database  is not bad idea)

create table _sequence
(
	seq_name varchar(50) not null primary key,
	seq_val int unsigned not null
);

Now we need a function to get the next sequence value, including these cases:

    • Create a sequence name if the sequence name don’t exists (is not necessary get complicated).
    • Increase the sequence number each function call.

There is the function  getNextSeq it receive sequence name as parameter:

delimiter //
drop function if exists getNextSeq//
 
create function getNextSeq(sSeqName varchar(50)) returns int unsigned
begin
    declare nLast_val int;
 
    set nLast_val =  (select seq_val
                          from _sequence
                          where seq_name = sSeqName);
    if nLast_val is null then
        set nLast_val = 1;
        insert into _sequence (seq_name,seq_val)
        values (sSeqName,nLast_Val);
    else
        set nLast_val = nLast_val + 1;
        update _sequence set seq_val = nLast_val
        where seq_name = sSeqName;
    end if;
 
    return nLast_val;
end//
 
delimiter ;

 

The next step is to create  (to maintain purposes) a stored procedure to modify the current sequence value:

drop procedure if exists sp_setSeqVal//
 
create procedure sp_setSeqVal(sSeqName varchar(50), nVal int unsigned)
begin
    if (select count(*) from _sequence where seq_name = sSeqName) = 0 then
        insert into _sequence (seq_name,seq_val)
        values (sSeqName,nVal);
    else
        update _sequence set seq_val = nVal
        where seq_name = sSeqName;
    end if;
end//
 
delimiter ;

 

That’s it!, just one more thing: testing. Creating a table with two auto-numeric fields using simulated sequences:

create table multiple_autonums
(
   id int not null primary key auto_increment,
   seq_1 int, -- sequence 1
   seq_2 int, -- sequence 2
   unique(seq_1),
   unique(seq_2)
);

We must create a before-insert trigger on table:

delimiter //
drop trigger if exists multiple_autonums_ai//
 
create trigger multiple_autonums_bi before insert on multiple_autonums
for each row
begin
   set new.seq_1 = getNextSeq("seq_1");
   set new.seq_2 = getNextSeq("seq_2");
end//
 
delimiter ;

 

Run some tests:

-- inserting new values.
insert into multiple_autonums (id) values (null),(null),(null);
 
-- getting the inserted values
select * from multiple_autonums;
+----+-------+-------+
| id | seq_1 | seq_2 |
+----+-------+-------+
|  1 |     1 |     1 |
|  2 |     2 |     2 |
|  3 |     3 |     3 |
+----+-------+-------+
3 rows in set (0.00 sec)
 
-- Changing sequence current values
 
call sp_setSeqVal("seq_1",47);
call sp_setSeqVal("seq_2",9786);
 
-- Inserting new values
insert into multiple_autonums (id) values (null),(null),(null);
 
-- Seeing what happen.
select * from multiple_autonums;
+----+-------+-------+
| id | seq_1 | seq_2 |
+----+-------+-------+
|  1 |     1 |     1 |
|  2 |     2 |     2 |
|  3 |     3 |     3 |
|  4 |    48 |  9787 |
|  5 |    49 |  9788 |
|  6 |    50 |  9789 |
+----+-------+-------+

 

What do you think?

This simulated sequence don’t consider some stuffs but it works and can be helpful. Even you can modify the function to make more complex generation of next value.

Hope you find it useful.

Hello world!

Welcome to my recently created blog, in this place I will publish my english entries, I have a spanish blog in www.latindevelopers.com/ivancp/ but I don’t like mix english posts inside a spanish blog.

Many of my posts may be are interesting in english too, this is the main reason.

Here’s a list of things I’m looking forward to:

  • Publish my open source and not open source experience.
  • Publish my personal stuffs.
  • Publish local stuffs unknown in english language.

Well, I see you in the next posts.

Go back to top