ftp://bond.edu.au/pub/Minerva/msql/faq.txt
or an HTML copy can be obtained via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/faq.html
This FAQ is maintained by
Peter Samuel
<[email protected]> and is produced independently of
Hughes Technologies (the developers of mSQL). While every attempt is made to ensure that the information contained in this FAQ is accurate, no guarantees of accuracy can or will be made.
This FAQ attempts to deal with mSQL. Because of time and space constraints, other applications, including value added applications provided with the mSQL distribution such as W3-mSQL and Lite, will not be covered in any great detail.
Third party applications mentioned in this FAQ may not be compatible with the current release of mSQL - by necessity their development will lag that of mSQL. If you have any questions concerning their status please contact the mSQL mailing list or the author of the application in question.
New questions in the FAQ are marked with (=). Questions that have been modified since the last release of the FAQ are marked with (-).
Note: BEFORE POSTING A QUESTION TO THE mSQL MAILING LIST, PLEASE READ THE SECTION "How do I post a question to the mSQL mailing list".
The reasons for this limited scope are simple - time and space. I maintain the FAQ out of the goodness/madness of my heart. I simply don't have the time to cover everything - if I did cover everything I'd never get any real work done and I'd starve :(
The FAQ is also quite large - 297k as of release 1.32 and growing. If I could include minute details about all the value added mSQL products you'd be looking at a very large document indeed (or several large documents). This is the sort of problem I don't have the time to deal with :)
There is a bright side however. I'm perfectly willing to accept contributions to the FAQ on ANY mSQL related issue - in fact I highly encourage it. If you have details concerning an area of mSQL that you'd like included in the FAQ, write it up and send it to me <[email protected]>.
Some of the value added products have mailing lists and home pages. See the "Contributed Code and Third Party Applications" section for details.
SQL is an acronym that stands for Structured Query Language. It is often pronounced "sequel". It was developed in the mid 1970s by IBM.
The American National Standards Institute (ANSI) and the International Standards Organisation (ISO) have adopted SQL as the standard language for relational database management systems.
SQL provides commands for a variety of tasks including:
While most relational database management systems - including mSQL - provide support for SQL, each vendor usually has their own unique extensions to the language that may hinder the portability of SQL procedures from one database platform to another.
JCC Consulting http://www.jcc.com maintains a central source of information about the SQL standards process and its current state. For more details visit:
http://www.jcc.com/sql_stnd.html
C. J. Date
"An Introduction to Database Systems"
Vol I, Sixth Edition, 1995
Addison Wesley
ISBN 0-201-54329-X
(http://heg-school.aw.com/cseng/authors/date/intro/intro.html)
C. J. Date and Hugh Darwen
"A Guide to Sql Standard"
Fourth Edition, 1997
Addison Wesley
ISBN 0-201-96426-0
(http://heg-school.aw.com/cseng/authors/date/sql/sql.html)
Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky
"The Practical SQL Handbook: Using Structured Query Language"
Third Edition
Addison Wesley
ISBN 0-201-44787-8
(http://www.awl.com/devpress/titles/44787.html)
Terry Halpin
"Conceptual Schema and Relational Database Design"
1995
Prentice Hall
ISBN 0-13-355702-2
(http://www.prenhall.com/allbooks/ptr_0133557022.html)
Joe Celko
"Instant SQL Programming"
1995
Wrox Press
ISBN 1-874416-508
(http://www.wrox.com/scripts/bookdetail.idc?Code=508)
Martin Gruber
"Understanding SQL"
1990
Sybex Publishing
ISBN 0-89588-644-8
(http://www.sybex.com/cgi-bin/bookpg.pl?644back.html)
Jeff Rowe
"Building Internet Database Servers with CGI"
1996
New Riders Publishing
ISBN 1-56205-573-9
(http://merchant.superlibrary.com:8000/catalog/hg/PRODUCT/PAGE/15620/bud/1562055739.html)
Perkins & Morgan
"Teach yourself SQL in 14 days"
1995
SAMS Publishing
ISBN 0-67230-855-X
(http://www.connection.se/cgi-bin/shop/method=showitem/sid=bm/uid=78F52F3/item=067230855X
or
http://merchant.superlibrary.com:8000/catalog/hg/PRODUCT/PAGE/06723/bud/067230855X.html)
Connolly, Begg & Strachan
"Database Systems: A Practical Approach to Design,
Implementation and Management"
1996
Addison Wesley
ISBN 0-201-42277-8
(http://heg-school.aw.com/cseng/authors/connolly/databsys/databsys.html)
Melton & Simon
"Understanding the New SQL: A Complete Guide"
1993
Morgan Kaufmann
ISBN 1-55860-245-3
Mike Morgan & Jeff Wandling
"Webmaster Expert Solutions"
1996
QUE books
ISBN 0-7897-0801-9
(http://merchant.superlibrary.com:8000/catalog/hg/PRODUCT/PAGE/07897/bud/0789708019.html)
(see http://www.speakeasy.org/~jdw for errata)
Ramez Elmasri and Shamkant B. Navathe
"Fundamentals of Database Systems"
Second Edition
Addison Wesley
ISBN 0-8053-1748-1
(http://heg-school.aw.com/cseng/authors/elmasri/Dbase2e/Dbase2e.html)
"mSQL has been released in the past under terms known as
'conscience-ware', the basic concept of which was that companies
that used the software could contribute a small amount to the
continued development of the software without any strict rules
being placed upon such 'donations'. Although the concept sounds
fair, it failed badly with only 3 contributions being made from
over 3,600 copies of mSQL-1.0.5 that were ftp'ed from my machine
alone. Over 1,000 of those copies went to commercial organisations
and I receive many questions a day from companies using mSQL behind
their WWW servers etc who are looking for free support.
In an attempt to balance this out and allow me to devote some time
to mSQL (rather than other pursuits that I do to generate an
income), mSQL is now shareware. I still believe in free software
over the Internet and cooperation in research so the new license is
designed not to hurt Universities, research groups and other people
that _should_ have free access to software. Commercial
organisations that are using this so that they don't have to buy an
Oracle or an Ingres will now have to buy mSQL (at a minute fraction
of the cost of one of the commercial offerings).
Please read the doc/License file to see if you are required to
register your copy. An invoice is included in both Postscript and
ASCII format to ease the generation of payments."
You may freely use mSQL if and only if you fall into the categories
outlined in the mSQL License file:
You can use this software free of charge if you are an educational
institution (excluding commercial training organisations),
non-commercial research organisation, registered charity,
registered not-for-profit organisation, or full-time student.
If you do not fall into any of these categories, you will have to pay
a license fee to use mSQL. As of release 1.0.16, the cost of mSQL is:
Commercial Installation - AUD $225
Private Installation - AUD $65
Exchange rates may vary wildly, but at the time of preparing this FAQ,
the Australian dollar was trading at about 0.75 US dollars. This
information is provided as an indication only. You MUST check your
local exchange rates before preparing to purchase mSQL. An online currency conversion system developed by Olsen & Associates is available at http://www.olsen.ch/cgi-bin/exmenu.
Another online currency conversion system is available from http://www.DynaMind-LLC.com/services/utilities/currency.cgi.
ftp://bond.edu.au/pub/Minerva/msql/
The latest beta version of 2.x can be found at:
http://hughes.com.au/software/msql2/current.htm
The following sites mirror the mSQL FAQ:
Thanks to Tobias Haecker <[email protected]>.
Thanks to David Perry <[email protected]>.
Thanks to Sjoerd de Heer <[email protected]>.
Thanks to Davorin Bengez <[email protected]>.
Thanks to Jeanne Gold <[email protected]>.
Thanks to Laurent Zanoni <[email protected]>.
Thanks to Ricardo Saito <[email protected]>.
Thanks to Fernando Lozano <[email protected]>.
Thanks to Yiorgos Adamopoulos <[email protected]>.
Thanks to Jesper Hagen <[email protected]>
Thanks to David Perry <[email protected]>.
Thanks to Davorin Bengez <[email protected]>.
Thanks to Malcolm Herbert <[email protected]>.
The following sites mirror the mSQL 2.x software distribution:
Thanks to WorldOne http://www.worldone.com.
Thanks to David Perry <[email protected]>.
Thanks to Sjoerd de Heer <[email protected]>.
Thanks to Davorin Bengez <[email protected]>.
Thanks to David Perry <[email protected]>.
It is available via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/msql-1.0.16.tar.gz
(195705 bytes)
mSQL development is NOT David's primary role. He does this work in whatever spare time he has available so PLEASE don't pester him with requests about when we can expect future releases. Take the zen approach and just let it happen :)
A beta snapshot of mSQL version 2.0.x was released on May 8 1997. It is available from:
http://hughes.com.au/software/msql2/msql-2.0-B7.1.tar.gz
(373261 bytes)
Note: Do NOT download the mSQL-2.0B7.0 distribution. If you do
so you will not be able to compile the Lite modules. Use the
mSQL-2.0B7.1 distribution instead. It is also mirrored at a number of other sites. See the section above titled "Are there any mirror sites for mSQL?" for details on these mirror sites.
More details on mSQL 2.0.x can be found by visiting the Hughes Technologies web pages at http://hughes.com.au.
This means, however, that this Beta software MAY NOT WORK CORRECTLY for you. If you cannot tolerate hangs, crashes, or loss of data, you should use the latest released version and any recommended patches (currently mSQL version 1.0.16 with the "lost table patch"). However, if you can use the new version, please do so and report bugs to the mailing list and to the bugs list so they can be fixed in the next release.
(See http://www.cre.canon.co.uk/~neilb/weblint.html for more details on weblint).
If you want something added to the FAQ, it would be a great help if you could write a section and forward it to me - then I can simply insert it in the appropriate location.
Remember, I don't own the FAQ - I'm just its caretaker on your behalf.
The above not withstanding there is one unofficial patch that should be applied to mSQL version 1.0.16.
This patch comes from David Hughes <[email protected]> and fixes the very old and very obscure problem of the occasionally disappearing table.
David writes:
After sitting here banging my head against the old "missing table"
bug with my partner in crime Jason <[email protected]> , we've fixed
it !!!!! This is yet another very obscure bug. So, can it be
reproduced? Yup, once you know the problem.
o Fill the table cache
o Cause a reference to a table that doesn't exist
o The oldest cache entry will have the table definition list
set to NULL but with the name, DB, and age fields still set
We all owe a debt of thanks to Jason as it was his inability to type
table names correctly that found the problem :)
*** ./src/msql/msqldb.c.orig Sun Jul 7 16:47:09 1996
--- ./src/msql/msqldb.c Tue Jul 30 17:07:42 1996
***************
*** 1398,1403 ****
--- 1398,1406 ----
safeFree(entry->rowBuf);
safeFree(entry->keyBuf);
entry->def = NULL;
+ *(entry->DB) = 0;
+ *(entry->table) = 0;
+ entry->age = 0;
}
/*
To avoid conflicts with mismatched whitespace,
apply this patch using the
"-l" option of patch.
(That's an "el", NOT a "one" or an
"eye"). David assures us that this patch will be rolled into version 1.0.17.
The definition of NAME_LEN changed from version 1.0.10 to 1.0.12. It now seems that 18 characters is the maximum value for a table or field name. This problem exists in mSQL versions 1.0.12 and above.
David Hughes writes:
The reason it changed in 1.0.12 is because I had a couple of
field_name buffers that didn't have room for a trailing NULL. Now,
trying to fix that without altering the size of the struct implies
that you have to shorten the name of the field (to leave the
required room). I didn't want to force everyone out there to drop
and reload every database they have just because of a 1 byte buffer
over-run.
If this is a major problem for you then ....
o dump all your databases
o find the definition in question
o set it to a value you like
o rebuild everything
o reload everything
If you can work out what to change to get the extra field name
length then you know enough about what you are doing to do the rest
of what's required. I haven't provided a step-by-step because if
people can't find the value they have to change I'm sure they'd
still end up asking the list about this stuff after breaking
things.
At present there is NO patch to fix this problem. Possible
workarounds are David's method outlined above or to downgrade to
version 1.0.10.
The cleanup also changes the way in which other special characters can be searched. The tables in the section "What other characters need special treatment?" outline the differences for versions up to and including version 1.0.13 and 1.0.14 and above.
http://www.bitwizard.nl/sig11
Marty Picco writes:
I have a 200K+ record database running on a p90 with 96MB memory.
I have noticed that the first SELECT I do on this database after
msqld is started causes msqld to grab as much memory as it can...in
this case about 68MB. It appears that the memory is never released
until the daemon is restarted. Indeed, the SELECT does have an
ORDER clause.
David has said that he'll investigate this problem.
./targets/your-architecture/site.mm
and ensuring the mmap() directive reads:
MMAP=
and then compile and re-install the mSQL package.
Note: Some parts of this section have been compiled by Lloyd Parkes <[email protected]>.
There are a few known problems with the current release of mSQL 2.0Bx. This list is undoubtedly incomplete and will probably remain so because of the "moving target" nature of new software.
msqldump -h dbhost -c dbname > db.dump
msql -h dbhost dbname < db.dump
#! /usr/local/bin/perl
use strict;
# read a msql 1.x dump and convert PRIMARY KEY entries to UNIQUE INDEX
# called pkey for that table.
#
# also converts fields named time->timestamp, date->datestamp, count->counter
# V. Khera ([email protected]) 08-JAN-1996
# Usage: msqldumpconvert orig.dumpfile > new.dumpfile
# or use it in a pipeline between msqldump from 1.x and msql from 2.x
my($field, $table);
while (<>) {
if (m/^\# mSQL Dump/) {
print "# mSQL Dump converted to mSQL 2.0 format\n";
} elsif (m/^\s+ (\w+).*PRIMARY KEY,/) {
$field = $1;
s/ PRIMARY KEY//;
print;
} elsif (m/CREATE TABLE (\w+)/) {
$table = $1;
$field = ""; # no field means no primary key
print;
} elsif (m/^\) \\g/ and $field) {
print;
print "\nCREATE UNIQUE INDEX pkey ON $table ($field)\\g\n\n";
} elsif (m/^ count /) {
s/^ count / counter /;
print;
print STDERR "Converted field 'count' to 'counter' in table $table\n";
} elsif (m/^ time /) {
s/^ time / timestamp /;
print;
print STDERR "Converted field 'time' to 'timestamp' in table $table\n";
} elsif (m/^ date /) {
s/^ date / datestamp /;
print;
print STDERR "Converted field 'date' to 'datestamp' in table $table\n";
} else {
print;
}
}
Note: Simply relinking will NOT make the new functionality available - the third party application must take advantage of the new functions for you to make use of them. Relinking will only enable you to use your existing third party applications with the mSQL 2.x database server.
David Hughes writes:
TEXT fields will always be slower than CHAR fields because the data
is split over multiple buffers. As for the searching you can do
anything other than *LIKE testing at the moment. I'll try to work
out a way to do LIKE and CLIKE on TEXT fields. RLIKE is a
non-option.
msqldump -h dbhost -c dbname > db.dump
msqladmin drop dbname
msqladmin create dbname
msql -h dbhost dbname < db.dump
A patch is available from http://www.vuw.ac.nz/~lloyd/msql.
*** src/msql/msqldump.c.orig Tue Mar 25 09:49:57 1997
--- src/msql/msqldump.c Tue Mar 25 09:50:57 1997
***************
*** 346,352 ****
if (row[i])
{
if (field->type == CHAR_TYPE ||
! field->type == TEXT_TYPE)
{
tmp = escapeText(row[i]);
printf("\'%s\'", tmp);
--- 346,353 ----
if (row[i])
{
if (field->type == CHAR_TYPE ||
! field->type == TEXT_TYPE ||
! field->type == DATE_TYPE)
{
tmp = escapeText(row[i]);
printf("\'%s\'", tmp);
To avoid conflicts with mismatched whitespace,
apply this patch using the
"-l" option of patch.
(That's an "el", NOT a "one" or an
"eye").
More details are available from http://www.vuw.ac.nz/~lloyd/msql.
Brendan writes:
If you have a field that is full and you do a like/clike search
where the last character of the like string matches the last
character of the field, mSQL-2.0 b3 and 4 (at least) incorrectly
match.
Example
-------
mSQL > create table blah ( Organisation CHAR(50) ) \g
Query OK. 1 row(s) modified or retrieved.
mSQL > insert into blah Values ('1995 Hopman Cup Tennis Tournament, Perth
Western A') \g
Query OK. 1 row(s) modified or retrieved.
mSQL > select * from blah where Organisation clike '%abababa%' \g
Query OK. 1 row(s) modified or retrieved.
+----------------------------------------------------+
| Organisation |
+----------------------------------------------------+
| 1995 Hopman Cup Tennis Tournament, Perth Western A |
+----------------------------------------------------+
which of course is wrong.
Brendan also supplies a patch for this problem:
*** src/msql/types.c.orig Fri Apr 11 08:58:30 1997
--- src/msql/types.c Tue Apr 15 16:53:00 1997
***************
*** 138,144 ****
{
if (*cp1 == *cp2)
{
! if(likeTest(cp1,cp2,length-count)==1)
{
return(1);
}
--- 138,144 ----
{
if (*cp1 == *cp2)
{
! if(likeTest(cp1,cp2,length-count+1)==1)
{
return(1);
}
***************
*** 224,230 ****
{
if (toupper(*cp1) == toupper(*cp2))
{
! if(cLikeTest(cp1,cp2,length-count)==1)
{
return(1);
}
--- 224,230 ----
{
if (toupper(*cp1) == toupper(*cp2))
{
! if(cLikeTest(cp1,cp2,length-count+1)==1)
{
return(1);
}
To avoid conflicts with mismatched whitespace,
apply this patch using the
"-l" option of patch.
(That's an "el", NOT a "one" or an
"eye").
ftp://ce-toolkit.crd.ge.com/pub/sumrn/msql-2.0-B7/src/msql
*** src/msql/index.c.orig Tue Jun 24 10:26:54 1997
--- src/msql/index.c Tue Jun 24 10:27:29 1997
***************
*** 353,359 ****
if (node)
{
curRow = (u_int)node->data;
! if (curRow != rowNum && rowNum != NO_POS)
return(0);
}
return(1);
--- 353,359 ----
if (node)
{
curRow = (u_int)node->data;
! if (curRow != rowNum || rowNum == NO_POS)
return(0);
}
return(1);
To avoid conflicts with mismatched whitespace,
apply this patch using the
"-l" option of patch.
(That's an "el", NOT a "one" or an
"eye").
Some of these patches are for older releases of mSQL. They may have been rolled into subsequent releases of mSQL or they may not work with later releases of mSQL or they may break the successful operation of later releases of mSQL. Use them at your own risk.
A brief and no doubt incomplete list of these patches follows:
Available from:
ftp://ftp.uni-bremen.de/pub/unix/database/Minerva/patches/insert.msql-1.0.9
(593 bytes)
update account set balance = balance + 100 where nr = 12345;
Contributed by Michael Koehne
<[email protected]>. Available from:
ftp://ftp.uni-bremen.de/pub/unix/database/Minerva/patches/update.msql-1.0.9
(12677 bytes)
Available from:
ftp://ftp.uni-bremen.de/pub/unix/database/Minerva/patches/quote.msql-1.0.9
(1225 bytes)
Michael writes:
This patch includes the following features :
- Usage of double quotes for strings. I know this is not ISO
but a lot of other databases also allow double quotes and
some of my programs use double quotes.
- Speedup of regular expression.
- Simple expressions in update.
- auto primary key insert.
- Some bug fixes.
They are available via anonymous ftp from:
ftp://ftp.uni-bremen.de/pub/unix/database/Minerva/features-msql-1.0.16
(19297 bytes)
or
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLpatches/features.msql-1.0.16
(19297 bytes)
select name from table where name ilike 'Manu%'
Contributed by Benjamin Jacquard
<[email protected]>. The patch is available via anonymous ftp from:
ftp://ftp.geo.tu-freiberg.de/pub/unix/msql/case-insensitive.patch
(8265 bytes)
The patch is available via anonymous ftp from:
ftp://ftp.infodrom.north.de/pub/mirror/msql/Patches/msqldump-1.0.16
(5275 bytes)
Robert writes:
1. Adjusted the deletion of backslashes during the mSQL to regexp
translator code to make treatment of special characters more
uniform. In other words, all regexp special characters except
backslash require only two backslashes in front of them to be
treated normally. (One is stripped by msql or the translator;
the second by the regexp package.) The backslash character
requires four backslashes because it is the escape character
for both the msql monitor and the regexp package. The single
quote, of course, remains the same needing only one backslash
because it is special only to mSQL.
So, you can all now search for \ and ^ and everything else. Or
so my ever expanding test set tells me.
2. The execution of some of the non-LIKE comparison code by the
NOT LIKE operator is fixed. (This should probably be a
miniscule performance increase--except that it might be eaten
by 1.)
*** src/msql/msqldb.c 1996/11/04 23:13:54 1.1
--- src/msql/msqldb.c 1996/11/05 01:02:01 1.2
***************
*** 3164,3176 ****
switch(*cp1)
{
case '\\':
! if (*(cp1+1) == '%' || *(cp1+1) == '_')
! {
! cp1++;
! *cp2 = *cp1;
}
- cp1++;
- cp2++;
break;
case '_':
--- 3164,3209 ----
switch(*cp1)
{
case '\\':
! /* RNS: The only time that we really want to
! drop backslashes is when they are escaping
! either % or _.
! */
! if (!*(cp1+1)) {
!
! /* keep backslash at the end of an expr. */
! *cp2 = *cp1;
! *cp1++; *cp2++;
!
! } else {
!
! if ((*(cp1+1) == '%') || (*(cp1+1) == '_')) {
!
! /* drop backslash when followed by
! % or _.
! */
! cp1++;
! *cp2 = *cp1;
! cp1++; cp2++;
!
! } else if (*(cp1+1) == '\\') {
!
! /* keep both backslashes when they escape
! themselves
! */
! *cp2 = *cp1;
! *cp1++; *cp2++;
! *cp2 = *cp1;
! *cp1++; *cp2++;
!
! } else {
!
! /* keep the backslash so it can escape
! some other regexp character.
! */
! *cp2 = *cp1;
! *cp1++; *cp2++;
! }
}
break;
case '_':
***************
*** 3351,3357 ****
REG char *c1,*c2;
REG int offset;
! if (op != LIKE_OP)
{
c1 = v1;
c2 = v2;
--- 3384,3394 ----
REG char *c1,*c2;
REG int offset;
! if ((op == LIKE_OP) || (op == NOT_LIKE_OP))
! {
! cmp = regexpTest( v1, v2, maxLen );
! }
! else
{
c1 = v1;
c2 = v2;
***************
*** 3395,3405 ****
break;
case LIKE_OP:
! result = regexpTest(v1,v2,maxLen);
break;
case NOT_LIKE_OP:
! result = !(regexpTest(v1,v2,maxLen));
break;
}
return(result);
--- 3432,3442 ----
break;
case LIKE_OP:
! result = cmp;
break;
case NOT_LIKE_OP:
! result = !cmp;
break;
}
return(result);
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLSSL/msql-1.0.16-patch.tar.gz
(7183 bytes)
Etienne writes:
I just want to share this little patch which solve the mmap problem
for NeXTSTEP. It apparently works (meaning all msql tests are OK)
for:
NSFIP 3.3 p1
NS Motorola 3.3 p1.
It does NOT work for HP 3.2
All other configurations are untested !
Thanks to the authors Fabien Roy and Robert Ehrlich for this patch !
Etienne Klein
Laboratoire de Chimie Analytique
Faculte de Pharmacie de Nancy
France
/* * @(#)map.c 1.0 of 20 December 1996 * * Copyright (c) 1996 by Fabien Roy. * Written by Fabien Roy and Robert Ehrlich. * [email protected] [email protected] * Not derived from licensed software. * * Permission is granted to anyone to use this software for any * purpose on any computer system, and to redistribute it freely, * subject to the following restrictions: * * 1. The author is not responsible for the consequences of use of * this software, no matter how awful, even if they arise * from defects in it. * * 2. The origin of this software must not be misrepresented, either * by explicit claim or by omission. * * 3. Altered versions must be plainly marked as such, and must not * be misrepresented as being the original software. * */ #include <sys/types.h> #include <sys/mman.h> #include <stdlib.h> #include <syscall.h> caddr_t mmap(caddr_t addr, size_t len, int prot, int flags, int fd, off_t off) { int pagelessone = getpagesize() -1; int size; caddr_t pageaddress; /* round to next page size */ size = (len + pagelessone) & ~pagelessone; /* allocate aligned pages */ if (!(pageaddress = (caddr_t) valloc(size))) return (caddr_t) -1; /* map it */ if (syscall(SYS_mmap, pageaddress, size, prot, flags, fd, off)){ free(pageaddress); return (caddr_t) -1; } return pageaddress; } void munmap(caddr_t addr, size_t len) { syscall(SYS_munmap,addr,len); free(addr); }
The easiest way to apply these patches is to use Larry Wall's patch program:
Patch will take a patch file containing any of the four forms of
difference listing produced by the diff program and apply those
differences to an original file, producing a patched version. By
default, the patched version is put in place of the original, with
the original file backed up to the same name with the extension
".orig".
Patch is available from a number of anonymous ftp sites
worldwide. Latest versions of patch are being distributed by
the Free Software Foundation as part of the GNU suite of products. If you're having difficulty finding the latest version of patch, you can download version 2.1 via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/Misc/patch-2.1.tar.gz
(74856 bytes)
While some patches are fairly simple - often involving minor changes
to a single line of code - others are quite complex and attempting to
apply these patches by hand is definitely NOT recommended. Use the
patch program whenever you need to apply a patch. To apply the patches listed in this FAQ, use this procedure:
You should be left with a file containing a number of sections similar to:
*** ./src/msql/net.c.orig Tue Oct 17 11:24:03 1995
--- ./src/msql/net.c Tue Dec 12 15:24:11 1995
***************
*** 66,72 ****
! void writePkt(fd)
int fd;
{
u_char *cp;
--- 66,72 ----
! int writePkt(fd)
int fd;
{
u_char *cp;
cd /usr/local/src/db/Minerva/msql/msql-1.0.16
patch -l < /tmp/msql-patch1
The "-l" option is used to tell patch
to ignore any whitespace mismatches between lines in the patch file
and lines in the mSQL source file. (That's an "el",
NOT a "one" or an "eye"). Patch will respond with output similar to:
Hmm... Looks like a new-style context diff to me...
The text leading up to this was:
--------------------------
|*** ./src/msql/net.c.orig Tue Oct 17 11:24:03 1995
|--- ./src/msql/net.c Tue Dec 12 15:24:11 1995
--------------------------
Patching file ./src/msql/net.c using Plan A...
Hunk #1 succeeded at 66.
Hunk #2 succeeded at 84.
done
If you have problems applying the patch, you may wish to consider
using some or all of the following arguments to patch:
forces patch to create a backup of the original file. By default it adds a .orig suffix to the original filename.
forces patch to ignore patches that it thinks are reversed or already applied.
forces patch to honour the full pathname of the files listed in the patch file.
http://Hughes.com.au
Jeff Rowe
<[email protected]> has converted the mSQL
documentation to HTML format. It is available from:
http://cscsun1.larc.nasa.gov/~beowulf/msql/msql.html
Tim Finin <[email protected]>
has created an online mSQL tutorial. It is available from:
http://www.cs.umbc.edu/~finin/461/msql
Mike Carpenter
<[email protected]> has begun to collate the available mSQL
documentation into a single site. He hopes to have a wide range of
resources available for value added mSQL products such as the Perl and
Java interfaces. His work can be found at:
http://www.emi.net/~mikec/tech/msql.html
To subscribe to the list, send the word "subscribe" in the body of your message to [email protected].
To unsubscribe from the list send the word "unsubscribe" in the body of your message to [email protected].
Postings should be addressed to [email protected].
Archives of the mailing list are available via anonymous ftp from:
ftp://ftp.bunyip.com/pub/mailing-lists/msql-list.archive/
Each month's archive is stored in a file:
msql-list.archive.YYMM
where YYMM represents the year and month. So the archive for
October 1995 would be in the file:
msql-list.archive.9510
These files are also available from the
majordomo
mailing list server at bunyip.com.
To receive a list of the archive files available as well as the
majordomo help file send a message to
[email protected] with the
text:
index msql-list
help
END
in the body of the message. To reach a human for help regarding the mailing list send a note to:
[email protected]
or
[email protected]
The mailing list discusses issues that arise from the use of mSQL and
W3-mSQL (both products developed by David Hughes). Often discussions on
contributed software arise but it is probably best to take these
discussions off line and summarise solutions back to the list. Traffic on the list is fairly high. There were approximately 1100 postings between April 1 1996 and May 31 1996 which gives an average of around 550 messages per month. (If you think this is high, try subscribing to the firewalls list - this has an average of around 1000 postings per month!)
Turn around times for postings can sometimes be a little slow. It is not unusual for messages sent from Australia to take a few hours to appear on the list. List subscribers from other countries have also reported similar turn around times. Please be patient.
To unsubscribe from the list digest send the words "unsubscribe msql-list-digest" in the body of your message to [email protected].
| Archive Location | Features |
|---|---|
|
http://tacyon.spectrum.com.au/mail/msql (Thanks to Matt Perkins <[email protected]>). |
Threaded archive, list archive, old list archives. |
|
http://cure.medinfo.org/lists/programming/index.html (Thanks to the folks at Medinfo http://cure.medinfo.org). |
Threaded archive, browse current month, search list archive. Currently Unavailable. |
|
http://www.nexial.nl/cgi-bin/msql (Thanks to Kim Hendrikse <[email protected]>). |
Fuzzy searchable archive. |
|
http://www.findmail.com/listsaver/msql-list (Thanks to FindMail Communications http://www.findmail.com). |
Listed by subject thread, date or author. Archives from October 1995. |
This question comes up about every three months or so - usually from new subscribers to the mSQL mailing list who haven't seen the previous threads.
There seem to be two distinct groups of people involved in this discussion:
Nor does it preclude you from establishing a global news group. There are well defined channels established within the Usenet community for the creation of new news groups.
One publicly available mail/news gateway is available via anonymous ftp from:
ftp://ftp.vix.com/pub/inn/contrib/newsgate.tar.Z
(443025 bytes)
It includes the programs mail2news and news2mail.
Is there any way I can find the answer to this question myself?
If you can figure out a way to simply find the answer, then it will
probably be quicker than asking the list. If you think your answer
would be helpful to others then post a summary to the mailing list.
Postings should be addressed to [email protected].
IF YOU POST A QUESTION TO THE LIST ASKING FOR HELP, YOU
MUST INCLUDE THE FOLLOWING INFORMATION!
Failure to include these details makes it almost impossible to pinpoint
the cause of your problem.
uname -a
msqladmin version
http://Hughes.com.au/product/msql/history.htm
If you want to report a bug, send a report to the mSQL bug reporting address at [email protected]. You may also wish to copy your report to the mSQL mailing list at [email protected].
When making your bug report, please include the following information:
msqld.c
Save the original file as follows:
cp ./src/msql/msqld.c ./src/msql/msqld.c.orig
Make your changes to the file:
./src/msql/msqld.c
diff -c ./src/msql/msqld.c.orig ./src/msql/msqld.c
ftp://bond.edu.au/pub/Minerva/msql/Incoming
then notify David at
<[email protected]>.
He will move your contribution to the mSQL
contributions
directory:
ftp://bond.edu.au/pub/Minerva/msql/Contrib
Note: The directory permissions for
ftp://bond.edu.au/pub/Minerva/msql/Incoming prohibit the
viewing of any files contained therein. This is a security feature
implemented by the system administration staff at
Bond University to eliminate recent
attempts to use the ftp server as a relay for the unauthorised transfer
of commercial software. If you upload any software to this directory,
you must notify
<[email protected]> to
have it moved to
ftp://bond.edu.au/pub/Minerva/msql/Contrib. You may like to discuss your proposed code with others on the mSQL mailing list. The subscribers to this list may be able to help you with improvements or modifications to your code or advise you of work already available in your area.
If you're writing code in other languages, have a look through the distribution of the mSQL language extension itself for examples. Another good place to look is the mSQL mailing list archives.
| Support Programs bundled with mSQL version 1.x | |
|---|---|
| msqld | the mSQL database server. |
| msqladmin | handles administrative details such as creation and deletion of databases, server shutdown etc. |
| msql | the mSQL monitor. It provides a user interface for making SQL queries on databases. |
| msqldump | Dumps a database in ASCII format. The dump takes the form of SQL commands so that the entire database can be re-created from the dump file. |
| relshow | The mSQL schema viewer. Shows table details for a given database. |
| Support Programs bundled with mSQL version 2.x | |
|---|---|
| msql2d | the mSQL database server. |
| msqladmin | handles administrative details such as creation and deletion of databases, server shutdown etc. |
| msql | the mSQL monitor. It provides a user interface for making SQL queries on databases. |
| msqldump | Dumps a database in ASCII format. The dump takes the form of SQL commands so that the entire database can be re-created from the dump file. |
| msqlexport | Dumps a database in ASCII format. The dump takes the form of user defined character delimited fields suitable for importing into other vendor's databases. |
| msqlimport | Loads a flat file in ASCII format into an mSQL database. |
| relshow | The mSQL schema viewer. Shows table details for a given database. |
| w3-msql | CGI program that allows users to embed mSQL statements in their HTML documents. |
| w3-auth | Access control and authentication module for use with w3-msql |
| lite | A stand alone mSQL scripting language. |
For more details on these programs see the documentation that comes with mSQL.
| mSQL version 1.x |
|---|
CREATE TABLE table_name (
col_name col_type [ not null | primary key ]
[, col_name col_type [ not null | primary key ] ]**
)
|
DROP TABLE table_name |
INSERT INTO table_name [ ( column [ , column ]** ) ]
VALUES (value [, value]** )
|
DELETE FROM table_name
WHERE column OPERATOR value
[ AND | OR column OPERATOR value ]**
OPERATOR can be <, >, =, <=, >=, <>, or LIKE
|
SELECT [ DISTINCT ] [table.]column [ , [table.]column ]**
FROM table [ = alias] [ , table [ = alias] ]**
[ WHERE [table.]column OPERATOR VALUE
[ AND | OR [table.]column OPERATOR VALUE]** ]
[ ORDER BY [table.]column [DESC] [, [table.]column [DESC] ] [LIMIT n]
OPERATOR can be <, >, =, <=, >=, <>, or LIKE
VALUE can be a literal value or a column name
|
UPDATE table_name SET column=value [ , column=value ]**
WHERE column OPERATOR value
[ AND | OR column OPERATOR value ]**
OPERATOR can be <, >, =, <=, >=, <>, or LIKE
|
For more details see the documentation that comes with mSQL version 1.x.
mSQL version 2.x supports the following SQL commands:
| mSQL version 2.x |
|---|
CREATE TABLE table_name (
col_name col_type [ not null ]
[, col_name col_type [ not null ] ]**
)
|
CREATE [ UNIQUE ] INDEX index_name ON table_name (
col_name [, col_name ]**
)
|
CREATE SEQUENCE ON table_name
[ STEP step_val ] [ VALUE initial_val ]
|
DROP TABLE table_name |
DROP INDEX index_name FROM table_name |
DROP SEQUENCE FROM table_name |
INSERT INTO table_name [ ( column [ , column ]** ) ]
VALUES (value [, value]** )
|
DELETE FROM table_name
WHERE column OPERATOR value
[ AND | OR column OPERATOR value ]**
OPERATOR can be <, >, =, <=, >=, <>, LIKE, RLIKE or CLIKE
|
SELECT [ DISTINCT ] [table.]column [ , [table.]column ]**
FROM table [ = alias] [ , table [ = alias] ]**
[ WHERE [table.]column OPERATOR VALUE
[ AND | OR [table.]column OPERATOR VALUE]** ]
[ ORDER BY [table.]column [DESC] [, [table.]column [DESC] ] [LIMIT n]
OPERATOR can be <, >, =, <=, >=, <>, LIKE, RLIKE or CLIKE
VALUE can be a literal value or a column name
WHERE clauses may contain parentheses () to nest conditions
|
UPDATE table_name SET column=value [ , column=value ]**
WHERE column OPERATOR value
[ AND | OR column OPERATOR value ]**
OPERATOR can be <, >, =, <=, >=, <>, LIKE, RLIKE or CLIKE
|
For more details see the documentation that comes with mSQL version 2.x.
Under mSQL version 1.x any single field of a table can be designated as the PRIMARY KEY. These keys are, by definition, unique. mSQL version 1.x does not support multiple keys within a table.
The creation of a PRIMARY KEY is made during the table declaration, for example:
CREATE TABLE employee (
employee_number INT PRIMARY KEY,
department CHAR(20),
first_name CHAR(20) NON NULL,
last_name CHAR(20) NON NULL
)
CREATE TABLE department (
department_name CHAR(20) PRIMARY KEY
)
For more details see the documentation that comes with mSQL version
1.x. mSQL version 2.x has disposed of the PRIMARY KEY in favour of an indexing mechanism.
An INDEX may be created for any field of a table at any time, for example:
CREATE UNIQUE INDEX idx1 ON employee(employee_number)
CREATE INDEX idx2 ON employee(first_name, last_name)
This examples shows that the first INDEX, idx1, is
unique. This is identical to the PRIMARY KEY concept used by
mSQL version 1.x. The second INDEX, idx2, need
not be unique. idx2 also spans two fields within a table which
will improve performance whenever a search is made based on these two
fields. These indices will be used automatically whenever a query is sent to the database engine that uses those fields in its WHERE clause. The user is not required to specify any special values in the query to ensure the indices are used to increase performance.
int (4 bytes) -2147483646 <= i <= 2147483647
real (8 bytes) 4.94E-324 <= x <= 1.79E+308, -1.79E+308 <= x <= -4.94E-324
char
The internal storage for types int and real is held at
4 bytes and 8 bytes respectively, regardless of the system architecture
you're using. So even on 64 bit Crays a real will be 8 bytes.
There is NO fixed limit on the size of a character field. Provided you declare it correctly when the table is defined, mSQL will handle fields of at least several thousand characters without problems. You may run into difficulty actually using fields of this size in whatever language you choose to interface to the mSQL database engine.
Note: If you declare a char field with a large size, each and every row in the table will allocate sufficient storage space for the char field - whether the full size of the field is used or not. This may lead to very large database tables indeed.
An example of each mSQL version 1.x datatype within a table declaration follows:
CREATE TABLE table_name (
field_name_1 INT PRIMARY KEY,
field_name_2 REAL,
field_name_3 CHAR(10) NOT NULL,
field_name_4 CHAR(80),
field_name_5 INT NOT NULL,
field_name_6 REAL
)
For more details see the documentation that comes with mSQL version
1.x. mSQL version 2.x supports the following datatypes:
int (4 bytes) -2147483646 <= i <= 2147483647
uint (4 bytes) 0 <= i <= 4294967295
real (8 bytes) 4.94E-324 <= x <= 1.79E+308, -1.79E+308 <= x <= -4.94E-324
char
text
date dd-mon-yy, for example 12-May-97
time
money
The internal storage for types int/uint and real is
held at 4 bytes and 8 bytes respectively, regardless of the system
architecture you're using. So even on 64 bit Crays a real will
be 8 bytes. There is NO fixed limit on the size of a character field. Provided you declare it correctly when the table is defined, mSQL will handle fields of at least several thousand characters without problems. You may run into difficulty actually using fields of this size in whatever language you choose to interface to the mSQL database engine.
Note: If you declare a char field with a large size, each and every row in the table will allocate sufficient storage space for the char field - whether the full size of the field is used or not. This may lead to very large database tables indeed.
text (or other 8 bit data) fields are defined with an expected average length. Values longer than the specified length will be split between the data table and external overflow buffers. text fields are slower to access than char fields and cannot be used in an index or in LIKE tests.
An example of each mSQL version 2.x datatype within a table declaration follows:
CREATE TABLE table_name (
field_name_1 INT,
field_name_2 REAL,
field_name_3 CHAR(10) NOT NULL,
field_name_4 CHAR(80),
field_name_5 INT NOT NULL,
field_name_6 REAL,
field_name_7 TEXT(25),
field_name_8 DATE,
field_name_9 UINT,
field_name_10 TIME,
field_name_11 MONEY
)
Note: The storage of real numbers is highly machine dependent.
If you store the number 10.03 don't be surprised if it is
actually stored as either 10.03 or 10.03000000001 or
10.02999999999. Note: The upper and lower limits for real numbers are also highly machine dependent. Both mSQL version 1.x and 2.x obtain these values from the system include file:
/usr/include/limits.h
If you plan on storing very big numbers (positive or negative numbers
with exponents approaching 308) or very small numbers (positive or
negative numbers with exponents approaching -324) you MUST check
whether your operating system will support them. Note: When dealing with real numbers it might be advisable to either convert the real number to an integer if possible. For example if you are dealing with decimal currency, it may be preferable to work in units of cents rather than dollars - note that mSQL 2.x now has a MONEY type which handles this concept - or to perform some sort of delta check when retrieving values. The following logic demonstrates one possible application of this concept:
delta = 0.00001
select a real number from a table
if abs(number - expected value) < delta
then
proceed
else
fail
| mSQL version 1.x | ||
|---|---|---|
| Return Type | Prototype | Type |
| int | msqlConnect(char *host); | FUNCTION |
| int | msqlSelectDB(int sock, char *dbname); | FUNCTION |
| int | msqlQuery(int sock, char *query); | FUNCTION |
| m_result * | msqlStoreResult(); | FUNCTION |
| void | msqlFreeResult(m_result *result); | FUNCTION |
| m_row | msqlFetchRow(m_result *result); | FUNCTION |
| void | msqlDataSeek(m_result *result, int pos); | FUNCTION |
| int | msqlNumRows(m_result *result); | MACRO |
| m_field * | msqlFetchField(m_result *result); | FUNCTION |
| void | msqlFieldSeek(m_result *result, int pos); | FUNCTION |
| int | msqlNumFields(m_result *result); | MACRO |
| m_result * | msqlListDBs(int sock); | FUNCTION |
| m_result * | msqlListTables(int sock); | FUNCTION |
| m_result * | msqlListFields(int sock, char *tableName); | FUNCTION |
| void | msqlClose(int sock); | FUNCTION |
For more details see the documentation that comes with mSQL version 1.x.
Note: The PostScript documentation that comes with mSQL version 1.x lists the function msqlClose() as type int. This is incorrect. It is actually type void.
| mSQL version 2.x | ||
|---|---|---|
| Return Type | Prototype | Type |
| int | msqlConnect(char *host); | FUNCTION |
| int | msqlSelectDB(int sock, char *dbname); | FUNCTION |
| int | msqlQuery(int sock, char *query); | FUNCTION |
| m_result * | msqlStoreResult(); | FUNCTION |
| void | msqlFreeResult(m_result *result); | FUNCTION |
| m_row | msqlFetchRow(m_result *result); | FUNCTION |
| void | msqlDataSeek(m_result *result, int pos); | FUNCTION |
| int | msqlNumRows(m_result *result); | MACRO |
| m_field * | msqlFetchField(m_result *result); | FUNCTION |
| void | msqlFieldSeek(m_result *result, int pos); | FUNCTION |
| int | msqlNumFields(m_result *result); | MACRO |
| m_seq * | msqlGetSequenceInfo(int sock, char *table); | FUNCTION |
| m_result * | msqlListDBs(int sock); | FUNCTION |
| m_result * | msqlListTables(int sock); | FUNCTION |
| m_result * | msqlListFields(int sock, char *tableName); | FUNCTION |
| m_result * | msqlListIndex(int sock, char *tableName, char *index); | FUNCTION |
| void | msqlClose(int sock); | FUNCTION |
| char | msqlUnixTimeToDate(char *date); | FUNCTION |
| char | msqlUnixTimeToTime(char *date); | FUNCTION |
| time_t | msqlDateToUnixTime(char *date); | FUNCTION |
| time_t | msqlTimeToUnixTime(char *date); | FUNCTION |
For more details see the documentation that comes with mSQL version 2.x.
Note: The HTML documentation that comes with mSQL version 2.x lists the function msqlClose() as type int. This is incorrect. It is actually type void.
This feature may be included in mSQL version 2.
As an example, consider this method to find the list of grandparents from a child/parent tuple:
SELECT t1.parent, t2.child from parent_data=t1,parent_data=t2
WHERE t1.child=t2.parent
mSQL also supports the SQL standard method of table aliasing which uses
either a space character or the keyword AS instead of an = character.
So the above example can also be written as either:
SELECT t1.parent, t2.child from parent_data t1,parent_data t2
WHERE t1.child=t2.parent
or
SELECT t1.parent, t2.child from parent_data AS t1,parent_data AS t2
WHERE t1.child=t2.parent
mSQL has an access control file which allows the database administrator to control access on a user and host level.
For more details see the documentation that comes with mSQL.
mSQL does not support access control from within SQL commands.
The current release of mSQL has NO direct support for BLOBs. However, you can always store the path name of a file that points to the BLOB in one of the fields of your table. Then your application can deal with the file name appropriately.
If you're dealing with large blocks of text, you may also wish to consider this approach from Pascal Forget <[email protected]>:
Another possible hack would be to have the "block_text"
record contain a pointer to a "text_lines" table. This
table would contain a linked list of text lines like this:
CREATE TABLE text_lines (
line_id int primary key,
string char(80),
next_line int
)
Mike Eggleston
<[email protected]>
offers this solution:
What I prefer to do in databases where I have text fields containing
near infinite amounts of text is to define several tables like:
create table prog (
id int,
name char(40),
programmer char(40),
....
)\p\g
create table descript (
id int,
line int,
descript char(100)
)\p\g
Then in a program I break up the text as necessary and put one line
of text in each record of <descript>. When I want it back, by
program, I [use]
select line, descript from prog, descript
where prog.id = descript.id
and name = 'foobar' order by line\p\g
Later versions of mSQL may have support for BLOBs.
The mSQL server handles requests serially - that is only one user's request is handled at a time. Therefore there is no danger of a user reading from a table that is currently being updated by another user.
However, there is the possibility that the same read operations may straddle another user's write operation so that different data will be returned from each read.
mSQL version 2 will provide client initiated locking.
19 ----- \ \ i 52 * / 63 = 813621925049196536663393538834956800 / ----- i = 0Though in practise, many of these combinations will probably remain unused.
While not recommended, the default maximum name length value of 20 can be changed by editing the mSQL source code. However, if you change it AFTER you have already created ANY databases, the old databases will be unreadable. To avoid this problem, follow this procedure:
./src/msql/msql_priv.h
Change the line reading
#define NAME_LEN 19 /* Field/table name length */
to suit your needs. Ensure that you use a number that is one less
than the maximum value you desire. For example, if you wish to have
a name length of 36 you would change the line to read:
#define NAME_LEN 35 /* Field/table name length */
./src/msql/msql_priv.h
contains the definitions of the internal mSQL limits:
#define MAX_FIELDS 75 /* Max fields per query */
#define MAX_CON 24 /* Max connections */
#define BUF_SIZE (256*1024) /* Read buf size if no mmap() */
#define NAME_LEN 19 /* Field/table name length */
#define PKT_LEN (32*1024) /* Max size of client/server packet */
#define CACHE_SIZE 8 /* Size of table cache */
If you want to increase them you can just edit this file and recompile.
Don't change MAX_CON or CACHE_SIZE without understanding why these
limits are set (maximum file descriptors per process etc). Changing any of these parameters will almost certainly make any existing databases unreadable. To avoid this problem, follow this procedure:
./src/msql/msql_priv.h
changing the definitions to suit your needs.
Consider the SQL query:
SELECT something from somewhere WHERE
name='jan' or country='italy' and sex='female' or title='ms'
Under mSQL version 1.x, the parser will scan the condition
from left to right. So in this example the condition reads:
((name='jan' or country='italy') and sex='female') or title='ms'
mSQL version 1.x does NOT support parentheses in logical
expressions, so there is NO way to change this parsing. Ted Harding <[email protected]> provides some solutions for three component queries.
Ted writes:
Let's get something clear: ALL 3-component (or equivalent) queries
can be implemented in mSQL (without parentheses and using the mSQL
left-to-right evaluation). There are only the following:
(A and B) and C = A and (B and C) = A and B and C
(A or B) or C = A or (B or C) = A or B or C
(A and B) or C = A and B or C
A and (B or C) = (B or C) and A = B or C and A
(A or B) and C = A or B and C
A or (B and C) = (B and C) or A = B and C or A
Queries like A and B and C and (E or F) are the same as (A and B
and C) and (E or F) which is the same form as G and (E or F).
The trouble starts with 4-component queries such as (A or B) and (C
or D) for which there is no one-pass generally correct mSQL
representation.
mSQL version 2.x allows parentheses within logical expressions
so this limitation is eliminated.
In C, for example, see the manual pages on atoi().
Does SELECT return the rows always in order 'first inserted first',
if there is no ORDER statement given, and the rows are selected
from one table only, and there has been no DELETEs on that table?
It seems be so, but is it guaranteed?
David Hughes replied:
This is guaranteed. The only time the rows will be returned in
another order is if you have deleted a row and it's then filled by
a later insert.
I am new at mSQL, and have a beginner question: Is it possible to
create a table "normally", and to have the fields of one
of the column being[sic] another table?
David Hughes replied:
You can't nest tables in mSQL (don't think you can in ANSI SQL
either). What you can do is to use a common value as a key to join
the contents of two tables (eg. a part number or a user ID).
/usr/local/Minerva/
then the databases will be created in the directory:
/usr/local/Minerva/msqldb/
Note that this can be overridden by specifying the MSQL_HOME
environment variable when starting
msqld. Each table in the database is stored as a number of files:
For each field in a table, mSQL will also store an additional flag byte. mSQL also stores an additional flag byte for each row of the table.
Consider the following table:
CREATE TABLE test (
f0 char(13),
f1 int,
f2 real,
f3 real,
f4 real,
f5 real,
f6 int
)
Storage space for each row of this table would be:
(13 * char) + (2 * int) + (4 * double) + (7 * fields) + (1 * rows)
= (13 * 1) + (2 * 4) + (4 * 8) + (7 * 1) + 1
= 61 bytes
So if this table had 1000 records, it would occupy 61000 bytes of
disk space. (In reality it may occupy slightly more real disk space
because of the way the underlying file system behaves. This is
operating system specific and not really an issue to worry about.
If you do an 'ls -l' on the file it will show 61000
bytes).
The size of this file will be the size of the key plus one flag byte times the number of rows in the table. In the above example, if the table was defined as:
CREATE TABLE test (
f0 char(13) primary key,
f1 int,
f2 real,
f3 real,
f4 real,
f5 real,
f6 int
)
and the table had 1000 rows, the size of the data file would still
be 61000 bytes and the size of the key file would be:
((13 * char) + 1) * 1000
= ((13 * 1) + 1) * 1000
= 14 * 1000
= 14000 bytes
Each field in the table has a 64 byte definition. Using the example above, the table has 7 fields so the size of the definition file will be:
7 * 64 = 448 bytes
For every hole in the table, this file will contain a 4 byte integer indicating the row number of the hole. It is accessed like a stack. When a row is deleted, it's index is appended to the file. When an insert is done, the last 4 bytes are "popped" off the file and the file is truncated back 4 bytes.
If the table contains 20 holes, the size of the stack file will be:
20 * 4 = 80 bytes
If the table contains no holes then this file will have zero
length.
table_storage_requirements
= expected_max_rows *
(
number_of_fields + 1 + total_chars +
(4 * total_ints) + (8 * total_reals) +
(size_of_key + 1) +
(4 * expected_deletion_ratio)
) +
(total_fields * 64)
table_storage_requirements
= expected_max_rows *
(
number_of_fields + 1 + total_chars +
(4 * total_ints) + (8 * total_reals) +
(4 * expected_deletion_ratio)
) +
(total_fields * 64)
10000 *
(
7 + 1 + 13 +
(4 * 2) + (8 * 4) +
(13 + 1) +
(4 * 0.10)
) +
(7 * 64)
= 10000 * ( 21 + 8 + 32 + 14 + 0.4) + 448
= 754448 bytes
plus a handful of bytes to store file names in directories. Note that this is the maximum storage allocation. Unlike some other database systems, mSQL only uses disk space when it has data to add to a table - it does NOT allocate a large block of empty disk space and then proceed to fill it. If our example only had 1000 rows the storage requirements would only be 75848 bytes.
does msqld allocate more ram to itself as new db's are added? i.e.
is any part of the database held in ram or does it just access the
database files directly from disc? I need to do some planning, and
want to know if I need to plan to get more simms...
David Hughes replies:
If your OS supports mmap() (e.g. Solaris, SunOS, *BSD, BSDI, Linux
1.3.x, HP-UX >9.x) then the more memory you throw at it the
better things will get if you are using big databases. The data is
left on disk but is accessed via the virtual memory subsystem so it
will be in memory some of the time.
If you are not using mmap() then data is just read from disk as it
is needed. There's a small buffer in the read code to make things
faster but that's about it. It doesn't matter how many databases
you have defined it only uses 1 buffer.
Does performance degrade at all as the number of databases
increases? That is, say a query from database A took n seconds when
database A was the only one served by msqld. After adding databases
B, C, D and E, should the database A query take any longer? It
seems like 'no' from my experience, but...
David Hughes replies:
No. It will degrade if people are hitting the new databases at the
same time as they are hitting database A though. msqld only handles
1 query at a time so if 2 queries come in they are queued and
processed in order.
> To browse the database, I want mSQL to return me the first row
> in the database, and keep a pointer to it. Then sometime later
> I can ask it for the second row, and so on.
mSQL does not provide support for cursors.
You'll have to issue a SELECT query each time you want the next
row. mSQL has no provisions for modifying a result set once it has
been created. I suggest you add a field containing a unique
identifier for each row, then fetch the next row using:
SELECT ... FROM mytable where unique_field > last_id LIMIT 1
> How do I express this in sql?
If you find a way to express it, it most certainly won't be in
standard SQL, as the language has no support for cursors.
> I see that I could add an explicit field that was an arbitrary
> row number, and query for the current row number +/- 1, but over
> time with insertions and deletions there would be gaps and the
> query would break. How is this problem usually solved?
The select statement I gave you won't break even if there are gaps
in the unique identifiers. You can periodically "compact" the
numbers if you want.
Yes, as long as the client that fetches the characters knows what
character set you are using. I.e. there is no support in mSQL for
keeping track of the character set name, but mSQL is 8bit clean so
you can store 8bit characters (in whatever character set).
In Digger, the Whois++ server, we store UNICODE characters by
encoding them first into UTF-8 which is an 8bit encoding scheme
described in UNICODE 1.1.
Neil Bowers
<[email protected]> has written a paper on
"Processing Japanese Text with mSQL and Perl". It is
available from:
http://www.cre.canon.co.uk/~neilb/jmSQL.html
AFS does not support some Unix file system behaviour that's needed
by mSQL. You cannot create Unix domain sockets in AFS space, and
mmap() does not work on at least some client-server combinations,
if not all.
You should install mSQL and its databases in NFS or local Unix
filesystems.
Other users have also reported problems when running mSQL from NFS
mounted partitions. To be absolutely safe you should only run
msqld from the same machine on which the physical disks
containing the mSQL databases are attached.
The solution to the problem with using the socket and then nsl
libraries with NIS in IRIX 5.2 is:
1. Do not link them if they are not needed :) This is the case for
mSQL.
or
2. link libc BEFORE the socket and the nsl libraries.
For those who didn't know, the problem is that if you use NIS and
link socket or nsl, the getpwuid() function doesn't work.
To ensure that the unnecessary libraries are avoided, edit
targets/your-architecture/site.mm and change the line
EXTRA_LIB= -lsocket -lnsl -lgen -ldl
so that it reads:
EXTRA_LIB=
Looks like the same thing that happens under HP-UX with background
processes in rc scripts. They are killed off on exit of the ksh
functions.
Create yourself a wrapper for msqld. In there you do a fork and
exit the parent process in the child process you do a call to
setsid() to get rid of the controlling terminal followed by a call
to execl() to launch msqld. You might also want to close all open
file descriptors before calling exec.
An alternative to this approach is to place the following in
/etc/inittab
msql:3:respawn:/usr/local/Minerva/bin/msqld </dev/console >/dev/console 2>&1
This assumes that your mSQL super user is "root".
The next version of the FAQ will attempt to address this issue in detail.
DEC Alphas running OSF/1 (Digital Unix): The original mSQL docs
recommended using cc rather than gcc on this platform. In my
experience this is still good advice. If you have gcc on the
machine, however, autoconf will find it and default to it. After
running 'setup' edit site.mm and change 'gcc' to 'cc' and
'bison -y' to yacc.
If you have an earlier version of Linux you can either upgrade or ensure that mmap() support in mSQL is disabled by running the 'setup' program and then editing
./targets/your-architecture/site.mm
and ensuring the mmap() directive reads:
MMAP=
and then recompile the mSQL package.
I just built msql-1.0.10 on hpux 9.05. It appears that you have
slain the mmap bug. Good job. The only mods I [made] are in site.mm
CC= cc -Ae +O3 +Z
Remove -g flags also or you'll get a lot of annoying messages about
opt not compatible with debug. The test suites for msql and
msqlperl ran flawlessly.
This problem has to do with the way HP-UX deals with shared
libraries.
Ensure the EXTRA_CFLAGS option in the file:
./targets/your-architecture/site.mm
reads:
EXTRA_CFLAGS= -Ae +O3 +Z
and recompile mSQL.
The +Z option ensures that "position independent
code" is used when creating object files. For more information
see the manual pages on your compiler.
The following is a summary of the efforts required by Andrew Cash <[email protected]> to install mSQL version 1.0.8 on a SCO Unix system. It should work perfectly well for version 1.0.9 as well.
gunzip -c msql-1.0.9.tar.gz | tar xvf -
cd msql-1.0.9
make target
cd targets/your-architecture
./setup
answer questions
./common/config.h
ensure the lines referring to the sys/select.h include file are
commented out as follows:
/* Defined if you have sys/select.h */
/* #define HAVE_SYS_SELECT_H 1 */
SCO Unix doesn't have an alloca() library function so you'll have to use malloc(). You need to generate the file:
./msql/msql_yacc.c
so run:
make
until it completes (or fails). Ensure that the msql_yacc.c
file has been created. If it has,
applythe following patch to the file.
(Use the "-l" option of patch to avoid
any problems with mismatched whitespace. That's an
"el", NOT a "one" or an "eye").
This patch ensures that <malloc.h> is explicitly included and that all references to alloca() are changed to malloc(). Note: This patch has been generated based on the output from the bison compiler compiler from GNU. It should NOT be applied to an msql_yacc.c that has been generated by yacc - such a file already uses malloc().
*** msql/msql_yacc.c.orig Fri Jan 5 13:07:02 1996
--- msql/msql_yacc.c Fri Jan 5 13:09:34 1996
***************
*** 329,362 ****
Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. */
- #ifndef alloca
- #ifdef __GNUC__
- #define alloca __builtin_alloca
- #else /* not GNU C. */
- #if (!defined (__STDC__) && defined (sparc)) || defined (__sparc__) || defined (__sparc) || defined (__sgi)
- #include <alloca.h>
- #else /* not sparc */
- #if defined (MSDOS) && !defined (__TURBOC__)
- #include <malloc.h>
- #else /* not MSDOS, or __TURBOC__ */
- #if defined(_AIX)
#include <malloc.h>
- #pragma alloca
- #else /* not MSDOS, __TURBOC__, or _AIX */
- #ifdef __hpux
- #ifdef __cplusplus
- extern "C" {
- void *alloca (unsigned int);
- };
- #else /* not __cplusplus */
- void *alloca ();
- #endif /* not __cplusplus */
- #endif /* __hpux */
- #endif /* not _AIX */
- #endif /* not MSDOS, or __TURBOC__ */
- #endif /* not sparc. */
- #endif /* not GNU C. */
- #endif /* alloca not defined. */
/* This is the parser code that is written into each bison parser
when the %semantic_parser declaration is not specified in the grammar.
--- 329,335 ----
***************
*** 607,618 ****
yystacksize *= 2;
if (yystacksize > YYMAXDEPTH)
yystacksize = YYMAXDEPTH;
! yyss = (short *) alloca (yystacksize * sizeof (*yyssp));
__yy_bcopy ((char *)yyss1, (char *)yyss, size * sizeof (*yyssp));
! yyvs = (YYSTYPE *) alloca (yystacksize * sizeof (*yyvsp));
__yy_bcopy ((char *)yyvs1, (char *)yyvs, size * sizeof (*yyvsp));
#ifdef YYLSP_NEEDED
! yyls = (YYLTYPE *) alloca (yystacksize * sizeof (*yylsp));
__yy_bcopy ((char *)yyls1, (char *)yyls, size * sizeof (*yylsp));
#endif
#endif /* no yyoverflow */
--- 580,591 ----
yystacksize *= 2;
if (yystacksize > YYMAXDEPTH)
yystacksize = YYMAXDEPTH;
! yyss = (short *) malloc (yystacksize * sizeof (*yyssp));
__yy_bcopy ((char *)yyss1, (char *)yyss, size * sizeof (*yyssp));
! yyvs = (YYSTYPE *) malloc (yystacksize * sizeof (*yyvsp));
__yy_bcopy ((char *)yyvs1, (char *)yyvs, size * sizeof (*yyvsp));
#ifdef YYLSP_NEEDED
! yyls = (YYLTYPE *) malloc (yystacksize * sizeof (*yylsp));
__yy_bcopy ((char *)yyls1, (char *)yyls, size * sizeof (*yylsp));
#endif
#endif /* no yyoverflow */
make
./msql/Makefile.full
and comment out the mode change line for msqld as
follows:
# chmod 4750 $(INST_DIR)/bin/msqld
Failure to make this change will result in an error during the
install phase.
make install
gcc -o prog prog.c -Imsql_install_dir/include \
-Lmsql_install_dir/lib -lmsql -lsocket -lnsl
If you installed Slackware and didn't get the K series disks then you will be missing a lot of C header files that you need to compile mSQL. Go back and grab the kernel sources disks and install them on your box.
After unpacking the mSQL distribution and running the make target command, the next step in installing mSQL is to change directories to:
targets/target
and run the setup command. If you have /sbin in your
$PATH ahead of ., or you don't have . in your
$PATH at all (which is a good idea if you happen to be
root) then your shell will attempt to run the Linux
setup program ahead of the mSQL setup program. This can be avoided (for all flavours of Unix) by issuing the command as:
./setup
This will force your shell to run the setup program in the
current working directory, regardless of the contents of your
$PATH.
Q: When I try to "make all" mSQL on Solaris (2.5 confirmed) it
fails with the error:
../makedepend/makedepend: warning: msqld.c (reading /usr/include/arpa/inet.h,
line 68): cannot find include file "sys/bitypes.h"
not in /usr/include/arpa/sys/bitypes.h
not in ../sys/bitypes.h
not in /usr/include/sys/bitypes.h
../makedepend/makedepend: warning: msqld.c (reading /usr/include/arpa/inet.h,
line 72): cannot find include file "sys/cdefs.h"
not in /usr/include/arpa/sys/cdefs.h
not in ../sys/cdefs.h
not in /usr/include/sys/cdefs.h
A: You have installed BIND 4.9 on your system. Bind replaces your existing
/usr/include/netdb.h, but forgets to include the compatability includes
cdefs.h and bitypes.h
Solution: locate the cdefs.h and bitypes.h files in your BIND source code
bind/compat/include/sys/cdefs.h
bind/compat/include/sys/bitypes.h
and copy these two files to /usr/include/src mSQL should now make
cleanly.
Credit should be given to Emir Mulabegovic <[email protected]> for this
answer.
The version of makedepend that ships with mSQL version 1.x does not appreciate the ANSI C syntax used by the Solaris 2.5 include files and gives a number of errors such as:
"strlib.c":225: defined(__EXTENSIONS__) || defined(_REENTRANT) ||
(_POSIX_C_SOURCE - 0 >= 199506L)
^--- expecting )
These messages can be safely ignored. The actual compilation of the
mSQL source is unaffected. If you wish to avoid seeing these messages you can use the following procedure to use the makedepend that is shipped with OpenWindows 3.5 instead of the version of makedepend shipped with mSQL version 1.x.
@$(TOP)/makedepend/makedepend
so that they read:
@/usr/openwin/bin/makedepend
/usr/openwin/bin/makedepend: ignoring option -a
These too can be safely ignored.
ld: WARNING 134: weak definition of pmap_getport in /usr/lib/libnsl.so
preempts that weak definition in /usr/lib/libc.so.
as well as unresolved symbols, you'll need to ensure that unnecessary
libraries are not being used during the compilation. Edit targets/your-architecture/site.mm and change the line
EXTRA_LIB= -lsocket -lnsl -lgen -ldl
so that it reads:
EXTRA_LIB=
David Schuler <[email protected]> provides the following patch to solve this problem:
*** scripts/sys-arch.orig Fri Mar 28 16:25:49 1997
--- scripts/sys-arch Fri Mar 28 16:29:19 1997
***************
*** 43,52 ****
if test "$os" = "AIX"
then
- machine="rs6000"
maj=`uname -v`
min=`uname -r`
rev="$maj.$min"
fi
echo "${os}-${rev}-${machine}" | sed 's/ /_/g'
--- 43,57 ----
if test "$os" = "AIX"
then
maj=`uname -v`
min=`uname -r`
rev="$maj.$min"
+ if test $maj -eq 1
+ then
+ machine="i386"
+ else
+ machine="rs6000"
+ fi
fi
echo "${os}-${rev}-${machine}" | sed 's/ /_/g'
To avoid conflicts with mismatched whitespace,
apply this patch using the
"-l" option of patch.
(That's an "el", NOT a "one" or an
"eye").
*** src/util_script.c~ Mon Feb 10 11:47:24 1997
--- src/util_script.c Tue Mar 25 15:53:14 1997
***************
*** 174,180 ****
else if (!strcasecmp (hdrs[i].key, "Content-length"))
table_set (e, "CONTENT_LENGTH", hdrs[i].val);
else if (!strcasecmp (hdrs[i].key, "Authorization"))
! continue;
else
table_set (e, http2env (r->pool, hdrs[i].key), hdrs[i].val);
}
--- 174,181 ----
else if (!strcasecmp (hdrs[i].key, "Content-length"))
table_set (e, "CONTENT_LENGTH", hdrs[i].val);
else if (!strcasecmp (hdrs[i].key, "Authorization"))
! table_set (e, "HTTP_AUTHORIZATION", hdrs[i].val);
! /* continue; */
else
table_set (e, http2env (r->pool, hdrs[i].key), hdrs[i].val);
}
To avoid conflicts with mismatched whitespace,
apply this patch using the
"-l" option of patch.
(That's an "el", NOT a "one" or an
"eye").
If you indicated that it would not be running as root, you would have then nominated a username for the "root user". In that case, you must be logged in as the user you nominated before you can perform admin functions like creation of databases.
The manual also states that you can only perform the admin functions of msqladmin (i.e. any function other than 'version') from the local host. For security reasons you cannot perform administrative functions in a client/server manner of a network.
A sample ACL file is installed in the installation directory. You could copy this file to msql.acl and edit it to reflect the access you want to offer to your databases.
If you are seeing an error regarding the PID file, then one of the following could be the cause:
Top of install tree ? [/usr/local/Minerva]
Will this installation be running as root ? [y] n
What username will it run under ? peters
Directory for pid file ? [/var/adm]
You must ensure that this directory exists. The mSQL installation
procedure will NOT create this directory for you. If you did not specify root as the mSQL administration user when you answered the questions:
Will this installation be running as root ? [y] n
What username will it run under ? peters
you must ensure that the user you specified has write permissions in
the directory in which msqld will store its PID file. Under IRIX 5.3 the /var/adm directory can only be written to by the root user, so if your mSQL administration user is NOT root then you'll have to choose some other location such as /var/tmp or /var/share.
If you need to change the location of this directory, you can either rerun the setup program, or edit the file:
./targets/your_architecture/site.mm
and change the line:
PID_DIR= /var/adm
to suit your needs.
Failure to do this will almost certainly guarantee that your applications will fail at some stage while talking to the new mSQL database server. You may also miss out on some new feature provided by the new mSQL API.
Consider the following scenario:
-rwsr-xr-x 1 peters db 24576 Nov 13 1995 db_app
Access to database denied
mSQL version 2 will have radically different security mechanisms.
msqladmin shutdown
The TCP/IP port will remain bound for about 90 seconds or so. After
this time the port should be available and msqld can be
started successfully. Another possibility to consider is that something is already using the TCP/IP port that msqld is trying to use. For a default installation these port numbers are 1112 for a root user or 4333 for an ordinary user. In this case user means the name of the user you entered when answering the setup question(s):
Will this installation be running as root ?
What username will it run under ?
There are a number of ways you can check for something using the TCP/IP
port:
If the mSQL monitor program msql can connect to the mSQL database server msqld then you KNOW that the database server is already running.
Telnet to the database server and specify the mSQL TCP/IP port number using one of the following commands:
telnet dbhost 1112
or
telnet dbhost 4333
You'll see the following types of messages:
Trying 127.0.0.1 ...
telnet: connect: Connection refused
telnet>
Trying 127.0.0.1 ...
Connected to localhost.
Escape character is '^]'.
0:6:1.0.10
Trying 127.0.0.1 ...
Connected to localhost.
Escape character is '^]'.
If your operating system has the netstat command, you can use it to display the contents of various network related data structures in various formats, depending on the options you select. Some of the options that may be helpful are:
netstat -a | grep 4333
If you see output similar to:
*.4333 *.* 0 0 0 0 LISTEN
then something is using that port.
netstat -f unix
Output similar to the following will indicate that msqld
is already running:
Active UNIX domain sockets
Address Type Vnode Conn Addr
fcf8bca8 stream-ord 231 0 /tmp/msql.sock
This may not work for all operating systems. - the above examples
were taken from a Solaris 2.4 system. Variations on this command
include:
netstat -f inet
or
netstat -f local
msqladmin shutdown
If you don't have a running msqld process then something else
may be using the port that msqld is trying to use. Examine
/etc/inetd.conf and /etc/services (or the services
NIS map if you're running NIS) to see if anything else is using the
port. The output from one of the netstat commands listed
above may be helpful. If you find such a program you have two options:
/*
** TCP port for the MSQL daemon
*/
#ifdef ROOT_EXEC
#define MSQL_PORT 1112
#else
#define MSQL_PORT 4333
#endif
to suit your needs. Then recompile and reinstall mSQL.
Under mSQL version 1.x this file is typically /dev/msql or /tmp/msql.sock while under mSQL version 2.x it is typically %I/msql2.sock where %I is a reference to the directory specified by Inst_Dir in the mSQL configuration file.
These permission problems usually involve the socket itself or the directory in which it resides. The general rule here is that the directory should have read and write permission for the mSQL root user, and the socket (or named pipe on some operating systems) should be owned by the mSQL root user. (Note: The mSQL root user is the user you specified during the setup stage during the installation of mSQL version 1.x or the user specified in the msql.conf file for mSQL version 2.x).
Some operating systems, such as HP-UX 10.x, ship with dr-xr-xr-x permissions on /dev which makes it difficult for msqld to write to the socket. In these circumstances a better place for the socket would be /var/msql.
The installation instructions for mSQL 2.x state:
8. Ensure that the installation directory (and everything under it)
is owned by the user that mSQL will run as (e.g. the msql user).
To do this, issue a command like "chown -R msql /usr/local/Hughes".
Substitute the correct username and installation directory if
you are running a non-default installation.
If you decide to place the mSQL Unix domain socket in one of the
temporary directories such as /tmp /usr/tmp or
/var/tmp please read the section in this FAQ titled
"Where did my mSQL socket file
go?" You should also read the section in this FAQ titled "Where did my mSQL socket file go?" if your operating system does not ship with write permission on /dev.
msqldb/.tmp
under the directory in which you elected to install mSQL. If the .tmp directory does not exist, or doesn't have permissions that allow the mSQL super user to create files then you'll see the "Couldn't create temporary table" message.
To fix this, check for the existence of the directory and ensure that it has permissions:
drwxr-xr-x 2 owner group 512 Jan 28 16:10 .tmp
where owner is the user name of the mSQL super user and
group is whatever is appropriate to the way in which you want to
run your system (in other words it isn't terribly important in this
case). If you're going to do really big table joins then have a look at the section in the FAQ titled "How can I avoid running out of space when doing certain complex table joins?".
msqladmin shutdown
rm -rf /usr/local/Minerva/msqldb/.tmp
ln -s /lots/of/space /usr/local/Minerva/msqldb/.tmp
msqld &
This looks to me like you have compiled your msqld binary on a
machine with bzero() in your libc but you are running it on a
machine that does not have the bzero() function in its libc. Could
it be that you compiled on a Solaris 2.5 box, but you are running
it on a Solaris 2.4 machine? Sun made the brilliant decision to add
bzero, bcopy and rindex to the Solaris 2.5 libc which means that
Solaris 2.5 and 2.4 are not completely binary compatible anymore.
When you compile msql on Solaris 2.5 it will see that bzero and
bcopy are available and thus try to use them.
The fix is to either link your msqld statically with the Solaris
2.5 libc, or perhaps more preferable, recompile msql to not use the
silly bzero, bcopy, bcmp and rindex functions.
Edit common/config.h and make sure you do not have HAVE_BCOPY and
HAVE_RINDEX defined. If you do, comment out these definitions and
recompile. The resulting binary should then run on both target
machines.
The exact error message you'll see when running a Solaris 2.5 compiled
msqld on a Solaris 2.4 machine is:
peters@wheel[710] ./msqld
mSQL Server 1.0.10 starting ...
ld.so.1: ./msqld: fatal: relocation error: symbol not found: bzero:
referenced in ./msqld
Killed
Wonder if anyone encountered this weird display while using relshow?
relshow bookmarks
Database = bookmarks
+---------------------+
| Table |
+---------------------+
| okmarks |
| st |
+---------------------+
notice that the first two letters of the table names are missing.
Please help. Thanks in advance!
David Hughes replies:
I've seen this on Solaris if you link against the oh so broken BSD
compatibility library (libbsd.a). Please make sure that libbsd.a
isn't mentioned in your site.mm file.
And from the comp.unix.solaris FAQ:
6.19) Why doesn't readdir work? It chops the first two characters of
all filenames.
You're probably linking with libucb and didn't read question
6.18. Readdir in libucb.so wants you to include sys/dir.h,
but many SunOS 4.1.x programs included <dirent.h>,
consequently, you're mixing native <dirent.h> struct dirent
with libucb readdir(). The symptom of this mixup is that the
first two characters of each filename are missing. Make sure
you use the native compiler (default /opt/SUNWspro/bin/cc,
which may not be in your PATH), and not /usr/ucb/cc.
If you haven't shelled out the cash for Sun's compiler you can
substitute gcc for /opt/SUNWspro/bin/cc above.
mSQL command failed!
Server error = Permission denied
msqladmin has the following restrictions:
msqladmin version
this command can be run by any user, even over a network connection.
The corruption can be avoided by ensuring that the database files are not stored in a compressed file system.
Protocol mismatch. Server Version = 0 Client Version = 6
may indicate that the version of index() being used is
broken. This is true with some implementations of HP-UX 9.x. mSQL's autoconf build procedure will use index() if it finds it. The fix is to modify:
./targets/your_architecture/common/config.h
and remove (or comment out) the line that reads:
#define HAVE_RINDEX 1
Then recompile mSQL.
At 12:10 PM 9/17/96, I wrote:
>I'm trying to install mSQL on a MachTen BSD unix system and am having
>problems. The compile and installation went great (with only a warning
>that it could not detect 'uname' on the system). But when I tried to start
>the server from root with:
>
>/usr/local/Minerva/bin/msqld&
>
>I get the following:
>
>Can't start server : UNIX Bind : Invalid argument
>
>Anyone have any suggestions on what I am doing wrong or what is missing?
Thought I'd let everyone know what the problem was in case anyone else is
thinking of using MachTen BSD Unix
There's a bug in the <sys/un.h> file. It reads:
/*
* Definitions for UNIX IPC domain.
*/
struct sockaddr_un {
short sun_family; /* AF_UNIX */
char sun_path[108]; /* path name (gag) */
};
And it should be:
/*
* Definitions for UNIX IPC domain.
*/
struct sockaddr_un {
short sun_family; /* AF_UNIX */
char sun_path[106]; /* path name (gag) */
};
It seems sun_path was a little too long and the call to 'bind' in msqld
does a check on the parameter's structure size... thus giving an invalid
argument error.
On another note....
After I got that bug fixed, the server started, but when exercising the
tests, msql crashed MachTen! YES... I mean crash! So much for protected
memory!
But the real problem is yet another bug with MachTen.
It seems MachTen's mmap function "has not been fully
tested" (as one tech support person finally admitted). So
while mmap exists, it really doesn't work.
Borrowing from the msql FAQ:
>Version 1.3 or greater of the Linux operating system has full mmap support.
>If you're using such a version of Linux, mSQL will work perfectly well using
>mmap.
>
>If you have an earlier version of Linux you can either upgrade or ensure that
>mmap support in mSQL is disabled by running the 'setup' program and then
>editing
>
> ./targets/your-architecture/site.mm
>
>and ensuring the mmap directive reads:
>
> MMAP=
>
>and then recompile the mSQL package.
This also works for MachTen since it seems mmap is "not fully tested"...
and in reality it damn well crashed the machine.
These two issues took two full days to resolve.... with calls to Tenon tech
support in the morning, suggestions that were nowhere near fixing the
problem during the day... and the "real" answers coming near 5pm when tech
support closes... that these are bugs in MachTen.
While their tech support staff was always nice and tried to be very
helpful, I wish they would have been able to answer my questions when I
first called. It would have saved me two days of work on these issues.
They had me try all kinds of things until the call was elevated to another
tech (he doesn't get in until late afternoon) who had the answers... it's a
MachTen bug!
Very frustrating.
However on a positive point, MachTen has been running very good as my
internet server for the past six months or so without a glitch.
You've probably been bitten by an operating feature and/or some good system administration practise. Firstly some background.
The two classic temporary areas under most flavours of Unix are /tmp and /usr/tmp (/var/tmp in some systems). They are usually on different file systems but don't have to be.
Under some versions of Unix, /tmp is cleared whenever the system is rebooted. Other versions don't do this. Most versions of Unix do NOT clear /usr/tmp on reboot.
Some operating systems - such as Solaris 2.x and SunOS 4.x under certain configurations - take advantage of unused portions of the swap partition to provide /tmp. (This happens by default under Solaris 2.x and can be turned on using the TMPFS file system under SunOS 4.x). Because this area is not a real file system /tmp only has a transient lifetime and is guaranteed to be empty after each reboot.
While the system is running, some versions of Unix regularly clear /tmp and possibly /usr/tmp of files older than some arbitrary period (usually a few days or more). This procedure is handled by root via cron. Some versions of Unix are shipped with a root crontab to perform this cleanup, others aren't. Often the entry in crontab is draconian enough to remove everything older than the required period - including directories, named pipes, sockets etc. Other entries will only remove files. In is not unusual for system administration staff to add a similar entry to root's crontab if it doesn't already exist.
So what this boils down to is that files in /tmp and /usr/tmp (/var/tmp) are TEMPORARY and should be treated as such. If you need to create a socket for mSQL you should follow these simple rules:
However some operating systems such as HP-UX 10.x ship with dr-xr-xr-x permissions on /dev. In this case, you should choose a different location entirely for the mSQL Unix domain socket. /var/msql is one possible choice.
For both mSQL version 1.x and 2.x this can be accomplished by editing:
./targets/your-architecture/site.h
and modifying the value of
#define MSQL_UNIX_ADDR "/dev/msql"
for mSQL version 1.x, or
#define MSQL_UNIX_ADDR "/dev/msql2"
for mSQL version 2.x. Then re-compile.
./targets/your-architecture/site.h
and modifying the value of
#define MSQL_UNIX_ADDR "/tmp/msql.sock"
Then re-compile.
msql.conf
and change the value of
UNIX_PORT
By default this is %I/msql2.sock where %I is
the directory in which you installed mSQL 2.x. There is
no need to recompile in this case.
msqld is a single threaded application and therefore can only deal with one SQL query at a time. If msqld is busy servicing an SQL query you may not be able to make another connection until the SQL query has finished. mSQL version 1.x is extremely inefficient in performing table joins. So an SQL query with only a single table join may take many minutes to complete.
There is no solution to this problem in mSQL version 1.x. The only possible workaround is to ensure that your database schema is designed in such a way as to avoid table joins at all costs.
Table joins are much more efficient under mSQL version 2.x so the problem will rarely be encountered.
The expiration messages are reminders to the users of the beta version that Hughes Technologies expects to have either a new beta version or a completed version released within the time limit suggested by the message.
If the time limit has elapsed, msql2d will cease to operate and you will be presented with a message:
This Beta test release has expired!
Check out www.Hughes.com.au for a new release!
This site has a few problems at present. Use
http://hughes.com.au
instead. If a new version of mSQL 2.x (beta or otherwise) is not available after the time limit has expired, it is a simple matter to disable this feature using one of the patches listed below. Once you have applied the patch you MUST recompile and re-install. There is no need to recompile any third party applications as this patch only affects the operations of the database server.
To avoid conflicts with mismatched whitespace, apply the patch using the "-l" option of patch. (That's an "el", NOT a "one" or an "eye").
*** src/msql/msqld.c.orig Mon Dec 16 19:45:33 1996
--- src/msql/msqld.c Wed Apr 30 10:08:49 1997
***************
*** 583,589 ****
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (850703170 + 91*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Snapshot release has expired!\n");
--- 583,589 ----
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (999999999 + 91*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Snapshot release has expired!\n");
*** src/msql/msqld.c.orig Wed Apr 30 09:57:54 1997
--- src/msql/msqld.c Wed Apr 30 10:11:37 1997
***************
*** 591,597 ****
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (850703170 + 91*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Snapshot release has expired!\n");
--- 591,597 ----
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (999999999 + 91*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Snapshot release has expired!\n");
*** src/msql/msqld.c.orig Wed Apr 30 09:57:54 1997
--- src/msql/msqld.c Wed Apr 30 10:11:48 1997
***************
*** 595,601 ****
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (851999170 + 91*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Snapshot release has expired!\n");
--- 595,601 ----
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (999999999 + 91*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Snapshot release has expired!\n");
*** src/msql/msqld.c.orig Wed Apr 30 09:57:54 1997
--- src/msql/msqld.c Wed Apr 30 10:11:58 1997
***************
*** 600,606 ****
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (854763970 + 91*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Snapshot release has expired!\n");
--- 600,606 ----
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (999999999 + 91*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Snapshot release has expired!\n");
*** src/msql/msqld.c.orig Wed Apr 30 09:57:54 1997
--- src/msql/msqld.c Wed Apr 30 10:12:08 1997
***************
*** 651,657 ****
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (858088800 + 60*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Beta test release has expired!\n");
--- 651,657 ----
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (999999999 + 60*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Beta test release has expired!\n");
*** src/msql/msqld.c.orig Wed Apr 30 09:57:54 1997
--- src/msql/msqld.c Wed Apr 30 10:12:17 1997
***************
*** 696,702 ****
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (858088800 + 60*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Beta test release has expired!\n");
--- 696,702 ----
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (999999999 + 60*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Beta test release has expired!\n");
*** src/msql/msqld.c Mon May 12 17:35:02 1997
--- src/msql/msqld.c.orig Thu May 8 11:54:23 1997
***************
*** 696,702 ****
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (999999999 + 60*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Beta test release has expired!\n");
--- 696,702 ----
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (863060000 + 60*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Beta test release has expired!\n");
*** src/msql/msqld.c Mon May 12 17:35:08 1997
--- src/msql/msqld.c.orig Thu May 8 11:54:23 1997
***************
*** 696,702 ****
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (999999999 + 60*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Beta test release has expired!\n");
--- 696,702 ----
chdir(msqlHomeDir);
cleanTmpDir();
! timeRemain = (863060000 + 60*24*60*60) - time(NULL);
if (timeRemain < 0)
{
printf("\nThis Beta test release has expired!\n");
I. Chazakis <[email protected]> reports:
I've been using ip addresses in acl files since day one, without
any problems in version 1.xx, so I guess it should work for you as
well.
The numbers mentioned in the message give an indication of which versions of mSQL each end of the session is using. In the example given, the client was compiled with mSQL version 1.x (specifically 1.0.16) because the protocol version is less than 10, and the server is mSQL version 2.x because the protocol version is greater than 20.
This problem can be rectified by recompiling your third party mSQL code (that is any client code that did NOT come with the mSQL distribution, including software such as MsqlPerl etc) against the latest version of mSQL available to you.
Also see the section in the FAQ titled "I've just installed the latest version of mSQL and now my own applications won't work!".
mSQL behaves like a charm - mostly. That is, depending on the ordering
of the tables in the FROM clause of a SELECT query, mSQL will return
the results in different time scales - if at all!
These two SELECT queries only differ in the FROM clause. You will
notice that the "component" table comes last and first, respectively.
SELECT component.location
FROM instrument, has_dpr, data_product, component
WHERE instrument.ins_id LIKE 'SUSI'
AND instrument.ins_id = has_dpr.comp_id
AND has_dpr.dpr_id = data_product.dp_id
AND component.comp_id = data_product.comp_id
AND data_product.type = 'PRG'
SELECT component.location
FROM component, instrument, has_dpr, data_product
WHERE instrument.ins_id LIKE 'SUSI'
AND instrument.ins_id = has_dpr.comp_id
AND has_dpr.dpr_id = data_product.dp_id
AND component.comp_id = data_product.comp_id
AND data_product.type = 'PRG'
In version 1.0.16, the latter fails miserably (it returns after 5
minutes or so), whereas the former completes the query correctly
within seconds.
In version 2.0 beta 6, the latter returns after 30 seconds, whereas
the former completes the query wrongly!!
Could you tell me whether this is a known feature and whether there
are any workarounds or fixes? Of course, I'd gladly be informed of any
nonsense in my queries.
Robert Sum replies: <[email protected]>
A two part answer:
Part 1. Neither version of mSQL has a particularly aggressive
optimiser and they default to following the FROM and WHERE lines in
the order in which they are entered while performing the query.
Joins generally result in the formation of temporary tables (on the
disk); consequently, they are highly sensitive to the amount of
data processed. If the order of the query is such that the
temporary tables are large (i.e., the first clauses to be processed
don't reduce the overall amount of data by much), then it will be
slower. Therefore, if one knows what tables and clauses will
result in the smallest sizes for intermediate results, a query
written with those tables and clauses considered first will run
faster than other possible queries. Though not apparent in these
examples, some versions of mSQL 2.0 Bx also have a glitch in the
query mechanism that causes extra parenthesis to generate extra
temporary tables. So, it is best to use parentheses only when
necessary. (mSQL 1.x does not do parentheses.) Finally, there is
an initialisation overhead whenever a table must be read/mapped
into memory which will often cause the first time a query is
executed to be slower than succeeding times, provided that
preceding queries have not used the same tables.
Part 2. Each of mSQL 1.x and 2.0 Bx have various bugs in their
LIKE operations: one of them being the ability to run off the end
of the data into mismatch and core-dump lands. While trying not to
blow my own horn, the mailing list archives have my patches that
fix most the problems. However, the use of a bug-free LIKE in
these specific examples would only be a performance penalty
because "LIKE 'SUSI'" is the same as "= 'SUSI'".
I'm trying to do a join and each time it gives me an error.
select * from t1,t2 where t1.key=t2.key
This always gives me:
Reference to un-selected table ""
I'm using the 1.0.16 version
Chris Lambrou
<[email protected]> replies:
There's one I've never seen! Try this instead:
select t1.*, t2.* from t1,t2 where t1.key=t2.key
And Joshua Kugler
<[email protected]> also replies:
I found it in the UMBC mSQL Tutorial, at
http://www.cs.umbc.edu/~finin/461/msql:
Minor limitation: can't use "SELECT *" over multiple tables
In mSQL's SELECT you can not use * for the fields you want in the
answer if there are two or more tables involved. Note that this
limitation does not reduce the "expressive power" of mSQL
but it is somewhat annoying. Here is an example. Suppose we define
T1 and T2 as:
create table t1 (a int, b int)
create table t2 (b int, c int)
insert into t1 values (1,2)
insert into t2 values (2,3)
Then these queries generate errors:
mSQL > select * from t1, t2 where t1.b=t2.b \g
ERROR : Reference to un-selected table ""
mSQL > select * from t1,t2 \g
ERROR : Reference to un-selected table ""
But explicitly specifying the fields to return works:
mSQL > select t1.a,t1.b,t2.b,t2.c from t1, t2 \g
+----------+----------+----------+----------+
| a | b | b | c |
+----------+----------+----------+----------+
| 1 | 2 | 2 | 3 |
+----------+----------+----------+----------+
mSQL > select t1.a,t1.b,t2.c from t1, t2 where t1.b=t2.b \g
+----------+----------+----------+
| a | b | c |
+----------+----------+----------+
| 1 | 2 | 3 |
+----------+----------+----------+
'Bambi's'
would be entered as
'Bambi\'s'.
Note: This applies when using
msql - the database monitor
program. If you're developing your own application you may have to
escape other characters that are special to the language you're using -
for example perl, C or tcl.
c:\windows\system\
would be entered as
'c:\\windows\\system\\'
When using regular expressions in queries of the form:
SELECT table.column FROM table WHERE table.column LIKE 'regexp'
the following rules apply:
| Use this table for mSQL versions 1.0.13 and below | |||||
|---|---|---|---|---|---|
|
To search for this character |
Use this string |
or this string |
To search for this character |
Use this string |
or this string |
| $ | \\\\$ | [$] | [ | \\\\[ | [[] |
| % | \\% | [%] | \ | \\\\\\\\ | [\\\\] |
| ' | \' | ^ | \\\\^ | [^] | |
| ( | \\\\( | [(] | _ | \\_ | |
| ) | \\\\) | [)] | | | \\\\| | [|] |
| ? | \\\\? | [?] | |||
| Use this table for mSQL versions 1.0.14 and above | |||
|---|---|---|---|
|
To search for this character |
Use this string |
To search for this character |
Use this string |
| $ | [$] | [ | [[] |
| % | [\\%] | \ | cannot be searched |
| ' | \' | ^ | cannot be searched |
| ( | [(] | _ | \\_ |
| ) | [)] | | | [|] |
| ? | [?] | ] | []] |
Note: This applies when using msql - the database monitor program. If you're developing your own application you may have to escape other characters that are special to the language you're using - for example perl, C or tcl.
If you are using mSQL via web interfaces, you may wish to use special HTML characters instead. For example, a single quote can be represented as:
'
A complete list of the HTML coded characters can be found at:
http://www.w3.org/pub/WWW/MarkUp/html-spec/html-spec_13.html
While we're on the topic of regular expressions, this explanation from
Robert Sum
<[email protected]> may shed some light on what
happens inside mSQL: Robert writes:
In mSQL 1.0.x, any time you use LIKE, you use an unusual form of
"regular expressions" which are the way they are because of some
historical implementation decisions that, in retrospect, were,
perhaps, not the right ones. Consequently, mSQL 1.0.x does the
following for a LIKE pattern which is somewhere between globbing
and full regular expressions:
1. it tries to force a match of the whole data string by implicitly
prepending the pattern with the beginning-of-pattern operator ^
and appending the end-of-pattern operator $ to the pattern
(which makes the use/non-use of these operators a little quirky
[see below]),
2. it does NOT allow the ., *, and + regexps (they are plain characters),
3. it does allow character classes using [] (i.e., any single
character within the [] is matched unless the first char is ^ in
which case any single character not in the [] is matched),
4. it does allow alternation using |,
5. it does allow grouping using (),
6. it does allow optional characters or groups using ?,
7. it allows _ to represent any single character (what would
ordinarily be the . above),
8. it allows % to represent any string of characters (what would
ordinarily be the combination .*),
As you can derive from above, the characters
^, $, [, ], |, (, ), ?, _, %, \
are all special in one way or another. There is a way to search for
everything, it just might not be completely obvious. For instance,
using the msql program, one can use
\\_ to match _
\\% to match %
\\^ to match ^
\\$ to match $
\\\\ to match \
Basically, there are three places where \ is used as the escape
character:
1. The msql program (always treats \ as an escape character),
2. The translation process (treats \ as an escape only when
followed by % or _), and
3. The regular expression evaluator (always treats \ as an escape
character).
In the first two examples above, steps 1 and 2 strip backslashes.
In the next three examples, steps 1 and 3 strip backslashes.
Things are actually a bit more consistent than folks realize, I
think. (Note: If you are using Perl or Tcl or some such, then
replace 1. with appropriate activity of that application.)
Furthermore, a recent message asked about
SELECT * FROM doc_info
WHERE foo LIKE 'x' or foo LIKE 'y' or foo LIKE 'z'
and bar LIKE 'a' or bar LIKE 'b' or bar LIKE 'c'
to which I replied, try
SELECT * FROM doc_info
WHERE foo LIKE '(x)|(y)|(z)'
AND bar LIKE '(a)|(b)|(c)'
Well, I forgot about rule number 1. above, which means this
conversion may not be quite right and the original sender may need
to fudge things a bit using either an additional set of parenthesis
to force a match of the whole data string as in
SELECT * FROM doc_info
WHERE foo LIKE '((x)|(y)|(z))'
AND bar LIKE '((a)|(b)|(c))'
or if he really wants a match anywhere within the string
SELECT * FROM doc_info
WHERE foo LIKE '$|(x)|(y)|(z)|^'
AND foo <> ''
AND bar LIKE '$|(a)|(b)|(c)|^'
AND bar <> ''
then this should work, but as noted, one must be careful about the
empty string. (If you want the empty string, just leave out the
not equal clauses.)
For example
insert into foo values ( NULL, 1, 2, 'some text' )
SELECT * FROM my_table WHERE my_field LIKE '[Ss][Oo][Mm][Ee]
[Vv][Aa][Ll][Uu][Ee]'
Sol Katz's <[email protected]>
Object Oriented HTML API includes a C routine
that converts a string into its case insensitive form. You may wish to
use this in any C code that you write. See the section below on
"Contributed Code and Third Party
Applications" Alternatively, you can create an additional field in each table that will hold a single case version of the information you are likely to be searching for.
For perl users, Michael Cowden <[email protected]> has contributed this code example:
The following statement turns mSQL into [mM][sS][qQ][lL]
$string = "mSQL";
$string =~ s/(.)/\[\L$1\E\U$1\E\]/g;
Vivek Khera
<[email protected]>
suggests a simpler method for perl users:
Personally, I use this in Perl, as there is no need to complicate
the regular expression with non-alpha characters.
$string =~ s/([A-Za-z])/\[\L$1\U$1\]/gi;
Rasmus Lerdorf's
<[email protected]>
Personal Home Page Construction Kit includes built
in operations for case insensitive searches by way of its
msql_RegCase(string) command. Version 2 of mSQL will support functions similar to upper() and lower() which will obviate the need for the above.
One possible solution is to use msqldump to create an ASCII dump of the entire database. Then edit this dump file by hand and add the extra field to the CREATE clause. You'll also need to edit each INSERT clause to ensure that the new field is referenced. Once you've modified the dump file, drop and recreate the database using msqladmin and re-populate the new database using the dump file and msql.
This procedure could be automated by a shell or perl script.
Note: Use the -c option to msqldump to ensure that a complete dump of the table is produced.
As an example consider this output from msqldump
#
# mSQL Dump (requires mSQL-1.0.6 or better)
#
# Host: localhost Database: test
#--------------------------------------------------------
#
# Table structure for table 'test'
#
CREATE TABLE test (
name CHAR(40),
num INT
) \g
#
# Dumping data for table 'test'
#
INSERT INTO test VALUES ('item 999',999)\g
...
INSERT INTO test VALUES ('item 0',0)\g
If you wish to insert a field, say "discount", then you will
need to modify the dump file as follows:
#
# mSQL Dump (requires mSQL-1.0.6 or better)
#
# Host: localhost Database: test
#--------------------------------------------------------
#
# Table structure for table 'test'
#
CREATE TABLE test (
name CHAR(40),
num INT,
discount REAL
) \g
#
# Dumping data for table 'test'
#
INSERT INTO test VALUES ('item 999',999,0.0)\g
...
INSERT INTO test VALUES ('item 0',0,0.0)\g
Notice that every insert clause MUST be changed as well as the
table definition.
| Reserved words in mSQL 1.x | |||||||
|---|---|---|---|---|---|---|---|
| < | >= | by | distinct | integer | not | real | update |
| <= | all | char | drop | into | null | select | values |
| <> | and | create | from | key | or | set | where |
| = | as | delete | insert | like | order | smallint | |
| > | asc | desc | int | limit | primary | table | |
| Reserved words in mSQL 2.0B6 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| < | all | by | delete | insert | limit | on | select | sum | update |
| <= | and | char | desc | int | max | or | sequence | table | value |
| <> | as | clike | distinct | integer | min | order | set | text | values |
| = | asc | count | drop | into | money | primary | slike | time | where |
| > | avg | create | from | key | not | real | smallint | uint | |
| >= | avl | date | index | like | null | rlike | step | unique | |
Remember that mSQL reserved words are case insensitive so UPPER case or MiXeD cAsE reserved words are also forbidden in table or field names.
SELECT number FROM table ORDER BY number DESC LIMIT 1
To obtain the minimum value use:
SELECT number FROM table ORDER BY number LIMIT 1
This will only work with mSQL 1.0.9 and above unless you have applied
the unofficial LIMIT patch to earlier versions. See the
mSQL mailing list archives for details on
this patch. (Before searching for this unofficial patch, you should
seriously consider upgrading to the
latest version of mSQL). Note: Rasmus Lerdorf writes:
The LIMIT statement limits the number of records actually
transferred from the server to the client. It doesn't limit the
scope of the search at all in any way. That means that if you are
looking for the maximum value in a table with 30,000 entries, the
query will first build the entire sorted result in memory in the
server, but when it comes time to transferring the result to the
client, it only sends the first item.
In many cases, especially when you have a lot of fields, or long
fields, the time it takes to transfer the data from the server to
the client is actually many times that of the actual search. And
the msqld daemon is tied up and not available to other clients
while it is wasting time sending result records that will never be
used. So, if you do queries and you know you will only be looking
at the first couple of them, you should use the limit clause and
cut down on the amount of useless records being sent across the
socket.
relshow
or
relshow -h host
This will return output similar to:
+-----------------+ | Databases | +-----------------+ | test | | photos | | patches | +-----------------+
relshow dbname
or
relshow -h host dbname
This will return output similar to:
Database = test
+---------------------+
| Table |
+---------------------+
| test_table |
| addresses |
| telephone |
+---------------------+
relshow dbname tablename
or
relshow -h host dbname tablename
This will return output similar to:
Database = test
Table = test_table
+-----------------+----------+--------+----------+-----+
| Field | Type | Length | Not Null | Key |
+-----------------+----------+--------+----------+-----+
| name | char | 40 | N | N |
| num | int | 4 | N | N |
+-----------------+----------+--------+----------+-----+
Programs that were connected to the mSQL database server should be either restarted or have some internal mechanism whereby they notice the server has died and attempt a reconnection.
One possible method for checking the status of the database server would be to examine the return status of the msqlSelectDB() call.
I'm looking for a way to provide the full 25 connections to each of
many mSQL databases running on a single box. Here's an idea, will
it work? or is there a better way?
(korn shell example)
$ export MSQL_TCP_PORT=3000; msqld
$ export MSQL_TCP_PORT=3001; msqld
$ export MSQL_TCP_PORT=3002; msqld
Then connect to the database as follows:
$ export MSQL_TCP_PORT=3000; msql db_a
While the above runs, another user connects:
$ export MSQL_TCP_PORT=3001; msql db_b
David Hughes replies:
Well, sort of. By running 'msql db_a' you are using the local UNIX
socket not the TCP socket so you'd have to use MSQL_UNIX_PORT not
MSQL_TCP_PORT.
The other thing is that you should run 3 MSQL_HOME areas
(/usr/local/Minerva for example). If two of these servers __ever__
access the same database at the same time then you are stuffed.
So, something like
export MSQL_UNIX_PORT=/dev/msql_1; export MSQL_HOME=/Minerva1; msqld&
export MSQL_UNIX_PORT=/dev/msql_2; export MSQL_HOME=/Minerva2; msqld&
and
export MSQL_UNIX_PORT=/dev/msql_1; msql db_a
export MSQL_UNIX_PORT=/dev/msql_2; msql db_b
would do the job.
One suggestion on how to implement this comes from Vivek Khera <[email protected]>.
Vivek writes:
What I do is take some of the fields in the record, tack on a salt
like the current time and generate a hash (either SHA or MD5) of
it. I use part of the the hash string value as the key.
Another solution is provided by Rasmus Lerdorf
<[email protected]>. Rasmus writes:
The issue here is not so much how to generate a key, but how to
ensure it is unique. The way I have done it in the past is to
associate a lock file with each table. Each table has a counter
record. With the table locked, I pull out the current counter
value, increment it, and put it back. Then I unlock the table. This
is not the most efficient way to do it, but it does work nicely for
systems that do not get pounded with queries.
Others have suggested using a timestamp with milli second
granularity. This approach has its pitfalls.
AUTO PRIMARY KEY
This patch is available via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLpatches/msql-auto-pkey.patch.gz
(6329 bytes)
Marcantonio writes:
I have found a quick and dirty method that works for most
situations. In my case, I include in the web form where I insert
something into the database a HIDDEN field whose value is
determined by a webpage hit counter. Any text-based server-side
include hit counter is fine. For instance, in my page I have
something like:
<input type=hidden name=id value="<!--#exec cgi-bin/counter.pl>" >
the ID field is always different, although it may not be sequential.
this didn't require a single line of code because I already had a
hitcounter, and this is supposed to work for many situations.
/usr/local/Minerva/include/msql.h:30: redefinition of `m_row'
/usr/local/Minerva/include/msql.h:32: redefinition of `struct field_s'
This occurs because the mSQL header file msql.h has been
included more than once. To avoid this, apply the following patch to msql.h contributed by Vesa Tuomi <[email protected]>
*** ./src/msql/msql.h.orig Wed Mar 6 09:27:20 1996
--- ./src/msql/msql.h Thu Mar 7 10:29:46 1996
***************
*** 16,21 ****
--- 16,23 ----
**
*/
+ #ifndef __MSQL_H__
+ #define __MSQL_H__
#if defined(__STDC__) || defined(__cplusplus)
# define __ANSI_PROTO(x) x
***************
*** 109,111 ****
--- 111,115 ----
#ifdef __cplusplus
}
#endif
+
+ #endif /* __MSQL_H__ */
and rerun the make install phase of the mSQL installation
procedure. This will remake all the core mSQL applications and install
the modified msql.h file in your installation include
directory. It will also reinstall the core mSQL applications in your
installation bin directory. There is NO need to recompile any
other third party applications.
Most C compilers understand the command line arguments:
-Iinclude_directory
and
-Llibrary_directory
to mean "search the directory include_directory for
include files and search the directory library_directory for
libraries". If you wish to compile a program that will communicate with the mSQL database server you must tell the compiler where the mSQL include and library files were installed. Assuming you installed mSQL in /usr/local/Minerva and you use gcc, the syntax would resemble:
gcc -c -I/usr/local/Minerva/include your_prog.c
gcc -o your_prog your_prog.o -L/usr/local/Minerva -lmsql
Note: Libraries MUST be specified last. Most compilers
will give strange errors if you specify the library paths and libraries
before any .c or .o files. Note: that the mSQL library name has been truncated. The actual mSQL library file name is libmsql.a. However, the compiler (and the link loader) only need the unique part of the file name so the lib and .a components should be removed when passing the library name on the command line.
If you are using an SVR4 version of Unix (such as Solaris 2.x) you may also have to include some networking libraries:
gcc -o your_prog your_prog.o -lsocket -lnsl -L/usr/local/Minerva -lmsql
msqlQuery(dbsocket, "SELECT * from blah");
result = msqlStoreResult();
number = msqlNumRows(result);
msqlFreeResult(result);
DELETE FROM table_name\g
msql-import is bundled with mSQL version 2.x.
In mSQL 2.0 the return value from msqlQuery() tells you the number
of rows "touched" by the query (i.e. deleted for a
DELETE, returned for a SELECT, modified for an UPDATE etc).
http://www.dancooks.com/~jason/w3-msql/w3auth.html
A number of people have contributed additional software that works with mSQL. The contributed software falls into two categories - that developed by David Hughes (mSQL's author) and that developed by others.
Note: Please consult the documentation that comes with each of these applications to determine the licensing obligations that may be involved in their use.
Note: Some of these applications are available via anonymous ftp from the mSQL contributed code directory ftp://bond.edu.au/pub/Minerva/msql/Contrib. There is often a delay of a day or so for newly released files to be moved from the ftp://bond.edu.au/pub/Minerva/msql/Incoming directory to the ftp://bond.edu.au/pub/Minerva/msql/Contrib directory.
ftp://bond.edu.au/pub/Minerva/esl/esl-0.3.tar.gz
(407046 bytes)
ftp://bond.edu.au/pub/Minerva/msql/w3-msql/w3-msql-1.0.3.tar.gz
(54811 bytes)
W3-mSQL version 2.0 is bundled with mSQL 2.x. It uses the
bundled Lite language embedded within HTML
tags. W3-mSQL version 2.0 is incompatible with version 1.x.
More details on version 2.0 of W3-mSQL can be found in the documentation distributed with mSQL 2.x.
More details on Lite can be found in the documentation distributed with mSQL 2.x.
http://ampere.scale.uiuc.edu/~mlevchin/addf
ftp://ftp.ceo.org/pub/ewse-mSQL-apache-demos/apache-msql-demo.1.0.1.tar.gz
(12723 bytes)
If you require a module that allows the Apache httpd daemon to
perform authentication via an mSQL database, grab the file:
http://www.apache.org/dist/contrib/modules/mod_auth_msql.c
(9269 bytes)
http://www.iinet.com.au/~cam/applix.html
#!/bin/sh
# $Id: msqlbckp v. 1.0 1996/6/22 $
# by: [email protected]
#
# usage: msqlbckp [-h host] 'backup directory'
#
# This script creates daily backups of mSQL databases. Input parameters are
# host machine (if msqld is not running on local machine) and the pathname
# of a directory where the backups will be made. The backups will be named:
# {table name}.{day of week}.gz (They are gzipped).
#
# The structure of the backup files are in a format that can be read
# by the 'msql' program to completely restore the database, by first
# dropping the corrupt table, then recreating the table and populating it
# with data (it is required that the database itself already exists, which
# may require the database administrator to use msqladmin to create the
# database in extreme circumstances).
#
# Access is required to msqldump, relshow and gzip.
#
# Set crontab to execute this script at, say, 4 am every day, every other
# day, or however often you want backups of your databases.
#
err( ) {
echo usage: msqlbckp [-h host] 'backup directory'
exit 1
}
case $# in
1)
bdir=$1
;;
3)
if [ $1 = "-h" ]
then
host="-h "$2
else
err
fi
bdir=$3
;;
*)
err
;;
esac
dow=`date '+%A'` # Get the day of the week
# Use relshow to get a list of the available databases, and pare that down
# into a file listing one database name per line...
relshow ${host} | \
sed -n -e '/^....[ \-].*/d' -e 's/ \| //' -e 's/ *\|//p' \
>${bdir}/db.names
# Get each database name from the file for table processing
dbline=1
while [ 1 ] # Do forever until no more db names
do
dodb=`cat ${bdir}/db.names | sed -n "${dbline}p"`
if [ "${dodb}" = "" ] # if no more db names to do
then
break
fi
cf=${bdir}/${dodb}.${dow} # Define current working file
echo "#
# mSQL Dump of Database: ${dodb}
#
# Begin by dropping all tables
#---------------------------------------------
" > ${cf}
# Now add commands to 'drop' each table in the database
relshow ${host} ${dodb} | \
sed -n -e '/^....[ \-].*/d' -e 's/ \| //' -e 's/ *\|//p' | \
awk '($0 !~ /^$/) { print "DROP TABLE", $1, "\\g" }' >> ${cf}
echo " " >> ${cf}
# Dump the database structure and data into the backup file
msqldump ${host} ${dodb} >> ${cf}
# Finally, gzip the file
gzip -f ${cf}
# Next database name
dbline=`expr ${dbline} + 1`
done
rm ${bdir}/db.names # Get rid of temp file
ftp://ftp.vix.com/pub/bind/release/bind-4.9.3-REL.tar.gz
(1682741 bytes)
Chris has also released a beta version of msql_bind that
works with mSQL version 2.x. For more details see:
http://www.seawood.org/msql_bind
http://www.camelot.de/~kvm/progs/sql.tar.gz
(14083 bytes)
http://bauhaus.skiles.gatech.edu/~jharrell
Source code is available via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/Contrib/dbadmin/dbadmin_v1.0.1.tar.gz
ftp://ftp.nerosworld.com/pub/SQL/dbf2sql/dbf2sql-2.2.tar.gz
(18207 bytes)
http://www.hermetica.com
ftp://ftp.mcqueen.com/pub/dbperl
It is also available from any CPAN (Comprehensive Perl Archive
Network) site in the "modules" directory. For more
information about CPAN see:
ftp://ftp.funet.fi/pub/languages/perl/CPAN
The latest blurb describing Alligator's work can be obtained from:
http://www.hermetica.com/technologia/DBI
http://www.homeport.org/~shevett/dbunk.tar.gz
It shows the structure of an mSQL database as well as indicating the number of records in the tables.
It is available via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/Contrib/dbview/dbview.c.lsm
(563 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/dbview/dbview.c
(16933 bytes)
Digger is a Distributed Directory Service for the Internet based on Whois++ technology. For more information about digger send mail to <[email protected]> or have a look at Bunyip's web pages:
http://www.bunyip.com/products/digger
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLEmacs/sql-mode.tar.gz
(6883 bytes)
ftp://ftp.wsc.com/pub/freeware/msql/msql-import-0.1.4.tar.gz
(14281 bytes)
Notwithstanding any bug fixes, this will be the last
"stand alone" release of msql-import that can be
used with mSQL version 1.x. Future versions will be bundled
with mSQL version 2.x.
It is available via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLCGI/msqlc2.zip
(29002 bytes)
and also from:
ftp://ftp.blm.gov/pub/gis/msqlc2.zip
(29002 bytes)
It can be seen in operation at:
http://www.blm.gov/gis/msql/vertical/test2.html
ftp://ftp.ntua.gr/pub/lang/ici/iciMsql.tar.gz
(249077 bytes)
Yiorgos has set up a mailing list for iciMsql. Questions
can be sent to:
[email protected]
http://aiace.lnf.infn.it/~ht/JATE.html
MsqlJava for mSQL version 1.x is available via anonymous ftp from:
ftp://dada.minmet.uq.edu.au/pub
MsqlJava for mSQL version 2.x is available via anonymous
ftp from:
ftp://dada.minmet.uq.edu.au/pub/MsqlJava-1.2.0.tar.gz
(23483 bytes)
For more details see:
http://www.minmet.uq.oz.au/msqljava
ftp://ftp.imaginary.com/pub/Java/database/mSQL-JDBC.tar.gz
(33063 bytes)
For more details see:
http://www.imaginary.com/Java
Sun has just released its Java Servlets API that includes, among other
things, a Java runtime that works from within Netscape servers, similar
to a plug-in. The same servlet runner is available for ISAPI and the
Apache server. With a servlet written in Java and using the Java-mSQL
package cited in the FAQ, you can open a connection to an mSQL database
on the local or remote server, query the DB and return the result as
HTML using an http response object pre-defined in the servlet API.
Best of all, the servlets API is free.
http://jserv.javasoft.com/products/java-server/sdk/index.shtml
It is available via anonymous ftp from:
http://www.koala.ie/jio/dist/jio-1.3.tar.gz
(35104 bytes)
Details of the current release of SIOD can be found at:
http://people.delphi.com/gjc/siod.html
The latest copy of SIOD can usually be found at:
ftp://ftp.std.com/pub/gjc/siod.tgz
(156846 bytes)
or
http://people.delphi.com/gjc/siod.tgz
(156730 bytes)
George has used SIOD as part of a system to:
abstract html tricks from programming tricks by considering html
files, or portions of html files, to be templates or chunks of html
upon which simple substitutions are made
More details of "Chunk HTML" can be found at:
http://people.delphi.com/gjc/chtml.html
http://www.gold-cousins.org/lite_site
Henry Minsky <[email protected]> writes:
<Meta-HTML> is a programming language specifically designed
for working within the World Wide Web environment. Although it is a
genuine programming language, suitable for large-scale symbolic
manipulation, it provides the most commonly wanted Web
functionality as built-in primitives, so you don't have to write
them.
More details (including source code) can be found at:
http://www.metahtml.com
There is also an anonymous ftp site for those of you without web
access:
ftp://ftp.metahtml.com/pub
Brian's original function is available from the mSQL mailing list archives for the month of January 1997.
Jon Hilton <[email protected]> has taken Brian's work and corrected a few bugs and included support for mSQL version 2.x.
Jon's function is available from the mSQL mailing list archives for the month of February 1997. It is also included here in full:
Function export_mSQL()
' Exports the database contents into a file in mSQL format
' IS NOT SELECTIVE! (exports ALL tables)
Dim dbase As DATABASE, tdef As Recordset, i As Integer, fd As Integer
Set dbase = CurrentDb()
' Open the export file
Open "E:\cota\infocom1.txt" For Output As #1
Print #1, "# Converted from MS Access to mSQL "
Print #1, "# by Brian Andrews, (c) InforMate Technologies, 1997"
Print #1, ""
' Go through the table definitions
For i = 0 To dbase.TableDefs.Count - 1
Print #1, "# TableDefs.Count - 1 =" & dbase.TableDefs.Count - 1
Print #1, "# This is table " & i
' Let's take only the visible tables
If ((dbase.TableDefs(i).Attributes And DB_SYSTEMOBJECT) Or
(dbase.TableDefs(i).Attributes And DB_HIDDENOBJECT)) Then
Else
' We DROP the table in case it already exists
' and then create it again
tname = "" & dbase.TableDefs(i).Name
Print #1, "# Access table " & tname
'remove spaces from tablename
For j = 1 To Len(tname)
If j < Len(tname) Then
If Mid$(tname, j, 1) = " " Then
s = Left$(tname, j - 1)
's = s & "" & Right$(t.Fields(j), Len(t.Fields(j)) - i + 1)
s = s & "" & Right$(tname, Len(tname) - j)
j = j + 1
found = True
tname = s
'Exit For
End If
End If
Next j
'restrict tablename to 19 chars
tname = Left$(tname, 19)
Print #1, ""
Print #1, ""
Print #1, "DROP TABLE " & tname & " \p\g"
Print #1,
Print #1, "CREATE TABLE " & tname & "("
' Step through all the fields in the table
For fd = 0 To dbase.TableDefs(i).Fields.Count - 1
'All fields are char at the moment - can be changed
Dim tyyppi As String, pituus As Integer, comma As String
Select Case dbase.TableDefs(i).Fields(fd).Type
Case DB_BOOLEAN
tyyppi = "char (8)"
Case DB_INTEGER, DB_BYTE, DB_LONG
tyyppi = "int"
Case DB_DOUBLE, DB_SINGLE, DB_CURRENCY
tyyppi = "real"
Case DB_TEXT
pituus = dbase.TableDefs(i).Fields(fd).Size
tyyppi = "char (" & pituus & ")"
' Need to leave enough room in date fields for date & time
Case DB_DATE
tyyppi = "char (17)"
' For some reason, DB_GUID doesn't seem to be noticed - 15 is the value
Case DB_MEMO, DB_GUID, DB_LONGBINARY, 15
'Special case fields - specific to one of my projects
If dbase.TableDefs(i).Fields(fd).Name = "Fund Summary " Then
tyyppi = "char (1500)"
ElseIf dbase.TableDefs(i).Fields(fd).Name = "Fund Analysis " Then
tyyppi = "char (3000)"
Else
tyyppi = "text (100)"
End If
End Select
' Don't print the separating comma after the last field
If fd < dbase.TableDefs(i).Fields.Count - 1 Then
comma = ","
Else
comma = ""
End If
' Print the field definition
'remove spaces from fieldname
stuff = "" & dbase.TableDefs(i).Fields(fd).Name
For j = 1 To Len(stuff)
If j < Len(stuff) Then
If Mid$(stuff, j, 1) = " " Then
s = Left$(stuff, j - 1)
's = s & "" & Right$(t.Fields(j), Len(t.Fields(j)) - i + 1)
s = s & "" & Right$(stuff, Len(stuff) - j)
j = j + 1
found = True
stuff = s
'Exit For
End If
End If
Next j
stuff = Left$(stuff, 19)
'mSQL 1 primary key declaration - always on first field
'mSQL 2 - now we need to use CREATE INDEX. Don't bother for now.
'If fd = 0 Then
'Print #1, " " & stuff & " " & tyyppi & " primary key" & comma
'Else
Print #1, " " & stuff & " " & tyyppi & comma
'End If
Next fd
Print #1, ")\p\g"
Print #1, ""
Dim recset As Recordset
Set recset = dbase.OpenRecordset(dbase.TableDefs(i).Name)
' Step through the rows in the table
reccount = recset.RecordCount
If reccount <> 0 Then
recset.MoveFirst
Do Until recset.EOF
Dim row As String, it As String
row = "INSERT INTO " & tname & " VALUES ("
' Go through the fields in the row
For fd = 0 To recset.Fields.Count - 1
Dim is_string As String
is_string = ""
stuff = "" & recset.Fields(fd).Value
Select Case recset.Fields(fd).Type
' For some reason, DB_GUID doesn't seem to be noticed - 15 is the value
Case DB_TEXT, DB_MEMO, DB_GUID, DB_DATE, DB_LONGBINARY, DB_BOOLEAN, 15
is_string = "'"
Case Else
If stuff = "" Then
stuff = "0"
End If
End Select
'**** escape single quotes
x = InStr(stuff, "'")
While x <> 0
s = Left$(stuff, x - 1)
s = s & "\" & Right$(stuff, Len(stuff) - x + 1)
stuff = s
x = InStr(x + 2, stuff, "'")
Wend
row = row & is_string & stuff & is_string
If fd < recset.Fields.Count - 1 Then
row = row & ","
End If
Next fd
' Add trailers and print
row = row & ")\p\g"
Print #1, row
' Move to the next row
recset.MoveNext
Loop
recset.Close
Set recset = Nothing
End If
End If
Next i
Close #1
dbase.Close
Set dbase = Nothing
End Function
Brian writes:
Here are three scripts, the first two are clean ones that break out
tables saved from access in dBaseIII format into a tab-separated
format that a set of database-operator scripts called rdb can use.
The first breaks out .dbf files and the second breaks out .dbt
files which contain the text of variable-length-text memo fields;
the .dbf and .dbt can be joined by the block offset number given in
the text field. These scripts have only been tested on the field
types my database happens to have. Since the first one breaks out
the table definitions it's the obvious candidate to generate table
definitions for msql. The third script is a hack to get data into
msql so I can play with msql, but it's a starting point. The first
two scripts were written to be free of data-dependent bugs, the
third is somehow confused about null fields in Pascal's msql-import
program which I've hacked around to enter the complaining fields as
the text "NULL"; debugging is welcomed. The third script
needs rdb, available from rand.org:/pub/RDB-hobbs. I do MS Access
-> rdb -> msql because I use rdb as a prototyping tool, the
format is trivial to generate and modify, I have a forms-based
emacs front-end to rdb, and the tables compress nicely in rcs.
However, someone may wish to modify these scripts, (a) so that they
generate msql dump files instead of rdb files, thereby bypassing
the msql-import bug and the rdb dependency and probably some data
dependencies with maximum portability, or (b) so that they talk to
the database directly. If so please post diffs. Trigger the new
behaviour by a command-line option so the non-msql-perl behaviour
continues to run under vanilla perl. These programs are gpl'ed.
They are available from the mSQL mailing list
archives for the month of February
1996.
http://petrified.cic.net/MsqlCGI
http://www.dcicorp.com/~scottb/projects/msqlexpire
http://design.delta.net/msqltools
"mSQL Keeper is a collection of perl scripts that enables easy management of an mSQL database. It requires no knowledge of SQL. All database maintenance functions are executed through a GUI front end, and SQL statements are built and submitted to the mSQL engine from mSQL Keeper."
mSQL Keeper currently supports mSQL 1.x databases. Chris has plans for an mSQL 2.x version once mSQL 2.x stabilises.
More details can be found at:
http://www.cglis.com/msql_keeper
ftp://ftp.nerosworld.com/pub/msql/Contrib/mSQLsummary/mSQL_summary.tar
http://design.delta.net/msqltools
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLui/msql-ui.zip
http://iseek.com/biztek/docs/msutil
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLwinapi/winapi.zip
(87211 bytes)
Not included in winapi.zip is an msql.ini file.
Its contents should resemble
[Server]
IP=your.server.host.name
Port=1112
Username=YourUsername
There also appears to be a later version of Dean's work which
includes compiled executables in
ftp://bond.edu.au/pub/Minerva/msql/Contrib/Win-mSQL/winmsql7.zip
(306827 bytes)
Peter Tillemans
<[email protected]> has taken Dirk Ohme's
<[email protected]>
OS/2 port of mSQL and used it to produce a Windows 95/NT version of
mSQL. This port can be obtained via anonymous ftp from:
ftp://bond.edu.au/pub/Minerva/msql/Contrib/Win-mSQL/msql116b-w32.zip
(755355 bytes)
Peter Tillemans
<[email protected]> and Fernando Lozano
<[email protected]> have
created the "mSQL PC Home-Page". Their work can be seen
at:
http://blnet.com/msqlpc
An Enterprise Object Framework (EOF) is an object framework that allows object oriented access to relational databases, where each row is considered an object. Besides a few limitations, it basically makes a relational database look like an OO database to the developer. By means of an adaptor, EOF can be used with virtually any database. The adaptor is responsible to transform the generic OO messages in database specific queries by subclassing a generic adaptor and modifying its behaviour.
It is available via anonymous ftp from:
ftp://ftp.blm.gov/pub/gis/msql_api.tar.gz
(10317 bytes)
An example can be found at
http://www.blm.gov/gis/msql/dbs6.html
ftp://vgl.ucdavis.edu/pub/mSQL/ocxmsql-0.90.zip
(1685189 bytes)
Chin-Jin Phua
<[email protected]> has also developed a mSQL 32-bit OCX
for Windows95 and WinNT. He has only tested it for Visual Basic.
ftp://Bond.edu.au/pub/Minerva/msql/Contrib/mSQLwinapi/msqlocx.zip
(17409 bytes)
ftp://ftp.comed.com/pub/msql/odbc
For more information on Dean's work see:
http://alfred.niehs.nih.gov
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC
The following files give some out of date general information about
the gorta software:
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/gorta.faq
(27450 bytes)
http://www.cyber.com.au/misc/gorta.htm
The gorta software operates as either a 32 bit or a 16 bit ODBC driver.
Different libraries/programs are required for each. For the 16 bit version (Windows 3.1 and 3.11) install:
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/wmsqlrts.zip
(11693 bytes) - Mini-SQL API DLL (Must install First)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/gorta-rts.2.10.0.6.zip
(17014 bytes) - Gorta Driver
For the 32 bit version (Windows 95) install:
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/wmsqlr32-rts.zip
(43193 bytes) - Mini-SQL API 32 bit DLL (Must Install First)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/gorta32-rts.2.10.0.6.zip
(22260 bytes) - Gorta 32 bit driver
The following test tools are available. (These are 16 bit but should
work with the 32 bit drivers).
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/wmslqrs.zip
(20257 bytes) - A DOS/Windows version of the relshow program
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/gortars.1.0.zip
(23756 bytes) - An ODBC version of the relshow program
To install the ODBC driver for the first time requires the use of an
install disk. This installs version 2.10.0.3 of the gorta drivers.
Install from this and then overwrite the gorta dll (in the
windows/system directory) with the one in the gorta 2.10.0.6 zip
file. The install disk installs both the 16 and 32 bit versions of
ODBC.
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/installd.zip
(225019 bytes)
The install disk tends to replace dlls with older versions. This is
a problem if you have ODBC already running. Check the DLLs on the
disk, and make a copy of your dlls before starting.
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/gortars.1.0.zip
(23756 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/gortarts.2.10.0.3.zip
(17480 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/gortasrc.2.10.0.3.zip
(51770 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/installd.zip
(225019 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/msql-1.12-dump.tar.gz
(158282 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/wmsqlrs.zip
(20257 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/wmsqlrts.zip
(11693 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLODBC/wmsqlsrc.zip
(15089 bytes)
http://users.ids.net/~bjepson/freeODBC
Dirk Ohme
<[email protected]> has ported this API to OS/2, SunOS
4.x and Solaris 2.x. This port is available via
anonymous ftp from:
ftp://ftp.nerosworld.com/pub/SQL/iODBC/iODBC-1.00.5.zip
(1023149 bytes)
For more details please send some mail to Ian at <[email protected]>.
Onyx consists of a transaction manager, a shell like 4GL and a Simple Database Transaction Protocol engine.
"Onyx is designed by the Model-View-Controller paradigm, so tables are the model, masks are the views and transactions are the controllers which can be bound to an input field, a menu, function keys or the change of the current record in a cursor."
It is available via anonymous ftp from:
ftp://ftp.uni-bremen.de/pub/unix/database/Onyx/Onyx.2.45.src.tar.gz
(195872 bytes)
http://iseek.com/Cafe
ftp://ftp.nerosworld.com/pub/msql/contrib/mSQLOS2/msql116e.lsm
(1913 bytes)
ftp://ftp.nerosworld.com/pub/msql/contrib/mSQLOS2/msql116e.zip
(871729 bytes)
Dirk has also finished a beta version of mSQL 2.0 for OS/2. This
release is based on mSQL 2.0B6. It is available via anonymous ftp
from:
ftp://ftp.nerosworld.com/pub/msql/contrib/mSQLOS2/msql20b6.lsm
(2228 bytes)
ftp://ftp.nerosworld.com/pub/msql/contrib/mSQLOS2/msql20b6.zip
(1865128 bytes)
This version also requires Dirk's intrinsic ODBC package. For
details regarding this package see the
"ODBC" section above.
ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-category/07_Database_Interfaces/Msql
For more information about CPAN see:
ftp://ftp.funet.fi/pub/languages/perl/CPAN
There is a mailing list for MsqlPerl. Send postings to
<[email protected]>.
Rasmus writes:
PHP/FI is a server-side html-embedded scripting language with
built-in access logging, access restriction, as well as support for
ndbm, gdbm and mSQL databases. It also implements the RFC-1867
standard for form-based file uploads.
The mSQL support is just a small set of functions supported by the
package. A full set of string manipulation, regular expression,
directory and file routines complement the script language.
The source distribution as well as more information is available
at:
http://www.vex.net/php
http://www.homeport.org/~shevett/pts
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLPython/PymSQL.tar.gz
(7581 bytes)
Mark Shuttleworth
<[email protected]> has improved upon Anthony's original
work. Mark's work is available via anonymous ftp from:
http://www.python.org/ftp/python/contrib/Database/mSQL.tar.gz
(6000 bytes)
Jeffrey Ollie
<[email protected]> has updated the Python module
for use with both mSQL version 1.x and 2.x. More
details can be found at:
http://www.ollie.clive.ia.us/python/msql
http://www.enteract.com/isp
According to Damian Hamill
<[email protected]>, the RADIUS server bundled with
Cablenet's
PoP In A
Box ISP server software also supports mSQL based accounting
records. For more details see:
http://www.cablenet.net/cablenet/popinabox
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLREXX
ftp://ftp.qut.edu.au/src/REXXSQL
ftp://ftp.xylogics.com/pub/misc/REXXSQL
You'll need to download the following files:
rxsqldoc13.lsm (1014 bytes)
rxsqldoc13.zip (39170 bytes)
rxsqlmin13.lsm (1001 bytes)
rxsqlmin13.zip (156525 bytes)
rxsqlsam13.lsm (1014 bytes)
rxsqlsam13.zip (25299 bytes)
http://www.saturn.net/~bjepson/simple.html
Brian has begun work on the successor to Simple SQL. Msql-RDBMS
"will eventually be a complete relational database management
system for Msql. It uses HTML forms as an interface". It is
available from:
http://www.perl.com/CPAN/modules/by-module/Msql
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLBase/mSQLBase-1.00.tgz
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLTCL/msqltcl-1.99.tar.gz
(69356 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLTCL/tcl_msql.tar.gz
(7998 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLtime/time_library.tar.gz
(7989 bytes)
tkmSQL requires:
Note: This is a perl Tk module and is NOT to be confused with Tk itself. It can be obtained via anonymous ftp from:
ftp://ftp.wpi.edu/perl5
There is also a FAQ available from:
http://w4.lns.cornell.edu/~pvhp/ptk/ptkFAQ.html
You may obtain tkmSQL via anonymous ftp from:
ftp://ftp.mcqueen.com/pub/databases/dbatools/tkmSQL
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLsgs/sgs-1.0.0.tar.gz
(24216 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLVdb/Vdb.tar.gz
(61670 bytes)
Damian is working on the next release which will include support
for a number of different database engines such as mSQL, mysql,
Sybase and Informix.
http://www.virtuflex.com
ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLVB/msqlcvb.zip
(42192 bytes)
WDB is a software tool set that tremendously simplifies the integration of SQL based databases into the World Wide Web. WDB lets you provide WWW access to the contents of databases without writing a single line of code!
At the moment WDB supports Sybase, Informx and mSQL. However it is relatively easy to port it to other SQL based databases.
For more details on WDB see:
http://www.dtv.dk/~bfr/wdb
Jeff Rowe
<[email protected]> has published a tutorial
on enhancing WDB. Details can be found at:
http://cscsun1.larc.nasa.gov/~beowulf/tutor/index.html
"This is a C web CGI script to examine and modify rows in tables of an mSQL database. You should use Netscape or another browser which supports HTML 3.0 tables."
More details and sample output are available from:
http://www.ua.com/websql
Source code is available from:
http://www.ua.com/websql/websql.tar.gz
(24225 bytes)
http://solaris.tryc.on.ca/files/files.phtml
ftp://bond.edu.au/pub/Minerva/msql/Contrib/XfSQL/xfsql.tar.gz
(74596 bytes)
Xforms is available for a number of different platforms via
anonymous ftp from either of the following locations:
ftp://einstein.phys.uwm.edu/pub/xforms
ftp://ta.twi.tudelft.nl/pub/dv/lemmens/xsqlmenu_1.02s.tar.gz
(14973 bytes)
A pre compiled binary for Linux is also available via anonymous ftp
from:
ftp://ta.twi.tudelft.nl/pub/dv/lemmens/xsqlmenu_1.02LinuxBin.tar.gz
(130945 bytes)
Xforms is available for a number of different platforms via
anonymous ftp from either of the following locations:
ftp://einstein.phys.uwm.edu/pub/xforms
Kees has upgraded Xsqlmenu to support mSQL 2.x. Source code
is available via anonymous ftp from:
ftp://ta.twi.tudelft.nl/pub/dv/lemmens/xsqlmenu.2.01b.tar.gz
(18601 bytes)
ftp://bond.edu.au/pub/Minerva/msql/Contrib/zmSQL/zmsql-2.1.tar
(40960 bytes)
http://www.3dplanet.com/d5.html
http://www.allensflowers.com
http://www.aussie.com.au
http://www.bishop.hawaii.org/bishop/HBS/arthrosearch.html
http://www.Bond.edu.au/Bond/General/MParse/mparse.html
http://www.borsen.dk
http://www.CanadaIT.com
http://www.ibase.org.br/~cinemabrazil
http://www.consecol.org
http://www.cyberclip.com
http://www.dmxmusic.com
http://www.mal.com/~dgymer/gizmo/music.html
http://www.imec.be/europractice/europractice.html
Their home page can be found at:
http://ewse.ceo.org
http://www.first-byte.com
http://www.govcon.com
http://cafe.sdc.uwo.ca
http://www.iqnow.com
http://www.vex.net/isp
http://www.kelloggs.com
http://KidsHealth.org
http://www.callnet.com/~matt
http://www.rose-hulman.edu/~allard/Mondo-DB/oindex.html
http://www.nerosworld.com/realestate/
http://www.nerosworld.com/business/
http://www.nerosworld.com/tradingpost/
http://www.nerosworld.com/fstop/
http://www.nerosworld.com/nero/zipcode.htm
http://www.nerosworld.com/romancing_the_web/
http://www.itribe.net/netcal
http://www.newshare.com
http://www.nc-insurance.com
http://www.opc.on.ca
http://www.paulinasprings.com
http://www.tp.umu.se/TIPTOP
http://www.qms.com/www/faq
James has made the source code available via anonymous ftp. It can
be downloaded from:
ftp://ftp.qms.com/pub/mktg/outgoing/SupportBase.tar.gz
(5509 bytes)
http://idea.exnet.iastate.edu:8080
http://www.railwayex.com/
http://www.synthcom.com/cgi-bin/gear
and the source code can be obtained via anonymous ftp from:
ftp://ftp.synthcom.com/pub/stuff
http://w3.e-sense.net/e-sense/Experimental/Virtu
http://www.webventures.com.au
http://research.ivv.nasa.gov/projects/WISE/wise.html