INDEX versus TAG 5

[SitesMasstec]

Hello colleagues!

I would like to have your opinion about INDEX clause when opening a file (free table only).

I have the following in a PRG file to add a new record, update or delete records:

SELECT 1
USE CLIENTES INDEX INDCCODI,INDCSTAT,INDCNPOP,INDCNOME,INDCCCGC,INDCCCPF,INDCCIDA,INDCMAIL,INDCDPRO,IND00001,IND00002,IND00003,IND00004,IND00005,IND00006,IND00007,IND00008,IND00009

This PRG file has evolved since FoxPro DOS, and now, after someone in this forum told me to use TAGs in the file so I do not need to use INDEX.

I have a lot of indexes for this file because in a report I need to order records by INDCCIDA+IND0003.

For another report: USE CLIENTES INDEX INDCNOME, or USE CLIENTES INDEX INDCCODI

Othe report uses USE CLIENTES INDEX IND00005, and so on.

I think it is an absurdity to have all this indexes opened when changing the file (add, exclude or delete records).

Can I get rid of all this indexes, and use TAG instead?

Thank you,
SitesMasstec

 

[TamarGranor]

Thanks for the feedback, @SitesMasstec. I just took a quick look and I see that in introducing concepts early in the paper, I failed to make the point that VFP doesn't require a DBC for you to still consider your set of tables to be a database or that almost everything in the paper applies to free tables, too.

Good feedback. Sometimes, as a writer, when you know your material well, you don't notice what you're assuming of the reader.

Tamar

 

[Chris Miller]

...for you to still consider your set of tables to be a database...

So you talked of a databse in the loose sense of a collection of tables?

I always think it's funny that the long term for DBF is database file, I think it even stems from dbase to mean dbase file and, well, dbase is just short for database. But it's a table file, indeed. Nevertheless the term datbaase therefore alrady refers to a single table, and if you mean the structural level of a root object of tables, then you would need to refer to a database container (DBC) and any article that talks of databses without the addition term container should be considered to talk of databases in the non VFP specific sense of DBCs.

So, I'm fine when you don't explicitly mention you don't mean DBFs, especially for readers that started using VFP in the early versions, maybe even from version 1, they should not consider database to mean DBC, really. Newer users that started later with VFP6 or higher, they would not know this double meaning of database regarding foxpro files and file extension meanings. To give them a precise context it would be worth adding that SQL and the newer indexes file type of CDXes also apply to free or legacy DBFs. I'm not so sure legacy 2.5/2.6 DBFs could have CDXes, too, but I think even if they were not available in legacy Foxpro IDE itself, a CDX is always completely independent on the DBF format, the index structure depends only on the fact that each record has a recno, which is the information an index tag of a CDX provides when seeking for a value, and a record number is the oldest concept of DBFs of any type, the concept of strictly same length records is the basis of fast record access by being able to determine the file offset by record number and straight away read from that offset.

I'm going off topic. Anyway, I still consider it to not be a problem of not explicitly defining terms, though in fundamental articles as introduction to SQL it is good practive to have a glossary of terms, as a new topic always comes with a ton of terms about that topic that a newbie has to learn before or at least while diving into the topic.

Chriss

 

[Steve Meyerson]

There's VFP and there's everyone else. When I hear the term "database" used by a non-VFP or on the news, I have no way of knowing if they really mean a "table" (as in VFP) or a collection (container) of tables - they almost never specify (nor does it usually matter).

So I don't see that uncertainty of those expressions going away for a very long time.

Just my opinion.

Steve

 

[Chris Miller]

Well, in every other DBMS except VFP a database means a database "container", the word container is not even necessary. A database server serves databases, because that's the level you connect to, a database. And then you query the tables of that database. VFP is the only DBMS I know that has this double meaning of database. Nevertheless for people wh only know FoxPro and know it from beginning, the term could actually be stronger connotated with a single DBF table file, whereas the most common meaning of database is that of an organizational level on a databsae server that contains multiple tables, no matter of whether the tables are in a single database file or separate or not.

