[libreoffice-users] compatibility with Microsoft Access

[Jaroslaw Staniek]

On 29 January 2015 at 16:18, Tom Davies <tomcecf at gmail.com> wrote:
> Hi :)
> Sorry to say that Access is not compatible with almost any other
> database program.  Even down to the sql language under the surface of
> the Queries it is different from all the rest.
>
[..]

Hi,
While I sympathize with the sentiment, there are some points to be
reminded. Disclaimer: I am maintainer of Kexi and lib Predicate.

- There's no single SQL standard to which software agrees to align, no
level of compliance; most of the reasons are related to
legacy/historical decision, performance

- There's no official subset of SQL (as in C98 in programming for
example) of SQL that everyone tries to align to; aligning to a subset
is accidental or a result of a common sense

- For many reasons we're all using proprietary formats (does not mean
closed) for SQL language and db storage. LO Base uses HSQL with Java
by default (so HSQL's SQL), Kexi uses SQLite (so SQLite's SQL). Both
apps define own metadata on top of the database, e.g. Kexi has a XML
markup for meta-data and meta-data tables, origins of whom are in use
of SQLite 2.8. So also SQLite's SQL is used ther. All this not
standardized.

- The above influences type definitions, quite fundamental thing. Then
even character encoding in strings is influenced.

- LO Base uses (and exposes to the user) whatever SQL dialect the
backend uses; for the record Kexi has own dialect of SQL (KexiSQL)
that it internally translates to backend's dialect at the very end;
this is a behavior being a superset of that ODBC wanted to be, BTW,
even while going this way is costly in terms of development, which is
the cost of keeping sane control over the format; in case of Base when
MySQL decides to deviate from the past SQL bits (for whatever reason),
Base's "format" will be certainly affected

- There's neither a "database storage" part in the ODF specs nor the
mention of what SQL shall be used; I was involved in fixing the
connection in ODF, and that's all what we have (last I checked)

- Programming/scripting layer is super-important for desktop database
tools such as LO Base or Kexi; there's no mention of programming
language in ODF; everything is implementation-defined, at least as in
MSOOXML

- Speaking of programming, LO Base inherits from product decisions of
StarOffice so uses StarBasic to mimic the VB/VBA language, object
model and run-time, I can understand users asking for compatibility
even if just because of that.

So things are not really white-black to me. There was a room for
coordination in, say, 2004 what I inefficiently proposed to then - the
OpenOffice guys led by Sun. No result, Base was started based on Sun's
technology[1]  and the tradition of copying many of MS Office design
decisions.

So for me, no surprise users are looking at "compatibility".

PS: How we can improve? Pick small areas and define standards. For
example CSV import/export specification description format. We can do
that and more!


[1] So deeply that they decided to use Java backend and put a db file
in a ZIP container risking breaking ACID principles known for years,
and removing typical efficiency of databases (in-place writes).



-- 
regards, Jaroslaw Staniek

KDE:
: A world-wide network of software engineers, artists, writers, translators
: and facilitators committed to Free Software development - http://kde.org
Calligra Suite:
: A graphic art and office suite - http://calligra.org
Kexi:
: A visual database apps builder - http://calligra.org/kexi
Qt Certified Specialist:
: http://www.linkedin.com/in/jstaniek