Whilst I appreciated many people do not want to use mysql or even sqlite.
I find it a lot easier to update tables and data using mysql.
Any ideas please or maybe a sqlite export and mysql import etc.
The only possibility I can think of would be to use an ODBC connector for SQLite that could be accessed from mysql. I have used an ODBC connector for SQLite to access an RM database from both Microsoft Excel and Microsoft Access. However, I have used such an ODBC connector only for reporting. I don’t think I would dare to use such an ODBC connector for updating.
It’s possible but not trivial according to:
I’ve used SQL Server Integration Services (SSIS) to export to a SQL Server database. But it’s not easy.
I’ve been using SSIS regularly for nearly 20 years as part of my job and it isn’t easy to master and this wasn’t a simple import as the data types caused issues - dates etc.
Apart from a very manual exporting of the data as csv from SQLite and importing to MySQL, I don’t know another easier way and since the RM database is changing frequently, it is only worth doing if it can be automated and rerun on demand.
An additional problem that direct updates of an RM database from outside of RM run into is the RMNOCASE issue for many text fields in the database. RMNOCASE is a proprietary RM collating sequence that is similar to the standard NOCASE collating sequence except that in addition it attempts to collate non-English letters according to the way the letter looks to an English speaker. For example, it collates an Å as if it were an A and it collates an Ü as if it were a U. RMNOCASE can be a difficult but not insurmountable problem when updating the RM database from outside of RM. I have no idea what would happen with this collating sequence and MySQL.
It did pass my mind about that, as I have had similar data problems in other databases. Its OK if you only use 1 language set. I think i will drop the idea of using mysql for the time being.
Actually, RMNOCASE creates problems in RM queries from SQLite even if you don’t have any of the non-English letters in your database. I don’t understand all the technical details, but you have to have a fake RMNOCASE collation in place to run most SQLite queries against RM databases. Updates of RMNOCASE collated columns from SQLite corrupts the database level indexes for data columns that are indexed. The database level indexes therefore usually have to be rebuilt using RM’s database tools after any updates performed with SQLite.
Despite what it may sometimes sound like on these forums, I work very hard to find ways do the reporting I need inside of RM instead of using SQLite. And even when I’m using SQLite, I work very hard only to query data instead of updating data. It’s just that sometimes it’s hard to do everything I need to do from inside RM. For example, I’m working on a project right now where I need to verify that all citations of a certain type have an associated media file. It’s not very easy to find all citations without media files from within RM.
By the way, I think the whole RMNOCASE concept provides a disservice to RM users who use letters from languages other than English because it collates incorrectly for those languages. And RMNOCASE probably isn’t needed by RM users who use only English letters. So I wish that the RM database simply used the standard NOCASE collation when case insensitive collation is needed. If and when RM fully supports other languages, I hope the whole collation question is given much more attention. Many languages will need their own collation sequences, and it seems difficult to accomplish such collations at the database level because the collation thereby becomes hardwired into the database design. So I think the collation for language support might need to be at the RM application level rather than at the RM database level.
I could see an export/import, but I don’t understand why you’d want to do such a thing. Updating in MySQL is the same SQL as it is in SQLite, so I don’t see any reason my it would be “easier” to do the update.
Either way it would end up being a one way trip because you’d need to deal with the RMNOCASE (which I REALLY wish RM would get rid of because it’s a huge crippling factor) so sending the data back to SQLite in RM would be a real PITA.
What I think RM should do if they’re going to remain wedded to RMNOCASE is to put a basic SQL window into RM so those that wanted to do raw updates or queries could do it without dealing with their extension. Otherwise they should distribute the RMNOCASE extention for Windows and Mac so people wanting to use a SQLite client wouldn’t have to place games. They obviously have one, so there’s no excuse for them not to distribute it.