Ask Your Question
2

LibreOffice base & MySQL - database name

asked 2018-11-17 16:51:23 +0200

sscala gravatar image

updated 2018-11-17 16:53:25 +0200

When I add a databse via MySQL, all references to tables (e.g. in forms) include the database name.

It is likely that in a later stage of the project, the database will be moved to a different server and also the name of the database will probably change.

I was thinking of adding a query for each table and referece the tables always via queries within LO forms, macros, etc. That way I'd only have to do some changes to the queries "mirroring" my tables when moving the database.

What is the preferred stragety in order to keep changes after transfer to a minimum?

edit retag flag offensive close merge delete

Comments

If you aren't adding any filters or order into your forms via the dialogs, but are handling such things in your source queries (ORDER BY columnZ, WHERE columnX = 'some value'), then you can just run all your queries in Native SQL mode (where Base doesn't rewrite them), and then it won't add the database name. However, by using the native SQL method, you do indeed miss out on several nice features Base offers. It's an annoying trade-off.

PhLo gravatar imagePhLo ( 2018-11-18 05:55:14 +0200 )edit
1

Also, for those that might not know, "Analyze SQL command" in the Form Properties dialog, the "Run SQL command directly" in the query writer (appears as "SQL" icon on toolbar), and "Native SQL" are all terms for the same toggle within Base. It would be nice if they chose one term across all the appearances of the toggle. Took me a while to figure out they were the same.

PhLo gravatar imagePhLo ( 2018-11-18 06:08:26 +0200 )edit

1 Answer

Sort by » oldest newest most voted
3

answered 2018-11-17 18:06:00 +0200

Ratslinger gravatar image

Hello,

I personally prefer to keep the Schema as is ( in MySQL there are no separate databases, only schema. PostgreSQL, for example, also has separate databases). This year I have decided, for a number of reasons, to move away from MySQL/MariaDB. I have successfully moved & tested all tables/forms/queries & such to PostgreSQL, Firebird server and Firebird embedded. The major system transferred was for personal finances with a few dozen forms, and almost as many tables and queries. There are also at least a few thousand lines of macro coding which had minimal effect when moved.

I found the number of changes needed in the forms (even though they may have contained a number of sub forms) to be inconsequential. It appears in using your method, at least in my systems, I would have a lot more work in completing the effort.

After doing this three times (three different conversions) the only issue was getting correct data to Firebird embedded as there were some problems with numeric & decimal info. I actually accomplished this using Firebird server as an intermediary. Looking back, I don't think I would change my approach.

edit flag offensive delete link more

Comments

Yes, it's a DISASTER and HORRIBLE that the developers chose a query engine that automatically adds database names into the queries. I'm going through this nightmare right now. I started development on a localhost installation of MySQL via XAMPP. When I uploaded the same MySQL database to shared hosting, the database name of course changed, and it has been atrocious to find all the places the name is referenced in various forms, SQL, queries, filters, order, etc.

PhLo gravatar imagePhLo ( 2018-11-18 05:48:12 +0200 )edit

There is no easy solution either. You can use the Native SQL toggle, but then you lose functionality of being able to inject filters and orders into forms. It's pretty bad. My solution has been to rewrite most of my macros and queries to handle things differently, which takes a lot of brain power and unnecessary work. If Base didn't inject database names into queries, none of this would be a problem.

PhLo gravatar imagePhLo ( 2018-11-18 05:49:25 +0200 )edit

In addition, my database went from being nearly lightning fast and stable on localhost installation to crashing Base on nearly click and being anywhere from 5 to 10 times as long to do any small operation on the remote MySQL connection. It's SLOOOOW. Major troubleshooting needed to get MySQL running remotely. No wonder no one seems to be talking much about it online. Most help you search for will talk about a local install. :(

PhLo gravatar imagePhLo ( 2018-11-18 05:51:36 +0200 )edit

Never stated in my answer anything like 'Disaster' or 'Horrible'. In fact, it may have taken a bit of time to change some of the SQL (moving to a different DB) but the rest was fairly easy. And even that wasn't anything close to a nightmare. As mentioned, performed the move three different times (to three different DB's) and still would do it the same way. Not a problem here!

Ratslinger gravatar imageRatslinger ( 2018-11-18 05:59:42 +0200 )edit

Sorry, I should have posted my comments under the author's question instead of your answer. I wasn't referring to YOUR approach, but what HE/SHE might experience if he/she does what he/she was talking about. Sounds like you came up with a good solution that circumvented some of the problems that can occur between the local and remote changes of a MySQL situation. The DISASTER and HORRIBLE are what I'm going through to fix the database name junk.

PhLo gravatar imagePhLo ( 2018-11-18 06:01:28 +0200 )edit

Ha, seeing how you praise Postgres, and I've heard others do the same, I'll have to look into it more seriously. I just read a few comparisons between it and MySQL, and it sounds like Postgres has the upper hand in many respects.

PhLo gravatar imagePhLo ( 2018-11-18 06:26:31 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-17 16:51:23 +0200

Seen: 222 times

Last updated: Nov 17 '18