Buto
commented
7 years ago Sails-oracledb-sp supports 'find', but not 'query'.
What Sails-oracledb-sp does is a bit unnatural so I think I owe you some explanation.
When Sails.js is configured to operate on SQL databases it expects to conduct its operations on tables. Sails.js' model describes a table's topology (e.g., the table's name, its columns' names, each column's datatype, which column contains the primary key and so on.) This enables the underlying adapter to automatically generate the correct SQL for that table.
Sails.js is not designed to invoke stored procedures. Sails-oracledb-sp was designed to add the ability to invoke Oracle stored procedures to Sails.js.
Sails-oracledb-sp works by enabling a suitable set of stored procedures to masquerade as a table in a SQL database. This set of stored procedures support the four CRUD (Create, Read, Update and Delete) operations. This set of stored procedures can wrap a table and provide CRUD operation on it. Such a stored procedure can also present a higher-level abstraction to external clients and operate on multiple tables.
You can arrange things such that each table has its own set of stored procedures. In this case Sails.js will, in effect, generate REST APIs for each table.
You can also design a set of stored procedures that function at a higher level of abstraction and manage multiple table, conduct join operations on multiple tables and so on. In this case Sails.js will expose these stored procedures as a REST API that enables the client to conceptually interact with an arbitrarily complex object and delegate the low-level table operations to the stored procedure. (I consider this mode to be a best practice.)
Sails-oracledb-sp repurposes Sails.js' model so as to describe this set of stored procedures.
For brevity I am going to skip the mechanics of how to adapt a Sails.js model to a set of stored procedures. That said, there is a particular point that will become important later in this post: the Sails.js model, when used with Sails-oracledb-sp, is oblivious to any tables in the database. This model only describes a set of stored procedures. Generally the stored procedure's parameters matches the columns in its underlying table—BUT—it is fine for the stored procedure to have additional parameters that have no counterpart in the table's columns, as necessary, so as to support your requirements. I will return to this last point later.
When the client invokes one of the REST APIs Sails.js passes the call to Sails-oracledb-sp. Sails-oracledb-sp conducts one of the CRUD operations on behalf of Sails.js by invoking the stored procedure that matches Sails' request. In the case of the 'find' operation Sails-oracledb-sp invokes the stored procedure designed to query a table. This stored procedure returns its results to the caller as a resultset.
Sails-oracledb-sp doesn't use the 'query' operation to call a stored procedure because 'query' enables the caller to specify a SQL string. This does not fit Sails-oracledb-sp's implementation because our design expects 100% of the SQL to be contained by the stored procedure that conducts query operations. That said, you can pass the variables to be bound to the SQL query via the stored procedure's parameters.
For certain kinds of queries (e.g., fetch all rows in the table, fetch the row that matches a particular primary key value, fetch the rows where a caller-specifies the value found in column 'A' and column 'B', etc.) the stored procedure only needs parameters that map to the columns in the underlying table. If the SELECT statement in the stored procedure needs something like “WHERE foo BETWEEN min_foo AND max_foo” then the stored procedure will also need min_foo and max_foo parameters.
Note well, PL/SQL supports polymorphism, which means you can have multiple versions of the stored procedure that conducts read operations on its table or tables. These stored procedures each have a unique combination of parameters. Each of of these can have their own unique SQL queries. A specific stored procedure is invoked by specifying its particular parameters in the GET call to the REST APIs exposed by Sails.js.
Does this adapter support the query method documented here?
http://sailsjs.com/documentation/reference/waterline-orm/models/query