Created attachment 41257 [details]
Calc - Find and replace dialog

Added to the list of EasyHacks.

An EasyHack should have been checked by developers and thus is confirmed regardless of age. Moving back to NEW from NEEDINFO again. Sorry for the hassle.

Deleted "Easyhack" from summary.

This issue was created at the time for LibreOffice 3.3.0 RC1.
I have tested and can confirm this issue (enhancement) is still valid for
LibreOffice 3.5.4 Build_ID 350m1(Build2).
I read the release notes for 3.6 and couldn't find any info regarding Base. So
the issue should still be valid.

It could be a problem with foreign-keys. Same content at different positions could be changed or could not.
Working with Base is often working in server-based databases. The database has to confirm every changing.
There has nothing happened with this report since the beginning of LO. It must be declared better so that more people could say "A good idea." or "Impossible with databases".
The first version this enhancement is missing is LO 3.3.0. This version has to be shown in the bug-report. I have changed the version to LO 3.3.0 Beta2.

Currently I can't see that EasyHck has been confirmed by a developer due to current proceeding

Hi,

I agree with the comment of robert@familiegrosskopf.de (comment#7) : "It could be a problem with foreign-keys.". I think that a find-replace function in a table of a relational database is not the same thing that a find-replace in a Calc sheet and it could be dangerous for the integrity of the database.

The modification of fields in a table must be done with SQL UPDATE statement.

Bernard Ribot

*** Bug 46576 has been marked as a duplicate of this bug. ***

Hi developers,

on GE-user-ML (http://listarchives.libreoffice.org/de/users/msg10921.html) is been discussed this bugreport. See you any possibility to solve the problem?

In order to limit the confusion between ProposedEasyHack and EasyHack and to make queries much easier we are changing ProposedEasyHack to NeedsDevEval.

Thank you and apologies for the noise

(In reply to QA Administrators from comment #13)
> 
> If you have time please do the following:
> 1) Test to see if the bug is still present on a currently supported version
> of LibreOffice (preferably 4.2 or newer).

There has nothing been changed in the dialog. So this ask for an enhancement also exists in LO 4.4.0.0alpha0+.

I would like to work on this.

Created attachment 124057 [details]
Calc - Find & Replace Dialog

Current state of the Find & Replace dialog in Calc. (As of LO 5.2.0.0.alpha0)

Created attachment 124058 [details]
Base - Record Search Dialog

Current state of Find (Record Search) dialog in Base. (As of 5.2.0.0.alpha0)

Waiting for devEval from a core developer, as suggested by janIV on irc. (Sorry for the comment spam)

Here's your devEval:

I expect this to be definitely doable. Qua desirability, I see use cases for that. Qua risks, e.g. for foreign keys, my philosophy is to do what the user asks, so if they want to shoot themselves in the foot and screw up their foreign keys by running this on a foreign key field, let them have it. Depending the the DB setup, the DB will actually refuse to do the update.

You need to add a "replace" field to the "search" dialog, as well as the "replace" and "replace all". Then the code when "replace all" is hit will look like:

 // frm is the Form object (not document) where the search/replace is done
 // colNum is the column number in which we search
 Reference<XResultSet> rs = frm
 if(should_search_from_beginning)
   rs.beforeFirst()
 while rs.next()
 {
     OUString colValue = rs.getString(colNum);
     if is_match(colValue)
     {
         rs.updateString(colNum, do_replacement(colValue));
         rs.updateRow();
     }
 }

Adapt the code for the case where "all fields" was chosen, for the case where "replace" instead of "replace all" was chosen, etc.

Created attachment 124069 [details]
Suggested Find & Replace Dialog for Base

I have added a GtkFrame for "Replace" part, and moved the "Search" button to the Search frame, and renamed to "Find Next". How does it look?

Hi, it was quite some years ago I filed this feature request. Happy to see someone took ownership, thx !

About the foreign keys: yes, a find & replace could try to enter data in a field that doesn't comply with a foreign key.
There are other possible problems as well: trying to find "123" and replace it by "train" in a numerical field.

But that is not a big problem. Most of the time people (also I) want to:
- change content of text-fields
- fields which do not contain foreign keys

If something does go wrong, usually the underlying database produces an error which could then be shown in Base.

The only thing we should think about, is what to do if an error is produced.

We could execute the find and replace in a transaction
  => if everything works out fine, all the changes are committed => success, confirmation dialog
  => if an error is thrown by the DB, we show the error in Base, the transaction is roll backed, no changes are committed => no succes, no data changes => error dialog with database error

But maybe there is even a better way.

You asked how your new screen mock-up looked: fine by me. Fits perfectly in the existing dialog.

Good idea you added "Set field content to NULL", that is going to be a useful one !

If at a certain time you could use help to test the new dialog, you can always contact me. If you can help me to install/run your new code, I am willing to test. If you can tell me which logfiles etc you would like as a result, I will provide them. I always use Base in conjunction with PostgreSQL, so I can test that combination.

Thanks for your effort.

Hi,

A foreign key in a relationship/table (T1) is a primary key in another relationship/table (T2). Which rule will be used in the case where the new value of the foreign key in T1 is not a primary key in T2? 
Knowing that the value of the new foreign key in T1 must exist as  a valid value (one of the primary key values) in T2, or must be equal to a null value, will a new record be created in T2 to respect the rules of referential integrity?

'Find &replace' of a primary key will be allowed? If allowed, the update of the value of the primary key must be done according with the update rules defined for the database (Tools > Relations).

In my view, 'Find & Replace' operation is not a correct Relational DataBase Management :-)) If implemented, this 'Find & Replace' operation should only be allowed on the tables without constraints of referential integrity.

Bernard

(In reply to ribotb from comment #25)

> A foreign key in a relationship/table (T1) is a primary key in another
> relationship/table (T2). Which rule will be used in the case where the new
> value of the foreign key in T1 is not a primary key in T2?

LibreOffice will not do anything different in that case. The database engine will raise an error.

> Knowing that the value of the new foreign key in T1 must exist as  a valid
> value (one of the primary key values) in T2, or must be equal to a null
> value, will a new record be created in T2 to respect the rules of
> referential integrity?

No.

> 'Find &replace' of a primary key will be allowed?

Yes.

> If allowed, the update of
> the value of the primary key must be done according with the update rules
> defined for the database (Tools > Relations).

Again, LibreOffice will not do anything different in that case. The database engine will either cascaded the update (if that relation is configured like that), or raise an error.

> In my view, 'Find & Replace' operation is not a correct Relational DataBase
> Management :-))

Not always, but also not never.

> If implemented, this 'Find & Replace' operation should only
> be allowed on the tables without constraints of referential integrity.

LibreOffice should not try to guess when it will work or make sense. The user asks for it, do it. If it fails, show the error message. If it corrupts the data, the user asked for it. After all she/she could have done the same manually.

OK. I thought and I was worried that the 'Find & Replace' operation is performed OUT  of the database engine.

Thanks for these explanations.

Bernard

A polite ping, still working on this patch?

