Mailing List Archive


Back to the month index Back to the list index
James E. Harrell (james_harrell@mindspring.com)
Mon, 12 Apr 1999 20:47:14 -0400 

Message-ID: <37129412.40252487@mindspring.com>
Date: Mon, 12 Apr 1999 20:47:14 -0400
From: "James E. Harrell" <james_harrell@mindspring.com>
Subject: Re: un-enforced "unique" indices

Here is a test example. The idea is that there are several "groups"
defined. Each group can have several categories, but all categories
within a group must be unique. This is enforced by the unique index on
(groupID, category_name). This also allows two different groups to have
categories by the same name.

Here's the script to create the database:

==========================================================

#
# mSQL Dump (requires mSQL 2.0 Beta 5 or newer)
#
# Host: localhost Database: sample
#--------------------------------------------------------

#
# Table structure for table 'categories'
#
CREATE TABLE categories (
  category_name CHAR(30) NOT NULL,
  categoryID INT,
  groupID INT
) \g

CREATE UNIQUE INDEX idx_catnam_grpid ON categories (
        category_name,
        groupID
) \g

CREATE UNIQUE INDEX idx_catid ON categories (
        categoryID
) \g

CREATE SEQUENCE ON categories STEP 1 VALUE 15 \g

#
# Dumping data for table 'categories'
#

INSERT INTO categories VALUES ('Test1',10,1)\g
INSERT INTO categories VALUES ('Test',1,1)\g
INSERT INTO categories VALUES ('Test2',13,1)\g

==========================================================
Here's the msql example that breaks the database:
==========================================================

[xxx]$ msql sample

Welcome to the miniSQL monitor. Type \h for help.

mSQL > select * from categories\g

Query OK. 3 row(s) modified or retrieved.

 +--------------------------------+------------+----------+
 | category_name | categoryID | groupID |
 +--------------------------------+------------+----------+
 | Test1 | 10 | 1 |
 | Test | 1 | 1 |
 | Test2 | 13 | 1 |
 +--------------------------------+------------+----------+

mSQL > insert into categories values ('Test2',200,1)\g

ERROR : Non unique value for unique index

mSQL > update categories set category_name='Test2' where categoryID=1\g

Query OK. 1 row(s) modified or retrieved.

mSQL > select * from categories\g

Query OK. 3 row(s) modified or retrieved.

 +--------------------------------+------------+----------+
 | category_name | categoryID | groupID |
 +--------------------------------+------------+----------+
 | Test1 | 10 | 1 |
 | Test2 | 1 | 1 |
 | Test2 | 13 | 1 |
 +--------------------------------+------------+----------+

mSQL >

"James E. Harrell" wrote:
>
> Having a problem with unique indices... The index will enforce UNIQUE
> when you attempt to insert *new* data, but when you update current data
> in an indexed field, it allows non-unique data to be created... any
> suggestions?
>
> Basically, I'd like the database to kick back the update as invalid to
> avoid doing the unique check manually. That's part of why unique indices
> exist, no?
>
> Table looks something like:
> category_name char(30)
> categoryID int
> groupID int
>
> The create index clause looks like this:
> CREATE UNIQUE INDEX idx_cid on categories (categoryID)
> CREATE UNIQUE INDEX idx_cid_gid on categories (category_name, groupID)
>
> Shouldn't this allow duplicate categories ONLY if the groupID is
> different? In other words, several different groups can have categories
> by the same name. But a single group can not have 2 categories by the
> same name.
>
> Version Details :-
>
> msqladmin version 2.0.8
> mSQL server version 2.0.8
> mSQL protocol version 23
> mSQL connection Localhost via UNIX socket
> Target platform Linux-2.0.29-i586
>
> Configuration Details :-
>
> Default config file /home/jharrell/msql/msql.conf
> TCP socket 1114
> UNIX socket /home/jharrell/msql/msql2.sock
> mSQL user jharrell
> Admin user jharrell
> Install directory /home/jharrell/msql
> PID file location /home/jharrell/msql/msql2d.pid
> Memory Sync Timer 30
> Hostname Lookup True
>
> ps: the reason this message is cc'd to bambi- I haven't seen any replies
> to a recent question I posted; don't know if the messages are getting
> through or not!
From braztoy@matrix.com.br Mon Apr 12 20:45:03 1999
Received: from smtp.fns.matrix.com.br (smtp.fns.matrix.com.br [200.202.16.3])
        by services.bunyip.com (8.8.5/8.8.5) with ESMTP id UAA29881
        for <msql-list@services.bunyip.com>; Mon, 12 Apr 1999 20:44:57 -0400 (EDT)
Received: from internet (dial-0-126.fns.matrix.com.br [200.202.15.126])
        by smtp.fns.matrix.com.br (8.9.3/8.9.3) with SMTP id VAA13605
        for <msql-list@services.bunyip.com>; Mon, 12 Apr 1999 21:46:25 -0300 (EST)
Message-Id: <3.0.1.32.19990412214127.00710e4c@home.matrix.com.br>
X-Sender: braztoy@home.matrix.com.br
X-Mailer: Windows Eudora Light Version 3.0.1 (32)
Date: Mon, 12 Apr 1999 21:41:27 -0300
To: msql-list@services.bunyip.com
From: Brazilian Toy Informatica Ltda <braztoy@matrix.com.br>
Subject: how can start....
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by services.bunyip.com id UAA29881

Hi friends,

Where can i put the commands to start msql at boot time of linux (RH 5.1),
as a specific user ???
Without do any login by any user after boot of machine.

Can i start by this way two diferent process (xxx&) each with a diferent
user ?

