sqldsl_test.rb

# sqldql testing

require 'rubygems'
require 'sqldsl'
require 'sqlite3'

db = SQLite3::Database.new(':memory:')

puts "creating UserRoles table"
db.execute <<SQL
CREATE TABLE UserRoles (
  user_role_id INTEGER PRIMARY KEY,
  user_role_desc TEXT
);
SQL

puts "creating Users table"
db.execute <<SQL
CREATE TABLE Users (
  user_id INTEGER PRIMARY KEY,
  user_name TEXT,
  user_pass TEXT,
  user_role INTEGER,
  FOREIGN KEY (user_role) REFERENCES UserRoles (user_role_id)
);
SQL

puts "adding user roles"
db.execute( Insert.into[:UserRoles].values(1, 'Admin').to_sql )
db.execute( Insert.into[:UserRoles].values(2, 'Moderator').to_sql )
db.execute( Insert.into[:UserRoles].values(3, 'Subscriber').to_sql )

puts "inserting users"
db.execute( Insert.into[:Users].values( :NULL, 'JoshS', 'changeme', 1 ).to_sql )
db.execute( Insert.into[:Users].values( :NULL, 'BobJ', 'changeme', 2 ).to_sql )
db.execute( Insert.into[:Users].values( :NULL, 'SteveS', 'changeme', 3 ).to_sql )
db.execute( Insert.into[:Users].values( :NULL, 'JohnB', 'changeme', 3 ).to_sql )

puts "list of contents:"
puts "ID\tName\tPass\t\tRole"
select_all = Select[:user_id,
                    :user_name,
                    :user_pass,
                    :user_role_desc].from[:Users].inner_join[:UserRoles].on {
  equal :'Users.user_role', :'UserRoles.user_role_id'
}.to_sql
db.execute( select_all ) do |row|
  puts "#{row[0]}\t#{row[1]}\t#{row[2]}\t#{row[3]}"
end