Re: theory and practice: ying and yang

mountain man wrote:

>> Even if the DBMS package is bundled with a graphical frontend (e.g.
>>  Enterprise Manager for SQL Server), it's logically separate from
>> the DBMS itself.

OK, so are you saying that all database usage could be done through a generic client, such as Enterprise Manager for SQL Server? What about database that power websites? What about operating systems that don't have that client available to them? I guess your argument would be that porting a single generic client to multiple platforms would be simpler than maintaining millions of different clients?

Also, the way a database is presented to an end-user for data input or output is often very different to a standard grid format. You may want dropdowns or pick lists, or to bring together data from several different tables (but not in a tabular form). You may want a first drop-down to narrow down the range of a second dropdown or something. Maybe include graphics or boilerplate text. All this would have to be done on the client.

Maybe you'd need clients that have no user interface; that run as services in the background.

> Both sprocs and views are essentially select statement. Perhaps our
> preferences are simply based on past use?

Stored procedures can be just select statements, but they can also be a series of SQL statements, SELECT, UPDATE, INSERT, DELETE, etc. to be executed in a specific order. And maybe wrapped in transactions that can be rolled back or committed.

>>OK, that sounds reasonable. But don't you still have the problem that if
>>the table designs change, you have to coordinate the corresponding
>>changes in all the stored procedures that access those tables? Granted,
>>they're now both in the same language, SQL, but it's still an issue.

OK, but I'm thinking if you do something more drastic like split a table into parts (maybe to allow for multiple phone numbers or something), or change a column's data type. You could use views to get round some of these issues (especially on the reporting side) but because your underlying predicates have changed, your stored procedures will need rewriting. And in this scenario would it be that much more work to make the similar changes to a more remote client?

>>How can SQL talk to a remote machine to draw a window on the screen and
>>populate it with controls? Surely it requires drivers to enable clients
>>to perform these functions?

So how does this differ from a chopped-down version of a standard database management tool like Enterprise Manager for SQL Server?

> The user on the remote machine talks to the tool (by a
> simple line reference of an array, the clicking of a mouse
> on the summary report line that says "Widgets").
>
> The tool sends this info to the RDBMS along with the
> fact that a second level report has been invoked.
>
> The RDBMS executes the second level stored procedure
> *using* the info supplied (in this case "Widgets").
>
> The RDBMS sends back to the tool (GUI) the result
> set, and the tool displays this to the user.
>
> The user is able to (multiple) sort, or filter upon, this
> result set, and/or print preview it, and/or export it to
> various industry standard formats.

OK, I see. This seems a bit low-level for a lot of users though; they don't want their clients to look like database tables - they want something less technical-looking.

>>I don't quite understand what you mean by "drill-down" and I can't find
>>references to this on your site.

That looks quite nice. How does the database designer define what is the behaviour when a user double-clicks on a given row? What if you had more than one possible drilldown for a row?

>>I think views are nicer from the end-user point of view, but I suspect
>>they are a lot more difficult to implement from the point of view of the
>>DBMS designer, being declarative rather than procedural.

In this case, they're both essentially declarative. But in general a stored procedure will have more than one SQL statement in it, using whatever procedural extension to SQL is used by that DBMS. (e.g. T-SQL for SQL Server). And this is giving the DBMS a sequence of steps to follow, and maybe some loops as well, so is procedural.

With views, you can have views upon views upon views, and I would guess that very deeply nested views are quite difficult to optimize, especially when things like aggregates enter the frame.

Paul. Received on Tue May 31 2005 - 16:19:56 CEST