(In reply to jan iversen from comment #28)
> A polite ping, still working on this patch?

Got interrupted by some other task, but still working on this.

A polite ping, still working on this issue ?

(In reply to jan iversen from comment #30)
> A polite ping, still working on this issue ?

Sorry for no update for a while. Going slow, but still working. Progress can be watched on gerrit: https://gerrit.libreoffice.org/#/c/25361/

A polite ping, still working on this ?
(We look at assigned patches that has had no movement for a month, to control people are still working).

I'm trying to solve this issue. Comment 11 and comment 31 are good starting points. I've a doubt. What is the Form Object being talked about in comment 11 midway in here - "// frm is the Form object (not document) where the search/replace is done". 

Thanks.

(In reply to abhilash300singh from comment #33)
> What is the Form Object being talked about in comment
> 11 midway in here - "// frm is the Form object (not document) where the
> search/replace is done". 

http://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1form_1_1component_1_1DataForm.html

(In reply to Lionel Elie Mamane from comment #34)

Thanks, that clears a few things. Currently the UI handlers corresponding to already implemented search functinality is present in cuifmsearch.cxx. I can handle various cases like "set to null" and "replace all" myself, but prior to that I need to get a reference to the Form object(so that I can apply the steps you mention in comment 21). I looked into the way current searching is handled, but it goes too deep, one function calling another and I failed to find any mention of a form object. Could you possibly provide more hints/code-pointers? Meanwhile, I would keep looking deeper.

Thanks.

(In reply to abhilash300singh from comment #35)

> Currently the UI handlers corresponding to already implemented
> search functinality is present in cuifmsearch.cxx. (...)
> I failed to find any mention of a form object.

That dialog seems to be instantiated from file 
dbaccess/source/ui/browser/brwctrlr.cxx
line 1835:

  pDialog = pFact->CreateFmSearchDialog(...)
a few lines lower:
  pDialog->SetActiveField( sActiveField );
  pDialog->SetFoundHandler( LINK( this, SbaXDataBrowserController, OnFoundData ) );
  pDialog->SetCanceledNotFoundHdl( LINK( this, SbaXDataBrowserController, OnCanceledNotFound ) );

So we search (in the same file) for "OnFoundData", that is line 2353:

IMPL_LINK(SbaXDataBrowserController, OnFoundData, FmFoundRecordInformation&, rInfo, void)

That's the function that is called when a match is found.

See the comment in cui/source/inc/cuifmsearch.hxx:
    /** The found-handler gets in the 'found'-case a pointer on a FmFoundRecordInformation-structure
        (which is only valid in the handler; so if one needs to memorize the data, don't copy the pointer but
        the structure).
        This handler MUST be set.
        Furthermore, it should be considered, that during the handler the search-dialog is still modal.
    */
    void SetFoundHandler(const Link<FmFoundRecordInformation&,void>& lnk) { m_lnkFoundHandler = lnk; }



Back to brwctlr.cxx, the handler starts with:

    Reference< css::sdbcx::XRowLocate >  xCursor(getRowSet(), UNO_QUERY);

I expect this "xCursor" is the form object.


brwctlr.cxx is most probably only for grid (table) controls. For other controls, it looks like it is svx/source/form/fmshimp.cxx line 1555:

    if ( pFact )
        pDialog.disposeAndReset(pFact->CreateFmSearchDialog( &m_pShell->GetViewShell()->GetViewFrame()->GetWindow(), strInitialText, aContextNames, nInitialContext, LINK( this, FmXFormShell, OnSearchContextRequest ) ));
    DBG_ASSERT( pDialog, "FmXFormShell::ExecuteSearch: could not create the search dialog!" );
    if ( pDialog )
    {
        pDialog->SetActiveField( strActiveField );
        pDialog->SetFoundHandler( LINK( this, FmXFormShell, OnFoundData ) );
        pDialog->SetCanceledNotFoundHdl( LINK( this, FmXFormShell, OnCanceledNotFound ) );

The corresponding OnFoundData is line 2181:

IMPL_LINK(FmXFormShell, OnFoundData, FmFoundRecordInformation&, rfriWhere, void)
{
    if ( impl_checkDisposed() )
        return;

    DBG_ASSERT((rfriWhere.nContext >= 0) && (rfriWhere.nContext < (sal_Int16)m_aSearchForms.size()),
        "FmXFormShell::OnFoundData : ungueltiger Kontext !");
    Reference< XForm> xForm( m_aSearchForms.at(rfriWhere.nContext));


And there's your form object.

After doing some backtraces, I found OnFoundData at  svx/source/form/fmshimp.cxx:2181 is never called. 

I'm thinking about how to implement this. To replace a record, we would first need to search for it, and since we already have search functionality in place, we could reuse parts of it. But I'm still not sure, where I would be inserting the "Replace" code.

(In reply to abhilash300singh from comment #37)
> After doing some backtraces, I found OnFoundData at 
> svx/source/form/fmshimp.cxx:2181 is never called.

Aha. What is called then, when _not_ in a grid?

i want to work on this

(In reply to charan from comment #39)
> i want to work on this

WELCOME have a look at:

https://wiki.documentfoundation.org/Development/GetInvolved

*** Bug 113140 has been marked as a duplicate of this bug. ***

Created attachment 150238 [details]
New dialog for Base Search&Replace

Update,

In my implementation, behaviour of "replace" will be:

if "search" is never called or failed last time(no field selected):
    search accordingly
else:
    replace

No checking for foreign key.
"Search/Replace All" not implemented.
Expected dialog attached.

Could be done before next Monday if everything goes right.
Any suggestions?

(In reply to Wenzhe Pei from comment #42)
> Created attachment 150238 [details]
> New dialog for Base Search&Replace
> 
> Update,
> 
> In my implementation, behaviour of "replace" will be:
> 
> if "search" is never called or failed last time(no field selected):
>     search accordingly
> else:
>     replace
> 
> No checking for foreign key.
> "Search/Replace All" not implemented.
> Expected dialog attached.
> 
> Could be done before next Monday if everything goes right.
> Any suggestions?

Forget to mention, after replacing, a "search" will proceed automatically.

*** Bug 125614 has been marked as a duplicate of this bug. ***

*** Bug 126123 has been marked as a duplicate of this bug. ***

Re-evaluating the EasyHack in 2022

This enhancement is still relevant, and it is not implemented yet. It is good to mention that MS Office provides similar functionality in the "Find and replace" for Access.

Currently my PR is in WIP https://gerrit.libreoffice.org/c/core/+/165226


As my terminal kinda crashes with make often.

Created this PR to get some feedback.


Moreover the Abandoned PR by Muhammet Kara helped so much. Thanks. :)