Posted in Database

MySQL’s built in FROM_UNIXTIME() function only handles positive numbers however we can still convert negative integers to dates using its handy DATE_ADD() function like so:

1
SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL -13391999 SECOND)

which gives:

1
1969-07-30 10:00:01

Note that this also works with positive numbers, so if your database contains a mixture of both it’s still safe to use.

Credit to user fat_kid for his tutorial here.

Read More »

Posted in Database

Without any adieu what-so-ever, below is a MySQL implementation of PHP’s ucfirst function which capitalizes the first letter of each word in a string.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DELIMITER $$
CREATE FUNCTION CAP_FIRST (INPUT VARCHAR(255))
 
RETURNS VARCHAR(255)
 
DETERMINISTIC
 
BEGIN
    DECLARE len INT;
    DECLARE i INT;
 
    SET len   = CHAR_LENGTH(INPUT);
    SET INPUT = LOWER(INPUT);
    SET i = 0;
 
    WHILE (i < len) DO
        IF (MID(INPUT,i,1) = ' ' OR i = 0) THEN
            IF (i < len) THEN
                SET INPUT = CONCAT(
                    LEFT(INPUT,i),
                    UPPER(MID(INPUT,i + 1,1)),
                    RIGHT(INPUT,len - i - 1)
                );
            END IF;
        END IF;
        SET i = i + 1;
    END WHILE;
 
    RETURN INPUT;
END$$
DELIMITER ;

Use it like so:

1
SELECT CAP_FIRST('my string of words');

and you should get:

My String Of Words

All credit and many thanks for this function to Joezack.

Read More »

Posted (Updated ) in Database, Linux

I’ve been using my Cloud Database Backup script for a few months now for weekly scheduled backups of my MySQL databases to Google Docs. Everything has been going smoothly, however I’m starting to run low on quota. For this reason I decided to look into splitting the SQL dumps into chunks small enough to be convertible and doing an upload-convert rather than a zip upload which will result in literally unlimited, quote free database backups as frequently as I like! The focus of this post though is the actual splitting script which splits a given MySQL dump into chunks of x characters.

As always, download it here.

Read More »

Posted (Updated ) in Database

Earlier today I completed my first project in Django and it came time to do some database optimization. I wanted to get a list of SQL queries executed for each page and a bit of Googling let me to this script on DjangoSnippets. It did everything I needed it to do, however I noticed it interfered with dynamically generated binary file outputs (such as the images made with django-simple-captcha). For this I needed to check if the output was in binary, and if so just return it without attempting to print the SQL log. I found what I was looking for here and after combining the two had the perfect SQL logger! Below is my finished code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
from django.db import connection
from django.template import Template, Context
import string 
#http://djangosnippets.org/snippets/161/class SQLLogMiddleware:
    def process_response ( self, request, response ):
        #Don't print SQL queries for binary outputs!        if istext(response.content) == 0:            return response 
        time = 0.0
        for q in connection.queries:
            time += float(q['time'])
 
        t = Template('''
            <p><em>Total query count:</em> {{ count }}<br/>
            <em>Total execution time:</em> {{ time }}</p>
            <ul class="sqllog">
                {% for sql in sqllog %}
                    <li>{{ sql.time }}: {{ sql.sql }}</li>
                {% endfor %}
            </ul>
        ''')
 
        response.content = "%s%s" % ( response.content, t.render(Context({'sqllog':connection.queries,'count':len(connection.queries),'time':time})))
        return response
 
#http://code.activestate.com/recipes/173220-test-if-a-file-or-string-is-text-or-binary/def istext(s):
    if "\0" in s:
        return 0
 
    if not s:  # Empty files are considered text
        return 1
 
    # Get the non-text characters (maps a character to itself then
    # use the 'remove' option to get rid of the text characters.)
    t = s.translate(string.maketrans("", ""), "".join(map(chr, range(32, 127)) + list("\n\r\t\b"))) 
    # If more than 30% non-text characters, then
    # this is considered a binary file
    if float(len(t))/len(s) >= 0.30:        return 0

To get this working on your site just add it to your MIDDLEWARE_CLASSES in settings.py and make DEBUG is set to True.

Read More »

