marytts.tools.dbselection

Class DBHandler

java.lang.Object

marytts.tools.dbselection.DBHandler

public class DBHandler
extends Object

Various functions for handling connection, inserting and querying a mysql database.

Author:

Marcela Charfuelan, Holmer Hemsen.

Constructor Summary

Constructors

Constructor and Description
DBHandler(String localeVal) The constructor loads the database driver.

Method Summary

Methods

Modifier and Type	Method and Description
void	addLocalePrefixToWikipediaTables() Rename the Wikipedia tables adding the prefix locale: locale_text locale_page and locale_revision.
boolean	askIfDeletingTable(String table) Ask the user if the table should be deleted
boolean	checkWikipediaTables() check if tables: locale_text, locale_page and locale_revision exist.
void	closeDBConnection()
void	createAndLoadWikipediaTables(String textFile, String pageFile, String revisionFile) This function creates text, page and revision tables loading them from text files.
void	createDataBaseSelectionTable() Creates dbselectionTable
boolean	createDBConnection(String host, String db, String user, String passwd) The createDBConnection method creates the database connection.
boolean	createEmptyWikipediaTables() This function creates empty text, page and revision tables (without locale prefix).
void	createSelectedSentencesTable(String stopCriterion, String featDefFileName, String covDefConfigFileName) Creates a selectedSentencesTable.
void	createTablesDescriptionTable() This table contains information about tables in the DB, specially for selected sentences tables.
void	createWikipediaCleanTextTable()
void	deleteWikipediaTables() Delete the Wikipedia tables: locale_text, locale_page and locale_revision tables.
String	getCleanText(int id)
String	getCleanTextTableName()
String	getDBSelectionSentence(int id) Get a sentence from a locale_dbselection table.
String	getDBselectionTableName()
byte[]	getFeatures(int id)
byte[][]	getFeaturesBulk(int[] ids) Bulk load a set of features as identified by their IDs.
HashMap<Integer,byte[]>	getFeaturesSet(int ini, int end, int[] idList)
int[]	getIdListOfSelectedSentences(String actualTableName, String condition) Get a list of id's from a selected sentences table. NOTE: use the actual table name: local + tableName + selectedsentences
int[]	getIdListOfType(String table, String condition) Get a list of id's
String[]	getIds(String field, String table) Get a list of ids from field in table.
Pair<int[],byte[][]>	getIdsAndFeatureVectors(String table, String condition) For the set of sentences identified by table and condition, retrieve from Mysql both the sentence ids and the corresponding features.
ArrayList<String>	getListOfTables() Get the list of tables for this locale
HashMap<String,Integer>	getMostFrequentWords(int numWords, int maxFrequency) Get the most frequent words and its frequency in a HashMap.
ArrayList<String>	getMostFrequentWordsArray(int numWords, int maxFrequency) Get the most frequent words sorted by frequency (descendent) in an ArrayList.
int	getNumberOfReliableSentences()
int	getNumberOfWords(int maxFrequency) Get number of words in the wordList table.
String	getSelectedSentence(String tableName, int id) Get a
String	getSelectedSentencesTableName()
String[]	getTableDescription(String tableName) Get the description of the tableName
String	getTextFromWikiPage(String id, int minPageLength, StringBuilder old_id, PrintWriter pw)
int[]	getUnprocessedTextIds() This function will select just the unprocessed cleanText records.
String	getWordListTableName()
void	insertCleanText(String text, String page_id, String text_id)
void	insertSelectedSentence(int dbselection_id, boolean unwanted) With the dbselection_id get first the sentence and then insert it in the locale_selectedSentences table.
void	insertSentence(String sentence, byte[] features, boolean reliable, boolean unknownWords, boolean strangeSymbols, int cleanText_id) Insert processed sentence in dbselection
void	insertWordList(HashMap<String,Integer> wordList) Creates a wordList table, if already exists deletes it and creates a new to insert current wordList.
void	loadPagesWithMWDumper(String xmlFile, String lang, String host, String db, String user, String passwd) Use mwdumper for extracting pages from a XML wikipedia dump file.
static void	main(String[] args)
String	mysqlEscapeCharacters(String str) The following characteres should be escaped: \0 An ASCII 0 (NUL) character.
void	printWordList(String fileName, String order, int numWords, int maxFrequency)
void	setDBTable(String table)
void	setSelectedSentencesTableName(String name) By default the name of the selected sentence is "locale + _selectedSentences" with this function the name can be changed, the locale prefix will be kept and the suffix "_selectedSentences".
void	setSentenceRecord(int id, String field, boolean fieldValue) Set a sentence record field as true/false in dbselection table.
void	setTableDescription(String tableName, String description, String stopCriterion, String featuresDefinitionFileName, String covDefConfigFileName) Set a description for table = name, it checks if the table tablesDescription exist, if not it creates it.
void	setUnwantedSentenceRecord(String actualTableName, int id, boolean fieldValue) This function updates the unwanted field as true/false of dbselection TABLE and selectedSentencesTable TABLE.
void	setWordListTable(String table)
boolean	tableExist(String tableName)

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

