Function that loads sql files generated by autoFR Experiment
504218
def loadEL(dbpath=None, recpath=None, remove_subs=None, wordpool=None, groupby=None, experiments=None,
    filters=None):
    '''
    
    '''

    assert (dbpath is not None), "You must specify a db file or files."
    assert (recpath is not None), "You must specify a recall folder."
    assert (wordpool is not None), "You must specify a wordpool file."
    assert (experiments is not None), "You must specify a list of experiments"

    ############################################################################
    # subfunctions #############################################################

    def db2df(db, filter_func=None):
        '''
        Loads db file and converts to dataframe
        '''
        db_url = "sqlite:///" + db
        table_name = 'turkdemo'
        data_column_name = 'datastring'

        # boilerplace sqlalchemy setup
        engine = create_engine(db_url)
        metadata = MetaData()
        metadata.bind = engine
        table = Table(table_name, metadata, autoload=True)

        # make a query and loop through
        s = table.select()
        rows = s.execute()

        data = []
        for row in rows:
            data.append(row[data_column_name])

        # parse each participant's datastring as json object
        # and take the 'data' sub-object
        data = [json.loads(part)['data'] for part in data if part is not None]

        # remove duplicate subject data for debugXG82XV:debug7XPXQA
        # data[110] = data[110][348:]

        # insert uniqueid field into trialdata in case it wasn't added
        # in experiment:
        for part in data:
            for record in part:
        #         print(record)
                if type(record['trialdata']) is list:

                    record['trialdata'] = {record['trialdata'][0]:record['trialdata'][1]}
                record['trialdata']['uniqueid'] = record['uniqueid']

        # flatten nested list so we just have a list of the trialdata recorded
        # each time psiturk.recordTrialData(trialdata) was called.
        def isNotNumber(s):
            try:
                float(s)
                return False
            except ValueError:
                return True

        data = [record['trialdata'] for part in data for record in part]

        # filter out fields that we dont want using isNotNumber function
        filtered_data = [{k:v for (k,v) in list(part.items()) if isNotNumber(k)} for part in data]

        # Put all subjects' trial data into a dataframe object from the
        # 'pandas' python library: one option among many for analysis
        data_frame = pd.DataFrame(filtered_data)

        data_column_name = 'codeversion'

        # boilerplace sqlalchemy setup
        engine = create_engine(db_url)
        metadata = MetaData()
        metadata.bind = engine
        table = Table(table_name, metadata, autoload=True)

        # make a query and loop through
        s = table.select()
        rows = s.execute()

        versions = []
        version_dict = {}
        for row in rows:
            version_dict[row[0]]=row[data_column_name]

        version_col = []
        for idx,sub in enumerate(data_frame['uniqueid'].unique()):
            for i in range(sum(data_frame['uniqueid']==sub)):
                version_col.append(version_dict[sub])
        data_frame['exp_version']=version_col

        if filter_func:
            for idx,filt in enumerate(filter_func):
                data_frame = filt(data_frame)
        return data_frame

    # custom filter to clean db file
    def experimenter_filter(data_frame):
        data=[]
        indexes=[]
        for line in data_frame.iterrows():
            try:
                if json.loads(line[1]['responses'])['Q1'].lower() in ['kirsten','allison','allison\nallison','marisol', 'marisol ','marisiol', 'maddy','campbell', 'campbell field', 'kirsten\nkirsten', 'emily', 'bryan', 'armando', 'armando ortiz',
                                                                      'maddy/lucy','paxton', 'lucy','campbell\ncampbell','madison','darya','rachael']:
                    delete = False
                else:
                    delete = True
            except:
                pass

            if delete:
                indexes.append(line[0])

        return data_frame.drop(indexes)

    def adaptive_filter(data_frame):
        data=[]
        indexes=[]
        subjcb={}
        for line in data_frame.iterrows():
            try:
                if 'Q2' in json.loads(line[1]['responses']):
                    delete = False
                else:
                    delete = False
            except:
                pass

            if delete:
                indexes.append(line[0])
        return data_frame.drop(indexes)

    def experiments_filter(data_frame):
        indexes=[]
        for line in data_frame.iterrows():
            try:
                if line[1]['exp_version'] in experiments:
                    delete = False
                else:
                    delete = True
            except:
                pass

            if delete:
                indexes.append(line[0])
        return data_frame.drop(indexes)


    # this function takes the data frame and returns subject specific data based on the subid variable
    def filterData(data_frame,subid):
        filtered_stim_data = data_frame[data_frame['stimulus'].notnull() & data_frame['listNumber'].notnull()]
        filtered_stim_data = filtered_stim_data[filtered_stim_data['trial_type']=='single-stim']
        filtered_stim_data =  filtered_stim_data[filtered_stim_data['uniqueid']==subid]
        return filtered_stim_data

    def createStimDict(data):
        stimDict = []
        for index, row in data.iterrows():
            try:
                stimDict.append({
                        'text': str(re.findall('>(.+)<',row['stimulus'])[0]),
                        'color' : { 'r' : int(re.findall('rgb\((.+)\)',row['stimulus'])[0].split(',')[0]),
                                   'g' : int(re.findall('rgb\((.+)\)',row['stimulus'])[0].split(',')[1]),
                                   'b' : int(re.findall('rgb\((.+)\)',row['stimulus'])[0].split(',')[2])
                                   },
                        'location' : {
                            'top': float(re.findall('top:(.+)\%;', row['stimulus'])[0]),
                            'left' : float(re.findall('left:(.+)\%', row['stimulus'])[0])
                            },
                        'category' : wordpool['CATEGORY'].iloc[list(wordpool['WORD'].values).index(str(re.findall('>(.+)<',row['stimulus'])[0]))],
                        'size' : wordpool['SIZE'].iloc[list(wordpool['WORD'].values).index(str(re.findall('>(.+)<',row['stimulus'])[0]))],
                        'wordLength' : len(str(re.findall('>(.+)<',row['stimulus'])[0])),
                        'firstLetter' : str(re.findall('>(.+)<',row['stimulus'])[0])[0],
                        'listnum' : row['listNumber']
                    })
            except:
                stimDict.append({
                        'text': str(re.findall('>(.+)<',row['stimulus'])[0]),
                        'color' : { 'r' : 0,
                                   'g' : 0,
                                   'b' : 0
                                   },
                        'location' : {
                            'top': 50,
                            'left' : 50
                            },
                        'category' : wordpool['CATEGORY'].iloc[list(wordpool['WORD'].values).index(str(re.findall('>(.+)<',row['stimulus'])[0]))],
                        'size' : wordpool['SIZE'].iloc[list(wordpool['WORD'].values).index(str(re.findall('>(.+)<',row['stimulus'])[0]))],
                        'wordLength' : len(str(re.findall('>(.+)<',row['stimulus'])[0])),
                        'firstLetter' : str(re.findall('>(.+)<',row['stimulus'])[0])[0],
                        'listnum' : row['listNumber']
                    })
        return stimDict

    # this function loads in the recall data into an array of arrays, where each array represents a list of words
    def loadRecallData(subid):
        recalledWords = []
        for i in range(0,16):
            try:
                f = open(recpath + subid + '/' + subid + '-' + str(i) + '.wav.txt', 'r')
                spamreader = csv.reader(f, delimiter=',', quotechar='|')
            except (IOError, OSError) as e:
                try:
                    f = open(recpath + subid + '-' + str(i) + '.wav.txt', 'r')
                    spamreader = csv.reader(f, delimiter=',', quotechar='|')
                except (IOError, OSError) as e:
                    print(e)
            try:
                words=[]
                altformat=True
                for row in spamreader:
                    if len(row)>1:
                        recalledWords.append(row)
                        altformat=False
                        break
                    else:
                        try:
                            words.append(row[0])
                        except:
                            pass
                if altformat:
                    recalledWords.append(words)
            except:
                print('couldnt process '+ recpath + subid + '/' + subid + '-' + str(i) + '.wav.txt')
        return recalledWords

    # this function computes accuracy for a series of lists
    def computeListAcc(stimDict,recalledWords):
        accVec = []
        for i in range(0,16):
            stim = [stim['text'] for stim in stimDict if stim['listnum']==i]
            recalled= recalledWords[i]

            acc = 0
            tmpstim = stim[:]
            for word in recalled:
                if word in tmpstim:
                    tmpstim.remove(word)
                    acc+=1
            accVec.append(acc/len(stim))
        return accVec

    def getFeatures(stimDict):
        stimDict_copy = stimDict[:]
        for item in stimDict_copy:
            item['location'] = [item['location']['top'], item['location']['left']]
            item['color'] = [item['color']['r'], item['color']['g'], item['color']['b']]
            item.pop('text', None)
            item.pop('listnum', None)
        stimDict_copy = [stimDict_copy[i:i+16] for i in range(0, len(stimDict_copy), 16)]
        return stimDict_copy

    ############################################################################
    # main program #############################################################

    # if its not a list, make it one
    if type(dbpath) is not list:
        dbpath = [dbpath]

    # read in stimulus library
    wordpool = pd.read_csv(wordpool)

    # add custom filters
    if filters:
        filter_func = [adaptive_filter, experimeter_filter, experiments_filter] + filters
    else:
        filter_func = [adaptive_filter, experimenter_filter, experiments_filter]

    # load in dbs and convert to df, and filter
    dfs = [db2df(db, filter_func=filter_func) for db in dbpath]

    # concatenate the db files
    df = pd.concat(dfs)

    # subjects who have completed the exp
    subids = list(df[df['listNumber']==15]['uniqueid'].unique())

    # remove problematic subjects
    if remove_subs:
        for sub in remove_subs:
            try:
                subids.remove(sub)
            except:
                print('Could not find subject: ' + sub + ', skipping...')

    # set up data structure to load in subjects
    if groupby:
        pres = [[] for i in range(len(groupby['exp_version']))]
        rec = [[] for i in range(len(groupby['exp_version']))]
        features = [[] for i in range(len(groupby['exp_version']))]
        subs = [[] for i in range(len(groupby['exp_version']))]

        # make each groupby item a list
        groupby = [exp if type(exp) is list else [exp] for exp in groupby['exp_version']]

    else:
        pres = [[]]
        rec = [[]]
        features = [[]]
        subs = [[]]

    # for each subject that completed the experiment
    for idx,sub in enumerate(subids):

        # get the subjects data
        filteredStimData = filterData(df,sub)

        if filteredStimData['exp_version'].values[0] in experiments:

            # create stim dict
            stimDict = createStimDict(filteredStimData)

            sub_data = pd.DataFrame(stimDict)
            sub_data['subject']=idx
            sub_data['experiment']=filteredStimData['exp_version'].values[0]
            sub_data = sub_data[['experiment','subject','listnum','text','category','color','location','firstLetter','size','wordLength']]

            # get features from stim dict
            feats = getFeatures(stimDict)

            # load in the recall data
            recalledWords = loadRecallData(sub)

            # get experiment version
            exp_version = filteredStimData['exp_version'].values[0]

            # find the idx of the experiment for this subjects
            if groupby:
                exp_idx = list(np.where([exp_version in item for item in groupby])[0])
            else:
                exp_idx = [0]

            if exp_idx != []:
                pres[exp_idx[0]].append([list(sub_data[sub_data['listnum']==lst]['text'].values) for lst in sub_data['listnum'].unique()])
                rec[exp_idx[0]].append(recalledWords)
                features[exp_idx[0]].append(feats)
                subs[exp_idx[0]].append(sub)

    eggs = [Egg(pres=ipres, rec=irec, features=ifeatures, meta={'ids' : isub}) for ipres,irec,ifeatures,isub in zip(pres, rec, features, subs)]

    # map feature dictionaries in pres df to rec df
    def checkword(x):
        if x is None:
            return x
        else:
            try:
                return stim_dict[x['item']]
            except:
                return x

    # convert utf-8 bytes type to string
    def update_types(egg):
        featlist = list(egg.pres.loc[0].loc[0].values.tolist()[0].keys())
        def update1df(df):
            for sub in range(egg.n_subjects):
                for liszt in range(egg.n_lists):
                    for item in range(len(df.loc[sub].loc[liszt].values.tolist())):
                        for feat in featlist:
                            if feat in df.loc[sub].loc[liszt].values.tolist()[item].keys():
                                if isinstance(df.loc[sub].loc[liszt].values.tolist()[item][feat], np.bytes_):
                                    try:
                                        df.loc[sub].loc[liszt].values.tolist()[item][feat] = str(df.loc[sub].loc[liszt].values.tolist()[item][feat], 'utf-8')
                                    except:
                                        print("Subject " + str(sub) + ", list " + str(liszt) + ", item " + str(item) + ", feature " + str(feat) + ": Could not convert type " + str(type(egg.rec.loc[sub].loc[liszt].values.tolist()[item][feat])) + " to string.")
        update1df(egg.pres)
        update1df(egg.rec)

    for egg in eggs:
        update_types(egg)
        old_meta = egg.meta
        temp_eggs = [egg]

        for i in range(egg.n_subjects):
            e = egg.crack(subjects=[i])
            stim = e.pres.values.ravel()
            stim_dict = {str(x['item']) : {k:v for k, v in iter(x.items())} for x in stim}

            e.rec = e.rec.applymap(lambda x: checkword(x))
            temp_eggs.append(e)

        edited_egg = stack_eggs(temp_eggs)
        mapped_egg = edited_egg.crack(subjects=[i for i in range(egg.n_subjects,egg.n_subjects*2)])
        mapped_egg.meta = old_meta
        eggs[eggs.index(egg)] = mapped_egg

    if len(eggs)>1:
        return eggs
    else:
        return eggs[0]
