Archive for the ‘MySQL’ Category
E/R Zero or One to Many relationships
While 1:n, 1:1 and n:m relationships are thoroughly used to design logical database models, there another another kind, {0|1}:n that may come in handy in some cases. Lets analyze this relationship carefully.
Imagine a relationship between students and classrooms. Every classroom can have N students, meaning this zero, one, or many pupils. From the other side, every student must have a classroom, otherwise she won’t be allowed to study. This is a classic 1:n relationship, used in most of the real cases that we may encounter.
But what if, let’s say, a student had finished her studies and, however, wasn’t deleted from the database? (just to keep the records and other sensible information). In that case, the statement ‘every student must have one and only one classroom’ would be wrong. Instead, we could say ‘every student can have one classroom or none’. Now we are talking about zero or one to many relationships.
MySQL clean reinstall
To perform a clean reinstall of the MySQL server (which regenerates start scripts, databases and config files) use:
1 2 3 4 | sudo apt-get install mysql-server</li> sudo apt-get remove --purge mysql-server</li> sudo apt-get autoremove mysql-server</li> sudo apt-get install mysql-server</li> |
Some times is useful!
Implementing a database cubes system on MySQL
I’ve already talked about how I solved the problem of managing huge amounts of data in my last post. Now, I’m going to explain how to implement one of the solutions found in order to comfortably face this continuously increasing avalanche of information.
Let’s imagine that, as I explained before, I have separated data input into tables coded by a station ID. All of them are stored in the same database for maintenance simplicity sake. So, we have the ‘database’ in which there are hundreds of tables called ‘Station_XYZ’. Every table has the same structure: to simplify, SensorID, Time (UNIX), Value. All right then, time to make cubes!
Managing big data sets with MySQL
I’m currently working on a database which stores, literally, a million of new data registers every day. They come from several hundred weather stations spread along the world, and need to be processed on real time. That’s a huge amount to be managed with a simple – but powerful – MySQL server, so I’ve had to design and implement a few tricks in order to succeed.
In this post I’d like to talk about the main three techniques we’ve used to engage our high performance database, and the reasons to choose these solutions.
First of all, MySQL innodb tables start degrading at some million registers (7-10M on my current server, but not tested further), so the first though I had was: we need to divide if we want to conquer. Then, after some thinking about, I decided to split the big table into small chunks, and use a table for each station. That made the whole problem more affordable, and tables smaller enough not to become scary: the new problem was how to store about 5 M registers per year per table in our database.
Of course, some logic in both the application controller and the data input server was needed to correctly place data registers. Additionally, tables get often fragmented so some cron jobs were needed to keep the database running smoothly. But there was still the problem of data growing. As long as registers were separated depending on the station, the scalability problem was solved: more tables can be added, even more servers with ranges of stations and a hash function to calculate what server is a given station hosted in. Once again were we talking about several million registers per year per table, and with a standart MySQL 5.1 server (the main problem with an start-up company tends to be funding, so we didn’t have anything better).
Creating Triggers in MySQL from PHP
On my last project, I needed to create tables periodically on a database, to partition data in several identical tables, named according to the year quarter. Instead of using MySQL 5.1 automatic partition, I preferred doing it in a more manual way, to allow accessing individual partitions if suitable to speed up queries.
So I wrote a php script that would run with a cron job and every three months, create a new table, add it some triggers, and remap a MyISAM merged table on another database. Clear and easy, at the beginning. Everything went right until I went for the triggers creation…
Avoid deletions with triggers and MySQL
Some times, we store valuable data that we would never want to lose. This is a particular fact when working with data for scientific, statistical purposes, like environmental analysis.
In such a situation, we can rely on our software, but… what happens if any error is introduced on the application layer? Some valuable registers could be lost and hard to identify and recover later. To avoid this effect, we’d rather prefer to implement some constraints in our database. I wouldn’t think that all the applications that connect to our database are going to be free of bugs, and our data is so valuable!
PHP class dbhandler to deal with databases
Notice: Article only available in Spanish!
Hemos visto en publicaciones anteriores cómo conectar a una base de datos mysql desde php, y cómo usar la función mysql_query para consultar datos. También hemos visto algunas nociones de seguridad en scripts y aplicaciones php. Ahora vamos a ver cómo encapsular estas funciones en una práctica clase que nos permitirá instanciar objetos dbhandler y trabajar cómodamente con ellos.
Parto de que el lector conoce el paradigma de la Programación Orientada a Objetos. Básicamente, una clase es un molde que tiene ciertas peculiaridades como métodos y atributos, que pueden ser públicos (de libre acceso) o privados (sólo accesibles dentro de la propia clase). Con esta clase, se pueden instanciar objetos pertenecientes a dicha clase. si estuviéramos en la arena de la playa con un cubo, el cubo sería la clase, y las torres de arena serían los objetos o instancias del cubo de playa.
Reading data from a MySQL database with PHP
Notice: article only available in Spanish!
Ya he explicado en otro post cómo conectar a una base de datos mysql desde php. Ahora voy a explicar cómo trabajar con esta base de datos, en concreto mostrar los datos de una tabla.
Parto de la premisa de que se ha aprendido a utilizar el script anteriormente citado para la conexión y selección de base de datos, y que se ha configurado correctamente para acceder a una BD donde existe la tabla ‘superheroes’, con los campos ‘id’, ‘name’, ‘special_powers’.
Vamos allá : leemos los datos de la tabla. Para ello, usamos la función php mysql_query(); para ejecutar código sql en el servidor de bases de datos. Vamos a leer los campos importantes (en este caso, los tres) de la tabla superheroes.
Connecting to a MySQL database from PHP
Notice: Article only available in Spanish!
El primer paso para trabajar con scripts y aplicaciones en php que conectan con bases de datos mysql, es crear una conexión. Una conexión en php devuelve un identificador que será utilizado para cada transacción, consulta, etc, que se realice sobre la base de datos.
Para comenzar, hacen falta tres datos del proveedor de servicios de alojamiento: nombre de la base de datos, nombre de usuario y contraseña de acceso. En php desde la versión 3 existe la función mysql_connect();
