importing external data to mySQL

For a project i was working on I had to import a csv file provided by booking.com
The constraint of this file is that i wanted to be able to allow to reimport that file as booking.com publishes updates with a list of new hotels quite often. That means that if another table in the database relies on the data of the imported file we can't create a normal foreign key as when we reimport the file, we can't guarantee the primary key will remain identical.

I decided to create the foreign key to a hash of the combination of town name and country name, why not only the town name? as for example London exists in England and in Canada, which would cause a problem as we want the foreign key to have only one option in the imported table.

Symfony doesn't know how to handle none numerical values of foreign key, hence we need to do the task of the association between the tables ourself.

in my schema.yml i configured the table PromoteTown that has a relation to BookingComHotels


PromoteTown:
  columns:
    town_hotel_id:
      type: BINARY(16)
      notnull: true
      unique: true
    position:
      type: integer(4)
  relations:
    BookingComHotels: { local: town_hotel_id, foreign: id_hash, onDelete: cascade, foreignAlias: PromoteTowns }


and in the model i have a method to insert to PromoteTown, the $this is related to BookingComHotels as this function is in BookingComHotels class.



        $hash = $this->getIdHash();
        $pt = new PromoteTown();
        $pt->type = '2';
        $pt->town_hotel_id = $hash;
        $pt->save();
        $pt->link('BookingComHotels', $hash);

unique on multiple columns

In Doctrine documentation I couldn't come across on how to mark in YML file to have unique on 2 columns.
The idea is as this example demonstrates of setting the category_id and user_id fields as unique:

Entry:
columns:
project_name:
type: string(255)
category_id:
type: integer
notnull: true      
user_id:
type: integer(4)
notnull: true
indexes:
category_user_idx:
fields:
user_id: []
category_id: []
type: unique

internalization of mysql

On a recent project I had a problem to import into MySQL a file tab delimited formatted with utf-8. I found that I have to set the collation of the column to utf-8, but there are several. according to what i found in other blogs, i tried utf8_unicode_ci, but after few trials and that i got gibberish for the special characters I changed it to utf8_bin and it works fine.

A later addition:
I repeat what I thought works a bit later and realised I can't repeat my success. It seems only writing php script solves the problem. Another thing if you copy utf8 data and use phpmyadmin it's important to verify the page's encoding is indeed utf8.

how to use mySQL of MAMP

Navigating to http://localhost:8888/MAMP/ displays the Start page of MAMP with the mySQL settings.

In order to setup symfony to connect to mySQL database we need to run from the command this

symfony configure:database 'mysql:host=localhost;port=8889;dbname=test' root root

test is database name as created for my symfony project using the phpMyAdmin on the MAMP page. By default the port for MySQL is 8889, you can verify it on the MAMP start page. Same with the username and the password, which in this example is root both for username and password.

Configure Symfony project on MAMP

I try to learn symfony framework and figure out, the easiest to install it on my Mac would be to use MAMP, the current MAMP version I use is 1.8.2

1. The symfony package is already installed as I used it when practising the jobeet demo. so in order to create a new project first we want to create a new folder. As we run the symfony commands from the shell we need to open terminal. Then using the mkdir command we create a new folder for our project.
2. We need to be within the project folder, using cd command and we run the symfony command php /usr/bin/symfony generate:project [project name] in this case I created a symbolic link from the installation of symfony to /usr/bin this is necessary only first time after the creation of the project will have a shortcut to symfony from the project folder.
3. To create new domain for your web development project, you need to update the following files: /etc/hosts and conf/apache/httpd.conf (in your MAMP folder) .
4. After changing the server name you need to restart the MAMP service.

The next step is configuring a repository to store data for the web application. I use mySQL that comes with the MAMP, by defaults the port is 8889 and you need to remember that when coming to configure the database in symfony. I'll talk about it including how to use Doctrine in my next post.