Posted (Updated ) in Database, Linux

Update 2011-10-12: More storage options! Google Storage, Local HDD, FTP, experimental unlimited Google Docs conversion

Have you ever wanted to ensure that even if your server dies in the most spectacular of ways, your DB is safe? Meet Cloud Database Backup. Cloud Database Backup is a little script I’ve written that allows you to quickly and easily back up your MySQL DBs to the cloud.

Currently you can back your database up to:

  • Amazon S3
  • FTP
  • Google Docs
  • Google Storage
  • Local HDD

Download the script here.

Requirements:

  • MySQL with access to INFORMATION_SCHEMA database
  • php, php5-cli (for S3 backups)
  • python2, python-gdata (for Google Docs backups)

This is an updated version of my old Automatic MySQL Database Backup script that adds support for Google Docs (which now allows uploads of any file type for non-enterprise users), the recently opened Google Storage, as well as local HDD and FTP servers. It does a MySQL dump of each DB separately, zips them all and uploads the zip to your cloud service of choice.

I’ve also added in an experimental ‘db chunker’ which uses this script to split SQL dumps into convertible sizes for Google Docs, allowing you to upload unlimited backups for free; owever due to a reproducible bug in Google Docs, I wouldn’t recommend its use (and it’s disabled by default).

To get started simply update your details in the appropriate places at the top of backup.sh and type ./backup.sh. Presto!

I’ve also included a crontab example to allow automation of backups.

Read More »

Posted (Updated ) in Database, PHP

NOTE: If you’re looking for the Doctrine 2 modules for K3 see this post.

Download the module here.

I’ve previously written a module for Kohana 3 for Doctrine, as well as drivers for Kohana’s Auth and Session modules, however I’ve finally had the time to merge the three and fix up some longstanding issues present with them; namely the folder structure and lack of PDO support. To make my life a little easier I’ve moved the project to Google Code too.

You can find the project here. It’s got Doctrine 1.23 already in there, you should just be able to drop it in and add it to your bootstrap file. It also includes sample schema and data fixtures for the kohana auth and session modules – they can be found in /models/fixtures.

Good luck!

Read More »

Posted (Updated ) in Database, PHP

Update Feb 22 2012: 1.0.8 is out. Now works with and requires Doctrine 2.2. Thanks to Markus for his patch.

Since releasing my original Doctrine 2 module for Kohana 3, I’ve done a bit of reshuffling of folders and added some additional features from my Doctrine 1.2 module. Due to the extent of modifications, I decided to put up a new post with some added information on how to use the new module.

Features:

  • Doctrine 2 integration (obviously)
  • Auth and Session drivers
  • /doctrine controller to load your schema files and generate the DB tables
  • Works in Kohana 3.0 and 3.1

For the impatient ones out there, here’s the download link:
Doctrine 2 Module for Kohana 3

 

As always, install the module (remembering to add it to your bootstrap file) and download the latest copy of Doctrine 2 from here, placing into the /modules/doctrine2/classes/vendor/doctrine folder. It should be noted that my module is tailored to the tarball download and not the SVN or Github ones which have a slightly different folder structure.

Read More »

Posted (Updated ) in Database, PHP

UPDATE 28 Dec 2010: This module is now defunct. Please instead use the Better Doctrine 2 Module for Kohana 3.

For those of you who don’t already know, Doctrine 2 stable was just released. It features what is essentially a complete rewrite and paradigm shift from the 1.2 version and comes with a laundry list of improvements. You can read the official blog post here. To celebrate the occasion I decided to do up a quick Kohana 3 module. Info & specz below:

Firstly the configuration. You’ll need the following files & folders:
/doctrine/Entities (These are essentially (to my meager knowledge) your model files.)
/doctrine/Proxies
/modules/doctrine/classes/doctrine
/modules/doctrine/init.php

Download the latest copy of Doctrine 2 from here and extract into the /modules/doctrine/classes/doctrine folder.

Here’s /modules/doctrine/init.php