DBHandler

public DBHandler(String localeVal)

The constructor loads the database driver.

Parameters:

localeVal - database language.

Method Detail

setSelectedSentencesTableName

public void setSelectedSentencesTableName(String name)

By default the name of the selected sentence is "locale + _selectedSentences" with this function the name can be changed, the locale prefix will be kept and the suffix "_selectedSentences". NOTE: If the name is changed this function should be called before calling createDBConnection() because ther prepared statments for each table are initialised.

Parameters:

name - name

getSelectedSentencesTableName

public String getSelectedSentencesTableName()

getCleanTextTableName

public String getCleanTextTableName()

getWordListTableName

public String getWordListTableName()

getDBselectionTableName

public String getDBselectionTableName()

createDBConnection

public boolean createDBConnection(String host,
                         String db,
                         String user,
                         String passwd)

The createDBConnection method creates the database connection.

Parameters:

host - a String value. The database host e.g. 'localhost'.

db - a String value. The database to connect to.

user - a String value. Database user that has excess to the database.

passwd - a String value. The 'secret' password.

Returns:

true if connection was succesfull, false otherwise

loadPagesWithMWDumper

public void loadPagesWithMWDumper(String xmlFile,
                         String lang,
                         String host,
                         String db,
                         String user,
                         String passwd)
                           throws Exception

Use mwdumper for extracting pages from a XML wikipedia dump file. The mwdumper reads a xml wikipedia file and extract the tables "text", "page" and "revision" in sql format. In this configuration the mwdumper creates a connection to the DB and load directly the tables, so the tables must be already created and they should be empty. Here these tables are created empty using the function createEmptyWikipediaTables, this function deletes any existing text, page and revision table before creating new ones.

Parameters:

xmlFile - xml dump file

lang - locale language

host - host

db - db

user - user

passwd - passwd

Throws:

Exception - Exception

askIfDeletingTable

public boolean askIfDeletingTable(String table)

Ask the user if the table should be deleted

Parameters:

table - table

Returns:

true if user answers false otherwise.

createTablesDescriptionTable

public void createTablesDescriptionTable()

This table contains information about tables in the DB, specially for selected sentences tables.

createDataBaseSelectionTable

public void createDataBaseSelectionTable()

Creates dbselectionTable

createSelectedSentencesTable

public void createSelectedSentencesTable(String stopCriterion,
                                String featDefFileName,
                                String covDefConfigFileName)

Creates a selectedSentencesTable.

Parameters:

stopCriterion - stopCriterion

featDefFileName - featDefFileName

covDefConfigFileName - covDefConfigFileName

createAndLoadWikipediaTables

public void createAndLoadWikipediaTables(String textFile,
                                String pageFile,
                                String revisionFile)

This function creates text, page and revision tables loading them from text files.

Parameters:

textFile - textFile

pageFile - pageFile

revisionFile - revisionFile

createEmptyWikipediaTables

public boolean createEmptyWikipediaTables()

This function creates empty text, page and revision tables (without locale prefix). If the tables already exist it will delete them.