240207
def _loadDB(self):
        """"""
        if not self._validID():
            raise NotFound, self._getID()
        (sql, fields) = self._prepareSQL("SELECT")
        curs = self.cursor()
        curs.execute(sql, self._getID())
        result = curs.fetchone()
        if not result:
            curs.close()
            raise NotFound, self._getID()
        self._loadFromRow(result, fields, curs)
        curs.close()
        self._updated = time.time()
695531
@Override
	public String load(final String filePath) {
		if (StringUtils.isEmpty(filePath)) {
			throw new IllegalArgumentException("Invalid file path. filePath=" + filePath);
		}
		String targetFilePath = getFilePath(trimSqlExtension(filePath.replace(".", PATH_SEPARATOR)));
		String sql = null;

		URL resource = Thread.currentThread().getContextClassLoader().getResource(targetFilePath);
		if (resource != null) {
			try (InputStream is = resource.openStream()) {

				sql = trimSlash(read(new BufferedReader(new InputStreamReader(is))));

				LOG.debug("Loaded SQL template.[{}]", targetFilePath);
			} catch (IOException e) {
				throw new UroborosqlRuntimeException("Failed to load SQL template[" + targetFilePath + "].", e);
			}
		}

		if (sql == null) {
			throw new UroborosqlRuntimeException("SQL template could not found.[" + targetFilePath + "]");
		}
		return sql;
	}
