Tablas de bases de datos personalizadas: creando la tabla

Spanish (Español) translation by Steven (you can also view the original English article)

En esta serie veremos el uso de tablas de bases de datos personalizadas. Cubriremos cómo crear, mantener y eliminar la tabla, así como cómo agregar, eliminar y consultar datos de manera segura y eficiente. En este primer artículo, analizamos cuándo las tablas personalizadas podrían ser apropiadas, los pros y los contras de usarlas y cómo crear la tabla.

Afortunadamente, WordPress proporciona una API bastante importante que simplifica un poco la creación e interacción con tablas personalizadas. Más notablemente: la clase $wpdb y la función dbDelta() que veremos más durante la serie. A pesar de eso, sin embargo, crear una tabla personalizada significa crear algo ajeno a WordPress, y pierde la mayor parte del framework que rodea a las tablas nativas. Por esa razón, tú, como autor del complemento, eres responsable de interactuar con él de manera segura y eficiente. Entonces, antes de saltar, debes considerar cuidadosamente si es más apropiado o no usar una tabla básica existente.

Las desventajas de usar una tabla personalizada

Como se mencionó, las tablas personalizadas se encuentran fuera del framework normal de WordPress y, en su mayor parte, esta es la causa subyacente de sus desventajas:

No hay funciones nativas de agregar, quitar, actualizar o consultar con las que interactuar con la tabla.
La interfaz de usuario debe construirse desde (casi) cero.
El saneamiento y el almacenamiento en caché dependen de ti (aunque WordPress proporciona mucha ayuda a este respecto).
Otros complementos, y el propio WordPress, no 'esperan' que tu tabla esté allí. Por otro lado, si tus datos son de un tipo de publicación personalizada, entonces la mayoría de los complementos de terceros bien construidos funcionarán junto con ellos.
WordPress, ni muchos otros complementos relacionados, respaldarán o exportarán tu tabla. (En realidad, bastantes complementos de respaldo admiten tablas no centrales, pero exportar/importar no es tan sencillo)
Eres responsable de configurar la estructura de tus tablas personalizadas de la manera más eficiente, incluida la elección del tipo de datos más apropiado para las columnas.
Eres responsable de escribir consultas SQL eficientes y sin errores.

¿Cuándo es apropiado crear una tabla personalizada?

No hay una respuesta "correcta" a esto, y se requiere un juicio sensato de los pros y los contras. Sin embargo, la sección anterior describe algunos inconvenientes graves de no utilizar el esquema de WordPress existente; por lo tanto, si no estás seguro, generalmente es mejor evitar crear una tabla. Además, un enfoque de tabla personalizada requiere mucho trabajo y ofrece una amplia oportunidad para que los errores se arrastren. Pero con eso en mente, ¿cuándo podría ser apropiada una tabla personalizada?

La estructura de datos

Uno de los argumentos más importantes para las tablas personalizadas es cuando los datos deben estructurarse de tal manera que sean inapropiados para las tablas nativas. La tabla *_posts está intrínsecamente orientada a publicaciones y páginas, que pueden ser totalmente inadecuadas para tus datos. De hecho, es mejor que tus datos se distribuyan en varias tablas, con relaciones entre ellas. Puede que ni siquiera sea tan complicado: el complemento Posts 2 Posts utiliza una tabla personalizada para almacenar relaciones de muchos a muchos entre los tipos de publicaciones. Esto se podría hacer utilizando la API de taxonomía (y originalmente lo era) o la meta API, pero ninguna de estas es particularmente eficiente, y aunque puede estar bien para sitios más pequeños, no se escala bien. Scribu movió "Posts 2 Posts" a una implementación de tabla personalizada para permitir que se almacene información sobre una relación.

Si bien la mayoría de los casos se pueden 'apretar' en el molde *_posts usando post meta, esto puede no proporcionar la ruta más eficiente: la metatabla 'post' usa una columna de valor no indexada para almacenar datos. Es increíblemente rápido para recuperar los metadatos de una publicación (WordPress también emplea el almacenamiento en caché aquí), pero las consultas complejas que usan la metatabla pueden ser ineficientes o casi imposibles.

Consultas complejas

En relación con lo anterior, se encuentran las consultas complejas, para las cuales las tablas nativas podrían no estar diseñadas para completar de manera eficiente. En Event Organizer, por ejemplo, un evento es una publicación con las fechas del evento almacenadas en una tabla separada. Aunque sería posible almacenar esas fechas como meta de publicación, hacerlo cuando los eventos tienen más de una fecha haría que las consultas basadas en fechas sean extremadamente difíciles e ineficientes, particularmente porque la columna de metavalores no está indexada.

Escalabilidad

Si usas wp_posts y tus datos son lo suficientemente grandes (más de 100,000 publicaciones), puede afectar el rendimiento, dependiendo de las consultas que estés ejecutando. Este argumento por sí solo es bastante débil, ya que hay muchas incógnitas que afectarán su validez. No obstante, en general, las bases de datos son rápidas en lo que hacen, y el framework de WordPress circundante sirve para optimizar las consultas tanto como sea posible. Sin embargo, en combinación con los otros dos factores, es posible que una tabla personalizada presente la opción más sensata.

