Below is my script and output. But I could not get this to replicate
in a test case. I will have to review my app's data to see what I am
doing wrong.
require 'rubygems'
require 'sequel'
require 'logger'
# ---------- ---------- ----------
# make a database in memory
# ---------- ---------- ----------
begin
# Sequel.upcase_identifiers - identifiers (column names,etc) should
all be upper case
# Sequel.quote_identifiers - Quoting identifiers, caused me
problems, so don't do it.
# Only an issue if using reserved words
as an identifier.
# timeout - 1_000 milliseconds = 1 sec
# DB = Sequel.connect( 'sqlite:/',
DB = Sequel.sqlite( ':memory:',
:timeout => 1_000,
:upcase_identifiers => true,
:quote_identifiers => false,
:loggers => [ Logger.new
( $stderr ) ] )
#Logger.datetime_format = "%Y-%m-%d %H:%M:%S"
rescue StandardError => ex
$stderr.print "Unable to open accounts database\n\n"
$stderr.print ex.message + "\n"
$stderr.print "\n" + ex.backtrace.join( "\n " )
$stderr.print "\n---------- db connection ----------\n"
exit(1)
end
puts "\n\tDB: " + DB.inspect
puts "\n"
# ---------- ---------- ----------
# Make a table and a model
# ---------- ---------- ----------
DB.transaction {
begin
DB.create_table :tests do
primary_key :id
String :name, :null => false,
:size => 80
Date :tst_dt, :null => false
index [ :name ]
end
rescue StandardError => ex
$stderr.print "Error creating table TESTS\n\n"
$stderr.print ex.message + "\n"
$stderr.print "\n" + ex.backtrace.join( "\n " )
$stderr.print "\n---------- create_table ----------\n"
exit(1)
end
}
class Test < Sequel::Model
end
# ---------- ---------- ----------
# Create some data
# ---------- ---------- ----------
DB.transaction {
begin
Test.insert( :name => 'aaa',
:tst_dt => '2009-01-01' )
Test.insert( :name => 'bbb',
:tst_dt => '2009-01-02' )
Test.insert( :name => 'ccc',
:tst_dt => '2009-02-02' )
Test.insert( :name => 'ddd',
:tst_dt => '2009-07-07' )
Test.insert( :name => 'eee',
:tst_dt => '2009-07-13' )
rescue StandardError => ex
$stderr.print "Error creating data\n\n"
$stderr.print ex.message + "\n"
$stderr.print "\n" + ex.backtrace.join( "\n " )
$stderr.print "\n---------- insert ----------\n"
exit(1)
end
}
# ---------- ---------- ----------
# what do we have?
# ---------- ---------- ----------
cnt = Test.count
puts "\n\t" + 'num of rows in table: ' + cnt.to_s
puts "\n"
data = Test.select{ strftime( '%Y-%m', tst_dt ).as( month ) }.
distinct.
order( :tst_dt ).
all
puts "\n" + data.inspect
puts "\n"
data.each_with_index do |d, i|
puts "\t" + i.to_s + "\t" + d[:month]
end
exit(0)
# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
C:\A\ruby>ruby t1.rb
DB: #<Sequel::SQLite::Database: "sqlite:/:memory:">
I, [2009-07-09T10:48:12.088000 #3224] INFO -- : BEGIN
I, [2009-07-09T10:48:12.088000 #3224] INFO -- : CREATE TABLE tests
(id integer PRIMARY KEY AUTOINCREMENT, name varchar(80) NOT NULL,
tst_dt date NOT NULL)
I, [2009-07-09T10:48:12.135000 #3224] INFO -- : CREATE INDEX
tests_name_index ON tests (name)
I, [2009-07-09T10:48:12.135000 #3224] INFO -- : COMMIT
I, [2009-07-09T10:48:12.135000 #3224] INFO -- : PRAGMA table_info
('tests')
I, [2009-07-09T10:48:12.151000 #3224] INFO -- : BEGIN
I, [2009-07-09T10:48:12.151000 #3224] INFO -- : INSERT INTO tests
(tst_dt, name) VALUES ('2009-01-01', 'aaa')
I, [2009-07-09T10:48:12.151000 #3224] INFO -- : INSERT INTO tests
(tst_dt, name) VALUES ('2009-01-02', 'bbb')
I, [2009-07-09T10:48:12.151000 #3224] INFO -- : INSERT INTO tests
(tst_dt, name) VALUES ('2009-02-02', 'ccc')
I, [2009-07-09T10:48:12.151000 #3224] INFO -- : INSERT INTO tests
(tst_dt, name) VALUES ('2009-07-07', 'ddd')
I, [2009-07-09T10:48:12.151000 #3224] INFO -- : INSERT INTO tests
(tst_dt, name) VALUES ('2009-07-13', 'eee')
I, [2009-07-09T10:48:12.151000 #3224] INFO -- : COMMIT
I, [2009-07-09T10:48:12.151000 #3224] INFO -- : SELECT COUNT(*) AS
'count' FROM tests LIMIT 1
num of rows in table: 5
I, [2009-07-09T10:48:12.151000 #3224] INFO -- : SELECT DISTINCT
strftime('%Y-%m', tst_dt) AS 'month' FROM tests ORDER BY tst_dt
[#<Test @values={:month=>"2009-01"}>, #<Test @values=
{:month=>"2009-02"}>, #<Test @values={:month=>"2009-07"}>]
0 2009-01
1 2009-02
2 2009-07
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