630583
def load_infile_sql(path, columns, options={})
      replacement = opts[:insert_ignore] ? :ignore : :replace
      options = {:update => replacement}.merge(options)
      LoadDataInfileExpression.new(path, 
                                   opts[:from].first, 
                                   columns, 
                                   options).
        to_sql(db)
    end
849194
function(db) {
		var methods_uuid = uuid();
		debug.assert(methods_uuid).is('uuid');

		return db.query('CREATE SEQUENCE methods_seq')
			.query(['CREATE TABLE IF NOT EXISTS methods (',
					"	id uuid PRIMARY KEY NOT NULL default uuid_generate_v5('"+methods_uuid+"', nextval('methods_seq'::regclass)::text),",
					'	types_id uuid REFERENCES types,',
					'	type text NOT NULL,',
					'	name text NOT NULL,',
					'	body text NOT NULL,',
					'	meta json NOT NULL,',
					'	active BOOLEAN DEFAULT TRUE,',
					'	created timestamptz NOT NULL default now(),',
					'	modified timestamptz NOT NULL default now(),',
					'	CONSTRAINT active_not_false CHECK(active != false)',
					')'
				].join('\n'))
			.query('ALTER SEQUENCE methods_seq OWNED BY methods.id')
			.query('CREATE INDEX methods_types_id ON methods (types_id)')
			.query('CREATE INDEX methods_types_id_name ON methods (types_id,name)')
			.query('CREATE INDEX methods_type ON methods (type)')
			.query('CREATE INDEX methods_type_name ON methods (type,name)')
			.query('CREATE UNIQUE INDEX ON methods USING btree(types_id, name, active nulls LAST);')
			.query('CREATE TRIGGER methods_modified BEFORE UPDATE ON methods FOR EACH ROW EXECUTE PROCEDURE moddatetime (modified)')
			.query(
				[
	'CREATE OR REPLACE FUNCTION ensure_only_one_active_row_trigger()',
	'RETURNS trigger',
	'AS $function$',
	'BEGIN',
	  // Disable the currently enabled row
	"  IF (TG_OP = 'UPDATE') THEN",
	     // Nothing to do if updating the row currently enabled'
	"    IF (OLD.active = true AND OLD.name = NEW.name) THEN",
	'      RETURN NEW;',
	'    END IF;',
	"    EXECUTE format('UPDATE %I.%I SET active = null WHERE active = true AND name = %L;', TG_TABLE_SCHEMA, TG_TABLE_NAME, OLD.name);",
	"    IF (OLD.name != NEW.name) THEN",
	"      EXECUTE format('UPDATE %I.%I SET active = null WHERE active = true AND name = %L;', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.name);",
	'    END IF;',
	'  ELSE',
	"    EXECUTE format('UPDATE %I.%I SET active = null WHERE active = true AND name = %L;', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.name);",
	'  END IF;',
	   // Enable new row
	'  NEW.active := true;',
	'  RETURN NEW;',
	'END;',
	'$function$',
	'LANGUAGE plpgsql'
				].join('\n')
			)
			.query('CREATE TRIGGER methods_only_one_active_row'+
				' BEFORE INSERT OR UPDATE OF active ON methods'+
				' FOR EACH ROW WHEN (NEW.active = true)'+
				' EXECUTE PROCEDURE ensure_only_one_active_row_trigger()'
			);
	}
