MariaDB
Configuring the MariaDB connector
The UNGA-DM database is hosted on a MariaDB server. To access it you need to install a MariaDB connector on your computer, available for a myriad of operating sytems here: https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.11/ Once it is installed it can be configured for the UNGA-DM database with the following pieces of informationServer name: dbaas.unige.ch
Port: 30001
Database: unga_database
Querying the MariaDB UNGA-DM database
After this configuration the UNGA-DM database can be directly queried. In Stata
odbc query ("unga_database")
will list all tables in the database.
odbc load, exec("SELECT * FROM votes")
Will query the votes table, which will became the active data in Stata.
In R a package like RODBC needs to be installed, after which the UNGA-DM database can be queried directly.
library(RODBC)
channel <- odbcConnect("unga_database")
votes<- sqlQuery(channel, paste("SELECT * FROM votes"))
This, will store the information of the UNGA-DM votes table in the votes dataframe (after a connection with the database has been opened).
More complex queries like
unga<- sqlQuery(channel, paste("SELECT decisions.decision_id,
meeting_date, member_state, decision_mode, original_vote, important
FROM votes RIGHT JOIN decisions
ON decisions.decision_id = votes.decision_id
RIGHT JOIN meeting_records
ON decisions.meeting_record_id=meeting_records.meeting_record_id"))
will generate a table on votes to which information from the
decisions and meeting_records table have been merged and
stored in the unga dataframe.
In Stata the same can be achieved by using
odbc load, exec("SELECT decisions.decision_id,
meeting_date, member_state, decision_mode, original_vote, important
FROM votes RIGHT JOIN decisions
ON decisions.decision_id = votes.decision_id
RIGHT JOIN meeting_records
ON decisions.meeting_record_id=meeting_records.meeting_record_id")
The codebook of the UNGA-DM database offers detailed information on the tables available, the variables contained therein, and how they are linked, which is also depicted in the following entity-relationship diagram:
If you encounter problems or detect errors, please message and/or .
File translated from TEX by TTH, version 4.12.