×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

INDEX versus TAG
5

INDEX versus TAG

INDEX versus TAG

(OP)
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:

CODE -->

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

RE: INDEX versus TAG

Sure,
But I doubt you need all this indexes all the time.
If you need an index JUST for report use SQL Select into cursor and order the information there.
And the base the report of the result cursor.

Borislav Borissov
VFP9 SP2, SQL Server

RE: INDEX versus TAG

As I told a client last week, I haven't used an IDX file since we got CDXs in FoxPro 2.0. If you're in a position to replace all those IDX files with a single structural CDX ("structural" means it has the same name as the table, and opens automatically when the table does), then absolutely, ditch the IDX files and make your life much simpler.

Borislav is also right; you don't need to maintain indexes that are used only for reporting. Run a query before you run a report to collect all the data and put it in the order you want. If you're not familiar with SQL, you might start with this paper I wrote: http://tomorrowssolutionsllc.com/ConferenceSession...

Tamar

RE: INDEX versus TAG

(OP)
Ok, Tamar, I want get rid of all IDX files in my applications.

But can I use TAG in free tables? (In your paper about SQL it seems I have to use the tables in a database, not in free tables)

I'm trying to get more basic information from the Hacker's Guide to VFP 6.0 (oh, heavy book).

Thank you,
SitesMasstec

RE: INDEX versus TAG

You've been given good advice by Borislav and Tamar. I can only add that it is very rare to see so many indexes on a single table (and even rare nowadays to see each index in a separate file, which is what you have).

As a rule of thumb, indexes can speed up the retrieval of data. But they can slow down the updating of data. For that reason, you should favour indexes only on the fields or expressions where retrieval performance is important.

Your best bet - as Tamar has said - is to replace all the single index files (IDXs) with a CDX. That way, you won't have to worry about explicitly opening the indexes.

Also, you asked if you can use TAG with free tables. The answer is yes. It makes no difference whether the table is free or is part of a database. But TAG isn't an alternative to INDEX. A tag is simply a name by which you can refer to the index. You specify the tag when you create the index; thereafter you can refer to the tag, for example in SET ORDER.

I hope this makes sense.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: INDEX versus TAG

You also need to take account of the size of the table. If the table is small - say, only a few hundred records - you can probably get away without any indexes at all. For reports, do as Borislav suggests: use SELECT ... ORDER BY ... INTO CURSOR, and use the resulting cursor as the data source for the report.

But if the table runs to hundreds of thousands of records, then the choice of indexes is critical. That might also be true if you have a moderately-sized table but you are running many reports many times.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: INDEX versus TAG

Hello,

just to clarify with an example

use adr
index on shortname tag short
index on bdate tag bdate
index on grpname + shortname tag grpname
(all this goes in one .cdx)

Use examples
set order to tag bdate or set order to 2 or set order to bdate
set order to grpname DESCENDING

Pls. also look in help for more options like for clause and other commands like indexseek and maybe you google on "binary index" if you for example have an index on deleted()

Regards
tom




RE: INDEX versus TAG

Free tables can have a cdx, that's not the problem.

The most important limitation on indexes of (legacy or not) free tables is they can only have a candidate index type, not a primary index. But using idxes you only use the normal index anyway. And candidate is as good in its properties of not allowig duplicate keys and null as primary keys are, they will just not be automatically used for pk/fk relationships. You should really dig a bit more into your options.

Instead of trusting your "belief" you can't do something, you can simply try an INDEX ON field TAG tagname and see if it works and as easy as that you get out a wrong idea you have about VFP. If you're concerned with data integrity in case something errors, do such an experiment on a copy of your DBF and you're safe?

You're wrong ides of VFP are limiting you and you have VFP itself available to answer such uncertainties yourself. You need even less code to check it out for yourself than wrinting a question here. What is your bloackade, really?

Chriss

RE: INDEX versus TAG

Quote (SitesMasstec)

In your paper about SQL it seems I have to use the tables in a database, not in free tables
In such situations, where you tell from where you got an idea or knowledge, it helps if you refer to it by a more specifci name of an article, (white)paper or with a link. What is it? Or don't you remember anymore?

I can't imagine Tamar ever wrote something like indexed optimized SQL only works on database tables, what spawned your wrong idea about how things work must have been a misunderstanding or generalization of a very specific case.

Chriss

RE: INDEX versus TAG

A little further clarification ...

In his post (above), Tom suggested:

CODE

use adr
index on shortname tag short
index on bdate tag bdate
index on grpname + shortname tag grpname 

All good stuff. But be clear that this would be a one-off operation. The INDEX command is what you use to create the indexes. You only need to do that once, when you first create the table - not every time you use the table.

It is the SET ORDER TO or the USE ... ORDER that actually opens the indexes that were created previously.

This is probably all obvious, but I thought it worth mentioning.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: INDEX versus TAG

This is probably all obvious, but I thought it worth mentioning.

Indeed I think the usage of USE CLIENTES INDEX list of idx filenames indicates SitesMasstec is aware that usage of IDX files has the need to open them together with a table to update them when changes are made to the DBF. Tamar also pointed out that a CDX is automatically taken into account, SET ORDER TO a tagname is then setting an order, if you explicitly want to and not just rely on automatic usage of indexes by SQL or LOCATE by Rushmore optimization, indexes always have that double use case of being used for optimization and sorting. You don't need to fear this gives you less space for your indexes, usually the CDX grws less than the fpt file for memos, for example, I've never had the case the CDX file size was the limiting factor of the overall table file space limitations. Plus, you can define further CDX files and have more indexes than the 2GB of the main CDX file. So there's no need to worry about that. CDXes in themselves are making better usage of disk space than IDX, which you can also verify by createingh two IDX and a CDX with the same indexes and comparing the toal size of both IDX vs the size of the CDX.

So indeed up until now you waste disk space and made optimizations harder than necessary, even if you always were opening IDXes together with a DBF. In some aspects free DBFs are still faster than DBC DBFs, but IDX files are not better than CDX files at all.

Chriss

RE: INDEX versus TAG

(OP)
Yes, Mike. Tom's example is good and your note is important for clarifying it:

Quote (Mike)


You only need to do that once, when you first create the table - not every time you use the table.

So, after I create a table, I shall index all the fields I will use (just one time).

CODE -->

USE CLIENTES EXCLUSIVE
INDEX ON CCODI TAG CCODI
INDEX ON CNOME TAG CNOME 


Then, I can use in a report

CODE

USE CLIENTES ORDER CNOME 

If after some weeks I need the table to be ordered in a new way for a new report, I can type the command:

CODE

USE CLIENTES EXCLUSIVE
INDEX ON CCIDA+CCCEP TAG ENDER 

In the event the CDX file becomes corrupted (blackout during saving a record, for example), I can have a PRG file to recreate it. As:

CODE -->

USE CLIENTES EXCLUSIVE
INDEX ON CCODI TAG CCODI
INDEX ON CNOME TAG CNOME
INDEX ON CCIDA+CCCEP TAG ENDER 

Thank you,
SitesMasstec

RE: INDEX versus TAG

(OP)
I've just finished reading what Chris posted above, before my last post.

So, I will stick to CDX, better than having lots of IDX files in a data folder. When, and if, necessary I can create one IDX for a particular listing.

Now, thank you, I have a clear direction to take.

As an additional learning I will read (or reread) "Indexes in Visual FoxPro", chapter "Working with Data" in Kilofox.

Thank you,
SitesMasstec

RE: INDEX versus TAG

SitesMasstec, looks like you've got it spot on. Your latest two posts are completely correct.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: INDEX versus TAG

Ah, I've just thought of something you might need to keep in mind.

It's quite common (and perfectly acceptable) to give the tag the same name as the field. And if you do that in the table designer, it will happen automatically by default. In other words, if you create a field in the table designer, then use the Index combo (to the right of the field name) to create a tag for it (and specify it as either ascending or descending), then the tag will have the same name as the field.

No problem ... except that tag names are limited to ten characters. So if the relevant field name is longer than ten characters, the tag name will be automatically truncated. And if you don't realise that that has happened, you will get an error in your program when you try to SET ORDER TO what you think is the correct tag name.

Obviously this is not a big problem provided you understand what is happening. But it is surprising how many people get caught out by it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: INDEX versus TAG

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.

