System Versioned Tables

System Versioned Tables

th3t1ckth3t1ck Posts: 213Questions: 35Answers: 1

Does datatables support system versioned tables? Specifically can/how do I use something like select * from table "FOR SYSTEM_TIME ALL"

Answers

  • allanallan Posts: 53,929Questions: 1Answers: 8,403 Site admin

    Do you mean more Editor and specifically its server-side libraries than DataTables? From DataTables point of view, as long as you give it a JSON or HTML feed, then it doesn't really matter what your database structure is.

    For Editor, it depends on what your database schema is really... Can you tell me a little more about how you have it set up?

    Thanks,
    Allan

  • th3t1ckth3t1ck Posts: 213Questions: 35Answers: 1
    edited June 11

    I don't need to modify the records so I don't see where I would need editor. I am setting up an existing table with system versioning so I can track changes made to the records in the table. This table is called cases...

    DESCRIBE cases;
    +------------------------+---------------------+------+-----+------------+------------------+
    | Field                  | Type                | Null | Key | Default    | Extra            |
    +------------------------+---------------------+------+-----+------------+------------------+
    | case_number            | int(10) unsigned    | NO   | PRI | NULL       | auto_increment   |
    | csirs_number           | varchar(10)         | YES  |     | 0          |                  |
    | report_date            | date                | NO   |     | NULL       |                  |
    | start_date             | date                | NO   |     | NULL       |                  |
    | close_date             | date                | YES  |     | 0000-00-00 |                  |
    | incident_type          | int(10) unsigned    | NO   | MUL | NULL       |                  |
    | incident_subtype       | int(10) unsigned    | NO   | MUL | NULL       |                  |
    | dept_code              | int(10) unsigned    | NO   | MUL | NULL       |                  |
    | department             | int(10) unsigned    | NO   | MUL | NULL       |                  |
    | status                 | int(10) unsigned    | NO   | MUL | NULL       |                  |
    | category               | int(10) unsigned    | NO   | MUL | NULL       |                  |
    | investigator_id        | int(10) unsigned    | NO   | MUL | NULL       |                  |
    | investigator_full_name | int(10) unsigned    | NO   | MUL | NULL       |                  |
    | number_warrants        | varchar(3)          | NO   |     | 0          |                  |
    | arrest_resulted        | varchar(3)          | NO   |     | no         |                  |
    | malware_resulted       | varchar(3)          | NO   |     | no         |                  |
    | computer               | int(3) unsigned     | NO   |     | 0          |                  |
    | infotainment           | int(3) unsigned     | NO   |     | 0          |                  |
    | mobile                 | int(3) unsigned     | NO   |     | 0          |                  |
    | storage                | int(3) unsigned     | NO   |     | 0          |                  |
    | synopsis               | text                | NO   |     | NULL       |                  |
    | notes                  | mediumtext          | NO   |     | NULL       |                  |
    | start_trxid            | bigint(20) unsigned | NO   |     | NULL       | STORED GENERATED |
    | end_trxid              | bigint(20) unsigned | NO   | PRI | NULL       | STORED GENERATED |
    | logged_user            | varchar(30)         | YES  |     | NULL       |                  |
    | ip                     | varchar(30)         | YES  |     | NULL       |                  |
    +------------------------+---------------------+------+-----+------------+------------------+
    

    And my query I'd like to use...

    SELECT c.case_number, cn.csirs_number, c.report_date, c.start_date, 
        -> c.close_date, deptCode.code AS 'Deptartment Code', dept.department, stat.status, 
        -> intType.incident_type, intsType.incident_subtype, cat.category, fu.user_id, fun.full_name
        -> FROM cases
        -> FOR SYSTEM_TIME ALL
        -> c
        -> LEFT JOIN csirs cn
        -> ON c.csirs_number = cn.id
        -> LEFT JOIN lk_department_codes deptCode
        -> ON c.dept_code = deptCode.id
        -> LEFT JOIN lk_departments dept
        -> ON c.department = dept.id
        -> LEFT JOIN lk_status stat
        -> ON c.status = stat.id
        -> LEFT JOIN lk_incident_types intType
        -> ON c.incident_type = intType.id
        -> LEFT JOIN lk_incident_subtypes intsType
        -> ON c.incident_subtype = intsType.id
        -> LEFT JOIN lk_category cat
        -> ON c.category = cat.id
        -> LEFT JOIN forensic_users fu
        -> ON c.investigator_id = fu.user_id
        -> LEFT JOIN forensic_users fun
        -> ON c.investigator_full_name = fun.full_name;
    
Sign In or Register to comment.