Checking Field TAG

[mstrcmtr]

Is there any way to check field has tag or NOT

Before deleting any field from table check this field has any tag

*** Checking then Delete ***

DELETE TAG Field_8 OF Table_test.Cdx

 

[Olaf Doschke]

Let me give another example. I often have an index sorting data in a sensible way in more than one single column, eg sort a person list by lastname, then firstname, then (not realistic, but for showing the case) by birth date. a field I would like to remove for a company which thinks of that as invalid private data to store.

CREATE CURSOR crsPersons (iID I AUTOINC, cLastname C(50), cFirstname C(50), dBirthdate D)
INDEX On cLastname+cFirstname+DTOS(dBirthdate) TAG xOrder

You can't alter that to remove the dBirthdate before removing the xOrder tag, but the xOrder tag purpose is to sort data, it should not be deleted but be modified to not contain the dBirthdate field.

In the end you have to take a look at the indexes involved before deleting them. If you modify the function Tore mentions to examine column 3, as I told you, that function should still not blindly delete tags. You have to know the purpose to be able to make a sensible decision, this is not a case for a function deciding on your behalf and in any case.

Bye, Olaf.

 

[Tore Bleken]

For your information, I very rarely use functions or calculations for index expressions, and when I do, it's usually Upper().

An index tag like you showed, cLastname+cFirstname+DTOS(dBirthdate), doesn't exist in my application as they shouldn't in any application according to my standards. Why complicate things? If I ever needed such an order, I would have separate index tags for each field. Then I would issue a SQL statement, Select cLastname, cFirstname, dBirthdate from MyTable order by 1,2,3 into cursor curTemp.

Enough said on my part, since these kind of discussions may be of little or no interest to the OP. Sometimes it only makes a simple matter complicated.

 

[Olaf Doschke]

Well,

just a last note on your single field index usage only. Single fields indexes won't help to make a nested ordering be applied faster, a separate last and first name index give fast ordering by last name only and by first name only, but it'll not help vfp to decide which first name (family member) will come first for a certain last name, and you know that. VFP wouldn't also use a complex index for order by 1,2,3, sure, but you can sort by the index expression and the index will be used for the sorting also in sql queries. In your case most probably VFP would do a rough sorting by the lastname index and then sort the rest without index usage, becaus I don't see an operation just readong part of an index tree for a set of already found records from anther index. This kind of sibling index relations don't exist in the simple index structures VFP maintains in CDX tags. VFP can make use of single field indexes to combine a result set "bitmap" from several simple filter criteria, you'll make use of that aspect with simple indexes on single fields. But also that aspect can be accelerated by more special indexes.

On the topic of "Why complicate things?". Well, indexes are the main ingredient for rushmore optimization and that's the main ingredient making vfp data fast and contributing to the success of foxpro applications. Only using simple indexes you waste a bit of that mechanism. It's a complicated topic to not create too many indexes, but an xOrder tag on any table made sense to me most always.

Bye, Olaf.

 

[Olaf Doschke]

OK, if you're like most of the others here, mstrcmtr, and name your tags just as the fields, then your solution is very simple:

TRY
   USE yourtable EXCLUSIVE
CATCH
   ? "Can't modify a table without exclusive access to it, sorry."
   RETURN
ENDTRY

TRY
   DELETE TAG fieldname
CATCH
   * no such index, nothing to worry about
ENDTRY

TRY
   ALTER TABLE yourtable DROP COLUMN fieldname
CATCH
   ? "couldn't drop the field because of some index tag, I think it's "+SYS(2018)+". It could be more index tags, but that's what I see right now."
ENDTRY

Bye, Olaf.

 

[Mike Lewis]

I have Free table with some fields not in Use from many years making table size heavier and heavier therefore i want to remove those unUsed Fields

You can do that interactively. You don't need program code to do it. Just open the table designer (MODIFY STRUCTURE), and go to the Indexes tab. That will tell you what indexes you have. If any of them involves the field you want to delete, remove it. Then delete the field.

But you still need to take care. Are you sure you have never referenced the field anywhere in your application? Deleting a field in a live table is quite a drastic thing to do, and the implications go beyond index tags.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes, Mike sums up my concerns in one sentence. Very fine.

Bye, Olaf.

 

[Olaf Doschke]

And mstrcmtr, if you use some older version of VFP, 7 or below, you don't have TRY CATCH, but you have ON ERROR * or this way:

LOCAL llError 
ON ERROR llError = .T.

* Try
* one line of code
If llError
  * catch block
Endif
llError = .F. && endtry
* next try block...

What you can't do in that structure is let the first error jump to the If llError catch block. If the Try block has several lines of code and one line errors, that just set llError=.T. and the next line still is executed, the only way to have the same behaviour as with TRY CATCH is controlling llError after each line or only have one line of code. Therefore I made the comment "* one line of code". That part up to IF llError can have multiple lines of code, too, if you're sure they work out.
Try Catch can be extended to give you an Exception object telling line of error, error number and message, etc, so you can better react to the specific error happening in the try block.

In this case I used three try catch blcoks, because errors happening in each of the three steps have very different implications. You can't do anything like removing a tag or field without exclusive access, therefore the first catch block is returning, you can simply ignore not finding the index tag named the same as the fieldname in the second block, so there just is the comment in that catch block, but you have to know what to address if you still can't drop a column though a simple index tag is not existing. I can only display SYS(2018) knowing the error comes from the ALTER TABLE.

And in the end, even if that code succeeds you'll see how failsafe your assumption was about the field not being used. You don't have to ask your customers about the field, you have to look into any code. I am not listening to single end users saying they don't need some feature, I'm not listening to the customers company stake holder confirming that this field or feature is obsolete, because next day, week, month some other user missed that field/feature. It happened far too many times and people don't learn the lesson from it.

Last not least, even if you don't want to go the TRY route, Mike posted early on, how to know a tag with field name exists: [tt]llTagExists = (TAGNO("SomeField") > 0)[/tt]. If you go by that convention, that'll be sufficient and you should at least know that, if you don't know how your own tables are structured.

Bye, Olaf.