Praktik 6: Database Non Spasial

InternalSebagian terujiArti badge ini

Materi Praktik 6 meminta membuat tabel lewat DBeaver, mengimpor CSV, lalu menambahkan primary key dan foreign key. Berkasnya hanya berisi judul dan langkah singkat; hampir seluruh isi DOCX adalah tangkapan layar.

Berkas yang diperiksa:

• Praktik 6/Management Database Non Spasial/File latihan/katalog_data_2d.csv
• Praktik 6/Management Database Non Spasial/Membuat Tabel, Import Tabel, Membuat Primary Key dan Foreign Key.docx

P6-1. CSV memakai pemisah pipa, bukan koma (Kritikal)

Baris pertama katalog_data_2d.csv:

"data_2d_id"|"layer_name"|"akses"|"is_editable"|"wms_url"|"wfs_url"|"author"

Pemisahnya tanda pipa, padahal nama berkasnya .csv. Materi meminta impor lewat “Import Data” di DBeaver, dan DBeaver memakai koma sebagai pemisah bawaan. Hasilnya satu kolom raksasa berisi seluruh baris, bukan tujuh kolom.

Yang membuat ini mudah lolos: berkasnya terlihat rapi saat dibuka di editor teks, dan materi tidak memuat tangkapan layar hasil impor yang bisa dipakai untuk membandingkan.

Berkasnya berisi dua baris data. Kolom author pada keduanya berisi nilai yang sama, ae5c7b2e-3537-4e94-ae1c-7596f1185f28.

P6-2. Kolom author tidak ada di tabel users (Tinggi)

Materi meminta menghapus seluruh baris hasil impor sebelum membuat foreign key. Alasannya tidak dijelaskan, sehingga langkah itu terbaca sebagai hafalan.

Sebab sebenarnya: nilai author di CSV tidak ada di tabel users. Foreign key ke kolom yang tidak punya baris induk selalu gagal. Hal yang sama terjadi pada katalog_data_3d.csv di Praktik 8, yang memakai author bernilai bc810d85-589d-4160-a9db-3c5516fa675a.

P6-3. Tidak ada batasan nilai di database (Sedang)

Seluruh validasi role dan kolom akses berada di kode aplikasi. Materi meminta impor CSV langsung ke tabel, dan jalur itu melewati validasi tersebut sepenuhnya.

Akibatnya database menerima nilai apa pun. Kolom role di Praktik 9 hanya dijaga oleh daftar di kode, sehingga salah ketik seperti super_admn bisa masuk tanpa peringatan.

P6-4. Seed password super admin dikerjakan manual (Sedang)

Materi Praktik 9 meminta membuka REPL node, menjalankan bcrypt.hashSync, menyalin hasilnya, lalu menempelkannya ke kolom password di DBeaver.

Tiga masalah pada cara itu:

• Hasilnya disalin manual, sehingga gampang salah tempel. Salah tempel password asli ke kolom password membuat akun tidak bisa login, sekaligus menyimpan password polos di database.
• Password aslinya tertinggal di riwayat terminal.
• Langkahnya tidak bisa diulang orang lain dengan hasil yang sama.

P6-5. Berkas materi hampir seluruhnya gambar (Rendah)

Membuat Tabel, Import Tabel, Membuat Primary Key dan Foreign Key.docx berukuran 359 KB, tetapi teksnya hanya enam baris. Sisanya 12 berkas PNG.

Konsekuensinya, materi ini tidak bisa dicari dengan kata kunci, tidak bisa disalin, dan tidak bisa dibaca mesin penerjemah. Peserta yang perlu mengulang satu langkah harus menebak dari gambar.

P6-6. Tidak konsisten dengan Praktik 8 (Rendah)

Praktik 6 menyuruh membuat tabel lewat antarmuka DBeaver. Praktik 8 menyuruh hal yang sama untuk tabel users, juga lewat DBeaver. Tidak ada satu pun langkah yang memakai SQL, padahal SQL membuat ulang tabel yang sama bisa dijalankan berulang dan hasilnya sama setiap kali.

Solusi

Untuk P6-1, pemisah pipa

Skrip perbaiki-csv.mjs membaca berkas apa adanya, mendeteksi pemisah yang benar-benar dipakai, lalu menulis ulang dengan koma. Perintahnya:

node perbaiki-csv.mjs katalog_data_2d.csv katalog_data_2d.bersih.csv

Skrip itu juga memeriksa setiap UUID dan menandai baris kembar, jadi kesalahan isi ikut ketahuan sebelum diimpor. Untuk berkas 3D pada Praktik 8, berkas yang sama bisa dipakai.

Perbaikan yang lebih tahan lama: ganti berkasnya di paket materi dengan versi berpemisah koma, dan tambahkan satu kalimat di modul bahwa pemisahnya koma.

Untuk P6-2, nilai author tidak ada di tabel users

Isi tabel users lebih dahulu, baru impor CSV. Urutannya:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f 01_schema_users_katalog.sql
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 \
  -v email=admin@example.com -v hash='$2b$12$...' -f 02_seed_super_admin.sql
# baru impor CSV

Bila CSV sudah terlanjur diimpor, kosongkan tabelnya tanpa membuat ulang:

TRUNCATE katalog_data_2d;

Kolom author pada skema juga dibuat boleh kosong, sehingga data lama yang belum punya pemilik tetap bisa dimuat.

Untuk P6-3, batasan nilai

Tambahkan batasan di database, jangan hanya di kode aplikasi:

ALTER TABLE users ADD CONSTRAINT users_role_valid
    CHECK (role IN ('viewer', 'editor', 'admin', 'super_admin'));

ALTER TABLE katalog_data_2d ADD CONSTRAINT katalog_data_2d_akses_valid
    CHECK (akses IN ('public', 'private'));

Dengan begitu nilai salah ketik ditolak di titik masuk mana pun, termasuk impor CSV dan DBeaver.

Untuk P6-4, seed password manual

Ganti langkah salin tempel dengan dua perintah:

HASH="$(node hash-password.mjs)"
psql "$DATABASE_URL" -v email=admin@example.com -v hash="$HASH" \
  -f 02_seed_super_admin.sql

Berkas 02_seed_super_admin.sql menolak jalan bila nilainya bukan hash bcrypt, sehingga password polos tidak mungkin masuk ke kolom password.

Untuk P6-5, materi hampir seluruhnya gambar

Ganti sebagian tangkapan layar dengan perintah yang bisa disalin. Untuk pembuatan tabel, perintah SQL lebih berguna daripada gambar langkah DBeaver, karena bisa dijalankan ulang dan hasilnya sama.

Bila tangkapan layar tetap dipertahankan untuk hal yang sulit dijelaskan, tambahkan keterangan teks di dekatnya. Kriteria yang praktis: satu langkah boleh berupa gambar hanya bila isinya tidak bisa dinyatakan sebagai perintah.

Untuk P6-6, pembuatan tabel tidak memakai SQL

Pakai berkas SQL, bukan langkah antarmuka. Cara ini juga membuat tabel bisa dibuat ulang di komputer lain tanpa mengulang setiap klik:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f 01_schema_users_katalog.sql

Berkas 01_schema_users_katalog.sql bisa dijalankan berulang tanpa menghapus data, karena memakai CREATE TABLE IF NOT EXISTS.

Berkas siap pakai

