FAST Real-time compression for MySQL. User Defined Function for MySQL that adds real-time data compression functions to the mysqld database server. These functions allow you to compress and uncompress data on the fly from the database. With the exception of adding these custom commands to the SQL syntax, compression and decompression are virtually transparent to the database client. http://www.servangle.net/udf_lzo/
MySQL 에서 주어진 자료를 compression 시키는 함수이다.
---------------------------------------------------------------
/* Copyright (C) 2003 John R. Smith, Jr.
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
/************************************************************************* * Mysql user-defined-functions for sqz_lzo() and exp_lzo() * * Written 08/02/2003, John R. Smith, Jr. * * This software is public domain and comes with NO WARRANTY of any kind. * * This code was written using: * - udf_example1.cc as the template. * udf_example1.cc ships with the mysqld source code (www.mysql.org). * - The LZO documentation is found at http://www.oberhumer.com/opensource/lzo/ * * LIMITATIONS: * The only known limitation is that the largest uncompressed size possible, * by default, is 12 times the compressed data's size, no more than a 12 to 1 * compression ratio. This is probably reasonable for most users. * This can be changed via the MAX_COMPRESSION_RATIO define. * WARNING!! If you under-compensate here, you risk a serious buffer overflow! * * USAGE: * sqz_lzo(string) * - Compresses string with LZO. string is typically a quoted constant, * or a column containing text or binary data. * exp_lzo(string) * - Decompresses string with LZO. string is typically a column containing LZO compressed data. * * Note: Do not try to store LZO compressed data in char or varchar columns. * Those column types remove trailing spaces from the data, which ruins * your data when it is compressed and the compressed data ends with * spaces, which happens quite often. You must use one of the BLOB * or TEXT types to store compressed data. * * COMIPLE: * I compiled, in FreeBSD, this way: * gcc -shared -Wall -O -I /usr/local/include -I /usr/local/include/mysql -L /usr/local/lib/mysql -lmysqlclient -L /usr/local/lib -llzo udf_lzo.cc -o libudf_lzo.so * Paths will vary on platforms. Just be sure to tell gcc where to find * the share libs libmysqlclient.so, liblzo.so, and the Header files. * You can try the included Makefile. * * INSTALL: * Copy the object file to your lib directory and refresh the linker cache: * cp libudf_lzo.so /usr/lib ; ldconfig * * MYSQL: * Syntax for the new commands are: * create function <function_name> returns {string|real|integer} soname <name_of_shared_library> * drop function <function_name> * * Load the functions from MySQL command line client: * CREATE FUNCTION sqz_lzo RETURNS STRING SONAME "udf_lzo.so"; * CREATE FUNCTION exp_lzo RETURNS STRING SONAME "udf_lzo.so"; * * After this the functions will work exactly like native MySQL functions. * Functions should be created only once. * * The functions can be deleted by: * * DROP FUNCTION sqz_lzo; * DROP FUNCTION exp_lzo; * * The CREATE FUNCTION and DROP FUNCTION update the func@mysql table. All * Active function will be reloaded on every restart of server * (if --skip-grant-tables is not given) * * **************************************************************************/
#ifdef STANDARD #include <stdio.h> #ifdef __WIN__ typedef unsigned __int64 ulonglong; /* Microsofts 64 bit types */ typedef __int64 longlong; #else typedef unsigned long long ulonglong; typedef long long longlong; #endif /*__WIN__*/ #else #include <my_global.h> #include <my_sys.h> #include <m_string.h> #endif #include <mysql.h> #include <m_ctype.h>
#include <lzo1x.h> /* LZO compression library */
// Uncomment next line to get more debug messages. //#define UDF_DEBUG /* Extra verbosity to stderr */
// Uncomment the next line to favor speed over compression. // #define LZO_SPEED /* Choose speed over compression */
// Set initial working memory size #define INIT_BUF (1024 * 128) /* 128K Initial Memory buffer for functions */
// Over compensate uncompression memory requirements to prevent excessive buffer resizing. #define MAX_COMPRESSION_RATIO 5 /* LZO avg decompression guess */
#define UDF_VERSION 1.0.0
#ifdef HAVE_DLOPEN
/* These must be right or mysqld will not find the symbol! */
extern "C" { my_bool sqz_lzo_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void sqz_lzo_deinit(UDF_INIT *initid); char *sqz_lzo(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);
my_bool exp_lzo_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void exp_lzo_deinit(UDF_INIT *initid); char *exp_lzo(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error); }
/************************************************************************* ** Example of ???_init function ** Arguments: ** initid Points to a structure that the init function should fill. ** This argument is given to all other functions. ** my_bool maybe_null 1 if function can return NULL ** Default value is 1 if any of the arguments ** is declared maybe_null. ** unsigned int decimals Number of decimals. ** Default value is max decimals in any of the ** arguments. ** unsigned int max_length Length of string result. ** The default value for integer functions is 21 ** The default value for real functions is 13+ ** default number of decimals. ** The default value for string functions is ** the longest string argument. ** char *ptr; A pointer that the function can use. ** ** args Points to a structure which contains: ** unsigned int arg_count Number of arguments ** enum Item_result *arg_type Types for each argument. ** Types are STRING_RESULT, REAL_RESULT ** and INT_RESULT. ** char **args Pointer to constant arguments. ** Contains 0 for not constant argument. ** unsigned long *lengths; max string length for each argument ** char *maybe_null Information of which arguments ** may be NULL ** ** message Error message that should be passed to the user on fail. ** The message buffer is MYSQL_ERRMSG_SIZE big, but one should ** try to keep the error message less than 80 bytes long! ** ** This function should return 1 if something goes wrong. In this case ** message should contain something usefull! **************************************************************************/
/**************** sqz_lzo_init ****************/
my_bool sqz_lzo_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
// *** Init the LZO engine if (lzo_init() != LZO_E_OK) { strcpy(message,"ERROR: lzo_init failed !!!"); return 1; }
if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT) { strcpy(message,"WARNING: Wrong assignment to sqz_lzo()"); return 1; }
// Allocating a fixed amount of memory. We can grow it later as needed. initid->ptr = (char *) malloc(INIT_BUF); initid->max_length=INIT_BUF; #ifdef UDF_DEBUG fprintf(stderr, "[%u] sqz_lzo_init() allocating memory buffer %u bytes\n", (int)initid, INIT_BUF); #endif /* UDF_DEBUG */ return 0; }
/**************** exp_lzo_init ****************/
my_bool exp_lzo_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
// *** Init the LZO engine if (lzo_init() != LZO_E_OK) { strcpy(message,"ERROR: lzo_init failed !!!"); return 1; }
if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT) { strcpy(message,"WARNING: Wrong assignment to exp_lzo()"); return 1; }
// Allocating a fixed amount of memory. We can grow it later as needed. initid->ptr = (char *) malloc(INIT_BUF); initid->max_length=INIT_BUF; #ifdef UDF_DEBUG fprintf(stderr, "[%u] exp_lzo_init() allocating memory buffer %u bytes\n", (int)initid, INIT_BUF); #endif /* UDF_DEBUG */
return 0; }
/**************************************************************************** ** ???_deinit function. This should free all resources allocated by ** this function. ** Arguments: ** initid Return value from xxxx_init ****************************************************************************/
/**************** sqz_lzo_deinit ****************/
void sqz_lzo_deinit (UDF_INIT *initid) { #ifdef UDF_DEBUG fprintf(stderr, "[%u] sqz_lzo_deinit() freeing memory buffer: %u bytes\n", (uint)initid, (uint)initid->max_length); #endif /* UDF_DEBUG */ free(initid->ptr); }
/**************** exp_lzo_deinit ****************/
void exp_lzo_deinit (UDF_INIT *initid) { #ifdef UDF_DEBUG fprintf(stderr, "[%u] exp_lzo_deinit() freeing memory buffer: %u bytes\n", (uint)initid, (uint)initid->max_length); #endif /* UDF_DEBUG */ free(initid->ptr); }
/*************************************************************************** ** UDF string function. ** Arguments: ** initid Structure filled by xxx_init ** args The same structure as to xxx_init. This structure ** contains values for all parameters. ** Note that the functions MUST check and convert all ** to the type it wants! Null values are represented by ** a NULL pointer ** result Possible buffer to save result. At least 255 byte long. ** length Pointer to length of the above buffer. In this the function ** should save the result length ** is_null If the result is null, one should store 1 here. ** error If something goes fatally wrong one should store 1 here. ** ** This function should return a pointer to the result string. ** Normally this is 'result' but may also be an alloced string. ***************************************************************************/
/**************** sqz_lzo ****************/
char *sqz_lzo(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error) { int r; uint required_mem; lzo_byte *in; lzo_byte *out; lzo_byte *wrkmem; lzo_uint in_len; lzo_uint out_len;
// Set input in = (lzo_bytep)args->args[0]; in_len = (lzo_uint)args->lengths[0];
// Check for Null argument or empty string if ((!in) || (args->lengths[0] == 0)) { *is_null = 1; fprintf(stderr, "WARNING: null or empty string.\n"); return 0; }
// Set output out = (lzo_bytep) initid->ptr;
// *** Allocate work memory #ifdef LZO_SPEED wrkmem = (lzo_byte *)malloc(LZO1X_1_MEM_COMPRESS); // More speed #else /* LZO_SPEED */ wrkmem = (lzo_byte *)malloc(LZO1X_999_MEM_COMPRESS); // More compression #endif /* LZO_SPEED */ if (wrkmem == NULL) { *error = 1; fprintf(stderr, "ERROR: lzo failed to allocate dynamic memory !!!\n"); return 0; }
// Set estimated required memory for compression (plus a little more for non-compressable data) required_mem=in_len + in_len / 64 + 16 + 3;
// Did we reserve too little memory? if (required_mem > initid->max_length) { // Get more memory #ifdef UDF_DEBUG fprintf(stderr, "[%u] sqz_lzo() resizing memory buffer: %u -> %u bytes\n", (uint)initid, (uint)initid->max_length, required_mem); #endif /* UDF_DEBUG */ free(initid->ptr); initid->ptr = (char *) malloc(required_mem); initid->max_length=required_mem; }
// Set output out = (lzo_bytep) initid->ptr;
// *** compress #ifdef LZO_SPEED r = lzo1x_1_compress(in,in_len,out,&out_len,wrkmem); // More speed #else /* LZO_SPEED */ r = lzo1x_999_compress(in,in_len,out,&out_len,wrkmem); // More compression #endif /* LZO_SPEED */ if (r != LZO_E_OK ) { *error = 1; fprintf(stderr, "ERROR: lzo internal error - compression failed !!!\n"); return 0; }
// *** Free work memory free(wrkmem);
// *** get results result = (char *)out; *length = (unsigned long)out_len;
// Debuging // Useful to get output from MySQL log file. // You will want to comment this out for production release. // tail -f <mysql.log> #ifdef UDF_DEBUG fprintf(stderr, "[%u] sqz_lzo() compressed %u bytes into %u bytes\n", (uint)initid, in_len, out_len); #endif /* UDF_DEBUG */
return result; }
/**************** exp_lzo ****************/
char *exp_lzo(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error) { uint required_mem; int r; int comp_ratio = MAX_COMPRESSION_RATIO; lzo_byte *in; lzo_byte *out; lzo_uint in_len; lzo_uint out_len;
// Set input in = (lzo_bytep)args->args[0]; in_len = (lzo_uint)args->lengths[0];
// Check for Null argument or empty string if ((!in) || (args->lengths[0] == 0)) { *is_null = 1; fprintf(stderr, "WARNING: null or empty string.\n"); return 0; }
// Loop until we successfully decompress or get an error. do {
out = (lzo_bytep) initid->ptr; // Set output pointer out_len = initid->max_length; // set decompress safe limit
// *** decompress r = lzo1x_decompress_safe(in,in_len,out,&out_len,NULL);
// *** Check for major errors if (r == LZO_E_ERROR) { *error = 1; fprintf(stderr, "[%u] exp_lzo() decompress failed. [Return code: %d]\n", (uint)initid, r); return 0; }
// *** Corrupt data if ((r == LZO_E_EOF_NOT_FOUND) || (r == LZO_E_LOOKBEHIND_OVERRUN )) { *is_null = 1; fprintf(stderr, "[%u] exp_lzo() decompress failed. Currupt or not LZO compressed data. [Return code: %d]\n", (uint)initid, r); return 0; }
// *** Check for too little decompress memory if (r == LZO_E_OUTPUT_OVERRUN) { // *** Check if ratio has grown too high if (comp_ratio > 100) { *is_null = 1; fprintf(stderr, "[%u] exp_lzo() decompress ratio (%d) too high. Aborting...\n", (uint)initid, comp_ratio); return 0; }
// Get more memory required_mem=in_len * comp_ratio; #ifdef UDF_DEBUG fprintf(stderr, "[%u] exp_lzo() resizing memory buffer: %u -> %u bytes\n", (uint)initid, (uint)initid->max_length, required_mem); #endif /* UDF_DEBUG */ free(initid->ptr); initid->ptr = (char *) malloc(required_mem); initid->max_length=required_mem;
// increase ratio for next time comp_ratio++; }
} while (r != LZO_E_OK);
// *** get results result = (char *)out; *length = (unsigned long)out_len;
// Debuging // Useful to get output from MySQL log file. // You will want to comment this out for production release. // tail -f <mysql.log> #ifdef UDF_DEBUG fprintf(stderr, "[%u] exp_lzo() uncompressed %u bytes into %u bytes\n", (uint)initid, in_len, out_len); #endif /* UDF_DEBUG */
return result; }
/*************************************************************************** ** UDF double function. ** Arguments: ** initid Structure filled by xxx_init ** args The same structure as to xxx_init. This structure ** contains values for all parameters. ** Note that the functions MUST check and convert all ** to the type it wants! Null values are represented by ** a NULL pointer ** is_null If the result is null, one should store 1 here. ** error If something goes fatally wrong one should store 1 here. ** ** This function should return the result. ***************************************************************************/
/*************************************************************************** ** UDF long long function. ** Arguments: ** initid Return value from xxxx_init ** args The same structure as to xxx_init. This structure ** contains values for all parameters. ** Note that the functions MUST check and convert all ** to the type it wants! Null values are represented by ** a NULL pointer ** is_null If the result is null, one should store 1 here. ** error If something goes fatally wrong one should store 1 here. ** ** This function should return the result as a long long ***************************************************************************/
#endif /* HAVE_DLOPEN */
========================================================
Linux 용 Make file
========================================================
CC=gcc CFLAGS=-shared -Wall -O -D__GNUC__ IFLAGS=-I /usr/include -I /usr/include/mysql/ LFLAGS=-L /usr/lib/mysql -lmysqlclient -L /usr/lib -llzo VERSION=0.1
SHELL=/bin/sh NOOP = $(SHELL) -c true RM_RF = rm -rf PREOP = @$(NOOP) POSTOP = @$(NOOP) TO_UNIX = @$(NOOP)
SRC=udf_lzo.cc OBJ=libudf_lzo.so
all: $(CC) $(CFLAGS) $(IFLAGS) $(LFLAGS) $(SRC) -o $(OBJ)
install: all cp $(OBJ) /usr/lib ldconfig
load : install echo "CREATE FUNCTION shrink RETURNS STRING SONAME \"$(OBJ)\"" | mysql -f -u root -p echo "CREATE FUNCTION stretch RETURNS STRING SONAME \"$(OBJ)\"" | mysql -f -u root -p
drop: echo "DROP FUNCTION shrink;" | mysql -f -u root -p echo "DROP FUNCTION stretch;" | mysql -f -u root -p
clean: -rm -f *.so
|