Sincronizar MySQL con SQLite

[aitorqrv]

Hola, perdón si este no es el sitio adecuado del foro para mi pregunta, ya que yo programo en lazarus.

Estoy intentando hacer un programa multi usuario que tiene que sincronizar una base de datos Mysql que esta en internet con otra base de datos SQLite que tiene cada usuario en su PC o en un teléfono y vicebersa.

Me podéis asesorar de cual seria la forma mas segura de sincronizar ambas base de datos.

Cada usuario tendra su nombre de usuario y contraseña, y al ser un programa que va a ser de código abierto no creo que se pueda acceder directamente al servidor MySQL desde el programa ya que sino cualquiera sabría las contraseñas de la base de datos MySQL y la podría liar.

Muchas gracias.

[aitorqrv]

Hola gracias a todos por vuestro interés, según parece el problema es mas complicado de lo que parece, el no saber como llevar acabo esto yo se lo achacaba a mi falta de experiencia...

Al no obtener respuesta decidí mandarle un mensaje privado a Ñuño Martínez ya que he leído unos cuantos post en los que el ha participado y al ver que tenia conocimientos de Lazarus y PHP, pense que seria la persona idonea para orientarme sobre cuales podían ser los pasos para llevar acabo este proyecto que tengo entre manos.

Desde aquí quiero aprovechar para darle las gracias por su respuesta.

Hola,

La verdad es que no se me ocurre ninguna forma de sincronizar ambas bases de datos de forma sencilla. Es a es la razón por la que no contesté en su momento a tu mensaje.

Por el lado web, habría que hacer un servicio web capaz de recibir los nuevos datos y actualizar la base de datos en web. Por el lado PC habría que hacer una conexión con dicho servicio y enviarle los datos adecuados. También sería útil poder consultar al servicio web las diferencias con la versión PC y viceversa.

Lamento ser tan escueto, pero no tengo mucho más tiempo. Puedes contestar a tu hilo en el foro para que vuelva a la cabecera, y quizá así alguien pueda darte alguna pista más.

Un saludo.

Visto de que esto es un poco complejo, creo que primero programare el programa que va a correr en el ordenador y luego cuando todo funcione correctamente, ya pensare en el modo de sincronizar ambas bases de datos.

Yo avía pensado el meter un campo Date Time a cada registro y así saber cuando se ha creado o actualizado ese registro. Luego tanto el programa como la aplicación PHP le preguntaría a cada tabla su última fecha y hora en el que se ha actualizado, compararía ambos resultados y en base a los resultados sincronizaría los datos en una dirección u otra.

Muchas gracias a todos por vuestro interés, si alguien tiene alguna idea o quiere aportar alguna opinión quedo a la escucha.

Gracias.

[Casimiro Notevi]

Empezado por aitorqrv

Yo avía pensado el meter un campo Date Time a cada registro y así saber cuando se ha creado o actualizado ese registro. Luego tanto el programa como la aplicación PHP le preguntaría a cada tabla su última fecha y hora en el que se ha actualizado, compararía ambos resultados y en base a los resultados sincronizaría los datos en una dirección u otra.

En todo caso sería más fácil tener una tabla donde se guardan todos los cambios que se van haciendo: altas, bajas y modificaciones y ejecutar lo que haya en esa tabla en la otra base de datos.
Pero es un trabajo delicado que tienes que pensar bien antes de hacer nada.

[mamcx]