485574
def _get_samples_to_process(fn, out_dir, config, force_single, separators):
    """"""
    out_dir = os.path.abspath(out_dir)
    samples = defaultdict(list)
    with open(fn) as handle:
        for l in handle:
            if l.find("description") > 0:
                logger.info("Skipping header.")
                continue
            cols = l.strip().split(",")
            if len(cols) > 0:
                if len(cols) < 2:
                    raise ValueError("Line needs 2 values: file and name.")
                if utils.file_exists(cols[0]) or is_gsm(cols[0]) or is_srr(cols[0]):
                    if cols[0].find(" ") > -1:
                        new_name = os.path.abspath(cols[0].replace(" ", "_"))
                        logger.warning("Space finds in %s. Linked to %s." % (cols[0], new_name))
                        logger.warning("Please, avoid names with spaces in the future.")
                        utils.symlink_plus(os.path.abspath(cols[0]), new_name)
                        cols[0] = new_name
                    samples[cols[1]].append(cols)
                else:
                    logger.info("skipping %s, File doesn't exist." % cols[0])
    for sample, items in samples.items():
        if is_fastq(items[0][0], True):
            fn = "fq_merge"
            ext = ".fastq.gz"
        elif is_bam(items[0][0]):
            fn = "bam_merge"
            ext = ".bam"
        elif is_gsm(items[0][0]):
            fn = "query_gsm"
            ext = ".fastq.gz"
        elif is_srr(items[0][0]):
            fn = "query_srr"
            ext = ".fastq.gz"
        files = [os.path.abspath(fn_file[0]) if utils.file_exists(fn_file[0]) else fn_file[0] for fn_file in items]
        samples[sample] = [{'files': _check_paired(files, force_single, separators),
                            'out_file': os.path.join(out_dir, sample + ext),
                            'fn': fn, 'anno': items[0][2:], 'config': config,
                            'name': sample, 'out_dir': out_dir}]
    return [samples[sample] for sample in samples]
