sql - source - Diseño de base de datos para aplicaciones agnósticas de base de datos

sql database modeler (17)

En 2001, trabajé en un producto que tenía que soportar Oracle 8, MS SQL Server 2000 y MS Jet 3.51 (también conocido como Access97). En teoría, podríamos haber empleado especialistas para cada uno de estos productos y un proceso de prueba que aseguró que todos produjeran los mismos resultados. En la práctica, hubo una tendencia hacia el mínimo común denominador.

Un enfoque fue crear tablas vinculadas en Access / Jet para Oracle y SQL Server y luego escribir exclusivamente Jet SQL. El problema aquí es que la sintaxis de Jet SQL es muy limitada.

Otro enfoque (¡comúnmente empleado incluso en sistemas que solo han usado un solo producto DBMS!) Es tratar de hacer más del trabajo que uno realmente debería hacer en el frente, cosas que deberían ser el dominio del DBMS. El problema aquí es que a menudo es desastroso en cuanto a la integridad de los datos. Estoy seguro de que conoce la situación: la aplicación debe abstenerse de escribir datos ilegales, pero sin restricciones en el DBMS en sí está abierta a errores de aplicación. Y luego está el usuario que sabe cómo conectarse a los datos a través de Excel, SQL Management Studio, etc., y de ese modo omite por completo la aplicación que se supone debe garantizar la integridad de los datos ...

Personalmente, me encontré cada vez más escribiendo código en una escala móvil de lo que más tarde descubrí que se llamaba ''portabilidad''. Idealmente, en primera instancia es el código ''vainilla'' entendido por todos los DBMS que apoyamos y al hacerlo, descubrí los estándares SQL-89 y SQL-92. Lo siguiente fue un código SQL que podría traducirse fácilmente (quizás usando código) para cada DBMS; por ejemplo, Oracle usó esa sintaxis horrible de unión externa infija pero el concepto de una unión externa estaba allí; Oracle y SQL Server usaron SUBSTRING pero Jet requirió que la palabra clave fuera MID $; etc. Por último, hay cosas que simplemente tienen que ser específicas de la implementación, obviamente evitadas si es posible mientras se sigue prestando la debida atención a la integridad de los datos, la funcionalidad y el rendimiento.

Afortunadamente, en los años transcurridos los productos se han acercado a los Estándares ANSI SQL (aparte de Jet, que fue desaprobado por MS ahora solo se mantiene vivo por el equipo de MS Access aparentemente recortando características importantes como la seguridad y la replicación). Así que he mantenido el hábito de escribir SQL estándar siempre que sea posible.

¿Qué tengo que considerar en el diseño de la base de datos para una nueva aplicación que debería ser capaz de soportar los sistemas de bases de datos relacionales más comunes (SQL Server, MySQL, Oracle, PostgreSQL ...)?

¿Vale la pena el esfuerzo? ¿Cuáles son las trampas?

Además de tener en cuenta las muchas respuestas buenas y sensatas aquí, también añadiría que algo como las migraciones de ActiveRecord (de Ruby On Rails, pero puedes usar la biblioteca) podría ser útil. Resume cosas como la creación / alteración de tablas, los tipos de columnas apropiados, la gestión de índices más simple y (hasta cierta cantidad) la secuencia en un lenguaje descriptivo bastante simple.

Los procedimientos almacenados y desencadenantes son prácticamente ignorados, pero si vas a usar multiplataforma, ese tipo de funcionalidad probablemente debería estar en una capa de código.

Por curiosidad, cambié entre Oracle, MS SQL, MySQL y SQLite con el mismo conjunto de migraciones y el peor problema que tuve fue descubrir que tenía que asegurarme de que mis nombres de columnas y tablas no estaban en la unión de listas de palabras reservadas en todo el DBs.

Regla 1: No use características específicas de la base de datos

Regla 2: No use procedimientos almacenados.

Regla 3: si rompe la Regla 1, entonces rompa la regla 2 también.

Ha habido muchos comentarios sobre no usar procedimientos almacenados. Esto se debe a que la sintaxis / semántica es muy diferente y, por lo tanto, transferirlos es difícil. No quieres montones de código que hayas reescrito y vuelto a probar.

Si decide que necesita usar características específicas de la base de datos, entonces debe ocultar esos detalles detrás de un procedimiento almacenado. Llamar a los procedimientos almacenados de diferentes bases de datos es bastante similar. Dentro del procedimiento, que está escrito en PL / SQL, puede usar cualquier construcción de Oracle que le parezca útil. Luego debe escribir un equivalente para las otras bases de datos de destino. De esta manera, las partes que son específicas de la base de datos están en esa base de datos solamente.

