function fetchMeaningIds() { $dbr = wfGetDB( DB_SLAVE ); $dc = $this->dataset; $id = $this->id; $queryResult = $dbr->query( "SELECT * FROM {$dc}_syntrans where expression_id=$id AND " . getLatestTransactionRestriction( "{$dc}_syntrans" ) ); while ( $syntransRecord = $dbr->fetchObject( $queryResult ) ) { $this->meaningIds[] = $syntransRecord->defined_meaning_id; } $dbr->freeResult( $queryResult ) ; }
function searchText( $text ) { $dc = wdGetDataSetContext(); $dbr = wfGetDB( DB_SLAVE ); $sql = "SELECT INSTR(LCASE({$dc}_expression.spelling), LCASE(" . $dbr->addQuotes( "$text" ) . ")) as position, {$dc}_syntrans.defined_meaning_id AS defined_meaning_id, {$dc}_expression.spelling AS spelling, {$dc}_expression.language_id AS language_id " . "FROM {$dc}_expression, {$dc}_syntrans " . "WHERE {$dc}_expression.expression_id={$dc}_syntrans.expression_id AND {$dc}_syntrans.identical_meaning=1 " . " AND " . getLatestTransactionRestriction( "{$dc}_syntrans" ) . " AND " . getLatestTransactionRestriction( "{$dc}_expression" ) . " AND spelling LIKE " . $dbr->addQuotes( "%$text%" ) . " ORDER BY position ASC, {$dc}_expression.spelling ASC limit 100"; $queryResult = $dbr->query( $sql ); list( $recordSet, $editor ) = getSearchResultAsRecordSet( $queryResult ); // return $sql; return $editor->view( new IdStack( "expression" ), $recordSet ); }
public function convert( $record ) { $dc = wdGetDataSetContext(); $o = OmegaWikiAttributes::getInstance(); $dbr = wfGetDB( DB_SLAVE ); $expressionId = $record->getAttributeValue( $this->attribute ); $queryResult = $dbr->query( "SELECT language_id, spelling from {$dc}_expression WHERE expression_id=$expressionId" . " AND " . getLatestTransactionRestriction( "{$dc}_expression" ) ); $expression = $dbr->fetchObject( $queryResult ); $expressionRecord = new ArrayRecord( new Structure( $o->language, $o->spelling ) ); $expressionRecord->language = $expression->language_id; $expressionRecord->spelling = $expression->spelling; $result = new ArrayRecord( $this->structure ); $result->expression = $expressionRecord; return $result; }
private function getSQLForCollection( $language ) { $dc = wdGetDataSetContext(); $dbr = wfGetDB( DB_SLAVE ); $userlang = ' ( SELECT language_id FROM language WHERE wikimedia_key = ' . $dbr->addQuotes( $language ) . ' LIMIT 1 ) '; $sql = "SELECT collection_id, spelling " . " FROM {$dc}_expression exp, {$dc}_collection col, {$dc}_syntrans synt, {$dc}_defined_meaning dm " . " WHERE exp.expression_id=synt.expression_id " . " AND synt.defined_meaning_id=col.collection_mid " . " AND dm.defined_meaning_id = synt.defined_meaning_id " ; // " AND synt.identical_meaning=1" . // fallback is English $sql .= " AND ( exp.language_id=$userlang " ; if ( $userlang != 85 ) { $sql .= ' OR ( ' . ' language_id=85 ' . " AND NOT EXISTS ( SELECT * FROM {$dc}_syntrans synt2, {$dc}_expression exp2 WHERE synt2.defined_meaning_id = synt.defined_meaning_id AND exp2.expression_id = synt2.expression_id AND exp2.language_id=$userlang AND synt2.remove_transaction_id IS NULL LIMIT 1 ) ) " ; } $sql .= ' ) ' ; $sql .= " AND " . getLatestTransactionRestriction( "synt" ) . " AND " . getLatestTransactionRestriction( "exp" ) . " AND " . getLatestTransactionRestriction( "col" ) . " AND " . getLatestTransactionRestriction( "dm" ); return $sql; }
public function getRestriction( Table $table ) { return getLatestTransactionRestriction( $table->getIdentifier() ); }
protected function showExpressionsNeedingTranslation( $sourceLanguageId, $destinationLanguageId, $collectionId ) { $o = OmegaWikiAttributes::getInstance(); $dc = wdGetDataSetContext(); require_once( "Transaction.php" ); require_once( "OmegaWikiAttributes.php" ); require_once( "RecordSet.php" ); require_once( "Editor.php" ); require_once( "WikiDataAPI.php" ); $dbr = wfGetDB( DB_SLAVE ); $sqlcount = 'SELECT COUNT(*)' . " FROM ({$dc}_syntrans source_syntrans, {$dc}_expression source_expression)"; if ( $collectionId != '' ) $sqlcount .= " JOIN {$dc}_collection_contents ON source_syntrans.defined_meaning_id = member_mid"; $sqlcount .= ' WHERE source_syntrans.expression_id = source_expression.expression_id'; if ( $sourceLanguageId != '' ) $sqlcount .= ' AND source_expression.language_id = ' . $sourceLanguageId; if ( $collectionId != '' ) $sqlcount .= " AND {$dc}_collection_contents.collection_id = " . $collectionId . ' AND ' . getLatestTransactionRestriction( "{$dc}_collection_contents" ); $sqlcount .= ' AND NOT EXISTS (' . " SELECT * FROM {$dc}_syntrans destination_syntrans, {$dc}_expression destination_expression" . ' WHERE destination_syntrans.expression_id = destination_expression.expression_id AND destination_expression.language_id = ' . $destinationLanguageId . ' AND source_syntrans.defined_meaning_id = destination_syntrans.defined_meaning_id' . ' AND ' . getLatestTransactionRestriction( 'destination_syntrans' ) . ' AND ' . getLatestTransactionRestriction( 'destination_expression' ) . ')' . ' AND ' . getLatestTransactionRestriction( 'source_syntrans' ) . ' AND ' . getLatestTransactionRestriction( 'source_expression' ) ; $queryResultCount_r = mysql_query( $sqlcount ); $queryResultCount_a = mysql_fetch_row( $queryResultCount_r ); $queryResultCount = $queryResultCount_a[0]; $nbshown = min ( 100, $queryResultCount ) ; $sql = 'SELECT source_expression.expression_id AS source_expression_id, source_expression.language_id AS source_language_id, source_expression.spelling AS source_spelling, source_syntrans.defined_meaning_id AS source_defined_meaning_id' . " FROM ({$dc}_syntrans source_syntrans, {$dc}_expression source_expression)"; if ( $collectionId != '' ) $sql .= " JOIN {$dc}_collection_contents ON source_syntrans.defined_meaning_id = member_mid"; $sql .= ' WHERE source_syntrans.expression_id = source_expression.expression_id'; if ( $sourceLanguageId != '' ) $sql .= ' AND source_expression.language_id = ' . $sourceLanguageId; if ( $collectionId != '' ) $sql .= " AND {$dc}_collection_contents.collection_id = " . $collectionId . ' AND ' . getLatestTransactionRestriction( "{$dc}_collection_contents" ); $sql .= ' AND NOT EXISTS (' . " SELECT * FROM {$dc}_syntrans destination_syntrans, {$dc}_expression destination_expression" . ' WHERE destination_syntrans.expression_id = destination_expression.expression_id AND destination_expression.language_id = ' . $destinationLanguageId . ' AND source_syntrans.defined_meaning_id = destination_syntrans.defined_meaning_id' . ' AND ' . getLatestTransactionRestriction( 'destination_syntrans' ) . ' AND ' . getLatestTransactionRestriction( 'destination_expression' ) . ')' . ' AND ' . getLatestTransactionRestriction( 'source_syntrans' ) . ' AND ' . getLatestTransactionRestriction( 'source_expression' ) ; if ( $queryResultCount > 100 ) { $startnumber = rand ( 0 , $queryResultCount - 100 ) ; $sql .= " LIMIT $startnumber,100"; } else { $sql .= ' LIMIT 100'; } $queryResult = $dbr->query( $sql ); $definitionAttribute = new Attribute( "definition", wfMsg( "ow_Definition" ), "definition" ); $recordSet = new ArrayRecordSet( new Structure( $o->definedMeaningId, $o->expressionId, $o->expression, $definitionAttribute ), new Structure( $o->definedMeaningId, $o->expressionId ) ); while ( $row = $dbr->fetchObject( $queryResult ) ) { $expressionRecord = new ArrayRecord( $o->expressionStructure ); $expressionRecord->language = $row->source_language_id; $spellingAsLink = definedMeaningReferenceAsLink( $row->source_defined_meaning_id, $row->source_spelling, $row->source_spelling ); $expressionRecord->spelling = $spellingAsLink ; $definition = getDefinedMeaningDefinitionForLanguage( $row->source_defined_meaning_id, $row->source_language_id ) ; if ( $definition == "" ) { $definition = getDefinedMeaningDefinition( $row->source_defined_meaning_id ) ; } $recordSet->addRecord( array( $row->source_defined_meaning_id, $row->source_expression_id, $expressionRecord, $definition ) ); } $expressionEditor = new RecordTableCellEditor( $o->expression ); $expressionEditor->addEditor( new LanguageEditor( $o->language, new SimplePermissionController( false ), false ) ); $expressionEditor->addEditor( new ShortTextNoEscapeEditor( $o->spelling, new SimplePermissionController( false ), false ) ); $editor = new RecordSetTableEditor( null, new SimplePermissionController( false ), new ShowEditFieldChecker( true ), new AllowAddController( false ), false, false, null ); $editor->addEditor( $expressionEditor ); $editor->addEditor( new TextEditor( $definitionAttribute, new SimplePermissionController( false ), false, true, 75 ) ); global $wgOut; $wgOut->addHTML( "Showing $nbshown out of $queryResultCount" ) ; $wgOut->addHTML( $editor->view( new IdStack( "expression" ), $recordSet ) ); }
function execute( $par ) { global $wgOut, $wgUser, $wgRequest; if ( !$wgUser->isAllowed( 'exporttsv' ) ) { $wgOut->addHTML( wfMsg( 'ow_exporttsv_not_allowed' ) ); return false; } $dbr = wfGetDB( DB_SLAVE ); $dc = wdGetDataSetcontext(); if ( $wgRequest->getText( 'collection' ) && $wgRequest->getText( 'languages' ) ) { // render the tsv file require_once( 'WikiDataAPI.php' ); require_once( 'Transaction.php' ); // get the collection to export. Cut off the 'cid' part that we added // to make the keys strings rather than numbers in the array sent to the form. $collectionId = substr( $wgRequest->getText( 'collection' ), 3 ); // get the languages requested, turn into an array, trim for spaces. $isoCodes = explode( ',', $wgRequest->getText( 'languages' ) ); for ( $i = 0; $i < count( $isoCodes ); $i++ ) { $isoCodes[$i] = trim( $isoCodes[$i] ); if ( !getLanguageIdForIso639_3( $isoCodes[$i] ) ) { $wgOut->setPageTitle( wfMsg( 'ow_exporttsv_export_failed' ) ); $wgOut->addHTML( wfMsg( 'ow_impexptsv_unknown_lang', $isoCodes[$i] ) ); return false; } } $wgOut->disable(); $languages = $this->getLanguages( $isoCodes ); $isoLookup = $this->createIsoLookup( $languages ); $downloadFileName = $this->createFileName( $isoCodes ); // Force the browser into a download header( 'Content-Type: text/tab-separated-values;charset=utf-8' ); header( 'Content-Disposition: attachment; filename="' . $downloadFileName . '"' ); // attachment // separator character used. $sc = "\t"; echo( pack( 'CCC', 0xef, 0xbb, 0xbf ) ); // start the first row: column names echo( 'defined meaning id' . $sc . 'defining expression' ); foreach ( $isoCodes as $isoCode ) { echo( $sc . 'definition_' . $isoCode . $sc . 'translations_' . $isoCode ); } echo( "\r\n" ); // get all the defined meanings in the collection $query = "SELECT dm.defined_meaning_id, exp.spelling "; $query .= "FROM {$dc}_collection_contents col, {$dc}_defined_meaning dm, {$dc}_expression exp "; $query .= "WHERE col.collection_id=" . $collectionId . " "; $query .= "AND col.member_mid=dm.defined_meaning_id "; $query .= "AND dm.expression_id = exp.expression_id "; $query .= "AND " . getLatestTransactionRestriction( "col" ); $query .= "AND " . getLatestTransactionRestriction( "dm" ); $query .= "AND " . getLatestTransactionRestriction( "exp" ); $query .= "ORDER BY exp.spelling"; // wfDebug($query."\n"); $queryResult = $dbr->query( $query ); while ( $row = $dbr->fetchRow( $queryResult ) ) { $dm_id = $row['defined_meaning_id']; // echo the defined meaning id and the defining expression echo( $dm_id ); echo( "\t" . $row['spelling'] ); // First we'll fill an associative array with the definitions and // translations. Then we'll use the isoCodes array to put them in the // proper order. // the associative array holding the definitions and translations $data = array(); // **************************** // query to get the definitions // **************************** $qry = 'SELECT txt.text_text, trans.language_id '; $qry .= "FROM {$dc}_text txt, {$dc}_translated_content trans, {$dc}_defined_meaning dm "; $qry .= 'WHERE txt.text_id = trans.text_id '; $qry .= 'AND trans.translated_content_id = dm.meaning_text_tcid '; $qry .= "AND dm.defined_meaning_id = $dm_id "; $qry .= 'AND trans.language_id IN ('; for ( $i = 0; $i < count( $languages ); $i++ ) { $language = $languages[$i]; if ( $i > 0 ) $qry .= ","; $qry .= $language['language_id']; } $qry .= ') AND ' . getLatestTransactionRestriction( 'trans' ); $qry .= 'AND ' . getLatestTransactionRestriction( 'dm' ); // wfDebug($qry."\n"); // uncomment this if you accept having 1700+ queries in the log $definitions = $dbr->query( $qry ); while ( $row = $dbr->fetchRow( $definitions ) ) { // $key becomes something like def_eng $key = 'def_' . $isoLookup['id' . $row['language_id']]; $data[$key] = $row['text_text']; } $dbr->freeResult( $definitions ); // ***************************** // query to get the translations // ***************************** $qry = "SELECT exp.spelling, exp.language_id "; $qry .= "FROM {$dc}_expression exp "; $qry .= "INNER JOIN {$dc}_syntrans trans ON exp.expression_id=trans.expression_id "; $qry .= "WHERE trans.defined_meaning_id=$dm_id "; $qry .= "AND " . getLatestTransactionRestriction( "exp" ); $qry .= "AND " . getLatestTransactionRestriction( "trans" ); // wfDebug($qry."\n"); // uncomment this if you accept having 1700+ queries in the log $translations = $dbr->query( $qry ); while ( $row = $dbr->fetchRow( $translations ) ) { // qry gets all languages, we filter them here. Saves an order // of magnitude execution time. if ( isset( $isoLookup['id' . $row['language_id']] ) ) { // $key becomes something like trans_eng $key = 'trans_' . $isoLookup['id' . $row['language_id']]; if ( !isset( $data[$key] ) ) $data[$key] = $row['spelling']; else $data[$key] = $data[$key] . '|' . $row['spelling']; } } $dbr->freeResult( $translations ); // now that we have everything, output the row. foreach ( $isoCodes as $isoCode ) { // if statements save a bunch of notices in the log about // undefined indices. echo( "\t" ); if ( isset( $data['def_' . $isoCode] ) ) echo( $this->escapeDelimitedValue( $data['def_' . $isoCode] ) ); echo( "\t" ); if ( isset( $data['trans_' . $isoCode] ) ) echo( $data['trans_' . $isoCode] ); } echo( "\r\n" ); } } else { // Get the collections $colQuery = "SELECT col.collection_id, exp.spelling " . "FROM {$dc}_collection col INNER JOIN {$dc}_defined_meaning dm ON col.collection_mid=dm.defined_meaning_id " . "INNER JOIN {$dc}_expression exp ON dm.expression_id=exp.expression_id " . "WHERE " . getLatestTransactionRestriction( 'col' ); $collections = array(); $colResults = $dbr->query( $colQuery ); while ( $row = $dbr->fetchRow( $colResults ) ) { $collections['cid' . $row['collection_id']] = $row['spelling']; } // render the page $wgOut->setPageTitle( wfMsg( 'ow_exporttsv_title' ) ); $wgOut->addHTML( wfMsg( 'ow_exporttsv_header' ) ); $wgOut->addHTML( getOptionPanel( array( wfMsg( 'ow_Collection_colon' ) => getSelect( 'collection', $collections, 'cid376322' ), wfMsg( 'ow_exporttsv_languages' ) => getTextBox( 'languages', 'ita, eng, deu, fra, cat' ), ), '', array( 'create' => wfMsg( 'ow_create' ) ) ) ); } }
function searchExternalIdentifiers($text, $collectionId) { $dc = wdGetDataSetContext(); $dbr = wfGetDB(DB_SLAVE); $sql = "SELECT " . $this->getPositionSelectColumn($text, "{$dc}_collection_contents.internal_member_id") . " {$dc}_collection_contents.member_mid AS member_mid, {$dc}_collection_contents.internal_member_id AS external_identifier, {$dc}_collection.collection_mid AS collection_mid " . "FROM {$dc}_collection_contents, {$dc}_collection "; $sql .= "WHERE {$dc}_collection.collection_id={$dc}_collection_contents.collection_id " . " AND " . getLatestTransactionRestriction("{$dc}_collection") . " AND " . getLatestTransactionRestriction("{$dc}_collection_contents") . $this->getSpellingRestriction($text, "{$dc}_collection_contents.internal_member_id"); if ($collectionId > 0) { $sql .= " AND {$dc}_collection.collection_id={$collectionId} "; } $sql .= " ORDER BY " . $this->getSpellingOrderBy($text) . "{$dc}_collection_contents.internal_member_id ASC limit 100"; $queryResult = $dbr->query($sql); $recordSet = $this->getExternalIdentifiersSearchResultAsRecordSet($queryResult); $editor = $this->getExternalIdentifiersSearchResultEditor(); return $editor->view(new IdStack("external-identifiers"), $recordSet); }
function execute( $par ) { global $wgOut, $wgUser; // These operations should always be on the community database. $dc = "uw"; require_once( 'Transaction.php' ); $wgOut->setPageTitle( wfMsg( 'importlangnames_title' ) ); if ( !$wgUser->isAllowed( 'languagenames' ) ) { $wgOut->addHTML( wfMsg( 'importlangnames_not_allowed' ) ); return false; } $dbr = wfGetDB( DB_MASTER ); /* Get collection ID for "ISO 639-3 codes" collection. if we want to find $collection_id , we can use the following query */ /* $sql = "SELECT collection_id FROM {$dc}_collection" . " JOIN {$dc}_defined_meaning ON defined_meaning_id = collection_mid" . " JOIN {$dc}_expression ON" . " {$dc}_defined_meaning.expression_id = {$dc}_expression.expression_id" . ' WHERE spelling LIKE "ISO 639-3 codes"' . ' AND ' . getLatestTransactionRestriction( "{$dc}_collection" ) . ' LIMIT 1'; $collection_id_res = $dbr->query( $sql ); $collection_id = $this->fetchResult( $dbr->fetchRow( $collection_id_res ) ); */ // but having "ISO 639-3 codes" hardcoded is the same as having "145264" hardcoded $collection_id = 145264 ; /* Get defined meaning IDs and ISO codes for languages in collection. */ $sql = "SELECT member_mid,internal_member_id FROM {$dc}_collection_contents" . ' WHERE collection_id = ' . $collection_id . ' AND ' . getLatestTransactionRestriction( "{$dc}_collection_contents" ); $lang_res = $dbr->query( $sql ); $editable = ''; $first = true; while ( $lang_row = $dbr->fetchRow( $lang_res ) ) { $iso_code = $lang_row['internal_member_id']; $dm_id = $lang_row['member_mid']; /* Get the language ID for the current language. */ $lang_id = getLanguageIdForIso639_3( $iso_code ) ; if ( $lang_id ) { if ( !$first ) { $wgOut->addHTML( '<br />' . "\n" ); } else { $first = false; } $wgOut->addHTML( wfMsg( 'importlangnames_added', $iso_code ) ); /* Add current language to list of portals/DMs. */ $sql = "SELECT spelling FROM {$dc}_expression" . " JOIN {$dc}_defined_meaning ON {$dc}_defined_meaning.expression_id = {$dc}_expression.expression_id" . ' WHERE defined_meaning_id = ' . $dm_id . ' LIMIT 1'; $dm_expr_res = $dbr->query( $sql ); $dm_expr = $this->fetchResult( $dbr->fetchRow( $dm_expr_res ) ); if ( $editable != '' ) $editable .= "\n"; $editable .= '*[[Portal:' . $iso_code . ']] - [[DefinedMeaning:' . $dm_expr . ' (' . $dm_id . ')]]'; /* Delete all language names that match current language ID. */ $sql = 'DELETE FROM language_names' . ' WHERE language_id = ' . $lang_id; $dbr->query( $sql ); /* Get syntrans expressions for names of language and IDs for the languages the names are in. */ $sql = "SELECT spelling,language_id FROM {$dc}_expression" . " JOIN {$dc}_syntrans" . " ON {$dc}_expression.expression_id = {$dc}_syntrans.expression_id" . ' WHERE defined_meaning_id = ' . $dm_id . ' AND ' . getLatestTransactionRestriction( "{$dc}_expression" ) . ' AND ' . getLatestTransactionRestriction( "{$dc}_syntrans" ) . ' GROUP BY language_id ORDER BY NULL'; $syntrans_res = $dbr->query( $sql ); while ( $syntrans_row = $dbr->fetchRow( $syntrans_res ) ) { $sql = 'INSERT INTO language_names' . ' (`language_id`,`name_language_id`,`language_name`)' . ' VALUES(' . $lang_id . ', ' . $syntrans_row['language_id'] . ', ' . $dbr->addQuotes( $syntrans_row['spelling'] ) . ')'; $dbr->query( $sql ); } } else { if ( !$first ) $wgOut->addHTML( '<br />' . "\n" ); else $first = false; $wgOut->addHTML( wfMsg( 'importlangnames_not_found', $iso_code ) ); continue; } } $this->addDMsListToPage( $editable, 'Editable_languages' ); }
function execute( $par ) { require_once( 'languages.php' ); require_once( 'Transaction.php' ); global $wgOut, $wgLang, $wgRequest, $wgOptionAttribute; $wgOut->disable(); $dc = wdGetDataSetContext(); $optionAttribute = $wgRequest->getVal( $wgOptionAttribute ); $attributeObject = $wgRequest->getVal( 'attribute-object' ); $lang_code = $wgLang->getCode(); $dbr = wfGetDB( DB_SLAVE ); $sql = 'SELECT language_id' . " FROM {$dc}_syntrans" . " JOIN {$dc}_expression ON {$dc}_expression.expression_id = {$dc}_syntrans.expression_id" . " WHERE {$dc}_syntrans.syntrans_sid = " . $attributeObject . ' AND ' . getLatestTransactionRestriction( "{$dc}_syntrans" ) . ' AND ' . getLatestTransactionRestriction( "{$dc}_expression" ); $lang_res = $dbr->query( $sql ); $objectLanguage = $dbr->fetchObject( $lang_res )->language_id; // language is not always defined, for example for a DM Option Attribute if ( ! $objectLanguage ) $objectLanguage = 0 ; $sql = "SELECT {$dc}_option_attribute_options.option_id,{$dc}_option_attribute_options.option_mid" . " FROM {$dc}_option_attribute_options" . " WHERE {$dc}_option_attribute_options.attribute_id = " . $optionAttribute . " AND ({$dc}_option_attribute_options.language_id = " . $objectLanguage . " OR {$dc}_option_attribute_options.language_id = 0)" . ' AND ' . getLatestTransactionRestriction( "{$dc}_option_attribute_options" ) ; $options_res = $dbr->query( $sql ); $optionsString = ''; $optionsArray = array() ; while ( $options_row = $dbr->fetchObject( $options_res ) ) { /* Use a simpler query if the user's language is English. */ if ( $lang_code == 'en' || !( $lang_id = getLanguageIdForCode( $lang_code ) ) ) { $sql = "SELECT {$dc}_expression.spelling" . " FROM {$dc}_syntrans" . " JOIN {$dc}_expression ON {$dc}_expression.expression_id = {$dc}_syntrans.expression_id" . " WHERE {$dc}_syntrans.defined_meaning_id = " . $options_row->option_mid . " AND {$dc}_expression.language_id = " . getLanguageIdForCode( 'en' ) . ' AND ' . getLatestTransactionRestriction( "{$dc}_syntrans" ) . ' AND ' . getLatestTransactionRestriction( "{$dc}_expression" ); } /* Fall back on English in cases where an option name is not present in the user's preferred language. */ else { /* XXX: This setup is really hacked together. It NEEDS to be improved. */ $sql = "SELECT {$dc}_expression.spelling" . " FROM {$dc}_syntrans" . " JOIN {$dc}_expression ON {$dc}_expression.expression_id = {$dc}_syntrans.expression_id" . " WHERE {$dc}_syntrans.defined_meaning_id = " . $options_row->option_mid . " AND {$dc}_expression.language_id = " . $lang_id . ' AND ' . getLatestTransactionRestriction( "{$dc}_syntrans" ) . ' AND ' . getLatestTransactionRestriction( "{$dc}_expression" ); $res = $dbr->query( $sql ); if ( !$dbr->fetchObject( $res )->spelling ) $sql = "SELECT {$dc}_expression.spelling" . " FROM {$dc}_syntrans" . " JOIN {$dc}_expression ON {$dc}_expression.expression_id = {$dc}_syntrans.expression_id" . " WHERE {$dc}_syntrans.defined_meaning_id = " . $options_row->option_mid . " AND {$dc}_expression.language_id = " . getLanguageIdForCode( 'en' ) . ' AND ' . getLatestTransactionRestriction( "{$dc}_syntrans" ) . ' AND ' . getLatestTransactionRestriction( "{$dc}_expression" ); } $spelling_res = $dbr->query( $sql ); $spelling_row = $dbr->fetchObject( $spelling_res ); $optionsArray[$options_row->option_id] = $spelling_row->spelling ; } asort( $optionsArray ) ; foreach ($optionsArray as $option_id => $spelling ) { if ( $optionsString != '' ) $optionsString .= "\n"; $optionsString .= $option_id . ';' . $spelling ; } echo $optionsString; }
function execute( $par ) { global $wgOut, $wgUser, $wgRequest; $wgOut->setPageTitle( wfMsg( 'ow_importtsv_title1' ) ); if ( !$wgUser->isAllowed( 'importtsv' ) ) { $wgOut->addHTML( wfMsg( 'ow_importtsv_not_allowed' ) ); return false; } $dbr = wfGetDB( DB_MASTER ); $dc = wdGetDataSetcontext(); $wgOut->setPageTitle( wfMsg( 'ow_importtsv_importing' ) ); setlocale( LC_ALL, 'en_US.UTF-8' ); if ( $wgRequest->getFileName( 'tsvfile' ) ) { // ***************** // process tsv // ***************** require_once( 'WikiDataAPI.php' ); require_once( 'Transaction.php' ); $testRun = $wgRequest->getCheck( 'testrun' ); // lets do some tests first. Is this even a tsv file? // It is _very_ important that the file is utf-8 encoded. // also, this is a good time to determine the max line length for the // fgetcsv function. $file = fopen( $wgRequest->getFileTempname( 'tsvfile' ), 'r' ); $myLine = ""; $maxLineLength = 0; while ( $myLine = fgets( $file ) ) { if ( !preg_match( '/./u', $myLine ) ) { $wgOut->setPageTitle( wfMsg( 'ow_importtsv_import_failed' ) ); $wgOut->addHTML( wfMsg( 'ow_importtsv_not_utf8' ) ); return false; } $maxLineLength = max( $maxLineLength, strlen( $myLine ) + 2 ); } // start from the beginning again. Check if the column names are valid rewind( $file ); $columns = fgetcsv( $file, $maxLineLength, "\t" ); // somehow testing for $columns[0] fails sometimes. Byte Order Mark? if ( !$columns || count( $columns ) <= 2 || $columns[1] != "defining expression" ) { $wgOut->setPageTitle( wfMsg( 'ow_importtsv_import_failed' ) ); $wgOut->addHTML( wfMsg( 'ow_importtsv_not_tsv' ) ); return false; } for ( $i = 2; $i < count( $columns ); $i++ ) { $columnName = $columns[$i]; $baseName = substr( $columnName, 0, strrpos( $columnName, '_' ) ); if ( $baseName == "definition" || $baseName == "translations" ) { $langCode = substr( $columnName, strrpos( $columnName, '_' ) + 1 ); if ( !getLanguageIdForIso639_3( $langCode ) ) { $wgOut->setPageTitle( wfMsg( 'ow_importtsv_import_failed' ) ); $wgOut->addHTML( wfMsg( 'ow_impexptsv_unknown_lang', $langCode ) ); return false; } } else { // column name does not start with definition or translations. $wgOut->setPageTitle( wfMsg( 'ow_importtsv_import_failed' ) ); $wgOut->addHTML( wfMsg( 'ow_importtsv_bad_columns', $columnName ) ); return false; } } // // All tests passed. lets get started // if ( $testRun ) { $wgOut->setPageTitle( wfMsg( 'ow_importtsv_test_run_title' ) ); } else { $wgOut->setPageTitle( wfMsg( 'ow_importtsv_importing' ) ); } startNewTransaction( $wgUser->getID(), wfGetIP(), "Bulk import via Special:ImportTSV", $dc ); # this string shouldn't be localized because it will be stored in the db $row = ""; $line = 1; // actually 2, 1 was the header, but increased at the start of while $definitions = 0; // definitions added $translations = 0; // translations added while ( $row = fgetcsv( $file, $maxLineLength, "\t" ) ) { $line++; $dmid = $row[0]; $exp = $row[1]; // find the defined meaning record $qry = "SELECT dm.meaning_text_tcid, exp.spelling "; $qry .= "FROM {$dc}_defined_meaning dm INNER JOIN {$dc}_expression exp ON dm.expression_id=exp.expression_id "; $qry .= "WHERE dm.defined_meaning_id=$dmid "; $qry .= "AND " . getLatestTransactionRestriction( 'dm' ); $qry .= "AND " . getLatestTransactionRestriction( 'exp' ); $dmResult = $dbr->query( $qry ); $dmRecord = null; // perfomr some tests if ( $dmRecord = $dbr->fetchRow( $dmResult ) ) { if ( $dmRecord['spelling'] != $exp ) { $wgOut->addHTML( "Skipped line $line: defined meaning id $dmid does not match defining expression. Should be '{$dmRecord['spelling']}', found '$exp'.<br />" ); continue; } } else { $wgOut->addHTML( "Skipped line $line: unknown defined meaning id $dmid. The id may have been altered in the imported file, or the defined meaning or defining expression was removed from the database.<br />" ); continue; } // all is well. Get the translated content id $tcid = $dmRecord['meaning_text_tcid']; for ( $columnIndex = 2; $columnIndex < count( $columns ); $columnIndex++ ) { // Google docs removes empty columns at the end of a row, // so if column index is higher than the length of the row, we can break // and move on to the next defined meaning. if ( columnIndex >= count( $row ) ) { break; } $columnValue = $row[$columnIndex]; if ( !$columnValue ) { continue; } $columnName = $columns[$columnIndex]; $langCode = substr( $columnName, strrpos( $columnName, '_' ) + 1 ); $langId = getLanguageIdForIso639_3( $langCode ); if ( strpos( $columnName, 'definition' ) === 0 ) { if ( !translatedTextExists( $tcid, $langId ) ) { if ( $testRun ) { $wgOut->addHTML( "Would add definition for $exp ($dmid) in $langCode: $columnValue.<br />" ); } else { addTranslatedText( $tcid, $langId, $columnValue ); $wgOut->addHTML( "Added definition for $exp ($dmid) in $langCode: $columnValue.<br />" ); $definitions++; } } } if ( strpos( $columnName, 'translation' ) === 0 ) { $spellings = explode( '|', $columnValue ); foreach ( $spellings as $spelling ) { $spelling = trim( $spelling ); $expression = findExpression( $spelling, $langId ); if ( !$expression ) { // expression does not exist if ( $testRun ) { $wgOut->addHTML( "Would add translation for $exp ($dmid) in $langCode: $spelling. Would also add new page.<br />" ); } else { $expression = createExpression( $spelling, $langId ); $expression->bindToDefinedMeaning( $dmid, 1 ); // not nescesary to check page exists, createPage does that. $title = getPageTitle( $spelling ); createPage( 16, $title ); $wgOut->addHTML( "Added translation for $exp ($dmid) in $langCode: $spelling. Also added new page.<br />" ); $translations++; } } else { // expression exists, but may not be bound to this defined meaning. if ( !$expression->isBoundToDefinedMeaning( $dmid ) ) { if ( $testRun ) { $wgOut->addHTML( "Would add translation for $exp ($dmid) in $langCode: $spelling.<br />" ); } else { $expression->bindToDefinedMeaning( $dmid, 1 ); $wgOut->addHTML( "Added translation for $exp ($dmid) in $langCode: $spelling.<br />" ); $translations++; } } } } } } } if ( $definitions == 0 && $translations == 0 ) { $wgOut->addHTML( "<br />" ); if ( $testRun ) { $wgOut->addHTML( wfMsg( 'ow_importtsv_nothing_added_test' ) ); } else { $wgOut->addHTML( wfMsg( 'ow_importtsv_nothing_added' ) ); } $wgOut->addHTML( "<br />" ); } else { $wgOut->addHTML( "<br />" . wfMsgExt( 'ow_importtsv_results', 'parsemag', $definitions, $translations ) . "<br />" ); } } else { // render the page $wgOut->setPageTitle( wfMsg( 'ow_importtsv_title2' ) ); $wgOut->addHTML( wfMsg( 'ow_importtsv_header' ) ); $wgOut->addHTML( getOptionPanelForFileUpload( array( wfMsg( 'ow_importtsv_file' ) => getFileField( 'tsvfile' ), wfMsg( 'ow_importtsv_test_run' ) => getCheckBox( 'testrun', true ) ) ) ); } }
/** * @param DataSet where to look * @param Integer Defined Meaning Id * @param String Spelling * @return DataSet or null * @see checkExistence * */ public function checkExistenceInDataSet( DataSet $dc ) { $definingExpression = $this->definingExpression; $id = $this->getId(); $dbr = wfGetDB( DB_SLAVE ); $queryResult = $dbr->query( "SELECT defined_meaning_id, expression_id from {$dc}_defined_meaning where defined_meaning_id=" . $this->id . " AND " . getLatestTransactionRestriction( "{$dc}_defined_meaning" ) ); $dmRow = $dbr->fetchObject( $queryResult ); if ( !$dmRow || !$dmRow->defined_meaning_id ) { return null; } if ( is_null( $definingExpression ) ) { return $dc; } else { $expid = (int)$dmRow->expression_id; $storedExpression = getExpression( $expid, $dc ); if ( is_null( $storedExpression ) ) return null; if ( $storedExpression->spelling != $definingExpression ) { // Defining expression does not match, but check was requested! return null; } else { return $dc; } } }