Suite à un crash de disque dur (paix à son âme) je profite de cette install tout fraîche de macosx 10.6 pour bien configurer mon environnement.

Pour mémo:

• Autoriser l’accès SSH sur la machine (pref système > partage > session à distance)
• créer un fichier /.profile
• créer un dossier ~/bin
• dans ~.profile, personnaliser le PATH comme suit :export PATH=~/bin:$PATH
• mettre dans ~/bin les liens symbolique et script utiles
  1. contrôle d’itunes (http://gist.github.com/184838)
  2. contrôle du volume (http://gist.github.com/184840)
  3. cd ~/bin && ln -s /Applications/MAMP/bin/php5/php php
  4. ln -s /Applications/MAMP/Library/bin/mysqladmin mysqladmin
ln -s /Applications/MAMP/Library/bin/mysql mysql
ln -s /Applications/MAMP/Library/bin/mysqldump mysqldump
  5. etc …
• virer les applications hadoc de /usr/bin (php au hasard)

Bon évidemment l’idéal aurait été de faire un Rsync du dernier backup sur la lunxbox mais bon c’ets moins marrant ;-)

Tags: Apple, bonne pratiques, gist, Macosx, MAMP, mysql, PHP, rsync, script, SSH, sysadmin, tips

In many database-based projects you have to show your visitors that this “thing” is similar to this other one. For example, this blog post is similar to this other one. That’s a crucial functionnality and at first sight it’s looks easy to create.

The landscape

let’s start with this basic example :
In a blog, a “blog_post” has many “tag”. These relations are stored in a “blog_post_tag” table. When a blog post is displayed, we want to show the list of similar post to the current blog_post. Our “blog_post_tag” table just store “blog_post_id” and “tag_id“.

The first train of thought

I wanted to get all blog post that have the same associated tag. But, in this database-relation context “the same” may only mean only one of them. Quite annoying. So i finally get something that worked, and that was based on this kind of algorythm:

blogPostTags = $myBlogPost->getTags
postList = array()
foreach(blogPostTags as currentTag)
tempPostList = getAllBlogPostByTag(currentTag)
postList = array_merge(postList, array_diff(tempPostList,postList)
endforeach


Quite awfull isn’t it ? after two hours spent on something else and got back to home and discussed with friends (and whatever you want. Who said IT guy do not have a social life ?!) i just looked back at this problem and finally wrote this :

The proper way


SELECT COUNT(tag_id) as similarity, blog_post_id FROM blog_post_tag
WHERE tag_id IN (
SELECT tag_id FROM blog_post_tag WHERE blog_post_id = ?
)
GROUP BY blog_post_id


And that’s all. This SQL code gives you how many common tag you have between all the post that have at least on tag in common with a specific blog_post (replace ‘?’ by the correct value). Now just have to write it whith the Doctrine syntax ;-)

Conclusion

Just remember : when it sucks, just take a break !

Tags: devloppement, mysql, tips

SQLSTATE[HY000]: General error: 1005 Can't create table
in a
symfony doctrine-build-all-load
command line means you have different integer size in your primary keys references

especially, sfDoctrineGuardPlugin use a integer(4) as primary key definition so, if you want to link your own ‘user’ class to sfGaurdUser class, you’ll have to define sf_uard_user_id as integer(4).

thanks to clear-cache.fr !

Tags: devloppement, mysql, symfony, tips

Je relaye le post “mémo”, de Marc alias Kemar , sur Mysql et particulièrement la “bonne” configuration pour le support d’UTF-8.

À noter plus particulièrement :

[mysqld]
default-character-set = utf8
[mysql]
default-character-set = utf8
[mysqldump]
default-character-set = utf8

et si vous essayez de faire les chose bien avec de l’existant :

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

Tags: mysql, sysadmin, utf-8