Work package #3(DEVELOPMENT)

Creation and Implementation of SQL based system for EFSC data collectrion

Activities and Results

Darko Anđelković, PhD Chemistry

University of NišFaculty of Agriculture in Kruševac

Faculty of Science and Mathematics in Nišdarko.andjel@outlook.com

NETCHEMFinal MeetingBelgrade Feb 27, 2020

SQL

“Structured Query Language”specialized programming languageused in programming anddesigned for managing data inRelationaldatabasemanagementsystems(RDBMS)

The most common database engine today:• Microsoft ACCESS• Microsoft SQL 2016• MySQL (open source)• in Cloud solutions: Microsoft Azure...

ɛs kjuːˈɛl/or

ˈsiːkwəl/

2017 – 2020

Outcome 3.1

START VERSION OF SQL DATABASE

❑ Identification of stakeholders’ needs(in- & out-consortium)

❑ Estimation of security & publicity problems,business and professional rules in SMEs & HEIs

❑ First concept of Database design (“alpha” – version)❑ Defining the DB objects,

attributes describing the objects,type and amount of associated data, relationships

❑ Possibility of implementing of cloud-based application ❑ Computer hardware issuesTask leader: UoG (England)Start: Oct 2017 End: Aug 2018Meetings: – UPMC (France)

– University of Niš (Serbia)

Outcome type:• Teaching material• Learning material• Training material• Event• Report✓ Service/product

Target groups:✓ Teaching staff (c.15)• Students• Trainees• Admin. staff✓ Technical staff (c.15)o Librarianso Other

Dissemination level:• Depart./Faculty✓ Institution• Local• Regional• National• International

Outcome 3.2

OPTIMIZED SQL BASED SYSTEM

❑ Development of database applications,enabling users (i.e. developers and invited testers) to import and export data etc.

❑ Modifications on tables, data fields, relationships and testing on “alpha” - version

❑ GUI environment, visual design, menus and controls, referencing of data entities

❑ “beta” – version of DB SQL

Task leader: BUT (Czech Republic)Start: March 2018End: September 2018Meeting: hosted by BUT (Czech Republic)

Outcome type:• Teaching material• Learning material• Training material• Event• Report✓ Service/product

Target groups:✓ Teaching staff (c.15+10)✓ Students (c.20)• Trainees• Admin. staff✓ Technical staff (c.20)o Librarianso Other

Dissemination level:• Depart./Faculty✓ Institution• Local• Regional• National• International

Outcome 3.3

LOADING DATA TO SQL BASED SYSTEM

RECORDS

❑ loading various data types❑ hardware and software upgrading and modification❑ testing DB performance❑ improving skills and training on DB implementation❑ providing instructions and help files

Task leader: CEA (France), with UN (Serbia) etc.Start: February 2018End: August 2019

Outcome type:• Teaching material✓ Learning material• Training material• Event• Report• Service/product

Target groups:✓ Teaching staff (c.100+200)✓ Students (c.500)✓ Trainees (c.100)• Admin. staff✓ Technical staff (c.20)o Librarianso Other

Dissemination level:✓ Depart./Faculty✓ Institution✓ Local✓ Regional• National✓ International

Outcome 3.4

SQL DATABASE INTERCONNECTED WITH OTHER

NETCHEM OER COMPONENTS

❑ SQL database objects and applications shall migration on public-open NETCHEM portal, with integration with the rest of components provided by NETCHEM portal

❑ final definitions of access hierarchy❑ backup/recovery issues❑ user authorisation and security mechanisms for in- and

out-Consortium beneficiaries❑ more massive loading and data flow

Task leader: UoG (England)Start: October 2018End: March 2019

Outcome type:• Teaching material• Learning material• Training material• Event• Report✓ Service/product

Target groups:✓ Teaching staff (c.100+200)✓ Students (c.500)✓ Trainees (c.100)• Admin. staff✓ Technical staff (c.20)o Librarianso Other

Dissemination level:✓ Depart./Faculty✓ Institution✓ Local✓ Regional• National✓ International

✓ Concept of Items✓ Concept of Keywords✓ Concept of Users✓ Concept of Classification/Grouping✓ Concept of File Upload etc…

Software utilized for this pilot-development:Microsoft Office 2016 MS Accessnon-WEB designscreen res. 1920×1080 (for forms)

First conteptual design of some DB components

Main Item form

Default name (mandatory)

Comments (description) Picture - formula or photo (optional)

Item classification

Default name (mandatory)

Keywords (synonyms of default Item’s name)

EXAMPLE: table of Keywords (synonyms) for Item ID no. 0001, named Murexide

A) Original word/term B) Addapted for sorting

Murexide monohydrate Murexidemonohydrate