978779
def dbfpack(fn, usecols=None):
    """"""

    loadfunc = pulldbf.dbf_asdict
    cp = ChannelPack(loadfunc)
    cp.load(fn, usecols)
    names = pulldbf.channel_names(fn, usecols)
    cp.set_channel_names(names)
    # cp.set_basefilemtime()
    return cp
600550
function processNextAutogenBlock(autogenContext, allData, commentInfo, pos, callback) {

    //Update the position of the next block. If there isn't one, call the callback and break the recursion.
    pos = utils.regexIndexOf(allData, commentInfo.start, pos);

    if (pos == -1) {
        callback(allData);
        return;
    }

    //Extract the information from the autogen definition
    var matchInfo = commentInfo.start.exec(allData.substring(pos));

    //Skip over the start tag
    pos += matchInfo[0].length;

    //Get the data from the autogen tag
    var tagContent = matchInfo[1];
    var autogenTagData = utils.parseAutogenTag(tagContent);

    //Make file name in to a full path
    var filename = path.resolve(autogenContext.templateDir, autogenTagData.templateFileName + ".liquid");

    //Find the end of the line (and the start of content)
    //    Handle both styles of line endings
    var endOfAutogenLine = utils.regexIndexOf(allData, /[\r|\n]/, pos);
    var startBlock = utils.regexIndexOf(allData, /[\n]/, endOfAutogenLine) + 1;
    var endBlock = allData.indexOf(commentInfo.end, startBlock);

    //Prepare and load the required data
    var loadedLiquid = fs.readFileSync(filename);
    //Deep-copy the spec data so that we can add context
    var dataContext = utils.extend({}, autogenContext.specData);

    //Populate the autogen data context with property mappings from autogen tag
    for (var mappingIndex in autogenTagData.propertyMappings) {
        var propertyMapping = autogenTagData.propertyMappings[mappingIndex];

        var propValue = utils.getProp(dataContext, propertyMapping.sourceString);
        utils.setProp(dataContext, propertyMapping.destString, propValue);
    }

    //Populate the autogen data context with value mappings from autogen tag
    for (var mappingIndex in autogenTagData.valueMappings) {
        var valueMapping = autogenTagData.valueMappings[mappingIndex];

        utils.setProp(dataContext, valueMapping.destString, valueMapping.sourceString);
    }

    function insertTemplatedAndContinue(result) {
        //Replace the old contents of the output block with the result from the liquid engine
        var newData =
            allData.substring(0, startBlock)
            + result
            + os.EOL
            + allData.substring(endBlock);

        //Start/continue the recursion, and update the position for the next iteration
        processNextAutogenBlock(autogenContext, newData, commentInfo,
            startBlock
            + result.length
            + os.EOL.length
            + commentInfo.end.length, callback);
    }

    if (autogenContext.clearTemplatedSections)
        insertTemplatedAndContinue('');
    else {
        //Parse and render the liquid using the spec data
        autogenContext.liquidEngine
            .parseAndRender(loadedLiquid, dataContext)
            .then(insertTemplatedAndContinue);
    }
}
243413
private void generateSqlInfos() {
		try {
			String loadPathSlash = loadPath.toString().replaceAll("\\\\", "/");
			Enumeration<URL> root = Thread.currentThread().getContextClassLoader()
					.getResources(loadPathSlash);

			while (root.hasMoreElements()) {
				URI uri = root.nextElement().toURI();
				String scheme = uri.getScheme();
				if (SCHEME_FILE.equals(scheme)) {
					traverse(Paths.get(uri), detectChanges && true, false);
				} else if (SCHEME_JAR.equals(scheme)) {
					FileSystem fs = null;
					try {
						fs = FileSystems.getFileSystem(uri);
					} catch (FileSystemNotFoundException ex) {
						Map<String, String> env = new HashMap<>();
						env.put("create", "false");
						fs = FileSystems.newFileSystem(uri, env);
					}
					traverse(fs.getPath(loadPathSlash), false, false);
				}
			}
		} catch (IOException | URISyntaxException e) {
			log.error("Can't load sql files.", e);
		}
	}
