System Versioned Tables

th3t1ck

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

allan

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

th3t1ck

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;

allan

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

th3t1ck

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

th3t1ck

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"

allan

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

th3t1ck

Sure can. Just sent it to you.

th3t1ck

Any luck on this Allan?

th3t1ck

Guess this isn't do-able

allan

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

Allan

th3t1ck

I sent you the info today.

th3t1ck

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

allan

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

th3t1ck

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

th3t1ck

Still having problems?