Databases Schemas

[GratitudeToken]

We are using ScyllaDB now.

Example query:

SELECT column_name FROM table_name LIMIT 1; // selects first row from column from table.

To run scylla using docker: docker run --name scylladb -p 9042:9042 -d scylladb/scylla

then login to cqlsh with default username and pass: cqlsh -u cassandra -p cassandra

Change pass: ALTER ROLE cassandra WITH PASSWORD = 'new_password';

I am defining the tables as I go with development:

NEXT STEP create the keyspace named genesis (This needs to be updated depending on the nodes added):

CREATE KEYSPACE genesis WITH replication = {'class': 'NetworkTopologyStrategy', 'datacenter1': 3};

To use the keyspace (the database): USE genesis;

In ScyllaDB datacenter1 is the name of the local DB;

Finding the Correct Datacenter Name: You can find the correct datacenter names by checking the configuration of your ScyllaDB or Cassandra nodes (e.g., in the cassandra-rackdc.properties file for Cassandra or the equivalent in ScyllaDB). Alternatively, you can use the "nodetool status" command, which displays datacenter names along with node statuses.

You may have to change: nano etc/scylla/scylla.yaml

settings like this:

authenticator: PasswordAuthenticator authorizer: CassandraAuthorizer

TABLES STRUCTURE

CREATE TABLE users (
  UserID uuid PRIMARY KEY,
  FullName text,
  Email text,
  DOB date,
  Sex text,
  UserName text,
  InvitedBy text,
  RegistrationDate timestamp,
  City text,
  Country text,
  Lang text
);

You should also index email and username:

CREATE INDEX ON users (Email);
CREATE INDEX ON users (UserName);

CREATE TABLE progression(
  UserID uuid PRIMARY KEY,
  level int,
  xp int,
  titles set<text>,
  achievements set<text>,
  abilities set<text>,
  enhancements set<text>,
  connections set<text>,
  following set<text>,
  blocked set<text>,
  pins set<text>,
  shareboard set<text>
);

INSERT INTO progression (userid, titles, level, xp) VALUES(c4471371-b6af-4392-9746-5261271ec442, {'Newb'}, 1, 0);

CREATE TABLE user_fault (
  UserID uuid PRIMARY KEY,
  Penalty text,
  Prison text,
  Ban text
);

CREATE TABLE user_actions (
  UserID uuid PRIMARY KEY,
  Date timestamp,
  Type text,
  Cooldown int,
  Details text
);

CREATE TABLE email_code (
  Email text PRIMARY KEY,
  Code text,
  Date timestamp
);

---

CREATE TABLE pvt_app (
  UserID uuid,
  PrivateKeyShare text,
  CreationDate timestamp,
  PRIMARY KEY (UserID)
);

CREATE INDEX ON pvt_app(PrivateKeyShare);

CREATE TABLE pvt_faceid (
  UserID uuid,
  PrivateKeyShare text,
  CreationDate timestamp,
  PRIMARY KEY (UserID)
);

CREATE INDEX ON pvt_faceid(PrivateKeyShare);

CREATE TABLE session (
    username text,
    ip_address text,
    token_string text,
    date timestamp,
    authenticated boolean,
    PRIMARY KEY (username, ip_address, token_string)
);

2 types of requests identified so far:

1. Regular request mostly for reading or normal read/update/write actions in ScyllaDB
2. Blockchain Transaction/Action (here the private key share must also be included.

ALL requests must first check the user_fault table before anything.

REGULAR REQUESTS Every regular request from the front-end that has to do with reading or writing data in genesis keyspace must pass the following validation schema:

• token_string (the session token code generated by the dApp) is a match
• purpose is a match ("login" for example)
• the username (last column) is not null and equals to the username sent from the request (this means that the user was previously authenticated anywhere from a second ago to the 10 days max session period which leads to the final condition...
• not expired ... expiry periods for each type of purpose, for Login it should be 10 days - case in which the session entry will be deleted from the DB and the client code has to clear all the local storage session credentials. For other purposes it can be much less, in minutes or less.

BLOCKCHAIN REQUESTS Everything above plus the private key share or use FaceID feature

[dyablohunter]

I think we can remove table and tables from the names at the end of each name.

[IntelligentQuantum]

I think we can remove table and tables from the names at the end of each name.

Y, I removed it from PG

[GratitudeToken]

This looks different now, I have changed it and I am using MYSQL.