871027
function _query($sql,$inputarr=false)
	{
		$this->_errorMsg = false;

		if (is_array($sql)) $sql = $sql[1];

		$insert = false;
		// handle native driver flaw for retrieving the last insert ID
		if(preg_match('/^\W*insert[\s\w()",.]+values\s*\((?:[^;\']|\'\'|(?:(?:\'\')*\'[^\']+\'(?:\'\')*))*;?$/i', $sql)) {
			$insert = true;
			$sql .= '; '.$this->identitySQL; // select scope_identity()
		}
		if($inputarr) {
			$rez = sqlsrv_query($this->_connectionID, $sql, $inputarr);
		} else {
			$rez = sqlsrv_query($this->_connectionID,$sql);
		}

		if ($this->debug) ADOConnection::outp("<hr>running query: ".var_export($sql,true)."<hr>input array: ".var_export($inputarr,true)."<hr>result: ".var_export($rez,true));

		if(!$rez) {
			$rez = false;
		} else if ($insert) {
			// retrieve the last insert ID (where applicable)
			while ( sqlsrv_next_result($rez) ) {
				sqlsrv_fetch($rez);
				$this->lastInsertID = sqlsrv_get_field($rez, 0);
			}
		}
		return $rez;
	}
956618
def generate_conf_file(argv: List[str]) -> bool:
    
    parser = ArgumentParser(description="Generate SQL db_conf file template")
    parser.add_argument("-f", "--configfile", help="File name to generate (Default: db_conf)", metavar="Config File",
                        default="db_conf")
    opts = parser.parse_args(argv)
    if os.path.exists(opts.configfile):
        print(f"{opts.configfile} already exists!")
        return False
    with open(opts.configfile, 'w') as f:
        f.write(conf_template)
    print(f"{opts.configfile} generated")
    return True
