Re: [2] SQL Update

[Debra <de...@netvision.net.il>]

Thanks Ryan for the quick reply and for your contribution to solr.

I did write some code to generate a xml document from a sql query (generates
a general xml file that with few changes to a properties file will generate
a solr add xml document), so I'll use that for the time being.

Maybe we can use the field database type to add a suffix/prefix to the field
name and have dynamic fields set.

I have posted another question to the group regarding the usage of dynamic
fields.
It would be useful to have a solr setting for stripping the dynamic field
suffix/prefix on index field name to get back the original field name. Does
it make sense?


Thank you,
Debra


ryan mckinley wrote:
> 
> SOLR-103 is waiting for SOLR-139 to solidify before i post more updates...
> 
> I have it running successfully, but it requires too many other patches
> to suggest trying to get it running unless you are up for a bit of
> work.  If you are, i can easily post an update.
> 
> About the schema... SOLR-103 uses the ResultSetMetaData to decide what
> field to push the value into - you will need to make sure the column
> names correspond to the fields in schema.sql.  If you use SELECT *
> FROM, your tables will need the same names, if you use:  SELECT
> mysqlfield as mysolrfieldname FROM ... you don't.
> 
> ryan
> 
> 
> On 3/6/07, Debra <de...@netvision.net.il> wrote:
>>
>> What is the status of the SQL update?
>> Should all database fields used in sql updates be added to schema.xml
>> before
>> running the sql update?
>>
>> --
>> View this message in context:
>> http://www.nabble.com/SQL-Update-tf3358303.html#a9341018
>> Sent from the Solr - User mailing list archive at Nabble.com.
>>
>>
> 
> 

-- 
View this message in context: http://www.nabble.com/SQL-Update-tf3358303.html#a9341943
Sent from the Solr - User mailing list archive at Nabble.com.

Re: [2] SQL Update

[Chris Hostetter <ho...@fucit.org>]

: I could create a list of field name + type, but doing so I might as well
: create it and add it to fields in schema.xml.

that was my original point: if you want to be able to refer to a field as
"username" and have it be string, just define it explicitly.

: Does solr reread the schema file when I post an add action or only on starup
: (or someother point)?

SOlr only reads the schema.xml file once, but the IndexSchema object it
builds from the schema.xml is used pervasively during document adds and
searches to understand how to use each field.

: In general, I wonder if adding the suffix for dynamic fields is not posing
: some usability tradeoff.
: I think, For a user (not a programmer) it's not intuitive to think of id as
: an integer and therefore enter id_i when searching,
: what do you think?

there's definitely a tradeoff ... dynamic fields make it easy for you to
add arbitrary fields where the type information is infered by naming
convention -- but then you have to use those names.  if you want cleaner
names you have create more explict fields in advance.


-Hoss

Re: [2] SQL Update

[Mike Klaas <mi...@gmail.com>]

On 3/8/07, Debra <de...@netvision.net.il> wrote:
>
> I could create a list of field name + type, but doing so I might as well
> create it and add it to fields in schema.xml.
<>

Alternative solution: write a SQL schema <-> Solr schema mapper.
Should be relatively simple, as long as you are confining yourself to
flat tables.  Or, it could provide the mapping on the fly going into
and out of Solr.

> In general, I wonder if adding the suffix for dynamic fields is not posing
> some usability tradeoff.
> I think, For a user (not a programmer) it's not intuitive to think of id as
> an integer and therefore enter id_i when searching,
> what do you think?

In my experience, it is very common for SQL schemata to include
suffices indicates the datatype of the field.

As we've discussed, Solr needs some way of distinguishing that a field
is a given type, so it is infeasible to simply drop the suffix.  If
you think it should go, there has to be some kind of alternatice
mechanism for recognizing dynamic field types.

cheers,
-Mike

Re: [2] SQL Update

[Debra <de...@netvision.net.il>]

I could create a list of field name + type, but doing so I might as well
create it and add it to fields in schema.xml.
Does solr reread the schema file when I post an add action or only on starup
(or someother point)?

In general, I wonder if adding the suffix for dynamic fields is not posing
some usability tradeoff.
I think, For a user (not a programmer) it's not intuitive to think of id as
an integer and therefore enter id_i when searching,
what do you think?



Chris Hostetter wrote:
> 
> 
> and i suppose you could make
> a customized ResponseWriter that when writing out documents striped off
> any suffixes it could tell came from dynamicFields so the response docs
> contained <str name="user"> and <int name="id"> ... but when parsing the
> query string your clients send, and they ask for "user:42" how would the
> request handler know that it shoudl rewrite that to user_string:42 and not
> user_int:42 ?
> 
> 
> 
> -Hoss
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/SQL-Update-tf3358303.html#a9372391
Sent from the Solr - User mailing list archive at Nabble.com.

Re: [2] SQL Update

[Chris Hostetter <ho...@fucit.org>]

: I wanted to add data from relational database tables.
: To avoid defining each and every table column name in the schema, I thought
: I'll append a suffix to the field name depending on it's type.

which is fine and dandy for when you index the data, mapping your "string"
database column "user", and your "int" database column "id" to the solr
dynamic fields "user_string" and "id_int" ...and i suppose you could make
a customized ResponseWriter that when writing out documents striped off
any suffixes it could tell came from dynamicFields so the response docs
contained <str name="user"> and <int name="id"> ... but when parsing the
query string your clients send, and they ask for "user:42" how would the
request handler know that it shoudl rewrite that to user_string:42 and not
user_int:42 ?



-Hoss

Re: [2] SQL Update

[Debra <de...@netvision.net.il>]

I wanted to add data from relational database tables.
To avoid defining each and every table column name in the schema, I thought
I'll append a suffix to the field name depending on it's type.



Chris Hostetter wrote:
> 
> 
> sorry, i'm really not following this talk of "stripping" the dynamic field
> name to get back the "original" field name ... what does that mean?
> 
> it seems like what you are describing is having a schema consisting
> entirely of dynamic fields where teh suffix determines hte datatype, but
> you don't wnat to have to use that suffix when interacting with Solr ...
> but if i've got two dynamicFields "*_i" and "*_s" and you refer to "user"
> in your query, how is Solr suppose to know wether you want "user_i" or
> "user_s"
> 
> If you want people do be able to refer to the field "user" and you want it
> to be an integer field, why not definie it as an explicit field instead of
> a dynamic field?
> 
> 
> 
> -Hoss
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/SQL-Update-tf3358303.html#a9362953
Sent from the Solr - User mailing list archive at Nabble.com.

Re: [2] SQL Update

[Chris Hostetter <ho...@fucit.org>]

: It would be useful to have a solr setting for stripping the dynamic field
: suffix/prefix on index field name to get back the original field name. Does
: it make sense?

sorry, i'm really not following this talk of "stripping" the dynamic field
name to get back the "original" field name ... what does that mean?

it seems like what you are describing is having a schema consisting
entirely of dynamic fields where teh suffix determines hte datatype, but
you don't wnat to have to use that suffix when interacting with Solr ...
but if i've got two dynamicFields "*_i" and "*_s" and you refer to "user"
in your query, how is Solr suppose to know wether you want "user_i" or
"user_s"

If you want people do be able to refer to the field "user" and you want it
to be an integer field, why not definie it as an explicit field instead of
a dynamic field?



-Hoss