#!/usr/bin/env ruby
require 'rubygems'
$: << "../lib"
$: << "../ext"
require 'amalgalite'
#--
# Create a database and a table to put some results from the functions in
#--
db = Amalgalite::Database.new( ":memory:" )
db.execute( "CREATE TABLE ftest( data, md5, sha1, sha2_bits, sha2)" )
#------------------------------------------------------------------------------
# Create an MD5 method using the block format of defining an sql fuction
#------------------------------------------------------------------------------
require 'digest/md5'
db.define_function( 'md5' ) do |x|
Digest::MD5.hexdigest( x.to_s )
end
#------------------------------------------------------------------------------
# Create a SHA1 method using the lambda format of defining an sql function
#------------------------------------------------------------------------------
require 'digest/sha1'
sha1 = lambda do |y|
Digest::SHA1.hexdigest( y.to_s )
end
db.define_function( "sha1", sha1 )
#------------------------------------------------------------------------------
# Create a SHA2 method using the class format for defining an sql function
# In this one we will allow any number of parameters, but we will only use the
# first two.
#------------------------------------------------------------------------------
require 'digest/sha2'
class SQLSha2
# track the number of invocations
attr_reader :call_count
def initialize
@call_count = 0
end
# the protocol that is used for sql function definition
def to_proc() self ; end
# say we take any number of parameters
def arity
-1
end
# The method that is called by SQLite, must be named 'call'
def call( *args )
text = args.shift.to_s
bitlength = (args.shift || 256).to_i
Digest::SHA2.new( bitlength ).hexdigest( text )
end
end
db.define_function('sha2', SQLSha2.new)
#------------------------------------------------------------------------------
# Now we have 3 new sql functions, each defined in one of the available methods
# to define sql functions in amalgalite. Lets insert some rows and look at the
# results
#------------------------------------------------------------------------------
possible_bits = [ 256, 384, 512 ]
sql = "INSERT INTO ftest( data, md5, sha1, sha2_bits, sha2 ) VALUES( @word , md5( @word ), sha1( @word ), @bits, sha2(@word,@bits) )"
db.prepare( sql ) do |stmt|
DATA.each do |word|
word.strip!
bits = possible_bits[ rand(3) ]
puts "Inserting #{word}, #{bits}"
stmt.execute( { '@word' => word, '@bits' => bits } )
end
end
#------------------------------------------------------------------------------
# And show the results
#------------------------------------------------------------------------------
puts
puts "Getting results..."
puts
columns = db.schema.tables['ftest'].columns.keys.sort
i = 0
db.execute("SELECT #{columns.join(',')} FROM ftest") do |row|
i += 1
puts "-----[ row #{i} ]-" + "-" * 42
columns.each do |col|
puts "#{col.ljust(10)} : #{row[col]}"
end
puts
end
__END__
some
random
words
with
which
to
play
