4 ms·

No comment re rDBs supporting parallelized ML but re stored procedures - if your workflow evolves to treat them as ‘1st class’ code assets they’ll be just the s

No comment re rDBs supporting parallelized ML but re stored procedures - if your workflow evolves to treat them as ‘1st class’ code assets they’ll be just the same as the rest of your code. We always had them in version control, unit tested etc. The tools are there if you want to use them.

itsthecourier 4y ago
Updating stored procedures was a pain in the ass last time I checked. Checking changes on them in deployed servers was too very painful
spprashant 4y ago
I ll take this as a learning opportunity. I have looked around to find a reliable framework to implement within our team and failed to find anything usable. How do you guys manage to implement versioning and testing? If you had a new stored procedure to deploy, where do you deploy? How to you integrate with existing applications which rely on it?
- dventimi 4y ago
  For testing if I'm in PostgreSQL I use pgTAP. For version control I use git just as I do for other program units.
- rukuu001 4y ago
  Depends on what you're using. The Java world has a bunch of JDBC wrappers for testing SPs. SQLServer GUI has unit testing tools that can also be run from command line. @dventimi has good postgres recommendation. Version control - each SP is in a file sp_descriptive_name.sql that's on the source tree under /db. Deployment - compiled from the command line. Deployment target managed by env variables. Systems can require odd workarounds for ensuring no downtime - this is where systems end up with sp_descriptive_name_vN with N for each version (still stored in sp_descriptive_name.sql tho) so the new SP can be loaded alongside the old version, and then application code can be updated (or config updated) to use the new SP name. Good practice to remove the old version on next release. I think it's true that SPs introduce a compromise, but having logic operate on data without having to send data back and forth over DB connection is a pretty big win. It can also be a good abstraction because it avoids application code needing to know (for example) which field in which table indicates a customer is active, and code can execute something like sp_deactivate_customer(cust_id) and then let the stored procedure take care of updating flags, dates etc that describe a customer's active status. ymmv depending on whether your team owns the data and database, or if that's managed separately.

itsthecourier 4y ago

Updating stored procedures was a pain in the ass last time I checked. Checking changes on them in deployed servers was too very painful

spprashant 4y ago

I ll take this as a learning opportunity. I have looked around to find a reliable framework to implement within our team and failed to find anything usable. How do you guys manage to implement versioning and testing? If you had a new stored procedure to deploy, where do you deploy? How to you integrate with existing applications which rely on it?

dventimi 4y ago
For testing if I'm in PostgreSQL I use pgTAP. For version control I use git just as I do for other program units.
rukuu001 4y ago
Depends on what you're using. The Java world has a bunch of JDBC wrappers for testing SPs. SQLServer GUI has unit testing tools that can also be run from command line. @dventimi has good postgres recommendation. Version control - each SP is in a file sp_descriptive_name.sql that's on the source tree under /db. Deployment - compiled from the command line. Deployment target managed by env variables. Systems can require odd workarounds for ensuring no downtime - this is where systems end up with sp_descriptive_name_vN with N for each version (still stored in sp_descriptive_name.sql tho) so the new SP can be loaded alongside the old version, and then application code can be updated (or config updated) to use the new SP name. Good practice to remove the old version on next release. I think it's true that SPs introduce a compromise, but having logic operate on data without having to send data back and forth over DB connection is a pretty big win. It can also be a good abstraction because it avoids application code needing to know (for example) which field in which table indicates a customer is active, and code can execute something like sp_deactivate_customer(cust_id) and then let the stored procedure take care of updating flags, dates etc that describe a customer's active status. ymmv depending on whether your team owns the data and database, or if that's managed separately.