Returns:

true if the tables were created succesfully, false otherwise.

addLocalePrefixToWikipediaTables

public void addLocalePrefixToWikipediaTables()

Rename the Wikipedia tables adding the prefix locale: locale_text locale_page and locale_revision. if any of the locale_* tables exist will be deleted before renaming the table.

deleteWikipediaTables

public void deleteWikipediaTables()

Delete the Wikipedia tables: locale_text, locale_page and locale_revision tables.

checkWikipediaTables

public boolean checkWikipediaTables()

check if tables: locale_text, locale_page and locale_revision exist.

Returns:

resText && resPage && resRevision

createWikipediaCleanTextTable

public void createWikipediaCleanTextTable()

tableExist

public boolean tableExist(String tableName)

Parameters:

tableName - tableName

Returns:

true if TABLE=tableName exist.

getIds

public String[] getIds(String field,
              String table)

Get a list of ids from field in table.

Parameters:

field - field

table - table

Returns:

idSet

getUnprocessedTextIds

public int[] getUnprocessedTextIds()

This function will select just the unprocessed cleanText records.

Returns:

idSet

getListOfTables

public ArrayList<String> getListOfTables()

Get the list of tables for this locale

Returns:

ArrayList<String>

setDBTable

public void setDBTable(String table)

setWordListTable

public void setWordListTable(String table)

insertCleanText

public void insertCleanText(String text,
                   String page_id,
                   String text_id)

insertSentence

public void insertSentence(String sentence,
                  byte[] features,
                  boolean reliable,
                  boolean unknownWords,
                  boolean strangeSymbols,
                  int cleanText_id)

Insert processed sentence in dbselection

Parameters:

sentence - text of the sentence.

features - features if sentences is reliable.

reliable - true/false.

unknownWords - true/false.

strangeSymbols - true/false.

cleanText_id - the id of the cleanText this sentence comes from.

insertSelectedSentence

public void insertSelectedSentence(int dbselection_id,
                          boolean unwanted)

With the dbselection_id get first the sentence and then insert it in the locale_selectedSentences table.

Parameters:

dbselection_id - dbselection_id

unwanted - unwanted

insertWordList

public void insertWordList(HashMap<String,Integer> wordList)

Creates a wordList table, if already exists deletes it and creates a new to insert current wordList.

Parameters:

wordList - wordList

closeDBConnection

public void closeDBConnection()

getNumberOfReliableSentences

public int getNumberOfReliableSentences()

getIdListOfType

public int[] getIdListOfType(String table,
                    String condition)

Get a list of id's

Parameters:

table - cleanText, wordList, dbselection (no need to add locale) (NOTE: this function does not work for the selectedSentences table, for this table use the function getIdListOfSelectedSentences ).

condition - reliable, unknownWords, strangeSymbols, selected, unwanted = true/false (combined are posible: "reliable=true and unwanted=false"); or condition=null for querying without condition.

Returns:

int array or null if the list of id's is empty.

getIdsAndFeatureVectors

public Pair<int[],byte[][]> getIdsAndFeatureVectors(String table,
                                           String condition)

For the set of sentences identified by table and condition, retrieve from Mysql both the sentence ids and the corresponding features.

Parameters:

table - table

condition - condition

Returns:

Pair<int[], byte[][]>(idSet, features)

getIdListOfSelectedSentences

public int[] getIdListOfSelectedSentences(String actualTableName,
                                 String condition)

Get a list of id's from a selected sentences table.
NOTE: use the actual table name: local + tableName + selectedsentences

Parameters:

actualTableName - = locale_tableName_selectedSentences

condition - unwanted=true/false

Returns:

idSet

getNumberOfWords

public int getNumberOfWords(int maxFrequency)

Get number of words in the wordList table.

Parameters:

maxFrequency - max frequency of a word to be considered in the list, if maxFrequency=0 it will retrieve all the words with frequency≥1.

Returns:

int number of words.

getMostFrequentWords

public HashMap<String,Integer> getMostFrequentWords(int numWords,
                                           int maxFrequency)