281832
public static void generateSQL(final SQLiteModelMethod method, MethodSpec.Builder methodBuilder) {
		JQLChecker jqlChecker = JQLChecker.getInstance();

		methodBuilder.addCode("\n// generate sql\n");
		String sql = jqlChecker.replace(method, method.jql, new JQLReplacerListenerImpl(method) {
			@Override
			public String onColumnNameToUpdate(String columnName) {
				// only entity's columns
				return currentEntity.findPropertyByName(columnName).columnName;
			}

			@Override
			public String onColumnName(String columnName) {
				return currentSchema.findColumnNameByPropertyName(method, columnName);
			}

			@Override
			public String onBindParameter(String bindParameterName, boolean inStatement) {
				return "?";
			}

		});

		if (method.jql.dynamicReplace.containsKey(JQLDynamicStatementType.DYNAMIC_WHERE)) {
			methodBuilder.addStatement("String _sql=String.format($S, $L)",
					sql.replace(method.jql.dynamicReplace.get(JQLDynamicStatementType.DYNAMIC_WHERE), "%s"),
					"StringUtils.ifNotEmptyAppend(_sqlDynamicWhere,\" AND \")");
		} else {
			methodBuilder.addStatement("String _sql=$S", sql);
		}
	}
580304
def split_sql(sql):
    """"""
    bookends = ("\n", ";", "--", "/*", "*/")
    last_bookend_found = None
    start = 0

    while start <= len(sql):
        results = get_next_occurence(sql, start, bookends)
        if results is None:
            yield (last_bookend_found, None, sql[start:])
            start = len(sql) + 1
        else:
            (end, bookend) = results
            yield (last_bookend_found, bookend, sql[start:end])
            start = end + len(bookend)
            last_bookend_found = bookend
874641
def _run_snpeff(snp_in, out_format, data):
    
    snpeff_db, datadir = get_db(data)
    if not snpeff_db:
        return None, None

    assert os.path.exists(os.path.join(datadir, snpeff_db)), \
        "Did not find %s snpEff genome data in %s" % (snpeff_db, datadir)
    ext = utils.splitext_plus(snp_in)[1] if out_format == "vcf" else ".tsv"
    out_file = "%s-effects%s" % (utils.splitext_plus(snp_in)[0], ext)
    stats_file = "%s-stats.html" % utils.splitext_plus(out_file)[0]
    csv_file = "%s-stats.csv" % utils.splitext_plus(out_file)[0]
    if not utils.file_exists(out_file):
        config_args = " ".join(_snpeff_args_from_config(data))
        if ext.endswith(".gz"):
            bgzip_cmd = "| %s -c" % tools.get_bgzip_cmd(data["config"])
        else:
            bgzip_cmd = ""
        with file_transaction(data, out_file) as tx_out_file:
            snpeff_cmd = _get_snpeff_cmd("eff", datadir, data, tx_out_file)
            cmd = ("{snpeff_cmd} {config_args} -noLog -i vcf -o {out_format} "
                   "-csvStats {csv_file} -s {stats_file} {snpeff_db} {snp_in} {bgzip_cmd} > {tx_out_file}")
            do.run(cmd.format(**locals()), "snpEff effects", data)
    if ext.endswith(".gz"):
        out_file = vcfutils.bgzip_and_index(out_file, data["config"])
    return out_file, [stats_file, csv_file]
