5.8 MySQL on localhost

CiteSpace provides a user interface to a MySQL database on localhost. The user interface provides various functions to import and export records in connection with the database.

Before you can use this group of functions, you need to set up your MySQL as follows.

On your computer, locate your own User folder and find the .citespace folder. Create a text file mysql.ini with the name-value pairs separated by a tab as the content:

host localhost

user user_id

pass password

where user_id and password are your user id and password for your own MySQL login.After connecting to the database, you will see existing projects, i.e. projects that have been loaded to the database. Note the projects here are stored in the database and they are different from the projects appeared on the main interface of CiteSpace, which are file-based, i.e. the files you downloaded from the Web of Science. You can import the downloaded files to the database and edit them accordingly and export to files in the Web of Science format.

Since the database is a MySQL database on localhost, you can access the database directly with your own MySQL login. You can use this database to process your data before you apply visualization functions on them.

Make sure that your MySQL server is on before you use this function in CiteSpace.

Structure of the Database

The name of the database is wos. It contains the following tables:

TABLE articles

id(int), uid, project, author, title, abstract, source, j9, volume, issue, bp, ep, page, dt, doi, year(int), month(int), date(int), citations(int), editor, tagged(boolean)

TABLE authors

id(int), lastname, firstname, initials, project, uid, pos

TABLE refs

id(int), bibcode, ref, doi, author, year, source, volume, page, citer_uid, project

TABLE keywords

id(int), keyword, uid, year, project, type

TABLE phrases

id(int), phrase, isTitlePhrase(booelan), project, uid, year(int), month(int), date(int), freq(int)

TABLE verbs

id(int), project, uid, verb, freq

TABLE bursts

id(int), project, term, weight(double), start(int), end(int)

TABLE institutions

id(int), name, country, uid, year(int), projectArticles ►Most Cited Articles

How to Export a Set of Records in the Web of Science Format from MySQL

You can query the database with a few built-in functions on a loaded dataset. For example, you can find the most cited articles in the current project. The SQL query is displayed along with the results. It will help you to get familiar with the internal structure of the database.

You can also clean up the data within MySQL and then export the clean data from MySQL to files in the Web of Science format.

Here are the steps:

  1. Select the project in the MySQL database that you want to draw data from
  2. Pull down menu Project > Export to Files in the WoS Format

Step 2 contains two steps: Select the data and Export the selected data set to files.

Select a Subset for Export

Once you select this menu item or press the Search button, CiteSpace will prompt you with a template SQL query similar to the one shown below so that you can modify the query to suit your own purpose.

SELECT uid FROM articles WHERE project='terrorism' and dt='Article' and year<=2000

The idea is to define a subset in terms of the uid field. If you want to select the entire data of the project, simply use the following query:

SELECT uid FROM articles WHERE project='terrorism'

Once you are happy with your query, click on the Search button. You will see results like this:

ISI:A1990DH62200002

ISI:A1990FY94400003

ISI:A1990EQ25700005

ISI:A1990FU90400003

Now move to the next step.

Export Data (WoS)

Choose this step from the menu or press the Save As WoS button. CiteSpace will ask you to select or create a directory to save files in the WoS format. Then wait until the export process to complete.

CiteSpace will save up to 300 records to a file. Multiple files will be generated if the total is more than 300 and named accordingly. For example, the sample query for the Terrorism project would export 586 records published before 2001:

download_Terrorism_300_of_586

download_Terrorism_586_of_586