Broad Network


Compressed Data Values with PurePerl MySQL API

Using the PurePerl MySQL API– Part 11

Foreword: In this part of the series, I explain how you can use MySQL to compress a long value (kilo and mega-bytes) and insert into a table; I also explain the data types to use for the table.

By: Chrysanthus Date Published: 9 Nov 2016

Introduction

This is part 11 of my series, Using the PurePerl MySQL API. In this part of the series, I explain how you can use MySQL to compress a long value (kilo and mega-bytes) and insert into a table; I also explain the data types to use for the table. The compressed value (string) is always smaller than the uncompressed value (string), except for small uncompressed values.

Data Types
In this scheme, compression takes place at the server (not at the client API). The table data type to use for a compressed column is BINARY or VARBINARY or BLOB. A Western European Character (e.g. A) occupies a space in memory of one byte. Each of these data types stores bytes (not really characters) in a table cell.

BINARY
With the binary data type, the number of bytes in each cell for a column are the same. Spaces are padded to make the length of the byte strings, the same, in all the cells of the column.

VARBINARY
VARBINARY is similar to BINARY; however, with VARBINARY a string of bytes in a cell can be of any length up to the maximum, indicated; there is no padding with spaces. So, in a column of VARBINARY, the string of bytes in the different cells are of different lengths.

BLOB
The BLOB type is similar to VARBINARY, but it is for large value, such as a large image or a video file. The BLOB type is further divided into, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

COMPRESS() and UNCOMPRESS()
The function for MySQL server to compress value, before saving, is COMPRESS(); the reverse function is, UNCOMPRESS(). The following SQL statement will compress 'any string' before inserting into the table.

    INSERT INTO Pet (Comp) VALUES (COMPRESS('any string'));

The following SQL statement will select all the cell values from the column, Comp and uncompress them (before outputting).

    SELECT UNCOMPRESS(Comp) from Pet;

Illustration
Alter the pet table in the PetStore database with the following code:

#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;

        if (!Mysql::connect("root", "azemawo", "localhost", 3306))
            {
                print "$Mysql::Error_msg";
            }
        else
            {
                if (Mysql::select_db("PetStore") != 1)
                    {
                        print $Mysql::Error_msg, "\n";
                    }
                else   
                    {
                        if (!Mysql::query("ALTER TABLE Pet ADD Comp VARBINARY(100)"))
                            {
                                print $Mysql::Error_msg, "\n";
                            }
                   }
            }

         Mysql::close();

The following code will compress 'any string' before inserting into the table, in a cell in the column, Comp:

#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;

        if (!Mysql::connect("root", "azemawo", "localhost", 3306))
            {
                print "$Mysql::Error_msg";
            }
        else
            {
                if (Mysql::select_db("PetStore") != 1)
                    {
                        print $Mysql::Error_msg, "\n";
                    }
                else   
                    {
                        if (!Mysql::query("INSERT INTO Pet (Comp) VALUES (COMPRESS('any string'))"))
                            {
                                print $Mysql::Error_msg, "\n";
                            }
                   }
            }

         Mysql::close();

The following code will select all the cell values from the column, Comp and uncompress them.

#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;

        if (!Mysql::connect("root", "azemawo", "localhost", 3306))
            {
                print "$Mysql::Error_msg";
            }
        else
            {
                if (Mysql::select_db("PetStore") != 1)
                    {
                        print $Mysql::Error_msg, "\n";
                    }
                else   
                    {
                        if (!Mysql::query("SELECT UNCOMPRESS(Comp) from Pet"))
                            {
                                print $Mysql::Error_msg, "\n";
                            }
                        else
                            {
                                my $numFields = @Mysql::Fields;

                                for (my $i=0; $i<$Mysql::No_of_Rows; ++$i)
                                    {
                                        for (my $j=0; $j<$numFields; ++$j)
                                            {
                                                my $filedName = $Mysql::Fields[$j][0];
                                                print $Mysql::Result[$i]{$filedName};
                                                print  ', ' if ($j != ($numFields-1));
                                            }  
                                        print "\n";     
                                    }
                            }
                   }
            }

         Mysql::close();

That is it for this part of the series. We stop here and continue in the next part.

Chrys

More Related Links
Perl Mailsend
PurePerl MySQL API
Perl Course - Professional and Advanced
Major in Website Design
Web Development Course
Producing a Pure Perl Library
MySQL Course

BACK

Comments

Become the Writer's Follower
Send the Writer a Message