<?php
	use Doctrine\ORM\EntityManager,
		Doctrine\ORM\Configuration;
 
	class Doctrine
	{
		private static $_instance = null;
		private $_application_mode = 'development';
		private $_em = array();
 
		/*
		 * Rename $conn_name to whatever you want your default DB connection to be
		 */
		public static function em( $conn_name = 'default' )
		{
			if ( self::$_instance === null )
				self::$_instance = new Doctrine();
 
			return isset(self::$_instance->em[ $conn_name ])
				? self::$_instance->em[ $conn_name ]
				: reset(self::$_instance->_em);
		}
 
		public function __construct()
		{
			require __DIR__.'/classes/doctrine/Doctrine/Common/ClassLoader.php';
 
			$classLoader = new \Doctrine\Common\ClassLoader('Doctrine', __DIR__.'/classes/doctrine');
			$classLoader->register();
			//This allows Doctrine-CLI tool & YAML mapping driver
			$classLoader = new \Doctrine\Common\ClassLoader('Symfony', __DIR__.'/classes/doctrine/Doctrine');
			$classLoader->register();
			//Load entities
			$classLoader = new \Doctrine\Common\ClassLoader('Entities', APPPATH.'doctrine');
			$classLoader->register();
 
			//Set up caching method
			$cache = $this->_application_mode == 'development'
				? new \Doctrine\Common\Cache\ArrayCache
				: new \Doctrine\Common\Cache\ApcCache;
 
			$config = new Configuration;
			$config->setMetadataCacheImpl( $cache );
			$driver = $config->newDefaultAnnotationDriver( APPPATH.'doctrine/Entities' );
			$config->setMetadataDriverImpl( $driver );
			$config->setQueryCacheImpl( $cache );
 
			$config->setProxyDir( APPPATH.'doctrine/Proxies' );
			$config->setProxyNamespace('Proxies');
			$config->setAutoGenerateProxyClasses( $this->_application_mode == 'development' );
 
			$dbconfs = Kohana::config('database');
 
			foreach ( $dbconfs as $conn_name => $dbconf )
				$this->_em[ $conn_name ] = EntityManager::create(array(
					'dbname' 	=> $dbconf['connection']['database'],
					'user' 		=> $dbconf['connection']['username'],
					'password' 	=> $dbconf['connection']['password'],
					'host' 		=> $dbconf['connection']['hostname'],
					'driver' 	=> 'pdo_mysql',
				), $config);
		}
	}

Once the module is enabled, you can access Doctrine from anywhere with the following code:

Doctrine::em()

The module supports multiple databases and uses the Kohana database config file for connection details. To grab any specific database use the following (where connection_name is the name specified in the config file)

Doctrine::em('connection_name')

Download the complete module here.

Hope this helps.

Read More »

Posted (Updated ) in Database, PHP

Here’s my second Doctrine 1.2 driver for Kohana 3, this time for the Session class. I wrote this because I don’t particularly like the Kohana Database or ORM classes (that or I just don’t want to take the time to learn them!) and it’s released under a creative commons v3 licence So here it is. Enjoy!

Kohana 3 Doctrine Session Driver 1.02 See here for details

UPDATE 22 Oct 2010: Bugfix release – should actually work now 🙂
UPDATE 03 Jan 2011: Fixed rare bug in DB cleaning function.
UPDATE 21 Feb 2011: New module at a new home! See here for details

Read More »

Posted (Updated ) in Database, PHP

It’s probably become apparent by now that I like Doctrine. It’s quick, it’s easy and it works with everything. So here’s a Doctrine driver for the Kohana Auth module, allowing you to use Doctrine instead of Kohanas integrated ORM.

Download: Kohana Auth Doctrine Driver 1.03 here

Installation instructions:
Unzip the module to your /modules folder.
Enable the module by adding

'auth_doctrine' => MODPATH.'auth_doctrine',  // Doctrine driver for Auth module

to the modules array in the following file:
Kohana 3:/bootstrap.php
Kohana 2: /config/config.php

UPDATE Jul 15 2010: Fixed incorrect user login bug. Please redownload
UPDATE Dec 22 2010: Fixed ‘remember’ session bug.
UPDATE Jan 3 2011: Fixed bug if calling Auth::instance()->logged_in(‘role_name’). Thanks to freenode #doctrine user SirFunk for this one – good catch!
Update Feb 21 2011: New module at a new home! See here for details

Read More »