[Maria-developers] Extendet FederatedX storage engine to support updating Sphinx RT indexes

Markus Lidel

28 May 2013 28 May '13

11:46 p.m.

Hello, the Sphinx Search engine has RT indexes, which could be updated with a subset of SQL commands, refered to as SphinxQL: http://sphinxsearch.com/docs/2.1.1/sphinxql-reference.html Because the FederatedX storage engine uses some commands, which Sphinx does not know, here is a patch, wich extends the FederatedX storage engine to support this subset of commands. Now its possible to update Sphinx RT indexes directly from MariaDB. To create a connection to the RT index in the Sphinx default RT-index: index rt { type = rt path = @CONFDIR@/data/rt rt_field = title rt_field = content rt_attr_string = title rt_attr_string = content rt_attr_uint = gid } you just have to call: CREATE TABLE `rt` ( `id` BIGINT UNSIGNED NOT NULL, `title` TEXT, `content` TEXT, `gid` INT UNSIGNED ) ENGINE=FEDERATED CONNECTION='sphinxql://root@localhost:9306/rt'; It would be great, if the attached patch could be integrated into MariaDB. If you have any suggestions, please feel free to contact me. Best regards, Markus Lidel

Attachments:

federatedx_with_sphinxql-10.0.2.patch (text/plain — 45.4 KB)

Show replies by date

Michael Widenius

4 Jun 4 Jun

5:18 p.m.

Hi!

...

...
...
...
...
"Markus" == Markus Lidel writes:

Markus> Hello, Markus> the Sphinx Search engine has RT indexes, which could be updated with a Markus> subset of SQL commands, refered to as SphinxQL: Markus> http://sphinxsearch.com/docs/2.1.1/sphinxql-reference.html Markus> Because the FederatedX storage engine uses some commands, which Sphinx Markus> does not know, here is a patch, wich extends the FederatedX storage Markus> engine to support this subset of commands. Now its possible to update Markus> Sphinx RT indexes directly from MariaDB. To create a connection to the Markus> RT index in the Sphinx default RT-index: <cut> Sergey, can you take a look at this patch? Regards, Monty

Sergey Vojtovich

16 Jun 16 Jun

3:48 p.m.

New subject: [Maria-developers] Extendet FederatedX storage engine to support updating Sphinx RT indexes

Hi Markus, thanks for your contribution. Connecting to Sphinx RT index via federatedx sounds like a great idea. I reviewed attached patch and from what I can see, relevant difference between dialects is as following: 1. Connection to RT index doesn't require database name and you made it optional in parse_url(). That's acceptable with sphinxql scheme, but with mysql scheme it's a bit ambiguous. What do you think if we keep database name mandatory and document that connection string to RT index should look like sphinxql://root@localhost:9306//rt or sphinxql://root@localhost:9306/dummy/rt 2. delete_row(): looks nice, but could be greatly simplified because sphinxql accepts only record identifier in WHERE clause. 3. delete_all_rows(): FWICS there is "TRUNCATE RTINDEX" statement in sphinx. Why can't we use it? Even better patch sphinx to make RTINDEX word optional? 4. table_metadata(): I believe it should be easy and natural to patch sphinx to support SHOW TABLE STATUS. What do you think? 5. query(): Same here, sphinxql shouldn't add implicit limit. 6. test_connection(): What's the problem with test query? Did I miss anything else? Thanks, Sergey 29.05.2013, в 1:46, Markus Lidel написал(а):

...

Hello,

the Sphinx Search engine has RT indexes, which could be updated with a subset of SQL commands, refered to as SphinxQL:

http://sphinxsearch.com/docs/2.1.1/sphinxql-reference.html

Because the FederatedX storage engine uses some commands, which Sphinx does not know, here is a patch, wich extends the FederatedX storage engine to support this subset of commands. Now its possible to update Sphinx RT indexes directly from MariaDB. To create a connection to the RT index in the Sphinx default RT-index:

index rt { type = rt

path = @CONFDIR@/data/rt

rt_field = title rt_field = content

rt_attr_string = title rt_attr_string = content

rt_attr_uint = gid }

you just have to call:

CREATE TABLE `rt` ( `id` BIGINT UNSIGNED NOT NULL, `title` TEXT, `content` TEXT, `gid` INT UNSIGNED ) ENGINE=FEDERATED CONNECTION='sphinxql://root@localhost:9306/rt';

