System Versioned Tables

System Versioned Tables

th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 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: 228Questions: 37Answers: 1
    edited June 2021

    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;
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    Perfect - if you execute the query and return the results as JSON to the client-side, you'll have an array of objects something like:

    [
      {
        "c.case_number": 1,
        "cn.csirs_number": 100,
        "c.report_date": "2021-06-14",
        ...
      }
    ]
    

    And you can use the usual DataTables options to populate the table.

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    I'll give it a shot again. Thank you.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    So I did get this working. I have noticed that the search filter does not work now and the number of records displayed (10) does not match the "Showing 1 to 5 of 5 entries"

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Are you using server-side processing? If so, the three queries used to determine the data, the number of records in the filtered set and the number of records in the unfiltered set would all need to use similar conditions to get just the latest rows for consider.

    Can you give me a link to your page showing the issue please?

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Sure can. Just sent it to you.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Any luck on this Allan?

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Guess this isn't do-able

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Apologies! I totally lost track of the thread. I've just added a reply back - having problems getting pass the security :).

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    I sent you the info today.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Just checking to see if you were able to log in.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I've just sent a reply back by PM. Still having login issues I'm afraid.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    I sent the credentials. They are different than the first set of creds.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Still having problems?

Sign In or Register to comment.