purpurate, ammonium, monohydrate purpurateammoniummonohydrate

5-[(Hexahydro-2,4,6-trioxo-5-pyrimidinyl)imino]-2,4,6(1H,3H,5H)-pyrimidinetrione, monoammoniumsalt, monohydrate

Hexahydrotrioxopyrimidinyliminopyrimidinetrionemonoammoniumsaltmonohydrate524652461H3H5H

C.I. 56085 CI56085

CONVENTIONSome rules for Addapted Alphanumerical Sorting have been proposed, examples:

acid, 7-α-D-Glucopyranosyl-9,10-dihydro-3,5,6,8-tetrahydroxy-1-methyl-9,10-dioxo-2-antracenecarboxylicacidglucopyranosyldihydrotetrahydroxymethyldioxoantracenecarboxylic7αD910356819102

chloride, phosphorus(III)chloridephosphorus3

(2R,3R)-(+)-Vinska kiselinaVinskakiselina2R3R

not strictly necessery to keep "+" sign

(+)-Tartaric acidTartaricacid+

do not remove "+" sign, since there is no other discrimination between (+) and (–) forms

TITRIPLEX® IIITITRIPLEX3

Sulfuric acid, tech. 38%Sulfuricacidtech38

Erlenmayer, narrow neck, 50 mLErlenmayer, narrow neck, 0050 mL

AcetanhidridAcetanhidrid

Aluminum-nitrate nonahydrateAluminumnitratenonahydrate

Aluminum-oxide 90Aluminumoxide90

Amberlite resin IR-45 (OH)AmberliteresinIR45OH

4,4'-(3H-2,1-Benzoxathiol-3-ylidene)bis[2-methylphenol] S,S-dioxideBenzoxathiolylidenebismethylphenolSSdioxide443H2132

α,α-Bis(3,5-dibrom-4-hidroksi-o-tolil)-α-hidroksitoluensulfonska kiselina, γ-sultonBisdibromhidroksitolilhidroksitoluensulfonskakiselinasultonαα354oαγ, OR:Bisdibromhidroksitolilhidroksitoluensulfonskakiselinasultonαα354oalphagamma

N-[4-[Bis[4-(dimethylamino)phenyl]methylene]-2,5-cyclohexadien-1-ylidene]-N-methylmethanaminium chlorideBisdimethylaminophenylmethylenecyclohexadienylidenemethylmethanaminiumchlorideN44251N

SQL DATABASE SOFTWAREMariaDB 10.3.0 Alpha/5.5.55 StableConnector/J 2.0.0 RC

❑ Free and open source software❑ Made by the original developers

of MySQL❑ One of the most popular

database servers in the world❑ >25 years on the market❑ Guaranteed to stay open source❑ High compatibility with MySQL

("drop-in" replacement capability)

❑ Notable users include:Wikipedia

WordPress.comGoogle…

❑ Application realized in PHP 7.2❑ Zend Framework 3.0 (UI – MVC)❑ Elephant admin template

(app design)

NETCHEM DB Access hierarchy

(user authorization)

1st level of access privileges – Administrators

Darko Andjelkovic, Ivan Stankovic have highest level of permissions

2nd level: Moderators

during Project are: Darko Andjelkovic, Ivan Stanković, Tatjana Andjelkovic, Ivana

Kostić, Milica Branković, Danica Milojković, Mariola Kodra, Majlinda Vasjari, Milan

Antonijević, Ivana Ivančev Tumbas, Josef Časlavsky, Miloš Ivanović etc.

In post-project period this will be defined as a

part of sustainability plan.

3rd level: Users

any person who wants to attend NETCHEM database with his/her own User name

and Password, will be granted with Read & Edit facilities.

4th level: Guests (any person)

who wants to have free attend to NETCHEM DB in Read Only mode (no edit and

without contribution to the DB content). Does not need authentication.

Rules of User registration:

SIGN-UP

➢ Registering personal account for NETCHEM DB is free to everyone.It comprises usual and simple procedure –providing private or official e-mail address, and afterward verification of user defined password through link in received e-mail message.

➢ If necessary, one person may be registered with several e-mails, thus having several independent accounts in NETCHEM DB

➢ Account creation is a matter of personal decision.

➢ No official permission from Company, University or other Entity is requested to open and manage his/her own account.

➢ User name (e-mail address) and user defined password is enough to

attend NETCHEM DB

➢ User can change password as many times as he wants

➢ In case of forgotten password, recovery is provided via e-mail Inbox

➢ Structured mechanism for content & quality control is not and will not be provided

It is on personal conscience to provide reliable data, proper item’s naming and adequate

quality material in DB content that is public open

➢ Intellectual properties rights (IPR) do not apply on NETCHEM DB.

Every public open content, or content seen by granted users, is by default understood to