De ser posible, evitaría hacer esto. He trabajado con varias de estas bases de datos en el pasado y fueron terriblemente lentas (un ejemplo particularmente doloroso que puedo pensar fue una aplicación de centro de llamadas que tardó diez minutos en pasar de una pantalla a otra en un día ocupado) debido a la necesidad para escribir sql genérico y no usar el ajuste de rendimiento que era mejor para el backend particular.

No use procedimientos almacenados
No use SQL específico del vendedor

O bien, utilice una tecnología de persistencia como hibernate / nHibernate que abstraiga las diferencias entre diferentes DB.

Actualmente apoyo Oracle, MySQL y SQLite. Y para ser honesto, es difícil. Algunas recomendaciones serían:

evite los procedimientos almacenados, pero puede necesitarlos para emular las características que faltan en alguna plataforma (ver a continuación)
aprende cómo usar los disparadores, porque los necesitarás para emular las características que faltan (por ejemplo, con Oracle no tienes autoincremento, así que debes emularlo, y una buena opción es con activadores)
tenga un entorno de prueba decente, ya que tendrá que probar muchos SQL antes de asegurarse de que está haciendo lo que desea en todas sus plataformas.

Vale la pena ... bien depende. Comercialmente vale la pena para las aplicaciones de nivel empresarial, pero para un blog o para decir un sitio web, es mejor que se quede con una plataforma si puede.

El 95% portátil es casi tan bueno como portátil si puede aislar el código dependiente de la plataforma en una capa específica. Al igual que Java se ha descrito como "Escribir una vez en todas partes" , todavía se debe probar la aplicación en cada plataforma en la que se quiera ejecutar.

Si es cauteloso con el código específico de su plataforma, puede usar un código portátil para el 95 +% de la funcionalidad que se puede hacer adecuadamente de una manera portátil. Las partes restantes que deben realizarse en un procedimiento almacenado u otra construcción dependiente de la plataforma se pueden integrar en una serie de módulos dependientes de la plataforma a una interfaz estándar. Dependiendo de la plataforma, usa el módulo apropiado para esa plataforma.

Esta es la diferencia entre ''Prueba en todas partes'' y ''Desarrollar módulos específicos de la plataforma y Prueba en todas partes''. Deberás probar en todas las plataformas compatibles de todos modos, no puedes escapar de eso. La construcción extra es relativamente menor, y probablemente menos que hacer una arquitectura realmente intrincada para tratar de hacer estas cosas de manera totalmente portátil.

Entiendo las otras respuestas aquí, pero ¿por qué no utilizar procedimientos almacenados? ¿Es así que la lógica no está oculta?

Investigue por adelantado el denominador común más bajo para los tipos de datos. Por ejemplo, SQL Server tiene un número entero pero Oracle usa un número.

La respuesta corta es apegarse a las características que están estándar o casi implementadas de manera estándar. Lo que esto significa con más detalle es:

Evite todo lo que use el lenguaje de procedimientos de la base de datos (procedimientos almacenados o disparadores) ya que aquí es donde entran las enormes diferencias entre los sistemas. Puede necesitar usarlos para emular algunas funciones, pero no las use para crear su propia funcionalidad.
Separar secuencias de campos de auto-incremento de los propios campos. Esto se verá un poco forzado para MSSQL, pero se implementará limpiamente en Oracle, DB / 2, etc., sin necesidad de correcciones de emulación.
Mantenga los campos char y varchar por debajo del tamaño máximo más pequeño para el conjunto de motores que está apuntando.
Cuando esté escribiendo consultas, use la sintaxis JOIN completa y corchetee las UNIONES para que cada unión se encuentre entre una sola tabla y una expresión entre corchetes.
Mantenga la lógica de manejo de fechas en el código, no las consultas, ya que muchas de las funciones de fecha están fuera del estándar. (Por ejemplo: si desea obtener material para las últimas dos semanas, calcule la fecha hace dos semanas en el código y utilícela en la consulta).

Más allá de eso, el esfuerzo involucrado no debe ser demasiado intimidante, por lo que bien puede valer la pena.

Mantenga los nombres de campo y tabla cortos (<30 caracteres) y no distinga entre mayúsculas y minúsculas. por ejemplo, TABLE_NAME y FIELD_NAME

