Compatibility between Linux and Windows based databases

Developers
1

Hi!
I develop on a WAMP server environment (under Windows) where I can easily switch back to former versions, can use my image processing programs and have easy access to my programming IDE as well as direct access to the DB tables.

I just realized, that Gibbon bears a big problem as it uses uppercase database table names, which are converted into lowercase as soon as they are loaded into a Windows environment (Windows does not support uppercase).

This works on the local installation (as Windows won’t check for uppercase) but will fail to be restored on the Linux based webserver and makes it almost impossible to exchange the data between development and release servers.

I’m in the process to setup a Linux virtual machine just for this problem and save the changes I did.

In this very old post from 2018

Sandra mentions a script to revert the table names into their original state:
‘I’ve attached an SQL snippet that when run should fix the case of your table names’

which unfortunately isn’t present anymore. Any possibility this is still around, maybe in an updated form to save me some time copying out all the original tables and writing a script?

I would highly suggest to rework the database table names for future releases, using the more accepted underline between words, as this poses a real problem possibly when migrating from Windows based IIS webservers to Linux based ones.

2

Try:
mysqldump -u root -p --all-databases --no-create-db --lower-case-table-names > dump.sql

Typically, our database is independent.

1 Like
3

Yep, I got that part. Applying ‘Find and Replace’ the whole list on a sql dump from my Windows environment for the almost 200 table names was a bit a hitch and without the right tools not really feasible. Good thing I had a something lying around which did the job in a batch.

I’m not sure what you mean with the ‘database is independent’. At the actual state it isn’t OS independent, as loading it to a Wampp server will change the uppercase letters in all lowercase. This works until you want to export the database to a Unix / Linux environment where it stops working, as there ‘gibbonaction’ is different to ‘gibbonAction’. This table should be named ‘gibbon_action’ which would be crossplatform.

4

Typically, the database should be installed and stored on a dedicated server with support for offsite backups or cold backups. When operating outside the development environment, it is not recommended to use integrated tools like WAMP. Instead, independent deployments should be used, such as Redis + MySQL (MariaDB) or Nginx + PHP-FPM, to ensure stability and maintainability.

Each application is independently virtualized to meet disaster recovery requirements. Even for smaller applications, we recommend migrating within the same environment (including the operating system and dependencies). The advantage of virtualization is its snapshot support, allowing for quick restoration when needed. Additionally, the database should be independent of the web server and, if possible, utilize cloud-based database solutions for enhanced security and scalability.

Gibbon has relatively low server requirements. If using a cloud platform, a 4-core, 8GB RAM instance can efficiently support thousands of students and faculty members. The primary cost concern is the high price of bandwidth.

It is rare to hear of Windows being used as the primary environment for web hosting (aside from IIS-based solutions). In comparison, Windows itself consumes significantly more system resources than Debian + Gibbon.

1 Like
5

Upper to Lower

#!/usr/bin/env python3
import re

pattern = re.compile(r'(?i)(CREATE TABLE\s+(?:IF NOT EXISTS\s+)?)(`?)(\w+)(`?)')

def lower_table(m): 
    return f"{m.group(1)}{m.group(2)}{m.group(3).lower()}{m.group(4)}"

try:
    with open('dump.sql', encoding='utf-8') as f:
        sql = f.read()
except Exception as e:
    print(f"Error reading dump.sql: {e}")
    exit(1)

new_sql = pattern.sub(lower_table, sql)

try: 
    with open('dump_lower.sql', 'w', encoding='utf-8') as f:
        f.write(new_sql)
except Exception as e:
    print(f"Error writing dump_lower.sql: {e}")
    exit(1)

Lower to Upper

#!/usr/bin/env python3
import re, sys
try:
    import wordninja
except ImportError:
    sys.exit("Please install wordninja: pip install wordninja")

pat = re.compile(r'(?i)(CREATE TABLE\s+(?:IF NOT EXISTS\s+)?)(`?)(gibbon)(\w*)(`?)')
def repl(m):
    prefix, bt, gibbon, rest, at = m.groups()
    new_rest = ''.join(w.capitalize() for w in wordninja.split(rest)) if rest else ''
    return f"{prefix}{bt}{gibbon}{new_rest}{at}"

try:
    sql = open('dump.sql', encoding='utf-8').read()
except Exception as e:
    sys.exit(f"Error reading dump.sql: {e}")

new_sql = pat.sub(repl, sql)

try:
    open('dump_camel.sql', 'w', encoding='utf-8').write(new_sql)
except Exception as e:
    sys.exit(f"Error writing dump_camel.sql: {e}")
1 Like
6

That’s about what I did, Python to the rescue!

Nevertheless, migrating from the development environment to the webserver should be possible without having to do this.

There is this other thing, called IIS and I’m quit sure, trying to migrate from there to Unix / Linux bases servers would yield the same problem.

1 Like
7

@Xavier_Bit Glad to hear you got it working, and thanks @AndroidOL for the excellent information and suggestions. If you are planning to work between a Windows environment for development and a live server, I would certainly recommend setting lower_case_table_names off to start. We can look at adding Windows-specific instructions to our docs, which could certainly help in the future.

8

I’ve added this to the Installing on Windows page in the docs.

9

Hi Sandra!

I tried the ‘lower_case_table_names off’ but with no success whatsoever. I came to the point where it messed up my WAMP installation so hard, I had to reinstall it.

It seems Windows is simply not able to differentiate between lower / uppercase table names reliably and it will create more problems down the line when using this settings for other installations.

I’m hoping you could rename those tables into lowercase and re-arrange the code for a future release as it would really help WAMP users developing locally and restoring it more or less directly to the server.

:slight_smile:

10

Hi @Xavier_Bit I appreciate your situation. It is not going to be possible to rename all 200+ tables in the Gibbon database for the sake of WAMP local development, the Gibbon codebase is massive with 15 years of development, and the case-sensitive table names are very much hard-coded into the system. As @AndroidOL mentioned, Gibbon is designed to be installed on a webserver, and unfortunately if lower_case_table_names isn’t working, it sounds like Windows isn’t well suited to this case.

11

Hi Sandra,

That’s all good for me, I got my ways around it and I just thought it would be an idea. But if it isn’t feasible, no problem.

1 Like
12

Hi!

For anybody running into the same problem, developing on a Windows system and deploying it to a Linux webserver, I created a handy list with the Gibbon default tables and their counterpart in ‘camel case’.

Additionally I have added the table names for following extensions:

  • Help Desk
  • Credentials
  • Data Admin

It seems the nomenclature for extensions do not follow the Gibbon naming convention (they don’t start with ‘gibbon…’, which makes it harder to change them programmatically. After some meager success using a Python script, trying to guess the correct ‘camel case’, I started using a batch renamer tool (in my case ‘Text Crawler’ but there are others available), where I can feed this list (without titles) directly into it which will find and replace all the table names in the .sql export file.

You can find the list here: Gibbon 28.0.01 – Default DB tables Wampserver DB table names Linux server D - Pastebin.com

I hope this helps all fellow WAMP / XAMPP users!

1 Like
13

Hi @Xavier_Bit , thank you for sharing this much handy list. Hopefully, it can help other Gibbon users who run into similar problem. Cheers!