Humble suggestion: why not make EA file default database SQLite instead of Acces

[Richard Freggi]

Hope I don't get in trouble for this but as I'm learning SQL queries on the scratchpad I'm just wondering why put up with the quirks and limitations of Access/Jet and not base the *.eap file format on the gloriously simple, efficient and 110% ANSI-SQL standard SQlite instead?  After all many hugely popular systems use it.   Just a thought for future EA releases?

[Eve]

My understanding is that "gloriously simple" == "simplistic".

It's perfect for storing program settings etc, but it doesn't support some of the basic functionality that EA needs.

[Paolo F Cantoni]

My understanding is that "gloriously simple" == "simplistic".

It's perfect for storing program settings etc, but it doesn't support some of the basic functionality that EA needs.

NOTE: Eve is using the word simplistically correctly (as in overly simple, such that it is not fit for purpose) in this context. However, please don't take the equation as correct.  Gloriously simple (the phrase) and Simplistic (the term) are NOT equivalent.  It is a common mistake to equate them. 

And, Richard, you can't be 110% compliant (at least not without an emoji) 

Paolo

[Richard Freggi]

My understanding is that "gloriously simple" == "simplistic".

It's perfect for storing program settings etc, but it doesn't support some of the basic functionality that EA needs.

Recommend to take a look at the SQLite documentation, its features are impressive

[qwerty]

I can tell blindly who's in front of me if he's arguing that MSAccess is better than SQLite.

q.

[Eve]

I don't think anyone is arguing that Access is better than SQLite, but I would say there are likely use cases where Access is the better choice.

I believe SQLite was investigated before we added Firebird as an alternate local db type.

Paolo, I think I was missing "in this case" and an emoji. It was a little tongue in cheek reference to the previous discussion on the topic.

[Paolo F Cantoni]

Last night, on Four Corners (ABC TV Current affairs programme), I heard early on, Brexit was described by some (Brexiteer) politician as "Gloriously Simple".

As the Brits have discovered, that's turned out to be rather a simplistic view.

Paolo

[timoc]

For the hell of it i did a minor port of the latest Database Schema to SQLite3 to work with EA 12. The schema port i semi automated with scripts i dug up, based it on the PostgreSQL schema i think. It was pretty straightforward. I had it kinda working as a proof of concept behind an ODBC endpoint, but i dumped it in favor of running a portable PostgreSQL.

So i was reasonably sure it would work.  Now i know a bit better. The database engine probably needs to support the SQL/XML extension for some of the newer features to work, or the database schema to have the XML pieces refactored/flattened out into records.

I would +1 SQLite as a replacement for the Access database, and as a way to preview schema changes with the community (e.g. refactored/flattened out XML). That way at least the database can evolve alongside the product. Not sure if it can be used as a multi-user database, but as there is SQLDiff tooling as part of the SQLite toolchain, that should not be an issue. All single user databases can be diffed against one another - model baselines as files, branch and merge should also be simpler.