Creando la tabla

Una vez que hayas decidido que es necesaria una tabla personalizada, debemos crear la tabla. Antes de hacer eso, almacenaremos el nombre de nuestra tabla personalizada en $wpdb. Este global contiene toda la información perteneciente a la base de datos para el blog actual (cambiará de un sitio a otro, cuando se usa multi-sitio). Agregaremos el nombre de nuestra tabla a este global. Esto no es en absoluto necesario, pero hace que el resto de nuestro código sea un poco más ordenado:


add_action( 'init', 'wptuts_register_activity_log_table', 1 );
add_action( 'switch_blog', 'wptuts_register_activity_log_table' );

function wptuts_register_activity_log_table() {
	global $wpdb;
	$wpdb->wptuts_activity_log = "{$wpdb->prefix}wptuts_activity_log";
}

El código anterior usa $wpdb->prefix para agregar un prefijo al nombre de la tabla. El prefijo es wp_ por defecto, pero el usuario puede modificarlo en wp-config.php. Esto es necesario cuando puedes tener más de una instalación de WordPress usando la misma base de datos, pero también puede cambiarse por otras razones. Como tal, no puedes asumir que el prefijo es wp_. Al igual que con las funciones, clases y configuraciones, etc., debes asegurarte de que el nombre de tu tabla sea único.

A lo largo de esta serie volveremos al siguiente ejemplo. Imaginaremos que estamos creando una tabla para registrar la actividad del usuario (actualizar o eliminar publicaciones, cambiar configuraciones, cargar una imagen, etc.).

Convenciones de nomenclatura de columnas

Existen varias convenciones sobre cómo nombrar tus columnas (y tus tablas para el caso), pero independientemente de cómo las nombres, es importante ser coherente. Recomendaría usar solo caracteres en minúscula, ya que en algunas situaciones los nombres de las columnas pueden distinguir entre mayúsculas y minúsculas, e imponer esa regla hace que los errores sean menos probables y mejora la legibilidad. Como veremos más adelante en la serie, también es útil para cuando necesites incluir columnas en la lista blanca. Debes separar las palabras en los nombres de las columnas (por ejemplo, post_data, post_content) para facilitar la lectura, pero debes hacerlo con guiones bajos y nunca espacios.

También debes evitar las palabras reservadas. Si la columna se refiere a una tabla externa, se recomienda que utilices el nombre de esa columna externa (como user_id, nuestro ejemplo).

En nuestro ejemplo nombraremos nuestras columnas:

log_id - el ID de registro.
user_id - el ID de usuario para el que corresponde el registro.
activity - la actividad que se produjo.
object_id - el ID del objeto (por ejemplo, POST ID, ID de usuario, ID de comentario, etc.) que fue objeto de la actividad del usuario.
object_type - el tipo de objeto (por ejemplo, 'post', 'usuario', 'comentario', etc.).
activity_date - la fecha y hora de la actividad.

Decidir los tipos de columna

Antes de continuar, deberás decidir los tipos de datos de las columnas que tendrá tu tabla. Los tipos de columna se pueden dividir en tres categorías: cadenas, números y fechas. Para cada uno de estos hay muchas variantes. Puedes encontrar una referencia completa aquí.

Es importante elegir el tipo de datos apropiado para tu tabla, ya que esto afectará la eficiencia de tus consultas. Algunos tipos de datos te permiten establecer un límite (por ejemplo, varchar(40), que te permite almacenar hasta 40 caracteres). El límite es opcional, pero se recomienda ya que puede mejorar el rendimiento, por lo que deberás decidir para cada columna cuál es la cantidad máxima de caracteres que requerirá la columna. Ten en cuenta que para los tipos de datos numéricos, la longitud se refiere a la cantidad de dígitos, no al máximo (por ejemplo, INT(10) permite números enteros no negativos de hasta 10 dígitos, es decir, hasta 4.294.967.295).

Al almacenar fechas, casi siempre debes usar el tipo de datos DATETIME (almacenado como 2012-11-05 14:55:10), y ciertamente no una representación amigable para humanos de la fecha (por ejemplo, 5 de noviembre de 2012 2:55 pm). Los valores DATETIME se pueden formatear fácilmente en un formato legible por humanos usando funciones como mysql2date(). Debes almacenar las fechas en la zona horaria UTC y, si es necesario, cambiarlas a una zona horaria diferente en la salida.

En nuestro ejemplo tendremos:

log_id - bigint(20)
user_id - bigint(20)
activity - varchar(20)
object_id - bigint(20)
object_type - varchar(20)
date - datetime

Indexando columnas

A continuación, deberás decidir qué columnas indexar; estas se declararán como KEY (claves), una de las cuales será la PRIMARY KEY (clave primaria). La clave principal es una columna donde cada fila tiene una entrada única; por lo general, es solo un entero que se incrementa automáticamente, esencialmente el 'número de fila'.

