LibreOffice base & MySQL - database name

[sscala]

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?

[PhLo]

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]

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.

[Ratslinger]

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.

[PhLo]

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]

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]

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.

[Ratslinger]

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!

[PhLo]

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]

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.

[Freddy_H]

Hello all,

As I didn’t find any more recent posts regarding this issue, I’ll post here. Sorry if I’m mistaken.

I went through the same experience as OP. During my LO Base application development process I evolved from embedded data via split HSQLDB to DB front-end (MariaDB).
Also, as there are multiple copies of the application (like development version, production version, …), I encountered the “need some little changes between versions” problem (like a background color here, a label text there, database name, database user, etc …).

Then I read somewhere (on this forum, I think) that an odb file is basically an archive (kinda zip) file, so I unpacked one and started nosing around in the various files within.

As I also have some Perl knowledge, I started experimenting with a script to get into an odb file, and lo and behold, I now have a script that reads an odb file, goes over all (xml) files inside, looks for certain text strings, replaces them with other text strings and writes out a modified version of the odb file (under a different name).

Of course I have to be carefull of what strings I replace in order not to end up with a ‘broken’ output odb file, but now I can make a ‘production version’ of my LO Base application (157 changes !) in +/- 1 second instead of spending half an hour or so (and hoping I don’t miss something).

Here is an anonymized output of the script:

************************************
*      ODB File Patch              *
************************************

 Input file : 'xyz_TST.odb' ...
  - Replace 'database_DEV' with 'database_PRD' 
  - Replace '\* T E S T \*' with ' ' 
  - Replace 'usertst' with 'userprd' 
  - Replace 'ffd7d7' with 'dde8cb' 

 Member : content.xml 
  - 86 matches for 'database_DEV' replaced with 'database_PRD'.
  - 1 match for 'usertst' replaced with 'userprd'.
  -> member patched.

 Member : forms/Obj11/content.xml 
  - 2 matches for 'database_DEV' replaced with 'database_PRD'.
  - 1 match for 'ffd7d7' replaced with 'dde8cb'.
  -> member patched.

 < --- cut --->

 Member : reports/Obj71/content.xml 
  - 1 match for '\* T E S T \*' replaced with ' '.
  -> member patched.

 < --- cut --->

 Member : settings.xml 
  - 60 matches for 'database_DEV' replaced with 'database_PRD'.
  -> member patched.

 SUMMARY :
  - Output file 'xyz_OUT.odb' written.
  - Patch 'database_DEV' replaced with 'database_PRD' : 149
  - Patch '\* T E S T \*' replaced with ' ' : 6
  - Patch 'usertst' replaced with 'userprd' : 1
  - Patch 'ffd7d7' replaced with 'dde8cb' : 1
 TOTAL patches applied : 157
************************************
*           Patch ENDED            *
************************************ 

This script is now in a state that works for my personal situation, but with a little more effort I can turn it into something that anyone who can edit a parameter (text) file and run a command line script (.cmd or .sh) can use (after checking/installing perl, that is).

I’m willing to put in the effort, but I would like to know :

1. Is anyone interested in such an
   odb-patch script ? (I googled around but found nothing similar, am I really the first one to come up with something like this ?)

2. What should be ‘delivered’ where ?

only the script file and a sample parameter file ?
or also some documentation (readme) ?
and/or a sample odb file / sql dump of testdata ???

Thanks for your time reading this, and for your feedback.