com.github.jlangch.venice.chinook-postgresql.venice Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of venice Show documentation
Show all versions of venice Show documentation
Venice, a sandboxed Lisp implemented in Java.
;;;; __ __ _
;;;; \ \ / /__ _ __ (_) ___ ___
;;;; \ \/ / _ \ '_ \| |/ __/ _ \
;;;; \ / __/ | | | | (_| __/
;;;; \/ \___|_| |_|_|\___\___|
;;;;
;;;;
;;;; Copyright 2017-2024 Venice
;;;;
;;;; Licensed under the Apache License, Version 2.0 (the "License");
;;;; you may not use this file except in compliance with the License.
;;;; You may obtain a copy of the License at
;;;;
;;;; http://www.apache.org/licenses/LICENSE-2.0
;;;;
;;;; Unless required by applicable law or agreed to in writing, software
;;;; distributed under the License is distributed on an "AS IS" BASIS,
;;;; WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
;;;; See the License for the specific language governing permissions and
;;;; limitations under the License.
;;;; Chinook database data set for PostgreSQL
;;;; See: https://github.com/lerocha/chinook-database
(ns chinook-postgresql)
(load-module :jdbc-core ['jdbc-core :as 'jdbc])
(load-module :jdbc-postgresql ['jdbc-postgresql :as 'jdbp])
(defonce data "https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_PostgreSql_AutoIncrementPKs.sql")
(defonce data-model "https://github.com/lerocha/chinook-database/tree/master#data-model")
(defonce database "chinook_auto_increment")
(defn
^{ :arglists '(
"(show-data-model)" )
:doc """
Opens a browser to show the
[Chinook data model](https://github.com/lerocha/chinook-database/tree/master#data-model)
"""
:examples '(
"""
(do
(load-module :chinook-postgresql ['chinook-postgresql :as 'chinook])
(chinook/show-data-model))
""")
:see-also '(
"chinook-postgresql/show-data"
"chinook-postgresql/download-data"
"chinook-postgresql/load-data" ) }
show-data-model []
(sh/open data-model))
(defn
^{ :arglists '(
"(show-data)" )
:doc """
Opens a browser to show the
[Chinook data](https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_PostgreSql_AutoIncrementPKs.sql)
"""
:examples '(
"""
(do
(load-module :chinook-postgresql ['chinook-postgresql :as 'chinook])
(chinook/show-data))
""")
:see-also '(
"chinook-postgresql/show-data-model"
"chinook-postgresql/download-data"
"chinook-postgresql/load-data" ) }
show-data []
(sh/open data))
(defn
^{ :arglists '(
"(download-data)" )
:doc """
Download the Chinook dataset for PostgreSQL.
The data set is downloaded from [GitHub/lerocha](https://github.com/lerocha/chinook-database)
The data set is published under the
[License](https://github.com/lerocha/chinook-database/blob/master/LICENSE.md)
"""
:examples '(
"""
(do
(load-module :chinook-postgresql ['chinook-postgresql :as 'chinook])
(chinook/download-data))
""")
:see-also '(
"chinook-postgresql/show-data-model"
"chinook-postgresql/load-data" ) }
download-data []
(-> (io/download data :binary false :encoding :utf-8)
;; split the file in two parts
(str/split ".c chinook_auto_increment;")))
(defn
^{ :arglists '(
"(load-data)" )
:doc """
Load the Chinook dataset to a PostgreSQL database.
The data set is loaded from [GitHub/lerocha](https://github.com/lerocha/chinook-database)
[Data Model](https://github.com/lerocha/chinook-database/tree/master#data-model)
published under the
[License](https://github.com/lerocha/chinook-database/blob/master/LICENSE.md)
The Chinook sample database has 11 tables as follows:
| [![width: 15%]] | [![width: 85%]] |
| employees | stores employee data such as id, last name, first name, \
etc. It also has a field named ReportsTo to specify who \
reports to whom |
| customers | stores customer data |
| invoices | stores invoice header data |
| invoice_items | stores the invoice line items data |
| artists | stores artist data. It is a simple table that contains \
the id and name |
| albums | stores data about a list of tracks. Each album \
belongs to one artist. However, one artist may have \
multiple albums |
| media_types | stores media types such as MPEG audio and AAC \
audio files |
| genres | stores music types such as rock, jazz, metal, etc. |
| tracks | stores the data of songs. Each track belongs to one \
album |
| playlists | stores data about playlists. Each playlist contains \
a list of tracks. Each track may belong to multiple \
playlists. The relationship between the playlists and \
tracks tables is many-to-many. The playlist_track \
table is used to reflect this relationship |
| playlist_track | reflect the many-to-many relationship between plylist \
and tracks |
Start the PostgreSQL docker container:
```
(do
(load-module :jdbc-postgresql ['jdbc-postgresql :as 'jdbp])
(jdbp/start "postgres" "16.2" 5432 "./postgres-storage" "postgres" "postgres"))
```
*Note: The storage directory (e.g. "./postgres-storage") must exist!*
"""
:examples '(
"""
(do
(load-module :chinook-postgresql ['chinook-postgresql :as 'chinook])
(chinook/load-data "localhost" 5432 "postgres" "postgres"))
""",
"""
(do
(load-module :chinook-postgresql ['chinook-postgresql :as 'chinook])
(load-module :jdbc-core ['jdbc-core :as 'jdbc])
(load-module :jdbc-postgresql ['jdbc-postgresql :as 'jdbp])
(try-with [conn (jdbp/create-connection "localhost"
5432
chinook/database
"postgres"
"postgres")]
(-> (jdbc/execute-query conn "SELECT * FROM album WHERE title LIKE '%Mozart%'")
(jdbc/print-query-result))))
""")
:see-also '(
"chinook-postgresql/show-data-model"
"chinook-postgresql/download-data" ) }
load-data [host port user password]
(println "Downloading data from \"https://github.com/lerocha/chinook-database\"...")
(let [[sql1 sql2] (download-data)]
(println "Download finished.")
;; drop and create the database (instead of sql1 we run run directly)
(try-with [conn (jdbp/create-connection host port user password)]
(jdbc/create-database conn database true)
(println "Created new database."))
;; load the data
(try-with [conn (jdbp/create-connection host port database user password)
st (jdbc/create-statement conn)]
(jdbc/execute st sql2)
(println "Data loaded into new database."))))