Page 2 of 2

Re: what is a stored procedure?

Posted: Fri Dec 25, 2009 3:27 pm
by Eran
What kind of API exactly?
E.g. you have a C# desktop application, an Asp .Net web site and a PHP Intranet web application (real case of mine). What do you suggest? Which platform will be the "winner"? How should I convince the other developer teams the it should be done somehow...?
As I was saying, it depends on the cohesion between the database and the applications. The most obvious usecase is that of web-services and how you consume them via HTTP requests. If the database is local to all the applications, it might be better served by daemon-type API. One of the applications (the main application, if there is one) would be responsible for providing the API.

Re: what is a stored procedure?

Posted: Fri Dec 25, 2009 9:55 pm
by alex.barylski
E.g. you have a C# desktop application, an Asp .Net web site and a PHP Intranet web application (real case of mine). What do you suggest? Which platform will be the "winner"? How should I convince the other developer teams the it should be done somehow...?
REST FTW IMO :)
An SP is an abstraction in this case, IMHO ...
I wouldn't say it's an abstraction so much as it is simply shifting the responsibility from the core language to that of SQL. Using the stored proc would actually couple your solution to a RDBMS, would it not? Whereas a REST web service is truly abstract as the data store is agnostic, could be XML files or RDBMS.
One of the applications (the main application, if there is one) would be responsible for providing the API
The web applications are almost always impetus, IME. They provide the REST and web interface, and C# or VB.NET applications utilize the web serivces layer.

Cheers,
Alex

Re: what is a stored procedure?

Posted: Tue Dec 29, 2009 4:57 am
by VladSun
IMHO, using an REST API will add another point of failure (i.e. the Apache service) for applications that would not need it otherwise (i.e. the desktop applications). Also, delegating the API to one of the applications may be a problem if it becomes redundant and is to be removed in the future. Sounds like coupling applications to an application.

