# DB VERSION

## DB Version
simatris 20210204

## ENGINE
+--------+----------+
| ENGINE | COUNT(*) |
+--------+----------+
| NULL | 10 |
| InnoDB | 165 |
| MyISAM | 355 |
+--------+----------+

## ROW_FORMAT
+------------+----------+
| ROW_FORMAT | COUNT(*) |
+------------+----------+
| NULL | 10 |
| Dynamic | 435 |
| Fixed | 85 |
+------------+----------+

## TABLE_ROWS
+----------+----------+
| RENTANG | COUNT(*) |
+----------+----------+
| 0 | 206 |
| 10 | 132 |
| 100 | 65 |
| 1000 | 37 |
| 10000 | 37 |
| 100000 | 27 |
| 1000000 | 13 |
| 10000000 | 3 |
| NULL | 10 |
+----------+----------+

Top 3
1. nilai = 6905683
2. kelaskuliah_presensimahasiswa = 3303840
3. kuesionerpp_hasil = 3023909

## DATA_LENGTH
+------------+----------+
| RENTANG | COUNT(*) |
+------------+----------+
| 0 | 159 |
| 10 | 3 |
| 100 | 40 |
| 1000 | 74 |
| 10000 | 26 |
| 100000 | 136 |
| 1000000 | 34 |
| 10000000 | 31 |
| 100000000 | 13 |
| 1000000000 | 4 |
| NULL | 10 |
+------------+----------+

Top 4
1. kuesionerpp_hasil = 344981504
2. nilai = 322540324
3. kelaskuliah_presensimahasiswa = 198230400
4. pengambilanmk = 117574592
5. transkrip_detil = 47890432

## INDEX_LENGTH
+------------+----------+
| RENTANG | COUNT(*) |
+------------+----------+
| 0 | 125 |
| 10000 | 311 |
| 100000 | 42 |
| 1000000 | 15 |
| 10000000 | 20 |
| 100000000 | 4 |
| 1000000000 | 3 |
| NULL | 10 |
+------------+----------+

Top 7
1. kuesionerpp_hasil = 495976448
2. nilai = 236479488
3. pengambilanmk = 101083136
4. kelaskuliah_presensimahasiswa = 55929856
5. oauth_refresh_token = 53477376
6. krss = 19464192
7. oauth_access_token = 17432576

## Jumlah Column
+-----------+----------+
| RENTANG | COUNT(*) |
+-----------+----------+
| 10 | 383 |
| 20 | 97 |
| 30 | 21 |
| 40 | 13 |
| 50 | 9 |
| 60 | 2 |
| 80 | 1 |
| Above 100 | 4 |
+-----------+----------+

Top 4
1. aturan = 201
2. mahasiswa = 193
3. calonmahasiswa = 184
4. trpim = 105
5. dosen = 76

## SQL

-- engine
SELECT
  ENGINE, COUNT(*)
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204"
GROUP BY ENGINE;

-- 
SELECT
  ROW_FORMAT, COUNT(*)
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204"
GROUP BY ROW_FORMAT;

-- order TABLE_ROWS
SELECT
  TABLE_NAME, TABLE_ROWS
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204"
ORDER BY TABLE_ROWS DESC;

-- TABLE_ROWS
SELECT t.RENTANG, COUNT(*)
FROM (
	SELECT CASE
		WHEN TABLE_ROWS IS NULL THEN 'NULL'
		WHEN TABLE_ROWS = 0 THEN '0'
		WHEN TABLE_ROWS BETWEEN 1 AND 10 THEN '10'
		WHEN TABLE_ROWS BETWEEN 11 AND 100 THEN '100'
		WHEN TABLE_ROWS BETWEEN 101 AND 1000 THEN '1000'
		WHEN TABLE_ROWS BETWEEN 1001 AND 10000 THEN '10000'
		WHEN TABLE_ROWS BETWEEN 10001 AND 100000 THEN '100000'
		WHEN TABLE_ROWS BETWEEN 100001 AND 1000000 THEN '1000000'
		WHEN TABLE_ROWS BETWEEN 1000001 AND 10000000 THEN '10000000'
		ELSE 'LAIN-LAIN'
		END AS RENTANG
	FROM
		information_schema.TABLES
	WHERE
		TABLE_SCHEMA = "sikad_tsm_20210204"
	) t
GROUP BY t.RENTANG
ORDER BY t.RENTANG ASC;

-- 
SELECT
  TABLE_NAME, DATA_LENGTH
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204"
ORDER BY DATA_LENGTH DESC;

-- DATA_LENGTH
SELECT t.RENTANG, COUNT(*)
FROM (
	SELECT CASE
		WHEN DATA_LENGTH IS NULL THEN 'NULL'
		WHEN DATA_LENGTH = 0 THEN '0'
		WHEN DATA_LENGTH BETWEEN 1 AND 10 THEN '10'
		WHEN DATA_LENGTH BETWEEN 11 AND 100 THEN '100'
		WHEN DATA_LENGTH BETWEEN 101 AND 1000 THEN '1000'
		WHEN DATA_LENGTH BETWEEN 1001 AND 10000 THEN '10000'
		WHEN DATA_LENGTH BETWEEN 10001 AND 100000 THEN '100000'
		WHEN DATA_LENGTH BETWEEN 100001 AND 1000000 THEN '1000000'
		WHEN DATA_LENGTH BETWEEN 1000001 AND 10000000 THEN '10000000'
		WHEN DATA_LENGTH BETWEEN 10000001 AND 100000000 THEN '100000000'
		WHEN DATA_LENGTH BETWEEN 100000001 AND 1000000000 THEN '1000000000'
		ELSE 'LAIN-LAIN'
		END AS RENTANG
	FROM
		information_schema.TABLES
	WHERE
		TABLE_SCHEMA = "sikad_tsm_20210204"
	) t
GROUP BY t.RENTANG
ORDER BY t.RENTANG ASC;


-- INDEX_LENGTH
SELECT
  TABLE_NAME, INDEX_LENGTH
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204"
ORDER BY INDEX_LENGTH DESC;

-- INDEX_LENGTH
SELECT t.RENTANG, COUNT(*)
FROM (
	SELECT CASE
		WHEN INDEX_LENGTH IS NULL THEN 'NULL'
		WHEN INDEX_LENGTH = 0 THEN '0'
		WHEN INDEX_LENGTH BETWEEN 1 AND 10 THEN '10'
		WHEN INDEX_LENGTH BETWEEN 11 AND 100 THEN '100'
		WHEN INDEX_LENGTH BETWEEN 101 AND 1000 THEN '1000'
		WHEN INDEX_LENGTH BETWEEN 1001 AND 10000 THEN '10000'
		WHEN INDEX_LENGTH BETWEEN 10001 AND 100000 THEN '100000'
		WHEN INDEX_LENGTH BETWEEN 100001 AND 1000000 THEN '1000000'
		WHEN INDEX_LENGTH BETWEEN 1000001 AND 10000000 THEN '10000000'
		WHEN INDEX_LENGTH BETWEEN 10000001 AND 100000000 THEN '100000000'
		WHEN INDEX_LENGTH BETWEEN 100000001 AND 1000000000 THEN '1000000000'
		ELSE 'LAIN-LAIN'
		END AS RENTANG
	FROM
		information_schema.TABLES
	WHERE
		TABLE_SCHEMA = "sikad_tsm_20210204"
	) t
GROUP BY t.RENTANG
ORDER BY t.RENTANG ASC;

-- COLUMNS
SELECT
	TABLE_NAME, COUNT(*) AS JML
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'sikad_tsm_20210204'
GROUP BY TABLE_NAME
ORDER BY JML DESC;

SELECT
  COUNT(*)
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "sikad_tsm_20210204";

SELECT t.RENTANG, COUNT(*)
FROM (
	SELECT CASE
		WHEN COUNT(*) IS NULL THEN 'NULL'
		WHEN COUNT(*) = 0 THEN '0'
		WHEN COUNT(*) BETWEEN 1 AND 10 THEN '10'
		WHEN COUNT(*) BETWEEN 11 AND 20 THEN '20'
		WHEN COUNT(*) BETWEEN 21 AND 30 THEN '30'
		WHEN COUNT(*) BETWEEN 31 AND 40 THEN '40'
		WHEN COUNT(*) BETWEEN 41 AND 50 THEN '50'
		WHEN COUNT(*) BETWEEN 51 AND 60 THEN '60'
		WHEN COUNT(*) BETWEEN 61 AND 70 THEN '70'
		WHEN COUNT(*) BETWEEN 71 AND 80 THEN '80'
		WHEN COUNT(*) BETWEEN 81 AND 90 THEN '90'
		WHEN COUNT(*) BETWEEN 91 AND 100 THEN '100'
		ELSE 'Above 100'
		END AS RENTANG
	FROM
		information_schema.COLUMNS
	WHERE
		TABLE_SCHEMA = "sikad_tsm_20210204"
	GROUP BY TABLE_NAME
	) t
GROUP BY t.RENTANG
ORDER BY t.RENTANG ASC;

## Total
Total tables = 530
Total columns = 5666

## Database
- [x] jumlah field per tabel? #data
mahasiswa = 190an field/colomn
- [x] tabel yg paling banyak dibaca? #data
- [ ] null vs empty string "" FK, doctrine => error #problem, sering terjadi, sentry.
- [ ] tabel yg paling banyak dijoin? #data

## Code
- [ ] jumlah URL/menu di simatris per role/jenis-user? #data
- [ ] jumlah endpoint di api-siakad4 per role #data
- [ ] code yg dikomentar (harusnya dihapus) #problem
- [ ] commit message #problem
- [ ] sinkronisasi/integrasi antar sistem (ex: civitas-sikad) #challenge, sikad-pusaka: rest
- cara resolve gagal sinkron
- [ ] test specification #rekomendasi
- [ ] LOC (kuantitasi) vs feature+BUG () #data
- [ ] config #problem
- hardcode ex: upload file
- masing2 kampus beda config, terdeploy
- data credential dipush ke github
- [ ] lesson learn dari project evoting
- security
- log
- [ ] lesson learn dari optimasi api-siakad4
- ketahui bottleneck sebelum optimasi dg profiling
- ambil data yg diperlukan saja
- hati2 menggunakan ORM
- hati2 memakai library, hindari bloated code
- [ ] TDD, pilot => fitur kritikal
- [ ] API Spec, json schema + API Mock

## Next
- tabel yg paling banyak dibaca
- tabel yg paling banyak dijoin
- endpoint yg paling banyak diakses
- ratio fitur/bug
- architecture/deployment diagram
- knowledege management
- statistik per repo
- statistik/analisis logs

## Feedback
- (ady) config, secret management
dev butuh lihat env
- simpan di DB, alih2 di file
- admin API
- service khusus config: hasicorp vault
- (ady) rikues api platform
- storage management, block, NFS:
- permission
- object stroge, service
- https://min.io/