migrate and revert executable, query insert and delete test

17 files changed, 211 insertions, 9 deletions

diff --git a/Cargo.lock b/Cargo.lock
index 812df3b..ffa901e 100644
--- a/Cargo.lock
+++ b/Cargo.lock
@@ -62,6 +62,7 @@ dependencies = [
  "byteorder 1.3.1 (registry+https://github.com/rust-lang/crates.io-index)",
  "diesel_derives 1.4.0 (registry+https://github.com/rust-lang/crates.io-index)",
  "pq-sys 0.4.6 (registry+https://github.com/rust-lang/crates.io-index)",
+ "serde_json 1.0.39 (registry+https://github.com/rust-lang/crates.io-index)",
 ]
 
 [[package]]
@@ -90,6 +91,14 @@ version = "0.2.50"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 
 [[package]]
+name = "migrations_internals"
+version = "1.4.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+dependencies = [
+ "diesel 1.4.2 (registry+https://github.com/rust-lang/crates.io-index)",
+]
+
+[[package]]
 name = "pq-sys"
 version = "0.4.6"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -266,6 +275,7 @@ version = "0.1.0"
 dependencies = [
  "clap 2.32.0 (registry+https://github.com/rust-lang/crates.io-index)",
  "diesel 1.4.2 (registry+https://github.com/rust-lang/crates.io-index)",
+ "migrations_internals 1.4.0 (registry+https://github.com/rust-lang/crates.io-index)",
  "rand 0.6.5 (registry+https://github.com/rust-lang/crates.io-index)",
  "serde 1.0.89 (registry+https://github.com/rust-lang/crates.io-index)",
  "serde_derive 1.0.89 (registry+https://github.com/rust-lang/crates.io-index)",
@@ -367,6 +377,7 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
 "checksum fuchsia-cprng 0.1.1 (registry+https://github.com/rust-lang/crates.io-index)" = "a06f77d526c1a601b7c4cdd98f54b5eaabffc14d5f2f0296febdc7f357c6d3ba"
 "checksum itoa 0.4.3 (registry+https://github.com/rust-lang/crates.io-index)" = "1306f3464951f30e30d12373d31c79fbd52d236e5e896fd92f96ec7babbbe60b"
 "checksum libc 0.2.50 (registry+https://github.com/rust-lang/crates.io-index)" = "aab692d7759f5cd8c859e169db98ae5b52c924add2af5fbbca11d12fefb567c1"
+"checksum migrations_internals 1.4.0 (registry+https://github.com/rust-lang/crates.io-index)" = "8089920229070f914b9ce9b07ef60e175b2b9bc2d35c3edd8bf4433604e863b9"
 "checksum pq-sys 0.4.6 (registry+https://github.com/rust-lang/crates.io-index)" = "6ac25eee5a0582f45a67e837e350d784e7003bd29a5f460796772061ca49ffda"
 "checksum proc-macro2 0.4.27 (registry+https://github.com/rust-lang/crates.io-index)" = "4d317f9caece796be1980837fd5cb3dfec5613ebdb04ad0956deea83ce168915"
 "checksum quote 0.6.11 (registry+https://github.com/rust-lang/crates.io-index)" = "cdd8e04bd9c52e0342b406469d494fcb033be4bdbe5c606016defbb1681411e1"
diff --git a/Cargo.toml b/Cargo.toml
index 0ee91d2..ac59a4e 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -12,4 +12,5 @@ termion = "*"
 rand = "0.6"
 toml = "0.4"
 clap = "2"
-diesel = {version = "1.4.2", features = ["postgres"]}
+diesel = {version = "1.4.2", features = ["postgres", "serde_json"]}
+migrations_internals = "1.4.0"
diff --git a/diesel.toml b/diesel.toml
new file mode 100644
index 0000000..f57985a
--- /dev/null
+++ b/diesel.toml
@@ -0,0 +1,2 @@
+[print_schema]
+file = "src/schema.rs"
diff --git a/migrations/.gitkeep b/migrations/.gitkeep
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/migrations/.gitkeep
diff --git a/migrations/0_diesel_inital_setup/down.sql b/migrations/0_diesel_inital_setup/down.sql
new file mode 100644
index 0000000..a9f5260
--- /dev/null
+++ b/migrations/0_diesel_inital_setup/down.sql
@@ -0,0 +1,6 @@
+-- This file was automatically created by Diesel to setup helper functions
+-- and other internal bookkeeping. This file is safe to edit, any future
+-- changes will be added to existing projects as new migrations.
+
+DROP FUNCTION IF EXISTS diesel_manage_updated_at(_tbl regclass);
+DROP FUNCTION IF EXISTS diesel_set_updated_at();
diff --git a/migrations/0_diesel_inital_setup/up.sql b/migrations/0_diesel_inital_setup/up.sql
new file mode 100644
index 0000000..dc50259
--- /dev/null
+++ b/migrations/0_diesel_inital_setup/up.sql
@@ -0,0 +1,33 @@
+-- This file was automatically created by Diesel to setup helper functions
+-- and other internal bookkeeping. This file is safe to edit, any future
+-- changes will be added to existing projects as new migrations.
+
+-- Sets up a trigger for the given table to automatically set a column called
+-- `updated_at` whenever the row is modified (unless `updated_at` was included
+-- in the modified columns)
+--
+-- # Example
+--
+-- ```sql
+-- CREATE TABLE users (id SERIAL PRIMARY KEY, updated_at TIMESTAMP NOT NULL DEFAULT NOW());
+--
+-- SELECT diesel_manage_updated_at('users');
+-- ```
+CREATE OR REPLACE FUNCTION diesel_manage_updated_at(_tbl regclass) RETURNS VOID AS $$
+BEGIN
+    EXECUTE format('CREATE TRIGGER set_updated_at BEFORE UPDATE ON %s
+                    FOR EACH ROW EXECUTE PROCEDURE diesel_set_updated_at()', _tbl);
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION diesel_set_updated_at() RETURNS trigger AS $$
+BEGIN
+    IF (
+        NEW IS DISTINCT FROM OLD AND
+        NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at
+    ) THEN
+        NEW.updated_at := current_timestamp;
+    END IF;
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
diff --git a/migrations/1_create_masses/down.sql b/migrations/1_create_masses/down.sql
new file mode 100644
index 0000000..819bb40
--- /dev/null
+++ b/migrations/1_create_masses/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE masses
diff --git a/migrations/1_create_masses/up.sql b/migrations/1_create_masses/up.sql
new file mode 100644
index 0000000..c0b6890
--- /dev/null
+++ b/migrations/1_create_masses/up.sql
@@ -0,0 +1,12 @@
+-- Your SQL goes here
+CREATE TABLE masses (
+	id SERIAL PRIMARY KEY,
+	name VARCHAR NOT NULL,
+	pos_x FLOAT NOT NULL,
+	pos_y DOUBLE PRECISION NOT NULL,
+	pos_z DOUBLE PRECISION NOT NULL,
+	vel_x DOUBLE PRECISION NOT NULL,
+	vel_y DOUBLE PRECISION NOT NULL,
+	vel_z DOUBLE PRECISION NOT NULL,
+	type_data JSONB NOT NULL
+)
diff --git a/postgres/bootstrap.sh b/postgres/bootstrap.sh
index 391aeab..070e104 100644
--- a/postgres/bootstrap.sh
+++ b/postgres/bootstrap.sh
@@ -10,8 +10,12 @@ apt-get install -y xauth
 apt-get install -y postgresql postgresql-client pgadmin3
 
 # set up db
-sudo -u postgres psql -c "CREATE USER space WITH PASSWORD 'space';"
-sudo -u postgres psql -c "CREATE DATABASE space_db WITH OWNER space;"
+USERNAME=$(grep POSTGRES_USERNAME /vagrant/src/constants.rs | cut -d '"' -f2)
+PASSWORD=$(grep POSTGRES_PASSWORD /vagrant/src/constants.rs | cut -d '"' -f2)
+DB_NAME=$(grep POSTGRES_DB_NAME /vagrant/src/constants.rs | cut -d '"' -f2)
+
+sudo -u postgres psql -c "CREATE USER $USERNAME WITH PASSWORD '$PASSWORD';"
+sudo -u postgres psql -c "CREATE DATABASE $DB_NAME WITH OWNER $USERNAME;"
 
 # copy configs
 cp /vagrant/postgres/*conf /etc/postgresql/9.6/main/
diff --git a/src/bin/migrate.rs b/src/bin/migrate.rs
new file mode 100644
index 0000000..8d269a4
--- /dev/null
+++ b/src/bin/migrate.rs
@@ -0,0 +1,14 @@
+use diesel::pg::PgConnection;
+use diesel::prelude::*;
+
+use space::math::get_db_url;
+
+fn main() {
+    let connection = PgConnection::establish(&get_db_url()).expect("Cannot connect");
+    migrations_internals::run_pending_migrations(&connection).expect("Cannot run migrations");
+
+    //migrations_internals::revert_latest_migration(&connection)
+    //    .expect("Cannot revert migrations");
+    //migrations_internals::revert_latest_migration(&connection)
+    //    .expect("Cannot revert migrations");
+}
diff --git a/src/bin/revert.rs b/src/bin/revert.rs
new file mode 100644
index 0000000..cd26968
--- /dev/null
+++ b/src/bin/revert.rs
@@ -0,0 +1,10 @@
+use diesel::pg::PgConnection;
+use diesel::prelude::*;
+
+use space::math::get_db_url;
+
+fn main() {
+    let connection = PgConnection::establish(&get_db_url()).expect("Cannot connect");
+    migrations_internals::revert_latest_migration(&connection).expect("Cannot revert migrations");
+    migrations_internals::revert_latest_migration(&connection).expect("Cannot revert migrations");
+}
diff --git a/src/constants.rs b/src/constants.rs
index 6f2cf6f..ad73b19 100644
--- a/src/constants.rs
+++ b/src/constants.rs
@@ -29,3 +29,8 @@ pub const CRUDE_MINERALS_SIZE: usize = 10;
 
 pub const SLEEP_DURATION: u64 = 100;
 pub const FLOAT_PRECISION: f64 = 0.001;
+
+pub const POSTGRES_USERNAME: &str = "space";
+pub const POSTGRES_PASSWORD: &str = "space";
+pub const POSTGRES_IP: &str = "localhost";
+pub const POSTGRES_DB_NAME: &str = "space_db";
diff --git a/src/lib.rs b/src/lib.rs
index e398dba..26dee5c 100644
--- a/src/lib.rs
+++ b/src/lib.rs
@@ -1,5 +1,7 @@
 #[macro_use]
 extern crate serde_derive;
+#[macro_use]
+extern crate diesel;
 
 pub mod client;
 pub mod constants;
@@ -7,5 +9,6 @@ pub mod item;
 pub mod mass;
 pub mod math;
 pub mod modules;
+pub mod schema;
 pub mod server_connection;
 pub mod storage;
diff --git a/src/mass.rs b/src/mass.rs
index 820173f..ed859a4 100644
--- a/src/mass.rs
+++ b/src/mass.rs
@@ -15,6 +15,7 @@ use crate::modules::navigation::Navigation;
 use crate::modules::refinery::Refinery;
 use crate::modules::tractorbeam::Tractorbeam;
 use crate::modules::types::ModuleType;
+use crate::schema::masses as db_masses;
 use crate::storage::Storage;
 
 #[derive(Serialize, Deserialize, Debug, Clone)]
@@ -25,6 +26,32 @@ pub struct Mass {
     pub effects: Effects,
 }
 
+#[derive(Queryable)]
+pub struct MassEntry {
+    pub id: i32,
+    pub name: String,
+    pub pos_x: f64,
+    pub pos_y: f64,
+    pub pos_z: f64,
+    pub vel_x: f64,
+    pub vel_y: f64,
+    pub vel_z: f64,
+    pub type_data: serde_json::Value,
+}
+
+#[derive(Insertable)]
+#[table_name = "db_masses"]
+pub struct NewMassEntry {
+    pub name: String,
+    pub pos_x: f64,
+    pub pos_y: f64,
+    pub pos_z: f64,
+    pub vel_x: f64,
+    pub vel_y: f64,
+    pub vel_z: f64,
+    pub type_data: serde_json::Value,
+}
+
 #[derive(Serialize, Deserialize, Debug, Clone, Default)]
 pub struct Effects {
     acceleration: Vector,
@@ -299,4 +326,18 @@ impl Mass {
             _ => 0,
         }
     }
+
+    pub fn to_new_mass_entry(&self, name: String) -> NewMassEntry {
+        NewMassEntry {
+            name,
+            pos_x: self.position.x,
+            pos_y: self.position.y,
+            pos_z: self.position.z,
+            vel_x: self.velocity.x,
+            vel_y: self.velocity.y,
+            vel_z: self.velocity.z,
+            type_data: serde_json::from_str(&serde_json::to_string(&self.mass_type).unwrap())
+                .unwrap(),
+        }
+    }
 }
diff --git a/src/math.rs b/src/math.rs
index 4f0558c..5ba7981 100644
--- a/src/math.rs
+++ b/src/math.rs
@@ -13,6 +13,19 @@ pub fn rand_name() -> String {
         .collect()
 }
 
+pub fn get_db_url() -> String {
+    let mut db_url = String::new();
+    db_url.push_str("postgres://");
+    db_url.push_str(constants::POSTGRES_USERNAME);
+    db_url.push_str(":");
+    db_url.push_str(constants::POSTGRES_PASSWORD);
+    db_url.push_str("@");
+    db_url.push_str(constants::POSTGRES_IP);
+    db_url.push_str("/");
+    db_url.push_str(constants::POSTGRES_DB_NAME);
+    db_url
+}
+
 #[derive(Serialize, Deserialize, Debug, Clone, Default)]
 pub struct ControlSystem {
     previous_error: f64,
diff --git a/src/schema.rs b/src/schema.rs
new file mode 100644
index 0000000..2bb43ed
--- /dev/null
+++ b/src/schema.rs
@@ -0,0 +1,13 @@
+table! {
+    masses (id) {
+        id -> Integer,
+        name -> Varchar,
+        pos_x -> Double,
+        pos_y -> Double,
+        pos_z -> Double,
+        vel_x -> Double,
+        vel_y -> Double,
+        vel_z -> Double,
+        type_data -> Jsonb,
+    }
+}
diff --git a/tests/tests.rs b/tests/tests.rs
index 884b812..fd8349d 100644
--- a/tests/tests.rs
+++ b/tests/tests.rs
@@ -1,5 +1,6 @@
-extern crate space;
 extern crate diesel;
+extern crate migrations_internals;
+extern crate space;
 
 #[cfg(test)]
 mod tests {
@@ -7,13 +8,15 @@ mod tests {
     use std::thread::sleep;
     use std::time::Duration;
 
-    use diesel::prelude::*;
     use diesel::pg::PgConnection;
+    use diesel::prelude::*;
+    use space::schema::masses::dsl;
+    use space::schema::masses::dsl::masses as db_masses;
 
     use space::constants;
     use space::item::{Item, ItemType};
-    use space::mass::Mass;
-    use space::math::Vector;
+    use space::mass::{Mass, MassEntry};
+    use space::math::{get_db_url, Vector};
     use space::modules::construction;
     use space::modules::mining;
     use space::modules::navigation;
@@ -523,7 +526,36 @@ mod tests {
     }
 
     #[test]
-    fn test_postgres_connection() {
-        PgConnection::establish("postgres://space:space@localhost/space_db").expect("Cannot connect");
+    fn test_postgres() {
+        let connection = PgConnection::establish(&get_db_url()).expect("Cannot connect");
+
+        let masses = db_masses
+            .load::<MassEntry>(&connection)
+            .expect("Cannot query, probably no migrations, run 'cargo run --bin migrate'");
+        let size = masses.len();
+        let name = String::from("test");
+
+        diesel::insert_into(db_masses)
+            .values(&Mass::new_astroid().to_new_mass_entry(name.clone()))
+            .execute(&connection)
+            .expect("Cannot insert");
+
+        let len = db_masses
+            .load::<MassEntry>(&connection)
+            .expect("Cannot query")
+            .len();
+
+        assert!(len == size + 1);
+
+        diesel::delete(db_masses.filter(dsl::name.eq(name)))
+            .execute(&connection)
+            .expect("Cannot delete");
+
+        let len = db_masses
+            .load::<MassEntry>(&connection)
+            .expect("Cannot query")
+            .len();
+
+        assert!(len == size);
     }
 }