diff options
26 files changed, 165 insertions, 330 deletions
@@ -1,4 +1,4 @@ # This file is *mostly* just a backup for environment variables in case Nix is # either not working or you just don't want to use it. :) -DATABASE_URl=sqlite:whirl.db +DATABASE_URl=whirl.sqlite3 @@ -15,12 +15,12 @@ Cargo.lock # Development /src/_*.* -/whirl.db +/whirl.sqlite3 /Whirl.toml # DB Browser -/whirl.db-shm -/whirl.db-wal +/whirl.sqlite3-shm +/whirl.sqlite3-wal # Wireshark *.pcapng @@ -46,12 +46,13 @@ tokio-util = { version = "0.6.6", features = ["codec"] } tokio-stream = "0.1.5" # Database -sqlx = { version = "0.5.2", features = ["runtime-async-std-rustls", "sqlite", "macros", "migrate", "chrono", "time", "tls"] } +libsqlite3-sys = { version = "0.9.1", features = ["bundled"] } +diesel = { version = "1.4.6", features = ["sqlite"] } # Web-server rocket = "0.4.7" rocket_contrib = "0.4.7" [dev-dependencies] -#sqlx-cli = "0.5.2" +#diesel_cli = { version = "1.4.1", default-features = false, features = ["sqlite-bundled"] } #cargo-watch = "7.8.0" # Optional @@ -45,7 +45,7 @@ Please reference the [contribution guidelines](./CONTRIBUTING.md) of this reposi ## Prerequisites ### Required -- [sqlx-cli](https://crates.io/crates/sqlx-cli) +- [diesel_cli](https://crates.io/crates/diesel_cli) ### Optional - [cargo-watch](https://crates.io/crates/cargo-watch) diff --git a/diesel.toml b/diesel.toml new file mode 100644 index 0000000..71215db --- /dev/null +++ b/diesel.toml @@ -0,0 +1,5 @@ +# For documentation on how to configure this file, +# see diesel.rs/guides/configuring-diesel-cli + +[print_schema] +file = "src/db/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/20210322015725_serial_numbers.down.sql b/migrations/2021-05-03-213012_create_serial_numbers/down.sql index 9b64755..9b64755 100644 --- a/migrations/20210322015725_serial_numbers.down.sql +++ b/migrations/2021-05-03-213012_create_serial_numbers/down.sql diff --git a/migrations/2021-05-03-213012_create_serial_numbers/up.sql b/migrations/2021-05-03-213012_create_serial_numbers/up.sql new file mode 100644 index 0000000..eb626d8 --- /dev/null +++ b/migrations/2021-05-03-213012_create_serial_numbers/up.sql @@ -0,0 +1,5 @@ +CREATE TABLE serial_numbers ( + serial_number TEXT NOT NULL UNIQUE, + user_name TEXT NOT NULL PRIMARY KEY , + serial_status INTEGER NOT NULL +) diff --git a/migrations/20210322015732_user_registration.down.sql b/migrations/2021-05-03-213030_create_user_registration/down.sql index 5bc3c0e..5bc3c0e 100644 --- a/migrations/20210322015732_user_registration.down.sql +++ b/migrations/2021-05-03-213030_create_user_registration/down.sql diff --git a/migrations/2021-05-03-213030_create_user_registration/up.sql b/migrations/2021-05-03-213030_create_user_registration/up.sql new file mode 100644 index 0000000..dc6d9db --- /dev/null +++ b/migrations/2021-05-03-213030_create_user_registration/up.sql @@ -0,0 +1,12 @@ +CREATE TABLE user_registration ( + user_name_lower TEXT NOT NULL, + user_name TEXT NOT NULL PRIMARY KEY, + serial_number TEXT NOT NULL, + password TEXT NOT NULL, + client_version TEXT NOT NULL, + account_status INTEGER NOT NULL, + registration_date TEXT NOT NULL, + times_on INTEGER NOT NULL, + total_minutes INTEGER NOT NULL, + user_privileges INTEGER NOT NULL +) diff --git a/migrations/20210322015737_user_properties.down.sql b/migrations/2021-05-03-213037_create_user_properties/down.sql index 6b03c94..6b03c94 100644 --- a/migrations/20210322015737_user_properties.down.sql +++ b/migrations/2021-05-03-213037_create_user_properties/down.sql diff --git a/migrations/2021-05-03-213037_create_user_properties/up.sql b/migrations/2021-05-03-213037_create_user_properties/up.sql new file mode 100644 index 0000000..11b8893 --- /dev/null +++ b/migrations/2021-05-03-213037_create_user_properties/up.sql @@ -0,0 +1,8 @@ +CREATE TABLE user_properties ( + user_name TEXT NOT NULL PRIMARY KEY, + property_id INTEGER NOT NULL, + property_flags INTEGER NOT NULL, + property_access INTEGER NOT NULL, + property_string_value INTEGER NOT NULL, + property_binary_value TEXT +) diff --git a/migrations/20210322015725_serial_numbers.up.sql b/migrations/20210322015725_serial_numbers.up.sql deleted file mode 100644 index b0c1895..0000000 --- a/migrations/20210322015725_serial_numbers.up.sql +++ /dev/null @@ -1,5 +0,0 @@ -CREATE TABLE serial_numbers ( - serial_number TEXT NOT NULL UNIQUE, - user_name TEXT NOT NULL, - serial_status TEXT NOT NULL -) diff --git a/migrations/20210322015732_user_registration.up.sql b/migrations/20210322015732_user_registration.up.sql deleted file mode 100644 index 6a76e9b..0000000 --- a/migrations/20210322015732_user_registration.up.sql +++ /dev/null @@ -1,12 +0,0 @@ -CREATE TABLE user_registration ( - user_name_lower TEXT NOT NULL, - user_name TEXT NOT NULL, - serial_number TEXT NOT NULL, - password TEXT NOT NULL, - client_version TEXT NOT NULL, - account_status INTEGER NOT NULL, - registration_date TEXT NOT NULL, - times_on INTEGER NOT NULL, - total_minutes INTEGER NOT NULL, - user_privileges INTEGER NOT NULL -) diff --git a/migrations/20210322015737_user_properties.up.sql b/migrations/20210322015737_user_properties.up.sql deleted file mode 100644 index fe43761..0000000 --- a/migrations/20210322015737_user_properties.up.sql +++ /dev/null @@ -1,8 +0,0 @@ -CREATE TABLE user_properties ( - user_name TEXT NOT NULL, - property_id INTEGER NOT NULL, - property_flags INTEGER NOT NULL, - property_access INTEGER NOT NULL, - property_string_value TEXT, - property_binary_value TEXT -) diff --git a/rustfmt.toml b/rustfmt.toml index 6a5c54c..74ad3a4 100644 --- a/rustfmt.toml +++ b/rustfmt.toml @@ -8,6 +8,7 @@ force_multiline_blocks = true format_code_in_doc_comments = true format_macro_matchers = true format_strings = true +ignore = ["src/db/schema.rs"] imports_layout = "HorizontalVertical" license_template_path = ".license_template" match_arm_blocks = false @@ -6,5 +6,5 @@ in pkgs.mkShell { buildInputs = with pkgs; [ niv rust ]; - DATABASE_URL = "sqlite:whirl.db"; + DATABASE_URL = "whirl.sqlite3"; } diff --git a/src/api/routes/stats/structures.rs b/src/api/routes/stats/structures.rs index a141ce6..88fc852 100644 --- a/src/api/routes/stats/structures.rs +++ b/src/api/routes/stats/structures.rs @@ -1,8 +1,6 @@ // Copyleft (ɔ) 2021-2021 The Whirlsplash Collective // SPDX-License-Identifier: GPL-3.0-only -use serde_derive::Serialize; - #[derive(Serialize)] pub struct Statistics { pub system: StatisticsSystem, @@ -11,7 +11,7 @@ impl CLI { let matches = Self::cli().get_matches(); Self::calc_log_level(&matches); - std::env::set_var("DATABASE_URL", "sqlite:whirl.db"); + std::env::set_var("DATABASE_URL", "whirl.sqlite3"); matches } diff --git a/src/config/mod.rs b/src/config/mod.rs index 7b05d72..4f01313 100644 --- a/src/config/mod.rs +++ b/src/config/mod.rs @@ -2,7 +2,6 @@ // SPDX-License-Identifier: GPL-3.0-only use config::{ConfigError, File}; -use serde_derive::{Deserialize, Serialize}; #[derive(Serialize, Deserialize, Debug)] pub struct WhirlsplashConfig { diff --git a/src/db/mod.rs b/src/db/mod.rs index a3257ab..2ae0ece 100644 --- a/src/db/mod.rs +++ b/src/db/mod.rs @@ -1,4 +1,32 @@ // Copyleft (ɔ) 2021-2021 The Whirlsplash Collective // SPDX-License-Identifier: GPL-3.0-only -mod tables; +pub mod models; +mod schema; + +use diesel::prelude::*; + +// use crate::db::models::*; + +pub fn establish_connection() -> SqliteConnection { + let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set"); + SqliteConnection::establish(&database_url) + .unwrap_or_else(|_| panic!("error connecting to {}", database_url)) +} + +#[test] +pub fn show_serials() { + use crate::db::{models::SerialNumber, schema::serial_numbers::dsl::*}; + + dotenv::dotenv().ok(); + + let results = serial_numbers + .limit(5) + .load::<SerialNumber>(&establish_connection()) + .expect("error loading serial numbers table"); + + println!("found {} results", results.len()); + for result in results { + println!("{}", result.user_name); + } +} diff --git a/src/db/models.rs b/src/db/models.rs new file mode 100644 index 0000000..52304b6 --- /dev/null +++ b/src/db/models.rs @@ -0,0 +1,50 @@ +// Copyleft (ɔ) 2021-2021 The Whirlsplash Collective +// SPDX-License-Identifier: GPL-3.0-only + +//! Much of the documentation that you will see within this module is quoted +//! from http://dev.worlds.net/private/GammaDocs/WorldServer.html#RoomServer. + +// use crate::db::schema::*; + +// -------------- +// | Queryables | +// -------------- + +#[derive(Queryable, Debug)] +pub struct SerialNumber { + pub serial_number: String, + pub user_name: String, + pub serial_status: i32, +} + +#[derive(Queryable, Debug)] +pub struct UserRegistration { + pub user_name_lower: String, + pub user_name: String, + pub serial_number: String, + pub password: String, + pub client_version: String, + pub account_status: i32, + pub registration_date: String, + pub times_on: i32, + pub total_minutes: i32, + pub user_privileges: i32, +} + +#[derive(Queryable, Debug)] +pub struct UserProperty { + pub user_name: String, + pub property_id: i32, + pub property_flags: i32, + pub property_access: i32, + pub property_string_value: String, + pub property_binary_value: String, +} + +// --------------- +// | Insertables | +// --------------- + +// -------------- +// | Updatables | +// -------------- diff --git a/src/db/schema.rs b/src/db/schema.rs new file mode 100644 index 0000000..e2c0da4 --- /dev/null +++ b/src/db/schema.rs @@ -0,0 +1,39 @@ +table! { + serial_numbers (user_name) { + serial_number -> Text, + user_name -> Text, + serial_status -> Integer, + } +} + +table! { + user_properties (user_name) { + user_name -> Text, + property_id -> Integer, + property_flags -> Integer, + property_access -> Integer, + property_string_value -> Integer, + property_binary_value -> Nullable<Text>, + } +} + +table! { + user_registration (user_name) { + user_name_lower -> Text, + user_name -> Text, + serial_number -> Text, + password -> Text, + client_version -> Text, + account_status -> Integer, + registration_date -> Text, + times_on -> Integer, + total_minutes -> Integer, + user_privileges -> Integer, + } +} + +allow_tables_to_appear_in_same_query!( + serial_numbers, + user_properties, + user_registration, +); diff --git a/src/db/tables.rs b/src/db/tables.rs deleted file mode 100644 index 399c496..0000000 --- a/src/db/tables.rs +++ /dev/null @@ -1,291 +0,0 @@ -// Copyleft (ɔ) 2021-2021 The Whirlsplash Collective -// SPDX-License-Identifier: GPL-3.0-only - -//! Much of the documentation that you will see within this module is quoted -//! from http://dev.worlds.net/private/GammaDocs/WorldServer.html#RoomServer. - -/// The SerialNumbers table contains a record for every valid serial number. It -/// is initialized with a set of serial numbers by a WorldServer administrator. -/// A user will register by providing a valid serial number that matches an -/// unused table entry. The serial number must be distributed with the client -/// software or in some other way, because it will be required for all -/// registrations from the client. The serial number record contains the -/// following information: -/// -/// -/// The SerialNumbers table will be initialized with a set of serial numbers by -/// a WorldServer administrator. The serialStatus column should be initialized -/// to SERIAL_FREE at this time (this will be done for you when you create -/// serial numbers). A user will then register via the client by providing a -/// valid serial number. The UserServer will set serialStatus to SERIAL_USED -/// upon successful user registration with a given serial number, and enter -/// their username in the userName field. -/// -/// The included program SerialGen can generate a list of serial numbers based -/// on a seed and tagged by prefix. The program runs in the C-shell and produces -/// three output files: an SQL script that you can use to directly modify the -/// SerialNumbers table, a master list as a text table for administration -/// purposes, and a separate text table for use in production of the serial -/// numbers to be given to client end users. See -/// [Generating Serial Numbers](http://dev.worlds.net/private/GammaDocs/WorldServer.html#SerialGen). -/// -/// -/// The values defined for serialStatus are: -/// -/// 0 = SERIAL_FREE -/// -/// 1 = SERIAL_USED -#[derive(Debug)] -pub struct SerialNumbers { - /// The user's serial number - pub serial_number: String, - - /// Username with case intact. - pub user_name: String, - - /// One of {SERIAL_FREE, SERIAL_USED} - pub serial_status: i64, -} - -/// The UserRegistration table contains a record for every registered user. The -/// record holds the following information: -/// -/// The WorldServer will set an authenticated user's privilege level to the -/// value given in this field when the user logs on. This privilege level is -/// communicated to all RoomServers the user connects to. To take effect, the -/// value must be changed while the user is logged off, otherwise it will only -/// become effective at the next login. -/// -/// -/// AccountStatus allowed values: -/// -/// The values defined for accountStatus include: -/// -/// 0 = ACCOUNT_INACTIVE -/// -/// 1 = ACCOUNT_ACTIVE -/// -/// The WorldServer will set accountStatus to ACCOUNT_ACTIVE upon successful -/// user registration. The WorldServer administrator may deactivate an account -/// by setting accountStatus to ACCOUNT_INACTIVE. -/// -/// -/// userPrivileges allowed values: -/// -/// The values defined for userPrivileges include: -/// -/// 0 = No privileges -/// -/// 1 = PRIV_BUILD - the user may dynamically register rooms -/// -/// 2 = PRIV_BROADCAST - the user may broadcast text -/// -/// 4 = PRIV_PROPERTY - the user may retrieve and set all properties of any -/// object -/// -/// 3 = PRIV_BUILD and PRIV_BROADCAST -/// -/// 5 = PRIV_BUILD and PRIV_PROPERTY -/// -/// 6 = PRIV_BROADCAST and PRIV_PROPERTY -/// -/// 7 = PRIV_BUILD and PRIV_BROADCAST and PRIV_PROPERTY -/// -/// The WorldServer will set an authenticated user's privilege level to the -/// value given in this field when the user logs on. This privilege level is -/// communicated to all RoomServers the user connects to. To take effect, the -/// value must be changed while the user is logged off, otherwise it will only -/// become effective at the next login. -#[derive(Debug)] -pub struct UserRegistration { - /// The user name in all lower case. - pub user_name_lower: String, - - /// The user name with case intact. - pub user_name: String, - - /// The user's serial number. - pub serial_number: String, - - /// The user's password. - pub password: String, - - /// The user's client software version. - pub client_version: String, - - /// One of {ACCOUNT_ACTIVE, ACCOUNT_INACTIVE}. - pub account_status: i64, - - /// The date and time the user registered. - pub registration_date: String, - - /// The number of times the user has logged on since registration. - pub times_on: i64, - - /// The number of minutes the user has been logged on since registration. - pub total_minutes: i64, - - pub user_privileges: i64, -} - -/// The UserProperties table is used to store persistent user properties. These -/// are accessed every time a user logs in, and they may also be used to form -/// the reply for a "finger" operation. The UserProperties table contains the -/// following columns: -/// -/// -/// The setting of the PropertyFlag determines which column the value of the -/// property is stored in. When the value of the property is a string and is -/// stored in propertyStringValue, the propertyBinaryValue will be NULL. When -/// the value of the property is binary data and is stored -/// in propertyBinaryValue, the propertyStringValue will be NULL. Properties -/// stored in propertyStringValue will be readable using the Select command in -/// SQLplus. Properties stored in propertyBinaryValue will appear encoded in -/// hexadecimal when selected using SQLplus. -/// -/// The values in the propertyFlags and propertyAccess as seen when doing a -/// select on these columns are as follows: -/// -/// propertyFlags -/// -/// 128 = Store in DB, no auto-update, not a finger property, stored in -/// propertyStringValue. -/// -/// 144 = Store in DB, no auto-update, not a finger property, stored in -/// propertyBinaryValue. -/// -/// 160 = Store in DB, no auto-update, finger property, stored in -/// propertyStringValue. -/// -/// 176 = Store in DB, no auto-update, finger property, stored in -/// propertyBinaryValue. -/// -/// 192 = Store in DB, auto-update, not a finger property, stored in -/// propertyStringValue. -/// -/// 208 = Store in DB, auto-update, not a finger property, stored in -/// propertyBinaryValue. -/// -/// 224 = Store in DB, auto-update, finger property, stored in -/// propertyStringValue. -/// -/// 240 = Store in DB, auto-update, finger property, stored in -/// propertyBinaryValue. -/// -/// -/// propertyAccess -/// -/// 0 = Public write, public read. -/// -/// 1 = Possessor write, public read. -/// -/// 2 = Public write, owner read. -/// -/// 3 = Possessor write, owner read. -/// -/// -/// UserProperties can be used to store persistent user data from session to -/// session, including potentially shared-state properties related to users. -/// Properties and their Id's need to be coordinated between the server and the -/// client (or the client's underlying language). Properties are generally -/// meaningless to the server, except for the reserved properties for -/// session tracking etc. It is up to the client to interpret the property -/// values correctly. -/// -/// In Gamma, properties are exchanged with the client by using attributes. A -/// full discussion of properties, attributes and using shared objects in Gamma -/// will be included in a future document. -/// -/// PropertyId: -/// -/// Each property has a PropertyId field that is one byte. Up to 255 PropertyIds -/// may be defined. Zero is reserved and is not a valid PropertyId. Some of -/// these properties are shared between o bjects (especially those relating to -/// session control), but others may be defined on a per-object basis. Currently -/// defined propertyIds include: -/// -/// Session Properties: -/// -/// 1 = Application Name (string) -/// -/// 2 = User Name (string) -/// -/// 3 = Protocol Number (string) -/// -/// 4 = Error Number (string) -/// -/// 6 = Password (string) -/// -/// 8 = Update Interval (string) -/// -/// 9 = Client Version (string) -/// -/// 10 = Serial Number (string) -/// -/// 12 = Logon/Logoff Flag (string) -/// -/// 13 = Permitted Session Duration (string) -/// -/// 14 = Guest User Flag (string) -/// -/// 15 = Server Type (string) -/// -/// User Properties: -/// -/// 5 = Avatar Bitmap (string) -/// -/// 7 = Avatar Updates Requested (string) -/// -/// 9 = Client Version (string) -/// -/// 11 = Email Address (string) -/// -/// The client software can require users to register with their email addresses -/// as an optional field. For example, in Worlds Chat, the client sends the -/// email address as VAR_EMAIL. VAR_EMAIL in turn is understood by the server to -/// be a property that is stored in the Properties database, as Property ID 11, -/// with the email itself stored in PropertyStringValue as a string. Since this -/// table is also keyed to the username, you can correlate tables of email -/// addresses as needed using SQL. -/// -/// To extract an email address for a given user: -/// ```sql -/// select propertyStringValue -/// from UserProperties -/// where userName='John Doe' and propertyId=11; -/// ``` -/// -/// To extract a list of all recorded email addresses: -/// ```sql -/// select userName, propertyStringValue -/// from UserProperties -/// where propertyId=11 -/// order by userName; -/// ``` -/// -/// You should note however that many database table queries, particularly in -/// the RoomProperties table, might give you data that is stored mostly in -/// binary form, and that cannot be properly interpreted by standard SQL. What -/// you'll get in this case is often meaningless hexadecimal, which could be -/// unicode text or just raw data. -#[derive(Debug)] -pub struct UserProperties { - /// The user name with case intact - pub user_name: String, - - /// The property identifier. - pub property_id: i64, - - /// Each property has a PropertyFlags field that defines certain aspects of - /// the property. - pub property_flags: i64, - - /// Defines access restrictions on the property. - pub property_access: i64, - - /// The value of the property when it is a string. - pub property_string_value: String, - - /// The value of the property when it is binary data. - pub property_binary_value: String, -} @@ -9,11 +9,16 @@ proc_macro_hygiene )] #![warn(rust_2018_idioms)] +#![recursion_limit = "128"] #[macro_use] extern crate log; #[macro_use] extern crate rocket; +#[macro_use] +extern crate diesel; +#[macro_use] +extern crate serde_derive; pub mod cli; pub mod config; diff --git a/src/utils/mod.rs b/src/utils/mod.rs index ce264a8..c188ccd 100644 --- a/src/utils/mod.rs +++ b/src/utils/mod.rs @@ -1,5 +1,5 @@ // Copyleft (ɔ) 2021-2021 The Whirlsplash Collective // SPDX-License-Identifier: GPL-3.0-only -pub mod db; +// pub mod db; pub mod system; |