MySQL cache management subsystem. More...

#include "playlist.h"
#include "cache-mysql.h"
#include <libgen.h>
#include <string.h>
#include <mysql.h>

Data Structures
struct	mysql_dir
	hand crafted "dirent" applied to our mysql data structure More...

Macros
#define	TABLE_FILES "musicindexfiles"
	table name for files
#define	TABLE_DIRS "musicindexdirs"
	table name for directories
#define	TABLE_FORMAT "musicindexformat"
	table name for cache global format
#define	TABLE_FORMAT_ID 1
	table format identifier.
#define	SQL_SMAX_UPD 64
	max length for user/pass/db
#define	SQL_SMAX_H 256
	max length for host
#define	SUBSTRINGIFY(x) STRINGIFY(x)
#define	STRINGIFY(x) #x
#define	CACHE_VERS 3
#define	AINC_OVFLERR 1062
	AUTO_INCREMENT overflow error code: 1062 == duplicate entry.
#define	SQL_CDATAF
	Order of the fields for the query, to be in sync with mysql_cache_new_ent(), cvers first, mtime 2nd.
#define	SQL_CDATAN 15
	Number of fields in CDATAF.
#define	CA_OK 0
#define	CA_CREATE 1
#define	CA_STALE 2
#define	CA_NOTREADY 3
#define	CA_FATAL -1

Typedefs
typedef struct mysql_dir	mysql_dir
	hand crafted "dirent" applied to our mysql data structure

Functions
static mu_ent *	mysql_cache_new_ent (apr_pool_t *pool, MYSQL_ROW mysql_row)
	Makes a new mu_ent from the contents of the provided mysql_row.
static int	mysql_cache_init (server_rec s, const char const setup_string)
	Initializes sql cache subsystem.
static void	mysql_cache_trunc_tables (request_rec r, MYSQL mysql)
	Truncates files and dirs tables.
static int	mysql_cache_make_dir (request_rec r, const char const dirpath, const unsigned long timestamp, MYSQL *mysql)
	Creates cache subdirectories.
static int	mysql_cache_remove_dir (request_rec r, const char const curdir, MYSQL *mysql)
	Removes cache subdirectories.
static int	mysql_cache_check_dir (request_rec r, const char const path, MYSQL *mysql)
	Checks if a directory already exists in the cache.
static mu_ent *	mysql_cache_make_entry (request_rec r, apr_pool_t pool, FILE const in, const char const filename)
	Fills in the information fields about a music file from the cache.
static void	mysql_cache_write (request_rec r, const mu_ent const p, const char *const filename)
	Creates and writes cache information.
static mysql_dir *	mysql_cache_dircontents (request_rec r, apr_pool_t pool, mu_pack const pack, const char const filename, const char const uri, MYSQL mysql, unsigned long soptions)
	Fetch the content of a whole directory in the cache.
static void *	mysql_cache_opendir (request_rec r, mu_pack const pack, const char const filename, const char const uri, unsigned long soptions)
	A nifty wrapper.
static const char *	mysql_cache_readdir (void *dir)
	Returns directory contents to be processed in make_music_entry() a la readdir().
static void	mysql_cache_prologue (request_rec *r)
	Initializes the database connection.
static void	mysql_cache_epilogue (request_rec *r)
	Closes the database connection.
int	cache_mysql_setup (cmd_parms cmd, const char const setup_string, mu_config *const conf)

Variables
struct {
char user [SQL_SMAX_UPD]
	mysql db user
char pass [SQL_SMAX_UPD]
	mysql db user's pass
char db [SQL_SMAX_UPD]
	mysql db
char host [SQL_SMAX_H]
	mysql db's host
short ready
}	mysql_params
	mysql connexion settings
static const cache_backend	cache_backend_mysql

Detailed Description

MySQL cache management subsystem.

This cache backend is expected to be impressively efficient. Almost no locking is necessary, since MySQL has its own locking mechanism. Locking is only necessary where things could go wrong if some data were to change between two requests; typically if mysql_cache_remove_dir() happens between two requests. We try to let MySQL do most of the computation, passing it or retrieving end results for complex queries.

This cache backend should be the primary choice for an evolved cache system where it would be possible to search the cache directly (almost) without looking at the original files, and where it would be possible to fetch directory listings without looking at the original directory either.

