Recap from last session

POSIT WORKBENCH
- Beware of the lock with active sessions!!
- Always quit duckDB before leaving workbench


VIEWS are live data, if you change any table the VIEW reports on, the VIEW will update automatically (like a function you rerun every time you query it)
  -- How to list all the views in DuckDB: SELECT view_name FROM duckdb_views;

I/O:
- How to import data into a database
  -- Define table schema including data types and constraints
  -- Add Primary key
  -- Add any foreign keys
  
      CREATE TABLE Snow_surveys (
        Site VARCHAR NOT NULL,
        Year INTEGER NOT NULL CHECK (Year BETWEEN 1950 AND 2015),
        Date DATE NOT NULL,
        Plot VARCHAR NOT NULL,
        Location VARCHAR NOT NULL,
        Snow_cover REAL,
        Water_cover REAL,
        Land_cover REAL,
        Total_cover REAL,
        Observer VARCHAR NOT NULL,
        Notes VARCHAR,
        PRIMARY KEY (Site, Plot, Location, Date)
    );
  
  -- Copy data: COPY Snow_cover FROM "../ASDN_csv/snow_survey_fixed.csv" (header TRUE, nullstr "NA"); 
  -- NOTE: If you run this last lines several times you will keep adding the data to the table!!!

- Update data: (!Alert danger!)
  -- You can do real damage to your data!!
  -- Strategies: (a) Start with SELECT then switch to UPDATE, (b) Create a TMP table and test your code on it
  

EXPORT

-- Exporting the entire database, including schema!! This is a DuckDB feature though
   EXPORT DATABASE 'export_adsn'; 
   
-- Export an entire table
   COPY Species TO 'output.csv' (HEADER, DELIMITER ',');
   
-- Export a specific query
   COPY (SELECT * FROM Species) TO 'species_db.csv' (HEADER, DELIMITER ',');



DATA MANAGEMENT STATEMENTS
- INSERT: already seen that
- UPDATE table SET ... WHERE ...
- DELETE FROM table WHERE ...
- caution: these operate on whole tables if unqualified
- strategies to avoid catastrophes
-- just subconsciouly be careful, like holding a kitchen knife
-- put comment in front: -- DELETE FROM ..., then remove comment
-- do SELECT first, then replace SELECT with DELETE <- allows inspection
   of what's about to be deleted
-- tweak table name, put x in front, then remove


TRIGGERS
- on some event, take some action(s)
- event: insert, update, delete on some table
- action: insert or update in same table or any other table
- example: SQLite imports empty columns as empty strings, not NULLs,
  which is lame
- database.db build script (look at it) does post-import updates
- trigger approach!
- can't play with real Species table due to foreign keys
-- .import --csv species.csv new_species
- .nullvalue --NULL--
- notice no NULLs!
- CREATE TRIGGER fix_species
      AFTER INSERT ON new_species
      FOR EACH ROW
      BEGIN
          UPDATE new_species
          SET Scientific_name = NULL
          WHERE Code = new.Code AND Scientific_name = '';
      END;