Any server datbase system is not only differing from VFP by having a service you first connect to, they also have system tables that store meta informations about the databases, tables, views, etc. and so a database is usually a record in such a system metadata table to which all tables, view, stored procedures, etc. are related.

The way Tamar said "your set of tables to be a database" she used the term in the more general meaning of the whole DBMS world to mean "set of tables", which you can also organize in a directory in the Foxpro world. The only need for a DBC arieses, if you want to use some features of VFP that are stored in there, stored procedures, for example, views, then more specific features like longer field names. But neither indexes nor SQL require a DBC file. So even though Tamar meant the more general meaning of database as a set of tables, not single tables, that doesn't limit it to DBFs part of a DBC.

You always remove any doubts about that if you just read the help about things like how to create an index and the syntax of an SQL-Select. The latter tells you that the table you query can be specified as a DBF filename, you don't have to use a tablename as stored in a DBC, so that alone tells you SQL-Selects are not limited to DBC tables, they can target DBF files whether they are recorded in a DBC or free.

Indeed it adds to the funny connotation of a DBF being a database, because if you call a single table a database, either your needs of structured data are very low and it's enough for you to have a list of records, or you have no idea how to better structure all your data so it finally can be called a database.

Last not least what can add to the confusion is that database often also is used for short of database system, talking of databases of several vendors or types of databases liky NoSQL vs relations databases means the systems, not the actual databases that could most generally be defined not as "set of tables" but as roof structure of all data that belongs to it, and that can even just be a directory.

Chriss

 

[Mike Lewis]

The habit of referring to a table as a database can be traced to Wayne Ratliffe in 1978. That's when he created the Vulcan system, which is a direct ancestor of VFP.

Ratliffe made a lot of decisions which might seem strange to us now, many of which we are still living with. But you must remember that he was developing this system on his own, in his spare time, and that it was designed to run on an 8-bit processor with a target RAM of 48K (K as in kilobytes; that was the entire RAM - no virtual memory of any kind in those days).

The original idea was that there would be a single - or "primary" - table. So why not call that a database (and hence the file extension DBF ((= "database file"))?. In order to support a rudimentary one-to-many model, he added support for a second table. And that was the limit. If you needed to open a third table, you had to close one of the other two.

That use of the term database survived right thought dBASE, FoxBase, FoxPro 1.x and 2.x and Clipper. It was only in VFP 3.0 that the term became (correctly) to be used to mean a collection of tables. Clearly, old habits linger on.

Sorry to bore you with all this. But in the words of George Santayana, "Those who cannot remember the past are condemned to repeat it."

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Those who cannot remember the past are condemned to repeat it.

I wonder how this is an important advice in this context. There's nothing wrong in repeatedly using databases for data centric applications. We're also not actually struggling with this outdated use of the term, indeed if you have that on your mind you would understand that SQL applies to "databases"="one table", too.

So, it's actually the more up to date and general term of database that made SitesMasstec imply this only works on DBC tables.

And indeed the term database file also contains file, so it isn't stating a database is 1 file, it's stating that a DBF is one of most generally many files that all combined can be called a database. And calling a DBF a database (stripping off the term file) is just the same shortening that we do when we talk of database but really mean database system.

Well, well, I think it's hard to grip why I think it's not Tamars fault alone, but I think we could agree that the existence of multiple meanings of the same term causes doubts and misunderstandings. Maybe a good motivation to once again write an introductory article about SQL and usage in FoxPro and be strict with terms used and what they refer to in the context of VFP, specifically.

And picking up my thread of discussion of how the most significant term of a topic attracts multiple meanings, just look at other worlds, like cooking, which is a roof term including baking and any other method of preparing food, too. Like preparing a salad. And even in the prefession of chefs there are specifc terms for positions like a pastry chef of confectioner but is there a position for a chef that prepares salads? Well, I looked it up and learned a Garde manger would be responsible for cold meals, mostly, like salads and, well look into

https://en.wikipedia.org/wiki/Garde_manger

for the details.

Nevertheless, it's a bit like a curse, isn't it? If you think about how such roof terms get watered down with multiple meanings they gain when you dive deeper into a topic? When you learn something on your own from reading articles or books, even those from the most renowned experts, you're responsible about checking your understanding. And being sure about something is your enemy, actually, without verifying it.

Chriss

 

[SitesMasstec]

To avoid confusion, I think the IT industry should have adopted in the first days of databases (but now it is too late):

1) Databank: a container with more than one table (in the VFP case, SOMEFILE.DBC)
2) Database (=Table): just one file of data (ok, two or three related, in the VFP case, SOMEFILE.DBF, SOMEFILE.FPT, SOMEFILE.CDX)