The SQL cache backend relies on the use of the following tables:

table files: |id|pid|filetype|flags|track|posn|cvers|date|freq|length|bitrate|size|mtime|album|artist|title|genre|filename|
table dirs: |id|timestamp|fullpath|
table format: |formatid|

The PID in the 'files' table will match the 'id' field of one (and only one) entry in the 'dirs' table, to be able to link a cache entry with its corresponding location on the filesystem. The FILENAME field in the 'files' table will store the basename of the full path to the original file, so that by concatenating the corresponding 'dirs' entry with that field we can reconstruct the full path to the file. The TIMESTAMP field in the 'dirs' table will store a recorded mtime from the original directory, for cache up-to-date-ness checking.

Todo:: clean error handler when data corruption happens.

Author: Thibaut Varene

Version

Revision:: 1011

Date: 2005,2007,2009-2010,2012

Definition in file cache-mysql.c.

Macro Definition Documentation

#define AINC_OVFLERR 1062

AUTO_INCREMENT overflow error code: 1062 == duplicate entry.

Definition at line 75 of file cache-mysql.c.

Referenced by mysql_cache_make_dir(), and mysql_cache_write().

#define CA_CREATE 1

Definition at line 96 of file cache-mysql.c.

Referenced by mysql_cache_check_dir(), mysql_cache_opendir(), and mysql_cache_write().

#define CA_FATAL -1

Definition at line 99 of file cache-mysql.c.

Referenced by mysql_cache_check_dir(), mysql_cache_make_dir(), mysql_cache_opendir(), and mysql_cache_remove_dir().

#define CA_NOTREADY 3

Definition at line 98 of file cache-mysql.c.

Referenced by mysql_cache_check_dir(), and mysql_cache_opendir().

#define CA_OK 0

Definition at line 95 of file cache-mysql.c.

Referenced by mysql_cache_check_dir(), mysql_cache_make_dir(), mysql_cache_opendir(), and mysql_cache_remove_dir().

#define CA_STALE 2

Definition at line 97 of file cache-mysql.c.

Referenced by mysql_cache_check_dir(), and mysql_cache_opendir().

#define CACHE_VERS 3

Definition at line 74 of file cache-mysql.c.

Referenced by mysql_cache_dircontents(), mysql_cache_make_entry(), and mysql_cache_write().

#define SQL_CDATAF

Value:

"cvers,mtime,filetype,flags,track,posn," \
                "date,freq,length,bitrate,size," \
                "album,artist,title,genre"

Order of the fields for the query, to be in sync with mysql_cache_new_ent(), cvers first, mtime 2nd.

Definition at line 89 of file cache-mysql.c.

Referenced by mysql_cache_dircontents(), and mysql_cache_make_entry().

#define SQL_CDATAN 15

Number of fields in CDATAF.

Definition at line 93 of file cache-mysql.c.

Referenced by mysql_cache_dircontents().

#define SQL_SMAX_H 256

max length for host

Definition at line 71 of file cache-mysql.c.

Referenced by mysql_cache_init().

#define SQL_SMAX_UPD 64

max length for user/pass/db

Definition at line 70 of file cache-mysql.c.

Referenced by mysql_cache_init().

#define STRINGIFY ( x ) #x

Definition at line 73 of file cache-mysql.c.

#define SUBSTRINGIFY ( x ) STRINGIFY(x)

Definition at line 72 of file cache-mysql.c.

Referenced by mysql_cache_init().

#define TABLE_DIRS "musicindexdirs"

table name for directories

Definition at line 67 of file cache-mysql.c.

Referenced by mysql_cache_check_dir(), mysql_cache_dircontents(), mysql_cache_init(), mysql_cache_make_dir(), mysql_cache_make_entry(), mysql_cache_remove_dir(), mysql_cache_trunc_tables(), and mysql_cache_write().

#define TABLE_FILES "musicindexfiles"

table name for files

Definition at line 66 of file cache-mysql.c.

Referenced by mysql_cache_dircontents(), mysql_cache_init(), mysql_cache_make_entry(), mysql_cache_remove_dir(), mysql_cache_trunc_tables(), and mysql_cache_write().

#define TABLE_FORMAT "musicindexformat"

table name for cache global format

Definition at line 68 of file cache-mysql.c.

Referenced by mysql_cache_init().

#define TABLE_FORMAT_ID 1