Si fuera usted, pensaría mucho sobre el rendimiento de su inversión aquí .

Siempre me parece una gran idea poder conectar cualquier parte trasera o cambiar de fondo cuando quiera, pero esto rara vez ocurre en The Real World en mi experiencia.

Puede ocurrir que cubra el 95% de sus clientes potenciales si solo brinda soporte a Oracle y SQL Server (o MySQL y SQL Server, o ... etc.).

Investiga antes de seguir adelante y ¡buena suerte!

Una respuesta que las personas a menudo le dirán es no usar sql específico de la base de datos y solo codificar para los estándares ansi. A menudo dirán que solo hablan con la base de datos a través de procs almacenados para abstraer cualquier sql. Estas son las respuestas incorrectas y solo conducen al dolor. La codificación de sql ''estándar'' es bastante imposible porque cada vendedor tiene interpretaciones diferentes.

Lo que necesita hacer es tener algún tipo de capa de persistencia de base de datos que resuma las diferencias entre las bases de datos (lo siento, johnstock, esto es casi exactamente lo que dijo). Hay muchos otros ORM y productos similares para hacer esto para cada plataforma,

Voy a plagerizar la respuesta de johnstok de 1) No usar procedimientos almacenados y 2) No usar SQL específico del vendedor y agregarlo.

También preguntaste: "¿Vale la pena el esfuerzo?". Yo diría ... tal vez. Escribí un rastreador de errores de código abierto, BugTracker.NET, que está basado en SQL Server. Hay muchos desarrolladores que simplemente no lo probarán porque les gusta apegarse a las tecnologías con las que se sienten cómodos. Y, cuando estaba considerando comenzar un servicio de alojamiento, noté que los servidores virtuales dedicados de Linux son mucho más baratos que los servicios de Windows (no virtuales). Teóricamente podría ejecutar el C # en mono, pero mi SQL es tan específico de SQL Server (aunque no utilizo procs almacenados) sería un gran esfuerzo de puerto.

Si se dirige a un mercado empresarial / corporativo, descubrirá que algunas tiendas son estrictamente Oracle o estrictamente SQL Server, y que su aplicación podría descartarse en las primeras rondas de la competencia según la tecnología que utiliza.

Entonces, quizás ser abierto sí importa, para ti. ¿Qué tipo de aplicación es? ¿Quién lo usará?

También preguntaste: "¿Qué son los ptifalls?". No probando sobre la marcha. Si planeas respaldar los 4 dbs que enumeró, entonces deberías probarlos con anticipación y con frecuencia, en lugar de solo enfocarte en uno, mientras piensas que será fácil convertirlo a los demás. Para entonces, es posible que se encuentre en un callejón sin salida arquitectónico.

Lo primero a tener en cuenta es si el costo de hacerlo de manera independiente es menor y depende de la base de datos. Creo que algunas veces es importante que algunos productos ofrezcan opciones a los clientes, pero están perdiendo muchas características de la base de datos (significa que el código debe escribirse nuevamente).

Para grandes clientes (grandes aplicaciones) tienen que depender completamente de la base de datos. Para pequeñas personalizaciones, es realmente un problema tener un Oracle XE y un MySQL en un servidor (o dos).

Realmente, prefiero usar más de una base de datos y que la aplicación sepa qué base de datos es un código "abastract".

IMO depende del tipo de aplicación que está desarrollando:

Una aplicación que cumple alguna otra necesidad que implica el almacenamiento de datos, por ejemplo, sitios web comerciales, aplicaciones de línea de negocio, incluso aplicaciones de inicio / estilo de vida.
Una aplicación diseñada específicamente para manipular o administrar bases de datos, por ejemplo, herramientas de diseño, herramientas de modelado, herramientas de ETL.

Para el caso 1, simplemente escoja un DBMS que se adapte mejor a sus necesidades, y codifique contra eso, usando toda la potencia de todas sus características patentadas.

Para el caso 2, probablemente encontrará que es bastante factible apegarse al subconjunto común de operaciones admitidas por todos los DBMS que pretenda apoyar.

En complemento a esta respuesta , y como regla general, no permita que el servidor genere o calcule datos. Envíe siempre instrucciones SQL directas, sin incluir fórmulas. No use propiedades de valor por defecto (ni las haga básicas, no fórmulas). No use reglas de validación. Tanto los valores predeterminados como las reglas de validación deberían implementarse en el lado del cliente.