Serializes an object into an object that can be stored in a SQLite table, as defined by columnDefinitions.
@private
550027
function serialize (value, columnDefinitions) {

    var serializedValue = {};

    try {
        Validate.notNull(columnDefinitions, 'columnDefinitions');
        Validate.isObject(columnDefinitions);
        
        Validate.notNull(value);
        Validate.isObject(value);

        for (var property in value) {

            var columnType = storeHelper.getColumnType(columnDefinitions, property);
            // Skip properties that don't match any column in the table 
            if (!_.isNull(columnType)) {
                serializedValue[property] = serializeMember(value[property], columnType);
            }
        }
        
    } catch (error) {
        throw new verror.VError(error, 'Failed to serialize value ' + JSON.stringify(value) + '. Column definitions: ' + JSON.stringify(columnDefinitions));
    }

    return serializedValue;
}
900353
def save_object_to_db(self,
                          obj: Any,
                          table: str,
                          fieldlist: Sequence[str],
                          is_new_record: bool) -> None:
        """"""
        if is_new_record:
            pkvalue = getattr(obj, fieldlist[0])
            if pkvalue is None:
                self.insert_object_into_db_pk_unknown(obj, table, fieldlist)
            else:
                self.insert_object_into_db_pk_known(obj, table, fieldlist)
        else:
            self.update_object_in_db(obj, table, fieldlist)
1149970
function serializeRefs(k,v, clientData) {
  if (k == 'clientTemplates') {  //avoid circular refs but doesn't seem to work??
    return undefined;
  }
  if (v && typeof v === 'object') {
    //we have to do it like this to avoid toString being call internally
    var copy = new v.constructor();
    for (var prop in v) {
      if (!v.hasOwnProperty(prop)) continue;
      var val = v[prop]
      if (val && val._id) {
        var obj = val;
        clientData[obj._id] = _.extend(clientData[obj._id] || {}, obj);
        val = "@@@" + obj._id+ "@@@";
      }
      copy[prop] = val;
    }
    return copy;
  }
  return v;
}
557418
function serializeKey (serviceId, tableIndex) {
  let buffer = []
  Uint16.serialize(serviceId, buffer, buffer.length)
  Uint16.serialize(tableIndex, buffer, buffer.length)
  return buffer
}
650122
function insertObject (table, object, fields) {
  var input = this

  fields.forEach(function addField (field) {
    var cells = {}
    cells[field] = getCellContent.call(input, object.fields[field])
    table.push(cells)
  })
}
670870
function(ident, defFunc) {

	/**
	 * Here's the actual class we're making.
	 */
	var model = function(params) {
		var me = this;

		if (Object.keys(model.columns).length == 0) {
			logger.error("Attempting to initialize `" + model.ident + "` instance before filling schema!");
			return null;
		} else if (model.primary === undefined) {
			logger.error("No primary key found for `" + model.ident + "`!");
			return null;
		}

		Object.defineProperty(me, 'values', {
			value: {},
			enumerable: false,
			writable: true,
			configurable: true,
		}); // Raw column values, accessed by setter/getter functions.

		params = params || {};

		/* --- Instance Methods --- */

		/**
		 * Saves the object's properties corresponding to table columns into the database.
		 */
		function save() {
			var act = new NobleMachine(function() {
				setStrs = [];

				for (var key in model.columns) {
					if ([model.primary].indexOf(key) == -1 && me[key] !== undefined) {
						setStrs.push('`' + key + '`' + " = " + nrutil.serialize(me[key]));
					}
				}

				if (model.columns['updatedAt'])
					setStrs.push('`updatedAt` = NOW()');

				var sql;
				if (me[model.primary]) {
					sql = "UPDATE " + model.table
							+ " SET " + setStrs.join(',')
							+ " WHERE `" + model.primary + "` = " + me[model.primary];
				} else {
					if (model.columns['createdAt'])
						setStrs.push('`createdAt` = NOW()');

					sql = "INSERT"
							+ " INTO " + model.table
							+ " SET " + setStrs.join(',');
				}


				act.toNext(db_query(sql));
			});

			act.next(function(result) {
				//log.debug("RESULT: " + JSON.stringify(result));

				if (result.insert_id) {
					me[model.primary] = result.insert_id;
				} else if (!me[model.primary]) {
					act.toError("No insert_id, and no extant primary key!");
					return;
				}

				
				logger.log("Successfully saved `" + model.ident + "` record `" + me[model.primary] + "`");

				act.toNext(me);
			});

			act.error(function(err) {
				logger.error("Error saving `" + model.ident + "` record: " + JSON.stringify(err));
				act.emitError(err);
			});

			return act;
		}

		/**
		 * Reloads the object's properties with the current database values.
		 */
		function reload() {
			var params = {}

			params[model.primary] = me[model.primary]
		
			var act = new NobleMachine(model.find(params));

			act.next(function(newInst) {
				if (newInst) {
					for (var col in model.columns) {
						me[col] = newInst[col];
					}

					act.toNext(me);
				} else {
					for (var key in model.columns) {
						me[key] = undefined
					}

					act.toNext(null);
				}
			});

			return act;
		}

		/**
		 * Removes this object's corresponding database row.
		 */
		function destroy() {
			var act = new NobleMachine(function() {
				if (me.onDestroy) {
					var preact = me.onDestroy();
					if (preact && preact.start instanceof Function) {
						act.toNext(preact)
						return;
					}
				}
			});

			act.next(function() {
				var sql = "DELETE FROM " + model.table
						+ " WHERE `" + model.primary + "` = " + me[model.primary];

				act.toNext(db_query(sql));
			});

			act.next(function() {
				logger.log("Successfully deleted `" + model.ident + "` record `" + me[model.primary] + "`");

				for (var param in me) {
					delete me[param];	
				}
			});

			act.error(function(err) {
				logger.error("Error deleting `" + model.ident + "` record: " + JSON.stringify(err));
				act.emitError(err);
			});

			return act;
		}

		// Generic setter for SQL-correspondent values. Forces typecasting for database compatibility.
		function setValue(key, val) {
			//util.log(key + ": " + JSON.stringify(val));

			if (val === null) {
				me.values[key] = null;
			} else if (val === undefined) {
				me.values[key] = undefined;
			} else {
				type = model.columns[key]['DATA_TYPE'];

				switch (type) {
					case 'datetime':
					case 'timestamp':
						me.values[key] = new Date(val);
						break;
					case 'text':
					case 'varchar':
						me.values[key] = val.toString();
						break;
					case 'int':
					case 'tinyint':
						if (model.columns[key]['COLUMN_TYPE'] == 'tinyint(1)') {
							me.values[key] = !!val // Boolean
						} else {
							me.values[key] = parseInt(val);
						}
						break;
					default:
						me.values[key] = val;
				}
			}
		}

		// Generic getter for SQL-correspondent values.
		function getValue(key) {
			return me.values[key];
		}

		for (var key in model.columns) {
			me.__defineSetter__(key, setValue.bind(me, key));
			me.__defineGetter__(key, getValue.bind(me, key));

			if (params[key] !== undefined) {
				setValue(key, params[key]);
			}
		}

		_.extend(me, {
			save: save,
			reload: reload,
			destroy: destroy,
			setValue: setValue
		});

		if (defFunc !== undefined) {
			defFunc(me);
		}

		return me;
	}

	// This should be a unique, proper-cased identifier for the model as a whole.
	model.ident = ident;

	// Will be filled with INFORMATION_SCHEMA data.
	model.columns = {};

	// Guess the table name based on clever pluralization of the identifier.
	model.table = 'tbl' + ident.pluralize();

	/* --- Definition Methods --- */

	// Use this to override the default guess based on ident pluralization.
	model.setTableName = function(name) {
		model.table = name;
	}

	model.hasOne = function(foreignIdent) {
		model.prototype["get" + foreignIdent] = function() {
			var foreign = Models[foreignIdent];

			var opts = {}
			opts[model.primary] = this[model.primary];
			return foreign.find(opts);
		}
	}

	model.belongsTo = function(foreignIdent) {
		model.prototype["get" + foreignIdent] = function() {
			var foreign = Models[foreignIdent];

			var opts = {}
			opts[foreign.primary] = this[foreign.primary];
			return foreign.find(opts);
		}
	}

	/* --- Retrieval Methods --- */

	/**
	 * Executes a SELECT query on the table associated with this class and returns
	 * the result.
	 * @param where Qualifier to be passed to WHERE e.g "`this` = 'that'""
	 * @param fields Fields to return. Defaults to '*'.
	 */
	model.select = function(where, fields) {
		fields = fields || '*';

		var act = new NobleMachine(function() {
			var sql = "SELECT " + fields + " FROM " + model.table +
					  (where.length ? " WHERE " : '') + where + ";"

			act.toNext(db_query(sql));
		});

		act.next(function(result) {
			if (result.affected_rows == -1)
				act.toNext([]);
			else
				act.toNext(result.map(function(sqlobj) {
					return new model(sqlobj);
				}));
		});

		act.error(function(err) {
			logger.error("Error executing select statement for `" + model.ident +"`: " + JSON.stringify(err));
			act.emitError(err);
		});

		return act;
	}

	/**
     * A very simple SELECT query wrapper that returns a list of model instances.
	 * @param params Key-value pairs to be matched.
	 * @param conjunction Default 'AND'.
	 * @param fields Fields to return. Default in select().
     */
	model.where = function(params, conjunction, fields) {
		conjunction = conjunction || 'AND'
		var where = '';
		var first = true;

		logger.log("Finding all `" + model.ident + "`" + (params === undefined ? '' : " matching: " + util.inspect(params) + " with conjunction '" + conjunction + "'"));

		if (params) {
			for (var key in model.columns) {
				if (params[key] !== undefined) {
					where += (first ? '' : " " + conjunction + " ")
					if (params[key] == null) {
						where += "`" + key + "` IS NULL";
					} else {
						where += "`" + key + "` = " + nrutil.serialize(params[key]);
					}

					first = false;
				}
			}
		}

		return model.select(where, fields);
	}

	/**
	 * Emits first result from where() query
	 * @param params Params for find().
	 */
	model.find = function(params) {
		var act = new NobleMachine(function() {
			act.toNext(model.where(params));
		});

		act.next(function(objs) {
			act.toNext(objs.length ? objs[0] : null);
		});

		return act;
	}

	/**
	 * Retrieves every record of this type!
	 */
	model.all = function() {
		return model.where({});
	}

	/* --- Initialization Methods --- */

	/**
	 * This action queries the database to determine the structure of the table we are bound to.
	 */
	model.fillSchema = function() {
		var act = new NobleMachine(function() {
			var sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"
					+ " WHERE TABLE_SCHEMA = '" + common.config.database.options.database + "'"
					+ " AND TABLE_NAME = '" + model.table + "';";

			act.toNext(db_query(sql));
		});

		act.next(function(res) {
			if (res.length == 0) {
				act.toError("No columns found for table `" + model.table + "`.");
				return;
			}

			res.forEach(function(col) {
				model.columns[col['COLUMN_NAME']] = col;

				if (col['COLUMN_KEY'] == 'PRI') { // Primary key!
					model.primary = col['COLUMN_NAME'];
				}
			});

			if (!model.primary) {
				act.toError("Could not detect primary key for table `" + model.table + "`.");
				return;
			}
		});

		return act;
	}

	Models[model.ident] = model;

	return model;
}
1058960
function (model) {
            this._super(arguments);
            var ctiKey = this.__ctiKey = model.__ctiKey;
            this.__ctiTables = model.__ctiTables.slice();
            this.__ctiModels = model.__ctiModels.slice();
            this.__ctiModels.push(this);
            this.__ctiTables.push(this.tableName);
            this.__ctiColumns = comb.merge({}, model.__ctiColumns);
            this.__ctiColumns[this.tableName] = this.columns;
            this.__ctiBaseModel = model.__ctiBaseModel;
            //copy over our schema
            var newSchema = comb.merge({}, this.__schema);
            var schemas = model.__ctiModels.map(
                function (m) {
                    return m.schema;
                }).reverse();
            schemas.forEach(function (s) {
                for (var i in s) {
                    newSchema[i] = s[i];
                }
            }, this);
            this._setSchema(newSchema);
            this._setDataset(model.dataset.join(this.tableName, this.primaryKey));
            this._setPrimaryKey(this.__ctiBaseModel.primaryKey);
            this.pre("save", function (next) {
                if (ctiKey) {
                    this[ctiKey] = this.tableName.toString();
                }
                next();
            });
        }
