Database - Maple Help

With built-in database connectivity, Maple allows engineers and scientists to quickly develop and deploy powerful applications that combine large enterprise databases with the state-of-the-art analysis and visualization tools of Maple. You can easily query, create, and update your databases in Maple, without any detailed SQL knowledge. Now in Maple 18, database connectivity has been extended to also include native support for SQLite databases. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. For more information about SQLite, refer to http://www.sqlite.org/

In the following example, we will use Maple's Database package to import population data from the G20 countries into a SQLite database, access the database, and produce population tables and charts.

Import Population Data from a .csv File into SQLite Database

 > $\mathrm{with}\left(\mathrm{ArrayTools}\right):$
 > $\mathrm{with}\left(\mathrm{Database}[\mathrm{SQLite}]\right):$
 > $\mathrm{with}\left(\mathrm{StringTools}\right):$

Import Data File

 > $\mathrm{csv}≔\mathrm{FileTools}:-\mathrm{JoinPath}\left(\left[\mathrm{kernelopts}\left('\mathrm{datadir}'\right),"SQLite","G20-Population.csv"\right]\right):$
 > $\mathrm{data}:=\mathrm{ImportMatrix}\left(\mathrm{csv}\right):$

Create in Memory Database

The Open command opens a new database connection.

 > $\mathrm{db}:=\mathrm{Open}\left(":memory:"\right)$
 ${\mathrm{db}}{:=}{"SQLite database"}{,}{\mathrm{table\left( \left[\left( "main" \right) = "" \right] \right)}}$ (1)

Create Population Table

 > $\mathrm{sql}:=\mathrm{sprintf}\left("CREATE TABLE population \left(%s\right)",\mathrm{Join}\left(\mathrm{convert}\left({\mathrm{data}}_{1},'\mathrm{list}'\right),","\right)\right)$
 ${\mathrm{sql}}{:=}{"CREATE TABLE population \left(Date, USA, CHN, JPN, DEU, FRA, BRA, GBR, ITA, RUS, IND, CAN, AUS, ESP, MEX, KOR, IDN, TUR, SAU, ARG, ZAF\right)"}$ (2)

The Execute command executes a SQL statement using the provided database connection.

 > $\mathrm{Execute}\left(\mathrm{db},\mathrm{sql}\right)$

Insert Data Into Population Table

 > $\mathrm{nrows}:=\mathrm{Size}\left(\mathrm{data},1\right):$
 > $\mathrm{ncols}:=\mathrm{Size}\left(\mathrm{data},2\right):$
 > $\mathrm{sql}:=\mathrm{sprintf}\left("INSERT INTO population VALUES \left(%s\right)",\mathrm{Join}\left(\left["?"\$\mathrm{ncols}\right],","\right)\right)$
 ${\mathrm{sql}}{:=}{"INSERT INTO population VALUES \left(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?\right)"}$ (3)

The Prepare command prepares a SQL statement for execution.

 > $\mathrm{stmt}:=\mathrm{Prepare}\left(\mathrm{db},\mathrm{sql}\right)$
 ${\mathrm{stmt}}{:=}{"SQLite statement"}{,}{"INSERT INTO population VALUES \left(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?\right)"}$ (4)
 >

The Finalize command finalizes the prepared statement.

 > $\mathrm{Finalize}\left(\mathrm{stmt}\right):$

 > $\mathrm{sql}≔"SELECT \cdot FROM population"$
 ${\mathrm{sql}}{:=}{"SELECT * FROM population"}$ (5)
 > $\mathrm{stmt}:=\mathrm{Prepare}\left(\mathrm{db},\mathrm{sql}\right)$
 ${\mathrm{stmt}}{:=}{"SQLite statement"}{,}{"SELECT * FROM population"}$ (6)

FetchAll returns all of the rows of the prepared statement.

 > $\mathrm{FetchAll}\left(\mathrm{stmt}\right)$
 $\left[\begin{array}{c}{\mathrm{53 x 21}}{\mathrm{Matrix}}\\ {\mathrm{Data Type:}}{\mathrm{anything}}\\ {\mathrm{Storage:}}{\mathrm{rectangular}}\\ {\mathrm{Order:}}{\mathrm{C_order}}\end{array}\right]$ (7)
 > $\mathrm{Finalize}\left(\mathrm{stmt}\right):$

Plot Data for Population in Canada from 1975 to 1990

 > $\mathrm{sql}:="SELECT date, CAN FROM population WHERE date >= \text{'}1975-12-31\text{'} AND date <= \text{'}1990-12-31\text{'}"$
 ${\mathrm{sql}}{:=}{"SELECT date, CAN FROM population WHERE date >= \text{'}1975-12-31\text{'} AND date <= \text{'}1990-12-31\text{'}"}$ (8)
 > $\mathrm{stmt}:=\mathrm{Prepare}\left(\mathrm{db},\mathrm{sql}\right)$
 ${\mathrm{stmt}}{:=}{"SQLite statement"}{,}{"SELECT date, CAN FROM population WHERE date >= \text{'}1975-12-31\text{'} AND date <= \text{'}1990-12-31\text{'}"}$ (9)
 > $\mathrm{population}:=\mathrm{Matrix}\left(1..0,1..2\right):$
 > $\mathrm{row}:=1:$
 >
 > $\mathrm{Finalize}\left(\mathrm{stmt}\right):$
 >