SPs may be a way to share and encapsulate business logic between applications that can't share it otherwise (e.g. PHP and .Net). The arguments that SP usage will "couple" the applications to a RDBMS make no sense to me, because I haven't used (and I don't think it will change in the next 10 years) any other data storage (like XML).

Also, an DB abstraction layer will provide a uniform interface to the SP/SPL used in different DB engines, so it's not an issue.

Re: what is a stored procedure?

Posted: Tue Dec 29, 2009 7:19 am
by jayshields
To chime in, in my opinion/experience, if you've got one application using one database then SPs are mostly useless because all your potential SPs will have an equivalent in your model.

On the other hand, when you've got multiple applications using one database then SPs are useful because it can dramatically reduce reproducing the same code across the applications.

Views are (again, in my opinion/experience) different to SPs because they are literally just alternate views of the database. One example might be to create a view which is a join of two tables, this is useful when it is very common to join these two tables, so that any queries which would do that join make more sense and are more succinct (across different applications or just in one application).

Re: what is a stored procedure?

Posted: Tue Dec 29, 2009 7:39 pm
by alex.barylski
IMHO, using an REST API will add another point of failure (i.e. the Apache service) for applications that would not need it otherwise (i.e. the desktop applications).
Not sure I follow...what difference does it make whether the API is REST (ie: Apache) or direct MySQL remote connection, if either fail your users suffer. Desktop applications would use the RESTful data just the same as a web service would if it to relied on a web service. You only use what you need, if your desktop application didnt require any web service data you wouldnt use it, so I am not sure I understand your perspective here.
Also, delegating the API to one of the applications may be a problem if it becomes redundant and is to be removed in the future. Sounds like coupling applications to an application.
Delegating the API to one of the applications is the fundemental idea of centralizing data access in an abstract way, thus done with RESTful web services is about as simple, intutive and abstract as we can get. A RESTful API is easier to refactor but thinking in terms of REST (as I experienced) usually makes refactoring unlikely, whereas with SOAP or XML-RPC from what I understand, would be more troublesome.

The idea a web service is ON all the time is what makes RESTful so appealing. No matter the application (desktop, iPhone, etc) so long as you have access to the internet you could utilize the RESTful API. In my experience, the only thing that becomes redundant is the desktop applications, as more and more applications move to the web.
SPs may be a way to share and encapsulate business logic between applications that can't share it otherwise (e.g. PHP and .Net). The arguments that SP usage will "couple" the applications to a RDBMS make no sense to me, because I haven't used (and I don't think it will change in the next 10 years) any other data storage (like XML).
SP's introduce a a concrete requirement on a specific RDBMS. Sure you could use PHP's or .Net's MySQL API and use stored procs on the server, but what if you one day want to switch to MSSQL or heaven for bid, SQLite? It really depends on the type of applications you build and the clientele you support I suppose.

On one hand, internal Intranet applications are usually less concerned with RDBMS flexibility, whereas COTS purchased by clients and installed on their own servers causes the flexible requirement for multiple DB support to skyrocket, IMO. In that case, it makes sense to use a RESTful web service API.

Personally I rely almost entirely on SQL but not exclusively. And while I see SQL staying for a long while its nice to be able to use other stores as needed and maybe giving access to them.
Also, an DB abstraction layer will provide a uniform interface to the SP/SPL used in different DB engines, so it's not an issue.
Sure of course, but do stored procs optimized for say MSSQL port automatically to MySQL. If they dont using a DB asbtraction layer only abstract the core language from the annoyance of having to switch RDBMS, not the DBA. They would still have to port stored procs from one to another.

Ideally when working with SQL, only the lowest common denominator of commands would be used, no stored procs, no views, no vendor specific enhancements. That way switching from one RDBMS to another is a trivial matter of reinstalling, etc.

This is an ideal that is rarely realistic, I agree, but the less work you have to do in switching the better. While I have yet to work on a project that managed a switch 100% without programmer intervention I have worked on various projects where the amount of work to switch from MSSQL to MySQL or whatever was significantly more work than those who followed a KISS approach to RDBMS usage.
On the other hand, when you've got multiple applications using one database then SPs are useful because it can dramatically reduce reproducing the same code across the applications
You can achieve the exact (if not better-cleaner) same result in the core language by carefully and closely refactoring your code, so I am not sure I agree 100%.

Personally the only time I use stored procs are when I have a dedicated DBA on site that knows how to tweak queries which are otherwise impossible to achieve using an ORM solution, data mapper, OO SQL, etc.

If there were a search function which was relatively complex I would rather have that stuffed in a stored proc (for maintenance, performance, etc) than writing it and maintaining it myself in PHP.

Where we obviously differ, is I would use stored procs sparingly (but not rule them out completely) whereas others feel they are a replacement for *part* of the domain model. Different projects, different requirements, different developers, different solutions. :P

Cheers,
Alex

Re: what is a stored procedure?

Posted: Wed Dec 30, 2009 6:25 am
by jayshields
PCSpectra wrote:
On the other hand, when you've got multiple applications using one database then SPs are useful because it can dramatically reduce reproducing the same code across the applications
You can achieve the exact (if not better-cleaner) same result in the core language by carefully and closely refactoring your code, so I am not sure I agree 100%.
I'm not sure I follow you here - core language?

A real example which I worked on was a system which had a (deployed) Java app for use on a hand-held device and a PHP app for a Web interface. Both of these apps used the same database and executed some of the same queries.

As far as I can see, the easiest and best way to avoid code duplication there is to use SPs. Sure there's a couple of other ways (which don't make as much sense), like having the Java app and PHP app call the same PHP script which returns the query result (this probably has a swanky name/acronym which I don't know). This would decrease the performance of both apps (especially the Java app if the PHP server and DB server are on separate machines) - so it's clearly better to use SPs.

Re: what is a stored procedure?

Posted: Wed Dec 30, 2009 6:29 am
by VladSun
PCSpectra wrote:
IMHO, using an REST API will add another point of failure (i.e. the Apache service) for applications that would not need it otherwise (i.e. the desktop applications).
Not sure I follow...what difference does it make whether the API is REST (ie: Apache) or direct MySQL remote connection, if either fail your users suffer. Desktop applications would use the RESTful data just the same as a web service would if it to relied on a web service. You only use what you need, if your desktop application didnt require any web service data you wouldnt use it, so I am not sure I understand your perspective here.
...
The idea a web service is ON all the time is what makes RESTful so appealing.
A web service may be not ALWAYS ON because of various reasons - hardware issues for example.
While if the DB fails, all application will fail, that's not true if a web service failure occurs for applications that have direct DB access, is it?
That's why I said "REST is another point of failure introduced".

In my particular project, if the web site is down, users can still make orders by using the callcenter service (the desktop application with a direct DB access) ...
PCSpectra wrote:
Also, delegating the API to one of the applications may be a problem if it becomes redundant and is to be removed in the future. Sounds like coupling applications to an application.
Delegating the API to one of the applications is the fundemental idea of centralizing data access in an abstract way, thus done with RESTful web services is about as simple, intutive and abstract as we can get.
I thought, such APIs are some kind of libraries, not applications.
PCSpectra wrote:
SPs may be a way to share and encapsulate business logic between applications that can't share it otherwise (e.g. PHP and .Net). The arguments that SP usage will "couple" the applications to a RDBMS make no sense to me, because I haven't used (and I don't think it will change in the next 10 years) any other data storage (like XML).
SP's introduce a a concrete requirement on a specific RDBMS.
It can also be said for pure SQL - different RDBMS have different SQL standard implementations... So, I don't think it's a good argument...
PCSpectra wrote:
Also, an DB abstraction layer will provide a uniform interface to the SP/SPL used in different DB engines, so it's not an issue.
Sure of course, but do stored procs optimized for say MSSQL port automatically to MySQL. If they dont using a DB asbtraction layer only abstract the core language from the annoyance of having to switch RDBMS, not the DBA. They would still have to port stored procs from one to another.

Ideally when working with SQL, only the lowest common denominator of commands would be used, no stored procs, no views, no vendor specific enhancements. That way switching from one RDBMS to another is a trivial matter of reinstalling, etc.

This is an ideal that is rarely realistic, I agree, but the less work you have to do in switching the better. While I have yet to work on a project that managed a switch 100% without programmer intervention I have worked on various projects where the amount of work to switch from MSSQL to MySQL or whatever was significantly more work than those who followed a KISS approach to RDBMS usage.
So, I see you agree with my previous argument :)
When I referred to the "DB abstraction layer" and "SP" I meant that only SP calls and results returning should be unified.


I have some more things to say, but I have to go now :) to be continued ...

Re: what is a stored procedure?

Posted: Wed Dec 30, 2009 6:31 pm
by alex.barylski
I'm not sure I follow you here - core language?
Core language, meaning the language that powers the majority of the application in this case, PHP.
far as I can see, the easiest and best way to avoid code duplication there is to use SPs. Sure there's a couple of other ways (which don't make as much sense), like having the Java app and PHP app call the same PHP script which returns the query result (this probably has a swanky name/acronym which I don't know).
RESTful web services. :)
This would decrease the performance of both apps (especially the Java app if the PHP server and DB server are on separate machines) - so it's clearly better to use SPs.
It does introduce a slight performance hit, at the cost of greater flexibility and reuse. This argument is similar to those by procedural developers who refuse to switch to OOP because they fail to see the additional asbtraction providing any benefit.

