Mailing List Archive


Back to the month index Back to the list index
rasmus@mail1.bellglobal.com
Tue, 7 Jan 1997 10:33:33 -0500 (EST) 

From: rasmus@mail1.bellglobal.com
Date: Tue, 7 Jan 1997 10:33:33 -0500 (EST)
Subject: Re: [mSQL] 2.0 - can it cache results?
Message-Id: <ML-2.3.852651213.5758.rasmus@inet-dev>

> The logical way to do this is to link the tmp table to the session handle
> in msql2d. That way it can be cleaned up at the end of the session and
> all is well. The down side is that it will not help web based app's as
> each page generates a new session :-(

Linking it to the session handle would sort of defeat the purpose. Could you
not create a unique identifier of some sort which map to the new table? You
would need to create this unique identifier anyway because it is very
possible that two client apps try to create the same temp table at the same
time, and one should not fail just because another is doing the same thing.
So, instead of actually naming the temp table by whatever name the client
app has specified in the SQL query, a unique identifier needs to be generated
and made accessible to the client.

eg.
   char temp[64];

   count = msqlQuery(dbsock,"SELECT * from table into temp");
   strcpy(temp,msqlGetTableIdent(dbsock,"table","temp"));

   // still a possibility of a race condition here. Perhaps the engine
   // could hand out unique table id's before a query

   strcpy(temp,msqlGetTableIdent(dbsock,"table",pid);
   sprintf(query,"SELECT * from table into %s",temp);
   count = msqlQuery(dbsock,query);

   // The 3rd argument to msqlGetTableIdent could be some sort of seed
   // to help the engine generate a unique id. The process id of the
   // calling process might be sufficient.
  
At this point, a completely different application can come along and
access the table using the unique identifier as if it were a normal table.

Some issues:

  . The unique identifier should not be a normal valid table name, or it
    should be so long and cryptic that it has an extremely high probability
    of never interfering.

  . When should the temp table be deleted? I would prefer if it were left
    up to the client application to delete it, but it would be neat if
    it could be given a timeout. eg.

         msqlSetTableTimeout(dbsock,temp,3600);

    This would cause the table identified by its unique identifier to time out
    after an hour. This of course assumes that tables have creation timestamps
    associated with them.

  . Permissions of these temporary result tables should be the same as the
    permissions of the parent table without the need for a separate ACL entry.

  . I don't think these tables should show up in an msqldump, nor should they
    survive if msqld is stopped and restarted. They should survive a reload
    though.

-Rasmus
--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com. Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!