Bug 32506 - Find & Replace for Base
Summary: Find & Replace for Base
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords: difficultyInteresting, easyHack, skillCpp, skillSql
: 46576 (view as bug list)
Depends on:
Blocks: Find-Search
  Show dependency treegraph
 
Reported: 2010-12-19 04:38 UTC by Enrique
Modified: 2017-04-03 04:40 UTC (History)
14 users (show)

See Also:
Crash report or crash signature:


Attachments
Base - Find dialog (31.98 KB, image/png)
2010-12-19 04:38 UTC, Enrique
Details
Calc - Find and replace dialog (33.70 KB, image/jpeg)
2010-12-19 04:38 UTC, Enrique
Details
Calc - Find & Replace Dialog (26.97 KB, image/png)
2016-04-04 08:33 UTC, Muhammet Kara
Details
Base - Record Search Dialog (47.00 KB, image/png)
2016-04-04 08:35 UTC, Muhammet Kara
Details
Suggested Find & Replace Dialog for Base (57.86 KB, image/png)
2016-04-04 15:28 UTC, Muhammet Kara
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Enrique 2010-12-19 04:38:23 UTC
Created attachment 41256 [details]
Base - Find dialog

Base already has a "Find" dialog, to search for data in a table. (see scr-shot)

A feature we are missing really hard is "Find and replace", something Calc for example has.

It would be great if the "Find" - functionality in Base
could be extend with "Replace" functionality.

That way it would be possible to:
- find and replace entire fields (f.e. "train" in "Trains")
- find and replace parts of fields (f.e. in a date field, replace all "2010" with "2011", without touching the day or the month)
Comment 1 Enrique 2010-12-19 04:38:54 UTC
Created attachment 41257 [details]
Calc - Find and replace dialog
Comment 2 Jan Holesovsky 2010-12-21 08:47:53 UTC
Added to the list of EasyHacks.
Comment 3 Björn Michaelsen 2011-12-23 11:35:13 UTC Comment hidden (obsolete)
Comment 4 Björn Michaelsen 2011-12-23 12:57:29 UTC
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.
Comment 5 Florian Reisinger 2012-05-18 09:38:52 UTC
Deleted "Easyhack" from summary.
Comment 6 Enrique 2012-08-15 10:18:01 UTC
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.
Comment 7 robert 2012-12-02 20:38:57 UTC
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.
Comment 8 Rainer Bielefeld Retired 2013-02-04 08:37:25 UTC
Currently I can't see that EasyHck has been confirmed by a developer due to current proceeding
Comment 9 ribotb 2013-02-04 09:36:14 UTC
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
Comment 10 robert 2013-09-11 19:12:38 UTC
*** Bug 46576 has been marked as a duplicate of this bug. ***
Comment 11 Jochen 2013-12-27 07:28:03 UTC
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?
Comment 12 Joel Madero 2014-02-27 22:55:14 UTC
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
Comment 13 QA Administrators 2014-10-23 17:31:58 UTC Comment hidden (obsolete)
Comment 14 robert 2014-10-23 18:41:30 UTC
(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+.
Comment 15 Alex Thurgood 2015-01-03 17:39:34 UTC Comment hidden (no-value)
Comment 16 Robinson Tryon (qubit) 2015-12-13 11:21:24 UTC
Migrating Whiteboard tags to Keywords: (needsDevEval)
[NinjaEdit]
Comment 17 Muhammet Kara 2016-04-04 07:16:35 UTC
I would like to work on this.
Comment 18 Muhammet Kara 2016-04-04 08:33:26 UTC
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)
Comment 19 Muhammet Kara 2016-04-04 08:35:06 UTC
Created attachment 124058 [details]
Base - Record Search Dialog

Current state of Find (Record Search) dialog in Base. (As of 5.2.0.0.alpha0)
Comment 20 Muhammet Kara 2016-04-04 08:39:47 UTC
Waiting for devEval from a core developer, as suggested by janIV on irc. (Sorry for the comment spam)
Comment 21 Lionel Elie Mamane 2016-04-04 09:10:55 UTC
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.
Comment 22 Muhammet Kara 2016-04-04 15:28:31 UTC
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?
Comment 23 Enrique 2016-04-06 10:42:25 UTC
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.
Comment 24 Enrique 2016-04-06 10:48:57 UTC
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.
Comment 25 ribotb 2016-04-06 14:20:18 UTC
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
Comment 26 Lionel Elie Mamane 2016-04-06 16:12:45 UTC
(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.
Comment 27 ribotb 2016-04-07 21:28:46 UTC
OK. I thought and I was worried that the 'Find & Replace' operation is performed OUT  of the database engine.

Thanks for these explanations.

Bernard
Comment 28 jani 2016-05-08 07:38:02 UTC
A polite ping, still working on this patch?
Comment 29 Muhammet Kara 2016-05-09 11:16:35 UTC
(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.
Comment 30 jani 2016-06-20 05:40:47 UTC
A polite ping, still working on this issue ?
Comment 31 Muhammet Kara 2016-06-20 07:36:26 UTC
(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/
Comment 32 jani 2016-07-21 07:28:29 UTC
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).
Comment 33 abhilash300singh 2016-12-19 11:38:10 UTC
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.
Comment 34 Lionel Elie Mamane 2016-12-19 12:05:21 UTC
(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
Comment 35 abhilash300singh 2016-12-19 18:00:04 UTC
(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.
Comment 36 Lionel Elie Mamane 2016-12-20 09:56:18 UTC
(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.
Comment 37 abhilash300singh 2017-01-06 04:58:04 UTC
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.
Comment 38 Lionel Elie Mamane 2017-01-06 10:05:21 UTC
(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?
Comment 39 charan 2017-02-09 21:42:56 UTC
i want to work on this
Comment 40 jani 2017-02-10 06:32:43 UTC
(In reply to charan from comment #39)
> i want to work on this

WELCOME have a look at:

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