When searching for text snippets in sql databases you might want to rank the results according to “how good did it match”. And: the ranking shouldn’t make the query slower.
Let’s take a simple example using the LIKE operator. (I know, FTS does a better job, but let’s stick to like for now).
Assume the search expression ‘a bc de’ and a table ‘my_table’ with text columns ‘title’ and ‘description’.
We want to find all rows with ‘title’ matching all three blank-separated parts of the search term:
SELECT rowid, title
FROM my_table
WHERE (title LIKE '%a%' AND title LIKE '%bc%' AND title LIKE '%de%')
To sort them, we apply a bonus for parts matching the column start:
SELECT rowid, title,
-- column start bonus
LIKE('a%', title) +
LIKE('bc%', title) +
LIKE('de%', title) +
0 AS bonus
FROM my_table
WHERE ((title LIKE '%a%') AND (title LIKE '%bc%') AND (title LIKE '%de%'))
ORDER BY bonus DESC, title ASC, rowid ASC
Next, we’d like to add a (somewhat smaller) bonus for word-starts:
SELECT rowid, title,
-- column start bonus
LIKE('a%', title) * 2 +
LIKE('bc%', title) * 2 +
LIKE('de%', title) * 2 +
-- word start bonus
LIKE('% a%', title) * 1 +
LIKE('% bc%', title) * 1 +
LIKE('% de%', title) * 1 +
0 AS bonus
FROM my_table
WHERE ((title LIKE '%a%') AND (title LIKE '%bc%') AND (title LIKE '%de%'))
ORDER BY bonus DESC, title ASC, rowid ASC
Rows matching the three terms in order get an even bigger bonus:
SELECT rowid, title,
-- correct order bonus
LIKE('%a%bc%de%', title) * 5 * 3 +
-- column start bonus
LIKE('a%', title) * 2 +
LIKE('bc%', title) * 2 +
LIKE('de%', title) * 2 +
-- word start bonus
LIKE('% a%', title) * 1 +
LIKE('% bc%', title) * 1 +
LIKE('% de%', title) * 1 +
0 AS bonus
FROM my_table
WHERE ((title LIKE '%a%') AND (title LIKE '%bc%') AND (title LIKE '%de%'))
ORDER BY bonus DESC, title ASC, rowid ASC
And finally adding the match on ‘description’ secondary:
SELECT rowid, title, description,
-- title is primary match:
-- correct order bonus
LIKE('%a%bc%de%', title) * 50 * 3 +
-- column start bonus
LIKE('a%', title) * 20 +
LIKE('bc%', title) * 20 +
LIKE('de%', title) * 20 +
-- word start bonus
LIKE('% a%', title) * 10 +
LIKE('% bc%', title) * 10 +
LIKE('% de%', title) * 10 +
-- description is secondary match:
-- correct order bonus
LIKE('%a%bc%de%', description) * 5 * 3 +
-- column start bonus
LIKE('a%', description) * 2 +
LIKE('bc%', description) * 2 +
LIKE('de%', description) * 2 +
-- word start bonus
LIKE('% a%', description) * 1 +
LIKE('% bc%', description) * 1 +
LIKE('% de%', description) * 1 +
0 AS bonus
FROM my_table
WHERE ((title LIKE '%a%') AND (title LIKE '%bc%') AND (title LIKE '%de%'))
OR ((description LIKE '%a%') AND (description LIKE '%bc%') AND (description LIKE '%de%'))
ORDER BY bonus DESC, title ASC, description ASC, rowid ASC
You get the idea.
Funny thing is – the whole ranking logic doesn’t hit performance (at least for small texts in the two columns)!
So, key is:
- scan the table only once to find match candidates using the LIKE operator,
- use the LIKE function(!) plus weighting-factors to compute a bonus for each hit,
- evtl. add secondary matching columns.
P.S.: This post was inspired by a chat with Deesa on the way home riding False Creek Ferry.
When searching for text snippets in sql databases you might want to rank the results according to "how good did it match". And: the ranking shouldn't make the query slower.
Let's take a simple example using the LIKE operator. (I know, FTS does a better job, but let's stick to like for now).
Assume the search ...
by far not as powerful as the Fastest Fourier Transform in the West but maybe sometimes useful for a quick data analysis or de-noising. Reads stdin and writes to stdout.
Algorithm taken from Meyberg, Vachenauer: Höhere Mathematik II and ported to plain ruby myself.
< ![CDATA[
#!/usr/bin/env ruby
require 'complex'
class Array
# DFT and inverse.
#
# Algorithm from
# 'Meyberg, Vachenauer: Hoehere Mathematik II, Springer Berlin, 1991, page 332'
#
# See http://blog.mro.name/2011/04/simple-ruby-fast-fourier-transform/
#
def fft doinverse = false
src = self
# raise ArgumentError.new "Expected array input but was '#{src.class}'" unless src.kind_of? Array
n = src.length
nlog2 = Math.log( n ) / Math.log( 2 )
raise ArgumentError.new "Input array size must be a power of two but was '#{n}'" unless nlog2.floor - nlog2 == 0.0
n2 = n / 2
phi = Math::PI / n2
if doinverse
phi = -phi
else
src.collect!{|c| c /= n.to_f}
end
# build a sine/cosine table
wt = Array.new n2
wt.each_index { |i| wt[i] = Complex.new Math.cos(i * phi), Math.sin(i * phi) }
# bit reordering
n1 = n - 1
j = 0
1.upto(n1) do |i|
m = n2
while j >= m
j -= m
m /= 2
end
j += m
src[i],src[j] = src[j],src[i] if j > i
end
# 1d(N) Ueberlagerungen
mm = 1
begin
m = mm
mm *= 2
0.upto(m - 1) do |k|
w = wt[ k * n2 ]
k.step(n1, mm) do |i|
j = i + m
src[j] = src[i] - (temp = w * src[j])
src[i] += temp
end
end
n2 /= 2
end while mm != n
src
end
end
class String
# parse Complex.new.to_s
def to_c
m = @@PATTERN.match self
return nil if m.nil?
Complex.new m[1].to_f, m[2].to_f
end
private
# float_pat = /(-?\d+(?:\.\d+)?(?:e[+-]?\d+)?)/
@@PATTERN = /^[ \t\r\n]*(-?\d+(?:\.\d+)?(?:e[+-]?\d+)?)?\s*((?:\s+|[+-])\d+(?:\.\d+)?(?:e[+-]?\d+)?i)?[ \t\r\n]*$/
end
values = []
$stdin.each_line do |l|
c = l.to_c
if c.nil?
$stderr.puts "unmatched '#{l}'"
else
values < < c
end
end
INVERSE = ARGV[0] == 'inverse'
$stderr.puts INVERSE ? 'inverse' : 'forward'
values.fft(INVERSE).each {|i| puts "#{i.real} #{i.image}i"}
]]>
WordPress messes up the angle brackets as usual, but there’s a gist for that.
by far not as powerful as the Fastest Fourier Transform in the West but maybe sometimes useful for a quick data analysis or de-noising. Reads stdin and writes to stdout.
Algorithm taken from Meyberg, Vachenauer: Höhere Mathematik II and ported to plain ruby myself.
< ![CDATA[
#!/usr/bin/env ruby
require 'complex'
class Array
# DFT and inverse.
#
# Algorithm from
# ...
¶
Posted 22 März 2011
† Marcus Rohrmoser §
Artikel auf deutsch § offtopic
‡
°
Tagged: Bayern, Bayernkurier, Burghausen, CSU, Elisabethkapelle, Frühgotik, Geschichte, Heinrich XIII, Islam, Kunstgeschichte, Laptop, Lederhose, Rant
Loading UIImages automatically in high-resolution works fine for locally stored images – but if you want to fetch them via remote URL you have to code yourself.
A simple, blocking but backward compatible (iOS >= 3.0, maybe even 2.0 but untested) implementation could look like this:
@implementation UIImage (MRORemote)
// add the @2x filename suffix
+(NSURL *)url2x:(NSURL *)url
{
NSString *path = url.path;
NSAssert(path != nil, @"");
NSString *last = path.lastPathComponent;
NSString *ext = path.pathExtension;
NSAssert(last != nil, @"");
NSAssert(ext != nil, @"");
NSString *part = [last substringToIndex:MAX(0, last.length - ext.length - 1)];
return [NSURL URLWithString:[NSString stringWithFormat:@"%@@2x.%@", part, ext] relativeToURL:[url absoluteURL]];
}
+(UIImage *)imageWithContentsOfURL:(NSURL *)url probe2x:(BOOL)probe2x
{
if ( url == nil )
return nil;
UIScreen *screen = [UIScreen mainScreen];
const CGFloat scale = [screen respondsToSelector:@selector(scale)] ? [screen scale] : 1.0f;
if ( probe2x && 2.0f == scale && [UIImage respondsToSelector:@selector(imageWithCGImage:scale:orientation:)] ) {
UIImage *img = nil;
NSData *raw = [NSData dataWithContentsOfURL:[UIImage url2x:url]];
if ( raw != nil )
img = [UIImage imageWithData:raw];
if ( img != nil )
img = [UIImage imageWithCGImage:img.CGImage scale:scale orientation:img.imageOrientation];
if ( img != nil )
return img;
NSAssert(raw == nil && img == nil, @"");
}
// MRLogD(@"loading %@", [url absoluteURL]);
NSData *raw = [NSData dataWithContentsOfURL:url];
if ( raw == nil )
return nil;
return [UIImage imageWithData:raw];
}
@end
Use at your will but without any warranty.
Loading UIImages automatically in high-resolution works fine for locally stored images - but if you want to fetch them via remote URL you have to code yourself.
A simple, blocking but backward compatible (iOS >= 3.0, maybe even 2.0 but untested) implementation could look like this:
@implementation UIImage (MRORemote)
// add the @2x filename suffix
+(NSURL *)url2x:(NSURL ...
The iPhone4 comes with a super high-res display and to leverage that encourages App Developers to provide all artwork twofold – once “normal” and once in double resolution named equally with a “@2x” suffix.
To ease my designers’ life and avoid confusion (and designers are easily confused I found) I ask them to provide the high-res artwork only and I scale it down myself. And as this is a reoccuring task, I automated via a Makefile like this:
#!/usr/bin/make
# Make help: http://www.gnu.org/software/make/manual/html_node/Phony-Targets.html#Phony-Targets
# Requires ImageMagick, Installation per macport: $ sudo port install imagemagick +no_x11
CONVERT := convert
# Where are the images?
ASSETS_DIR := .
# Which ones? All @2x.png plus twins without @2x.png
ASSETS_HIGH := $(wildcard $(ASSETS_DIR)/*@2x.png)
ASSETS_LOW := $(patsubst %@2x.png,%.png,$(ASSETS_HIGH))
# The scaling command
%.png: %@2x.png
convert $< -resize 50% $@
assets: $(ASSETS_LOW)
clean:
-rm $(ASSETS_LOW)
See also my link collection about high-res images and my general Xcode project setup.
The iPhone4 comes with a super high-res display and to leverage that encourages App Developers to provide all artwork twofold - once "normal" and once in double resolution named equally with a "@2x" suffix.
To ease my designers' life and avoid confusion (and designers are easily confused I found) I ask them to provide the ...
again, for my custom install location I need to prepare:
- cleanly uninstall and remove cruft:
$ dir=/Users/Developer.SnowLeopard
$ sudo sh $dir/Library/uninstall-devtools
$ sudo rm -r $dir/*
$ sudo mv /Developer /Developer.deleteme
- then do the custom-location install and
- finally restore some hotfix softlinks:
$ dir=/Users/Developer.SnowLeopard
$ sudo ln -s $dir/Platforms /Developer/Platforms
$ sudo ln -s $dir/SDKs /Developer/SDKs
$ sudo ln -s $dir/Applications/Xcode.app /Developer/Applications/Xcode.app
Not removing the cruft will get you this quite terse error message:

XCode 3.2.4 install without prior cleaning
again, for my custom install location I need to prepare:
cleanly uninstall and remove cruft:
$ dir=/Users/Developer.SnowLeopard
$ sudo sh $dir/Library/uninstall-devtools
$ sudo rm -r $dir/*
$ sudo mv /Developer /Developer.deleteme
then do the custom-location install and
finally restore some hotfix softlinks:
$ dir=/Users/Developer.SnowLeopard
$ sudo ln -s $dir/Platforms /Developer/Platforms
$ sudo ln -s $dir/SDKs /Developer/SDKs
$ sudo ln -s $dir/Applications/Xcode.app /Developer/Applications/Xcode.app
Not removing the cruft ...
UILabel’s font property accepts UIFonts – but strange enough there’s no way to get a custom loaded CGFont (from a ttf or otf file) converted into such an UIFont. You’re stuck with the iPhone’s pre-installed fonts – at least when you have to support iOS 3.0 devices.
After googling a bit and searching Stackoverflow I found the solutions presented there not ideal or great, but too heavy weight.
So I inherited UILabel with a very lean custom class UILabelWithCGFont and overloaded it’s drawTextInRect: method like this:
-(void)drawTextInRect:(CGRect)rect
{
MRLogD(@"(%f,%f) (%f,%f)", rect.origin.x, rect.origin.y, rect.size.width, rect.size.height);
if ( _CGFont == NULL ) {
[super drawTextInRect:rect];
return;
}
NSAssert(_mapping != NULL, @"Mapping function pointer not set.");
// prepare the target graphics context.
const CGContextRef ctx = UIGraphicsGetCurrentContext();
CGContextSaveGState(ctx);
{
// prepare the glyphs array to draw
const NSString *txt = self.text;
const size_t glyphCount = txt.length;
CGGlyph glyphs[glyphCount];
{
// turn the string txt into glyphs (indices into the font):
// give non-allocating unicode character retrieval a try:
const UniChar *raw_unichars = CFStringGetCharactersPtr( (CFStringRef)txt );
const UniChar *unichars = raw_unichars == NULL ? malloc( glyphCount * sizeof(UniChar) ) : raw_unichars;
NSAssert(unichars != NULL, @"unichars not allocated");
if ( raw_unichars == NULL )
CFStringGetCharacters( (CFStringRef)txt, CFRangeMake(0, txt.length), (UniChar *)unichars );
for ( int i = glyphCount - 1; i >= 0; i-- )
glyphs[i] = _mapping(unichars[i]);
if ( raw_unichars == NULL )
free( (void *)unichars );
}
CGContextSetFont(ctx, _CGFont);
CGContextSetFontSize(ctx, self.font.pointSize);
CGContextSetTextMatrix( ctx, CGAffineTransformMake(1.0, 0.0, 0.0, -1.0, 0.0, 0.0) );
// first print 'invisible' to measure size:
CGContextSetTextDrawingMode(ctx, kCGTextInvisible);
const CGPoint pre = CGContextGetTextPosition(ctx);
CGContextShowGlyphs(ctx, glyphs, glyphCount);
const CGPoint post = CGContextGetTextPosition(ctx);
// restore text position
CGContextSetTextPosition(ctx, pre.x, pre.y);
// centered horizontal + vertical:
NSAssert( (int)rect.origin.x == 0, @"origin.x not zero" );
NSAssert( (int)rect.origin.y == 0, @"origin.y not zero" );
NSAssert(self.baselineAdjustment == UIBaselineAdjustmentAlignCenters, @"vertical alignment not 'center'");
NSAssert(self.textAlignment == UITextAlignmentCenter, @"horizontal alignment not 'center'");
const CGPoint p = CGPointMake( ( rect.size.width - (post.x - pre.x) ) / 2, (rect.size.height + self.font.pointSize + pre.y) / 2 );
// finally render it to the graphics context:
CGContextSetTextDrawingMode(ctx, kCGTextFill);
CGContextSetFillColorWithColor(ctx, self.textColor.CGColor);
CGContextShowGlyphsAtPoint(ctx, p.x, p.y, glyphs, glyphCount);
}
CGContextRestoreGState(ctx);
}
Usage: Just turn the UILabel instances in Interface Builder into UILabelWithCGFont and implement the UIViewController::viewDidLoad method like this:
CGGlyph unicode2glyphDeutscheDruckschrift(UniChar c)
{
if ( '0' < = c && c <= '9' )
return c + (16 - '0');
if ( 'A' <= c && c <= 'Z' )
return c + (32 - 'A');
if ( 'a' <= c && c <= 'z' )
return c + (58 - 'a');
return 0;
}
-(void)viewDidLoad
{
[super viewDidLoad];
...
[fontLabel setFontFromFile:@"DeutscheDruckschrift" ofType:@"ttf" mapping:unicode2glyphDeutscheDruckschrift];
...
}
See this github gist for the complete implementation.
The mapping from Unicode character codes to glyph indices (inside the font description) currently is done via a C mapping function you have to provide a function pointer for. A later implementation could map the unicode character code to the glyph name and leverage CGFontGetGlyphWithGlyphName and render the custom mapping function obsolete.
Continue Reading »
UILabel's font property accepts UIFonts - but strange enough there's no way to get a custom loaded CGFont (from a ttf or otf file) converted into such an UIFont. You're stuck with the iPhone's pre-installed fonts - at least when you have to support iOS 3.0 devices.
After googling a bit and searching Stackoverflow I found ...