Last active
April 5, 2023 01:47
-
-
Save craftsmanship/37feb202ba32a1ae0d693060d3089288 to your computer and use it in GitHub Desktop.
Test data (Insert statement) generator for RDBMS. Depends on my another gist https://gist.github.com/craftsmanship/eb49e6342104dbc3e7bb5fced5412669.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(in-package :cl-user) | |
(ql:quickload :iterate) | |
(ql:quickload :cl-ppcre) | |
;;; https://gist.github.com/craftsmanship/eb49e6342104dbc3e7bb5fced5412669 | |
(load "mssql-client.lisp") | |
(defpackage generate-test-data-sql | |
#+ccl (:use :cl :ccl) | |
#+sbcl (:use :cl) | |
(:import-from :msclient | |
:execute) | |
(:import-from :iterate | |
:iter | |
:for | |
:collect) | |
(:import-from :ppcre | |
:register-groups-bind) | |
(:export :generate)) | |
(in-package :generate-test-data-sql) | |
(defun val (name record) | |
(cdr (assoc name record :test #'string=))) | |
(defun generate (select-statement pkeys increment-key count &key db user password host) | |
"テストデータの INSERT ステートメントを生成する. | |
select-statement: テンプレートにするレコードを SELECT する SQL. | |
全てのカラムが、INSERT ステートメントに含まれるべき値で SELECT される必要がある. | |
(Date 型のカラム等は要注意) | |
pkeys: P キーカラム名のリスト. | |
incremant-key: インクリメントする P キーカラム名. | |
P キー、またはその一部であり、数値型である必要がある. | |
(非数値カラムについてはそのうち対応するかも) | |
count: 作成するレコード数. | |
db: データベース名 | |
user: ユーザ名 | |
password: パスワード | |
host: DB ホスト" | |
(let* ((rec (caadr (assoc :records (execute select-statement | |
:db db | |
:user user | |
:password password | |
:host host)))) | |
(table (register-groups-bind (table-name) | |
("(?i)\\s+from\\s+(\\w+)\\s*" select-statement) | |
table-name)) | |
(key-vals (mapcar #'(lambda (key) | |
(list key (val key rec))) | |
pkeys)) | |
(select-max (format nil "SELECT MAX(~a) | |
FROM ~a | |
~@[WHERE ~{~{~a~^=~}~^ | |
AND ~}~]" | |
increment-key | |
table | |
(remove-if #'(lambda (key-val) | |
(string= increment-key (car key-val))) | |
key-vals))) | |
(max-id (progn | |
(format t "select-max=~a~%" select-max) | |
(cdr (caaadr (assoc :records | |
(execute select-max | |
:db db | |
:user user | |
:password password | |
:host host))))))) | |
(format t "max-id=~a~%" max-id) | |
(iter (for i from (1+ max-id) to (+ max-id count)) | |
(collect (format nil "INSERT INTO ~a VALUES(~{~a~^,~});" | |
table | |
(mapcar #'(lambda (key-val) | |
(cond ((string= increment-key (car key-val)) | |
i) | |
((stringp (cdr key-val)) | |
(format nil "'~a'" (cdr key-val))) | |
((null (cdr key-val)) | |
"NULL") | |
(t | |
(cdr key-val)))) | |
rec)))))) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment