Taghelper 2.0

These instructions will become effective soon but please refer to Taghelper 1.0 below this section until the transfer.

Installation

Dependencies

• python (3.7.4+)

pip install -r requirements.txt

Configuration

Adding new computers to mariadb

Any computer that will run these scripts needs to be recognized by the db. You must create a database user for each computer before attempting to run the scripts.

Check your computer's current ip address. You can do this by running

ipconfig

on windows or

ifconfig

on linux. Use ssh to connect to the computer or server hosting the database.

ssh [hostname]@[hostip]

Open the mariadb configuration console.

sudo mariadb

Run the following SQL queries to add a new user.

CREATE USER '[the computer being added's username]'@'[the computer being added's ip]' IDENTIFIED BY '[password]';
GRANT ALL PRIVILEGES ON [database_name].* TO '[the computer being added's username]'@'[the computer being added's ip]';
FLUSH PRIVILEGES;

You can verify the new user's existance with:

SELECT User, Host FROM mysql.user;

Exit the database by typing:

EXIT;

followed by:

exit

Verify that the scripts are now functional on this computer by running the query-db.py script.

python query-db.py

Accessing a database on a different server than default (Nebula).

The tagserver is currently hosted on Nebula. If you have moved the database to another host and want to change the scripts to access this host instead, you need to update the ip and hostname in the config.

Open id-upload.py (or whichever script you are trying to reconfigure) in a text editor.

Locate the try/except block towards the top of the page. If the script you are in doesn't have one that looks like this, it doesn't need to be reconfigured.

try:
    conn = mariadb.connect(
        user="topplab",
        password=getpass.getpass(prompt='Database user password: '),
        host="10.16.0.101", #Nebula's relational ip!
        port=3306,
        database="tag_server"
    )
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

Update the username field to the new host's username, the host field to the new host's ip address, and the database field to the new host's database for tags (if changed).

Logging into Adminer

The database manager, Adminer, is currently set up on Nebula to help manage this database. This is how you can log into Adminer from any computer on the same network as Nebula.

In the browser of your choice (this has only been tested in chrome though), navigate to

[ipaddress of server]/adminer

Currently this is

http://10.16.0.101/adminer

You should see a screen that looks like this:

Keep localhost the same as it is referring to the localhost of the ip address, not your machine.

Fill in your login details and click login. Database is an optional field and should probably usually be left blank to access the entire server.

Adding new tables (for new sample types) to mariadb.

The database currenty has a central table for basic information, as well as tables for biomass, core wholes, core segments, and crowns. You will likely need to add more tables to accomadate new projects in the future. This database was designed to be flexible and easily expandable.

Log into Adminer (following the steps above). Select the database you want to add tables to (currently named "Samples").

Choose the "Create Table" option from the menu.

Taghelper 1.0

WARNING These instructions will be depreciated after the transfer to Taghelper 2.0

This is a collection of scripts that provide various functions to interact with a Mariadb database that contains sample barcode strings. The database is currently hosted on nebula, although this can be modified in the future.

These tools are customized to the needs of the Topp Lab in 2023 but should be easily modifed to fit future changes.

Installation

Dependencies

• python (3.7.4+)

pip install -r requirements.txt

Configuration

Adding new computers to mariadb

Any computer that will run these scripts needs to be recognized by the db. You must create a database user for each computer before attempting to run the scripts.

Check your computer's current ip address. You can do this by running

ipconfig

on windows or

ifconfig

on linux. Use ssh to connect to the computer or server hosting the database.

ssh [hostname]@[hostip]

Open the mariadb configuration console.

sudo mariadb

Run the following SQL queries to add a new user.

CREATE USER '[the computer being added's username]'@'[the computer being added's ip]' IDENTIFIED BY '[password]';
GRANT ALL PRIVILEGES ON [database_name].* TO '[the computer being added's username]'@'[the computer being added's ip]';
FLUSH PRIVILEGES;

You can verify the new user's existance with:

SELECT User, Host FROM mysql.user;

Exit the database by typing:

EXIT;

followed by:

exit

Verify that the scripts are now functional on this computer by running the query-db.py script.

python query-db.py

Accessing a database on a different server than default (Nebula).

The tagserver is currently hosted on Nebula. If you have moved the database to another host and want to change the scripts to access this host instead, you need to update the ip and hostname in the config.

Open id-upload.py (or whichever script you are trying to reconfigure) in a text editor.

Locate the try/except block towards the top of the page. If the script you are in doesn't have one that looks like this, it doesn't need to be reconfigured.

try:
    conn = mariadb.connect(
        user="topplab",
        password=getpass.getpass(prompt='Database user password: '),
        host="10.16.0.101", #Nebula's relational ip!
        port=3306,
        database="tag_server"
    )
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

This part is fairly self-explanatory. Update the username field to the new host's username, the host field to the new host's ip address, and the database field to the new host's database for tags (if changed).

How do I use ___.py?

Using id-upload.py

id-upload.py is the main script used to upload tags and generate ids. Here's how to use it :)

1. Open the command line and navigate to the directory containing the script by using

cd [directory name]

2. Run the script by typing

python id-upload.py

3. You will be prompted for the database password. Enter it in the command line and press enter.

4. Click the big "browse" button at the top to open a file explorer in which you will locate and open the excel sheet you plan to use.

5. Go into your workbook and double click on the sheet name in the tab at the bottom. Use Ctl+C to copy this text exactly. Paste it into the area that says "Sheet name".

6. Find the col number containing your barcode strings by counting the columns, starting with 1 from the left. Type this number into the appropriate box.

7. Find the first row number containing ACTUAL DATA and enter this into the next area.

8. Find the last row number containing data. MAKE SURE THERE ARE NO EMPTY BOXES IN BETWEEN THE FIRST AND LAST ROW IN THE SELECTED COL.

9. MAKE SURE YOU CLOSE THE EXCEL DOCUMENT BEFORE UPLOADING!!

10. Click "Upload". Monitor the console and be sure that the "Reading Row: x" counter goes until the last row number.

11. Find the col number for UIDs in much the same way as finding the col number for the barcodes. Count columns starting from the left. Enter the value in the appropriate box.

12. Find the first row number to save UIDs in. THIS SHOULD BE THE SAME AS THE FIRST ROW OF BARCODE VALUES! Enter it in the correct box.

13. MAKE SURE YOU CLOSE THE EXCEL DOCUMENT BEFORE WRITING!!

14. Click "Write". Monitor the console and wait for the message "Write Successful" to be printed.

15. Open your document and make sure that the numbers seem to be consistent. You can use the script

python query-db.py

to make sure that all values are the same in the database.

17. You're done! I hope that was easy enough to understand and follow.