sql database orm portability rdbms-agnostic

Problemas de portabilidad SQL



database orm (17)

Mi compañía me tiene trabajando en terminar un back-end para Oracle para Python ORM. Me sorprende la forma en que los RDBMS hacen las cosas de manera diferente incluso para cosas simples. Aprendí mucho sobre las diferencias entre Oracle y otros RDBMS. Solo por pura curiosidad, me gustaría aprender más.

¿Cuáles son algunos "errores" comunes en términos de portabilidad de SQL de una plataforma a otra?

Por favor, solo un gotcha por respuesta.

¿Cuáles son algunos "errores" comunes en términos de portabilidad de SQL de una plataforma a otra?

Lo mismo que los intentos de traducir del English al Russian sustituyendo las palabras del diccionario por las frases.

Trabaja para hola y adiós , pero fracasa para que Mary tuviera un corderito que no decía nada de Shakespeare.

Los diferentes RDBMS tienen culturas diferentes, a pesar de tener SQL en su nombre.

Por ejemplo, la fila limitante.

En Oracle :

WHERE rownum = 1

En SQL Server :

SELECT TOP 1

En MySQL y PostgreSQL :

LIMIT 1

En DB2 :

SELECT * ... FETCH FIRST 1 ROW ONLY

Cuatro cláusulas diferentes.

Consultas de cláusula IN de valores múltiples. Solía ​​usar estos todo el tiempo en Oracle y me sorprendió descubrir que no se puede hacer esto en SQL Server. Por ejemplo, esta consulta:

SELECT * FROM mytable WHERE (col1, col2) IN ( SELECT col1, col2 FROM othertable )

Diferentes bases de datos manejan datos binarios de forma un poco diferente. Entonces, por ejemplo, esto funcionará bajo MySQL:

mysql> CREATE TABLE t (c BINARY(3)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t SET c = ''z''; Query OK, 1 row affected (0.01 sec)

Sin embargo, Oracle confía en que estos valores sean hexadecimales:

SQL> CREATE TABLE t (c RAW(3)); Table created. SQL> INSERT INTO t VALUES (''z''); INSERT INTO t VALUES (''z'') * ERROR at line 1: ORA-01465: invalid hex number

En cambio, tenemos que convertirlo a hexadecimal:

SQL> INSERT INTO t VALUES (rawtohex(''z'')); 1 row created.

Establecer el soporte del operador.

Además de UNION / UNION ALL, establecer el soporte del operador es bastante irregular en las bases de datos. Oracle y SQL Server son compatibles con la mayoría de ellos, pero Oracle admite una operación MINUS así como la operación equivalente EXCEPT DISTINCT equivalente. AFIK, MySQL solo es compatible con UNION (sin soporte INTERSECT o EXCEPT).

Los problemas de rendimiento son grandes. Por ejemplo, las vistas en Oracle son, AFAIK, casi tan rápidas como las tablas. Este no fue el caso con SQLServer cuando tuve que usarlos. Las vistas efectivamente mataron el rendimiento reduciendo la velocidad de las mismas selecciones en un orden de magnitud o más (una consulta directa de las tablas tomó, digamos 0.5s, mientras que usar una vista podría tomar un minuto). También hubo muchas limitaciones en su uso, por ejemplo, no todas las funciones de SQL se podían usar en las vistas.

Tenga en cuenta que esto era cierto 5-6 hace seis años, no sé si Microsoft ha mejorado esto desde entonces.

No estoy seguro de si esto es cierto en los controladores que vienen con Python, pero en la versión de "Horrable" que utilicé, si una columna en un conjunto de resultados devuelve NULL para todos los valores en la columna, la columna no se devuelve en la estructura del conjunto de resultados Esto podría (y a menudo lo hizo) dar como resultado errores de producción que eran imposibles de duplicar en el desarrollo.

Ya que se está alejando de Oracle, esto no debería ser una preocupación, pero hay una palabra clave que usan los desarrolladores para mitigar esta "característica" que dudo que sea compatible con MySQL. Sin embargo, me olvido de lo que es y Google no está ayudando.

Oracle no le permite tener múltiples insertos en una consulta. MySQL permite esto:

INSERT INTO test(id, name) VALUES (1, ''foo''),(2, ''bar'');

Oracle no le permitirá insertar cadenas vacías: se convierten silenciosamente a NULL.

Oracle no parece tener problemas con los cursores, son un gran problema de rendimiento en el servidor SQL.

En realidad, prácticamente todo el ajuste de rendimiento es específico de la base de datos (razón por la cual el código estándar ANSII a menudo funciona muy mal en comparación con los mejores métodos diseñados en el sabor específico de SQL que es específico de la base de datos).

Las fechas son otra cosa que parece manejarse de manera muy diferente desde la base de datos a la base de datos.

Los tipos de datos tampoco son equivalentes. Una cosa que tiende a atraer a los recién llegados a SQL Server es que el tipo de datos de marca de tiempo no tiene absolutamente nada que ver con fechas y horas y no se puede convertir a un valor de tiempo de datos.

Oracle no permite una instrucción select sin una cláusula FROM. Por lo tanto, no puedes hacer consultas como esta:

SELECT 1

En su lugar, debe decir que la consulta es de la tabla DUAL :

SELECT 1 FROM DUAL

Oracle tiene un enfoque diferente de las cotizaciones opuestas a MySQL.

MySQL: `object_name`, ''string'', "string" Oracle: "object_name", ''string''

Además, escapar es diferente.

MySQL: ''It/'s easy'' Oracle: ''It''''s slightly confusing''

(Tenga en cuenta que para escapar de cualquier cosa que no sean citas en Oracle, puede usar la directiva ESCAPE en su consulta; SELECCIONAR * FROM testTable WHERE percent = ''50 /% ''ESCAPE'' / '')

Otro ejemplo es generar claves primarias únicas (típicamente sustitutas).

Muchas bases de datos, como SQL Server y sqlite, permiten declarar una columna como identidad: normalmente, si falta el valor de esta columna en la inserción, la base de datos generará un valor único para la columna.

Oracle, por el contrario, tiene que crear una secuencia separada de la tabla y luego utilizar nextval en la secuencia para generar el siguiente valor:

CREATE SEQUENCE test_seq; SELECT test_seq.nextval FROM dual;

O, más típicamente:

INSERT INTO foo(id, title) VALUES (test_seq.nextval, ''bar'');

Sintaxis de unión oscura, como la sintaxis de Oracle (+) para las uniones externas. En una compañía donde solía trabajar, esta sintaxis se usaba en todas partes en lugar de la sintaxis LEFT OUTER OJER / LEFT JOIN estándar, lo que hacía que cargar algunas cosas en MySQL fuera un verdadero dolor.

Para obtener una idea general de los errores específicos del producto, debe conocer la diferencia entre el diseño de la base de datos lógica y el diseño de la base de datos física.

El diseño lógico de la base de datos tiene que ver principalmente con las características de las tablas. Las características de las tablas incluyen columnas y restricciones. Aunque las tablas en sí mismas son físicas, el diseño de la tabla generalmente es muy portátil de un sistema de base de datos a otro. Existen diferencias en la forma en que funcionan algunos tipos de datos, y algunas diferencias en la sintaxis, como si el guión bajo se puede usar en un nombre de tabla o no. Pero un buen diseño lógico debe trasladarse de un sistema a otro con solo cambios menores o sin cambios.

El diseño físico de la base de datos tiene que ver principalmente con las características de la infraestructura en la que descansa la estructura de la tabla. Casi todos los sistemas admiten índices, y el tipo de índice predeterminado es B-tree, aunque podría llamarse algo más. Pero a partir de ahí, cada sistema tiene sus propias características físicas, que pueden ser completamente diferentes de un sistema a otro. Una característica física típica de Oracle son los espacios de tabla. Estrechamente asociado con los espacios de tabla está el mapeo entre tablas y espacios de tabla. El diseño físico tiene que hacerse en una base específica del sistema.

Además del sistema RDM que está utilizando, debe tener en cuenta el volumen de datos, la carga, los requisitos de tiempo de respuesta y los recursos del sistema, como los discos, en la cuenta de su diseño. La buena noticia es que se pueden realizar muchos cambios en el diseño físico sin cambios en el código de la aplicación. Esto se conoce como independencia de datos físicos. Esto significa que es un tanto libre de modificar y ajustar el diseño físico después de que haya escrito un código de aplicación y cargado algunos datos.

Es posible que desee consultar algunos libros sobre diseño de bases de datos para obtener una idea más profunda del diseño lógico y físico y la diferencia entre ellos. Algunos autores populares son CJ Date y Joe Celko.

Tablas temporales: Oracle vs SQL Server / MySQL. Transición de Oracle a MS / MySQL, no hay problema. A la inversa, un poco diferente.

Recuerdo un problema peculiar de Oracle que me sacó completamente de gueard. No estoy seguro de que esta sea una configuración de la instancia o una configuración predeterminada, pero no podríamos tener más de 1000 elementos en una declaración IN. Así que tuvimos que engañarlo para que hiciera lo que quería:

SELECT Col1,Col2 FROM Table WHERE Code IN (1,2,3,...,1000) OR Code IN (1001,1002,1003,...,2000)

etc.

Feo, pero funcionó.

(Antes de que alguien señale la solución obvia de una subconsulta o vista en línea, la consulta se generó en un sistema completamente diferente)