So, a Databank could have some databases (or tables).


Thank you,
SitesMasstec

 

[Chris Miller]

Well, that's also not a good way of telling the differences and calling a table a database is against all odds a bad choice. A Database server is not a table server, it's a server of databases and more generally speaking data, you also rarely need just one table in a query, because in normalized databases most real world lists you need will join two or more tables. So using databsase to mean a table is really a bad choice.

If you would need to change something in the past it's that unfortunate use of "database file", and the nature of humans to shorten things, i.e. not say database when they actually mean database server or (R)DBMS, or say database when they mean database file, if you'd accept that usage at all, because it's clearly better to call a table a table. And how all these things are organizeed in files is of no importance at all, if a table has one file or 10 or all tables of a whole database or even all databases a database server manages are written into just one file, that's the concern of the layer that persists data on a hard drive and only the concern of a developer, if he needs to manage such files or - just in the special case of VFP again , there is no other DBMS I know that does that - when your SQL engine actually allows you to act on files.

Chriss

 

[Mike Lewis]

And just to confuse the issue even more ...

The concept of a database (or, if you prefer, a "databank") as some sort of container for one or more tables - that might work for a relational database. But not all databases are relational. For example, there is the hierarchical database, which does not have the concept of tables at all. (At least, I think that's right. Someone will correct me if I'm wrong.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

That's right, a datbase mainly is about data, and I've looked up datbase history.

Of course, that starts even before the computer, but when we talk of the first database systems, the term was coined in the 60s, far before Wayne Ratliffe coined the term database file for the DBF file format.

The relational data model is based on mathematical terms that go back a long way. The earliest reference within

https://en.wikipedia.org/wiki/Relation_(mathematics)#Definition

is to an article from 1918 and sets were a topic in math far longer already. I see set theory was formalized in the way we use until today in the 1870s by Cantor and from math I think I heard about conjectures about sets from far earlier than that, too.

Well, I'm not a historian, but no matter if you see a database from the perspecitve of relational database systems, as hiararchical data, and even more recent systems like document stores and more, the term database early on was already meaning an overarching strcuture of data, while a table, no matter if it's only part of some databases, was always defined as one of many subsets of data of a database.

I don't find an explicit source and first usage of the term table, but It is mentioned in

https://www.researchgate.net/publication/298332910_History_Of_Databases

that Codd, when he designed his concept of the relational database system, was talking of instance - the physical table with rows and colummns - and schema, the logical structure, including ... the type of each column. And those two terms are only the technical terms, Codd is much more known for the term of relation, but as far as I get it from that researchgate source Codd didn't only separate the schema from an instance of a table but also relations and the relational model from the technical structures.

So, Mike, as always a sound voice pointing out that everything has more aspects, it's true to say that the explanation of database as set of tables is far too specific, but it's still completely absurd to talk of a table as a database and associate the term database as kind of synonym for a table. It's describing what it literally says when you split it into it's two parts, it's a base of data, the fundamental structure that holds data, in whatever form.

Chriss

 

[SitesMasstec]

If I use these commands in a PRG file, I can see the two indexed fields when I open a table to modify it:

INDEX ON CCODI TAG CCODI
INDEX ON CNOME TAG CNOME
INDEX ON CCIDA+CCCEP TAG ENDER

How can I see all indexed fields, including the compound index, like the ENDER tag (CCIDA+CCCEP)?


Thank you,
SitesMasstec

 

[Chris Miller]

You click on the tab "Inbdexes".

Chriss

 

[SitesMasstec]

Yes, Chris, but it doesn't show all information:

In IND00007 Expression it should display:
CCIDA+CBAIR+CCCEP+CSTAT+STR(CCODI,4)

Well, anyway it's better than nothing.

Thank you,
SitesMasstec

 

[Chris Miller]

Click into the row your interested in...

Then you'll see buttons[...} for Expression and Filter, click on the one after the cut off expression.
You'll get into the expression build, that'll show the full expression.

There you could also change it.

You're again just clicks away, could you explore Foxpro yourself a bit. You're on Windows, the most natural way to find out something about anything in Windows is clicking on it, if not right clicking it.

Another way to get any VFP dialog explained:
1. Have a dialog active, like the table desginer
2. Press F1
=> The help will show the topic about that dialog, in case you do this for the table designer when the indexes tab is active you get to the topic "Indexes Tab, Table Designer" and there you'll see:

Expression
Specifies the index expression, such as a field name. Click the button to create or edit an expression in the Expression Builder Dialog Box. An expression can be up to 240 characters.

So you even learn something about limitations of expression lengths.

Other ways to see the expression of an index:
In the command window type SET ORDER TO and intellisense shows all index tag names with their expression:

Last not least, get going with learning about the nex CDX index tag types by learning the language functions and commands related to it, like the KEY() function. For the active workarea set to some order the expression of that current index tag is given by KEY() without parameters, you can inspect all tags with the function ATAGINFO(), which creates an array of all tags and their keys (expressions).

Chriss

 

[Chris Miller]

By the way, after using functions of VFP, which create an array filled with informations, besides programmatically accessing the array elements, you can alsway s make use of the debuggers locals or watch window to expand an array and see all content of it:

The locals in the first place lists all variables available, and in case of arrays (and also collections) it displays a plus left of the name, that's to expand the array and see all elements of it:

This, for example, are all the tagss of the customers table of the northwind sample database.

It's really all about interactivity to get forward. Be more interactive with your computer, in general, SitesMasstec.

Chriss

 

[Chris Miller]

SitesMasstec,

I'd like to just add some encouragement to you, because you did a lot of things right, your code example was on point, it's also very understandable you have questions about something you learn and use for the first time and you were not shy asking them. So well done, in many aspects.

You even got the term "database" quite correct, your self-imposed restriction of CDXes is something you could have seen by simply trying INDEX ON field TAG tagname on a legacy/free table. But your self-imposed restriction also was coming from taking the term database too strict in the sense of VFPs DBC, even VFP is not that strict on itself if you take the hint of a detour of someone accessing VFP data without having VFP but only the ODBC driver or OLEDB provider. Those drivers/propviders in general require you to make a connection and the most often used connection is not only to the server itself, but to a database, which is also the case for VFP, using both the ODBC driver or the OleDB provider you can choose between two major categories:
1. connect to a DBC
2. connect to a directory of free tables
Both categoreis are usable on equal terms, nothing is preferred from that point of view.

It's quite a detour to think of database as a term meaning both, especially as you have native access to data in VFP and only need ODBC/OleDB if you look at VFP data from outside of VFP, but just don't take any limitation for granted, verify it yourself, it's indeed just trying INDEX ON that would have told you the undoubtable truth about what can be done and what not.


Chriss

 

[SitesMasstec]

Yes, Chris, I got it, following your instructions in your post.

As part of my application documentation, I'd like to print it, but I could not resize the index fields, so I did by hand:

Also, I have a PRG for reindexing it, so it fits well as a documentation, too:

SELECT 1
USE CLIENTES
DELETE TAG ALL
INDEX ON CCODI TAG CCODI
INDEX ON CSTAT TAG CSTAT
INDEX ON CNPOP TAG CNPOP
INDEX ON CNOME TAG CNOME 
INDEX ON CCCGC TAG CCCGC
INDEX ON CCCPF TAG CCCPF
INDEX ON CCIDA TAG CCIDA
INDEX ON CBAIR TAG CBAIR
INDEX ON CMAIL TAG CMAIL
INDEX ON CDPRO TAG CDPRO
INDEX ON CCIDA+STR(CCODI,4) TAG IND00001
INDEX ON CCIDA+CNOME TAG IND00002
INDEX ON CCIDA+CBAIR+CCCEP+STR(CCODI,4) TAG IND00003
INDEX ON CCIDA+CBAIR+CCCEP+CNOME TAG IND00004
INDEX ON CCIDA+CBAIR+CCCEP+CNPOP TAG IND00005
INDEX ON CSTAT+STR(CCODI,4) TAG IND00006
INDEX ON CCIDA+CBAIR+CCCEP+CSTAT+STR(CCODI,4) TAG IND00007     && parece que não é usado
INDEX ON CCIDA+CNPOP TAG IND00008
INDEX ON CCIDA+CSTAT TAG IND00009

I agree with Mike, what you posted as code examples is a good way of using the index tags, including a way to recreate them. Just add that you would DELETE TAG ALL before recreating the indexes.

In a previous post, you advised to use DELETE TAG ALL before reindexing (above). Is it really mandatory?


Thank you,
SitesMasstec

 

[mm0000]

One advantage of creating an idx vs cdx index is that the table does not have to be used exclusively for creating an idx index. So if a table is being shared and you need to create a temporary index for a report or any other reason you could create a idx index and continue. Creating an idx index does not require the table to be used exclusively while creating a cdx index does.

 

[Mike Lewis]

One advantage of creating an idx vs cdx index is that the table does not have to be used exclusively for creating an idx index. So if a table is being shared and you need to create a temporary index for a report or any other reason you could create a idx index and continue. Creating an idx index does not require the table to be used exclusively while creating a cdx index does.

That's all true. But, in that scenario, it would be better to create a cursor for the report. The overhead would be similar to creating the IDX index, and you wouldn't have to worry about cleaning up afterwards.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

so it fits well as a documentation

Yes, a PRG is already documenting how the indexes are created, so there is no need for a screenshot of the indexes tab for that.

The DELETE TAG ALL is not necessary, if you create the whole table from scratch, but you're doing the indexing code in a special case of a broken CDX file, usually and want to keep all your data.
a) Which makes REINDEX risky, because the corruption of the CDX could mean the CDX contains wrong index expressions and REINDEX will create wrong tags.
b) you don't want to get errors about already existnig index tags

