> On May 2, 2017, 3:58 p.m., Nate Cole wrote:
> > ambari-server/src/main/resources/Ambari-DDL-Derby-CREATE.sql
> > Lines 284-286 (original), 283-294 (patched)
> > <https://reviews.apache.org/r/58929/diff/1/?file=1705974#file1705974line287>
> >
> > We usually use BIGINTs for timestamps. I seem to recall JPA mapping
> > these differently for db types.
>
> Robert Levas wrote:
> The original line was not changed, the diff didn't show that the original
> line was moved.
> ```
> create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> ```
>
> I used the same timestamp type from the the original in the new table as
> well. That said, I am all for consitancy and will change it to BIGINT or
> whatever is appoproate for the specific databases.
After further research, it appears that using TIMSTAMP may be a bit easier when
it comes to inserting the initial users and user_authenticating records. For
example in Derby, the inserts will look like:
```
insert into users(user_id, principal_id, user_name, display_name,
local_username, create_timestamp)
SELECT 1, 1, 'admin', 'Administrator', 'admin', {fn
TIMESTAMPDIFF(SQL_TSI_SECOND,timestamp('1970-1-1-00.00.00.000000'),
current_timestamp)} FROM SYSIBM.SYSDUMMY1;
insert into user_authentication(user_authentication_id, user_id,
authentication_type, authentication_key, create_timestamp, update_timestamp)
SELECT 1, 1, 'LOCAL',
'538916f8943ec225d97a9a86a2c6ec0818c1cd400e09e03b660fdaaec4af29ddbb6f2b1033b81b00',
{fn TIMESTAMPDIFF(SQL_TSI_SECOND,timestamp('1970-1-1-00.00.00.000000'),
current_timestamp)}, {fn
TIMESTAMPDIFF(SQL_TSI_SECOND,timestamp('1970-1-1-00.00.00.000000'),
current_timestamp)} FROM SYSIBM.SYSDUMMY1;
```
For databases that I am not familiar with and cannot test, I am not sure how to
these conversions will go.
Let me know if you still think it is worth the effort.
- Robert
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/58929/#review173630
-----------------------------------------------------------
On May 2, 2017, 3:09 p.m., Robert Levas wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/58929/
> -----------------------------------------------------------
>
> (Updated May 2, 2017, 3:09 p.m.)
>
>
> Review request for Ambari, Attila Magyar, Balázs Bence Sári, Eugene
> Chekanskiy, Jonathan Hurley, Laszlo Puskas, Nate Cole, and Sebastian Toader.
>
>
> Bugs: AMBARI-20907
> https://issues.apache.org/jira/browse/AMBARI-20907
>
>
> Repository: ambari
>
>
> Description
> -------
>
> User management tables in the DB should be:
>
> # users
> Name |Type |Description
> -------------------------------|----------|-----------
> user_id |INTEGER |Internal unique identifier
> principal_id |INTEGER |Foreign key from adminprincipal
> table
> user_name |VARCHAR |Unique, case-insensitive, login
> identifier expected to be used when logging into Ambari
> create_time |TIMESTAMP |Creation time for this account in
> Ambari
> active |BOOLEAN |Active/not active flag
> consecutive_failed_auth_attemps|INTEGER |The number a failed authorization
> attempts since the last successful authentication
> active_widgets_layout |VARCHAR |
> display_name |VARCHAR |Cosmetic name value to show the
> user in user interfaces
> local_username |VARCHAR |Case-sensitive username to use
> when impersonating user in facilities like Ambari Views
>
> - Primary Key: `user_id`
> - Foreign Key: `principal_id` -> `adminprincipal.principal_id`
>
>
> # user_authentication
> Name |Type |Description
> ----------------------|---------|------------
> user_authentication_id|INTEGER |Primary key for this table
> user_id |INTEGER |Foreign key from users table
> authentication_type |VARCHAR |Type of authentication system - LOCAL, LDAP,
> KERBEROS, JTW, PAM, etc...
> authentication_key |VARCHAR |Type-specific key (or identifier): LOCAL:
> the user's password (digest); LDAP: the user’s distinguished name; KERBEROS:
> the user’s principal; etc...
> create_time |TIMESTAMP|Creation time of this record
> update_time |TIMESTAMP|Update time for this record, can be used to
> enforce password retention times
>
> - Primary Key: `user_authentication_id`
> - Foreign Key: `user_id` -> `users.user_id`
>
>
> Diffs
> -----
>
> ambari-server/src/main/resources/Ambari-DDL-Derby-CREATE.sql b241dc271c
> ambari-server/src/main/resources/Ambari-DDL-MySQL-CREATE.sql 670bf17594
> ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql 00b3248b25
> ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql f6af96807c
> ambari-server/src/main/resources/Ambari-DDL-SQLAnywhere-CREATE.sql
> 64a0137f5b
> ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql 22b2c3d7d5
>
>
> Diff: https://reviews.apache.org/r/58929/diff/1/
>
>
> Testing
> -------
>
> Tested creating new databases using Posgress, MySQL, and Derby.
> _I have no way to test creating Oracle, MS SQL, and SQL Anywhere databases._
>
>
> Thanks,
>
> Robert Levas
>
>
