SQL Update

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL Update

Debra-3
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?
Reply | Threaded
Open this post in threaded view
|

Re: SQL Update

Ryan McKinley
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 <[hidden email]> 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.
>
>
Reply | Threaded
Open this post in threaded view
|

Re: SQL Update

Debra-3
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 <devoraf@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.
>
>
Reply | Threaded
Open this post in threaded view
|

Re: SQL Update

Chris Hostetter-3

: 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

Reply | Threaded
Open this post in threaded view
|

Re: SQL Update

Debra-3
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
Reply | Threaded
Open this post in threaded view
|

Re: SQL Update

Chris Hostetter-3

: 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

Reply | Threaded
Open this post in threaded view
|

Re: SQL Update

Debra-3
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
Reply | Threaded
Open this post in threaded view
|

Re: SQL Update

Chris Hostetter-3

: 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

Reply | Threaded
Open this post in threaded view
|

Re: SQL Update

Mike Klaas
In reply to this post by Debra-3
On 3/8/07, Debra <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Re: SQL Update

archive-2
In reply to this post by Debra-3