Few Suggestions

[karthik56]

Hi it's a great project. I feel below things are missing in your doc. Can you please mention them for a better clarity? 1) How to load data from a csv directly to the sqlite? 2) Where is the original sqlite file getting stored?? How can we access it directly apart from the client? 3) Because of the module's blocking nature it couldn't be executed in eval script of redis. So we need to run a microservice which directly hits the redis commands as we hit in the client terminal. Is there any other possible way of doing it in API??

We are planning to use this module as a queryable cache layer. For that we need to load data directly into the sqlite file. Please reply with your suggestions

[siscia]

Hi @karthik56 , thanks for your feedback.

The reality is that what you see from the documentation is half of the module capabilities. Unfortunately I am very very busy at the moment and so I don't have the time to document everything. Which I know is a shame, but I really don't have the time.

I will answer your question here and I will leave the issue open so that I will remember to update the doc.

1. Unfortunately there is not a simple way to import CSV files. However this can be added quite easily.

I am wondering what interface sound better to you? Supposing the file flights.csv is inside the server I am considering something like this REDISQL.IMPORT_CSV DB /path/to/flights.csv flights which will create a table flights inside the database bounded to DB and inside that table will import all the values. The command will abort in the case the table flights is already present or if there are any issues in the CSV. HOWEVER please note that this is quite a messy business. There is no a standard used definition of CSV and slightly different formats will mess everything. Moreover the table will never be sure of the type of the columns, which makes everything even more complex. To summarize I am not sure yet that this is a good idea. Maybe it would be more interesting to load .sql files, they have a clear syntax and semantics, are easily portable and it is quite easy to "translate" a CSV into a .sql file.

If you need to import a csv inside rediSQL, right now, I will suggest to write a small script (maybe in python or ruby) that does just that.

2.If you create a new database with REDISQL.CREATE_DB DB you will create a :memory: database. You can use the similar syntax: REDISQL.CREATE_DB DB \path\to\file\database.sqlite to create a file-backed database. Please, be carefull with the trade-off between performance and reliability. The more reliable your DB is the less perfomant will be. If you create a file-backed db you can use the several PRAGMAs that SQLite provides you as knobs to manage this trade-off.

3. I am not sure I understood. Do you mind to re-word your question? The last release of the module should not be blocking.

Please, any feedback is appreciate :)

[karthik56]

Hi @siscia, Thanks for the response. My java client Jedis doesn't support Modules. I have to use eval scripts to achieve it. When I try "EVAL" "return redis.call('REDISQL.EXEC','DB','CREATE TABLE foo(A INT, B TEXT);;')" "0"

I get ERR Blocking module command called from Lua script. Is there any workaround?

[siscia]

:)

Jedis seems quite more complex than the client I usually work with.

Have you had a look at this file? https://github.com/xetorthio/jedis/blob/b03d4231f4412c67063e356a7c3acf9bb7e62534/src/test/java/redis/clients/jedis/tests/ModuleTest.java

[karthik56]

Hi @siscia, Will check that. The jar which I am using doesn't have these methods. May be I have to compile the source code. I will check that. Thanks for your response.

I have Two more doubts 1) I am planning to use this module as a queryable cache layer. Tables will be available already in the primary database, required data will be loaded once (write once) and queried multiple times. If data in the primary database changes this table will be dropped. So I need a load command to make fullest use of this module. In this case the table structure is well known already so a create table command will be executed 1st and then the load command will be executed which will load the data into redisql. Instead of import you can give a load command which will throw error if the table isn't present already or if the values mis-matches with the column definition. 2) If redis slave is configured with the redisql module, will the sqlite tables be replicated to the slave as well?

[siscia]

Hi,

how big is the table you are talking about?

As now there is not a load command and I am not that sure that is a good idea to add it. An idea could be to generate a dump of your table and just run something like REDISQL.EXEC DB "your dump here" This approach is not ideal but not even that bad. (You need to pay the network IO anyway, the module is not blocking so normal redis operation should keep going.)

Another idea could be to generate a SQLite database with your dump in your local machine, move the SQLite file in the redis machine and create a new DB using that same database as base.

It really depends on your use case.

Replication is not yet implemented. There is some issues on the replication and state that I need to figure out before to proceed to implement it. Anyhow, I don't believe that Replication will be a free feature. I may ask user to pay something for support the project.

[siscia]

@karthik56 Finally we got a Java Client.

You can get it from here: https://github.com/RedBeardLab/JRediSQL

Cheers,