be fully free for copying, modification, usage for any kind of private, public, commercial or

non-commercial purpose, printed and electronic publication or further distribution

without obtaining author’s (User who created it) permission

➢ No claims on violation of intellectual properties will be accepted, nor administrators and

moderators are obliged to justify intellectual properties and ownership of user’s uploads

➢ Users are required to take care on his own responsibility about IPRs of other authors

➢ Users must avoid leaving personal data of other people, such as their phone numbers,

address, family status, e-mails, pictures etc.

➢ Ownership on some DB records (Items, Keywords,

Files etc.) is noted aside every single data record

➢ No legal mechanisms or consequences:

data contents in NETCHEM DB is a public open and is

not considered as a private property in any way

➢ Chemical compound (4,5-dihydroxynaphthalene)

➢ Active pesticide component (atrazin, aldicarb, cyprodinil)

➢ Active pharmaceutical components (acetylsalycilic acid, ibuprofen)

➢ Ions, radicals, fragments (Cd2+, CH3●)

➢ Laboratory instrument (Agilent GC/MS 6890, Thermo LCQ Deca)

➢ Analytical methods (BOD5 , dissolved O2 acc. to Winkler)

➢ Commercial products, e.g.Chromatographic columns (Zorbax™ HPLC 25µm)Pesticides for agriculture (Monosan Herbi® Galenika)Drugs (Aspirin® BAYER GmbH)

➢ Software for analytical processing (XCalibur™ 2.2.11)

➢ Chemical or physical phenomenon (electronegativity, ozone layer)

➢ Food – general group (vegetables, bananas, oranges, meat, popcorn)

➢ Food - specific products (Milka™ chocolate, Zlatiborac® pork sausage)

➢ Various materials (iron, asbestos, air, limestone, fabric, cosmetics)

➢ Geographical or topological term (Schwarzwald mountain, Danube river)

➢ Botanical names, plants, animals (Thilia argentea, Escherichia coli)

➢ Lectures, CPD courses (Chemistry of atmosphere, Soil remediation)

➢ Persons, Institutions, Labs (Jean-Claude, Aqualeer, MS lab 2)f

ITEMS types overview

Search by Item’s ID…

Choose language…

…or by part(s) of name

(including synonyms)

Results

Item

search

Item EDIT formImage, Keywords/syn.

Items

➢ More then 1200 Item records ➢ approx. 4000 synonyms have been loaded➢ about a half of them being of general significance➢ other ones are of user-specific interest

Item Grouping

Item Grouping

Item GroupingNow we have:❑ > 500 different groups❑ > 2000 Item grouping definitions

Item-to-Item connections

Item-to-Item connections

Item-to-Item connections

Uploaded files

Uploaded files

❶

Uploaded files

❷

Uploaded files

❸

❹

Item: User’s comments

1. Every day (automysqlbackup script):backups saved- for every day in last week- for every week in last month- for every month in the current year, and- for every year

2. Every day (webmin script):“Incremental” backup saves all data on VPS (Virtual Private Server) – account and virtual domain configuration, WEB site content, SQL data, e-mail accounts etc.“Full” backup on 1st and 16th in a month

3. Weekly:backup of all configuration files and log files, stored on VPS

QUESTION:Do we want to re-write Encyclopedia Britannica?

ANSWER: NO

NETCHEM platform DB should be loaded with data we want or we need to put inside!

NETCHEM DB vs. WIKIPEDIA… !?

Wikipedia Netchem DB

Non-profit Non-profit

Funding: ??? EU ERASMUS+ Grant

Free-access Even more of “Free-access”

Created and edited by volunteers Created and edited (mostly) by volunteers

Upload and modification need a procedure From the first SIGN-UP to the first upload5 min is more then enough

Excellently structured data and polished UI ▪ Not so refined visual interface▪ No in-text ineractive hyperlinks ▪ Lack of publication-like presentation

Editing must follow pre-defined rules Editing & Upload rules are more relaxed

Classification of data may be well done, but somewhat complicate

Grouping, classification and Item-to-Item connections are fast and very flexible

File sharing among users not supported Public/Private and Group File sharing is very easy

File upload & download is not in focus File upload & download is one of main advantages

Ferrari 458 Italia (2018) Trabant 601S (DDR, 1977)

Price 500.000 € 3.000 €

Weight 2.500 kg 700 kg

Fuel, lit./100 km 25 lit. 6 lit.

Need 4 weeks of special training?

YES(in Modena Italy, 5000 €)

NO

Service available in your country/town?

NO (only Paris, London…) YES (on every corner)

Driving on non-perfect motorways?

Not possible or not recommended

OK

KASKO Incurance 1000 €/month Nobody wants to steal Trabant…

What car is a better choice?