Berkas	Isi
Praktik 6/sql/00_reset.sql	Drop tabel untuk mulai dari nol
Praktik 6/sql/01_schema_users_katalog.sql	Tiga tabel, primary key, foreign key, dan CHECK
Praktik 6/sql/02_seed_super_admin.sql	Seed akun dengan penjagaan hash
Praktik 6/tools/perbaiki-csv.mjs	Ubah pemisah pipa menjadi koma
Praktik 6/data/*.bersih.csv	CSV yang sudah diperbaiki

Untuk P6-7, constraint users_email_key gagal dipasang

Butir ini muncul saat mencoba menjalankan skema. Ada tiga sebab, dan perbaikannya berbeda. Diagnosa lebih dahulu:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f 00_diagnosa.sql

Pesan galat	Sebab	Perbaikan
could not create unique index ... Key (email)=(x) is duplicated	Ada email kembar	Bereskan kembarnya, lalu pasang constraint
relation "users_email_key" already exists	Constraint sudah ada, dijalankan dua kali	Tidak perlu apa pun, atau pakai penjagaan
check constraint "users_role_valid" ... is violated	Ada nilai role di luar daftar	Perbaiki nilainya, lalu pasang constraint

Perbaikan otomatis untuk ketiganya:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f 03_perbaikan-email.sql

Berkas itu melakukan enam hal, berurutan:

1. Membuat tabel bila belum ada.
2. Mengisi email yang kosong dengan nilai unik, lalu mengunci kolomnya NOT NULL.
3. Menyimpan daftar email kembar ke tabel users_email_kembar supaya bisa diperiksa.
4. Memberi akhiran angka pada email kembar, sehingga semua baris tetap ada.
5. Menyisir nilai role yang tidak dikenal menjadi editor, lalu memasang CHECK.
6. Memasang kedua constraint dengan penjagaan IF NOT EXISTS.

Langkah 3 sampai 5 mengubah data. Bila Anda ingin memeriksa lebih dahulu tanpa mengubah apa pun, jalankan hanya 00_diagnosa.sql dan lihat tabel ke-3 dan ke-4.

Berkas itu aman dijalankan berulang. Sudah diuji pada tiga keadaan: database kosong, data dengan email kembar dan role tak dikenal, dan database yang constraint-nya sudah terpasang.

Untuk P6-2 dan P6-8, skema yang menerima CSV apa adanya

Modul menyuruh import lalu menghapus, karena author di CSV berisi UUID yang tidak ada di tabel users. Urutan itu bisa dibalik: isi penulisnya lebih dahulu, baru import. CSV lalu masuk pada percobaan pertama tanpa satu pun DELETE.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f 05_skema-siap-import.sql

Berkas itu melakukan lima hal:

1. Membuat tabel users, katalog_data_2d, dan katalog_data_3d.
2. Mengisi dua penulis yang dipakai CSV, memakai UUID yang sama persis dengan nilai author di berkasnya. Karena itu CSV tidak perlu disunting.
3. Memberi nilai bawaan pada tiga kolom, supaya import yang tidak memuat kolomnya tetap berhasil: akses menjadi private, is_editable menjadi false, dan author boleh kosong.
4. Memasang foreign key. Sekarang pasti berhasil, karena penulisnya sudah ada.
5. Membuat indeks untuk join dan penyaringan.

Dua penulis itu bukan akun login. Emailnya berakhiran @example.com, is_active false, dan hash kata sandinya sengaja tidak cocok dengan apa pun. Ganti dengan akun Anda sendiri bila data itu ingin dimiliki akun yang benar-benar dipakai.

Setelah itu import CSV seperti biasa:

\copy katalog_data_2d FROM 'katalog_data_2d.bersih.csv' WITH (FORMAT csv, HEADER true)
\copy katalog_data_3d FROM 'katalog_data_3d.bersih.csv' WITH (FORMAT csv, HEADER true)

Bila memakai DBeaver: klik kanan tabel, Import Data, pilih berkas, dan pastikan delimiter diisi koma.

Sudah diuji pada PostgreSQL sungguhan. Hasilnya: 2 baris masuk ke katalog_data_2d, 2 baris ke katalog_data_3d, nol baris yatim, dan kedua katalog menampilkan nama pemiliknya. Skema itu juga aman dijalankan berulang.

Tiga nilai bawaan yang membuat import tidak perlu diulang

Kolom	Bawaan	Alasannya
akses	private	Layer baru tidak terbuka ke publik tanpa sengaja
is_editable	false	Layer baru tidak bisa diubah tanpa sengaja
author	boleh kosong	Baris tanpa pemilik tetap bisa dimuat, tidak perlu dihapus

Bawaan private dipilih dengan sadar. Pada WebGIS, salah menyetel akses berarti data terbuka untuk umum, dan kesalahan itu jauh lebih mahal daripada lupa membuka satu layer.

Kalau ingin menghapus penulisnya

ON DELETE RESTRICT dipilih, sehingga user yang masih punya data tidak bisa dihapus. Itu yang diinginkan: menghapus user tidak boleh membuat data katalog kehilangan pemilik tanpa peringatan.

Bila Anda lebih suka barisnya tetap ada dengan pemilik dikosongkan, ganti jadi:

ALTER TABLE katalog_data_2d DROP CONSTRAINT katalog_data_2d_author_fkey;
ALTER TABLE katalog_data_2d ADD CONSTRAINT katalog_data_2d_author_fkey
    FOREIGN KEY (author) REFERENCES users (user_id) ON DELETE SET NULL;

Pilih salah satu, jangan keduanya.

Catatan: UNIQUE (email) tidak menangkap semua duplikat

Dua hal yang sering dikira sudah dijaga padahal belum:

• NULL tidak bertabrakan. UNIQUE memperbolehkan NULL berapa pun. Selama kolom email boleh NULL, email kosong bisa dimasukkan berkali-kali. Karena itu kolomnya perlu NOT NULL, dan berkas perbaikan menguncinya.
• Huruf besar-kecil dianggap berbeda. Budi@example.com dan budi@example.com adalah dua nilai yang berbeda bagi UNIQUE. Berkas perbaikan menyeragamkannya dengan lower(btrim(email)).

Bila ingin penjagaan yang menutup keduanya sekaligus tanpa mengubah data, pakai indeks unik pada bentuk normalnya:

CREATE UNIQUE INDEX IF NOT EXISTS users_email_lower_key ON users (lower(btrim(email)));

Cara itu menjaga email kembar sejak awal, tetapi tidak mengubah isi tabel. Pilih salah satu, jangan keduanya, karena dua penjagaan untuk hal yang sama hanya menambah beban tulis.

Jangan andalkan tab Constraints DBeaver

Tampilan itu menyesatkan, karena tiga sebab:

• NOT NULL bukan constraint di mata sebagian orang. Pada PostgreSQL 18, definisi NOT NULL tersimpan di pg_constraint dengan kode jenis n. DBeaver menampilkan atau menyembunyikannya bergantung pada setelan filter di bilah alat tab itu.
• Nama constraint disimpan dalam huruf kecil. CONSTRAINT users_email_key tersimpan sebagai users_email_key, sedangkan CONSTRAINT usersEmailKey akan tersimpan sebagai usersemailkey. Pencarian dengan huruf besar tidak akan menemukannya.
• Tab itu tidak menyegarkan sendiri. Perlu Refresh setelah menjalankan DDL.

Cara yang pasti: baca katalog sistem langsung.

SELECT c.relname AS tabel, con.conname AS nama_constraint, con.contype AS kode
FROM pg_constraint con
JOIN pg_class c     ON c.oid = con.conrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname IN ('users', 'katalog_data_2d', 'katalog_data_3d')
ORDER BY c.relname, con.contype;

Kode jenis: p primary key, u unique, f foreign key, c check, n not null.

Jumlah yang benar pada PostgreSQL 18:

Tabel	Constraint	Rincian
users	10	1 primary key, 1 unique, 1 check, 7 not null
katalog_data_2d	8	1 primary key, 1 foreign key, 1 check, 5 not null
katalog_data_3d	8	1 primary key, 1 foreign key, 2 check, 4 not null

Bila jumlahnya kurang, jalankan 06_periksa-constraint.sql. Berkas itu menyebutkan nama constraint yang hilang satu per satu, sehingga tidak perlu membandingkan daftar secara manual.

Kenapa constraint bisa belum terpasang

CREATE TABLE IF NOT EXISTS tidak melakukan apa pun bila tabelnya sudah ada. Bila tabel dibuat lebih dahulu tanpa constraint, lalu berkas yang memuat constraint dijalankan, hasilnya bergantung pada apakah seluruh berkas itu benar-benar dieksekusi sampai selesai. Di DBeaver, menekan Execute statement alih-alih Execute script hanya menjalankan satu pernyataan, sehingga sisanya terlewat tanpa peringatan.

Berkas 05_skema-siap-import.sql aman dijalankan berulang, dan hanya menambahkan yang belum ada. Menjalankannya lagi pada tabel yang constraint-nya belum lengkap akan melengkapinya.

Ringkasan

Butir	Tingkat	Inti
P6-1	Kritikal	Pemisah pipa pada berkas .csv
P6-2	Tinggi	Nilai author tidak ada di tabel users
P6-3	Sedang	Tidak ada batasan nilai di database
P6-4	Sedang	Seed password dikerjakan manual dan tidak bisa diulang
P6-5	Rendah	Materi hampir seluruhnya gambar
P6-6	Rendah	Pembuatan tabel tidak memakai SQL
P6-7	Tinggi	Skema tidak idempoten, constraint gagal bila data sudah ada
P6-8	Sedang	Alur import lalu hapus tidak diperlukan bila penulis diisi lebih dahulu