Hi Pat,
I dropped this in my Gemfile:
gem 'thinking-sphinx', :git => "git://github.com/freelancing-god/
thinking-sphinx.git", :branch => "rails3"
ran a bundle install and a bundle exec rake ts:conf and the datetimes
are still being case as INT (see below)
source mls_property_core_0
{
type = pgsql
sql_host = postgres.domain.com
sql_user = db_user
sql_pass = db_pass
sql_db = mls_properties
sql_port = 6432
sql_query_pre = SET TIME ZONE 'UTC'
sql_query = SELECT "properties"."id" * 1::INT8 + 0 AS "id" ,
"properties"."description" AS "description",
"properties"."street_number" AS "street_number",
"properties"."street_name" AS "street_name", "properties"."mls_id" AS
"mls_id", "states"."abbreviation" AS "state", "towns"."name" AS
"town", "offices"."mls_id" AS "list_office_mls", "offices"."name" AS
"list_office_name", "agents"."mls_id" AS "list_agent_mls",
agents.first_name || ' ' || agents.last_name AS "list_agent_name",
"sale_offices_properties"."mls_id" AS "sale_office_mls",
"sale_offices_properties"."name" AS "sale_office_name",
"sale_agents_properties"."mls_id" AS "sale_agent_mls",
properties.town_id || 'x' || properties.neighborhood_id AS
"town_neighborhood", (properties.full_bathrooms +
properties.half_bathrooms + properties.quarter_bathrooms +
properties.three_quarter_bathrooms) AS "total_bathrooms",
"properties"."zip" AS "zip_code", "properties"."id" AS
"sphinx_internal_id", 0 AS "sphinx_deleted", 1814387568 AS
"class_crc", "properties"."neighborhood_id" AS "neighborhood_id",
"properties"."state_id" AS "state_id", "properties"."town_id" AS
"town_id", "properties"."property_type_id" AS "property_type_id",
"properties"."status_id" AS "status_id", "properties"."service_id" AS
"service_id", "properties"."bedrooms" AS "bedrooms",
"properties"."full_bathrooms" AS "full_bathrooms",
"properties"."half_bathrooms" AS "half_bathrooms",
"properties"."price" AS "price", "properties"."square_footage" AS
"square_footage", "properties"."acres" AS "acres",
"properties"."year_built" AS "year_built",
"properties"."parking_spaces" AS "parking_spaces",
"properties"."taxes" AS "taxes", "properties"."parking_available" AS
"parking_available", "properties"."waterfront" AS "waterfront",
"properties"."beachfront" AS "beachfront", "properties"."basement" AS
"basement", "properties"."bank_owned" AS "bank_owned",
"properties"."opt_out" AS "opt_out", cast(extract(epoch from
"properties"."mls_created_at") as int) AS "mls_created_at",
cast(extract(epoch from "properties"."mls_updated_at") as int) AS
"mls_updated_at",
array_to_string(array_accum(COALESCE(cast(extract(epoch from
"open_houses"."start_time") as int), '0')), ',') AS
"open_house_start", "properties"."id" AS "property_id" FROM
"properties" LEFT OUTER JOIN "states" ON "states"."id" =
"properties"."state_id" LEFT OUTER JOIN "towns" ON "towns"."id" =
"properties"."town_id" LEFT OUTER JOIN "offices" ON "offices"."id" =
"properties"."list_office_id" LEFT OUTER JOIN "agents" ON
"agents"."id" = "properties"."list_agent_id" LEFT OUTER JOIN "offices"
"sale_offices_properties" ON "sale_offices_properties"."id" =
"properties"."sale_office_id" LEFT OUTER JOIN "agents"
"sale_agents_properties" ON "sale_agents_properties"."id" =
"properties"."sale_agent_id" LEFT OUTER JOIN "open_houses" ON
"open_houses"."property_id" = "properties"."id" WHERE
("properties"."id" >= $start AND "properties"."id" <= $end) GROUP BY
"properties"."id", "properties"."description",
"properties"."street_number", "properties"."street_name",
"properties"."mls_id", "states"."abbreviation", "towns"."name",
"offices"."mls_id", "offices"."name", "agents"."mls_id",
"sale_offices_properties"."mls_id", "sale_offices_properties"."name",
"sale_agents_properties"."mls_id", "properties"."zip",
"properties"."id", "properties"."neighborhood_id",
"properties"."state_id", "properties"."town_id",
"properties"."property_type_id", "properties"."status_id",
"properties"."service_id", "properties"."bedrooms",
"properties"."full_bathrooms", "properties"."half_bathrooms",
"properties"."price", "properties"."square_footage",
"properties"."acres", "properties"."year_built",
"properties"."parking_spaces", "properties"."taxes",
"properties"."parking_available", "properties"."waterfront",
"properties"."beachfront", "properties"."basement",
"properties"."bank_owned", "properties"."opt_out",
"properties"."mls_created_at", "properties"."mls_updated_at",
"properties"."id", list_agent_name, town_neighborhood, total_bathrooms
sql_query_range = SELECT COALESCE(MIN("id"), 1::bigint),
COALESCE(MAX("id"), 1::bigint) FROM "properties"
sql_attr_uint = sphinx_internal_id
sql_attr_uint = sphinx_deleted
sql_attr_uint = class_crc
sql_attr_uint = neighborhood_id
sql_attr_uint = state_id
sql_attr_uint = town_id
sql_attr_uint = property_type_id
sql_attr_uint = status_id
sql_attr_uint = service_id
sql_attr_uint = bedrooms
sql_attr_uint = full_bathrooms
sql_attr_uint = half_bathrooms
sql_attr_uint = price
sql_attr_uint = square_footage
sql_attr_uint = year_built
sql_attr_uint = parking_spaces
sql_attr_uint = property_id
sql_attr_bool = parking_available
sql_attr_bool = waterfront
sql_attr_bool = beachfront
sql_attr_bool = basement
sql_attr_bool = bank_owned
sql_attr_bool = opt_out
sql_attr_timestamp = mls_created_at
sql_attr_timestamp = mls_updated_at
sql_attr_float = acres
sql_attr_float = taxes
sql_attr_multi = uint open_house_start from field
sql_query_info = SELECT * FROM "properties" WHERE "id" = (($id -
0) / 1)
}
I did not change anything w/ regards to the index block:
define_index do
indexes description, street_number, street_name, mls_id
indexes state.abbreviation, :as => :state
indexes town.name, :as => :town
indexes list_office.mls_id, :as => :list_office_mls
indexes list_office.name, :as => :list_office_name
indexes list_agent.mls_id, :as => :list_agent_mls
indexes "agents.first_name || ' ' || agents.last_name", :as
=> :list_agent_name
indexes sale_office.mls_id, :as => :sale_office_mls
indexes sale_office.name, :as => :sale_office_name
indexes sale_agent.mls_id, :as => :sale_agent_mls
indexes "properties.town_id || 'x' ||
properties.neighborhood_id", :as => :town_neighborhood
indexes "(properties.full_bathrooms + properties.half_bathrooms +
properties.quarter_bathrooms +
properties.three_quarter_bathrooms)", :as => :total_bathrooms
indexes zip, :as => :zip_code
group_by 'list_agent_name', 'town_neighborhood', 'total_bathrooms'
set_property :enable_star => true
set_property :min_prefix_len => 3
has neighborhood_id, state_id, town_id, property_type_id,
status_id, service_id
has bedrooms, full_bathrooms, half_bathrooms, price,
square_footage, acres, year_built, parking_spaces, taxes
has parking_available, waterfront, beachfront, basement,
bank_owned, opt_out
has mls_created_at, mls_updated_at
has open_houses.start_time, :as => :open_house_start
has (:id), :as => :property_id
end
Do I need to change anything to get those 3 datetimes to cast as
BIGINT?
Any thoughts?
Thanks,
~ Tom
On Aug 11, 2:59 am, Pat Allan <[email protected]> wrote:
> Hi Tom
>
> I think the datetime columns could be bigints - which aren't handled as such
> by default in TS. There has been a patch which could help this though - it's
> not in 2.0.5, and I haven't released a new TS just yet... so, can you use the
> rails3 branch of the github repo as your source?
>
> git://github.com/freelancing-god/thinking-sphinx.git
>
> If so, try setting use_64_bit to true in your sphinx.yml file - this will
> ensure all datetime values are casted to bigints instead of ints.
>
> Cheers
>
> --
> Pat
>
> On 10/08/2011, at 2:49 AM, Tom Cocca wrote:
>
>
>
>
>
>
>
> > Hello,
>
> > I am running thinking-sphinx 2.0.5, Sphinx 0.9.9-id64-release (r2117)
> > and PG 9.0.2 and I am getting the following error when I run rake
> > ts:rebuild
>
> > Generating Configuration to /home/deploy/rails/generic_updater/
> > releases/20110809141657/config/production.sphinx.conf
> > Sphinx 0.9.9-id64-release (r2117)
> > Copyright (c) 2001-2009, Andrew Aksyonoff
>
> > using config file '/home/deploy/rails/generic_updater/releases/
> > 20110809141657/config/production.sphinx.conf'...
> > indexing index 'mls_property_core'...
> > ERROR: index 'mls_property_core': sql_range_query: ERROR: integer out
> > of range
> > (DSN=pgsql://db_user:*[email protected]:6432/mls_properties).
> > total 423927 docs, 187383324 bytes
> > total 757.711 sec, 247301 bytes/sec, 559.48 docs/sec
> > distributed index 'mls_property' can not be directly indexed;
> > skipping.
> > total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
> > total 638 writes, 1.737 sec, 953.1 kb/call avg, 2.7 msec/call avg
> > Started successfully (pid 3592).
>
> > The Index looks as follows:
>
> > define_index do
> > indexes description, street_number, street_name, mls_id
> > indexes state.abbreviation, :as => :state
> > indexes town.name, :as => :town
> > indexes list_office.mls_id, :as => :list_office_mls
> > indexes list_office.name, :as => :list_office_name
> > indexes list_agent.mls_id, :as => :list_agent_mls
> > indexes "agents.first_name || ' ' || agents.last_name", :as
> > => :list_agent_name
> > indexes sale_office.mls_id, :as => :sale_office_mls
> > indexes sale_office.name, :as => :sale_office_name
> > indexes sale_agent.mls_id, :as => :sale_agent_mls
> > indexes "properties.town_id || 'x' ||
> > properties.neighborhood_id", :as => :town_neighborhood
> > indexes "(properties.full_bathrooms + properties.half_bathrooms +
> > properties.quarter_bathrooms +
> > properties.three_quarter_bathrooms)", :as => :total_bathrooms
> > indexes zip, :as => :zip_code
> > group_by 'list_agent_name', 'town_neighborhood', 'total_bathrooms'
>
> > set_property :enable_star => true
> > set_property :min_prefix_len => 3
>
> > has neighborhood_id, state_id, town_id, property_type_id,
> > status_id, service_id
> > has bedrooms, full_bathrooms, half_bathrooms, price,
> > square_footage, acres, year_built, parking_spaces, taxes
> > has parking_available, waterfront, beachfront, basement,
> > bank_owned, opt_out
> > has mls_created_at, mls_updated_at
> > has open_houses.start_time, :as => :open_house_start
> > has (:id), :as => :property_id
> > end
>
> > With a generated production.sphinx.conf file like so:
>
> > indexer
> > {
> > }
>
> > searchd
> > {
> > listen = 127.0.0.1:9312
> > log = /home/deploy/rails/generic_updater/releases/20110809141657/log/
> > searchd.log
> > query_log = /home/deploy/rails/generic_updater/releases/
> > 20110809141657/log/searchd.query.log
> > pid_file = /home/deploy/rails/generic_updater/releases/
> > 20110809141657/log/searchd.production.pid
> > }
>
> > source mls_property_core_0
> > {
> > type = pgsql
> > sql_host = postgres.mydomain.com
> > sql_user = db_user
> > sql_pass = db_pass
> > sql_db = mls_properties
> > sql_port = 6432
> > sql_query_pre = SET TIME ZONE 'UTC'
> > sql_query = SELECT "properties"."id" * 1::INT8 + 0 AS "id" ,
> > "properties"."description" AS "description",
> > "properties"."street_number" AS "street_number",
> > "properties"."street_name" AS "street_name", "properties"."mls_id" AS
> > "mls_id", "states"."abbreviation" AS "state", "towns"."name" AS
> > "town", "offices"."mls_id" AS "list_office_mls", "offices"."name" AS
> > "list_office_name", "agents"."mls_id" AS "list_agent_mls",
> > agents.first_name || ' ' || agents.last_name AS "list_agent_name",
> > "sale_offices_properties"."mls_id" AS "sale_office_mls",
> > "sale_offices_properties"."name" AS "sale_office_name",
> > "sale_agents_properties"."mls_id" AS "sale_agent_mls",
> > properties.town_id || 'x' || properties.neighborhood_id AS
> > "town_neighborhood", (properties.full_bathrooms +
> > properties.half_bathrooms + properties.quarter_bathrooms +
> > properties.three_quarter_bathrooms) AS "total_bathrooms",
> > "properties"."zip" AS "zip_code", "properties"."id" AS
> > "sphinx_internal_id", 0 AS "sphinx_deleted", 1814387568 AS
> > "class_crc", "properties"."neighborhood_id" AS "neighborhood_id",
> > "properties"."state_id" AS "state_id", "properties"."town_id" AS
> > "town_id", "properties"."property_type_id" AS "property_type_id",
> > "properties"."status_id" AS "status_id", "properties"."service_id" AS
> > "service_id", "properties"."bedrooms" AS "bedrooms",
> > "properties"."full_bathrooms" AS "full_bathrooms",
> > "properties"."half_bathrooms" AS "half_bathrooms",
> > "properties"."price" AS "price", "properties"."square_footage" AS
> > "square_footage", "properties"."acres" AS "acres",
> > "properties"."year_built" AS "year_built",
> > "properties"."parking_spaces" AS "parking_spaces",
> > "properties"."taxes" AS "taxes", "properties"."parking_available" AS
> > "parking_available", "properties"."waterfront" AS "waterfront",
> > "properties"."beachfront" AS "beachfront", "properties"."basement" AS
> > "basement", "properties"."bank_owned" AS "bank_owned",
> > "properties"."opt_out" AS "opt_out", cast(extract(epoch from
> > "properties"."mls_created_at") as int) AS "mls_created_at",
> > cast(extract(epoch from "properties"."mls_updated_at") as int) AS
> > "mls_updated_at",
> > array_to_string(array_accum(COALESCE(cast(extract(epoch from
> > "open_houses"."start_time") as int), '0')), ',') AS
> > "open_house_start", "properties"."id" AS "property_id" FROM
> > "properties" LEFT OUTER JOIN "states" ON "states"."id" =
> > "properties"."state_id" LEFT OUTER JOIN "towns" ON "towns"."id" =
> > "properties"."town_id" LEFT OUTER JOIN "offices" ON "offices"."id" =
> > "properties"."list_office_id" LEFT OUTER JOIN "agents" ON
> > "agents"."id" = "properties"."list_agent_id" LEFT OUTER JOIN "offices"
> > "sale_offices_properties" ON "sale_offices_properties"."id" =
> > "properties"."sale_office_id" LEFT OUTER JOIN "agents"
> > "sale_agents_properties" ON "sale_agents_properties"."id" =
> > "properties"."sale_agent_id" LEFT OUTER JOIN "open_houses" ON
> > "open_houses"."property_id" = "properties"."id" WHERE
> > ("properties"."id" >= $start AND "properties"."id" <= $end) GROUP BY
> > "properties"."id", "properties"."description",
> > "properties"."street_number", "properties"."street_name",
> > "properties"."mls_id", "states"."abbreviation", "towns"."name",
> > "offices"."mls_id", "offices"."name", "agents"."mls_id",
> > "sale_offices_properties"."mls_id", "sale_offices_properties"."name",
> > "sale_agents_properties"."mls_id", "properties"."zip",
> > "properties"."id", "properties"."neighborhood_id",
> > "properties"."state_id", "properties"."town_id",
> > "properties"."property_type_id", "properties"."status_id",
> > "properties"."service_id", "properties"."bedrooms",
> > "properties"."full_bathrooms", "properties"."half_bathrooms",
> > "properties"."price", "properties"."square_footage",
> > "properties"."acres", "properties"."year_built",
> > "properties"."parking_spaces", "properties"."taxes",
> > "properties"."parking_available", "properties"."waterfront",
> > "properties"."beachfront", "properties"."basement",
> > "properties"."bank_owned", "properties"."opt_out",
> > "properties"."mls_created_at", "properties"."mls_updated_at",
> > "properties"."id", list_agent_name, town_neighborhood, total_bathrooms
> > sql_query_range = SELECT COALESCE(MIN("id"), 1::bigint),
> > COALESCE(MAX("id"), 1::bigint) FROM "properties"
> > sql_attr_uint = sphinx_internal_id
> > sql_attr_uint = sphinx_deleted
> > sql_attr_uint = class_crc
> > sql_attr_uint = neighborhood_id
> > sql_attr_uint = state_id
> > sql_attr_uint = town_id
> > sql_attr_uint = property_type_id
> > sql_attr_uint = status_id
> > sql_attr_uint = service_id
> > sql_attr_uint = bedrooms
> > sql_attr_uint = full_bathrooms
> > sql_attr_uint = half_bathrooms
> > sql_attr_uint = price
> > sql_attr_uint = square_footage
> > sql_attr_uint = year_built
> > sql_attr_uint = parking_spaces
> > sql_attr_uint = property_id
> > sql_attr_bool = parking_available
> > sql_attr_bool = waterfront
> > sql_attr_bool = beachfront
> > sql_attr_bool = basement
> > sql_attr_bool = bank_owned
> > sql_attr_bool = opt_out
> > sql_attr_timestamp = mls_created_at
> > sql_attr_timestamp = mls_updated_at
> > sql_attr_float = acres
> > sql_attr_float = taxes
> > sql_attr_multi = uint open_house_start from field
> > sql_query_info = SELECT * FROM "properties" WHERE "id" = (($id -
> > 0) / 1)
> > }
>
> > index mls_property_core
> > {
> > source = mls_property_core_0
> > path = /home/deploy/rails/generic_updater/releases/20110809141657/db/
> > sphinx/production/mls_property_core
> > charset_type = utf-8
> > min_prefix_len = 3
> > enable_star = 1
> > }
>
> > index mls_property
> > {
> > type = distributed
> > local = mls_property_core
> > }
>
> > I have narrowed it down to 1 of these pieces in the index (I believe).
>
> > has mls_created_at, mls_updated_at
> > has open_houses.start_time, :as => :open_house_start
>
> > Any idea what is causing this issue or how to fix it? I have the id64
> > version of sphinx and the latest released gem of TS which is what
> > issue #97 (https://github.com/freelancing-god/thinking-sphinx/issues/
> > 97) said I needed?
>
> > Any ideas would be incredibly appreciated.
>
> > Thanks,
> > ~ Tom
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Thinking Sphinx" group.
> > To post to this group, send email to [email protected].
> > To unsubscribe from this group,
>
> ...
>
> read more »
--
You received this message because you are subscribed to the Google Groups
"Thinking Sphinx" 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/thinking-sphinx?hl=en.