It would be great, if the attached patch could be integrated into MariaDB. If you have any suggestions, please feel free to contact me.

Best regards,

Markus Lidel _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

Markus Lidel

7:58 p.m.

Hello Sergey, thank you very much for reviewing my patch! Am 16.06.2013 15:48, schrieb Sergey Vojtovich:

...

Hi Markus,

thanks for your contribution. Connecting to Sphinx RT index via federatedx sounds like a great idea.

I reviewed attached patch and from what I can see, relevant difference between dialects is as following:

1. Connection to RT index doesn't require database name and you made it optional in parse_url(). That's acceptable with sphinxql scheme, but with mysql scheme it's a bit ambiguous. What do you think if we keep database name mandatory and document that connection string to RT index should look like sphinxql://root@localhost:9306//rt or sphinxql://root@localhost:9306/dummy/rt

Hmmm, i think both ways are a workaround. Probably the best way is to implement a unique parse_url() function for sphinxql and mysql. This way you don't have to specify a database, which isn't used for SphinxQL and for MySQL you couldn't forget to set the database. The only reason why i didn't implement it already was because i want to change as less as possible. I'll look into it and send an updated patch.

...

2. delete_row(): looks nice, but could be greatly simplified because sphinxql accepts only record identifier in WHERE clause.

Yes, that's true.

...

3. delete_all_rows(): FWICS there is "TRUNCATE RTINDEX" statement in sphinx. Why can't we use it? Even better patch sphinx to make RTINDEX word optional?

TRUNCATE RTINDEX was implemented first in Sphinx 2.1.1-beta. In the current release the TRUNCATE RTINDEX is not available.

...

4. table_metadata(): I believe it should be easy and natural to patch sphinx to support SHOW TABLE STATUS. What do you think?

Yes, that will be the best solution, but i didn't looked at the source of Sphinx.

...

5. query(): Same here, sphinxql shouldn't add implicit limit.

SphinxQL by default adds a limit to 30 rows. So if you don't specify a LIMIT you only get 30 rows back instead of all rows. If you for example have 100 rows in your Sphinx index, and you want to delete all rows, you could only delete 30 at once. So i thought the best way is to add a limit.

...

6. test_connection(): What's the problem with test query?

Sphinx simply doesn't understand the test query: SELECT * FROM <table> WHERE 1=0 so i changed it into SELECT * FROM <table> LIMIT 0 which should do the same, and Sphinx did understand.

...

Did I miss anything else?

If so, please feel free to contact me. Best regards, Markus Lidel

...

Thanks, Sergey

29.05.2013, в 1:46, Markus Lidel написал(а):

...
Hello,

the Sphinx Search engine has RT indexes, which could be updated with a subset of SQL commands, refered to as SphinxQL:

http://sphinxsearch.com/docs/2.1.1/sphinxql-reference.html

Because the FederatedX storage engine uses some commands, which Sphinx does not know, here is a patch, wich extends the FederatedX storage engine to support this subset of commands. Now its possible to update Sphinx RT indexes directly from MariaDB. To create a connection to the RT index in the Sphinx default RT-index:

index rt { type = rt

path = @CONFDIR@/data/rt

rt_field = title rt_field = content

rt_attr_string = title rt_attr_string = content

rt_attr_uint = gid }

you just have to call:

CREATE TABLE `rt` ( `id` BIGINT UNSIGNED NOT NULL, `title` TEXT, `content` TEXT, `gid` INT UNSIGNED ) ENGINE=FEDERATED CONNECTION='sphinxql://root@localhost:9306/rt';

It would be great, if the attached patch could be integrated into MariaDB. If you have any suggestions, please feel free to contact me.

Best regards,

Markus Lidel

Markus Lidel

2 Sep 2 Sep

6:03 p.m.

Hallo Sergey, sorry for the delay, but now sphinxql:// has an own parser and delete_row() is much simplified. If i still has something missed, please feel free to contact me. Best regards, Markus Lidel Am 16.06.2013 19:58, schrieb Markus Lidel:

...

Hello Sergey,

thank you very much for reviewing my patch!

Am 16.06.2013 15:48, schrieb Sergey Vojtovich:

...
Hi Markus,

thanks for your contribution. Connecting to Sphinx RT index via federatedx sounds like a great idea.

