Useful SQL commands
Below are some additional SQL commands that can give you some info that are not possible from inside Open Enventory
There are a lot more SQL commands in the following section:
CHANGING INFO OF MULTIPLE CHEMICALS
Install phpMyAdmin (See OE Settings)
To return all chemicals borrow by somebody (borrowed_by_person_id)
Look up
person_id
by going into [Database]/personShow chemical borrow by that person (i.e. a person with person_id=8)
3. Return by:
To change the owner info of a chemical (owner_person_id)
Look up
person_id
by going into [Database]/personShow chemical owned by that person (i.e. a person with person_id=7)
3. Change, in this case, to no owner (NULL)
Find person_id using Console command line in PHPMyAdmin
In the following example, show person_id
of user with username=julia
Find storage ID using Console Command line in PHPMyAdmin
First choose the desired database on the tree folder from the left
Use:
Explain:
LIKE 'FH-2206%'
: show thestorage_id
of the storage location with name starting with FH-2206LIKE '%FH-2206%'
would mean find anything with storage name containing FH-2206LIKE 'FH-2206%'
would mean find anything with storage name ending with FH-2206
Show chemicals with multiple criteria
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
Explain: see above for explanation of the criteria.
Show storage_name and storage_id using username
Explain:
GROUP BY storage_name
: is added so that duplicates are only shown once
Update responsible person for chemicals in many location (storage)
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 != ""
)
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
REMOVE ALL CONTAINERS (BOTTLES) OF CHEMICALS
WARNING!!! BE VERY CAREFUL BEFORE DOING THIS!
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.
Login into your server via ssh
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
3. Show database and then choose the database of interest. test
is the name of the database in this example
4. Remove the chemical container by running the following command
5. (Optional): Run the command below if you want to reset the id counter (chemical_storage_id
as well as autogenerated barcode)
6. Log out of mysql, restart httpd and mariadb services
7. Go back to OE website and import new tab-separated text file
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.
Find duplicated custom barcodes using SQL
This should be run on the database of interest, table chemical_storage
chemical_storage_disabled is NULL
: to ignored disposed containers
Last updated