Month: June 2012

Making MySQL do what you want.

Just some miscellaneous code that I’ve been using to update fields in my database. Basically lots of trimming and concatenating. A little substring manipulating thrown in for good measure.


SELECT *  FROM `words_for_slps` WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'

UPDATE`words_for_slps` SET `phonemes` = TRIM(TRAILING 's' FROM phonemes) WHERE `F` REGEXP 'z$' AND `phonemes` REGEXP 's$' AND `word` = 'Ares'

UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'z') WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'

UPDATE`words_for_slps` SET `phonemes` = TRIM(TRAILING 's hz' FROM phonemes) WHERE `F` REGEXP 'hz$' AND `phonemes` REGEXP 'hz$'

UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'ʃ ɛ z') WHERE `F` REGEXP 'z$'

UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'z') WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'
UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,' ɛ s') WHERE `F` REGEXP 's' AND `phonemes` REGEXP 'n$'

UPDATE `words_for_slps` SET `phonemes` = TRIM(LEADING 'c' FROM phonemes) WHERE `word` REGEXP '^c' AND `I` LIKE 'c'
UPDATE `words_for_slps` SET `phonemes` = CONCAT('k',phonemes) WHERE `phonemes` REGEXP '^ ' AND `I` LIKE 'c'

UPDATE `words_for_slps` SET `phonemes` = TRIM(LEADING 'k' FROM phonemes) WHERE `word` REGEXP '^cy' AND `phonemes` REGEXP '^k'
UPDATE `words_for_slps` SET `phonemes` = CONCAT('s',phonemes) WHERE `word` REGEXP '^cy' AND `phonemes` REGEXP '^ '

UPDATE `words_for_slps` SET `phonemes` = REPLACE(phonemes, 't ɛ,i  d', 't ə d') WHERE `phonemes` REGEXP 't ɛ,i  d$' AND `pronunciation` REGEXP 't u d$'

UPDATE `words_for_slps` SET `phonemes` = REPLACE(phonemes, 'n z', 'nz') WHERE `pronunciation` REGEXP 'n z$' AND `phonemes`REGEXP 'n z$'

I used these select statements to look for words that need some manual cleanup.


SELECT *  FROM `words_for_slps` WHERE `F` <> SUBSTRING_INDEX(phonemes, ' ', -1)
SELECT *  FROM `words_for_slps` WHERE `word` REGEXP '^w' AND `pronunciation` NOT REGEXP '^w' AND `pronunciation` IS NOT NULL

This works when I want to update the fields in one table with values from fields in another table.


UPDATE words_for_slps, words_for_slpsBAK12
SET words_for_slps.grade = words_for_slpsBAK12.grade
WHERE words_for_slps.word_id = words_for_slpsBAK12.word_id;

Even though I’m only updating one table you’d think this should work, but it doesn’t.


UPDATE `words_for_slps` 
LEFT JOIN `words_for_slpsBAK12` ON `words_for_slps.word_id` = `words_for_slpsBAK12.word_id`
SET `words_for_slps.grade` = `words_for_slpsBAK12.grade`

Finding Directories in iOS

I created a Utilities singleton to keep things I refer to often. These are the methods I use for referring to directories.


#pragma mark - Application's Documents directory
// Directory locations
+ (NSString *)applicationCachesDirectory {
    return [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject];
}

