/contenter_gui/spec/models/content_query_spec.rb
Ruby | 568 lines | 448 code | 52 blank | 68 comment | 27 complexity | d0badad9db8b92883e644b92e120bc80 MD5 | raw file
Possible License(s): JSON
- # -*- ruby -*-
- #require 'test_helper'
- require 'spec/spec_helper'
- describe "Content::Query" do
- COMMON_TABLES = "content_keys, languages, countries, brands, applications, mime_types, content_statuses, users AS updater_users, users AS creator_users".freeze
- JOINS = <<"END"
- (content_keys.id = contents.content_key_id)
- AND (languages.id = contents.language_id)
- AND (countries.id = contents.country_id)
- AND (brands.id = contents.brand_id)
- AND (applications.id = contents.application_id)
- AND (mime_types.id = contents.mime_type_id)
- AND (content_statuses.id = contents.content_status_id)
- AND (updater_users.id = contents.updater_user_id)
- AND (creator_users.id = contents.creator_user_id)
- AND (content_types.id = content_keys.content_type_id)
- END
- ORDER_BY = <<"END".chomp.freeze
- ORDER BY
- (SELECT content_keys.code FROM content_keys WHERE content_keys.id = contents.content_key_id),
- (SELECT languages.code FROM languages WHERE languages.id = contents.language_id),
- (SELECT countries.code FROM countries WHERE countries.id = contents.country_id),
- (SELECT brands.code FROM brands WHERE brands.id = contents.brand_id),
- (SELECT applications.code FROM applications WHERE applications.id = contents.application_id),
- (SELECT mime_types.code FROM mime_types WHERE mime_types.id = contents.mime_type_id)
- END
- ORDER_BY_CV = ORDER_BY.gsub(/\.id = contents\./, '.id = cv.').freeze
- DATA = "(CASE WHEN (content_types.mime_type_id NOT IN (SELECT id FROM mime_types WHERE code LIKE 'text/%')) THEN '' ELSE convert_from(contents.data, 'UTF8') END)".freeze
- it 'should create SQL to query all Content' do
- q = Content::Query.new()
- # $stderr.puts(q.sql + "\n")
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- #{ORDER_BY}
- END
- end
- it 'should create SQL to query by ContentType' do
- q = Content::Query.new(:params => { :content_type => 'phrase' })
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (content_types.code = E'phrase')
- )
- #{ORDER_BY}
- END
- end
- it 'should create SQL to query by ContentKey' do
- q = Content::Query.new(:params => { :content_key => 'content_key' })
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (content_keys.code = E'content_key')
- )
- #{ORDER_BY}
- END
- end
- it 'should create SQL to query by content_id ' do
- q = Content::Query.new(:params => { :content_id => '123' })
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (contents.id = 123)
- )
- #{ORDER_BY}
- END
- end
- it 'should create SQL to query by UUID ' do
- q = Content::Query.new(:params => { :uuid => '123' })
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (contents.uuid = E'123')
- )
- #{ORDER_BY}
- END
- end
- it 'should create SQL to query by md5sum ' do
- q = Content::Query.new(:params => { :md5sum => '123' })
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (contents.md5sum = E'123')
- )
- #{ORDER_BY}
- END
- end
- [ :updater, :creator ].each do | field |
- it "should query by #{field}" do
- q = Content::Query.new(:params => { field => 'joeuser' })
- # $stderr.puts (q.sql + "\n")
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (#{field}_users.login = E'joeuser')
- )
- #{ORDER_BY}
- END
- end
- end # each
- it 'should search for data using LIKE' do
- q = Content::Query.new(:params => { :data => '%123%' }, :like => true)
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (#{DATA} LIKE E'%123%')
- )
- #{ORDER_BY}
- END
- end
- it 'should search for data using case-insensitive LIKE' do
- q = Content::Query.new(:params => { :data => '%123%' }, :ilike => true)
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (#{DATA} ILIKE E'%123%')
- )
- #{ORDER_BY}
- END
- end
- it 'should search using a version_list_id' do
- q = Content::Query.
- new(:params => { :version_list_id => 123 }
- )
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM content_versions AS contents, #{COMMON_TABLES}, version_list_contents, content_types
- WHERE
- #{JOINS}
- AND (version_list_contents.version_list_id = 123)
- AND (version_list_contents.content_version_id = contents.id)
- #{ORDER_BY}
- END
- end
- [ :content_key_id, :content_type_id, :country_id, :content_status_id ].each do | column |
- it "should search using a #{column}." do
- q = Content::Query.
- new(:params => { column => 123 }
- )
- # $stderr.puts ("\n" + q.sql + "\n")
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (#{column.to_s.sub(/_id\Z/, '').pluralize}.id = 123)
- )
- #{ORDER_BY}
- END
- end
- end
- it "should search using tasks." do
- q = Content::Query.
- new(:params => { :tasks => 123 }
- )
- # $stderr.puts ("\n" + q.sql + "\n")
- (q.sql + "\n").should =~ /\(contents.tasks LIKE E'% 123 %'\)/
- end
- it 'should search by version_list_id' do
- q = Content::Query.
- new(
- :params => { :version_list_id => 123 }
- )
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM content_versions AS contents, #{COMMON_TABLES}, version_list_contents, content_types
- WHERE
- #{JOINS}
- AND (version_list_contents.version_list_id = 123)
- AND (version_list_contents.content_version_id = contents.id)
- #{ORDER_BY}
- END
- end
- it 'should search by version_list_name' do
- q = Content::Query.
- new(
- :params => { :version_list_name => 'production' }
- )
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM content_versions AS contents, #{COMMON_TABLES}, version_list_names, version_lists, version_list_contents, content_types
- WHERE
- #{JOINS}
- AND (version_list_names.name = E'production')
- AND (version_list_contents.version_list_id = version_list_names.version_list_id)
- AND (version_list_contents.content_version_id = contents.id)
- #{ORDER_BY}
- END
- end
- it 'should search using a simple user_query' do
- q = Content::Query.
- new(:user_query => ' %foo_bar% '
- )
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (#{DATA} ILIKE E'%foo_bar%')
- OR (content_keys.code ILIKE E'%foo_bar%')
- )
- #{ORDER_BY}
- END
- end
- [ :content_key_id, :content_type_id, :language_id ].each do | column |
- it "should search using a simple user_query with #{column}:123" do
- q = Content::Query.
- new(:user_query => " #{column}:321 %foo_bar% #{column}:123"
- )
- # $stderr.puts("\n" + q.sql + "\n")
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (#{DATA} ILIKE E'%foo_bar%')
- OR (content_keys.code ILIKE E'%foo_bar%')
- )
- AND (
- (#{column.to_s.sub(/_id\Z/, '').pluralize}.id = 123)
- )
- #{ORDER_BY}
- END
- end
- end
- it 'should search using a user_query with additional selection AND clauses' do
- q = Content::Query.
- new(:user_query => ' content_type:phrase language:en %foo_bar% ',
- :params => { :id => 123 }
- )
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (contents.id = 123)
- )
- AND (
- (#{DATA} ILIKE E'%foo_bar%')
- OR (content_keys.code ILIKE E'%foo_bar%')
- )
- AND (
- (content_types.code = E'phrase')
- AND (languages.code = E'en')
- )
- #{ORDER_BY}
- END
- end
- it 'should search using a user_query with content_key' do
- q = Content::Query.
- new({
- :user_query => ' content_key:key '
- })
- # $stderr.puts("\n" + q.sql)
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (content_keys.code = E'key')
- )
- #{ORDER_BY}
- END
- end
- it 'should count using a user_query with content_key' do
- q = Content::Query.
- new({
- :user_query => ' content_key:key ',
- })
- # $stderr.puts("\n" + q.sql(:count => true))
- (q.sql(:count => true) + "\n").should == <<"END"
- SELECT COUNT(contents.*)
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (content_keys.code = E'key')
- )
- END
- end
- it 'should search all versions using a user_query with content_key' do
- q = Content::Query.
- new({
- :user_query => ' content_key:key ',
- :versions => true,
- })
- # $stderr.puts("\n" + q.sql)
- (q.sql + "\n").should == <<"END"
- SELECT cv.* FROM content_versions AS cv
- WHERE cv.id IN (
- SELECT contents.id
- FROM content_versions AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (content_keys.code = E'key')
- )
- AND contents.content_id = cv.content_id
- )
- #{ORDER_BY_CV},
- version DESC
- END
- end
- it 'should search using a user_query and version_list_id' do
- q = Content::Query.
- new(:user_query => ' content_type:email country:US %foo_bar% ',
- :params => { :version_list_id => 123 }
- )
- (q.sql + "\n").should == <<"END"
- SELECT contents.*
- FROM content_versions AS contents, #{COMMON_TABLES}, version_list_contents, content_types
- WHERE
- #{JOINS}
- AND (version_list_contents.version_list_id = 123)
- AND (version_list_contents.content_version_id = contents.id)
- AND (
- (#{DATA} ILIKE E'%foo_bar%')
- OR (content_keys.code ILIKE E'%foo_bar%')
- )
- AND (
- (content_types.code = E'email')
- AND (countries.code = E'US')
- )
- #{ORDER_BY}
- END
- end
- it 'should search using a user_query content_status and latest version' do
- q = Content::Query.
- new(:user_query => ' content_status:initial ',
- :latest => true
- )
- # $stderr.puts("\n" + q.sql)
- (q.sql + "\n").should == <<"END"
- SELECT cv.* FROM content_versions AS cv
- WHERE cv.id IN (
- SELECT MAX(contents.id)
- FROM content_versions AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (content_statuses.code = E'initial')
- )
- AND contents.content_id = cv.content_id
- )
- #{ORDER_BY_CV}
- END
- end
- it 'should count using a user_query content_status OR expression and latest version' do
- q = Content::Query.
- new(:user_query => ' latest:true content_status:approved|released '
- )
- # $stderr.puts("\n" + q.sql(:count => true))
- (q.sql(:count => true) + "\n").should == <<"END"
- SELECT COUNT(cv.*) FROM content_versions AS cv
- WHERE cv.id IN (
- SELECT MAX(contents.id)
- FROM content_versions AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- ((content_statuses.code = E'approved') OR (content_statuses.code = E'released'))
- )
- AND contents.content_id = cv.content_id
- )
- END
- end
- it 'should count using a user_query content_status AND expression and latest version' do
- q = Content::Query.
- new(:user_query => ' latest:true content_status:!approved&released '
- )
- # $stderr.puts("\n" + q.sql(:count => true))
- (q.sql(:count => true) + "\n").should == <<"END"
- SELECT COUNT(cv.*) FROM content_versions AS cv
- WHERE cv.id IN (
- SELECT MAX(contents.id)
- FROM content_versions AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (((content_statuses.code IS NULL) OR (content_statuses.code <> E'approved')) AND (content_statuses.code = E'released'))
- )
- AND contents.content_id = cv.content_id
- )
- END
- end
- it 'should query using a content_status AND expression and latest version using :exact match and not parse AND and OR expressions' do
- q = Content::Query.
- new({
- :latest => true,
- :exact => true,
- :params => {
- :content_key => "something&somethingelse",
- :content_status => "!approved&released",
- },
- }
- )
- # $stderr.puts("\n" + q.sql())
- (q.sql() + "\n").should == <<"END"
- SELECT cv.* FROM content_versions AS cv
- WHERE cv.id IN (
- SELECT MAX(contents.id)
- FROM content_versions AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (content_statuses.code = E'!approved&released')
- AND (content_keys.code = E'something&somethingelse')
- )
- AND contents.content_id = cv.content_id
- )
- #{ORDER_BY_CV}
- END
- end
- [ 123, nil ].each do | b_id |
- [ :like, :ilike ].each do | opt |
- it "should search where brand_id=>#{b_id.inspect} and #{opt.inspect}" do
- q = Content::Query.
- new({
- :params => {
- :brand_id => b_id,
- },
- opt => true,
- })
- # $stderr.puts("\n" + q.sql())
- (q.sql() + "\n").should == <<"END"
- SELECT contents.*
- FROM contents AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (brands.id #{opt.to_s.upcase} E'#{b_id}')
- )
- #{ORDER_BY}
- END
- end # it
- end # each
- end # each
- it 'should search all versions with a :conditions option' do
- q = Content::Query.
- new({
- :user_query => ' content_key:key ',
- :versions => true,
- :conditions => ' brands.id = 123 ',
- })
- # $stderr.puts("\n" + q.sql)
- (q.sql + "\n").should == <<"END"
- SELECT cv.* FROM content_versions AS cv
- WHERE cv.id IN (
- SELECT contents.id
- FROM content_versions AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND ( brands.id = 123 )
- AND (
- (content_keys.code = E'key')
- )
- AND contents.content_id = cv.content_id
- )
- #{ORDER_BY_CV},
- version DESC
- END
- end
- it 'should by filename: and versions:t' do
- q = Content::Query.
- new({
- :user_query => ' filename:abc.png versions:t ',
- })
- # $stderr.puts("\n" + q.sql)
- (q.sql + "\n").should == <<"END"
- SELECT cv.* FROM content_versions AS cv
- WHERE cv.id IN (
- SELECT contents.id
- FROM content_versions AS contents, #{COMMON_TABLES}, content_types
- WHERE
- #{JOINS}
- AND (
- (contents.filename = E'abc.png')
- )
- AND contents.content_id = cv.content_id
- )
- #{ORDER_BY_CV},
- version DESC
- END
- end
- end # describe