(sorry by the bad english write)

Thanks for all,

Leonardo

----------------------------------------------------------
Brazilian Toy Informática Ltda.
http://chat.prodau.com.br
----------------------------------------------------------
From jcastagnetto@hotmail.com Mon Apr 12 21:19:36 1999
Received: from hotmail.com (wya-lfd91.hotmail.com [207.82.252.155])
        by services.bunyip.com (8.8.5/8.8.5) with SMTP id VAA00556
        for <msql-list@services.bunyip.com>; Mon, 12 Apr 1999 21:19:35 -0400 (EDT)
Received: (qmail 57267 invoked by uid 0); 13 Apr 1999 01:19:04 -0000
Message-ID: <19990413011904.57266.qmail@hotmail.com>
Received: from 137.131.240.50 by wy1lg.hotmail.com with HTTP;
        Mon, 12 Apr 1999 18:19:04 PDT
X-Originating-IP: [137.131.240.50]
From: "J. M. Castagnetto" <jcastagnetto@hotmail.com>
To: bambi@Hughes.com.au, msql-list@services.bunyip.com
Subject: Re: http_referer
Date: Mon, 12 Apr 1999 18:19:04 PDT
Mime-Version: 1.0
Content-type: text/plain

The "warning" below is just so there is no confusion, and Hughes
support does not get swamped with PHP questions, as per you explicit
request in an e-mail from some time ago.

>> [Warning PHP3 only answer]
>
>[Warning : generic answer ;-)]
>
>
>> This will give you a list of all the variables you script has
access
>> to, when called via yous web server.
>
>You could naturally do with with a 2 line bourne shell script
installed as
>a cgi.
>
> #!/bin/sh
> echo
> set

Well, yes and no. This will give you some of the variables available
from inside PHP. When using PHP as a module of Apache, you have access
to extra information about the server status and information. You can,
for example, check whether the user has aborted a connection, or
change the script timeout, so it will not run forever, etc.

You can also access the GET, POST variables using the associative
arrays named: $HTTP_GET_VARS and $HTTP_POST_VARS, cookies are simply
accessed by their names. GET and POST variables can also be accessed
via their names (i.e. $foo, $name, etc.), but accessing the array
makes it possible to write general parsing scripts.

Also, when using IMAGE SUBMIT variables, $sub_x and $sub_y are defined
w/ the coordinates of the user click (equivalent to sub.x and sub.y).
You can also access part of the URI parsing inside Apache, and use
functions to obtain info on URI, and even post internal apache notes.

>
>You need the echo to mark the end of an empty set of headers. This
will
>list all the environment variables that your web server has made
available
>to you via the CGI interface.
>
>
>
>Bambi
>..
>

Regards.
=====
Jesus M. Castagnetto - jcastagnetto@hotmail.com
Pilot stuff & etc.:
  http://www.geocities.com/ResearchTriangle/Lab/1059/
LAB: http://www.scripps.edu/research/metallo
Metalloprotein DB: http://metallo.scripps.edu/

_______________________________________________________________
Get Free Email and Do More On The Web. Visit http://www.msn.com
From raimi@cs.tu-berlin.de Mon Apr 12 21:55:55 1999
Received: from mail.cs.tu-berlin.de (root@mail.cs.tu-berlin.de [130.149.17.13])
        by services.bunyip.com (8.8.5/8.8.5) with ESMTP id VAA01265
        for <msql-list@services.bunyip.com>; Mon, 12 Apr 1999 21:55:54 -0400 (EDT)
Received: from cerberus.cs.tu-berlin.de (raimi@cerberus.cs.tu-berlin.de [130.149.31.113])
        by mail.cs.tu-berlin.de (8.9.1/8.9.1) with ESMTP id DAA27736
        for <msql-list@services.bunyip.com>; Tue, 13 Apr 1999 03:55:00 +0200 (MET DST)
Received: (from Received: (from raimi@localhost)
        by cerberus.cs.tu-berlin.de (8.9.1/8.9.0) id DAA08494;
        Tue, 13 Apr 1999 03:54:59 +0200 (MET DST)
Date: Tue, 13 Apr 1999 03:54:59 +0200 (MET DST)
From: Raimi <raimi@cs.tu-berlin.de>
To: Multiple recipients of list <msql-list@services.bunyip.com>
Subject: Lite wishlist and more
Message-ID: <Pine.SOL.3.91.990413034327.8258D-100000@cerberus.cs.tu-berlin.de>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

Hi all!

I followed the thread where some of you made wishes for lite enhancements.

It seems pretty much useless to me because you want to invent the wheel
again. There is a powerful scripting language that features everything
you want and has an excellent interface to mSQL (and other DBs).
For you who didn't guess: perl.
It's available for every platform, is GNU, is well documented and very
professional.

Anyway, I think enhancements should be done in the DB engine itself. I'd
would like to see some operations one can do on tables. For example:
Applying simple math on numerical fields (atomic and synchronized).
My specific problem: I want to add something on integer fields from a
 SQL query.
If this is possible already: Where did I miss something?

If you still want to enhance lite and the w3 stuff consider supporting
some more webservers. I don't like apache very much and therefore run my
application from a perl FastCGI within a Roxen Challenger Webserver.
I guess the performance is equal to anything patched inside an apache.

Ok, I hope I did not upset too many of you...
... and Bambi: I really like mSQL for it's speed and size.

Have a nice week,
 _ __
| ) | "What do you get if you * ** |
|\aimund multiply 6 by 9?" * * * |
| | **** *
   \_/acob * *** O
   
             **** Remember: 21 is just half the truth ****