table format identifier.

If this changes, drop the tables

Definition at line 69 of file cache-mysql.c.

Referenced by mysql_cache_init().

Typedef Documentation

typedef struct mysql_dir mysql_dir

hand crafted "dirent" applied to our mysql data structure

Function Documentation

int cache_mysql_setup	(	cmd_parms *	cmd,
		const char *const	setup_string,
		mu_config *const	conf
	)

Definition at line 1252 of file cache-mysql.c.

References mu_config::cache, cache_backend_mysql, mu_config::cache_setup, and mysql_cache_init().

static int mysql_cache_check_dir	(	request_rec *	r,
		const char *const	path,
		MYSQL *	mysql
	)

static

Checks if a directory already exists in the cache.

This function is called when the caller wants to know whether a given directory (as found in the path) exists or not in the cache. If the directory exists, the function returns successfully if the cache is still valid, otherwise it checks whether the cache was not ready or stale and returns accordingly.

Warning: Asserts we're already connected to the db.; Asserts path won't contain trailing slashes.

Parameters

r	Apache request_rec struct to handle log writings.
path	A string containing the full directory path, without trailing '/'.
mysql	mysql handler to communicate with the server.

Returns: CA_OK if dir exists and is up to date, CA_CREATE if dir doesnt't exist, CA_STALE if dir exists but is stale data, CA_NOTREADY if dir exists but doesn't contain any data, CA_FATAL otherwise.

Definition at line 571 of file cache-mysql.c.

References apr_psprintf, CA_CREATE, CA_FATAL, CA_NOTREADY, CA_OK, CA_STALE, mi_rerror, TABLE_DIRS, and unlikely.

Referenced by mysql_cache_opendir(), and mysql_cache_write().

static mysql_dir* mysql_cache_dircontents	(	request_rec *	r,
		apr_pool_t *	pool,
		mu_pack *const	pack,
		const char *const	filename,
		const char *const	uri,
		MYSQL *	mysql,
		unsigned long	soptions
	)

static

Fetch the content of a whole directory in the cache.

This function creates a new list of entries from a cached directory.

It works as follows:

1. select all immediate subdirs (if any) in that dir (using regexp on TABLE_DIRS) and add them to the queue of files that need more processing.
2. select all songs in that dir, new_ent() them if valid or a) trash the cache directory if they're gone or b) requeue them if they've changed.

Warning: Asserts we're already connected to the db.; Asserts filename doesn't contain trailing /.

Parameters

r	Apache request_rec struct to handle log writings.
pool	Pool allocation.
pack	A pack to add new entries to.
filename	current file path.
uri	current server-side uri.
mysql	mysql handler to talk to the server.
soptions	Flags to use for created entries.

Returns: NULL or a chained list of entries needing more processing from the core system (typically, directories if any will be added to that list for further processing/recursion).

Definition at line 919 of file cache-mysql.c.

References apr_palloc, apr_pcalloc, apr_psprintf, apr_pstrcat, apr_pstrdup, CACHE_VERS, EF_INCACHE, mu_ent::file, mu_ent::filename, mu_pack::filenb, mu_ent::flags, mu_pack::fsize, mu_pack::head, likely, MI_CUSTOM, mi_rdebug, mi_rerror, MI_TARBALL, mu_ent::mtime, musicindex_module, mysql_cache_new_ent(), mysql_cache_remove_dir(), mu_ent::next, mu_config::options, P_FLAGS_OPTIONS, mu_config::search, mu_ent::size, SQL_CDATAF, SQL_CDATAN, TABLE_DIRS, TABLE_FILES, unlikely, and mu_ent::uri.

Referenced by mysql_cache_opendir().

static void mysql_cache_epilogue ( request_rec * r )

static

Closes the database connection.

This is the counter part to the prologue(): we need to properly shutdown the database connection that we established there.

Parameters

r	Apache request_rec struct to handle log writings and pool allocation.

Definition at line 1228 of file cache-mysql.c.

References mu_config::cache_setup, musicindex_module, and unlikely.

static int mysql_cache_init	(	server_rec *	s,
		const char *const	setup_string
	)

static

Initializes sql cache subsystem.

The cache_setup variable is expected to contain a string formatted as: "user[:pass]@host/db" All fields are mandatory except 'pass'. Size limitations apply, see SQL_SMAX_UDP and SQL_SMAX_H. We clean up any existing table if they don't look like what we expect.

