Database structure

[enekoerce]

As I couldn't make the installacion through Composer, I've chosen the manual installation downloading all files. Now I need to setup database structure, but I don't find where to get fields names and types.

I have opened the models files and I can check at some fields names in there, as I look into $allowedFields variable. But I don't know if there are another fields not allowed, and I can just guess the type of fields, but don't know for sure.

Where can I find the complete structure to create it manually in my installation?

Thank you, I'm really looking forward to use this library, it looks promising.

[MGatner]

The database structure is available in the migration file (https://github.com/lonnieezell/myth-auth/blob/develop/src/Database/Migrations/2017-11-20-223112_create_auth_tables.php). You can publish that into your app (be sure to change the namespace) and run php spark migrate -a to migrate the database automatically, or use it as a reference to build your own table.

[enekoerce]

Thank you a lot! Didn't know about that file and how to handle the migration. Finally I've been able to migrate database running the command.

Very kind.

[manieqq]

Hello
I have OVH hosting and cannot execute php spark migrate --all
So I am asking for a SQL file or info how to do it?

[enekoerce]

I'm very noob and don't know if this is the way, but I exported my database to see how auth tables are created. My result (hope there's nothing else I didn't see):

-- phpMyAdmin SQL Dump

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8mb4 */;

---

CREATE TABLE auth_activation_attempts (
id int(11) UNSIGNED NOT NULL,
ip_address varchar(255) NOT NULL,
user_agent varchar(255) NOT NULL,
token varchar(255) DEFAULT NULL,
created_at datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

CREATE TABLE auth_groups (
id int(11) UNSIGNED NOT NULL,
name varchar(255) NOT NULL,
description varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

CREATE TABLE auth_groups_permissions (
group_id int(11) UNSIGNED NOT NULL DEFAULT '0',
permission_id int(11) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

CREATE TABLE auth_groups_users (
group_id int(11) UNSIGNED NOT NULL DEFAULT '0',
user_id int(11) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

CREATE TABLE auth_logins (
id int(11) UNSIGNED NOT NULL,
ip_address varchar(255) DEFAULT NULL,
email varchar(255) DEFAULT NULL,
user_id int(11) UNSIGNED DEFAULT NULL,
date datetime NOT NULL,
success tinyint(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

---

CREATE TABLE auth_permissions (
id int(11) UNSIGNED NOT NULL,
name varchar(255) NOT NULL,
description varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

CREATE TABLE auth_reset_attempts (
id int(11) UNSIGNED NOT NULL,
email varchar(255) NOT NULL,
ip_address varchar(255) NOT NULL,
user_agent varchar(255) NOT NULL,
token varchar(255) DEFAULT NULL,
created_at datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

CREATE TABLE auth_tokens (
id int(11) UNSIGNED NOT NULL,
selector varchar(255) NOT NULL,
hashedValidator varchar(255) NOT NULL,
user_id int(11) UNSIGNED NOT NULL,
expires datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

---

CREATE TABLE auth_users_permissions (
user_id int(11) UNSIGNED NOT NULL DEFAULT '0',
permission_id int(11) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

CREATE TABLE users (
id int(11) UNSIGNED NOT NULL,
email varchar(255) COLLATE utf8_spanish_ci NOT NULL,
username varchar(30) COLLATE utf8_spanish_ci DEFAULT NULL,
password_hash varchar(255) COLLATE utf8_spanish_ci NOT NULL,
reset_hash varchar(255) COLLATE utf8_spanish_ci DEFAULT NULL,
reset_at datetime DEFAULT NULL,
reset_expires datetime DEFAULT NULL,
activate_hash varchar(255) COLLATE utf8_spanish_ci DEFAULT NULL,
status varchar(255) COLLATE utf8_spanish_ci DEFAULT NULL,
status_message varchar(255) COLLATE utf8_spanish_ci DEFAULT NULL,
active tinyint(1) NOT NULL DEFAULT '0',
force_pass_reset tinyint(1) NOT NULL DEFAULT '0',
created_at datetime DEFAULT NULL,
updated_at datetime DEFAULT NULL,
deleted_at datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

ALTER TABLE auth_activation_attempts
ADD PRIMARY KEY (id);

ALTER TABLE auth_groups
ADD PRIMARY KEY (id);

ALTER TABLE auth_groups_permissions
ADD KEY auth_groups_permissions_permission_id_foreign (permission_id),
ADD KEY group_id_permission_id (group_id,permission_id);

ALTER TABLE auth_groups_users
ADD KEY auth_groups_users_user_id_foreign (user_id),
ADD KEY group_id_user_id (group_id,user_id);

ALTER TABLE auth_logins
ADD PRIMARY KEY (id);

ALTER TABLE auth_permissions
ADD PRIMARY KEY (id);

ALTER TABLE auth_reset_attempts
ADD PRIMARY KEY (id);

ALTER TABLE auth_tokens
ADD PRIMARY KEY (id);

ALTER TABLE auth_users_permissions
ADD KEY auth_users_permissions_permission_id_foreign (permission_id),
ADD KEY user_id_permission_id (user_id,permission_id);

ALTER TABLE users
ADD PRIMARY KEY (id),
ADD UNIQUE KEY email (email),
ADD UNIQUE KEY username (username);

ALTER TABLE auth_activation_attempts
MODIFY id int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE auth_groups
MODIFY id int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE auth_logins
MODIFY id int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE auth_permissions
MODIFY id int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE auth_reset_attempts
MODIFY id int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE auth_tokens
MODIFY id int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE users
MODIFY id int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE auth_groups_permissions
ADD CONSTRAINT auth_groups_permissions_group_id_foreign FOREIGN KEY (group_id) REFERENCES auth_groups (id) ON DELETE CASCADE,
ADD CONSTRAINT auth_groups_permissions_permission_id_foreign FOREIGN KEY (permission_id) REFERENCES auth_permissions (id) ON DELETE CASCADE;

ALTER TABLE auth_groups_users
ADD CONSTRAINT auth_groups_users_group_id_foreign FOREIGN KEY (group_id) REFERENCES auth_groups (id) ON DELETE CASCADE,
ADD CONSTRAINT auth_groups_users_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE auth_users_permissions
ADD CONSTRAINT auth_users_permissions_permission_id_foreign FOREIGN KEY (permission_id) REFERENCES auth_permissions (id) ON DELETE CASCADE,
ADD CONSTRAINT auth_users_permissions_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[manieqq]

Thanks, everything is working fine