Drop all tables in a database

Sometimes it can be useful to completely drop all tables in a database, for example to reset a DB to a previous version from a backup.

After checking that you are doing this on the right database and that you know what you are doing, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
 FROM information_schema.tables
 WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;

SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

All credits goes to this guy on Stackoverflow.

Configure SFTP access to your Ubuntu server through SSH

Sometimes you have to give your users a way to manage files on your server. A typical scenario is a web server where your users manage their websites by themselves.

The classical approach in this scenario was to use FTP to give file management capabilities to your users, but it has many drawbacks:

  1. You have to provision and maintain a new service on your server;
  2. FTP is an annoying protocol from a firewall configuration point of view;
  3. FTP is not encrypted by default, and you have to put some effort to configure an FTP server which is protected from sniffing.

If you are a lazy sysadmin like me you’ll prefer to use a service you already have, which is encrypted by default and do not require a special firewall configuration other than the port 22 you are already using.

ssh to the rescue!Read More »

A simple script to update a DB migration in Django 1.7+

During the development of a Django model on your local machine is it often necessary to refine the most recent migration to cope with updates to the model, without polluting the migrations of the app with a new migration for each local update.

South had the update flag for the schemamigration command, but I didn’t find a similar functionality in the Django builtin makemigrations command introduced in Django 1.7.

So I put togheter a simple bash script to automate the process.

Read More »