560149
public void addObject(final Object[] _row)
        throws SQLException
    {
        // store the ids also
        this.idList.add((Long) _row[0]);

        if (getFromSelect() != null) {
            final int column = "id".equals(this.valueSelect.getValueType())
                            ? this.valueSelect.getColIndexs().get(0) : 2;
            this.relIdList.add((Long) _row[column - 1]);
            // this means that it is a chained LinkFromSelect, but exclude
            // AttributeSets
            if (!getSelectParts().isEmpty() && getSelectParts().get(0) instanceof LinkFromSelect.LinkFromSelectPart
                            && !(((LinkFromSelect.LinkFromSelectPart) getSelectParts().get(0)).getType()
                                            instanceof AttributeSet)) {
                this.idList.set(this.idList.size() - 1, (Long) _row[1]);
            }
        }
        Object object = null;
        final AbstractValueSelect tmpValueSelect;
        if (this.valueSelect == null) {
            tmpValueSelect = this.fromSelect.getMainOneSelect().getValueSelect();
        } else {
            tmpValueSelect = this.valueSelect;
        }
        if (tmpValueSelect.getParentSelectPart() != null) {
            tmpValueSelect.getParentSelectPart().addObject(_row);
        }

        if (tmpValueSelect.getColIndexs().size() > 1) {
            final Object[] objArray = new Object[tmpValueSelect.getColIndexs().size()];
            int i = 0;
            for (final Integer colIndex : tmpValueSelect.getColIndexs()) {
                objArray[i] = _row[colIndex - 1];
                i++;
            }
            object = objArray;
        } else if (tmpValueSelect.getColIndexs().size() > 0) {
            object = _row[tmpValueSelect.getColIndexs().get(0) - 1];
        }
        this.objectList.add(object);
    }