Los valores de las otras columnas indexadas no necesitan ser únicos, pero el valor debe determinar un conjunto relativamente pequeño de registros. La idea de la indexación es mejorar las consultas de lectura. Sin un índice, una búsqueda tendría que leer toda la tabla para encontrar filas coincidentes. Si una columna está indexada y es parte de la consulta, entonces esto puede encontrar rápidamente filas que coincidan con esa columna y luego ese subconjunto más pequeño de filas coincidentes puede cotejarse con la consulta (la analogía es un índice para un libro).

Por lo tanto, si no consultas por esa columna, indexar esa columna no ayudará (si nunca buscas una palabra en el índice del libro, es posible que no esté allí). Tampoco si muchos registros comparten el mismo valor, como una columna de 'género', ya que esto no ofrecerá una gran mejora en un escaneo completo de la tabla (imagina un índice de libro que enumera una palabra que aparece en todas las páginas)

La indexación tampoco es libre: las columnas declaradas como KEY reducen el rendimiento de escritura (para continuar con la analogía, necesitarías actualizar el índice del libro cuando se agregue o elimine una palabra indexada), por lo que deberás decidir cuál es el balance correcto para tu configuración. Puedes encontrar más información aquí.

Dado que es probable que deseemos realizar consultas por usuario (para ver su actividad reciente) indexaremos esta columna y usaremos log_id como clave principal.

Creación de la tabla

Colocaremos el código para crear la tabla personalizada dentro de la siguiente función:


function wptuts_create_tables() {
	// Code for creating a table goes here
}

// Create tables on plugin activation
register_activation_hook( __FILE__, 'wptuts_create_tables' );

Esta función deberá invocarse en el gancho de activación del complemento, así como siempre que deseemos realizar modificaciones en la tabla, por ejemplo, agregar columnas o cambiar su tipo de datos (explicaremos el por qué más adelante en la serie).

El hecho de que al usar el gancho de activación, se pueda llamar a wptuts_create_tables() cuando ya existe una tabla, no es un descuido, y nuevamente, explicaremos el por qué más adelante en la serie.

Dentro de esa función, incluimos wp-admin/includes/upgrade.php para configurar algunas constantes y cargar la función dbDelta(). Ten en cuenta que cuando se activa un complemento, pierde el gancho init, por lo que wptuts_register_activity_log_table() debe llamarse manualmente.


require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
global $wpdb;
global $charset_collate;
// Call this manually as we may have missed the init hook
wptuts_register_activity_log_table();

El $charset_collate global contiene el conjunto de caracteres y la intercalación utilizados por las tablas nativas de WordPress. En términos generales, estos definen las codificaciones de los caracteres y cómo se comparan; dado que WordPress se usa en muchos idiomas diferentes, es importante usar la intercalación correcta para tu tabla.

Aparte de la intercalación, la declaración SQL debe declarar el nombre de la tabla, junto con cada columna, su tipo y valor predeterminado y cualquier columna KEY, incluida una columna PRIMARY KEY. Normalmente será de la forma:


CREATE TABLE [table name] (
	[primary key column] bigint(20) unsigned NOT NULL auto_increment,
	[column name] [data type] [default],
	PRIMARY KEY  ([column name]) ,
	KEY key_name ([column name])
) [collation];

Para crear esta tabla agregamos lo siguiente a nuestra función wptuts_create_tables():


$sql_create_table = "CREATE TABLE {$wpdb->wptuts_activity_log} (
          log_id bigint(20) unsigned NOT NULL auto_increment,
          user_id bigint(20) unsigned NOT NULL default '0',
          activity varchar(20) NOT NULL default 'updated',
          object_id bigint(20) unsigned NOT NULL default '0',
          object_type varchar(20) NOT NULL default 'post',
          activity_date datetime NOT NULL default '0000-00-00 00:00:00',
          PRIMARY KEY  (log_id),
          KEY user_id (user_id)
     ) $charset_collate; ";

dbDelta( $sql_create_table );

La función dbDelta() realiza nuestro comando CREATE TABLE. Puede ser bastante estricto con la declaración SQL que se le da. Por ejemplo, debe haber dos espacios entre PRIMARY KEY y la columna de la clave principal; y las llaves deben tener un nombre.

Depuración

Si en la activación encuentras el mensaje de error 'Tienes un carácter X de salida inesperada...', es probable que haya un error en tu declaración SQL. A veces se debe al rigor de dbDelta(). Si agregas wp_die(); después de dbDelta(), esto mata el procesamiento y (con `WP_DEBUG` establecido en verdadero) revelará cualquier mensaje de error.

Resumen

En este artículo, hemos analizado las razones por las que deberías y no deberías utilizar tablas personalizadas, así como los detalles que deberás considerar y, finalmente, cómo crear una tabla. La siguiente parte de esta serie cubrirá la sanitización, analizando la inyección SQL y cómo protegerte de ella. El código de este artículo está disponible en este repositorio de GitHub y se actualizará a medida que continúe la serie.