Select from view?

[heyciao]

Hi

How can I select all records from a view? ( https://www.tarantool.io/en/doc/2.2/reference/reference_sql/sql/#create-view ) (the view has already "order by" and "limit" clauses embedded)

All my attempts ended with the error message "No index #0 is defined in space 'MYVIEW'". It's just a view so it doesn't have any index... .

Thank you

[Totktonada]

I guess that the only way for now is to perform a select from the view via SQL. The connector does not support IPROTO_EXECUTE (a code to execute an SQL statement) for now, so the only way to perfrom SQL is to call box.execute([[<sql-statement>]]) Lua function.

AFAIR @robinhood23rus said once that he had some PoC of IPROTO_EXECUTE support. Denis, is it in such state that can be shared?

[denis-ignatenko]

As for now it is WIP in branch dbapi2. IPROTO_EXECUTE works fine but full dbapi is not finished yet

[heyciao]

Thanks a lot!! The ability to freely execute SQLs was actually my original goal (the view was just a workaround because I didn't understand how to join tables by using the available class methods) :)

Therefore, if I understood correctly (I am completely clueless about both Tarantool and Lua), this is what I did:

1) Added at the bottom of "/etc/tarantool/instances.available/tarantool-myinstance.lua" this function definition:

function myfunc_run_sql(sSQL)
   return box.execute(sSQL)
end

2) Started Tarantool

3) Checked in "tarantoolctl" that I could execute the function myfunc_run_sql('select * from myview')

4) Wrote the following Python code:

self.dbHandle = tarantool.connect(host="myhost", port=3301)

sSQL = "select * from myview"
result = self.dbHandle.call("myfunc_run_sql", (sSQL,))

5) Checked / broke down the result ("response"):

print("FULL RESULT: \n\"" + str(result) + "\"" )
print("FULL RESULT - TYPE: \"" + str(type(result)) + "\"" )
print("\n")
print("RESULT DATA: \n\"" + str(result.data) + "\"" )
print("RESULT DATA - TYPE: \"" + str(type(result.data)) + "\"" )
print("\n")
print("RESULT DATA- FIRST ROW: \n\"" + str(result.data[0]) + "\"" )
print("RESULT DATA- FIRST ROW - TYPE: \"" + str(type(result.data[0])) + "\"" )
print("\n")
print("RESULT DATA- FIRST ROW - ROWS: \n\"" + str(result.data[0]["rows"]) + "\"" )
print("RESULT DATA- FIRST ROW - ROWS - REAL ROWS => loop:")
for uiRownum in range(0, len(result.data[0]["rows"])):
    print("Row " + str(uiRownum) + ": " + str(result.data[0]["rows"][uiRownum]))

It seems to work perfectly. Anything against this? Am I doing something superfluous or not optimal or just stupid? Thx

[Totktonada]

It looks okay, but you can call box.execute directly, w/o defining myfunc_run_sql.

You may also consider the alternative Python connector, which has superior feature set, including SQL support.

[heyciao]

Ok, got it => changed code from... result = self.dbHandle.call("myfunc_run_sql", (sSQL,)) ...to... result = self.dbHandle.call("box.execute", (sSQL,)) Thank you!

[heyciao]

( btw. I did look at asynctnt but I just didn't manage to use that async class in my existing non-async python code (I already use multiple processes). I tried to use "async_to_sync" of the "asgiref" package but it kept crashing, therefore I came back to this )