Merge pull request #21 from upstartmobile/sql-server - reportable - Fork of reportable required by WarVox, from hdm/reportable.
(DIR) Log
(DIR) Files
(DIR) Refs
(DIR) README
---
(DIR) commit 0e804fa4eb8a5092c1e4f0d04094c5828b2c3a93
(DIR) parent 5f29dfb16b4b34158a89f37283d31d2c42852f04
(HTM) Author: Martin Kvlr <martin@sauspiel.de>
Date: Wed, 9 Jan 2013 09:50:59 -0800
Merge pull request #21 from upstartmobile/sql-server
Add support for MS SQL Server
Diffstat:
M lib/saulabs/reportable/grouping.rb | 25 +++++++++++++++++++++++++
M spec/classes/grouping_spec.rb | 49 ++++++++++++++++++++++++++++++-
2 files changed, 73 insertions(+), 1 deletion(-)
---
(DIR) diff --git a/lib/saulabs/reportable/grouping.rb b/lib/saulabs/reportable/grouping.rb
@@ -41,6 +41,8 @@ module Saulabs
from_sqlite_db_string(db_string)
when /postgres/i
from_postgresql_db_string(db_string)
+ when /mssql/i, /sqlserver/i
+ from_sqlserver_db_string(db_string)
end
end
@@ -57,6 +59,8 @@ module Saulabs
sqlite_format(date_column)
when /postgres/i
postgresql_format(date_column)
+ when /mssql/i, /sqlserver/i
+ sqlserver_format(date_column)
end
end
@@ -94,6 +98,14 @@ module Saulabs
end
end
+ def from_sqlserver_db_string(db_string)
+ if @identifier == :week
+ parts = [db_string[0..3], db_string[5..6]].map(&:to_i)
+ else
+ db_string.split(/[- ]/).map(&:to_i)
+ end
+ end
+
def mysql_format(date_column)
case @identifier
when :hour
@@ -133,6 +145,19 @@ module Saulabs
end
end
+ def sqlserver_format(date_column)
+ case @identifier
+ when :hour
+ "DATEADD(hh,DATEDIFF(hh,DATEADD(dd,DATEDIFF(dd,'1 Jan 1900',#{date_column}), '1 Jan 1900'),#{date_column}), DATEADD(dd,DATEDIFF(dd,'1 Jan 1900',#{date_column}), '1 Jan 1900'))"
+ when :day
+ "DATEADD(dd,DATEDIFF(dd,'1 Jan 1900',#{date_column}), '1 Jan 1900')"
+ when :week
+ "LEFT(CONVERT(varchar,#{date_column},120), 4) + '-' + CAST(DATEPART(isowk,#{date_column}) AS VARCHAR)"
+ when :month
+ "DATEADD(mm,DATEDIFF(mm,'1 Jan 1900',#{date_column}), '1 Jan 1900')"
+ end
+ end
+
end
end
(DIR) diff --git a/spec/classes/grouping_spec.rb b/spec/classes/grouping_spec.rb
@@ -76,6 +76,30 @@ describe Saulabs::Reportable::Grouping do
end
+ describe 'for MS SQL Server' do
+
+ before do
+ ActiveRecord::Base.connection.stub!(:adapter_name).and_return('sqlserver')
+ end
+
+ it 'should output a format of "YYYY-MM-DD HH:00:00.0" for grouping :hour' do # string "%Y-%m-%d %h:00:00.0"
+ Saulabs::Reportable::Grouping.new(:hour).send(:to_sql, 'created_at').should == "DATEADD(hh,DATEDIFF(hh,DATEADD(dd,DATEDIFF(dd,'1 Jan 1900',created_at), '1 Jan 1900'),created_at), DATEADD(dd,DATEDIFF(dd,'1 Jan 1900',created_at), '1 Jan 1900'))"
+ end
+
+ it 'should output a format of "YYYY-MM-DD" for grouping :day' do
+ Saulabs::Reportable::Grouping.new(:day).send(:to_sql, 'created_at').should == "DATEADD(dd,DATEDIFF(dd,'1 Jan 1900',created_at), '1 Jan 1900')"
+ end
+
+ it 'should output a format of "YYYY-WW" for grouping :week' do
+ Saulabs::Reportable::Grouping.new(:week).send(:to_sql, 'created_at').should == "LEFT(CONVERT(varchar,created_at,120), 4) + '-' + CAST(DATEPART(isowk,created_at) AS VARCHAR)"
+ end
+
+ it 'should output a format of "YYYY-MM-01" for grouping :month' do
+ Saulabs::Reportable::Grouping.new(:month).send(:to_sql, 'created_at').should == "DATEADD(mm,DATEDIFF(mm,'1 Jan 1900',created_at), '1 Jan 1900')"
+ end
+
+ end
+
end
describe '#date_parts_from_db_string' do
@@ -94,7 +118,7 @@ describe Saulabs::Reportable::Grouping do
end
- it 'should split the string with "-" and return teh calendar year and week for grouping :week' do
+ it 'should split the string with "-" and return the calendar year and week for grouping :week' do
db_string = '2008-2-1'
expected = [2008, 5]
@@ -150,6 +174,29 @@ describe Saulabs::Reportable::Grouping do
end
+ describe 'for MS SQL Server' do
+
+ before do
+ ActiveRecord::Base.connection.stub!(:adapter_name).and_return('sqlserver')
+ end
+
+ for grouping in [[:hour, '2008-12-31 12'], [:day, '2008-12-31'], [:month, '2008-12']] do
+
+ it "should split the string with '-' and ' ' for grouping :#{grouping[0].to_s}" do
+ Saulabs::Reportable::Grouping.new(grouping[0]).date_parts_from_db_string(grouping[1]).should == grouping[1].split(/[- ]/).map(&:to_i)
+ end
+
+ end
+
+ it 'should use the first 4 numbers for the year and the last 2 numbers for the week for grouping :week' do
+ db_string = '2008-52'
+ expected = [2008, 52]
+
+ Saulabs::Reportable::Grouping.new(:week).date_parts_from_db_string(db_string).should == expected
+ end
+
+ end
+
end
end