I reviewed attached patch and from what I can see, relevant difference between dialects is as following:

1. Connection to RT index doesn't require database name and you made it optional in parse_url(). That's acceptable with sphinxql scheme, but with mysql scheme it's a bit ambiguous. What do you think if we keep database name mandatory and document that connection string to RT index should look like sphinxql://root@localhost:9306//rt or sphinxql://root@localhost:9306/dummy/rt

Hmmm, i think both ways are a workaround. Probably the best way is to implement a unique parse_url() function for sphinxql and mysql. This way you don't have to specify a database, which isn't used for SphinxQL and for MySQL you couldn't forget to set the database. The only reason why i didn't implement it already was because i want to change as less as possible. I'll look into it and send an updated patch.

...
2. delete_row(): looks nice, but could be greatly simplified because sphinxql accepts only record identifier in WHERE clause.

Yes, that's true.

...
3. delete_all_rows(): FWICS there is "TRUNCATE RTINDEX" statement in sphinx. Why can't we use it? Even better patch sphinx to make RTINDEX word optional?

TRUNCATE RTINDEX was implemented first in Sphinx 2.1.1-beta. In the current release the TRUNCATE RTINDEX is not available.

...
4. table_metadata(): I believe it should be easy and natural to patch sphinx to support SHOW TABLE STATUS. What do you think?

Yes, that will be the best solution, but i didn't looked at the source of Sphinx.

...
5. query(): Same here, sphinxql shouldn't add implicit limit.

SphinxQL by default adds a limit to 30 rows. So if you don't specify a LIMIT you only get 30 rows back instead of all rows. If you for example have 100 rows in your Sphinx index, and you want to delete all rows, you could only delete 30 at once. So i thought the best way is to add a limit.

...
6. test_connection(): What's the problem with test query?

Sphinx simply doesn't understand the test query:

SELECT * FROM <table> WHERE 1=0

so i changed it into

SELECT * FROM <table> LIMIT 0

which should do the same, and Sphinx did understand.

...
Did I miss anything else?

If so, please feel free to contact me.

Best regards,

Markus Lidel

...
Thanks, Sergey

29.05.2013, в 1:46, Markus Lidel написал(а):

...
Hello,

the Sphinx Search engine has RT indexes, which could be updated with a subset of SQL commands, refered to as SphinxQL:

http://sphinxsearch.com/docs/2.1.1/sphinxql-reference.html

Because the FederatedX storage engine uses some commands, which Sphinx does not know, here is a patch, wich extends the FederatedX storage engine to support this subset of commands. Now its possible to update Sphinx RT indexes directly from MariaDB. To create a connection to the RT index in the Sphinx default RT-index:

index rt { type = rt

path = @CONFDIR@/data/rt

rt_field = title rt_field = content

rt_attr_string = title rt_attr_string = content

rt_attr_uint = gid }

you just have to call:

CREATE TABLE `rt` ( `id` BIGINT UNSIGNED NOT NULL, `title` TEXT, `content` TEXT, `gid` INT UNSIGNED ) ENGINE=FEDERATED CONNECTION='sphinxql://root@localhost:9306/rt';

It would be great, if the attached patch could be integrated into MariaDB. If you have any suggestions, please feel free to contact me.

Best regards,

Markus Lidel

-- ------------------------------------------ Markus Lidel (Senior IT Consultant) Shadow Connect GmbH Carl-Reisch-Weg 12 D-86381 Krumbach Germany Phone: +49 82 82/99 51-0 Fax: +49 82 82/99 51-11 E-Mail: Markus.Lidel@shadowconnect.com URL: http://www.shadowconnect.com Geschäftsführer/CEO: Markus Lidel HRB 10357, Amtsgericht Memmingen

Markus Lidel

8 Apr 8 Apr

11:38 a.m.

Hello Sergey, is there any progress with the patch or is there still something missing? Thank you very much! Best regards, Markus Lidel Am 16.06.2013 19:58, schrieb Markus Lidel:

...

Hello Sergey,

thank you very much for reviewing my patch!

Am 16.06.2013 15:48, schrieb Sergey Vojtovich:

...
Hi Markus,

thanks for your contribution. Connecting to Sphinx RT index via federatedx sounds like a great idea.

