Compound indexes

[Ruslan Zasukhin]

on 6/21/03 0:24, Bryan McCormack at bmccormack at artsystems.com wrote:

Hi Bryan,

> Could someone clarify and provide an example of how you're creating
> compound indexes with Valentina?  An example of how I would do this on
> other SQL databases would be something like:
> 
> CREATE TABLE tblCustomerInvoices (
> CustomerInvoiceID ULONG NOT NULL UNIQUE INDEXED,
> CustomerID ULONG INDEXED,
> SalespersonID ULONG INDEXED,
> InvoiceID ULONG INDEXED,
> Notes TEXT(255)
> );
> 
> CREATE INDEX idxCustomerSalespersonInvoice
> ON tblCustomerInvoices (CustomerID, SalespersonID, InvoiceID);


> In this example, I've been using the properties "NOT NULL UNIQUE
> INDEXED" together to form, in essense, a Primary Key.

Note, that Valentina do not even use idea of PRIMARY KEY.
In Valentina 2.0 this will be added of course to SQL for compatibility with
RDBMS, but Valentina will just ignore this info. At least on internal level.

> Then, I want an individual index on the fields CustomerID, SalespersonID and
> InvoiceID.

No problems.

> But how do I create a compound index from more than one field as in the
> "CREATE INDEX..." syntax?  I've looked through previous posts and have
> seen that using BaseObject methods is the way to accomplish this, but am
> unclear on how.  Or am I wrong there, too?

With Valentina you need to use virtual field or as we name it -- BaseObject
method. 

In SQL Server 2003 Microsoft also have add this feature -- calculated
fields.

Calculated fields are more powerful than CREATE INDEX,
Because you get the same index file by formula,
And you also can use VALUE of that field.

A BaseObject method can have TYPE the same as normal fields.
In your case you join 4 ULONG values. So you get 12 bytes key.
As you know there is no such numeric type, so you need to use String for
such key.

In case you'd need join only 4 ULONG fields you could use method of type
ULLONG and build its value using bit operations:   (v1 << 32) + v2

In your case you need do:
(btw, in ValentinaSQL.pdf you can see that Valentina for command CREATE
TABLE have extension METHOD )

 CREATE TABLE tblCustomerInvoices (
 CustomerInvoiceID ULONG NOT NULL UNIQUE INDEXED,
 CustomerID ULONG INDEXED,
 SalespersonID ULONG INDEXED,
 InvoiceID ULONG INDEXED,
 Notes TEXT(255)
 
 idxCustomerSalespersonInvoice STRING(12) INDEXED
                  METHOD('concat(CustomerID, SalespersonID, InvoiceID)')
);


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------