Show page Old revisions Backlinks Fold/unfold all This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. ====== Introduction to database management with open source tools ====== Workshop offered by Guillaume Larocque (guillaume.larocque@mcgill.ca), research professional at the [[http://qcbs.ca|Quebec Centre for Biodiversity Science]]. [[http://prezi.com/z1ib62c5ybmx/|Link to Prezi presentation]] {{ ::prezi_opendb_qcbs.pdf |PDF version of the Prezi presentation}} ===== Useful resources ===== * [[https://www.postgresql.org/docs/current/static/reference.html|PostgreSQL Reference Manual]] * [[http://link.springer.com/book/10.1007%2F978-1-4302-0018-5|Beginning databases with PostgreSQL]] * [[https://wiki.postgresql.org/wiki/Main_Page|PostgreSQL wiki]] * [[http://www.pgsql.ru/db/pgsearch/|PGSearch to search PostgreSQL related websites and lists.]] ====== Installation of PostgreSQL ====== ===== On Windows and Mac ===== * Visit the [[http://www.enterprisedb.com/products-services-training/pgdownload#windows|EntrepriseDB site]] and download the version appropriate for your platform. Opt to download the 64bit version. * Execute the file and go through the installation steps, accepting default options. * When prompted, choose an appropriate password for the postgres user and write this password down. ===== On Linux/Ubuntu ===== In a terminal, type: <file> sudo apt-get install postgresql-9.5 libreoffice libreoffice-sdbc-postgresql </file> It is possible that the version of postgresql available in your repositories is earlier than 9.5. You can install the version you have available. For other distributions, [[http://www.postgresql.org/download/|check this page]]. ++++ Installing MySQL | * Visit the [[http://www.mysql.com/downloads/mysql/|Download section of the MySQL website]] and download the version of MySQL Community Server appropriate for your platform (Windows users: choose the 32 bit version!). Note that you DO NOT have to register. Simply click on "No thanks, just take me to the downloads!" at the bottom. * Follow the installation instructions. Use the Typical Setup Type. * If a Window appears to register for a MySQL Entreprise support, **close the window**. * Choose to proceed to create and configure a MySQL instance. Select the standard configuration type. * On Windows, choose to install MySQL as a Windows service. Also choose a password for the root user (administrator). **WRITE DOWN THIS PASSWORD!!** * Download [[http://www.libreoffice.org/download/|LibreOffice 3.6]] for your platform and follow the installation instructions. Choose the Typical installation option. * On Windows: Install the [[http://dev.mysql.com/downloads/connector/odbc/|32 bit version of the MySQL ODBC connector]]. ++++ ====== Installing LibreOffice ====== * [[https://www.libreoffice.org/download/libreoffice-fresh/|Click here, download and install LibreOffice 5 (Fresh)]]. ===== Accessing PSQL ===== **On Windows and Mac**: * Find PostgreSQL 10.X in the Start menu and click on "SQL shell (psql)" * In the shell, accept the default values by pressing enter for the username and server name and type the proper Postgres password (chosen above). Note that you will not see anything while typing the password. This is normal. ++++ If that doesn't work... | Open the terminal; * Configure so you can log in without a password: Stop the postgres server (launch daemon): <file> sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-9.5.plist </file> - Edit data/pg_hba.conf and change 'md5' to 'trust' in the 'local' and 'host' lines - To do this on a mac, open a terminal and type 'nano /Library/PostgreSQL/10/data/pg_hba.conf'. Make the necessary changes, hit CTRL-O and then CTRL-X. - Start the daemon <file> sudo launchctl load /Library/LaunchDaemons/com.edb.launchd.postgresql-10.plist </file> ++++ **On Linux** Simply type: <file> psql </file> in a terminal. ++++ If that doesn't work... | If you cannot connect to psql, it is likely because PostgreSQL is using your linux username to log you in instead of the 'postgres' user. What you need to do is create a new postgres user that has the same name as your linux user name. <file> createuser -P -s -e your_linux_username </file> You can then connect to Postgres using this command <file> psql </file> If you want to create a new user when logged in, you can do so by executing the CREATE USER commands below. Then, in psql you type: <file> SHOW hba_file; </file> then find that file and edit it. Replace this line <file> local all postgres peer </file> and replace it with <file> local all postgres md5 </file> Then, you should be able to connect to Postgres with <file> psql worshop -U your_username -W </file> ++++ \\ \\ ++++ If that doesn't work on Mac... | Open the terminal; * Configure Postgres so that you can enter without a password: Stop the postgres server(launch daemon): <file> sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-9.4.plist </file> - Edit data/pg_hba.conf and change 'md5' to 'trust' in lines 'local' and 'host'. - Restart the server: <file> sudo launchctl load /Library/LaunchDaemons/com.edb.launchd.postgresql-9.4.plist </file> ++++ ====== Group discussion ====== [[http://qcbs.ca/wp-content/uploads/2012/10/Crustacean_plankton_Canada.ods|Click here to download the original spreadsheet]] \\ \\ \\ ===== Creating a new user and database ===== If desired, you can create a superuser other than the default postgres user. To do so, type the following command, replacing your_username and your_password with values of your choice. <file postgresql> CREATE USER your_username WITH SUPERUSER PASSWORD 'your_password'; </file> Creating a new database for the workshop <file> CREATE DATABASE workshop; </file> Important step to secure access to database. <file postgresql> REVOKE CONNECT ON DATABASE your_database FROM PUBLIC; </file> <file postgresql> CREATE USER user_name WITH PASSWORD 'your_password'; GRANT ALL ON DATABASE your_database TO user_name; </file> ===== Connecting PSQL and Libre Office Base ===== * Open Libre Office Base and "New...database". * Connect to an existing database>PostgreSQL * For Datasource URL, type the following: <file postgresql> dbname=workshop host=localhost port=5432 </file> * Click Next and put the appropriate user name, and click on 'password required'. You can then click on 'Test connection' to make sure the connection works properly. * You can then same the database connection information as a file on your computer for easily connecting to it later. ===== Basic commands ===== List all databases <file postgresql> \l </file> Connect to a database <file postgresql> \c workshop </file> List and describe all tables in the current database <file postgresql> \dt </file> ... with more details <file postgresql> \dt+ </file> View a list of all tables in database <file postgresql> \d+ </file> Describe a particular table <file postgresql> \d tablename </file> List all users <file postgresql> \du </file> Quit Postgresql <file postgresql> \q </file> ===== To execute SQL commands in LibreOffice Base ===== One you have connected to your database using the instructions above, you can click on Tool...SQL in LibreOffice Base and type your command there. If you create or delete a new table, you might have to click on **View...Refresh Tables** to see the changes. ===== Exercise 1 - CREATE AND INSERT statements ===== More common data types ^function call^meaning^example^ |integer|-2147483648 to +2147483647|5| |decimal(,)|decimal(total digits, digits after period)|122.52 decimal(5,2)| |real|6 decimal digits precision|122.527964| |double|15 decimal digits precision|122.527964| |varchar()|character(maximum length)| 'tree swallow'| |text|text of unspecified length| 'Once upon a time, there was...'| |timestamp|date and time| '2012-10-21 10:03:21'| [[http://www.postgresql.org/docs/9.3/static/datatype.html|Full list of PostgreSQL data types.]] <file postgresql> CREATE TABLE bird_sightings (sight_id SERIAL PRIMARY KEY, obs_time timestamp,species text,sp_count integer,lat real,"long" real); </file> We use "" when we want to store a column or table name that is a reserved word or that contains spaces or special characters. ++++ MySQL | <file > CREATE TABLE bird_sightings (sight_id int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, obs_time datetime,species text,Sp_count int(10),Lat decimal(8,5),`Long` decimal(8,5)); </file> ++++ To add a new row with values to the table <file postgresql> INSERT INTO bird_sightings (obs_time,species,sp_count,lat,"long") VALUES ('2012-06-19 12:31:16','Sturnus vulgaris',40,45.3522,-73.7930); </file> Note that we did not specify the sight_id field since it is set to SERIAL (auto-increment) and it will be assigned a value automatically. To see the table: <file postgresql> SELECT * FROM bird_sightings; </file> If you need to delete the table <file postgresql> DROP TABLE bird_sightings; </file> **Question 1** :?: Reproduce the following table in PostgreSQL, name it 'actors' and visualize it in PostgreSQL ^first_name^last_name^birth_date^gender^height^trade_mark^ |Bruce|Willis|1955-03-19|M|1.81|Frequently plays a man who suffered a tragedy, had lost something or had a crisis of confidence or conscience.| |Emma|Watson|1990-04-15|F|1.65|Often portrays literary characters| ===== Exercise 2 - Importing data into PostgreSQL===== Download the following files to your computers (right-click, save link as...): {{ :lakes.csv |Lakes.csv}} {{ :lakes_species.csv | lakes_species.csv}} {{ :species_acro.csv |species_acro.csv}} On Windows: Copy those three files to a folder named 'opendbwork' in the C: folder (i.e. C:\opendbwork) On Mac or Linux: Copy those three files to a folder in your path that you can easily identify (e.g. /home/myname/opendbwork). Modify the commands below accordingly by replacing <html>"C:/opendbwork"</html> with your path. Create the table containing the environmental information of each lake - **Lakes** <file postgresql> CREATE TABLE lakes (lake_id SERIAL PRIMARY KEY,numero INT, lake_name text, province text, latitude DEC(10,5),longitude DEC(10,5), number_of_species INT, ecoprov VARCHAR(50), ecozone VARCHAR(50), gss DEC(10,2),gse DEC(10,2), gsl DEC(10,2), gdd10 DEC(10,2), egdd DEC(10,2), mean_ele DEC(10,2), pe_ann_p DEC(10,2), totp_ann DEC(10,2), srann_me DEC(10,2), shann_me DEC(10,2), tmax_ann DEC(10,2), tmean_an DEC(10,2), vpann_me DEC(10,2)); </file> ++++ MySQL | <file> CREATE TABLE lakes (lake_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,numero INT, lake_name text, province text, latitude DEC(10,5),longitude DEC(10,5), number_of_species INT, ecoprov VARCHAR(50), ecozone VARCHAR(50), gss DEC(10,2),gse DEC(10,2), gsl DEC(10,2), gdd10 DEC(10,2), egdd DEC(10,2), mean_ele DEC(10,2), pe_ann_p DEC(10,2), totp_ann DEC(10,2), srann_me DEC(10,2), shann_me DEC(10,2), tmax_ann DEC(10,2), tmean_an DEC(10,2), vpann_me DEC(10,2)); </file> ++++ Load the CSV file into this table <file postgresql> COPY lakes FROM 'C:/opendbwork/Lakes.csv' WITH csv HEADER DELIMITER AS ','; </file> ++++ If this command gives you an access denied error message in Windows | * Right click the folder containing the data file(s) that permission was denied to and then click Properties. * In the Folder's Properties window, select the Security tab. * Click the Edit button. * In the "Permissions for the folder" window that opened, click the Add... button. * Type Everyone into the "Enter the object names to select" text area box. * Click OK and the window will close. * Verify that the default Read & Execute permissions were set to Allow via the check checkbox in the previous window. * Click OK and the window will close. * Click the Apply button in the Folder Properties window. ++++ ++++ If this command gives you an access denied error message on Mac | Copy the files to a folder under /User/Shared/ and link to the files in this folder in the command. ++++ ++++ MySQL | <file> LOAD DATA LOCAL INFILE 'C:/opendbwork/Lakes.csv' INTO TABLE lakes FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES; </file> Note: if the above command gives you 'command not allowed' error. Type 'exit;' and restart MySQL with the following command <code>mysql -u root -p --local-infile=1;</code> ++++ Create the table containing the Species presence at each lakes and load CSV into it - **lakes_species** <file postgresql> CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id INT NOT NULL); COPY lakes_species FROM 'C:/bdlibre/lakes_species.csv' WITH csv HEADER DELIMITER AS ','; </file> ++++ MySQL | <file> CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id varchar(25) NOT NULL); LOAD DATA LOCAL INFILE 'C:/opendbwork/Lakes_Species.csv' INTO TABLE lakes_species FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES; </file> ++++ For the third table, we could use the command line with the following: ++++ Command | <file> CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id INT NOT NULL); COPY lakes_species FROM 'C:/bdlibre/lakes_species.csv' WITH csv HEADER DELIMITER AS ','; </file> ++++ However, we will use LibreOffice instead... ===== Importing a table in LibreOffice Base from Calc ===== * Open the Species_Acro.csv file with LibreOffice Calc (In Windows, find LibreOffice Calc in the start menu). Specify that the file is separated by commas and specify utf8 as the character set (encoding). * Select the entire worksheet with ctrl-a; * Open LibreOffice Base and go to Edit...paste. Call the new table public.species_acro. Select 'Definition and data'. Select to use first line as column names. Do not create a primary key at this point. * Follow the wizard... Move all columns to the right to select them. Then, specify the proper data format (verify the types of attributes in the table) for the other columns. Right click on the column named 'species_id' and select 'Primary Key'. Set the unique_id as int [int4], full_name as text [varchar] with 200 characters and species_id as text [varchar] with 30 characters. ===== REFERENCES ===== ** MATH AND GROUP BY (Aggregate) Functions** |avg() |Return the average value of the argument| |count(DISTINCT) |Return the count of a number of different values| |count() |Return a count of the number of rows returned| |group_concat() |Return a concatenated string| |max() |Return the maximum value| |min() |Return the minimum value| |stddev() |Return the population standard deviation| |stddev_pop() |Return the population standard deviation| |stddev_samp() |Return the sample standard deviation| |sum() |Return the sum| |var_pop() |Return the population standard variance| |var_samp() |Return the sample variance| |variance() |Return the population standard variance| [[http://www.postgresql.org/docs/9.3/static/functions-aggregate.html|Full list]] \\ \\ **Conditional selections used in the where clause** |= |Equal| |> |Greater than| |< |Less than| |>= |Greater than or equal| |< = |Less than or equal| |<> or !=|Not equal to| |LIKE|String matching| **Order of operators in a SELECT statement**: <code> SELECT columns FROM tables WHERE conditions JOIN GROUP BY columns HAVING condition ORDER BY columns; </code> ===== Exercise 3 - SELECT statement ===== Select all rows from the lakes table. <file postgresql> SELECT * FROM lakes; </file> In the terminal, when you are done browsing a table, hit 'q' to go back to the prompt. Select just the lake names and province. <file postgresql> SELECT lake_name, province FROM lakes; </file> Show all province names in table <file postgresql> SELECT DISTINCT province from lakes; </file> Show all province names in table, use an alias for province and order by descending alphabetical order. <file postgresql> SELECT DISTINCT province as "province name" FROM lakes ORDER BY province DESC; </file> Show the birth year of each actor <file postgresql> SELECT first_name, last_name, EXTRACT(YEAR FROM birth_date) FROM actors; </file> Show initials of each actor <file postgresql> SELECT first_name, last_name, concat(substr(first_name,1,1),' ',substr(last_name,1,1)) as initials FROM actors; </file> Select all lakes from Quebec where the mean annual temperature is below -5. <file postgresql> SELECT lake_name, tmean_an FROM lakes WHERE province='QUEBEC' AND tmean_an<-5; </file> Or simply, to count the number of lakes <file postgresql> SELECT count(*) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5; or equivalently: SELECT count(lake_id) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5; </file> **Question 1** :?: - How many lakes in British Columbia receive more than 3000 mm of precipitation (totp_ann column)? \\ ++answer| 12 ++ \\ **Question 2** :?: - What is the average Elevation (mean_ele column) of all lakes in the Montane Cordillera Ecozone (use the avg() operator)? \\ ++answer| 1364.36 ++ \\ \\ Note: the % operator is a wildcard used to replace the beginning or the end of a string. \\ Select all lake names that contain the word 'Small' <file postgresql> SELECT lake_name FROM lakes WHERE lake_name like '%Small%'; </file> Select all lake names that contain the word 'Small' and that are not located in Ontario. <file postgresql> SELECT lake_name, province FROM lakes WHERE lake_name like '%Small%' AND province!='ONTARIO'; other possibility: SELECT lake_name, province FROM lakes WHERE lake_name like '%Small%' AND province NOT LIKE 'ONTARIO'; </file> **Question 3** :?: - What is the maximum latitude of all lakes in Ecozones with names that start with 'Taiga' \\ ++answer| 68.8 ++ \\ **Question 4** :?: - How many species of Daphnia are there in the species_acro table? \\ ++answer| 17 (21 have the word 'Daphnia' somewhere in their name) ++ \\ ===== Exercise 4 - GROUPING ===== Calculate the average annual temperature of all lakes within each province <file postgresql> SELECT province, AVG(tmean_an) as mean_an_t FROM lakes GROUP BY province; </file> Same table ordered by increasing temperatures <file postgresql> SELECT province, AVG(tmean_an) as Mean_AN_T FROM lakes GROUP BY province ORDER BY AVG(tmean_an); </file> **Question 5** :?: - Which province has the lake with the highest maximum temperature (column tmax_ann)? \\ ++answer| BRITISH COLUMBIA 14.60 ++ \\ Update statement: Give all rows where the province name includes NWT the same province name 'NWT' - **Careful, this actually modifies the table!** <file postgresql> UPDATE lakes SET province='NWT' WHERE province like '%NWT%'; </file> ===== Exercise 5 - Working with multiple tables ===== Count the number of species in each lake. Display lake name and species number. <file postgresql> SELECT lake_name, COUNT(DISTINCT species_id) as num_species FROM lakes, lakes_species WHERE lakes.lake_id=lakes_species.lake_id GROUP BY lakes.lake_id ORDER BY num_species DESC; </file> Same query, but only display the first 20. <file postgresql> SELECT lake_name, COUNT(DISTINCT species_id) as num_species FROM lakes, lakes_species WHERE lakes.lake_id=lakes_species.lake_id GROUP BY lakes.lake_id ORDER BY num_species DESC LIMIT 20; </file> Count the number of species in each Ecozone and Ecoprovince. Display lake name and species number. <file postgresql> SELECT ecozone, ecoprov, COUNT(species_id) as num_species FROM lakes, lakes_species WHERE lakes.lake_id=lakes_species.lake_id GROUP BY ecozone, ecoprov ORDER BY ecozone, COUNT(species_id); </file> **Question 6** :?: Generate a table that lists the full name of each species of Daphnia and the number of lakes where this species is present? ++++ Answer | <code postgresql>SELECT full_name, count(DISTINCT a.lake_id) as sp_count FROM lakes a, species_acro b, lakes_species c WHERE full_name like 'Daphnia%' AND a.lake_id=c.lake_id AND b.species_id=c.species_id GROUP BY full_name;</code> ^full_name^Count^ | Daphnia pulex Leydig, 1860 | 234 | | Daphnia similis Claus, 1876 | 17 | | Daphnia ambigua Scourfield, 1947 | 59 | | Daphnia catawba Coker, 1926 | 28 | | Daphnia dubia Herick, 1883 | 71 | | Daphnia galeata Sars, 1864 | 17 | | Daphnia longiremis G. O. Sars, 1862 | 426 | | Daphnia longispina hyalina ceresiana | 3 | | Daphnia longispina hyalina microcephela | 27 | | Daphnia magna Straus, 1820 | 18 | | Daphnia mendotae Birge, 1918 | 433 | | Daphnia middendorffiana Fischer, 1851 | 117 | | Daphnia parvula Fordyce, 1901 | 28 | | Daphnia pulicaria Forbes, 1893 | 110 | | Daphnia retrocurva Forbes, 1882 | 324 | | Daphnia rosea G.O. Sars, 1862 | 52 | | Daphnia thorata Forbes, 1893 | 14 | ++++ \\ ===== Exercise 6 - JOIN Operations ===== |JOIN (INNNER JOIN, CROSS JOIN)| Keep all possible combinations of rows from A and B| |LEFT JOIN| Keep all records of table A, join with elements from B that are present in A| |RIGHT JOIN| Keep all records of table B, join with elements from A that are present in B| |OUTER JOIN| Keep all records from table A and B| Create a list of all the species found in the lakes in Quebec, with the full names of the species and the number of occurences <file postgresql> SELECT full_name, count(full_name) FROM species_acro,lakes_species,lakes WHERE species_acro.species_id=lakes_species.species_id AND lakes.lake_id=lakes_species.lake_id AND province='QUEBEC' GROUP BY full_name; </file> Alternatively, and equivalently... <file postgresql> SELECT full_name, count(full_name) FROM lakes LEFT JOIN lakes_species ON (lakes.lake_id=lakes_species.lake_id) LEFT JOIN species_acro ON (species_acro.species_id=lakes_species.species_id) WHERE province='QUEBEC' GROUP BY full_name; </file> For every possible combinations of lakes found in the Lake of the Woods Ecoprovince, return the difference in mean annual temperature between those lakes. <file postgresql> SELECT concat(a.lake_name,'-',b.lake_name) as lakes, a.TMEAN_AN-b.TMEAN_AN as temp_diff FROM lakes a, lakes b WHERE a.ecoprov='Lake of the Woods' AND b.ecoprov='Lake of the Woods'; </file> ===== Exercise 7 - Subqueries ===== Generate a table containing the count of the presence of each species in lakes North and South of the 50th parallel (latitude). <file postgresql> SELECT a.species_id, count_50south, count_50north FROM (SELECT species_id, count(d.species_id) as count_50south FROM lakes c, lakes_species d WHERE c.lake_id=d.lake_id AND latitude<=50 GROUP BY species_id) a, (SELECT species_id, count(f.species_id) as count_50north FROM lakes e, lakes_species f WHERE e.lake_id=f.lake_id AND latitude>50 GROUP BY species_id) b WHERE a.species_id=b.species_id; </file> :?: **Question 7** Using a subquery, find the mean elevation of lakes (MEAN_ELE column) where are species of Daphnia is present \\ ++ Answer | 474.627172 \\ Code: SELECT avg(mean_ele) FROM (SELECT DISTINCT a.lake_id, mean_ele FROM lakes a,species_acro b,lakes_species c WHERE b.full_name like 'Daphnia%' AND a.lake_id=c.lake_id AND c.species_id=b.species_id ORDER BY a.lake_id) d; \\ ++ ===== Exporting a table ===== Create a new table with just lakes in Quebec <file postgresql> CREATE TABLE lakes_qc AS SELECT * FROM lakes WHERE province='QUEBEC'; </file> <file postgresql> COPY lakes_qc TO 'C:/opendbwork/lakes_qc.csv' WITH csv HEADER DELIMITER AS ','; </file> ====== Using the LibreOffice Base front-end ====== ===== Creating a form ===== * Click on Forms>Create Form in Design view. * Click on the icon associated with the first form element you want to add to the form. For example, click on Text Box and then click/drag to delineate the location of the element. * Right-click on the form element and click on Form... and then click on the Data tab. In the Content drop-down menu, choose the main table that you want to fill with this form. You only need to select this once for the whole form. Close this dialog. * Right-click again on the form element and click on Control... Data and choose the column that you want to fill through this form element. * You can keep adding other elements in the same way, repeating the previous step each time. * To test your form, save it, close the Design view and open your form by double-clicking on its name. ===== List Box ===== * For a dropdown/select menu that contains elements from a secondary table, click on the List Box icon and place it on the form. Then, a wizard will appear to ask you which table and column should be used to show options of the menu. Then, you will have to select the foreign and primary keys from the two tables. When an option is selected through this menu, the foreign key of this option will me added to the associated column in the main table. ===== Radio buttons ===== * To have radio buttons that are mutually exclusive, add them to your form and then right-click on each of them and click on Control... Associated each of them with the same Data field, but specify a different Reference value(on) for each. Leave the (off) value blank. * Then, select the radio buttons and right-click and select Group. ===== Exercise ===== You are given a dataset in the following format: ^Plot 1^ ^species_name^dbh^status^comment^ |Acer rubrum|12.4|alive| | |Acer saccharum|25.3|alive| | |Fagus grandifolia|14.1|dead| |Fagus grandifolia|66.0|alive| | |Fraxinus americana|30.1|alive|diseased bark| ^Plot 2^ |Quercus rubra|12.4|alive| | |Fraxinus americana|64.2|alive|big hole in trunk| |Fraxinus americana|53.1|dead|uprooted| |Carpinus caroliana|10.3|alive| | :?: You are told that thousands more trees will have to be entered in this way. How would you go about designing a database and form system to store this information? :?: Create the following table in PostgreSQL. You can then fill the table with its values by clicking on the table name in Base and entering values manually. Table name: tree_species ^species_id^species_name^english_name^ |1|Quercus rubra|Red oak| |2|Acer saccharum|Sugar maple| |3|Acer rubrum|Red maple| |4|Fagus grandifolia|American beech| |5|Fraxinus americana|White ash| |6|Carpinus caroliana|Musclewood| \\ :?: Create this table structure and create a form to fill it in the form design view, with a drop-down menus (list box) to select the species names and radio buttons for the tree health. tree_id is the primary key. For technical reasons, make sure that you specify varchar as the format for the status field. Table name: tree_plots ^plot_id^tree_id^species_id^dbh^health^comment^ |1|1|3|12.4|alive| | |1|2|2|25.3|alive| | |1|3|4|14.1|dead| | |1|4|4|66.0|alive| | |1|5|4|30.1|alive|diseased bark| |2|6|1|40.1|alive| | |2|7|5|64.2|alive|big hole in trunk| |2|8|5|53.1|dead|uprooted| |2|9|6|10.3|alive| | ====== Using Microsoft Access with PostgreSQL ====== * On Windows, download the [[http://www.postgresql.org/ftp/odbc/versions/msi/|PostgreSQLODBC]] driver and install it on your computer. * Create your tables in PostgreSQL. * In Access, click on External data... ODBC database, and then select "Link to the data source by creating a linked table" and then choose New DSN name. Specify a name for the file, and then fill the information to connect to the database. You can then choose the tables you want to link. * Those table will then be linked to PostgreSQL and you can edit then as you would with other Access tables. ========= Basic concepts for the creation of web forms ========= * [[https://prezi.com/xd-ueiomgbj-/basic-concepts-for-the-creation-of-web-forms/|Access the Prezi presentation here]] **Useful link** * [[http://www.codecademy.com/|Code Academy]] Great website to learn HTML, CSS, PHP, Javascript, JQuery or other. * [[http://link.springer.com/book/10.1007/978-1-4302-0136-6|Beginning PHP and PostgreSQL 8. ]] Book that can be downloaded in e-book format. A bit outdated. **Main steps** - Creation of a web interface with html code and CSS. - Creation of a database to accept the form data. - Creation of PHP code to receive the form data and send it to the database. - Possibility to enrich the form with Javascript/JQuery ===== Installation of PHP, PostgreSQL, apache and PHPPGAdmin ===== [[https://bitnami.com/tag/postgresql|Download and install the WAPP server for pour Windows, or MAPP for Mac.]] For linux: <file> sudo apt-get install postgresql-9.5 apache2 php5 phppgadmin php5-pgsql </file> ===== Part 1 - HTML form ===== Main components needed for an HTML file <file html> <!DOCTYPE html> <html> <head> <title>Document title</title> </head> <body> The page content is here! </body> </html> </file> Creation of a form The basic form <file html> <form method="POST" action="phpfile.php"> <input type="text"> </form> </file> [[http://www.w3schools.com/tags/att_input_type.asp|List of html input types]] Text input <file html> <input type="text" name="name_text"> </file> Dropdown menu <file html> <select name="select1"> <option value="1">Option 1</option> <option value="2">Option 2</option> <option value="3">Option 3</option> </select> </file> Check box <file html> <input type="checkbox" name="poutine" value="yes"> I like poutine <input type="checkbox" name="sushis" value="yes"> I like sushi <input type="checkbox" name="poutine_sushis" value="yes"> I like poutine topped with sushi </file> Radio buttons <file html> I am a... <input type="radio" name="gender" value="m" > Man <input type="radio" name="gender" value="w"> Woman </file> Text area <file html> <textarea name="text_zone"> </textarea> </file> ===== Form styling with CSS ===== [[http://www.w3schools.com/cssref/|CSS reference]] You can give an "id" to any html element, but there can only one element per id. <file html> <input type="text" name="fieldname" id="fieldid"> </file> We can then reference it in the header of the css file with the pound symbol (#) <file html> <head> <style> #fieldid { width:300px; height:30px; color:red; background-color:#ffff00; font-family:verdana,arial; font-size:14px; font-weight:bold; padding:0px 5px 0px 5px; #top right bottom left } </style> </head> </file> We can also give a class to any html element. This class can be repeated for multiple elements. <file html> <input type="text" name="name_text1" class="class_text"> <input type="text" name="name_text2" class="class_text"> <input type="text" name="name_text3" class="class_text"> </file> Et on peut y faire référence dans l'entête de style css du fichier html avec le point "." We can then reference it in the style section of the html header with the "." <file html> <head> <style> .class_text { width:300px; height:30px; color:red; background-color:#ffff00; font-family:verdana,arial; font-size:14px; font-weight:bold; padding:0px 5px 0px 5px; #top right bottom left } </style> </head> </file> ====== Part 2 - Creation of the associated database ====== [[https://secure.qcbs.ca/systeme/phppgadmin/|Link to phppgadmin]] ====== Part 3 - PHP language basics ====== All php commands must be contained within <file php> <?php PHP code is here ?> </file> Comments <file php> <?php // Comments in a line /* Comments section */ ?> </file> <file php> <?php // Creation of variables $myvar=5; $myvar='this is text'; // Creation of an array $myarray=array('value1','value2','value3'); echo $myarray[0]; //value1 $myarray=[]; $myarray[0]='value1'; $myarray[1]='value2'; // Creation of a "key-value" array $myarray=array( 'key1'=>'key1', 'key2'=>'key2', 'key3'=>'key3', ); echo $myarray['key1']; //value1 ?> </file> Loops and if/else <file php> <?php if ($f==5){ //Do something }else{ //Do something else } for ($i=0;$i<10;$i++){ echo 'i='.$i; } //i=0 //i=1 //... //i=9 foreach ($myarray as $my){ //Do something here with $my ?> </file> ====== Part 4 - Insertion into PostgreSQL via PHP ====== [[http://php.net/manual/en/book.pgsql.php|PostgreSQL functions in PHP]] This PHP file is the one which is specified in the "ACTION" part of the <form> field <file php> <?php // Connexion and selection of the database $dbconn = pg_connect("host=localhost dbname=database user=username password=my_password"); // The $_POST contains all the data from the form. Each 'key' in this array corresponds to the name given to each html element. $element1=$_POST['name_element1']; $element2=$_POST['name_element2']; $element1=pg_escape_string($element1); $element2=pg_escape_string($element2); // Inserted into the databse $query = "INSERT INTO ma_table (column1,column2) VALUES ($element1,$element2)"; $result = pg_query($query); // Close the connexion pg_close($dbconn); echo "Form successfully entered into the database|"; ?> </file> Accéder à différents types d'éléments <file php> //Check boxes foreach ($_POST['checkbox'] as $check){ //Faire quelque chose ici avec $check } //Convertir les valeurs en liste séparée par des virgules $check=implode(',',$_POST['checkbox']); </file> Retrouver des éléments dans la base de données <file php> // Connexion et sélection de la base de données $dbconn = pg_connect("host=localhost dbname=base_donnees user=nom_usager password=mot_de_passe"); $query="SELECT * FROM nom_table"; // Insertion dans la base de données $result = pg_query($query); while ($t=pg_fetch_array($result)){ echo $t['prenom'].' '.$t['nom_famille']; } // Fermer la connexion pg_close($dbconn); </file> ====== Linking R with PostgreSQL ====== ===== Using the RPostgreSQL package ===== From within R: On first use only: <file | install RPostgreSQL package> install.packages('RPostgreSQL') </file> <file rsplus| R Code> library(RPostgreSQL) drv <- dbDriver("PostgreSQL") con <- dbConnect(drv, user="your_username", password="your_password", dbname="workshop"); lakes <- dbGetQuery(con,'SELECT * FROM lakes'); </file> <file rsplus| Plot some data> lakesqc <- dbGetQuery(con,"SELECT * FROM lakes WHERE province='QUEBEC'") hist(lakes$tmean_an) </file> ===== Using the dplyr package===== <file rsplus> library(dplyr) src<-src_postgres(dbname="workshop",host="localhost", port="5432",user="your_username",password="your_password") lakes <- tbl(src, "lakes") # Define lakes table lakes_qc<-filter(lakes, province %=% 'QUEBEC') # Select lakes in Quebec prov_tmean<-summarise(group_by(lakes, province), mean(tmean_an)) # Mean annual temperature per province prov_tmean=collect(prov_tmean) # Transfer result to standard R data frame lakes_qc2<-tbl(src, sql("SELECT * FROM lakes WHERE province='QUEBEC'")) #Perform any SQL statement </file>