I reviewed attached patch and from what I can see, relevant difference between dialects is as following:

1. Connection to RT index doesn't require database name and you made it optional in parse_url(). That's acceptable with sphinxql scheme, but with mysql scheme it's a bit ambiguous. What do you think if we keep database name mandatory and document that connection string to RT index should look like sphinxql://root@localhost:9306//rt or sphinxql://root@localhost:9306/dummy/rt

Hmmm, i think both ways are a workaround. Probably the best way is to implement a unique parse_url() function for sphinxql and mysql. This way you don't have to specify a database, which isn't used for SphinxQL and for MySQL you couldn't forget to set the database. The only reason why i didn't implement it already was because i want to change as less as possible. I'll look into it and send an updated patch.

...
2. delete_row(): looks nice, but could be greatly simplified because sphinxql accepts only record identifier in WHERE clause.

Yes, that's true.

...
3. delete_all_rows(): FWICS there is "TRUNCATE RTINDEX" statement in sphinx. Why can't we use it? Even better patch sphinx to make RTINDEX word optional?

TRUNCATE RTINDEX was implemented first in Sphinx 2.1.1-beta. In the current release the TRUNCATE RTINDEX is not available.

...
4. table_metadata(): I believe it should be easy and natural to patch sphinx to support SHOW TABLE STATUS. What do you think?

Yes, that will be the best solution, but i didn't looked at the source of Sphinx.

...
5. query(): Same here, sphinxql shouldn't add implicit limit.

SphinxQL by default adds a limit to 30 rows. So if you don't specify a LIMIT you only get 30 rows back instead of all rows. If you for example have 100 rows in your Sphinx index, and you want to delete all rows, you could only delete 30 at once. So i thought the best way is to add a limit.

...
6. test_connection(): What's the problem with test query?

Sphinx simply doesn't understand the test query:

SELECT * FROM <table> WHERE 1=0

so i changed it into

SELECT * FROM <table> LIMIT 0

which should do the same, and Sphinx did understand.

...
Did I miss anything else?

If so, please feel free to contact me.

Best regards,

Markus Lidel

...
Thanks, Sergey

29.05.2013, в 1:46, Markus Lidel написал(а):

...
Hello,

the Sphinx Search engine has RT indexes, which could be updated with a subset of SQL commands, refered to as SphinxQL:

http://sphinxsearch.com/docs/2.1.1/sphinxql-reference.html

Because the FederatedX storage engine uses some commands, which Sphinx does not know, here is a patch, wich extends the FederatedX storage engine to support this subset of commands. Now its possible to update Sphinx RT indexes directly from MariaDB. To create a connection to the RT index in the Sphinx default RT-index:

index rt { type = rt

path = @CONFDIR@/data/rt

rt_field = title rt_field = content

rt_attr_string = title rt_attr_string = content

rt_attr_uint = gid }

you just have to call:

CREATE TABLE `rt` ( `id` BIGINT UNSIGNED NOT NULL, `title` TEXT, `content` TEXT, `gid` INT UNSIGNED ) ENGINE=FEDERATED CONNECTION='sphinxql://root@localhost:9306/rt';

It would be great, if the attached patch could be integrated into MariaDB. If you have any suggestions, please feel free to contact me.

Best regards,

Markus Lidel

Sergey Vojtovich

9 Apr 9 Apr

9:51 a.m.

Hi Markus, sorry for this delay. I was going to check with other developers regarding your patch but apparently it didn't happen. Sergei, could you review this contribution and send back your suggestions? I believe most of this stuff should be implemented in sphinx, but you may have different opinion. Thanks, Sergey On Tue, Apr 08, 2014 at 11:38:42AM +0200, Markus Lidel wrote:

...

Hello Sergey,

is there any progress with the patch or is there still something missing?

Thank you very much!

Best regards,

Markus Lidel

Am 16.06.2013 19:58, schrieb Markus Lidel:

...
Hello Sergey,

thank you very much for reviewing my patch!

Am 16.06.2013 15:48, schrieb Sergey Vojtovich:

...
Hi Markus,

thanks for your contribution. Connecting to Sphinx RT index via federatedx sounds like a great idea.

I reviewed attached patch and from what I can see, relevant difference between dialects is as following:

1. Connection to RT index doesn't require database name and you made it optional in parse_url(). That's acceptable with sphinxql scheme, but with mysql scheme it's a bit ambiguous. What do you think if we keep database name mandatory and document that connection string to RT index should look like sphinxql://root@localhost:9306//rt or sphinxql://root@localhost:9306/dummy/rt

Hmmm, i think both ways are a workaround. Probably the best way is to implement a unique parse_url() function for sphinxql and mysql. This way you don't have to specify a database, which isn't used for SphinxQL and for MySQL you couldn't forget to set the database. The only reason why i didn't implement it already was because i want to change as less as possible. I'll look into it and send an updated patch.

...
2. delete_row(): looks nice, but could be greatly simplified because sphinxql accepts only record identifier in WHERE clause.

Yes, that's true.

...
3. delete_all_rows(): FWICS there is "TRUNCATE RTINDEX" statement in sphinx. Why can't we use it? Even better patch sphinx to make RTINDEX word optional?

TRUNCATE RTINDEX was implemented first in Sphinx 2.1.1-beta. In the current release the TRUNCATE RTINDEX is not available.

...
4. table_metadata(): I believe it should be easy and natural to patch sphinx to support SHOW TABLE STATUS. What do you think?

Yes, that will be the best solution, but i didn't looked at the source of Sphinx.

...
5. query(): Same here, sphinxql shouldn't add implicit limit.

SphinxQL by default adds a limit to 30 rows. So if you don't specify a LIMIT you only get 30 rows back instead of all rows. If you for example have 100 rows in your Sphinx index, and you want to delete all rows, you could only delete 30 at once. So i thought the best way is to add a limit.

...
6. test_connection(): What's the problem with test query?

Sphinx simply doesn't understand the test query:

SELECT * FROM <table> WHERE 1=0

so i changed it into

SELECT * FROM <table> LIMIT 0

which should do the same, and Sphinx did understand.

...
Did I miss anything else?

If so, please feel free to contact me.

Best regards,

Markus Lidel

...
Thanks, Sergey

29.05.2013, в 1:46, Markus Lidel написал(а):

...
Hello,

the Sphinx Search engine has RT indexes, which could be updated with a subset of SQL commands, refered to as SphinxQL:

http://sphinxsearch.com/docs/2.1.1/sphinxql-reference.html

Because the FederatedX storage engine uses some commands, which Sphinx does not know, here is a patch, wich extends the FederatedX storage engine to support this subset of commands. Now its possible to update Sphinx RT indexes directly from MariaDB. To create a connection to the RT index in the Sphinx default RT-index:

index rt { type = rt

path = @CONFDIR@/data/rt

rt_field = title rt_field = content

rt_attr_string = title rt_attr_string = content

rt_attr_uint = gid }

you just have to call:

CREATE TABLE `rt` ( `id` BIGINT UNSIGNED NOT NULL, `title` TEXT, `content` TEXT, `gid` INT UNSIGNED ) ENGINE=FEDERATED CONNECTION='sphinxql://root@localhost:9306/rt';

It would be great, if the attached patch could be integrated into MariaDB. If you have any suggestions, please feel free to contact me.

Best regards,

Markus Lidel

-- ------------------------------------------ Markus Lidel (Senior IT Consultant)

Shadow Connect GmbH Carl-Reisch-Weg 12 D-86381 Krumbach Germany

Phone: +49 82 82/99 51-0 Fax: +49 82 82/99 51-11

E-Mail: Markus.Lidel@shadowconnect.com URL: http://www.shadowconnect.com

Geschäftsführer/CEO: Markus Lidel HRB 10357, Amtsgericht Memmingen

Markus Lidel

11 Jun 11 Jun

11:53 a.m.

Hello Sergey, is there any progress with the patch or could i do something to help you? Thank you very much! Best regards, Markus Lidel Am 09.04.2014 09:51, schrieb Sergey Vojtovich:

...

Hi Markus,

sorry for this delay. I was going to check with other developers regarding your patch but apparently it didn't happen.

Sergei, could you review this contribution and send back your suggestions? I believe most of this stuff should be implemented in sphinx, but you may have different opinion.

Thanks, Sergey

On Tue, Apr 08, 2014 at 11:38:42AM +0200, Markus Lidel wrote:

...
Hello Sergey,

is there any progress with the patch or is there still something missing?

Thank you very much!

Best regards,

Markus Lidel