45525
function dataToObjects (data) {
  if (!data) return null
  if (data instanceof Array) {
    return data.map(function(doc) { 
      return doc.toObject()
    })
  }
  return data.toObject()
}
461392
@Override
  public Writable serialize(Object obj, ObjectInspector objInspector)
    throws SerDeException {
    StringBuilder sb = new StringBuilder();
    try {

      StructObjectInspector soi = (StructObjectInspector) objInspector;
      List<? extends StructField> structFields = soi.getAllStructFieldRefs();
      assert (columnNames.size() == structFields.size());
      if (obj == null) {
        sb.append("null");
      } else {
        sb.append(SerDeUtils.LBRACE);
        for (int i = 0; i < structFields.size(); i++) {
          if (i > 0) {
            sb.append(SerDeUtils.COMMA);
          }
          appendWithQuotes(sb, columnNames.get(i));
          sb.append(SerDeUtils.COLON);
          buildJSONString(sb, soi.getStructFieldData(obj, structFields.get(i)),
            structFields.get(i).getFieldObjectInspector());
        }
        sb.append(SerDeUtils.RBRACE);
      }

    } catch (IOException e) {
      LOG.warn("Error generating json text from object.", e);
      throw new SerDeException(e);
    }
    return new Text(sb.toString());
  }
