This code initializes a MySQL connection using credentials from a JSON file and defines several functions to interact with the database, including executing queries, showing tables, parsing data types, and selecting objects. The functions are designed to parse and manipulate data in a MySQL database, with features such as filtering out sensitive columns and converting data types.

Cell 0

var mysql = require('mysql');
var importer = require('../Core');
var fs = require('fs');
var path = require('path');
var https = require('https');

var PROFILE_PATH = process.env.HOME || process.env.HOMEPATH || process.env.USERPROFILE;
var project = PROFILE_PATH + '/Documents/studysauce4/data';
var filename = path.join(project, 'cleaned.json');
var credentials = path.join(PROFILE_PATH, 'credentials/studysaucedb.json');

var con = mysql.createConnection(fs.readFileSync(credentials).toString());

function mysqlQuery(q) {
    return new Promise((resolve, reject) =>
        con.query(q, (err, result) => {
            if (err) reject(err);
            console.log(result);
            resolve(result);
        }));
};

function showTables() {
    return mysqlQuery('SHOW TABLES')
        .then(result => result.map(r => r[Object.keys(r)[0]])
            .filter(r => r.indexOf('acl_') === -1
                && r.indexOf('rememberme') === -1
                && r.indexOf('session') === -1
                && r.indexOf('mail') === -1
                && r.indexOf('visit') === -1));
};

var parseBool = r => r === 'true' || r === '0' || parseInt(r) > 0;

var parseString = r => r + '';

function describeTable(table) {
    return mysqlQuery('DESCRIBE ' + table)
        .then(fields => {
            var obj = {};
            fields.forEach(f => {
                var parse = parseString;
                if (f.Type === 'tinyint(1)')
                    return obj[f.Field] = parseBool;
                if (f.Type.indexOf('int') > -1)
                    return obj[f.Field] = parseInt;
                if (f.Type.indexOf('datetime') > -1)
                    return obj[f.Field] = Date.parse;
                obj[f.Field] = parse;
            })
            return obj;
        })
};

function selectObjects(table, descriptor, query = '') {
    return mysqlQuery('SELECT * FROM ' + table + ' ' + query)
        .then(rows => rows.map(r => {
            delete r['password'];
            delete r['salt'];
            delete r['confirmation_token'];
            if (typeof r['coupon_id'] !== 'undefined') {
                r['bundle_id'] = r['coupon_id'];
                delete r['coupon_id'];
            }
            if (typeof r['options'] !== 'undefined') {
                r['properties'] = r['options'];
                delete r['options'];
            }
            if (typeof r['properties'] !== 'undefined') {
                r['properties'] = unserialize(r['properties']);
            }
            return r;
        }));
};

$.async();
var tables, unserialize;
new Promise((resolve, reject) => https.get(
    'https://raw.githubusercontent.com/naholyr/js-php-unserialize/master/php-unserialize.js',
    (res) => {
        var data = '';
        res.on('data', (d) => {
            data += d.toString();
        });
        res.on('end', () => {
            resolve(data);
        });
    }).on('error', (e) => {
    reject(e);
}))
    .then(r => {
        unserialize = importer.runInNewContext(r + '\rmodule.exports = exports;\n', {
            __filename: '//raw.githubusercontent.com/naholyr/js-php-unserialize/master/php-unserialize.js',
            exports: {}
        }, {}, false).unserialize;
        return new Promise((resolve, reject) => con.connect((err) => {
            if (err) reject(err);
            console.log('Connected!');
            resolve();
        }))
    })
    .then(() => showTables())
    .then(tables => {
        var results = {};
        return importer.runAllPromises(tables
            .map(t => describeTable(t)
                .then(descriptor => selectObjects(t, descriptor))
                .then(rows => results[t
                    .replace('ss_', '')
                    .replace('coupon', 'bundle')] = rows)))
            .then(() => results)
    })
    .then(result => {
        fs.writeFileSync(filename, JSON.stringify(result, null, 4));
        $.sendResult(result)
    })
    .then(result => $.sendResult(result))
    .catch(e => $.sendError(e))

// Import required modules
const mysql = require('mysql2/promise'); // Use promise version of mysql driver
const importer = require('../Core');
const fs = require('fs');
const path = require('path');
const https = require('https');

// Define constants
const HOME = process.env.HOME || process.env.HOMEPATH || process.env.USERPROFILE;
const PROFILE_PATH = HOME;
const project = path.join(PROFILE_PATH, 'Documents/studysauce4/data');
const filename = path.join(project, 'cleaned.json');
const credentialsPath = path.join(PROFILE_PATH, 'credentials/studysaucedb.json');

// Load database credentials
const credentials = fs.readFileSync(credentialsPath, 'utf8');
const credentialsObject = JSON.parse(credentials);

// Connect to database
const con = mysql.createConnection({
  host: credentialsObject.host,
  port: credentialsObject.port,
  user: credentialsObject.user,
  password: credentialsObject.password,
  database: credentialsObject.database
});

// Query database
async function queryDatabase(q) {
  try {
    const result = await con.execute(q);
    console.log(result);
    return result;
  } catch (err) {
    throw err;
  }
}

