Consider shift to postgresql

[davclark]

@r03ert0 (and possibly @jbpoline) can you let me know if there's a better way to access the database than via the CSV dump? If it's a hassle, don't worry - it shouldn't be TOO much trouble to parse the CDATA blocks, just a minor nuisance.

List of SQL query locations in brainspell.php. Please put your name next to a function if you're updating it. Maybe we should also prioritize the functions that are part of the "core" functionality - querying and constructing maps:

• [ ] user_register
• [ ] user_login
• ... add remaining functions here!

[davclark]

Also - would it be useful if I set up a different postgres server for folks to do development on?

[davclark]

A few other things.

1. The CSV has no headers. Do you have the MySQL column names handy for us?
2. Looking at the structure of the data, it doesn't look that irregular. We could probably set up a more relational database that would work with MySQL or Postgres, and it'd be easy to query on, e.g., specific coordinates, or whatnot. Can you point us at the core queries that you're making against the current database?

[r03ert0]

I Dav, I'll try to make a fresh dump of the db tomorrow. There are no more CDATA blocks in the newer version (I'm traveling to a conference tomorrow morning, giving a talk at ~3pm, but I'll try to do it after that)

[davclark]

Don't feel any strong pressure here... I'll be pretty booked over the next week. As long as we maintain some momentum forwards, I think we're OK.

[r03ert0]

@davclark : the best is to discuss this with @jbpoline. He has a fresh dump of the complete brainspell's db. It would be nice to come up with a good way of structuring the downloadable data to make it easy to import, but without diffusing too many user information.

[davclark]

I've decided to back up and re-work the mysql dump to emit mostly CSV w/ minimal SQL load files, and then translate the load files to postgres. Working from "Export using CSV files" here:

https://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL

[davclark]

MySQL is locked down, we can find out where we can legally dump files like this:

# mysql -u root -p brainspell

[snip]

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

Thus (scroll to the right!):

# mysqldump -u root -p --tab=/var/lib/mysql-files --compatible=postgresql brainspell

If desired, these flags could also be used to create CSV rather than TSV files as the .txt files: --fields-terminated-by=',' --fields-optionally-enclosed-by='"'. You still use --tab, even if you're going for commas!

[davclark]

@ayoon FYI I'm working on this!