1282566
function convertData(objectBody, objectChange) {
    const keysChange = Object.keys(objectChange);
    let objectReturn = {};
    keysChange.map(keyChange => {
        let changeOption = objectChange[keyChange];
        let valueKeyReturn = getValueChangeOption(objectBody, keyChange, changeOption, objectReturn);
        if(valueKeyReturn !== undefined && valueKeyReturn !== null){
            objectReturn[keyChange] = valueKeyReturn;
        }
        return keyChange;
    });
    return objectReturn;
}
332714
function convertObject (schema, self) {
  var keys = Object.keys(schema);
  var ret = {};

  for (var i = 0; i < keys.length; ++i) {
    var key = keys[i];
    ret[key] = convertElement(schema[key], self);
  }

  return ret;
}
86193
function __serializeArray(data, options=DEFAULT_OPTIONS) {
	let dataBuffers = [];
	// ignore undefined value
	for (let key in data) {
		let subData = data[key];
		let subType = __getType(subData, options);
		let subTypeBuffer = __serializeType(subType);
		let subDataBuffers = __serializeData(subType, subData, options);
		dataBuffers.push(subTypeBuffer, ...subDataBuffers);
	}
	let length = __getLengthByArrayBuffers(dataBuffers);
	let lengthData = new Uint32Array([length]);
	return [lengthData.buffer, ...dataBuffers];
}
390623
function Library(sqliteHelper, opts) {
    const query = sqliteHelper.query;
    const run = sqliteHelper.run;
    const self = this;
    opts = opts || {autosave: true};

    /**
     * Enables or disables automatically saving after db change
     * @param autosave {boolean}
     */
    this.autosave = function(autosave) {
        opts.autosave = autosave;
    };

    /**
     * Manually persists all changes in the db to the disk
     */
    this.commit = function() {
        sqliteHelper.save();
    };

    /**
     * Saves the database if autosave is enabled
     */
    const save = function() {
        if(opts.autosave) {
            sqliteHelper.save();
        }
    };

    /**
     * A utility to perform insert+update operations.
     * Insert failures are ignored.
     * Update failures are thrown.
     *
     * @param table {string}
     * @param params {{}} keys must be valid column names
     * @param unique {[]} a list of unique columns on this table. This should be a subset of params
     * @returns {int} the id of the inserted/updated row or -1
     */
    const insertOrUpdate = function(table, params, unique) {
        unique = unique || [];

        let columns = _.keys(params);
        let whereStatements = _.map(unique, function(c) { return c + '=:' + c});
        let updatedColumns = _.map(_.filter(columns, function(c) { return unique.indexOf(c) }), function(c) { return c + '=:' + c });
        let insertHolders = _.map(columns, function(k) { return ':' + k; });

        run('insert or ignore into ' + table + ' (' + columns.join(', ') + ') values (' + insertHolders.join(', ') + ')', params);
        run('update or fail ' + table + ' set ' + updatedColumns.join(', ') + ' where ' + whereStatements.join(' and '), params);

        save();

        // TRICKY: in this module we are strictly dealing with inserts or *full* updates.
        // Therefore we can rely on the existence of the unique columns to retrieve the id
        let result = query('select id from ' + table + ' where ' + whereStatements.join(' and ') + ' order by id asc limit 1', params);
        return firstId(result);
    };

    /**
     * Performs an empty validation check on an object or scalar.
     * This should only be used to validate string properties
     * since invalid integers values are handled by the db schema.
     *
     * @param obj {{}|string} the object or string to be validated
     * @param required_props {[string]} an array of required properties. default is all properties in obj. If obj is a scalar this should be the name of the property for reporting purposes.
     */
    const validateNotEmpty = function(obj, required_props) {
        if(typeof obj === 'object') {
            required_props = required_props || Object.keys(obj);
            for (let prop of required_props) {
                if (obj[prop] === undefined || obj[prop] === null || obj[prop] === '') {
                    throw new Error('Missing required property "' + prop + '"');
                }
            }
        } else if(required_props && required_props.length > 0) {
            throw new Error('Missing required property "' + required_props + '"');
        } else if(obj === undefined || obj === null || obj === '') {
            throw new Error('The value cannot be empty');
        }
    };

    /**
     * A tool to remove all data from a table.
     * Use this with caution.
     * @param table string the table that will lose all it's data
     */
    const truncateTable = function(table) {
        if(typeof table !== 'string') throw new Error('Table must be a string');
        run('delete from ' + table);
    };

    /**
     * Cleans the database.
     * This eliminates free pages, aligns table data to be contiguous,
     * and otherwise cleans up the database file structure.
     */
    const vacuum = function() {
        try {
            run('vacuum');
        } catch (err) {
            console.log(err);
        }
    };

    /**
     * Inserts or updates a source language in the library.
     *
     * @param language {{}}
     * @returns {int} the id of the source language row
     * @throws {Error}
     */
    this.addSourceLanguage = function(language) {
        validateNotEmpty(language, ['slug', 'name', 'direction']);
        return insertOrUpdate('source_language', {
            slug: language.slug,
            name: language.name,
            direction: language.direction
        }, ['slug']);
    };

    /**
     * Inserts or updates a target language in the library.
     *
     * Note: the result is boolean since you don't need the row id. See getTargetLanguages for more information
     *
     * @param language {{}}
     * @returns {boolean} indicates if the target language was successfully added.
     */
    this.addTargetLanguage = function(language) {
        validateNotEmpty(language, ['slug', 'name', 'direction']);
        let id = insertOrUpdate('target_language', {
            slug: language.slug,
            name: language.name,
            anglicized_name: language.anglicized_name,
            direction: language.direction,
            region: typeof(language.region) === 'string' ? language.region : '',
            is_gateway_language: language.is_gateway_language ? 1 : 0
        }, ['slug']);
        return id > 0;
    };

    /**
     * Inserts or updates a temporary target language in the library.
     *
     * Note: the result is boolean since you don't need the row id. See getTargetLanguages for more information
     *
     * @param language {{}}
     * @returns {boolean} indicates if the temporary target language was successfully added.
     */
    this.addTempTargetLanguage = function(language) {
        validateNotEmpty(language, ['slug', 'name', 'direction']);
        let id = insertOrUpdate('temp_target_language', {
            slug: language.slug,
            name: language.name,
            anglicized_name: language.anglicized_name,
            direction: language.direction,
            region: typeof(language.region) === 'string' ? language.region : '',
            is_gateway_language: language.is_gateway_language ? 1 : 0
        }, ['slug']);
        return id > 0;
    };

    /**
     * Updates the target language assigned to a temporary target language
     * @param temp_target_language_slug {string} the temporary target language that is being assigned a target language
     * @param target_language_slug {string} the assigned target language
     * @returns {boolean} indicates if the approved language was successfully set
     */
    this.setApprovedTargetLanguage = function(temp_target_language_slug, target_language_slug) {
        run('update temp_target_language set' +
            ' approved_target_language_slug=?' +
            ' where slug=?', [target_language_slug, temp_target_language_slug]);
        let result = query('select id from temp_target_language' +
            ' where slug=? and approved_target_language_slug=?', [temp_target_language_slug, target_language_slug]);
        return firstId(result) > 0;
    };

    /**
     * Inserts or updates a project in the library
     * @param project {{}}
     * @param source_language_id {int} the parent source language row id
     * @returns {int} the id of the project row
     */
    this.addProject = function(project, source_language_id) {
        validateNotEmpty(project, ['slug', 'name']);

        // add categories
        let parent_category_id = 0;
        if(project.categories) {
            // build categories
            for(let category of project.categories) {
                validateNotEmpty(category, ['slug', 'name']);
                run('insert or ignore into category' +
                    ' (slug, parent_id) values (?, ?)',
                    [category.slug, parent_category_id]);

                parent_category_id = firstId(query('select id from category where slug=? and parent_id=?',
                    [category.slug, parent_category_id]));

                if(parent_category_id == -1) {
                    parent_category_id = 0;
                    continue;
                }

                insertOrUpdate('category_name', {
                    source_language_id: source_language_id,
                    category_id: parent_category_id,
                    name: category.name
                }, ['source_language_id', 'category_id']);
            }
        }

        // add project
        return insertOrUpdate('project', {
            slug: project.slug,
            name: project.name,
            desc: typeof(project.desc) === 'string' ? project.desc : '',
            icon: typeof(project.icon) === 'string' ? project.icon : '',
            sort: project.sort,
            chunks_url: typeof(project.chunks_url) === 'string' ? project.chunks_url : '',
            source_language_id: source_language_id,
            category_id: parent_category_id
        }, ['slug', 'source_language_id']);
    };

    /**
     * Inserts or updates a versification in the library.
     *
     * @param versification {{}}
     * @param source_language_id {int} the parent source language row id
     * @returns {int} the id of the versification
     */
    this.addVersification = function(versification,  source_language_id) {
        validateNotEmpty(versification, ['slug', 'name']);
        run('insert or ignore into versification' +
            ' (slug) values (?)', [versification.slug]);
        let versification_id = firstId(query('select id from versification where slug=? order by id asc', [versification.slug]));
        insertOrUpdate('versification_name', {
            source_language_id: source_language_id,
            versification_id: versification_id,
            name: versification.name
        }, ['source_language_id', 'versification_id']);
        return versification_id;
    };

    /**
     * Inserts a chunk marker in the library.
     *
     * @param chunk {{}}
     * @param project_slug {string} the project that this marker exists in
     * @param versification_id {int} the versification this chunk is a member of
     * return {int} the id of the chunk marker
     */
    this.addChunkMarker = function(chunk, project_slug, versification_id) {
        validateNotEmpty(chunk, ['chapter', 'verse']);
        validateNotEmpty(project_slug);
        run('insert or ignore into chunk_marker' +
            ' (chapter, verse, versification_id, project_slug)' +
            ' values(:chapter, :verse, :versification, :project);', {
            chapter: chunk.chapter,
            verse: chunk.verse,
            project: project_slug,
            versification: versification_id
        });
        save();
        return firstId(query('select id from chunk_marker' +
            ' where versification_id=?' +
            ' and project_slug=?' +
            ' and chapter=?' +
            ' and verse=?' +
            ' order by id asc', [versification_id, project_slug, chunk.chapter, chunk.verse]));
    };

    /**
     * Inserts or updates a catalog in the library.
     *
     * @param catalog {{}}
     * @returns {int} the id of the catalog
     */
    this.addCatalog = function(catalog) {
        validateNotEmpty(catalog, ['slug', 'url']);
        return insertOrUpdate('catalog', {
            slug: catalog.slug,
            url: catalog.url,
            modified_at: catalog.modified_at
        }, ['slug']);
    };

    /**
     * Inserts or updates a resource in the library.
     *
     * @param resource {{}}
     * @param project_id {int} the parent project row id
     * @returns {int} the id of the resource row
     */
    this.addResource = function(resource, project_id) {

        validateNotEmpty(resource, ['slug', 'name', 'type', 'formats']);
        validateNotEmpty(resource.status, ['translate_mode', 'checking_level', 'version']);
        let resourceId = insertOrUpdate('resource', {
            slug: resource.slug,
            name: resource.name,
            type: resource.type,
            translate_mode: resource.status.translate_mode,
            checking_level: resource.status.checking_level,
            comments: typeof(resource.status.comments) === 'string' ? resource.status.comments : '',
            pub_date: resource.status.pub_date || 0,
            license: typeof(resource.status.license) === 'string' ? resource.status.license : '',
            version: resource.status.version,
            project_id: project_id
        }, ['slug', 'project_id']);

        // add formats
        for (let format of resource.formats) {
            insertOrUpdate('resource_format', {
                package_version: format.package_version,
                mime_type: format.mime_type,
                modified_at: format.modified_at || 0,
                url: format.url,
                imported: format.imported ? 1 : 0,
                resource_id: resourceId
            }, ['mime_type', 'resource_id']);
        }

        // add legacy data
        if(resource.translation_words_assignments_url) {
            insertOrUpdate('legacy_resource_info', {
                translation_words_assignments_url: resource.translation_words_assignments_url,
                resource_id: resourceId
            }, ['resource_id']);
        }

        return resourceId;
    };

    /**
     * Inserts or updates a questionnaire in the library.
     *
     * @param questionnaire {{}} the questionnaire to add
     * @returns {int} the id of the questionnaire row
     */
    this.addQuestionnaire = function(questionnaire) {
        validateNotEmpty(questionnaire, ['language_slug', 'language_name', 'language_direction', 'language_data']);
        let id = insertOrUpdate('questionnaire', {
            language_slug: questionnaire.language_slug,
            language_name: questionnaire.language_name,
            language_direction: questionnaire.language_direction.toLowerCase() === 'rtl' ? 'rtl' : 'ltr',
            td_id: questionnaire.td_id
        }, ['td_id', 'language_slug']);

        // add data fields
        for(let key of Object.keys(questionnaire.language_data)) {
            insertOrUpdate('questionnaire_data_field', {
                questionnaire_id: id,
                field: key,
                question_td_id: questionnaire.language_data[key]
            }, ['field', 'questionnaire_id']);
        }

        return id;
    };

    /**
     * Inserts or updates a question in the library.
     *
     * @param question {{}} the questionnaire to add
     * @param questionnaire_id {int} the parent questionnaire row id
     * @returns {int} the id of the question row
     */
    this.addQuestion = function(question, questionnaire_id) {
        validateNotEmpty(question, ['text', 'input_type']);

        // sanitize depends_on
        try {
            validateNotEmpty(question, ['depends_on']);
        } catch (err) {
            question.depends_on = -1;
        }

        return insertOrUpdate('question', {
            text: question.text,
            help: question.help,
            is_required: question.is_required,
            input_type: question.input_type,
            sort: question.sort,
            depends_on: question.depends_on,
            td_id: question.td_id,
            questionnaire_id: questionnaire_id
        }, ['td_id', 'questionnaire_id']);
    };

    /**
     * Removes all target language data
     */
    this.clearTargetLanguages = function() {
        truncateTable('target_language');
        vacuum();
    };

    /**
     * Removes all questionnaire data
     */
    this.clearNewLanguageQuestions = function() {
        truncateTable('questionnaire_data_field');
        truncateTable('question');
        truncateTable('questionnaire');
        vacuum();
    };

    /**
     * Clears out all assigned target languages.
     * This does not actually truncate anything just sets all the approved slugs to null.
     */
    this.clearApprovedTempLanguages = function() {
        run('update temp_target_language set approved_target_language_slug=null');
    };

    /**
     * Returns a list of source languages and when they were last modified.
     * The value is taken from the max modified resource format date within the language
     *
     * @returns {[{slug, modified_at}]}
     */
    this.listSourceLanguagesLastModified = function() {
        return query('select sl.slug, max(rf.modified_at) as modified_at from resource_format as rf'
            + ' left join resource  as r on r.id=rf.resource_id'
            + ' left join project as p on p.id=r.project_id'
            + ' left join source_language as sl on sl.id=p.source_language_id'
            + ' where rf.mime_type like("' + rc.spec.base_mime_type + '+%")'
            + ' group by sl.slug');
    };

    /**
     * Returns a list of projects and when they were last modified
     * The value is taken from the max modified resource format date within the project
     *
     * @param languageSlug {string} the source language who's projects will be selected. If left empty the results will include all projects in all languages.
     * @returns {[{slug, modified_at}]}
     */
    this.listProjectsLastModified = function(languageSlug) {
        if(languageSlug) {
            return query('select p.slug, max(rf.modified_at) as modified_at from resource_format as rf'
                + ' left join resource  as r on r.id=rf.resource_id'
                + ' left join project as p on p.id=r.project_id'
                + ' left join source_language as sl on sl.id=p.source_language_id'
                + ' where rf.mime_type like("' + rc.spec.base_mime_type + '+%") and sl.slug=?'
                + ' group by p.slug', languageSlug);
        } else {
            return query('select p.slug, max(rf.modified_at) as modified_at from resource_format as rf'
                + ' left join resource  as r on r.id=rf.resource_id'
                + ' left join project as p on p.id=r.project_id'
                + ' where rf.mime_type like("' + rc.spec.base_mime_type + '+%")'
                + ' group by p.slug');
        }
    };

    /**
     * Returns the parent category of the given category
     * @param languageSlug the language of the parent category name
     * @param childCategorySlug the slug of the child category
     * @returns the parent category
     */
    this.getParentCategory = function(languageSlug, childCategorySlug) {
        let result = query('select pcn.name, pc.slug from category as pc' +
            ' left join category as cc on cc.parent_id=pc.id' +
            ' left join category_name as pcn on pcn.category_id=pc.id' +
            ' left join source_language as sl on sl.id=pcn.source_language_id' +
            ' where cc.slug=? and sl.slug=?', [childCategorySlug, languageSlug]);
        if(result.length > 0) {
            return result[0];
        }
        return null;
    };

    /**
     * Returns meta data about a project without any localized information such as the title an description.
     */
    this.getProjectMeta = function(projectSlug) {
        let result = query('select p.*, c.slug as category_slug from project as p' +
            ' left join category as c on c.id=p.category_id' +
            ' where p.slug=? limit 1', [projectSlug]);
        if(result.length > 0) {
            delete result[0].id;
            delete result[0].name;
            delete result[0].desc;
            delete result[0].source_language_id;
            return result[0];
        }
        return null;
    };

    /**
     * These are methods designed for public use
     */
    this.public_getters = {

        /**
         * Returns a map of metrics about content in the index
         */
        getMetrics: function() {
            let resultLangCount =  query('select count(*) as count from target_language');
            let resultResLvl3Count = query('select count(*) as count from resource where checking_level >= 3');
            let resultResCount = query('select count(*) as count from resource');
            let resultProjCount = query('select count(*) as count from (select id from project group by slug)');
            return {
                resource_count_level3:resultResLvl3Count[0]['count'],
                resource_count:resultResCount[0]['count'],
                target_language_count:resultLangCount[0]['count'],
                project_count:resultProjCount[0]['count']
            };
        },

        /**
         * Returns a source language.
         *
         * @param slug {string}
         * @return {{}|null} the language object or null if it does not exist
         */
        getSourceLanguage: function(slug) {
            let result =  query('select * from source_language where slug=? limit 1', [slug]);
            if(result.length > 0) {
                return result[0];
            }
            return null;
        },
    
        /**
         * Returns a list of every source language.
         *
         * @returns {[{}]} an array of source languages
         */
        getSourceLanguages: function() {
            return query('select * from source_language order by slug asc');
        },

        /**
         * Returns a target language.
         * The result may be a temp target language.
         *
         * Note: does not include the row id. You don't need it
         *
         * @param slug {string}
         * @returns {{}|null} the language object or null if it does not exist
         */
        getTargetLanguage: function(slug) {
            let result = query('select * from (' +
                '  select slug, name, anglicized_name, direction, region, is_gateway_language from target_language' +
                '  union' +
                '  select slug, name, anglicized_name, direction, region, is_gateway_language from temp_target_language' +
                '  where approved_target_language_slug is null' +
                ') where slug=? limit 1', [slug]);
            if(result.length > 0) {
                return result[0];
            }
            return null;
        },

        /**
         * Returns a list of every target language.
         * The result may include temp target languages.
         *
         * Note: does not include the row id. You don't need it
         *
         * @returns {[{}]} an array of target languages
         */
        getTargetLanguages: function() {
            return query('select * from (' +
                '  select slug, name, anglicized_name, direction, region, is_gateway_language from target_language' +
                '  union' +
                '  select slug, name, anglicized_name, direction, region, is_gateway_language from temp_target_language' +
                '  where approved_target_language_slug is null' +
                ') order by slug asc, name asc');
        },

        /**
         * Returns the target language that has been assigned to a temporary target language.
         *
         * Note: does not include the row id. You don't need it
         *
         * @param temp_target_language_slug {string} the temporary target language with the assignment
         * @returns {{}|null} the language object or null if it does not exist
         */
        getApprovedTargetLanguage: function(temp_target_language_slug) {
            let result = query('select tl.* from target_language as tl' +
                ' left join temp_target_language as ttl on ttl.approved_target_language_slug=tl.slug' +
                ' where ttl.slug=?', [temp_target_language_slug]);
            if(result.length > 0) {
                delete result[0].id;
                return result[0];
            }
            return null;
        },

        /**
         * Returns a project.
         *
         * @param languageSlug {string|{languageSlug, projectSlug}}
         * @param projectSlug {string}
         * @returns {{}|null} the project object or null if it does not exist
         */
        getProject: function(languageSlug, projectSlug) {
            // support passing args as an object
            if(languageSlug != null && typeof languageSlug == 'object') {
                projectSlug = languageSlug.projectSlug;
                languageSlug = languageSlug.languageSlug;
            }
            let result =  query('select p.*, c.slug as category_slug from project as p' +
                ' left join category as c on c.id=p.category_id' +
                ' where p.slug=? and p.source_language_id in (' +
                '  select id from source_language where slug=?)' +
                ' limit 1', [projectSlug, languageSlug]);
            if(result.length > 0) {
                // store the language slug for convenience
                result[0].source_language_slug = languageSlug;
                return result[0];
            }
            return null;
        },

        /**
         * Returns a list of projects available in the given language.
         *
         * @param languageSlug {string} if left null an array of all unique projects will be returned
         * @returns {[{}]} an array of projects
         */
        getProjects: function(languageSlug) {
            let result;
            if(languageSlug) {
                result = query('select p.*, c.slug as category_slug from project as p' +
                    ' left join category as c on c.id=p.category_id' +
                    ' where p.source_language_id in (select id from source_language where slug=?)' +
                    ' order by p.sort asc', [languageSlug]);
            } else {
                result = query('select * from project' +
                    ' group  by slug order by sort asc');
            }
            for(let project of result) {
                // store the language slug for convenience
                project.source_language_slug = languageSlug;
            }
            return result;
        },

        /**
         * Returns an array of categories that exist underneath the parent category.
         * The results of this method are a combination of categories and projects.
         *
         * @param parentCategoryId {int} the category who's children will be returned. If 0 then all top level categories will be returned.
         * @param languageSlug {string} the language in which the category titles will be displayed
         * @param translateMode {string} limit the results to just those with the given translate mode. Leave this falsy to not filter
         * @returns {[{}]} an array of project categories
         */
        getProjectCategories: function(parentCategoryId, languageSlug, translateMode) {
            let preferredSlug = [languageSlug, 'en', '%'];
            let categories = [];
            if(translateMode) {
                categories = query('select \'category\' as type, c.slug as name, \'\' as source_language_slug,' +
                    ' c.id, c.slug, c.parent_id, count(p.id) as num from category as c' +
                    ' left join (' +
                    '  select p.id, p.category_id, count(r.id) as num from project as p' +
                    '  left join resource as r on r.project_id=p.id and r.translate_mode like(?)' +
                    '  group by p.slug' +
                    ' ) p on p.category_id=c.id and p.num > 0' +
                    ' where parent_id=? and num > 0 group by c.slug', [translateMode, parentCategoryId]);
            } else {
                categories = query('select \'category\' as type, category.slug as name, \'\' as source_language_slug, * from category where parent_id=?', [parentCategoryId]);
            }

            // find best name
            let catNameQuery = 'select sl.slug as source_language_slug, cn.name as name' +
                ' from category_name as cn' +
                ' left join source_language as sl on sl.id=cn.source_language_id' +
                ' where sl.slug like(?) and cn.category_id=?';
            for(let cat of categories) {
                for(let slug of preferredSlug) {
                    let result = query(catNameQuery, [slug, cat.id]);
                    if(result.length > 0) {
                        cat.name = result[0]['name'];
                        cat.source_language_slug = result[0]['source_language_slug'];
                        break;
                    }
                }
            }

            let projects = query('select * from (' +
                ' select \'project\' as type, \'\' as source_language_slug,' +
                ' p.id, p.slug, p.sort, p.name, count(r.id) as num from project as p' +
                ' left join resource as r on r.project_id=p.id and r.translate_mode like (?)' +
                ' where p.category_id=? group by p.slug' +
                ')' + (translateMode ? ' where num > 0' : ''), [translateMode || '%', parentCategoryId]);
            // find best name
            let projNameQuery = 'select sl.slug as source_language_slug, p.name as name' +
                ' from project as p' +
                ' left join source_language as sl on sl.id=p.source_language_id' +
                ' where sl.slug like(?) and p.slug=? order by sl.slug asc';
            for(let proj of projects) {
                for(let slug of preferredSlug) {
                    let result = query(projNameQuery, [slug, proj.slug]);
                    if(result.length > 0) {
                        proj.name = result[0]['name'];
                        proj.source_language_slug = result[0]['source_language_slug'];
                        break;
                    }
                }
            }
            return categories.concat(projects);
        },

        /**
         * Returns a resource.
         *
         * @param languageSlug {string}
         * @param projectSlug {string}
         * @param resourceSlug {string}
         * @returns {{}|null} the resource object or null if it does not exist
         */
        getResource: function(languageSlug, projectSlug, resourceSlug) {
            let result =  query('select r.*, lri.translation_words_assignments_url from resource as r' +
                ' left join legacy_resource_info as lri on lri.resource_id=r.id' +
                ' where r.slug=? and r.project_id in (' +
                '  select id from project where slug=? and source_language_id in (' +
                '   select id from source_language where slug=?)' +
                ' )' +
                ' limit 1', [resourceSlug, projectSlug, languageSlug]);
            if(result.length > 0) {
                let res = result[0];
                // organize the status
                res.status = {
                    translate_mode: res.translate_mode,
                    checking_level: res.checking_level,
                    comments: res.comments,
                    pub_date: res.pub_date,
                    license: res.license,
                    version: res.version
                };
                delete res.translate_mode;
                delete res.checking_level;
                delete res.comments;
                delete res.pub_date;
                delete res.license;
                delete res.version;

                // store language and project slug for convenience
                res.source_language_slug = languageSlug;
                res.project_slug = projectSlug;

                // get formats
                res.formats = [];
                let formatResults =  query('select * from resource_format' +
                    ' where resource_id=?', [res.id]);
                res.imported = false;
                for(let format of formatResults) {
                    delete format.id;
                    delete format.resource_id;
                    format.imported = !!format.imported;
                    res.formats.push(format);
                    // TRICKY: this is not technically correct, but for convenience we attach the import status to the resource directly
                    if(format.imported) res.imported = true;
                }

                return res;
            }
            return null;
        },

        /**
         * Returns a list of resources available in the given project
         * Note: You may provide a single object parameter if you prefer
         *
         * @param languageSlug {string|null|{languageSlug, projectSlug}} the language of the resource. If null then all resources of the project will be returned.
         * @param projectSlug {string} the project who's resources will be returned
         * @return {[{}]} an array of resources
         */
        getResources: function(languageSlug, projectSlug) {
            // support passing args as an object
            if(languageSlug != null && typeof languageSlug == 'object') {
                projectSlug = languageSlug.projectSlug;
                languageSlug = languageSlug.languageSlug;
            }

            let result = [];
            if(languageSlug) {
                // filter by language
                result = query('select r.*, lri.translation_words_assignments_url from resource as r' +
                    ' left join legacy_resource_info as lri on lri.resource_id=r.id' +
                    ' where r.project_id in (' +
                    '  select id from project where slug=? and source_language_id in (' +
                    '   select id from source_language where slug=?)' +
                    ' )' +
                    ' order by r.slug asc', [projectSlug, languageSlug]);
            } else {
                // no language filtering
                result = query('select sl.slug as source_language_slug, r.*, lri.translation_words_assignments_url from resource as r' +
                    ' left join legacy_resource_info as lri on lri.resource_id=r.id' +
                    ' left join project as p on p.id=r.project_id' +
                    ' left join (' +
                    '  select id, slug from source_language' +
                    ' ) as sl on sl.id=p.source_language_id' +
                    ' where p.slug=? order by r.slug asc', [projectSlug]);
            }

            for(let res of result) {
                // organize the status
                res.status = {
                    translate_mode: res.translate_mode,
                    checking_level: res.checking_level,
                    comments: res.comments,
                    pub_date: res.pub_date,
                    license: res.license,
                    version: res.version
                };
                delete res.translate_mode;
                delete res.checking_level;
                delete res.comments;
                delete res.pub_date;
                delete res.license;
                delete res.version;

                // store language and project slug for convenience
                if(!res.source_language_slug) res.source_language_slug = languageSlug;
                res.project_slug = projectSlug;

                // get formats
                res.formats = [];
                let formatResults =  query('select * from resource_format' +
                    ' where resource_id=?', [res.id]);
                res.imported = false;
                for(let format of formatResults) {
                    delete format.id;
                    delete format.resource_id;
                    format.imported = !!format.imported;
                    res.formats.push(format);
                    // TRICKY: this is not technically correct, but for convenience we attach the import status to the resource directly
                    if(format.imported) res.imported = true;
                }
            }
            return result;
        },

        /**
         * Returns a catalog.
         *
         * @param slug {string}
         * @returns {{}|null} the catalog object or null if it does not exist
         */
        getCatalog: function(slug) {
            let result = query('select * from catalog where slug=?', [slug]);
            if(result.length > 0) {
                return result[0];
            }
            return null;
        },

        /**
         * Returns a list of catalogs.
         *
         * @returns {[{}]}
         */
        getCatalogs: function() {
            return query('select * from catalog');
        },

        /**
         * Returns the category with it's localized title.
         * This will return null if there is no matching localized category.
         * This does not necessarily mean the category does not exist.
         *
         * @param languageSlug the language slug in which the category title will be given
         * @param slug the category slug
         * @returns the category
         */
        getCategory(languageSlug, slug) {
            let result = query('select cn.name, c.slug from category as c' +
                ' left join category_name as cn on cn.category_id=c.id' +
                ' left join source_language as sl on sl.id=cn.source_language_id' +
                ' where c.slug=? and sl.slug=?', [slug, languageSlug]);
            if(result.length > 0) {
                return result[0];
            }
            return null;
        },

        /**
         * Returns a list of categories in a project
         *
         * @param languageSlug the language in which the category title will be given
         * @param projectSlug the project slug
         * @returns an array of categories
         */
        getCategories(languageSlug, projectSlug) {
            let categories = [];
            let result = query('select cn.name, c.slug from category as c' +
                ' left join category_name as cn on cn.category_id=c.id' +
                ' left join source_language as sl on sl.id=cn.source_language_id' +
                ' left join project as p on p.source_language_id=sl.id and p.category_id=c.id' +
                ' where p.slug=? and sl.slug=?', [projectSlug, languageSlug]);
            if(result.length > 0) {
                categories.push(result[0]);

                // find the rest of the categories
                let previousSlug = result[0].slug;
                let nextCat = null;
                do {
                    nextCat = self.getParentCategory(languageSlug, previousSlug);
                    if(nextCat) {
                        previousSlug = nextCat.slug;
                        categories.unshift(nextCat);
                    }
                } while (nextCat);
            }
            return categories;
        },

        /**
         * Returns a versification.
         *
         * @param languageSlug {string}
         * @param versificationSlug {string}
         * @returns {{}|null}
         */
        getVersification: function(languageSlug, versificationSlug) {
            let result = query('' +
                'select vn.name, v.slug, v.id from versification_name as vn' +
                ' left join versification as v on v.id=vn.versification_id' +
                ' left join source_language as sl on sl.id=vn.source_language_id' +
                ' where sl.slug=? and v.slug=?', [languageSlug, versificationSlug]);
            if(result.length > 0) {
                return result[0];
            }
            return null;
        },

        /**
         * Returns a list of versifications.
         *
         * @param languageSlug {string}
         * @returns {[{}]}
         */
        getVersifications: function(languageSlug) {
            return query('' +
                'select vn.name, v.slug, v.id from versification_name as vn' +
                ' left join versification as v on v.id=vn.versification_id' +
                ' left join source_language as sl on sl.id=vn.source_language_id' +
                ' where sl.slug=?', [languageSlug]);
        },

        /**
         * Returns a list of chunk markers for a project.
         *
         * @param projectSlug {string}
         * @param versificationSlug {string}
         * @returns {[{}]}
         */
        getChunkMarkers: function(projectSlug, versificationSlug) {
            return query('' +
                'select cm.* from chunk_marker as cm' +
                ' left join versification as v on v.id=cm.versification_id' +
                ' where v.slug=? and cm.project_slug=?', [versificationSlug, projectSlug]);
        },

        /**
         * Returns a single questionnaire
         * @param tdId {string} the translation database id of the questionnaire
         */
        getQuestionnaire: function(tdId) {
            let result = query('select * from questionnaire where td_id=?', [tdId]);
            if(result.length > 0) {
                let questionnaire = result[0];
                questionnaire.language_data = {};

                // load data fields
                let dataResults = query('select field, question_td_id from questionnaire_data_field where questionnaire_id=?',
                    [questionnaire.id]);
                for(let data of dataResults) {
                    questionnaire.language_data[data.field] = data.question_td_id;
                }
                return questionnaire;
            }
            return null;
        },

        /**
         * Returns a list of questionnaires.
         *
         * @returns {[{}]} a list of questionnaires
         */
        getQuestionnaires: function() {
            let results = query('select * from questionnaire');

            for(let questionnaire of results) {
                // load data fields
                questionnaire.language_data = {};
                let dataResults = query('select field, question_td_id from questionnaire_data_field where questionnaire_id=?',
                    [questionnaire.id]);
                for(let data of dataResults) {
                    questionnaire.language_data[data.field] = data.question_td_id;
                }
            }
            return results;
        },

        /**
         * Returns a list of questions.
         *
         * @param questionnaire_id {int} the parent questionnaire row id
         * @returns {[{}]} a list of questionnaires
         */
        getQuestions: function(questionnaire_id) {
            return query('select * from question where questionnaire_id=? order by sort asc', [questionnaire_id]);
        },

        /**
         * Return sa list of translations.
         *
         * @param language_slug string the language these translations are available in. Leave null for all.
         * @param project_slug string the project for whom these translations are available. Leave null for all.
         * @param resource_slug string the resource for whom these translations are available. Leave null for all.
         * @param resource_type string the resource type allowed for returned translations. Leave null for all.
         * @param translate_mode string limit results to just those with the given translate mode. Leave null for all.
         * @param min_checking_level int the minimum checking level allowed for returned translations. Use 0 for no minimum.
         * @param max_checking_level int the maximum checking level allowed for returned translations. Use -1 for no maximum.
         */
        findTranslations: function(language_slug, project_slug, resource_slug, resource_type, translate_mode, min_checking_level, max_checking_level) {
            let condition_max_checking = '';

            language_slug = language_slug || '%';
            project_slug = project_slug || '%';
            resource_slug = resource_slug || '%';
            resource_type = resource_type || '%';
            translate_mode = translate_mode || '%';
            min_checking_level = min_checking_level || 0;
            max_checking_level = max_checking_level || -1;

            if(max_checking_level >= 0) condition_max_checking = ' and r.checking_level <= ' + max_checking_level;

            let translations = [];
            let results = query("select l.slug as language_slug, l.name as language_name, l.direction," +
                " p.slug as project_slug, p.category_id, p.name as project_name, p.desc, p.icon, p.sort, p.chunks_url," +
                " r.id as resource_id, r.slug as resource_slug, r.name as resource_name, r.type, r.translate_mode, r.checking_level, r.comments, r.pub_date, r.license, r.version," +
                " lri.translation_words_assignments_url," +
                " c.slug as category_slug " +
                " from source_language as l" +
                " left join project as p on p.source_language_id=l.id" +
                " left join resource as r on r.project_id=p.id" +
                " left join legacy_resource_info as lri on lri.resource_id=r.id" +
                " left join category as c on c.id=p.category_id" +
                " where l.slug like(?) and p.slug like(?) and r.slug like(?)" +
                " and r.checking_level >= ?" +
                condition_max_checking +
                " and r.type like(?) and r.translate_mode like(?)", [language_slug, project_slug, resource_slug, min_checking_level, resource_type, translate_mode]);

            for(let r of results) {
                let trans = {
                    language: {
                        slug: r.language_slug,
                        name: r.language_name,
                        direction: r.direction
                    },
                    project: {
                        slug: r.project_slug,
                        name: r.project_name,
                        desc: r.desc,
                        sort: r.sort,
                        icon: r.icon,
                        chunks_url: r.chunks_url,
                        source_language_slug: r.language_slug,
                        category_id: r.category_id,
                        category_slug: r.category_slug
                    },
                    resource: {
                        slug: r.resource_slug,
                        name: r.resource_name,
                        type: r.type,
                        status: {
                            translate_mode: r.translate_mode,
                            checking_level: r.checking_level,
                            comments: r.comments,
                            pub_date: r.pub_date,
                            license: r.license,
                            version: r.version
                        },
                        project_slug: r.project_slug,
                        source_language_slug: r.language_slug
                    }
                };

                // load formats and add to resource
                let formats = [];
                let formatResults =  query('select * from resource_format' +
                    ' where resource_id=?', [r.resource_id]);
                let imported = false;
                for(let f of formatResults) {
                    delete f.id;
                    delete f.resource_id;
                    f.imported = !!f.imported;
                    formats.push(f);
                    // TRICKY: this is not technically correct, but for convenience we attach the import status to the resource directly
                    if(f.imported) imported = true;
                }
                trans.resource.formats = formats;
                trans.resource.imported = imported;

                translations.push(trans);
            }
            return translations;
        }

        // TODO: add getTranslation. see android
    };
    
    return this;
}
896505
async function decodeData (record) {
    // convert null values to undefined
    for (const key of Object.keys(record)) {
        if (record[key] === null) {
            record[key] = undefined
        }
    }
    // get data column to decode data
    var dataColumn = this.columnName('data')
    // skip if no data column
    if (!defined(dataColumn) || !defined(record[dataColumn])) {
        return
    }
    // decompress if compresion in use
    if (this.compression) {
        // decompress data
        record[dataColumn] = await snappyUncompress(Buffer.from(record[dataColumn], 'base64'), {asBuffer: false})
    }
    // decode data
    record[dataColumn] = JSON.parse(record[dataColumn])
}
339013
function columnToSQL(col) {
	var sql = "`" + col.name + "` ";

	sql += nrutil.typeToSQL(col.type);

	if (col['additional']) {
		sql += ' ' + col['additional'];
	}

	if (!col['allow_null']) {
		sql += " NOT NULL";
	}

	if (col['default_value'] !== undefined) {
		var def = col['default_value']

		sql += " DEFAULT " + nrutil.serialize(def);
	}

	if (col.type == 'primary_key') {
		sql += " PRIMARY KEY AUTO_INCREMENT";
	}

	return sql;
}
318788
synchronized int storeObject(JSONObject obj, Table table) {
        if (!this.belowMemThreshold()) {
            Logger.v("There is not enough space left on the device to store data, data discarded");
            return DB_OUT_OF_MEMORY_ERROR;
        }

        final String tableName = table.getName();

        Cursor cursor = null;
        int count = DB_UPDATE_ERROR;

        //noinspection TryFinallyCanBeTryWithResources
        try {
            final SQLiteDatabase db = dbHelper.getWritableDatabase();

            final ContentValues cv = new ContentValues();
            cv.put(KEY_DATA, obj.toString());
            cv.put(KEY_CREATED_AT, System.currentTimeMillis());
            db.insert(tableName, null, cv);
            cursor = db.rawQuery("SELECT COUNT(*) FROM " + tableName, null);
            cursor.moveToFirst();
            count = cursor.getInt(0);
        } catch (final SQLiteException e) {
            getConfigLogger().verbose("Error adding data to table " + tableName + " Recreating DB");

            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
            dbHelper.deleteDatabase();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            dbHelper.close();
        }
        return count;
    }