Let me see if I've got this straight. Caligra is the umbrella Office product intended to handle data in all its forms:

Totally unstructured ("my muse is singing, get out of the way!") = Author
Minimally structured = Words
Conceptually structured = Braindump
Graphically structured = Krita/Karbon (why 2 drawing apps is a topic for another forum)
Structured in time = Stage
Semi-structured = Sheets
Formally structured = Kexi
Domain structured = Plan

All of these need a common set of tools to provide services: A presenter using stage will need charts with data from Sheets or Kexi or hand-coded. Similarly for Words doing a mail merge.

I'm not part of Caligra so I don't have any insight into how well the various parts use each other's work. Ideally you build on each other so that the interface is similar across tools.

Like Caligra (and Access), Kexi is several things jammed into one box:

1) GUI to visually define the structure (tables and queries) = KexiDB?
2) Tool to add, modify and delete structured data from heterogenous DB systems = Predicate?
3) Tool to create reports from structured data (from csv, Sheets or query) outbound only = KexiReport?
4) Tool to create and interact with forms to go bidirectionally into (semi-)structured data = KexiForm?

From reading the wish lists and TODOs I get the sense that once them most basic items in KexiDB and Predicate were identified the focus has shifted to KexiForm and KexiReport. I don't often need either of the latter so I can't really comment on what they compete with.

A good implementation of #s 1 and 2 above doesn’t yet exist in the FOSS world. Kexi destroys all test data if you make even a minor change to your table making it unusable in the design phase. I know Jaroslaw has what I consider a well-designed way to fix that and that'll be huge when it ships. Until then I'm stuck with Access.

Doing #2 well implies a bidirectional on-the-fly translation from KexiSQL (a well-defined superset) to the target DBMS (API exported, of course, for reuse)

Dealing with heterogeneous back-end DBMSs is hugely difficult and requires vastly different skills from those required to do graphics. I know the Perl and PHP folks have burned a lot of gray cells trying to make it happen and I hope you're collaborating with them (or at least picking their brains).

While I'm on this topic, be sure your keywords and structures have kept Oracle in mind. You won't want to start with them but you will eventually need to go there if Kexi is to have the success it deserves. One ignores the 400 kilo gorilla in the room at one's peril.

One final note on the exchange of data between Kexi and Sheets. I've seen some discussion that makes me think that you're considering a way to let a user modify a cell on a Sheet and have that propagate to an update in the database. This makes me very nervous as I see it as being fraught with opportunities to mess up the underlying data.

Let me instead propose 2 new Sheet types for Sheets: A read-only Sheet that contains the content of the linked query the last time the "Refresh" event was triggered and an update Sheet that invokes the linked query using the Sheet as input using a reserved prefix to identify it (a temporary table). The linked query would be run when the "Refresh" event triggers.

Hi, cool post - this forum exists exactly for this kind of collaboration.
Just a quick clarification for terms (I'll reply with details later):

littauer wrote:Like Caligra (and Access), Kexi is several things jammed into one box:

1) GUI to visually define the structure (tables and queries) = KexiDB?
2) Tool to add, modify and delete structured data from heterogenous DB systems = Predicate?
3) Tool to create reports from structured data (from csv, Sheets or query) outbound only = KexiReport?
4) Tool to create and interact with forms to go bidirectionally into (semi-)structured data = KexiForm?

1) GUI to visually define the structure (tables and queries) = just the Kexi app (reasonably modular, plugin based where it makes sense)
2) Tool to add, modify and delete structured data from heterogenous DB systems = KexiDB (dependent on a few % of KDElibs), it will become Predicate in the future (removed KDElibs dependencies, will live as a 3rdparty Qt module; compare to core Qt modules at http://qt-project.org/wiki/Qt-Essentials-Modules)
3) Tool to create reports from structured data (from csv, Sheets or query) outbound only = Kexi Reports
4) Tool to create and interact with forms to go bidirectionally into (semi-)structured data = Kexi Forms

There is also a Data/Database Migration module that can be identified.

Regarding mail merge we fortunately do not have it yet in Calligra so there are chances for common component. Such approach has been initiated in Calligra in case of Bibliography database for the Words word processor - implementation based on KexiDB is in the works.

I'd like to add: in our design, the place for logics such as triggers or higher-level rules is in the db schema, not in the GUI, i.e. Table Designer is a tool that will support them, not Forms; the goal is to have as much as possible very close to the db schema, models, etc. Such a separation would enable web forms (we had it but it's unmaintained now), 3-tier apps generator, and autoforms (based on predefined rich/intelligent styles).

jstaniek wrote:There is also a Data/Database Migration module that can be identified.

Plus: planned user scripting, a layer that can be applied both to visual and nonvisual features.
Among others:
- db triggers could be extended with scripting,
- visual parts can have blocks of scripting,
- scripts as tools can be saved as objects (this is how it's looking now)
- server-side scripting can be exposed to kexi (in case of SQLite Kexi's scripting could be reused thanks to specifics of this local backend and its extension system)

So far scripting implementation is experimental, lack design and manpower.
We're considering having scripting reworked based on javascript (through the excellent QtScript). This is undecided but given short resources we'd like to have good support for one language (with 'native' feeling) than unfinished and buggy support for 5.

Designing/documenting DB Object model for scripting has been even planned: http://community.kde.org/Kexi/Plugins/S ... ject_Model (please see the note "Scripting makes sense mostly in Qt5-based Kexi.")

littauer wrote:Let me see if I've got this straight. Caligra is the umbrella Office product intended to handle data in all its forms:

Totally unstructured ("my muse is singing, get out of the way!") = Author
Minimally structured = Words
Conceptually structured = Braindump
Graphically structured = Krita/Karbon (why 2 drawing apps is a topic for another forum)
Structured in time = Stage
Semi-structured = Sheets
Formally structured = Kexi
Domain structured = Plan

All of these need a common set of tools to provide services: A presenter using stage will need charts with data from Sheets or Kexi or hand-coded. Similarly for Words doing a mail merge.

The topic of reusing Kexi's data definition/entry facilities and data sources in other Calligra apps has been mentioned during Calligra meetings. It looks like quite long term; Biblio DB and Mailmerge would be the first step. One particular idea is that Kexi can adjust its interface and offer a service for other apps, e.g. a table view or form for entering data for the Mailmerge. Under the mask it would be still Kexi.

For other details please look at minutes from Calligra 2011 meeting: http://community.kde.org/Calligra/Meeti ... ng/Minutes

Remaining answers:

littauer wrote:Doing #2 well implies a bidirectional on-the-fly translation from KexiSQL (a well-defined superset) to the target DBMS (API exported, of course, for reuse)

Dealing with heterogeneous back-end DBMSs is hugely difficult and requires vastly different skills from those required to do graphics. I know the Perl and PHP folks have burned a lot of gray cells trying to make it happen and I hope you're collaborating with them (or at least picking their brains).

We don't miss opportunity of reusing these ideas. However there are limits. If you refer to data access APIs of Perl or PHP, they're ... usually just data access APIs, in particular they do not address needs of integration with GUI app, and especially editors, such as Table or Query designer. For those needs, we have much deeper API, which is never needed for server-side software written using PHP or Perl.

Kexi needs to 'know' a lot more about differences between the backends than programs that only operate at data access level. We have DDL and more DML features directly in the API (and DCL is planned), the app do not hardcode any native SQL, only KexiSQL is used. KexiSQL is parsed and fully defined, what gives a chance for reliable translation to native SQL commands. The way how the parser also improves experience of the Query Designer beats many competing tools.

littauer wrote:While I'm on this topic, be sure your keywords and structures have kept Oracle in mind. You won't want to start with them but you will eventually need to go there if Kexi is to have the success it deserves. One ignores the 400 kilo gorilla in the room at one's peril.

Perfectly agree, we'll more than welcome contributor and maintainer of Oracle support of any kind. Kexi 1.x oferred Oracle support but that's been turned off due to lack of maintenance. But why not after moving from KexiDB to Predicate? (actual situation of db drivers: http://community.kde.org/Kexi/KexiDB_Drivers)

littauer wrote:One final note on the exchange of data between Kexi and Sheets. I've seen some discussion that makes me think that you're considering a way to let a user modify a cell on a Sheet and have that propagate to an update in the database. This makes me very nervous as I see it as being fraught with opportunities to mess up the underlying data.

Let me instead propose 2 new Sheet types for Sheets: A read-only Sheet that contains the content of the linked query the last time the "Refresh" event was triggered and an update Sheet that invokes the linked query using the Sheet as input using a reserved prefix to identify it (a temporary table). The linked query would be run when the "Refresh" event triggers.

My imagination has been in line with what you explain: special mode for the Sheets app (a new kind of sheet). I've been discussing this with Inge Wallin and I remember we were quite excited about possibilities.

There is also a Data/Database Migration module that can be identified.

OK, glad there's more

Regarding mail merge we fortunately do not have it yet in Calligra so there are chances for common component. Such approach has been initiated in Calligra in case of Bibliography database for the Words word processor - implementation based on KexiDB is in the works.

Again, good. I always try for more generality if I can.

I'd like to add: in our design, the place for logics such as triggers or higher-level rules is in the db schema, not in the GUI, i.e. Table Designer is a tool that will support them, not Forms; the goal is to have as much as possible very close to the db schema, models, etc. Such a separation would enable web forms (we had it but it's unmaintained now), 3-tier apps generator, and autoforms (based on predefined rich/intelligent styles).

Please distinguish between the KexiForm GUI and the Kexi GUI. Kexi GUI (DB schema aware) needs to be able to define events. KexiForm GUI is a consumer of events.

So far scripting implementation is experimental, lack design and manpower.
We're considering having scripting reworked based on javascript (through the excellent QtScript). This is undecided but given short resources we'd like to have good support for one language (with 'native' feeling) than unfinished and buggy support for 5.

Designing/documenting DB Object model for scripting has been even planned: http://community.kde.org/Kexi/Plugins/S ... ject_Model (please see the note "Scripting makes sense mostly in Qt5-based Kexi.")

I'll take a look. Are we talking about scripted events that happen in the DB or scripted events that happen in the GUI?

The topic of reusing Kexi's data definition/entry facilities and data sources in other Calligra apps has been mentioned during Calligra meetings. It looks like quite long term; Biblio DB and Mailmerge would be the first step. One particular idea is that Kexi can adjust its interface and offer a service for other apps, e.g. a table view or form for entering data for the Mailmerge. Under the mask it would be still Kexi.

For other details please look at minutes from Calligra 2011 meeting: http://community.kde.org/Calligra/Meeti ... ng/Minutes

Again, I'll take a look. Are there other competing form/report tools in Calligra?

Remaining answers:

littauer wrote:Doing #2 well implies a bidirectional on-the-fly translation from KexiSQL (a well-defined superset) to the target DBMS (API exported, of course, for reuse)

Dealing with heterogeneous back-end DBMSs is hugely difficult and requires vastly different skills from those required to do graphics. I know the Perl and PHP folks have burned a lot of gray cells trying to make it happen and I hope you're collaborating with them (or at least picking their brains).


We don't miss opportunity of reusing these ideas. However there are limits. If you refer to data access APIs of Perl or PHP, they're ... usually just data access APIs, in particular they do not address needs of integration with GUI app, and especially editors, such as Table or Query designer. For those needs, we have much deeper API, which is never needed for server-side software written using PHP or Perl.

Kexi needs to 'know' a lot more about differences between the backends than programs that only operate at data access level. We have DDL and more DML features directly in the API (and DCL is planned), the app do not hardcode any native SQL, only KexiSQL is used. KexiSQL is parsed and fully defined, what gives a chance for reliable translation to native SQL commands. The way how the parser also improves experience of the Query Designer beats many competing tools.

Yes, I'm very aware how difficult this is. This is why I worry that you're working report/form issues instead of DB issues. Perhaps it's happening behind the scenes out of sight. There has been a good amount of discussion in these groups about what is and isn't supported in the various back-ends and I was hoping you were stealing researching it.

littauer wrote:While I'm on this topic, be sure your keywords and structures have kept Oracle in mind. You won't want to start with them but you will eventually need to go there if Kexi is to have the success it deserves. One ignores the 400 kilo gorilla in the room at one's peril.


Perfectly agree, we'll more than welcome contributor and maintainer of Oracle support of any kind. Kexi 1.x oferred Oracle support but that's been turned off due to lack of maintenance. But why not after moving from KexiDB to Predicate? (actual situation of db drivers: http://community.kde.org/Kexi/KexiDB_Drivers)

I took a quick look at the Drivers code and didn't spot the translation mechanism. Perhaps I was looking in the wrong place. The point about Oracle is not whether or not you support it anytime soon (I'm hopeful I'll never again have to use it). The point was that the design needs to be able to accommodate their quirks; They're odder than the FOSS DBs.

By the way, I apologize for opening this thread in the "Help" forum; it obviously belongs in the "Discussion" forum. Feel free to move it.

littauer wrote:Please distinguish between the KexiForm GUI and the Kexi GUI. Kexi GUI (DB schema aware) needs to be able to define events. KexiForm GUI is a consumer of events.

If you mean the escape from all the ugly/error-prone "beforeUpdate" events of MS Access Forms, I cannot agree more... yes we have quite deep separation.

littauer wrote:I'll take a look. Are we talking about scripted events that happen in the DB or scripted events that happen in the GUI?

Both.

As I noted before, most of the scripted events for the DB needs proper support for selected language to perform a binding, what means for now only the SQLite backend would do that - but this alone has potential to compete or beat MS Access in terms of scripting capabilities. For other (server) backends what we can have is support for stored procedures/language extensions, whatever is supported/installed. This is unsafe and undefined area for me as long as I see the server-side scripting designed without GUI bindings in mind.

littauer wrote:Yes, I'm very aware how difficult this is. This is why I worry that you're working report/form issues instead of DB issues. Perhaps it's happening behind the scenes out of sight. There has been a good amount of discussion in these groups about what is and isn't supported in the various back-ends and I was hoping you were stealing researching it.

Kexi uses very little of the relational db features, look taht even groupping isn't supported in the API and the GUI. What's supported between SQLite, MySQL and PostgreSQL. It reportedly 'mostly' worked for Oracle backend in Kexi 1.x.

As for oddities, MySQL is very odd at places and they have never truly convinced that it's just for performance. For example it does not offer any real OID, so user needs to explicitly define primary (or at least unique) key so the GUI can reference rows if update or removal is needed. KexiDB (and thus Predicate) 'knows' this difference through a special ROW_ID_FIELD_RETURNS_LAST_AUTOINCREMENTED_VALUE flag in DriverBehaviour, so Kexi GUI can warn the user and propose addition of the primary key in case of MySQL.

littauer wrote:I took a quick look at the Drivers code and didn't spot the translation mechanism. Perhaps I was looking in the wrong place. The point about Oracle is not whether or not you support it anytime soon (I'm hopeful I'll never again have to use it). The point was that the design needs to be able to accommodate their quirks; They're odder than the FOSS DBs.

First, please note that in master and calligra/2.6 branches of Calligra, KexiDB is located in calligra/libs/db/ not within calligra/kexi/. This is a result of its reuse in Words' bibliography plugin hopefully to be added.

As for translation from a KEXISQL query to the native SQL, it's a chain like KEXISQL PARSER (libs/db/parser) -> (QuerySchema instance) -> KexiDB::selectStatement().

Opposite direction is currently performed only for migration plugins, in kexi/migration/, for example there's MySQL migration code that understands specifics of the native MySQL, discovers the schema and re-creates it using KexiDB API with all needed metadata. Currently to use database not created with Kexi we have to 'import' them using that migration facility. I am considering Predicate to just use introspection capability of the given backend. Historically that was not practiced because of rather poor introspection features of (then used) SQLite 2. Using introspection capability would give us similar feature to the "Linked tables" of MS Access relatively easily. In any case we have to care about backward compatibility.

littauer wrote:By the way, I apologize for opening this thread in the "Help" forum; it obviously belongs in the "Discussion" forum. Feel free to move it.

Done

littauer wrote:Are there other competing form/report tools in Calligra?

No there are not.