Also, you don't need to use the ORDER clause of USE, and you can switch sort order at any time by using SET ORDER TO TAG tagname, there does not need to be an initial order, i.e. openina table now can concentrate on just that, opening the table. The TAG is automatically found in the main CDX, if you have secondary IDX or CDX you need to set order as before or in case of a secondary CDX, like tablenam2.cdx you'd use SET ORDER TO TAG tagname OF tablename2.cdx, for example, and the OF clause is unnecessary, if the tag is part of the main CDX. which will be the the most likely case. You rarely need a second CDX. There is still a reson to have the ORDER clause of the USE command, you often will use/list/search a table in a spcific sort order and therefore a tag used right away is often helpful, but it won't be necessary to optimize a search by SQL or a LOCATE, that will not only use open CDX tags, it will use the full CDX, no matter if you open a tag or not.

There is a use case where an IDX is very appropriate: If you only need a temporary sorting of data, which you later can dispose by deleting the IDX file. It's possible to use in a DBF that's no open exclusive, I'm quite sure you also don't need exclusive access to create a CDX tag, too. The major point here is that an IDX could be created as a local file that only applies to one client and not all, so you could use an IDX to create a complex sort order with primary, secondary sort order by an expression like creating an IDX on LASTNAME+FIRSTNAME limited to a current filter with a FOR clause, to give an example, where an IDX still is quite fine. Anyway, when indexing query data I usually have that in a cursor that's temporary anyway and use a cursors CDX, cursors, like DBFs, can also store their indexes in a major CDX and that's also automatically disposed when a cursor is closed, so even that use case for an IDX is something you can also solve with a CDX.

Chriss

RE: INDEX versus TAG

(OP)
Yes, Mike, I realized that there is an Index combo in the Table Designer to activate the correspondent TAG for the field.

But the table field name doesn't accept more than 10 characters. But I understand now, as you notify me that if I use programatically this, the Tag name will be truncated to ENDERECOFI:

CODE -->

INDEX ON CCIDA+CCCEP TAG ENDERECOFIRMA 

Thank you,
SitesMasstec

RE: INDEX versus TAG

Looks like the others have all sorted out your questions. I am curious what I wrote that misled you to think that CDXs were only for tables in databases (so I can say it more clearly the next time, or even fix the paper, if that's appropriate).

Tamar

RE: INDEX versus TAG

(OP)
Hello Tamar!
I was not absolutelly mislead by your excellent paper "Learn to use SQL": as one example I saw in the article was about a table connected to a Database, I was in doubt if the use of Tag/Set Order could be used in a free table, too. That was the only reason I ask you dear colleagues the question in my post.

Thank you,
SitesMasstec

RE: INDEX versus TAG

SitesMasstec,

Thanks for pointing out the title of the article you read, now Tamar could verify what she wrote and whether that was misleading in some way.

SQL can also be used on free tables, and also on 2.x version legacy/free tables, the SQL engine does not have any requirement about DBF file versions. I guess it's not the article that talked about the limit you have remembered, it's your interpretation of the article. Nothing seriously alarming, it's hard to get something completely right when you learn it for the first time, but learning wrong conclusions from what you read obviously is even worse than the other usually feared danger of half knowledge that even exists if you have everything right but still only know part of what should be known to use a technology. If you don't learn from a teacher in person, in University or school and are not tested and have to pass exams, in short learning autodidactically, you should always take your time to test what you read in techincal articles with hands on VFP and do the things that are explained to see whether your understanding is right or wrong, an article, book or any such one way learning material will never be able to test your understanding, but VFP itself will always react to something that doesn't work, it's always even better than any expert in telling you what works and what doesn't. An expert then is good for telling you what you have to change to make your failed try work.

Chriss

RE: INDEX versus TAG

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

RE: INDEX versus TAG

Quote (TamarGranor)

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

RE: INDEX versus TAG

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


RE: INDEX versus TAG

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

RE: INDEX versus TAG

3
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

RE: INDEX versus TAG

Quote (George Santayana)

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

RE: INDEX versus TAG

(OP)
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

RE: INDEX versus TAG

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

RE: INDEX versus TAG

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

RE: INDEX versus TAG

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_(mathematic... 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... 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

RE: INDEX versus TAG

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

CODE -->

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

RE: INDEX versus TAG

You click on the tab "Inbdexes".

Chriss

RE: INDEX versus TAG

(OP)
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

RE: INDEX versus TAG

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:

Quote (VFP help)

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

RE: INDEX versus TAG

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

RE: INDEX versus TAG

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

RE: INDEX versus TAG

(OP)
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:

CODE -->

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 

Quote (Chris Miller)


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

RE: INDEX versus TAG

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.

RE: INDEX versus TAG

Quote:

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

RE: INDEX versus TAG

Quote (SitesMasstec)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close