copiousfreetime / amalgalite
watch download tarball
public
Forks1Right
Watchers20Right
Description: SQLite database engine embedded in a ruby extension.
Homepage: http://copiousfreetime.rubyforge.org/amalgalite/
Clone URL: git://github.com/copiousfreetime/amalgalite.git
amalgalite / examples / define_aggregate.rb
raw normal view history
88665f85 » copiousfreetime 2009-01-06 Added example of defining a... 1 #!/usr/bin/env ruby
2
3 require 'rubygems'
4 $: << "../lib"
5 $: << "../ext"
6 require 'amalgalite'
7
8 #--
9 # Create a database and a table to put some results from the functions in
10 #--
11 db = Amalgalite::Database.new( ":memory:" )
12 db.execute( "CREATE TABLE atest( words )" )
13
14 #------------------------------------------------------------------------------
15 # Create unique word count aggregate
16 #------------------------------------------------------------------------------
17 class UniqueWordCount < ::Amalgalite::Aggregate
18 attr_accessor :words
19
20 def initialize
21 @name = 'unique_word_count'
22 @arity = 1
23 @words = Hash.new { |h,k| h[k] = 0 }
24 end
25
26 def step( str )
27 str.split(/\W+/).each do |word|
28 words[ word.downcase ] += 1
29 end
30 return nil
31 end
32
33 def finalize
34 return words.size
35 end
36 end
37
38 db.define_aggregate( 'unique_word_count', UniqueWordCount )
39
40 #------------------------------------------------------------------------------
41 # Now we have a new aggregate function, lets insert some rows into the database
42 # and see what we can find.
43 #------------------------------------------------------------------------------
44 sql = "INSERT INTO atest( words ) VALUES( ? )"
45 verify = {}
46 db.prepare( sql ) do |stmt|
47 DATA.each do |words|
48 words.strip!
49 puts "Inserting #{words}"
50 stmt.execute( words )
51 words.split(/\W+/).each { |w| verify[w] = true }
52 end
53 end
54
55 #------------------------------------------------------------------------------
56 # And show the results
57 #------------------------------------------------------------------------------
58 puts
59 puts "Getting results..."
60 puts
61 all_rows = db.execute("SELECT unique_word_count( words ) AS uwc FROM atest")
62 puts "#{all_rows.first['uwc']} unique words found"
63 puts "#{verify.size} unique words to verify"
64
65 __END__
66 some random
67 words with
68 which
69 to play
70 and there should
71 be a couple of different
72 words that appear
73 more than once and
74 some that appear only
75 once