Is SQL Server Table-Valued Function supported?

[jkhchan307]

This is a question or request to include SQL Server Table-Valued Function tables.

[jkhchan307]

I have a number of Table-Valued Function that I would like to also provide API for them. Is it possible and if so how? Thanks!

[thomaxxl]

Hi,

I haven't worked with table-valued functions before. What does the corresponding sqlalchemy model look like?

It's certainly possible to expose these in the json api and swagger because it's possible to expose any kind of data. It might take a bit of work to work out the models though.

[valhuber]

Automation would be great, we will look into that, but you always have the option to just write services: https://github.com/valhuber/ApiLogicServer/wiki/Tutorial#customize-api

On Wed, Apr 14, 2021 at 3:10 AM Thomas Pollet @.***> wrote:

Hi,

I haven't worked with table-valued functions before. What does the corresponding sqlalchemy model look like?

It's certainly possible to expose these in the json api and swagger because it's possible to expose any kind of data. It might take a bit of work to work out the models though.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/valhuber/ApiLogicServer/issues/14#issuecomment-819402172, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABKSG33L6T4P3QMO74HBDOLTIVSYJANCNFSM4224JG6A .

-- Thanks, Val

510-703-5909 valhuber on skype

[jkhchan307]

I don't know anything about sqlalchemy but here is the command to get the definition of TVF's in SQL Server:

SELECT TABLE_CATALOG AS [Database], TABLE_SCHEMA AS [Schema], TABLE_NAME AS [Function], COLUMN_NAME AS [Column], DATA_TYPE AS [Data Type], CHARACTER_MAXIMUM_LENGTH AS [Char Max Length] FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS WHERE TABLE_NAME IN (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' AND DATA_TYPE = 'TABLE') ORDER BY TABLE_NAME, COLUMN_NAME;

Hope this helps give some ideas.

[valhuber]

Thanks for this... this is currently not supported in the sense of automated.

In the meantime, you can certainly invoke TVFs in writing your own services.

ApiLogicServer / SAFRS provide powerful automation for SQLAlchemy, an ORM for Python. For most projects, I would expect you'll need to learn at least the basics of SQLAlchemy, to extend the ApiLogicServer / SAFRS automation.

We will look into whether the feature can be automated. It would help our investigation if you give us a good sense of the value / urgency here - is this an evaluation, or committed project, etc. It would probably be best to confer - you can reach me at [email protected].

[jkhchan307]

Actually I recently wrote a .NET Core application to do exactly what ApiLogicServer can offer and more (what I meant was that ApiLogicServer can actually more than my application). Although it is already working, I had to pay for a library to automate the generation of all the data classes corresponding to the tables and TVF's. I also had to cookie cutting each controller corresponding to each API. Filtering and sorting possible for each API also need to be hand coded. As you can imagine how excited I am to see ApiLogicServer doing all these automatically. Of course I still have to test the stability and performance of ApiLogicServer but at this moment the key missing piece is the automation of TVF's as well. The database I am targeting has hundreds of them so hand coding would not be possible.

Thanks!

[thomaxxl]

It should be possible to automate this to some extent. I'll look into this next week.

[valhuber]

As Thomas noted, we’ll have a look next week. Some more perspective would be useful:

1. Is the functionality meeting your needs in other respects?
2. What is your time frame?

[jkhchan307]

From what I have seen so far I believe it would. But it would depend on whether the automation would work with TVF's as good as it would for normal tables and views. Also, I need to test the performance as well. I already have a working setup calling API's from within Salesforce. Once the automation for TVF's is in place, all I have to do is to replace the .NET Core gateway with ApiLogicServer and see how well it compares. Thanks!

[thomaxxl]

API performance and stability are well tested and should be fine for most use cases. However due to the generic nature, some adjustments may have to be made for certain API calls. For example, when fetching a collection, safrs counts all object in the corresponding table so it can return the jsonapi "count" parameter. Now, doing a count on a table with millions of rows might take a couple of seconds. In a case like that it's possible to tweak the model, but this is pretty difficult if you're not familiar with the software.

Please open an issue should you encounter performance problems.

[thomaxxl]

I created an example for calling a postgres TVF using safrs jsonapi_rpc (this only works with the latest commits from the safrs repo).

https://gist.github.com/thomaxxl/f8cff63a80979b4a4da70fd835ec2b99

You could extract the TVF function prototype from the db schema and generate the code for this, but this isn't automated at this point.

[valhuber]

Thanks, @thomaxxl

@jkhchan307 .... Your SQL posted above was very useful... good start for generating the model. I am guessing you also want the Swagger to identify the args. Is there a similar query for that?

Oh wait, I think I found it here.

I am not real familiar with TVFs, but I believe you can pass tables as arguments... is that required for your situation?

[jkhchan307]

Sorry but I am not sure I understand your question.

[valhuber]

Sorry, let me clarify. There were 2 matters:

1. I was unclear how to get metadata for the args, but the subsequent update with the link ("Oh, wait)" was to show I have found it.

2. The second question is whether your TVF args are always simple scalar values - a simple number, string etc. I think I have read that you can also pass tables to your TVF (think - a result list), so was wondering if you do that.

Hope that helps... Val

[jkhchan307]

Thanks Val. No need to pass table names to TVF’s as arguments. Should be able to treat TVF as a regular table in most cases.

[valhuber]

I have provided an exploratory proposal here. It creates a tvf.py file, which would normally be placed under your api folder, and imported from api/expose_services.py. (The current example is .txt to avoid impacting ApiLogicServer)

@jkhchan307 please see if this looks like a reasonable approach. You are welcome to extend the file; if we go this direction and you get it working, I will be happy to pull your example into ApiLogicServer.

@thomaxxl comments solicited. I did not know how to code the rpc so that it designates in table meta data, though I vaguely recall such an option exists.

[valhuber]

@jkhchan307 -- @thomaxxl and I met today to review the approach, and we think it makes sense. The example code will not yet run, but I am hoping you can get a good sense of where it's headed. It would be great to hear if you concur on this approach.

[valhuber]

@jkhchan307 - starting to run, with issues. Important to understand whether this approach seems workable to you, described here.

@thomaxxl - my error was trying to run get instead of post, and a few swagger comments issues. I am still able to expose only 1 service... exposing the second yields this:

Inspect finds base <class 'safrs.jabase.JABase'>
Inspect finds base <class 'safrs.jabase.JABase'>
Inspect finds base <class 'safrs.jabase.JABase'>
Inspect finds base <class 'safrs.jabase.JABase'>
[2021-04-27 22:12:26,697] INFO: Exposing method udfEmployeeInLocationWithName.udfEmployeeInLocationWithName on /udfEmployeeInLocationWithName/udfEmployeeInLocationWithName, endpoint: api.udfEmployeeInLocationWithName.udfEmployeeInLocationWithName
[2021-04-27 22:12:26,702] INFO: Exposing udfEmployeeInLocationWithName on /udfEmployeeInLocationWithName/, endpoint: api.JAType
Traceback (most recent call last):
  File "/Users/val/Desktop/ApiLogicServer/TVF/api_logic_server_run.py", line 107, in <module>
    flask_app, safrs_api = create_app(host=host)
  File "/Users/val/Desktop/ApiLogicServer/TVF/api_logic_server_run.py", line 96, in create_app
    expose_services.expose_services(flask_app, safrs_api, project_dir)  # custom services
  File "/Users/val/Desktop/ApiLogicServer/TVF/api/expose_services.py", line 45, in expose_services
    api.expose_object(tvf.udfEmployeeInLocationWithName)
  File "/Users/val/Desktop/ApiLogicServer/TVF/venv/lib/python3.8/site-packages/safrs/safrs_api.py", line 130, in expose_object
    self.add_resource(api_class, url, endpoint=endpoint, methods=["GET", "POST"])
  File "/Users/val/Desktop/ApiLogicServer/TVF/venv/lib/python3.8/site-packages/safrs/safrs_api.py", line 385, in add_resource
    super(FRSApiBase, self).add_resource(resource, *urls, **kwargs)
  File "/Users/val/Desktop/ApiLogicServer/TVF/venv/lib/python3.8/site-packages/flask_restful/__init__.py", line 392, in add_resource
    self._register_view(self.app, resource, *urls, **kwargs)
  File "/Users/val/Desktop/ApiLogicServer/TVF/venv/lib/python3.8/site-packages/flask_restful/__init__.py", line 428, in _register_view
    raise ValueError('This endpoint (%s) is already set to the class %s.' % (endpoint, previous_view_class.__name__))
ValueError: This endpoint (api.JAType) is already set to the class JAType_API.
Inspect finds base <class 'safrs.jabase.JABase'>
Inspect finds base <class 'safrs.jabase.JABase'>
Inspect finds base <class 'safrs.jabase.JABase'>
Inspect finds base <class 'safrs.jabase.JABase'>

[jkhchan307]

@valhuber Sorry that I am getting a bit busy at work. Initially I think so but need to wait to have a bit more time to try it out. Honestly it really depends on how much hand coding there is. Thanks again!

[valhuber]

@jkhchan307 Great to hear from you, thanks for getting back. We all get snowed under from time to time, so appreciate your taking the time...

I am sure I am speaking fot @thomaxxl when I say we agree that hand-code reduction is the right metric. I am hoping you were able to scan this page and discover that:

1. We are successfully scanning a Sql/Server schema to create an endpoint for each TVF, with arguments
2. And generate swagger
3. And it runs (both swagger and cURL), returning results

We clearly have a bug on exposing multiple services. I am no doubt Thomas will straighten me out in short order, and that it's not a fundamental blocker. We will keep you posted (though Thomas has his own work emergencies ;) ).

So, can you look over this page, and confirm that (presuming the bug fix) that the current approach will reduce the code as you had hoped?

I do think a short zoom meeting or email discussion might be helpful in confirming this... I am [email protected].

Best, Val

[valhuber]

@thomaxxl has a fixed that I have tested and worked. I will be rolling out an update shortly.

@jkhchan307 alerting you, and wondering if you are in a position to try this on your database...?

[valhuber]

@jkhchan307 just pushed update with multiple running TVFs, described here. We are prepared to close this item if this works for you.

@thomaxxl FYI

[valhuber]

@jkhchan307 @thomaxxl This is now released as the current version in PyPi. This release uses the Extended Builder framework to:

1. Scan a Sql/Server schema for 1 or more Table Valued Functions
2. Build a service (end-point) for each, with parameter and Swagger support
3. Constructed services are automatically available and fully executable (e.g., via Swagger or cURL).

@jkhchan307 this approach is now code-free, so please advise whether the functionality meets your requirements.

[jkhchan307]

Fantastic news. Will try it out as soon as I can.

On Sat, 1 May 2021 at 10:34 AM, Val Huber @.***> wrote:

@jkhchan307 https://github.com/jkhchan307 @thomaxxl https://github.com/thomaxxl This is now released as the current version in PyPi. This release uses the Extended Builder https://github.com/valhuber/ApiLogicServer/wiki/Extended-Builder framework to:

1. Scan a Sql/Server schema for 1 or more Table Valued Functions
2. Build a service (end-point) for each, with parameter and Swagger support
3. Constructed services are automatically available and fully executable (e.g., via Swagger or cURL).

@jkhchan307 https://github.com/jkhchan307 this approach is now code-free, so please advise whether the functionality meets your requirements.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/valhuber/ApiLogicServer/issues/14#issuecomment-830494182, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEZUE6E7LQSWSTRUH4PG4GLTLNSEDANCNFSM4224JG6A .

[valhuber]

Great. In addition to uninstalling/re-installing ApiLogicServer, you will need to do the same for SAFRS. Or, just created a new venv. Please advise if I'm not being clear...

My pip freeze:

aniso8601==8.1.1
apispec==3.3.2
attrs==20.3.0
Babel==2.9.0
cffi==1.14.4
click==7.1.2
colorama==0.4.4
cryptography==3.3.1
defusedxml==0.6.0
dnspython==2.1.0
email-validator==1.1.2
Flask==1.1.2
Flask-Admin==1.5.7
Flask-AppBuilder==3.1.1
Flask-Babel==1.0.0
Flask-Cors==3.0.10
Flask-JWT-Extended==3.25.0
Flask-Login==0.4.1
Flask-OpenID==1.2.5
Flask-RESTful==0.3.8
flask-restful-swagger-2==0.35
Flask-SQLAlchemy==2.4.4
flask-swagger-ui==3.36.0
Flask-WTF==0.14.3
greenlet==1.0.0
idna==3.1
inflect==5.0.2
itsdangerous==1.1.0
Jinja2==2.11.2
jsonschema==3.2.0
logicbank==0.9.6
logicbankutils==0.6.0
MarkupSafe==1.1.1
marshmallow==3.10.0
marshmallow-enum==1.5.1
marshmallow-sqlalchemy==0.23.1
pio==0.0.3
prison==0.1.3
psycopg2-binary==2.8.6
pycparser==2.20
Pygments==2.7.4
PyJWT==1.7.1
pymssql==2.2.0
PyMySQL==1.0.2
pyodbc==4.0.30
pyrsistent==0.17.3
python-dateutil==2.8.1
python-dotenv==0.15.0
python3-openid==3.2.0
pytz==2020.5
PyYAML==5.4.1
safrs==2.11.3
six==1.15.0
SQLAlchemy==1.3.24
SQLAlchemy-Utils==0.37.0
Werkzeug==1.0.1
WTForms==2.3.3

[jkhchan307]

Is my understanding correct that I don't need to use the extended builder to include TVF's in the generation anymore or I still do?

[valhuber]

You must use the Extended Builder. TVFs are automated via the sample ExtendedBuilder, not the core product (Extensible Automation).

You just find the sample file (it's in your venv install area; on my Mac, it is /Users/val/Desktop/ApiLogicServer/venv/lib/python3.8/site-packages/api_logic_server_cli), and copy it to your directory per this screen:

[jkhchan307]

Sorry but I just noticed that I did not get API successfully generated for all tables. For example, I would get result likes below:

t_Account_extract = Table( 'Account_extract', metadata, Column('TW_ACCOUNT__R NAME', String(500, 'Chinese_Taiwan_Stroke_BIN')), Column('TW_ACCOUNT__R TW_TAX_NUMBER__C', String(500, 'Chinese_Taiwan_Stroke_BIN')), Column('TW_TRANSACTION_ACCOUNT_NAME__C', String(500, 'Chinese_Taiwan_Stroke_BIN')), Column('TW_TA_ID__C', String(500, 'Chinese_Taiwan_Stroke_BIN')), Column('TW_ACCOUNT__R OWNER NAME', String(500, 'Chinese_Taiwan_Stroke_BIN')) )

class AcrmCalender(SAFRSBase, Base): tablename = 'Acrm_Calender'

dt = Column(String(7, 'Chinese_Taiwan_Stroke_CI_AS'), primary_key=True, nullable=False)
fno = Column(String(12, 'Chinese_Taiwan_Stroke_CI_AS'), primary_key=True, nullable=False)
tdate = Column(String(7, 'Chinese_Taiwan_Stroke_CI_AS'), nullable=False)
vdate = Column(String(7, 'Chinese_Taiwan_Stroke_CI_AS'), nullable=False)

[valhuber]

The first is (I am pretty sure) a view; sqlacodegen uses t_ for those. The second is a table, which looks ok except for the missing indent for tablename...

Can you extract the schema (not data), including the TVFs? I need to see where / how it fails....

[valhuber]

BTW, was it your expectation to expose views? I was noting those are generated as Tables, but not exposed as APIs by SAFRS. This is because they have no pKey....

If you need that, we might be able to extend the TVF Extended Builder to create services for views too (without update)...?