Todo:: add a table prefix, so that all tables with that prefix will be under module's control

Parameters

s	Apache server_rec struct to handle log writings.
setup_string	configuration string in which the path to the cache root can be found.

Returns: 0 on succes, -1 otherwise.

Definition at line 151 of file cache-mysql.c.

References MAX_FNAME, MAX_GENRE, MAX_PATHNAME, mi_serror, mysql_params, SQL_SMAX_H, SQL_SMAX_UPD, SUBSTRINGIFY, TABLE_DIRS, TABLE_FILES, TABLE_FORMAT, and TABLE_FORMAT_ID.

Referenced by cache_mysql_setup().

static int mysql_cache_make_dir	(	request_rec *	r,
		const char *const	dirpath,
		const unsigned long	timestamp,
		MYSQL *	mysql
	)

static

Creates cache subdirectories.

This subroutine takes care of creating all requested directories and subdirectories if they don't already exist and if possible.

Warning: Asserts we're already connected to the db.

Parameters

r	Apache request_rec struct to handle log writings and pool allocation.
dirpath	A string representing a path to create.
timestamp	the mtime for the directory to be cached.
mysql	mysql handler to communicate with the server

Returns: CA_OK on succes, CA_FATAL otherwise.

Definition at line 440 of file cache-mysql.c.

References AINC_OVFLERR, apr_palloc, apr_psprintf, apr_pstrdup, CA_FATAL, CA_OK, mi_rerror, mysql_cache_trunc_tables(), TABLE_DIRS, and unlikely.

Referenced by mysql_cache_opendir(), and mysql_cache_write().

static mu_ent* mysql_cache_make_entry	(	request_rec *	r,
		apr_pool_t *	pool,
		FILE *const	in,
		const char *const	filename
	)

static

Fills in the information fields about a music file from the cache.

This function reads the tags from the cache db and fills in the struct mu_ent fields accordingly.

It doesn't need to check for mods on the original, since this is handled at the directory level (see mysql_cache_check_dir() and mysql_cache_dircontents()).

Warning: MUST close in file handler on success.

Parameters

r	Apache request_rec struct to handle log writings.
pool	Apache pool
in	Not used (except for closing).
filename	current filename.

Returns: When possible, struct mu_ent correctly set up, file stream closed, NULL otherwise.

Definition at line 647 of file cache-mysql.c.

References apr_psprintf, mu_config::cache_setup, CACHE_VERS, likely, mi_rerror, musicindex_module, mysql_cache_new_ent(), mysql_params, SQL_CDATAF, TABLE_DIRS, TABLE_FILES, and unlikely.

static mu_ent* mysql_cache_new_ent	(	apr_pool_t *	pool,
		MYSQL_ROW	mysql_row
	)

inlinestatic

Makes a new mu_ent from the contents of the provided mysql_row.

Parameters

pool	Allocation pool
mysql_row	The mysql row containing data

Returns: populated mu_ent structure

Definition at line 109 of file cache-mysql.c.

References mu_ent::album, apr_pstrdup, mu_ent::artist, mu_ent::bitrate, mu_ent::date, mu_ent::filetype, mu_ent::flags, mu_ent::freq, mu_ent::genre, mu_ent::length, mu_ent::mtime, NEW_ENT, mu_ent::posn, mu_ent::size, mu_ent::title, mu_ent::track, and unlikely.

Referenced by mysql_cache_dircontents(), and mysql_cache_make_entry().

static void* mysql_cache_opendir	(	request_rec *	r,
		mu_pack *const	pack,
		const char *const	filename,
		const char *const	uri,
		unsigned long	soptions
	)

static

A nifty wrapper.

Under the pretense of opening a given directory, actually check that this directory exists in the cache. If not, create it, or update it if needed. Cache up-to-dateness management is done here, as directory creation can happen elsewhere in the code (in particular in mysql_cache_make_entry()).

Warning: Asserts filename won't contain trailing /.

Todo:: uses MI_QUICKPL, API should be fixed.

Parameters

r	Apache request_rec struct to handle log writings and pool allocation.
pack	A pack to add new entries to.
filename	current file path.
uri	current server-side uri.
soptions	Flags to use for created entries.

