>>Following the theme proposed by Mr. Bill Karwin...
>
>A problem with the proposal is security -- anybody connected
>to the database can get any messages. Does anybody have
>any idea of how this might be extended for SQL-like
>security?

I'll build up to my proposed solution...

What I think would be great is to have a special table type that is used for
messaging. This would allow the developer to setup customized messages just
like we have customized record types in tables.

Then, instead of having a new post_message command you could simply perform
inserts into the message table as you would insert into any other table.

A SELECT statement could be prepared to determine the client's interest in
events. A WHERE clause could be used as a filter.

When first prepared if executed it will appear to have zero records. The
preparing could also be the initialization necessary to hook in the AST for
receiving fetches and determining it's starting point. (This could also be
an opportune time to wire in the AST for handling the cancel request
callback on a server process that is currently running and blocking the main
thread.)

Then, once prepared it will receive notification that a record is there to
be fetched if one matching its WHERE clause criteria is made available.
Then, the client would execute the statement, open a cursor and fetch the
messages just like it is fetching records from a table.

So, following this model, I see the security stuff applying in parallel just
the way that it applies to the other tables. Use the SELECT and INSERT
privilages to govern who can do what with messages.

This will of course add an additional case everywhere that tables, etc. are
dealt with so the changes would be quite pervasive throughout the engine but
it would have the benefit of meshing in with all of the existing code,
logic, etc. to be immediately put to practise. I think this would require
the least amount of learning for the user-developers as well.

The API would be impacted somewhat as there would most likely need to be a
new API method to check for the existence of waiting messages and/or the
ability to plug in an AST that would wake up once notification was received.

Another significant impact of this is the "garbage collection" of message
records that are no longer significant to any attached clients. I'm not sure
what algorithms would be used to determine when the message records would be
plucked out of the message table. (This is one of the challenges I have had
with using message tables already.)

Oh ya, another essential item I really, really need along with this is the
ability to capture a unique ID of some sort signifying the connection
number. It would be like the 'USER' system variable except it would allow me
to distinguish in SP/TR code one client connection from all others. This
would also need to be accessible via the isc_database_info() call or some
other way (like a SELECT CLIENTID FROM RDB$DATABASE would do it eh?). This
way in the message table I could include a column that signifies the unique
client id and then each client that didn't want to receive its own messages
could register an event something like this:

SELECT *
FROM MYMESSAGETABLE
WHERE MSGCLIENTID <> CLIENTID

Then, that particular connection would not receive any messages that it is
soley responsible for generating. Just everyone else who is connected
regardless of what username they are connected with.

Hopefully this is presented clearly enough... Don't hesitate to ask for more
details.

In short, this is the ACTIVE table concept...

Regards,
Jason Wharton
InterBase Developer Initiative
jwharton@...

InterBase will be the database of the new millennium.