Mailing List Archive


Back to the month index Back to the list index
Gary Bickford (garyb@outlawnet.com)
Thu, 9 Jan 1997 01:34:53 -0800 

Message-Id: <v01540b02aefa5ca18239@[204.245.248.249]>
Date: Thu, 9 Jan 1997 01:34:53 -0800
From: garyb@outlawnet.com (Gary Bickford)
Subject: Persistent tables (long) (Was: Re: [mSQL] Re: 2.0 - can it cache results?)

Sorry this is so long folks - I just got carried away. Hopefully it
provides some useful insight.
gb

At 2:36 AM 1/9/97, Mark H. James wrote:
>If we are talking about persistent temp tables (sounds like a pretty
>good oxymoron to me<g>), is that the same thing as a view under SQL?

Gee, that sounds like a good theory to me - I know zip about SQL.

>I guess the results of a view are not necessarily cached, however,
>just the query path -- but that probably would speed complex queries.
>
>It seems like having to build your own intermediate layer to cache
>results could end up recreating mSQL all over again -- it seems like
>the answer may be to create new tables of the results from common
>queries, and somehow keep track of their age and success in answering
>new queries.

Well, if I understand mSQL rightly, the results of a query are now stored
in tables that are not directly accessible by the application (all those
.dat files in /var/tmp or wherever) - then the data is passed to the
application over the socket. So if a simple, clean way of passing the
appropriate tables to a "persistent table manager" could be made, then the
problem is solved. Not having read line one of the internals of mSQL, I
think this could be done as simply as the following, given the idea of a
separate process that does the bookkeeping. Note that this could be ANY
process - mSQL needn't care, as long as somebody makes sure the disk
doesn't fill up.

The following sequence actually doesn't require the "INTO" clause suggested
by Rasmus, but could use something like SELECT PERSISTENT ... or SELECT
PERSISTENT 3400 ... for a time-limited table. The application wouldn't
actually name the table.

0. The bookkeeping process opens up a database of temporary tables,
which starts out empty. It makes a connection with mSQL, which it
maintains indefinitely, and uses for adding and deleting bookkeeping data.
Its entire purpose in life is to do the bookkeeping and delete files as
appropriate.
...time passes...
1. A client process requests a persistent result from mSQL.
2. mSQL produces the result table, puts it into the bookkeeper's
database (a la CREATE TABLE) and adds the relevant information as a row in
the bookkeeper's bookkeeping table.
3. mSQL also passes back to the application, the same row, which
identifies the table - NOT the actual data. This is the simplest, from
mSQL's point of view. At that point from mSQL's point of view the table is
handled like any other permanent table.
4. The application uses the results of its PERSISTENT query to know
the persistent table's location, and proceeds with its actual query, which
may be the same as the prior one except for the db and table information,
or may be different according to need.
5. At some point the application can issue a "drop table" command to
delete the table, or the bookkeeping process will age it out based on
predefined parameters, PERSISTENT time statement, or space limitations.

The bookkeeping process could be implemented in perl or something else very
simply - all it has to do is wake itself up every few seconds or minutes,
connect to the bookkeeping database, look at every record in the
bookkeeping table, issue "delete from" queries for the bookkeeping record
for tables that are too old followed by a "drop table" query to actually
drop the table. Then it would also look at the disk space and do the same
for the oldest/ largest tables until it is within the desired parameters.
It might be handy to include a table of relevant parameters in the
bookkeeper's database, to allow some dynamic tuning.

NOTES

An application library a la msqlperl or PHP's SQL functions could hide the
two step query from the programmer, but I don't think that's necessary or a
good idea. there's also the issue of handling a query on a table that's
expired - if it fails, it'll have to be reissued on the original data.

Then also, I think we've all assumed that such a table is read only, but
I'm not sure if it matters.

One issue I'm not sure of, as I haven't had to deal with it is how much
hassle it is for the application to work with two separate databases - in
this case the original and the persistent. I can't remember if one has to
use connect() in this case, or can just (using PHP as an example), do the
following:
$result=msql("db1","select...");
$otherresult=msql("db2","select...");

Rasmus' idea of SELECT ... INTO actually provides a very clean alternative,
if this essentially causes mSQL to create a new table (or even write over
an existing one?) in the SAME database as the query, and use the table name
provided by the INTO. This requires some more work on mSQL side, maybe,
and leaves open the problem of unique table names when more than one
instance of a program is doing this. It also provides no way for aging and
automatically deleting the persistent tables. But it would certainly work
- it's not completely out of line to require the application to come up
with a unique table name, and to DROP the table when it's done.

Blah, blah - sorry this is so long.

end
=======
Gary Bickford, FXT Corporation http://www.fxt.com
System integration, active web site design, intranets.
garyb@fxt.com 541-923-3060

--------------------------------------------------------------------------
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!