rss_feed

Auto generated update and rollback database scripts with magit and emacs

homeHome
pagesemacs
pagessql
pagesgit

This code loops over all .sql .pks and .pkb files and merges them into the current buffer, it currently need to be run from the projects root.

Magit has some useful methods we can utilise to get a list of staged files, from there we can join the results into the current buffer. We can also use magit functions to get the same list before modification to generate a rollback.

Get previous version of staged files and generate a rollback script, we can use magit-find-file-noselect to get an older version.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
(defun generate-oracle-rollback-script () 
  (interactive) 
  (insert "SET DEFINE OFF;\n\n")
  (loop for i in (cddr (magit-staged-files)) 
        if (string-match "\\(\.sql\\|\.pkb\\|\.pks\\)" i)
        collect (insert (concat "-- START AUTO GENERATED " i "\n" (with-current-buffer 
                                                                            (magit-find-file-noselect "HEAD" 
                                                                            (expand-file-name i)) (buffer-string))
                                                                            )
                                "\n-- END AUTO GENERATED " i "\n\n"))
        )

Get all staged files and generate an update script, we can get a list of staged files with magit-staged-files and insert into the current buffer with insert-file-contents .

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
(defun generate-oracle-update-script () 
  (interactive) 
  (insert "SET DEFINE OFF;\n\n")
  (loop for i in (cddr (magit-staged-files)) 
        if (string-match "\\(\.sql\\|\.pkb\\|\.pks\\)" i)
        collect (insert (concat "-- START AUTO GENERATED " i "\n" (with-temp-buffer
                                                                            (insert-file-contents
                                                                            (expand-file-name i))
                                                                            (buffer-string))
                                "\n-- END AUTO GENERATED " i "\n\n"))
        )
)

Future enhancement would be to detect the project root from any buffer with in the project you have open.