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: 102847
Blocks:
  Show dependency treegraph
 
Reported: 2010-12-19 04:38 UTC by Enrique
Modified: 2016-09-30 08:32 UTC (History)
13 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
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
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
Please read this message in its entirety before responding.

Your bug was confirmed at least 1 year ago and has not had any activity on it for over a year. Your bug is still set to NEW which means that it is open and confirmed. It would be nice to have the bug confirmed on a newer version than the version reported in the original report to know that the bug is still present -- sometimes a bug is inadvertently fixed over time and just never closed.

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).
2) If it is present please leave a comment telling us what version of LibreOffice and your operating system.
3) If it is NOT present please set the bug to RESOLVED-WORKSFORME and leave a short comment telling us your version and Operating System

Please DO NOT
1) Update the version field
2) Reply via email (please reply directly on the bug tracker)
3) Set the bug to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case)

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link:
https://wiki.documentfoundation.org/QA/BugTriage

There are also other ways to get involved including with marketing, UX, documentation, and of course developing -  http://www.libreoffice.org/get-help/mailing-lists/. 

Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
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 jan iversen 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 jan iversen 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 jan iversen 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).