Differences

This shows you the differences between two versions of the page.

--- opendb [2017/02/07 14:29]
glaroc [Exercise 2 - Importing data into PostgreSQL]
+++ opendb [2019/04/30 15:39] (current)
qcbs [Importing a table in LibreOffice Base from Calc]
@@ Line 2: / Line 2: @@
-Workshop offered by Guillaume Larocque (guillaume.larocque@mcgill.ca), research professional at the [[http://qcbs.ca|Quebec Centre for Biodiversity Science]] on February 7 and 14, 2017.
+Workshop offered by Guillaume Larocque (guillaume.larocque@mcgill.ca), research professional at the [[http://qcbs.ca|Quebec Centre for Biodiversity Science]].
@@ Line 45: / Line 45: @@
 ===== Accessing PSQL =====
 **On Windows and Mac**:
-  * Find PostgreSQL 9.X in the Start menu and click on "SQL shell (psql)"
+  * 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.
@@ Line 59: / Line 59: @@
 </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/9.5/data/pg_hba.conf'. Make the necessary changes, hit CTRL-O and then CTRL-X.
+  - 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-9.5.plist
+sudo launchctl load /Library/LaunchDaemons/com.edb.launchd.postgresql-10.plist
 </file>
@@ Line 327: / Line 327: @@
 <file postgresql>
-CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id varchar(25) NOT NULL);
+CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id INT NOT NULL);
-COPY lakes_species FROM 'C:/opendbwork/Lakes_Species.csv' WITH csv HEADER DELIMITER AS ',';
+COPY lakes_species FROM 'C:/bdlibre/lakes_species.csv' WITH csv HEADER DELIMITER AS ',';
 </file>
@@ Line 343: / Line 343: @@
 ++++ Command |
 <file>
-CREATE TABLE species_acro (unique_id INT NOT NULL, full_name varchar(100), species_id varchar(25));
+CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id INT NOT NULL);
-COPY species_acro FROM 'C:/opendbwork/Species_Acro.csv' WITH csv HEADER DELIMITER AS ',';
+COPY lakes_species FROM 'C:/bdlibre/lakes_species.csv' WITH csv HEADER DELIMITER AS ',';
 </file>
 ++++
@@ Line 354: / Line 354: @@
   * 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 'unique_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.
+  * 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.
@@ Line 606: / Line 606: @@
-===== Creating tables and forms =====
+===== 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:
@@ Line 655: / Line 673: @@
   * 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 ======
+========= 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]]
@@ Line 742: / Line 760: @@
 [[http://www.w3schools.com/cssref/|CSS reference]]
-On peut donner un "id" à n'importe quel élément html. Important: il ne peut y avoir qu'un id unique par élément
+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">
@@ Line 927: / Line 945: @@
 From within R:
 On first use only:
-<file | install RMySQL package>
+<file | install RPostgreSQL package>
 install.packages('RPostgreSQL')
 </file>