Lab 8: PowerShell Database Loader Introduction Lab 8 is translation of Lab 5 from Python to PowerShell. You are provided a file containing student registration records. Each record contains information about the student and the class in which they are registered. You are to take this information and create two tables in a database using PowerShell and SQLite3 . The input file is in Comma Separated Values format (CSV) and the output is a SQLite3 database. Support for SQLite3 is added from a DLL obtained from sqlite.org and placed in your private repo (see below). Requirements Your PowerShell script shall be named dbload.ps1 , located in the root of your repo and be marked executable. Usage: ./dbload.ps1 INPUTCSV OUTPUTDB Your PowerShell script shall read CSV file INPUTCV and create a SQLite3 database OUTPUTDB . In that database you will create two tables classes and students with the following attributes (please use these exact names and data types and see the CREATE statements below): A sample input CSV file is in your repo, called studentregs.csv . Inspect it carefully to learn where the fields are located. Note the column headings. The following is true about all input files you will need to process: The field separator character is a comma. Double quote marks are used to enclose strings with imbedded spaces. classes id (text) subjcode (text) coursenumber (text) termcode (text) TEXT students id (text primary key unique) lastname (text) firstname (text) email (text) major (text) city (text) state (text) zip (text) TEXT There is one record for each class registration and there may be more than one registration per student. The student information is duplicated for each class registration by that student. Two fields in the CSV file require explanation: Use the wnumber field in the CSV file as the ID field in both tables The course field ( $row.course) in the CSV file is composed of the subject code and the course number. To process the CSV file, do this: 1 $args[0] is the first argument on the commandline, the name of the CSV file When writing your code, open the database with these PowerShell statements: 1 This Add-Type statement adds the SQLite3 functionality to PowerShell 2 This DLL is provided for you in your repo in the dlls/ folder 3 The name of the output database file is obtained from the commandline arguments 4 This prints an appropriate error message if any of the above commands fail 5 $_ is the current error How to DROP the two tables and CREATE the students and classes tables in SQLite3 : try { $csv = import-csv $args[0] -delimiter "," } catch { write-output ("Error opening CSV file: $_") exit 1 } 1 try { Add-Type -Path "dlls/System.Data.SQLite.dll" $con = New-Object -TypeName System.Data.SQLite.SQLiteConnection $con.ConnectionString = "Data Source=$($args[1])" $con.Open() } catch { write-output("Error opening database file: $_") exit exit 1 } POWERSHELL 1 2 3 4 5 To insert a record into the students table, use this code, and create similar code for the classes table (without the "or REPLACE" clause). 1 This begins the SQLite3 transaction for a later commit 2 Notice the @parameters for each field that must be provided with AddWithValue() . 3 AddWithValue() supplies the data for each parameter in the INSERT statement 4 The id field in the students table is provided from the wnumber field in the CSV file. 5 ExecuteNonQuery() executes the SQL command 6 Here we commit the changes to the database. $transaction = $con.BeginTransaction("create") $sql = $con.CreateCommand() $sql.CommandText = 'DROP table if exists students' [void]$sql.ExecuteNononQuery() $sql = $con.CreateCommand() $sql.CommandText = 'DROP table if exists classes' [void]$sql.ExecuteNononQuery() $sql.CommandText = 'CREATE table classes (id text, subjcode text, coursenumber text, termcode text);' [void]$sql.ExecuteNononQuery() $sql = $con.CreateCommand() $sql.CommandText = 'CREATE table students (id text primary key unique, lastname text, firstname text, major text, email text, city text, state state text, zip text);' [void]$sql.ExecuteNononQuery() [void]$transaction.Commit() POWERSHELL $transaction = $con.BeginTransaction("addstudenttransaction") $sql.CommandText = "INSERT or REPLACE into students (id,firstname,l astname,email,major,city,state,zip) values(@id,@firstname,@lastname,@email,@major,@city,@state,@zip);" [void]$sql.Parameters.AddWithValue("@id", $row.wnumber) [void]$sql.Parameters.AddWithValue("@firstname", $row.firstname) [void]$sql.Parameters.AddWithValue("@lastname", $row.lastname) [void]$sql.Parameters.AddWithValue("@email", $row.email) [void]$sql.Parameters.AddWithValue("@major", $row.major) [void]$sql.Parameters.AddWithValue("@city", $row.city) [void]$sql.Parameters.AddWithValue("@state", $row.state) [void]$sql.Parameters.AddWithValue("@zip", $row.zip) [void]$sql.ExecuteNonQuery() [void]$transaction.Commit() POWERSHELL 1 2 3 4 5 6 When inserting into the classes table, you must split the string in the course column into subjcode and coursenumber . Say a row contains "CS 3030" in the course field. The subjcode is CS and coursenumber is 3030 . Extract the two fields using split(" ") : 1 $s[0] = "CS" 2 $s[1] = "3030" Hints Suggested logic (and this is not the only way it could be done): Issue an appropriate usage message and exit(1) if the user did not specify both the input CSV file and the output database file. Read the CSV file using import-csv in a try/catch block. and print the exception and exit(1) if an error occurs. Open the database using try/catch and print the exception and exit(1) if an error occurs Drop the two tables students and classes from the database if they exist and then create them. For each record in the CSV file (using foreach ($row in $csv) {…} ): insert or replace the student record into the students table. insert the class record into the classes table exit(0) after all records have been added to the database Add comments to document your logic. Lab 5 required that you skip the first record (the header record) in the CSV file. This is not required for this lab because PowerShell skips it for you. Clone your private repo on github.com In the assignment module in Canvas, find the link to the Canvas page entitled "Clone your Lab 8 Repo", click on it and follow the instructions. Copy your private repo down to icarus Write and test dbload.ps1 in the root of your repo folder $s = $row.course.split(" ") POWERSHELL 1 2 git clone https://github.com com/cowancs3030/lab8-YOURGITHUBUSERNAME cdcd lab8-YOURGITHUBUSERNAME BASH ! Touch nothing else in your repo except for your dbload.ps1 file. Fire up your favorite text editor, and update the header: " Test your program manually using the sample CSV file studentregs.csv , BEFORE using cucumber. Cucumber likes to hide important error messages, syntax errors and crashes. The sqlite3 utility can be used to verify the contents of your database file. Run you script with studentregs.csv , creating test.db and then execute these commands to examine the contents of test.db . 1 Execute your dbload.ps1 with the sample CSV file and create database test.db 2 Display the CREATE TABLE entries from test.db 3 Display 10 records in the students table with a column header 4 Display 10 records from the classes table with a column header After you are convinced your code is correct, run cucumber to verify your progress: ./cucumber -s ! cucumber randomly generates testfiles so you will want to run cucumber many, many times to verify your script’s operation. Submit your assignment code for grading Remember, you must push your script in your private repo to github.com to receive any points, for all assignments in this course: #!/usr/bin/env pwsh #!/usr/bin/env pwsh # (Your name) # Lab 8 - PowerShell Database Loader # CS 3030 - Scripting Languages (add your celestial code here) TEXT ./dbload.ps1 studentregs.csv test.db sqlite3 test.db ".schema" sqlite3 test.db -header header "select * from students limit 10;" sqlite3 test.db -header header "select * from classes limit 10;" BASH 1 2 3 4 Files created for this lab dbload.ps1 Grading Here is how you earn points for this assignment: FEATURES POINTS Must-Have Features Script is named correctly and found in the root folder of your private repo 5 Script is executable 5 Required Features Script prints a “Usage:” statement and exits rc=1 if either the INPUTCSV or OUTPUTDB files are not specified on the commandline 10 Script prints an error message containing the word “Error” and exits rc=1 if the INPUTCSV file cannot be opened 15 Script prints an error message containing the word “Error” and exits rc=1 if the OUTPUTDB file cannot be opened 15 Script exits rc=0 on successful completion 10 Script correctly defines the students table in the database 30 Script correctly defines the classes table in the database 30 Script adds the right number of students to the students table 40 Script adds the right number of classes to the classes table 40 Script add the correct student data to the students table 50 Script adds the correct classes data to the classes table 50 Grand Total 300 git add add dbload.ps1 git commit -m"COMMIT MESSAGE" git push origin master BASH