Therefore DELETE TAG ALL enables running this indexing code without problems, no matter how the CDX is corrupted.
The only other way to complete rebuild the CDX file is obviously deleting it, but you won't be able to delete the CDX file while a table is used and you won't be able to use (open) a table that is marked as having a CDX file when the CDX file is deleted, so DELETE TAG ALL is the way to go.

Taking all this into account it's mandatory. The only way it's not mandatory is when you're relying on the key expressions to still be good in the CDX header. Then you can trash all that code and rely on REINDEX to do this job.

There's one index type you can't create with INDEX ON, a primary index. And there's another advantage of REINDEX on top of being very short code: It can also recreate the index tree of a primary index key, because it simply does all index tags according to the CDX header.

So there's one more other method of recreating indexes instead of all this code: When you first create the table and store a backup of the CDX when the table is empty or has few records, you can rely on that CDX header to be healthy and reindex from that. Then, again, this code also is your documentation of how a table is indexed. So there are some pros and cons here, you could simply document it with a txt or whatever else, you still also have ATAGINFO().

There's still that primary index type to cope with so after a DELETE TAG ALL you will need an ALTER TABLE to recrete the primary index. It's not of your concern for free tables, which can't have primary keys. But there's still a point in this: Your example doesn't even create the alternative candidate index type and there always should be a unique identifier in a table, no matter if it is a unique primary field like an autoinc integer (also available for free tables), a guid or a natural compound key. Your whole index list is only using regular indexes.

Chriss