Am 16.06.2013 19:58, schrieb Markus Lidel:

...
Hello Sergey,

thank you very much for reviewing my patch!

Am 16.06.2013 15:48, schrieb Sergey Vojtovich:

...
Hi Markus,

thanks for your contribution. Connecting to Sphinx RT index via federatedx sounds like a great idea.

I reviewed attached patch and from what I can see, relevant difference between dialects is as following:

1. Connection to RT index doesn't require database name and you made it optional in parse_url(). That's acceptable with sphinxql scheme, but with mysql scheme it's a bit ambiguous. What do you think if we keep database name mandatory and document that connection string to RT index should look like sphinxql://root@localhost:9306//rt or sphinxql://root@localhost:9306/dummy/rt

Hmmm, i think both ways are a workaround. Probably the best way is to implement a unique parse_url() function for sphinxql and mysql. This way you don't have to specify a database, which isn't used for SphinxQL and for MySQL you couldn't forget to set the database. The only reason why i didn't implement it already was because i want to change as less as possible. I'll look into it and send an updated patch.

...
2. delete_row(): looks nice, but could be greatly simplified because sphinxql accepts only record identifier in WHERE clause.

Yes, that's true.

...
3. delete_all_rows(): FWICS there is "TRUNCATE RTINDEX" statement in sphinx. Why can't we use it? Even better patch sphinx to make RTINDEX word optional?

TRUNCATE RTINDEX was implemented first in Sphinx 2.1.1-beta. In the current release the TRUNCATE RTINDEX is not available.

...
4. table_metadata(): I believe it should be easy and natural to patch sphinx to support SHOW TABLE STATUS. What do you think?

Yes, that will be the best solution, but i didn't looked at the source of Sphinx.

...
5. query(): Same here, sphinxql shouldn't add implicit limit.

SphinxQL by default adds a limit to 30 rows. So if you don't specify a LIMIT you only get 30 rows back instead of all rows. If you for example have 100 rows in your Sphinx index, and you want to delete all rows, you could only delete 30 at once. So i thought the best way is to add a limit.

...
6. test_connection(): What's the problem with test query?

Sphinx simply doesn't understand the test query:

SELECT * FROM <table> WHERE 1=0

so i changed it into

SELECT * FROM <table> LIMIT 0

which should do the same, and Sphinx did understand.

...
Did I miss anything else?

If so, please feel free to contact me.

Best regards,

Markus Lidel

...
Thanks, Sergey

29.05.2013, в 1:46, Markus Lidel написал(а):

...
Hello,

the Sphinx Search engine has RT indexes, which could be updated with a subset of SQL commands, refered to as SphinxQL:

http://sphinxsearch.com/docs/2.1.1/sphinxql-reference.html

Because the FederatedX storage engine uses some commands, which Sphinx does not know, here is a patch, wich extends the FederatedX storage engine to support this subset of commands. Now its possible to update Sphinx RT indexes directly from MariaDB. To create a connection to the RT index in the Sphinx default RT-index:

index rt { type = rt

path = @CONFDIR@/data/rt

rt_field = title rt_field = content

rt_attr_string = title rt_attr_string = content

rt_attr_uint = gid }

you just have to call:

CREATE TABLE `rt` ( `id` BIGINT UNSIGNED NOT NULL, `title` TEXT, `content` TEXT, `gid` INT UNSIGNED ) ENGINE=FEDERATED CONNECTION='sphinxql://root@localhost:9306/rt';

It would be great, if the attached patch could be integrated into MariaDB. If you have any suggestions, please feel free to contact me.

Best regards,

Markus Lidel

Daniel Black

12 Jun 12 Jun

4:38 a.m.

Just letting you know I tried going down this path. as FederatedX there's a lot SQL pushed across that sphinxql doesn't support - http://sphinxsearch.com/bugs/view.php?id=1950 aside form FederatedX retrieving the entire dataset to do an update. sphinxql urls require a specific table structure that I couldn't work out - http://sphinxsearch.com/bugs/view.php?id=1952 My general impression is sphinxse needs to be totally rewritten as using the sphinx client library. ----- Original Message -----

...

Hello Sergey,

is there any progress with the patch or could i do something to help you?

Thank you very much!

Best regards,

Markus Lidel

Am 09.04.2014 09:51, schrieb Sergey Vojtovich:

...
Hi Markus,

sorry for this delay. I was going to check with other developers regarding your patch but apparently it didn't happen.

Sergei, could you review this contribution and send back your suggestions? I believe most of this stuff should be implemented in sphinx, but you may have different opinion.

Thanks, Sergey

On Tue, Apr 08, 2014 at 11:38:42AM +0200, Markus Lidel wrote:

...
Hello Sergey,

is there any progress with the patch or is there still something missing?

Thank you very much!

Best regards,

Markus Lidel

Am 16.06.2013 19:58, schrieb Markus Lidel:

...
Hello Sergey,

thank you very much for reviewing my patch!

Am 16.06.2013 15:48, schrieb Sergey Vojtovich:

...
Hi Markus,

thanks for your contribution. Connecting to Sphinx RT index via federatedx sounds like a great idea.

I reviewed attached patch and from what I can see, relevant difference between dialects is as following:

1. Connection to RT index doesn't require database name and you made it optional in parse_url(). That's acceptable with sphinxql scheme, but with mysql scheme it's a bit ambiguous. What do you think if we keep database name mandatory and document that connection string to RT index should look like sphinxql://root@localhost:9306//rt or sphinxql://root@localhost:9306/dummy/rt

Hmmm, i think both ways are a workaround. Probably the best way is to implement a unique parse_url() function for sphinxql and mysql. This way you don't have to specify a database, which isn't used for SphinxQL and for MySQL you couldn't forget to set the database. The only reason why i didn't implement it already was because i want to change as less as possible. I'll look into it and send an updated patch.

...
2. delete_row(): looks nice, but could be greatly simplified because sphinxql accepts only record identifier in WHERE clause.

Yes, that's true.

...
3. delete_all_rows(): FWICS there is "TRUNCATE RTINDEX" statement in sphinx. Why can't we use it? Even better patch sphinx to make RTINDEX word optional?

TRUNCATE RTINDEX was implemented first in Sphinx 2.1.1-beta. In the current release the TRUNCATE RTINDEX is not available.

...
4. table_metadata(): I believe it should be easy and natural to patch sphinx to support SHOW TABLE STATUS. What do you think?

Yes, that will be the best solution, but i didn't looked at the source of Sphinx.

...
5. query(): Same here, sphinxql shouldn't add implicit limit.

SphinxQL by default adds a limit to 30 rows. So if you don't specify a LIMIT you only get 30 rows back instead of all rows. If you for example have 100 rows in your Sphinx index, and you want to delete all rows, you could only delete 30 at once. So i thought the best way is to add a limit.

...
6. test_connection(): What's the problem with test query?

Sphinx simply doesn't understand the test query:

SELECT * FROM <table> WHERE 1=0

so i changed it into

SELECT * FROM <table> LIMIT 0

which should do the same, and Sphinx did understand.

...
Did I miss anything else?

If so, please feel free to contact me.

Best regards,

Markus Lidel

...
Thanks, Sergey

29.05.2013, в 1:46, Markus Lidel написал(а):

...
Hello,

the Sphinx Search engine has RT indexes, which could be updated with a subset of SQL commands, refered to as SphinxQL:

http://sphinxsearch.com/docs/2.1.1/sphinxql-reference.html

Because the FederatedX storage engine uses some commands, which Sphinx does not know, here is a patch, wich extends the FederatedX storage engine to support this subset of commands. Now its possible to update Sphinx RT indexes directly from MariaDB. To create a connection to the RT index in the Sphinx default RT-index:

index rt { type = rt

path = @CONFDIR@/data/rt

rt_field = title rt_field = content

rt_attr_string = title rt_attr_string = content

rt_attr_uint = gid }

you just have to call:

CREATE TABLE `rt` ( `id` BIGINT UNSIGNED NOT NULL, `title` TEXT, `content` TEXT, `gid` INT UNSIGNED ) ENGINE=FEDERATED CONNECTION='sphinxql://root@localhost:9306/rt';

It would be great, if the attached patch could be integrated into MariaDB. If you have any suggestions, please feel free to contact me.

Best regards,

Markus Lidel

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

-- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.

3772

Age (days ago)

4152

Last active (days ago)

List overview

Download

8 comments

4 participants

participants (4)

Daniel Black
Markus Lidel
Michael Widenius
Sergey Vojtovich