Get the most frequent words and its frequency in a HashMap.

Parameters:

numWords - max number of words to retrieve, if numWords=0 then it will retrieve all the words in the list in descending order of frequency.

maxFrequency - max frequency of a word to be considered in the list, if maxFrequency=0 it will retrieve all the words with frequency≥1.

Returns:

wordList

getMostFrequentWordsArray

public ArrayList<String> getMostFrequentWordsArray(int numWords,
                                          int maxFrequency)

Get the most frequent words sorted by frequency (descendent) in an ArrayList.

Parameters:

numWords - max number of words to retrieve, if numWords=0 then it will retrieve all the words in the list in descending order of frequency.

maxFrequency - max frequency of a word to be considered in the list, if maxFrequency=0 it will retrieve all the words with frequency≥1.

Returns:

words

printWordList

public void printWordList(String fileName,
                 String order,
                 int numWords,
                 int maxFrequency)

Parameters:

fileName - file to write the list

order - word or frequency

numWords - max number of words, if numWords=0 then it will retrieve all the words in the list.

maxFrequency - max frequency of a word to be considered in the list, if maxFrequency=0 it will retrieve all the words with frequency≥1.

getDBSelectionSentence

public String getDBSelectionSentence(int id)

Get a sentence from a locale_dbselection table.

Parameters:

id - dbselection (no need to add locale)

Returns:

String sentence

getSelectedSentence

public String getSelectedSentence(String tableName,
                         int id)

Get a

Parameters:

tableName - tableName

id - id

Returns:

sentence

getTextFromWikiPage

public String getTextFromWikiPage(String id,
                         int minPageLength,
                         StringBuilder old_id,
                         PrintWriter pw)

getCleanText

public String getCleanText(int id)

setSentenceRecord

public void setSentenceRecord(int id,
                     String field,
                     boolean fieldValue)

Set a sentence record field as true/false in dbselection table.

Parameters:

id - id

field - reliable, unknownWords, strangeSymbols, selected or unwanted = true/false

fieldValue - true/false (as string)

setUnwantedSentenceRecord

public void setUnwantedSentenceRecord(String actualTableName,
                             int id,
                             boolean fieldValue)

This function updates the unwanted field as true/false of dbselection TABLE and selectedSentencesTable TABLE.
NOTE: use the actual table name: local + tableName + selectedsentences

Parameters:

actualTableName - including local and _selectedSentences

id - id in dbselection table

fieldValue - true/false

setTableDescription

public void setTableDescription(String tableName,
                       String description,
                       String stopCriterion,
                       String featuresDefinitionFileName,
                       String covDefConfigFileName)

Set a description for table = name, it checks if the table tablesDescription exist, if not it creates it.

Parameters:

tableName - the name of the table, it can not be null

description - if no description set to null

stopCriterion - if no stopCriterion set to null

featuresDefinitionFileName - if no featuresDefinitionFileName set to null

covDefConfigFileName - if no covDefConfigFileNamen set to null

getTableDescription

public String[] getTableDescription(String tableName)

Get the description of the tableName

Parameters:

tableName - tableName

Returns:

and String array where: desc[0] tableName desc[1] description desc[2] stopCriterion desc[3] featuresDefinitionFileName desc[4] featuresDefinitionFile desc[5] covDefConfigFileName desc[6] covDefConfigFile

getFeatures

public byte[] getFeatures(int id)

getFeaturesBulk

public byte[][] getFeaturesBulk(int[] ids)

Bulk load a set of features as identified by their IDs.

Parameters:

ids - a sorted array of feature IDs.

Returns:

an array of coverage features, of the same length as the input array.

getFeaturesSet

public HashMap<Integer,byte[]> getFeaturesSet(int ini,
                                     int end,
                                     int[] idList)

mysqlEscapeCharacters

public String mysqlEscapeCharacters(String str)

The following characteres should be escaped: \0 An ASCII 0 (NUL) character. \' A single quote (“'”) character. \" A double quote (“"”) character.

Parameters:

str - str

Returns:

str

main

public static void main(String[] args)
                 throws Exception

Throws:

Exception