+ (NSString *)applicationDocumentsDirectory {
    return [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
}

+ (NSString *)applicationLibraryDirectory {
    
    return [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
}

Here’s one for retrieving the contents of a file stored in the Caches Directory. Notice how it uses [self applicationCachesDirectory] to get the location of the directory.


// Cached files for results
+ (NSString *)cachedFilePath:(NSString *)fileName {
    NSString *pathComponent = [NSString stringWithFormat:@"%@.txt",fileName];
    NSString *filePath = [[self applicationCachesDirectory] stringByAppendingPathComponent:pathComponent];
    return filePath;
}

If I want to store the results in the Documents directory, I’ll use this to construct the path.


        NSString *resultsFilePath = [ [Utilities applicationDocumentsDirectory] stringByAppendingPathComponent:[self formattedHTMLFileName:@"Results"] ];

Here’s a slightly more complicated example where I retrieve the contents of a cached file. Note that it makes use of cachedFilePath:fileName as described above.


+ (NSString *)cachedFileContents:(NSString *)fileName {
    NSStringEncoding encoding; NSError* error = nil;
    NSString *text = [NSString stringWithContentsOfFile:[self cachedFilePath:fileName] usedEncoding:&encoding error:&error];
    return text;
}

Here’s another example. This time I’m loading a string with the contents of the FullResults file.


 NSStringEncoding encoding;
    NSError* error = nil;
    NSString *resultsText = [NSString stringWithContentsOfFile:[Utilities cachedFilePath:@"FullResults"] usedEncoding:&encoding error:&error];

Rename a bunch of files

Photoshop mangles file names when you run batch saves on them, so I’ve had to rename a bunch of files after processing. Here’s what I do from the shell prompt to rename a bunch of files in a PictsFolder.

Create a file called rename.sh


#!/bin/bash
cd /Users/gmiller/Desktop/PictsFolder
mv 1.png a.png
mv 2.png b.png
exit

chmod it to be executable. I use chmod 777 rename.sh because I’m lazy. Then run it from the command line.

jscarry$ /Users/gmiller/Files/renamePicts.sh
Be sure to escape special characters like spaces in your file names.

e.g. File\ Name\ With\ Spaces.png

JPEG Image Compression

We’ve made a bunch of games for iPads and based on reading the docs made all of our images PNGs. But that resulted in huge file sizes, especially for games that use photos. So we switched to JPEGs for the content. Buttons are still PNGs.

In our first batch of games we used PhotoShop at 30% quality setting. The pictures are OK looking but you can see some artifacts, especially when there are large areas of the same color, like skies. For our current game, we experimented with ImageOptim. The new images are virtually indistinguishable from the originals. This is significantly smaller than Photoshop—but not as small as you can get with a lower quality setting in Photoshop. Since there are no sounds in this game, we’ll probably go with ImageOptim.

Here are some stats for 3673 pictures at 644×498.


Originals:         1,140.0 MB 
ImageOptim 80%       249.6 MB
ImageOptim 2nd Pass  238.9 MB

Photoshop 80%        323.9 MB
Photoshop 50%        249.8 MB
50% with ImageOptim  241.3 MB
50% with 2x IO       241.0 MB
Photoshop 30%        166.7 MB

ImageOptim only has an 80% setting and running two passes gets you as small as you are going to get. I think, but can’t verify, that IgageOptim on JPEGs doesn’t do any compression per se. It just strips out and cleans up overhead and doesn’t affect the image quality. In other words, it’s lossless compression.

UPDATE: I tried this on a bunch of PNGs and got less dramatic, but still significant, results.


438 Images: 98.9 MB --> 81.5 MB = 18%
 68 Images: 19.3 MB ==> 15.8 MB = 18%

I also compressed a file with a mix of JPEGs, PNGs, and PDFs.


145 Images: 65.9 --> 38.8 MB = 39%

How to center an image with a caption.

E6-B

Let’s check that wind correction angle.

Use this code:


<div class="centered"><img class="centered" src="/images/696866-spock.jpg" alt="E6-B" /><p class="caption">Let’s check that wind correction angle.</p>
</div>

Note that the whole thing is wrapped in a div and the caption is in a paragraph.

The CSS for the classes is:

Image centering in a paragraph with padding.


img.centered {
  display: block;
  margin-left: auto;
  margin-right: auto;
}

Caption


.caption {
  text-align: center;
  margin-top: -4px;
  font-style: italic;
  font-size: 80%;
}