Returns: the chained list from mysql_cache_dircontents(), if any, NULL otherwise.

Definition at line 1113 of file cache-mysql.c.

References CA_CREATE, CA_FATAL, CA_NOTREADY, CA_OK, CA_STALE, mu_config::cache_setup, likely, MI_QUICKPL, mi_rdebug, musicindex_module, mysql_cache_check_dir(), mysql_cache_dircontents(), mysql_cache_make_dir(), mysql_cache_remove_dir(), mysql_params, mu_config::options, and unlikely.

static void mysql_cache_prologue ( request_rec * r )

static

Initializes the database connection.

We really only want one database connection max per request.

Parameters

r	Apache request_rec struct to handle log writings and pool allocation.

Definition at line 1191 of file cache-mysql.c.

References mu_config::cache_setup, musicindex_module, mysql_params, and unlikely.

static const char* mysql_cache_readdir ( void * dir )

static

Returns directory contents to be processed in make_music_entry() a la readdir().

Parameters

dir	pointer to the head of the chainlist of elements

Returns: filename

Definition at line 1164 of file cache-mysql.c.

References mysql_dir::d_name, likely, mysql_dir::next, and unlikely.

static int mysql_cache_remove_dir	(	request_rec *	r,
		const char *const	curdir,
		MYSQL *	mysql
	)

static

Removes cache subdirectories.

This subroutine takes care of removing any given directory and its content (recursively) if any, and if possible.

Warning: Asserts we're already connected to the db.

Parameters

r	Apache request_rec struct to handle log writings and pool allocation.
curdir	A string representing the absolute path of the corresponding parent directory on the "original" filesystem.
mysql	mysql handler to communicate with the server.

Returns: CA_OK on succes, CA_FATAL otherwise.

Definition at line 515 of file cache-mysql.c.

References apr_palloc, apr_psprintf, CA_FATAL, CA_OK, mi_rerror, TABLE_DIRS, TABLE_FILES, and unlikely.

Referenced by mysql_cache_dircontents(), and mysql_cache_opendir().

static void mysql_cache_trunc_tables	(	request_rec *	r,
		MYSQL *	mysql
	)

static

Truncates files and dirs tables.

Especially useful if AUTO_INCREMENT fields overflow.

Warning: Truncate operations are not transaction-safe, so an error occurs if the session attempts one during an active transaction or while holding a table lock.

Parameters

r	Apache request_rec struct to handle log writings
mysql	mysql handler to communicate with the server

Definition at line 410 of file cache-mysql.c.

References mi_rerror, TABLE_DIRS, and TABLE_FILES.

Referenced by mysql_cache_make_dir(), and mysql_cache_write().

static void mysql_cache_write	(	request_rec *	r,
		const mu_ent *const	p,
		const char *const	filename
	)

static

Creates and writes cache information.

This function creates a new cache entry for (using #filename), and fills it with the data contained in the mu_ent p structure.

Bug:: we don't check enough return values of apr_psprintf()/apr_pstrcat()...

Parameters

r	Apache request_rec struct to handle log writings and pool allocation.
p	A mu_ent struct containing the data to store.
filename	current filename of the file to cache.

Definition at line 734 of file cache-mysql.c.

References AINC_OVFLERR, mu_ent::album, apr_psprintf, apr_pstrcat, apr_pstrdup, mu_ent::artist, mu_ent::bitrate, CA_CREATE, mu_config::cache_setup, CACHE_VERS, mu_ent::date, EF_FLAGSTOSAVE, mu_ent::filetype, mu_ent::flags, mu_ent::freq, mu_ent::genre, mu_ent::length, likely, mi_rerror, mu_ent::mtime, musicindex_module, mysql_cache_check_dir(), mysql_cache_make_dir(), mysql_cache_trunc_tables(), mysql_params, mu_ent::posn, mu_ent::size, TABLE_DIRS, TABLE_FILES, mu_ent::title, mu_ent::track, and unlikely.

Variable Documentation

const cache_backend cache_backend_mysql

static

Initial value:

 {
        .opendir =      mysql_cache_opendir,
        .readdir =      mysql_cache_readdir,
        .closedir =     NULL,
        .make_entry =   mysql_cache_make_entry,
        .write =        mysql_cache_write,
        .prologue =     mysql_cache_prologue,
        .epilogue =     mysql_cache_epilogue,
}