search

ODM2 / WOFpy

A server-side implementation of CUAHSI's Water One Flow service stack in Python.
http://odm2.github.io/WOFpy/
9 stars 9 forks source link

First step to spin up a database for testing #193

Closed ocefpaf closed 7 years ago

ocefpaf commented 7 years ago

@lsetiawan this is not ready yet and I'll need your help to continue.

For now this PR only adds the mysql and postgresql services.

Below you'll find the script we have in odm2api. Not sure how to edit that to provide a similar functionality for WOFpy. So here are my questions:

I am available tomorrow afternoon if you want to (g)talk these out.

ls -al /etc/mysql
sudo service mysql stop
# lower-case-table-names = 1 leaving lower_case_table_names=1 since that is what the docs say
 echo "[mysqld]" > $HOME/.my.cnf
 echo "lower-case-table-names = 1" >> $HOME/.my.cnf
# echo "lower_case_table_names=1" >> $HOME/.my.cnf
cat $HOME/.my.cnf # be sure it registered
service --status-all
#  this should have worked.
sudo sed -i '/\[mysqld\]/a lower_case_table_names = 1 ' /etc/mysql/my.cnf
cat /etc/mysql/my.cnf
sudo service mysql start
mysql --verbose -e  "show variables like 'lower%';" --user=root

#strace mysql 2>&1 | grep cnf # will tell you what files are being used
mysql --verbose -e "CREATE USER 'ODM'@'localhost' IDENTIFIED BY 'odm';GRANT ALL PRIVILEGES ON *.* TO 'ODM'@'localhost';" --user=root
mysql --verbose -e "CREATE USER 'ODM'@'127.0.0.1' IDENTIFIED BY 'odm';GRANT ALL PRIVILEGES ON *.* TO 'ODM'@'127.0.0.1';" --user=root
mysql --verbose -e "CREATE USER 'ODM'@'%' IDENTIFIED BY 'odm';GRANT ALL PRIVILEGES ON *.* TO 'ODM'@'%';" --user=root
mysql  --verbose  -e "create database IF NOT EXISTS odm2;" --user=root
mysql -e "create database IF NOT EXISTS odm2test;" --user=root
#####
# install
#####
ls -al  ./tests/usecasesql/littlebearriver/sampledatabases/odm2_mysql/LBR_MySQL_SmallExample.sql ./tests/usecasesql/marchantariats/marchantariats.sql
mysql --user=ODM --password=odm  odm2 < ./tests/usecasesql/littlebearriver/sampledatabases/odm2_mysql/LBR_MySQL_SmallExample.sql
mysql --user=root  -e  "show databases;"
mysql --user=root -e   "GRANT ALL PRIVILEGES ON odm2.* TO 'ODM'@'localhost';FLUSH PRIVILEGES;"
# these should produce results, if they don't the lower_case_table_names failed
# should make them grep or sed for some keywords
mysql --user=ODM --password=odm odm2 -e "use odm2; Select * from Variables;"
mysql --user=ODM --password=odm odm2 -e "Select * from odm2.Variables;"
mysql --user=ODM --password=odm  -e "Select * from odm2.Variables;"
emiliom commented 7 years ago

Thanks @ocefpaf. @lsetiawan will get back to you tomorrow.

Being able to deploy CI tests involving relational databases will be very helpful not just for WOFpy, but for other ODM2 software as well, including odm2api and an ODM2 REST API we're developing.

lsetiawan commented 7 years ago

Do we need both mysql and postgresql or can we start with one and expand to more testing later?

I think we can start with one and expand later.

I recall that we had to enable sudo in odm2api, can we avoid that here (see sudo service mysql stop that stops the service and the scripts restarts it later.)?

If we are not using mysql, we can definitely avoid this, but for mysql, there is no way to avoid it. 😞

I believe we'll need some data for the service, do we already have that in the repo (I could not find any .sql file)?

I'll find you a nice .sql example. The envirodiy is a good testing case, and it does have the unicode error.

ocefpaf commented 7 years ago

The envirodiy is a good testing case, and it does have the unicode error.

Awesome!

If we are not using mysql, we can definitely avoid this, but for mysql, there is no way to avoid it.

You mean we can test with postgresql and avoid that? If so let's try that. If not we can live with sudo enable. It is slower but not too bad.

I think we can start with one and expand later.

Let me know the answer of the question above and I'll adapt this PR accordingly.

lsetiawan commented 7 years ago

@ocefpaf I have sent an email to the group checking to see if it's appropriate to use the envirodiy database as a test case. Stay tuned! Thanks 😄

ocefpaf commented 7 years ago

@ocefpaf I have sent an email to the group checking to see if it's appropriate to use the envirodiy database as a test case.

Saw that. Thanks!

BTW, how hard it is to create one with fake data? I do prefer a real case scenario, just wondering if we can do something here that is self-contained.

lsetiawan commented 7 years ago

BTW, how hard it is to create one with fake data?

I think it would be too complex to meet all the function testing. There's already some fake data here. But I think we should make the current test match the actual application like I said on https://github.com/ODM2/WOFpy/pull/188#issuecomment-326685770 to start with.

ocefpaf commented 7 years ago

But I think we should make the current test match the actual application

I agree. Just wondering in case we face some roadblocks.

Also, with a real case scenario, we can make start creating some integration tests that can catch unforeseen errors in the unittests.

lsetiawan commented 7 years ago

@ocefpaf Should I merge this and continue development?

ocefpaf commented 7 years ago

Did you see my https://github.com/ODM2/WOFpy/pull/193#issuecomment-331479994 ?

If you want to go ahead with mysql you can merge. If not let me know :smile:

lsetiawan commented 7 years ago

mysql requires sudo. I think we should start with postgresql.

ocefpaf commented 7 years ago

OK, changed that yo be only postgresql. Did you get the green to use the database?

lsetiawan commented 7 years ago

Did you get the green to use the database?

Yepp. I did. I have shared with you via email the sql file. Thanks.

lsetiawan commented 7 years ago

Once travis is green. I will merge your changes.

ocefpaf commented 7 years ago

Yepp. I did. I have shared with you via email the sql file. Thanks.

Awesome!