If you use a web service you:

1. Decouple from a specific data store (SQL, XML, third party web service, etc)
2. Decouple from the SQL specifics (SELECT is different in MySQL than MSSQL)
3. Increase security

Yes it is another asbtraction layer and yes it comes at the cost of performance, but you further decouple the design, make maintenance a little easier and maybe even improve security. If you provide direct access to a remote MySQL server so that localhost can connect and some desktop application, you increase your chance of having an exploit.

That being said, the web service itself might still use a stored procedure itself and improperly sanitizing the incoming data could still result in an exploit, but allowing third party application (other than localhost) connect to a remote server requires a skilled system admin to make sure everything goes as expected. You lessen the responsibility by using a web service, now your server doesn't need port 3036 open it just keeps on working with port 80 open, or maybe 443 (or whatever SSL listens on).
A web service may be not ALWAYS ON because of various reasons - hardware issues for example.
Your more experienced than me when it comes to system admin, and your experience may be different, but in my experience Apache is one of the most reliable services.
While if the DB fails, all application will fail, that's not true if a web service failure occurs for applications that have direct DB access, is it?
If apache fails, your web services are finished, yes. But if MySQL fails your web services are finished also, because they usually rely on MySQL as well. The client developer (whether extJS or C# or whatever) should be checking for connection activity and responding appropriately on failure, whether the results are a MySQL connection object or null or a empty JSON object or XML, the client is responsible for effective error handling and notification, IMO.
That's why I said "REST is another point of failure introduced".
I understood that much, I just don't agree 100%. :) Yes its another point of failure, but realisitically what goes down more often, RDBMS or Apache? Secondly, if the uptime is mission critical, setting up a secondary server in either case should be considered an option, no?

Technically speaking, it's not really another failure point, as Apache is already installed and running under 99% of circumstances, it's just shifting the responsibility exclusively from the RDBMS and sharing the responsibility with the Apache server as well, IMO.

It is not like you need to install and setup yet another system/daemon, such as the case for some servers.
In my particular project, if the web site is down, users can still make orders by using the callcenter service (the desktop application with a direct DB access) ...
And what happens when the DB server goes down? I supose you could cluster your DB's much like you could probably load balance multiple Apache servers??? If this is the case, this is certainly more robust a solution than relying on the DB not to fail over the Apache server or visa-versa.

I see what your saying though:

1. (Your Setup) if Apache goes down users can order through Desktop application as it connects directly to Apache
2. (REST Setup) if Apache goes down users can no longer order via desktop or web site

What I am saying is that even in your case, if the DB goes down, users can no longer order from the desktop or the web, the DB is down. So whether REST is used and Apache fails or direct connection is used and MySQL fails, the end users are stuck without service, so it would make sense to begin looking into redundant system, which is outside the scope of this conversation, unless you want to discuss that I'm listening, probalby best to start a new thread though. :)
I thought, such APIs are some kind of libraries, not applications.
I wouldn`t call it a library, per se, but definetely an API not so much an application. Just an API that allows anyone to manipulate a remote data store. Adding users, deleting users, etc. How you reneder the results is strictly dependent on the application consuming the results, such as extJS or C# or iPhone, etc.
It can also be said for pure SQL - different RDBMS have different SQL standard implementations... So, I don't think it's a good argument...
Well not in that context, no, but that is not what I am arguing. I am saying that RESTful services remove those requirements. If you can decouple the web application and the desktop application and your iPhone application from MySQL or MSSQL wouldnèt that be an ideal. The the web services layer developers only ever need to worry about SQL specifics, whether they use stored procs or not, etc.

The web application typically uses the model explicitly to avoid unnessecary performance hits, but outside applications would simply invoke the RESTful API and never worry about the specifics of data storage.
So, I see you agree with my previous argument
When I referred to the "DB abstraction layer" and "SP" I meant that only SP calls and results returning should be unified.
I think (as in most cases) we are probably very close to sharing the same understanding, only I would prefer to see a RESTful API services layer used by distinct applications, as opposed to each application explicitly connecting to the SQL DB server. Yes performance hit, but that is always the cost for greater flexibility and more abstraction.
I have some more things to say, but I have to go now to be continued ...
Sounds good, I look forward to it :)

Cheers,
Alex

Re: what is a stored procedure?

Posted: Wed Dec 30, 2009 7:52 pm
by Eran
No one solution fits all. I think it's obvious that local applications that share the same physical machine or maybe even the same network should have direct access to services like a database. Definitely there is no need for REST transactions between local processes. When I said an API, I meant that one of the application should consolidate data access and allow others access to the database through it's API. It's my opinion that scripting / programming languages have far better tools to handle abstraction and avoid duplication than MySQL (through SP). Therefor those should offer the API instead of SP being the API (so to speak).

On the other hand a remote service (as in, Internet and not intranet) would not normally be given direct access to the database. Security implications are usually too severe. Most commonly, an API (REST or otherwise) would abstract such access and also limit it's capabilities.

Re: what is a stored procedure?

Posted: Thu Jan 07, 2010 7:48 am
by VladSun
The last argument for using SPs as a "database API" when more than one developer teams are involved and especially when they work on different platforms/technologies is that, in fact, SQL SPL is the only common language that all of the teams understand ;)

It may be an "inter-team communications booster" because neither of teams depends on the other team. It's in case the "power" of changing SP is used carefully :)
Also any of the team can perform a "code-review" on the SPs and fix bugs - one can't say that one of the team will always be the most professional one and thus this team should deliver the API and everyone else should rely on it.