HOME BLOG

Archive for the ‘Databases’ Category

Database Trigger

Posted on: February 4th, 2023 by Olu No Comments

Hi folks,

In this post I will talk about database trigger. A few days ago, a colleague working on a piece of software I develop tried to submit some code containing database triggers for review. I hadn’t seen any codebase with database triggers until that point. Another developer who looked at the code felt the triggers ought to be removed. This got me curious about what triggers are what they are used for.

What is a database trigger? A database trigger is procedural code that gets executed automatically when certain events happen on a table or view in a database. Triggers are mostly used for maintaining the integrity of the information in the database. E.g. When a new record (for a new worker) is added to the employees table, a trigger can be used to add new records to the taxes, vacations and salaries tables. Triggers can be used to log historical data too for auditing purpose.

Thinking about trigger, I feel they must be used with caution because it is easy for developers to not even know that the triggers exist. This could lead to surprises and lots of time debugging code. This problem can be minimized though through good documentation.
Also, the use of triggers mean the business logic is handled in not just the application source code but in the database which could make it more difficult to understand how the applcation works. Furthermore, it may take more effort to write and maintain automated tests for database triggers.

That’s all for now. Till next time, happy software development.

 

References

Database Trigger. Wikipedia. https://en.wikipedia.org/wiki/Database_trigger.

Handy SQL query for backups

Posted on: April 13th, 2022 by Olu No Comments

Hi folks,

In this post I share a quick and handy technique for backing up tables within a database. It’s the CREATE TABLE AS statement.

You can use it to create a table by copying data from an existing table e.g.

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

 

You can even back up specific columns or rows that meet certain conditions e.g.

CREATE TABLE suppliers
  AS (SELECT companies.id, companies.address, categories.cat_type
      FROM companies, categories
      WHERE companies.id = categories.id
      AND companies.id > 1000);

 

Note: this is a light-weight backup and shouldn’t be used a substitute for a full backup of the entire database.

That’s all for now. Till next time, happy software development.

 

Reference

SQL: CREATE TABLE AS Statement. https://www.techonthenet.com/sql/tables/create_table2.php

Tips for data modelling with MySQL Workbench

Posted on: February 6th, 2021 by Olu No Comments

Hi Folks,

In this post I’ll share  few tips when doing database modelling with MySQL Workbench.

If you need to create a table based on another, duplicate the old tables rather than creatiog the table from scratch. To do this. press Ctrl C, Ctrl V. Then update the table name.

Use separate multiple EER diagrams where appropriate. If working on a large database when multiple different concerns, consider using a separate EER diagram for each concern. Don’t shove all very many tables into a single EER diagram.

Double-check queries when synchronizing entity model with database. you will be surprised at some unexpected SQL you may occasionally find generated by MySQL Workbench. To avoid nasty surprises, always read through the SQL queries before applying them to your database.

Use MySQL Model tab to quickly view all tables in a MySQL Workbench file.

When copying tables, pay attention to autoincrement clause carried over from the old table, deleting it if necessary.

That’s all for now. Till next time, happy software development.

How to import SQLite tables quickly

Posted on: October 24th, 2020 by Olu No Comments

Hi folks,

In this post I’ll sharemy recent experience with importing a large SQLite table. By large, I mean a table with very many rows.

Usually I use DB Browser for SQLite to manage the database.

So at first I thought, maybe I should use the export and import functionality from DB Browser for SQLite.

I exported to an SQL file, made my changes to the dump, and then tried importing that dump to a new database using DB Browser for SQLite.

Lo and behold, it was super duper slow. On realizing it would take forever, I decided there must be a faster way.

More research showed that there’s an SQLite command-line tool that can carry out imports. I learnt that it may be a good idea to export to CSV format, then import it using the SQLite command-line tool. To export to csv, use the following command:

 

sqlite3 -header -csv your_db.db "select * from your_table;" > your_csv_file.csv

 

To import a csv file use the following commands:

 

sqlite3 your_db_file.db
.mode csv
.import /path/to/your/csv_file.csv your_table_name

 

So I tried it. Boy, was it much faster.

So, the takeaway is that if you must export and later import large SQLite table, first export the database to a CSV file, and use the SQLite command-line utility to import the file(s) into a new database.

That’s all for now. Happy software development.

How to create table literals in Oracle

Posted on: October 9th, 2020 by Olu No Comments

Hi folks,

In this post I talk about how to create literal tables in Oracle.

You may have a list of values and want to run an Oracle query against those values.

A convenient way to do this is to create a literal table in an Oracle SQL using those values.

 

Single-column

If you want to create a single-column literal table,

you can use either dbms_debug_vc2coll or sys.ODCIVarchar2List

e.g.

select * from table (sys.ODCIVarchar2List('AAA', 'BBB', 'CCC'));

This will return an inline table with a single column named COLUMN_VALUE and rows with values AAA, BBB and CCC.

i.e.

COLUMN_VALUE
==
AAA
BBB
CCC

Second example

select column_value from table(sys.dbms_debug_vc2coll(‘AAA’, ‘BBB’, ‘CCC’))

 

Multiple colums

If you want to generate a literal table with two columns, you can use a query like:

select objectschema m, objectname n
from
table(sys.ODCIObjectList(
sys.odciobject('APPLE', 'FRUIT'),
sys.odciobject('CARROT', 'VEGGIE')
));

For more tahan 2 columns, you can use a query like:

create type t as object (a varchar2(10), b varchar2(10), c number);
create type tt as table of t;

select * from table( tt (
    t('APPLE', 'FRUIT', 1),
    t('APPLE', 'FRUIT', 1122), 
    t('CARROT', 'VEGGIE', 3),
    t('PEACH', 'FRUIT', 104),
    t('CUCUMBER', 'VEGGIE', 5),
    t('ORANGE', 'FRUIT', 6) ) )

That’s all for now. Happy database querying.

 

Sources

Oracle SQL Tip: Using dbms_debug_vc2coll and sys.ODCIVarchar2List. https://training.fusionapplied.com/2017/08/23/oracle-sql-tip-using-dbms_debug_vc2coll-and-sys-odcivarchar2list/

Is there a non-ugly way to use a multi-column, multi-row table literal in an Oracle 11g query? https://stackoverflow.com/questions/29903397/is-there-a-non-ugly-way-to-use-a-multi-column-multi-row-table-literal-in-an-ora/29904128

How to dump stored MySQL routines

Posted on: June 12th, 2020 by Olu No Comments

Hi folks,

In this post I will quickly share how to make a MySQL dump of stored routines (procedures and functions). Recently, I was looking to back up databases for a MySQL server which I maintain. One of the databases contained a stored function. So, I thought, heh, let’s just use the good old mysqldump the way I do it with tables. Well, it didn’t work. After a bit of Googling and searching MySQL documentation, I eventually found the answer. All you have to do is add the routines flag when invoking mysqldump.

E.g.

 

mysqldump --routines -u youruser -p yourdb > yourdb.sql

 

That’s all for now.