1000211
def blast(request, blast_form, template_init, template_result, blast_commandline, sample_fasta_path,
          extra_context=None):
    

    if request.method == 'POST':

        form = blast_form(request.POST)

        if form.is_valid():

            query_file_object_tmp = form.cleaned_data['sequence_in_form']
            evalue = float(form.cleaned_data['evalue_in_form'])
            word_size = int(form.cleaned_data['word_size_in_form'])
            database_path = str(form.cleaned_data['blast_db_in_form'])

            standard_opt_dic = {'query': query_file_object_tmp, 'evalue': evalue, 'outfmt': 5, 'db': database_path,
                                'word_size': word_size}
            annotated = utils.get_annotation(database_path, BLAST_DB_NUCL_LIST)

            # none standard options:
            try:
                matrix = str(form.cleaned_data['matrix_in_form'])
                standard_opt_dic["matrix"] = matrix
            except:
                pass

            sensitivity_opt_dic = ast.literal_eval(str(form.cleaned_data['search_sensitivity_in_form']))

            blast_records__file_xml = None
            try:

                # blast search, parse results from temp file, put them into template for rendering.
                blast_records__file_xml, blast_error = utils.run_blast_commands(blast_commandline,
                                                                                **dict(standard_opt_dic,
                                                                                       **sensitivity_opt_dic))

                if len(blast_error) > 0:
                    return render(request=request, template_name=template_result,
                                  context={"blast_record": '', blast_error: BLAST_CORRECT_PARAMS})

                else:

                    # converts blast results into objects and pack into list
                    blast_records_in_object_and_list = utils.blast_records_to_object(
                        list(NCBIXML.parse(blast_records__file_xml)))

                    # user defined function to modify blast results
                    # e.g. join blast results with external database in template
                    if extra_context is not None:
                        blast_records_in_object_and_list = extra_context(blast_records_in_object_and_list)

                    return render(request=request, template_name=template_result,
                                  context={'application': blast_records_in_object_and_list[0].application,
                                           'version': blast_records_in_object_and_list[0].version,
                                           'blast_records': blast_records_in_object_and_list,
                                           'annotated': annotated})

            finally:
                # remove result - temporary file
                if blast_records__file_xml is not None:
                    os.remove(blast_records__file_xml.name)

    else:
        form = blast_form(initial={'sequence_in_form': '', 'evalue_in_form': EVALUE_BLAST_DEFAULT})

    return render(request=request, template_name=template_init,
                  context={'form': form, 'sequence_sample_in_fasta': utils.get_sample_data(sample_fasta_path),
                           "blast_max_number_seq_in_input": BLAST_MAX_NUMBER_SEQ_IN_INPUT, })
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;
}
1137425
function load($str, $lowercase=true, $stripRN=true, $defaultBRText=DEFAULT_BR_TEXT, $defaultSpanText=DEFAULT_SPAN_TEXT)
	{
		global $debug_object;

		// prepare
		$this->prepare($str, $lowercase, $stripRN, $defaultBRText, $defaultSpanText);
		// strip out cdata
		$this->remove_noise("'<!\[CDATA\[(.*?)\]\]>'is", true);
		// strip out comments
		$this->remove_noise("'<!--(.*?)-->'is");
		// Per sourceforge http://sourceforge.net/tracker/?func=detail&aid=2949097&group_id=218559&atid=1044037
		// Script tags removal now preceeds style tag removal.
		// strip out <script> tags
		$this->remove_noise("'<\s*script[^>]*[^/]>(.*?)<\s*/\s*script\s*>'is");
		$this->remove_noise("'<\s*script\s*>(.*?)<\s*/\s*script\s*>'is");
		// strip out <style> tags
		$this->remove_noise("'<\s*style[^>]*[^/]>(.*?)<\s*/\s*style\s*>'is");
		$this->remove_noise("'<\s*style\s*>(.*?)<\s*/\s*style\s*>'is");
		// strip out preformatted tags
		$this->remove_noise("'<\s*(?:code)[^>]*>(.*?)<\s*/\s*(?:code)\s*>'is");
		// strip out server side scripts
		$this->remove_noise("'(<\?)(.*?)(\?>)'s", true);
		// strip smarty scripts
		$this->remove_noise("'(\{\w)(.*?)(\})'s", true);

		// parsing
		while ($this->parse());
		// end
		$this->root->_[HDOM_INFO_END] = $this->cursor;
		$this->parse_charset();

		// make load function chainable
		return $this;

	}