14 Sep 2023

Catalog and schema support for SQL functions – difficulty interesting EasyHack

LibreOffice has a database application called Base. It can connect to various database management systems, and is integrated with two internal database engines: Firebird and HSQLDB. Here I discuss how to add catalog and schema support for SQL functions in LibreOffice Base.

SQL window

SQL window

One can use SQL to create and use internal functions. For example, with Firebird:

CREATE FUNCTION F(X INT) RETURNS INT
AS
BEGIN
  RETURN X+1;
END;

To run this, you can use “Tools > SQL…”, and then write the above SQL query. To see the result, you need to run this query:

SELECT F(5) FROM RDB$DATABASE;

Catalog and schema support

On the other hand, support for SQL commands is limited. For example, as the issue tdf#95174 describes, SQL parser of LibreOffice parser currently does not handle catalog and schema in function names:

Currently, this command  works fine:

SELECT function_name(a, b) FROM C

But this one does not:

SELECT schema_name.function_name(a, b) FROM C

The goal is to make the second one also work in LibreOffice Base.

Code Pointers

To add the support for catalog and schema in function names, you should refer to the Yacc rule for the SQL Parer.

Lionel, the experienced Base developer describes what to do in the first comment. In the file connectivity/source/parse/sqlbison.y, you can find this rule

function_name:
		string_function
	|	date_function
	|	numeric_function
	|	SQL_TOKEN_NAME

Here, you should add two new cases, like:

	|	SQL_TOKEN_NAME '.' SQL_TOKEN_NAME 
			{$$ = SQL_NEW_RULE;
			$$->append($1);
			$$->append(newNode(".", SQLNodeType::Punctuation));
			$$->append($3);
			}
	|	SQL_TOKEN_NAME '.' SQL_TOKEN_NAME '.' SQL_TOKEN_NAME
			{$$ = SQL_NEW_RULE;
			$$->append($1);
			$$->append(newNode(".", SQLNodeType::Punctuation));
			$$->append($3);
			$$->append(newNode(".", SQLNodeType::Punctuation));
			$$->append($5);}

After that, one should invoke this command:

git grep -E '(function_name|set_fct_spec)'

to find parts of the code that use them.

If the code is examining one of the above nodes, it expects a single token at the function_name. The code should be changed to expect a token or a node to handle the schema_name and function_name.

Final Notes

An implementation should be accompanied with a test to make sure that the code actually works, and will remain fine in the future changes. To see other discussed EasyHacks, follow the EasyHacks tag in this blog.

31 Aug 2023

Warning for low disk space – difficulty interesting EasyHack

Without enough space, one may face data corruption, which is really a terrible thing that can possibly happen for someones important data. In order to avoid falling into such a situation, it is good idea to give warning to the users in advance.

Code Pointers for generating warning for low disk space

To implement such a feature in LibreOffice, first place to look is this file sfx2/source/doc/sfxbasemodel.cxx.

The method to query the free space method should be added to the sal/osl folder in LibreOffice core source code. To add OS specific code, one may use unx and w32 folders inside it.

Please note that LibreOffice needs to know the disk space on different devices, so passing a vector containing path and free disk space is a good suggestion here.

You should know that guessing the required disk space to save the file is not easy. So, the idea is to have several megabytes free to avoid facing problems. That is in cases the file is not actually very huge. It is possible to add that limit as an option, placed in Tools > Options. These days, even 100-200 megabyte is not that much when comparing it to the very fast disk consumption by different applications like browsers and other similar huge software that people use regularly.

Another nice feature to implement is a handler that runs with low priority every several seconds and checks the available temporary space. That will help avoiding problems with saving images in that specifc temp directory.

Testing the Warning for Low Disk Space

One needs to create a test environment to actually test the patch in action. Using a small RAM drive, it is possible to do that. These commands are useful to create a 20 MB partition for testing:

mkdir /tmp/small
sudo /bin/mount -t tmpfs -o size=20m,mode=0700,uid=$USER,gid=$GROUP /dev/shm /tmp/small

After invoking the above instructions and filling the disk space, you can invoke LibreOffice with the below command to use temp drive. As a result, you will get the below error message:

No disk space error

No disk space error

But, no warning message is shown when you have some small disk space which is < 1 MB.

$ instdir/program/soffice -env:SAL_USE_VCLPLUGIN=gen -env:UserInstallation=file:///tmp/small /tmp/small/1.pptx

While having < 1 MB disk space, you will get this warning in the terminal, but not when the space is between 1 and 2 MBs.

warn:configmgr:57868:58063:configmgr/source/components.cxx:190: error writing modifications com.sun.star.uno.RuntimeException message: "cannot write to file:///tmp/small/user/nnePqE at ~/Projects/libreoffice/core/configmgr/source/writemodfile.cxx:109"

Please note that both the profile and the opened file were inside /tmp/small.

Final Words

The above issue is tdf#60909. If you like it, just follow the Bugzilla link to see more information.

To implement this feature, first you have to build LibreOffice from the sources. If you have not done that yet, please refer to this guide first:

Getting Started (Video Tutorial)

31 Aug 2023

Find and replace For Base – difficulty interesting EasyHack

LibreOffice Base is part of LibreOffice productivity suite that makes it possible to work with databases. It is an alternative to MS Access. One of the proposed enhancement for Base is to add a “Find and replace” dialog. Right now, a “Find” dialog is available, but it is not possible to do the replacement with the LibreOffice Base dialogs. This issue is filed as tdf#32506.

The importance

This was requested for a long time ago, but until now no developer has put time to make it a reality. This feature request has is a difficutlyIntersting EasyHack, which means it is among the EasyHacks that need more work compared to the difficutlyBeginner and difficutlyMedium ones.

I will describe the details of the task, and if you find it interesting, you can start working on it. Solving difficutlyIntersting EasyHacks is among the criterias for selecting GSoC candidates, so it worth trying if you want to be among next year GSoC candidates.

It is worth mentioning that MS Office provide a comparable functionality in “Find and replace” dialog for MS Access. Thus, it would be helpful for the people migrating from Access to Base.

Proposed UI Design for Find and Replace

Enrique, which proposed this enhancement, also provided a design for the “Search and replace” dialog.

Proposed design for LibreOffice Base Find and Replace dialog

Proposed design for LibreOffice Base Find and Replace dialog

Code Pointers For Implementing Find and Replace

As described, this enhancement will be extending the search functionality of Base with the ability to do replacement, which is not currently available from dialogs. It is however possible to use SQL queries to do the replacement. Then, the task would be extending the search dialog, and then adding the required methods that use SQL to do search and replacement.

Lionel, a LibreOffice Base developer, has suggested this path, which I have updated:

The discussed dialog is instantiated in this C++ file
dbaccess/source/ui/browser/brwctrlr.cxx:1798:

pDialog = pFact->CreateFmSearchDialog(getFrameWeld(), sInitialText, aContextNames, 0, LINK(this, SbaXDataBrowserController, OnSearchContextRequest));
pDialog->SetActiveField( sActiveField );
pDialog->SetFoundHandler( LINK( this, SbaXDataBrowserController, OnFoundData ) );
pDialog->SetCanceledNotFoundHdl( LINK( this, SbaXDataBrowserController, OnCanceledNotFound ) );
pDialog->Execute();
pDialog.disposeAndClear();

As the SetFoundHandler() uses OnFoundData, we search the same file for "OnFoundData", and find it in the line 2347:

IMPL_LINK(SbaXDataBrowserController, OnFoundData, FmFoundRecordInformation&, rInfo, void)
{
...
}

This function is called, when a match is found.

The comment above the function SetFoundHandler() describes the idea of “found handler”s:

/** 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)
{
...
}

In the above mentioned file, brwctlr.cxx, this is the start of handler function:

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

This "xCursor" is the form object. The brwctlr.cxx is only for grid (table) controls. For other controls, one should look into svx/source/form/fmshimp.cxx:1544:

SvxAbstractDialogFactory* pFact = SvxAbstractDialogFactory::Create();
ScopedVclPtr<AbstractFmSearchDialog> pDialog(
pFact->CreateFmSearchDialog(
m_pShell->GetViewShell()->GetViewFrame().GetFrameWeld(),
strInitialText, aContextNames, nInitialContext,
LINK(this, FmXFormShell, OnSearchContextRequest_Lock) ));
pDialog->SetActiveField( strActiveField );
pDialog->SetFoundHandler(LINK(this, FmXFormShell, OnFoundData_Lock));
pDialog->SetCanceledNotFoundHdl(LINK(this, FmXFormShell, OnCanceledNotFound_Lock));
pDialog->Execute();
pDialog.disposeAndClear();

The corresponding OnFoundData is line 2150:

IMPL_LINK(FmXFormShell, OnFoundData_Lock, FmFoundRecordInformation&, rfriWhere, void)
{
    if (impl_checkDisposed_Lock())
        return;

    DBG_ASSERT((rfriWhere.nContext >= 0) && (o3tl::make_unsigned(rfriWhere.nContext) < m_aSearchForms.size()),
        "FmXFormShell::OnFoundData : invalid context!");
    Reference< XForm> xForm( m_aSearchForms.at(rfriWhere.nContext));
    DBG_ASSERT(xForm.is(), "FmXFormShell::OnFoundData : invalid form!");
...
}

And then we can use the form object to implement the required change to fulfill the request.

Possible Pitfalls

It is important not to cause troubles with the keys, both foreign keys and primary keys. The idea is to allow find and replace in primary and foreign keys, but then it would be the role of the underlying database engine to see if the replacement is actually possible, or not, and then raise an error message.

Also, it would be the responsibility of the users to make sure that the search and replace they issue is a meaningful one. But, anyway the developer should handle the errors from the underlying database engine.

Final Notes

To implement this feature, first you have to build LibreOffice from the sources. If you have not done that yet, please refer to this guide first:

Getting Started (Video Tutorial)

10 Aug 2023

Highlight the current row and column in Calc – difficulty interesting EasyHack

In large computer displays, it is somehow hard to track the active cell, and the associated row and column. One of the solutions provided to fix this problem is to highlight the row and column. The proposed solution is visible in tdf#33201:

The importance

This is requested for a long time, but until now no developer has put time to make it a reality. This feature request has is a difficutlyIntersting EasyHack, which means it is among the EasyHacks that need more work compared to the difficutlyBeginner and difficutlyMedium ones.

I will describe the details of the task, and if you find it interesting, you can start working on it. Solving difficutlyIntersting EasyHacks is among the criterias for selecting GSoC candidates, so it worth trying if you want to be among next year GSoC candidates.

Highlighting the row and column in Calc

Highlighting the row and column in Calc

One of the contributors have created the above mock-up to show that how the implementation should look like. As you can see, it is somehow similar to a combination of the situations where you select a row, and a column. This can provide an insight on the possible path to the actual implementation. The only difference is that the actual color of the selected cell should be different.

Code pointers to highlight row and column

Eike, an experienced LibreOffice developer, suggests that one should look intoScTabView::MarkRows() and ScTabView::MarkColumns(). These functions are called for .uno:SelectRow and .uno:SelectColumn. As described previously, the selected cell should have a different color.

To do the implementation, one should add HighlightOverlay to the OverlayManager. As a similar implementation, SelectionOverlay is updated from InitBlockMode() as ScGridWindow::UpdateSelectionOverlay(), for example like UpdateHighlightOverlay().

Rafael, another active community member, suggests looking into these files, and add a new sdr::overlay::OverlayObjectList to handle the new highlight overlay:

sc/source/ui/inc/gridwin.hxx
sc/source/ui/view/gridwin.cxx
sc/source/ui/view/gridwin4.cxx

Heiko, the UX mentor for LibreOffice, has mentioned that this feature should be optional. He suggested to use this path in options:

Tools > Options > Calc > View: “[x] Highlight col/row <Blue>

Final Notes

To implement this feature, first you have to build LibreOffice from the sources. If you have not done that yet, please refer to this guide first:

Getting Started (Video Tutorial)

30 Jul 2023

ccache for a 5 minutes LibreOffice build

If you have ever tried to build LibreOffice code, you know that it can take a lot of time. LibreOffice has ~6 million lines of C++ and some Java code (<280k). But, there are tools that can help you build LibreOffice from source code much faster, if you do it repeatedly! Here I discuss how to use one of these tools: “ccache”. (more…)

4 Jul 2023

Parallel build: tuning the performance

Do you want to build LibreOffice alongside other applications that are open on your computer like the IDEs, and you want to keep the programs responsive during the build? Then you need to know how to tune the parallel build. Here I discuss how. (more…)

23 Mar 2023

Internal includes instead of global ones – EasyHack

In every C/C++ source code file, we use header files to put declarations of functions, data types, class, macro and other relevant things inside it. These files have the extension of .h (for C) and .hxx (for C++). (more…)

3 Mar 2023

VCL application in its minimal form

LibreOffice uses an internal GUI toolkit, named VCL (Visual Class Library). It creates the GUI widgets for LibreOffice, but it is not generally available for other applications. But there are ways that you can create standalone applications with VCL, at least to learn it better. (more…)

21 Feb 2023

Telemetry required? Ask users first!

In this article, I will discuss the recent problems with compiling LibreOffice using Microsoft Visual Studio, things that I did to debug and find the root cause, the source of problem itself – which is problems in Microsoft’s telemetry – and how I could fix it. (more…)

5 Feb 2023

QR code improvement – LibreOffice EasyHack

A QR code is a type of 2D barcode that is useful for encoding data, such as a URL, contact information and many other data types. One can scan the code via applications on mobile phones to capture a URL or import contact information. (more…)