# Useful SQL commands

There are a lot more SQL commands in the following section:

{% content-ref url="oe-settings" %}
[oe-settings](https://open-enventory.gitbook.io/user-guides/advanced-settings/oe-settings)
{% endcontent-ref %}

## CHANGING INFO OF MULTIPLE CHEMICALS

Install phpMyAdmin (See [OE Settings](https://open-enventory.gitbook.io/user-guides/oe-settings#changing-location-of-multiple-containers))

###

### To return all chemicals borrow by somebody (borrowed\_by\_person\_id)

1. Look up `person_id` by going into \[*Database*]/person
2. Show chemical borrow by that person (i.e. a person with person\_id=8)

```sql
SELECT * FROM chemical_storage WHERE borrowed_by_person_id=8
```

    3\. Return by:

```sql
UPDATE chemical_storage SET borrowed_by_person_id=NULL WHERE borrowed_by_person_id=8
```

###

### To change the owner info of a chemical (owner\_person\_id)

1. Look up `person_id` by going into \[*Database*]/person
2. Show chemical owned by that person (i.e. a person with person\_id=7)

```sql
SELECT * FROM chemical_storage WHERE owner_person_id=7
```

   3\. Change, in this case, to no owner (NULL)

```sql
UPDATE chemical_storage SET owner_person_id=NULL WHERE owner_person_id=7
```

###

### Find person\_id using Console command line in PHPMyAdmin

In the following example, show `person_id` of user with username=julia

```sql
SELECT person_id FROM person WHERE username='julia'
```

###

### Find storage ID using Console Command line in PHPMyAdmin

1. First choose the desired database on the tree folder from the left
2. Use:

```sql
SELECT storage_id FROM storage WHERE storage_name LIKE 'FH-2206%'
```

 Explain:

* `LIKE 'FH-2206%'` : show the `storage_id` of the storage location with name **starting** with FH-2206
* `LIKE '%FH-2206%'` would mean find anything with storage name **containing** FH-2206
* `LIKE 'FH-2206%'` would mean find anything with storage name **ending** with FH-2206

###

### Show chemicals with multiple criteria

```sql
SELECT * FROM chemical_storage 
WHERE owner_person_id=15 AND storage_id NOT LIKE 18
```

Explain: the above command is used to

* Select all of the chemical in the database that match: owner\_person\_id=15 **AND** with storage\_id is **NOT** 18

###

### Change Responsible person for chemicals with multiple criteria

```sql
UPDATE chemical_storage 
SET owner_person_id='NULL' 
WHERE owner_person_id=15 AND storage_id NOT LIKE 18
```

 Explain: see above for explanation of the criteria.

###

### Show storage\_name and storage\_id using username

```sql
SELECT storage_name,storage_id 
FROM storage 
WHERE storage_id IN
    (SELECT storage_id 
     FROM chemical_storage 
     WHERE owner_person_id in
         (SELECT person_id FROM person WHERE username='mingzhao')
     )
GROUP BY storage_name
```

 Explain:

 `GROUP BY storage_name` : is added so that duplicates are only shown once

### Update responsible person for chemicals in many location (storage)

```sql
UPDATE chemical_storage 
SET owner_person_id=null 
WHERE owner_person_id=13 AND storage_id IN (19,7,5,4,12,8)
```

Explain: the command above change responsible people to no one for all of the chemicals being owned by person\_id=13 AND in all of the storage\_id listed

###

### Search for barcodes that are duplicated

The below command will search for all the barcode that is assigned to more than 1 container (chemical\_storage). This search **ignores disposed container as well as container without barcodes** (`chemical_storage_disabled IS NULL AND chemical_storage_barcode != ""`)

```sql
SELECT chemical_storage_barcode
FROM chemical_storage
WHERE chemical_storage_disabled IS NULL AND chemical_storage_barcode != ""
GROUP BY chemical_storage_barcode
HAVING Count(*) > 1;
```

###

### Update/Get extra info for containers with duplicated barcode (continued from “Search for barcodes that are duplicated”)

To select info from the list of duplicated barcodes. In this case, created by a specific user and created after some specific timestamp

```sql
SELECT molecule_id, 
        chemical_storage_id, 
        chemical_storage_created_when, 
        chemical_storage_created_by,
        chemical_storage_barcode
FROM chemical_storage
WHERE chemical_storage_barcode IN 
    (SELECT chemical_storage_barcode
     FROM chemical_storage
     WHERE chemical_storage_disabled IS NULL AND chemical_storage_barcode != ""
     GROUP BY chemical_storage_barcode
     HAVING Count(*) > 1
    ) AND
    chemical_storage_created_by = "user1" AND 
    chemical_storage_created_when > '2019-08-16 18:00:00'
ORDER BY chemical_storage_barcode;
```

## REMOVE ALL CONTAINERS (BOTTLES) OF CHEMICALS

{% hint style="danger" %}
**WARNING!!! BE VERY CAREFUL BEFORE DOING THIS!**
{% endhint %}

This will **remove all of the containers**, including info about manufactures, order date, barcode, owner, location, etc. However, info about molecule (MW, structures, safety, etc) and database related such as users will be reserved.

This is particularly helpful when you need to update a large amount of chemicals via text import.

1. Login into your server via ssh
2. Login into myswl using root account or user account with administrator priviledge for the database that you want to change. Replace “root” with the username if desired

```bash
mysql –u root -p
```

    3\. Show database and then choose the database of interest. `test` is the name of the database in this example

```sql
show databases;
use test;
```

![](https://1203731670-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2Fuser-guides%2F-Lv6iQsxlcmWQpVpKscm%2F-Lv6kkl5du3ZbtCK2R5K%2F11.png?generation=1575309486706196\&alt=media)

    4\. Remove the chemical container by running the following command

```sql
truncate table chemical_storage;
truncate table cache;
truncate table change_notify;
```

![](https://1203731670-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2Fuser-guides%2F-Lv6iQsxlcmWQpVpKscm%2F-Lv6kkl6ijQqGvyn5lnk%2F12.png?generation=1575309486653268\&alt=media)

    5\. (Optional): Run the command below if you want to reset the id counter (`chemical_storage_id` as well as autogenerated barcode)

```sql
alter table chemical_storage auto_increment=1
```

    6\. Log out of mysql, restart httpd and mariadb services

```sql
exit
```

```bash
systemctl restart httpd mariadb
```

    7\. Go back to OE website and import new tab-separated text file

{% hint style="info" %}
**Update (Oct 11, 2019):** new function to delete multiple containers based on barcode has been added into Settings section. **Only users with admin permission would see this option**. 
{% endhint %}

## Find duplicated custom barcodes using SQL

This should be run on the database of interest, table `chemical_storage`

```sql
SELECT chemical_storage_id, chemical_storage_barcode, count(chemical_storage_barcode) as NumOccurrences
from `chemical_storage`
WHERE chemical_storage_disabled is NULL
GROUP BY chemical_storage_barcode
HAVING (COUNT(chemical_storage_barcode) > 1)
```

* `chemical_storage_disabled is NULL` : to ignored disposed containers

##