La forma mas simple es crear desde 0 la BD de sqlite desde los datos de MySql. Osea, se cargan todos los registros, tal cual. Es un proceso rapido para una carga de datos mediana (eso es lo que hago ahora con http://www.elmalabarista.com/bestseller).

Si tienes que sincronizar en tiempo real los cambios, es un cuento muy diferente. Es algo particularmente dificil de hacer (a menos que todos los cambios sean adiciones de registros, sin relaciones).

Si cuentas mas detalles...

[aitorqrv]

Hola, muchas gracias a todos.

Los cambios no tienen por que hacerse en tiempo real ya que esto no es tan importante.

La opción que comenta mamcx me parece muy interesante lo que me preocupa es lo de a que se refiere con "Es un proceso rapido para una carga de datos mediana". La tabla mas grande que puedo llegar a manejar creo que en un caso extremo podrian ser 10000 registros anuales (la media estaria en 250) y la tabla podria tener 10 columnas como maximo (ahora no lo recuerdo la cantidad esacta). A lo largo de 100 años serian 1000000 de registros, ami eso me parece poco ya que la única referencia que tengo es el robot que manejo en el trabajo, y todos los meses ya genera mas de un millon de entradas en su control historico de movimientos.

No se sis estos datos seran aproximados ya que como he comentado no controlo mucho del tema:

En el caso extremo (mas bien tirando a imposible):
1.000.000 registros anuales
por 10 columnas 10.000.000 de datos
a una media de 10 letras por celda o registro 100.000.000
a lo largo de 100 años 10.000.000.000 letras
a 1kbyte por letra en 100 años la db ocuparia 9.31323Gb (95.36743Mb anuales)

En el caso mas realista (in cluso un poco tirando por lo alto):
250 (reg año)x10 (columnas)x10 (letras reg)x100(años)=2.500.000 caracteres en 100 años que suponenen 2.38419Gb (0.02384Mb anuales)



Yo creo que 0.02384Mb anuales no es tanto y en las opciones del progrma se podria hacer que por defecto solo sinclonizase solo los últimos 12 meses, los últimos 2 o 5 años, 10 años y todo....

Muchas gracias, un saludo.

[Casimiro Notevi]

El problema no es la cantidad de datos, sino sincronizarlos.
Si no necesitas tiempo real, entonces quizás te valdría comprimir la BD en un zip, enviarla completa al otro sitio, allí la descomprimes y la importas.

[mamcx]

Lo que comentas como estimación inicial no es nada. Mucho mas carga BestSeller y lo hace en pocos segundos (unos 20 incluyendo que ademas manda datos de vuelta a los ERPs y comprime los datos y regresa la BD).

[bucanero]

Hola, dispongo de un sistema similar a lo que intentas implementar, funcionando ya hace algun tiempo (casi dos años), que permite el proceso de sincronización entre nuestra central y las distintas delegaciones que disponemos (tiendas).

El sistema consta de un servidor de internet con una BD en MySQL, (BD común donde cada delegación recoge y vuelca sus datos) y las delegaciones disponen de sus propia BD en local en un servidor MS-SQL SERVER con un software de gestión de terceros que no me permite realizar modificaciones a la estructura de dichas BD (locales), por lo que he tenido que adaptar todos los sistemas de control para la sincronización a la BD remota que yo si puedo gestionar.

PROCESO GENERAL DE SINCRONIZACIÓN:

Para el control de la sincronización, a cada tabla del servidor remoto le he añadido tres campos adicionales:

-MD5 de tipo string(32): Guarda un valor HASH de los campos de datos que se han sincronizado
-Updated de tipo TimeStamp: Guarda la fecha y hora que se actualizo
-Deleted de tipo boolean: Indica si este registro ha sido borrado

De tal forma que a la hora de realizar el proceso de comparación de datos uso dos Querys (Origen y destino) uno conectado a la BD remota y otro a la BD local, y solamente me descargo los índices de la tabla (campos PRIMARY KEYS), y el campo MD5, de la BD remota y en la BD local pongo los campos índices (PRIMARY KEYS) y una función que me calcula el valor MD5 de los campos de datos de cada registro. En ambos casos ordeno por los valores indice, y ya simplemente se trata de recorres ambos QUERYS simultáneamente aplicando un algoritmo de mezcla.

Según se va recorriendo encontraras las siguiente situaciones:

· El primary key esta en las dos tablas:

o El MD5 es igual en ambas tablas´: el registro esta ya actualizado y no ha cambiado nada, por tanto no se hace nada y se pasa al siguiente registro

o El MD5 es distinto: los datos de origen y destino han cambiado. Se actualizan los datos de la tabla de destino, y en caso de ser el destino el servidor remoto también se actualiza el valor del MD5

· El primary key solo esta en el origen:

o Es un nuevo registro y se añade a la tabla de destino, insertando también el valor del campo MD5 si se trata del servidor remoto.

· El primary key solo esta en el destino: Se trata de un registro antiguo que ha sido borrado.

o Se borra de la tabla si es el servidor local o se marca como deleted si son los datos del servidor remoto

Los datos completos del registro solamente los cargo cuando se ha producido alguna modificación o es necesario dar de alta un nuevo registro, de esta forma el trafico de red no es muy alto, puesto que solo descarga completamente aquellos registros que han sido modificados o añadidos.

TIPOS DE TABLA:

En función del tipo de datos que contiene las tablas me encuentro con las siguientes opciones:

· Tablas que se actualizan completamente desde un sitio al resto por ejemplo desde la CENTRAL a las tiendas, aquí se pueden incluir como ejemplo, las tablas de Articulos, precios, categorías, etc…, este es el proceso mas simple de todos, puesto que los datos se traspasan íntegramente desde un sitio al otro sin mas problemas y sin necesidad de control de delegación.

· Tablas que se actualizan parcialmente, donde cada delegacion actualiza sus datos y recoge los datos de las otras delegaciones, por ejemplo clientes, ventas, stocks, etc. Para este caso es necesario que el PRIMARY KEY o INDICE de la tabla contenga un campo identificador de la delegación, y el proceso de actualización se realiza en dos pasos:

* Primero se mandan nuestros datos a la BD remota, para que estén disponibles al resto de delegaciones, por lo que la consulta de índices la filtramos a delegación=nuestra

* Y finalmente se actulizan los datos que tenemos guardados del resto de delegaciones en la BD local nuestra, filtrando la consulta de indices para que delegación<>nuestra

Este sistema que yo dispongo esta sincronizando algo mas de 100 tablas de media con unos 5000 registros la mayoría y algunas tablas con mas de medio millón de registros, en un tiempo medio de unos 10 minutos, creo que consiguiendo una velocidad de sincronización bastante alta .

[mamcx]

Yo tengo un diseño diferente, que elimina los timestamp. El problema es que las fechas pueden desincronizarse o los usuarios pueden cambiar la fecha y la cosa grave.

Imitando el diseño de mercurial/GIT, uso un campo versión. Un INT que se incrementa cada vez que hay un update/insert/delete en cada fila. Guardo cual es la ultima versión en el local y hago WHERE Version>X para obtener lo cambiado. El resto es similar al post anterior.