// Get tables
async function getTables() {
  try {
    const result = await queryDatabase('SHOW TABLES');
    return result.map(r => r[Object.keys(r)[0]])
     .filter(r => r.indexOf('acl_') === -1
        && r.indexOf('rememberme') === -1
        && r.indexOf('session') === -1
        && r.indexOf('mail') === -1
        && r.indexOf('visit') === -1);
  } catch (err) {
    throw err;
  }
}

// Parse boolean values
function parseBoolean(value) {
  return value === 'true' || value === '0' || parseInt(value) > 0;
}

// Parse string values
function parseString(value) {
  return value + '';
}

// Describe table
async function describeTable(table) {
  try {
    const fields = await queryDatabase(`DESCRIBE ${table}`);
    const obj = {};
    fields.forEach(f => {
      const parse = parseString;
      if (f.Type === 'tinyint(1)') return obj[f.Field] = parseBoolean;
      if (f.Type.indexOf('int') > -1) return obj[f.Field] = parseInt;
      if (f.Type.indexOf('datetime') > -1) return obj[f.Field] = Date.parse;
      obj[f.Field] = parse;
    });
    return obj;
  } catch (err) {
    throw err;
  }
}

// Select objects
async function selectObjects(table, descriptor, query = '') {
  try {
    const rows = await queryDatabase(`SELECT * FROM ${table} ${query}`);
    return rows.map(r => {
      delete r['password'];
      delete r['salt'];
      delete r['confirmation_token'];
      if (typeof r['coupon_id']!== 'undefined') {
        r['bundle_id'] = r['coupon_id'];
        delete r['coupon_id'];
      }
      if (typeof r['options']!== 'undefined') {
        r['properties'] = r['options'];
        delete r['options'];
      }
      if (typeof r['properties']!== 'undefined') {
        r['properties'] = unserialize(r['properties']);
      }
      return r;
    });
  } catch (err) {
    throw err;
  }
}

// Unserialize data
function unserialize(data) {
  // Load unserializer from GitHub
  return new Promise((resolve, reject) => {
    https.get('https://raw.githubusercontent.com/naholyr/js-php-unserialize/master/php-unserialize.js', (res) => {
      let data = '';
      res.on('data', (d) => {
        data += d.toString();
      });
      res.on('end', () => {
        const unserializer = require(data);
        resolve(unserializer.unserialize);
      });
    }).on('error', (e) => {
      reject(e);
    });
  });
}

// Main function
async function main() {
  try {
    // Load unserializer
    const unserializeFunc = await unserialize();

    // Get tables
    const tables = await getTables();

    // Describe and select objects for each table
    const results = {};
    await Promise.all(tables.map(async (table) => {
      const descriptor = await describeTable(table);
      const rows = await selectObjects(table, descriptor);
      results[table.replace('ss_', '').replace('coupon', 'bundle')] = rows;
    }));

    // Write results to file
    fs.writeFileSync(filename, JSON.stringify(results, null, 4));

    // Send result
    $.sendResult(results);
  } catch (err) {
    $.sendError(err);
  }
}

// Run main function
main();

Code Breakdown

Importing Modules

• var mysql = require('mysql');: Imports the MySQL module.
• var importer = require('../Core');: Imports the importer module from the Core directory.
• var fs = require('fs');: Imports the File System module.
• var path = require('path');: Imports the Path module.
• var https = require('https');: Imports the HTTPS module.

Setting Environment Variables and Paths

• var PROFILE_PATH = process.env.HOME || process.env.HOMEPATH || process.env.USERPROFILE;: Sets the PROFILE_PATH variable to the user's profile directory, using environment variables.
• var project = PROFILE_PATH + '/Documents/studysauce4/data';: Sets the project variable to the path of the project directory.
• var filename = path.join(project, 'cleaned.json');: Sets the filename variable to the path of a JSON file named cleaned.json in the project directory.
• var credentials = path.join(PROFILE_PATH, 'credentials/studysaucedb.json');: Sets the credentials variable to the path of a JSON file named studysaucedb.json in the user's profile directory.

Creating a MySQL Connection

• var con = mysql.createConnection(fs.readFileSync(credentials).toString());: Creates a MySQL connection using the credentials from the studysaucedb.json file.

Defining Functions

mysqlQuery

• function mysqlQuery(q) {... }: Defines a function that executes a MySQL query and returns a Promise.
• The function takes a query string q as an argument.
• It executes the query using the con.query() method and resolves the Promise with the query result.
• If an error occurs, it rejects the Promise with the error.

showTables

• function showTables() {... }: Defines a function that shows the tables in the MySQL database.
• It calls the mysqlQuery() function with the query SHOW TABLES.
• It then filters the result to exclude certain tables (e.g. acl_, rememberme, session, etc.).

parseBool and parseString

• var parseBool = r => r === 'true' || r === '0' || parseInt(r) > 0;: Defines a function that parses a string as a boolean value.
• var parseString = r => r + '';: Defines a function that converts a value to a string.

describeTable

• function describeTable(table) {... }: Defines a function that describes a table in the MySQL database.
• It calls the mysqlQuery() function with the query DESCRIBE <table>.
• It then parses the result to create an object with column names as keys and parsing functions as values.

selectObjects

• function selectObjects(table, descriptor, query = '') {... }: Defines a function that selects objects from a table in the MySQL database.
• It calls the mysqlQuery() function with the query SELECT * FROM <table> <query>.
• It then maps the result to remove sensitive columns (e.g. password, salt, confirmation_token) and returns the result.