How to read and execute an SQL file stored inside workspace?

Tagged: sql file read

This topic has 4 replies, 4 voices, and was last updated 8 years, 5 months ago by vi.

Viewing 4 reply threads

Author

Posts

July 7, 2016 at 2:26 am #6995

Tommy Butt
Participant

We have a stored procedure script that gets run to refresh a user’s data access control when their user type has changed. The source of this stored proc is stored in the workspace under <project>\etc\dist\sql\…

To unit test the stored proc, the stored proc definition is replicated inside a library function, which is called by default.utest so that the stored proc is dropped and recreated before each unit test run. This requires any changes to the stored proc to be replicated in both the sql file and the library function. Sometimes developers forget to replicate these changes.

Question: Is it possible to have the unit test to read an sql file inside the workspace and execute its content in order to avoid the duplication?

0

July 7, 2016 at 9:52 am #7027

Rob Duda

Participant

You could move the execution into the model and then it would run at startup no matter what. I’m not familiar with the /etc/dist/sql folder but I’m assuming that placing scripts here cause them to run at start-up or seed time? Anyway, you can add the PERSISTENCE_INITIALIZER aspect (to run only at seed time) or RUNTIME_INTITIALIZER (run at start-up) to any class and implement the required initializePersistence() or initializeRuntime() events respectfully. From there you just need to get the script and use “sql-execute” to execute it.

If the script isn’t very long I would suggest just creating a string in scheame but reading from the file system is easy, if you place the file somewhere on the classpath you can even use the classloader to get at it. I’m not sure if we modified the ant scripts at all but our studio project has a src folder and everything in it is compiled and relocated to the bin folder of the project, this in turn is packaged with the EAR for deployment. This way any property files/resources will always be visible to the classloader no matter where the code is run.

Here’s a snippet of code that can get a resource from the classloader:

   (resourcePath (string-append ((resourceDirectory'toURL)'path) resourceName))
   (resource (java.io.File'new resourcePath))
   (errorPathSuffix (if (resource'exists) resourcePath resourceName))
 )
  (try
     (let
        (
           (inputStream
              (if (resource'exists)
                 (java.io.FileInputStream'new resource)
                  ((nexj.core.runtime.InvocationContext'classLoader)'getResourceAsStream
                     resourceName
                   )
               )
            )
         )

(resourcePath (string-append ((resourceDirectory'toURL)'path) resourceName))

(resource (java.io.File'new resourcePath))

(errorPathSuffix (if (resource'exists) resourcePath resourceName))

)

(try

(let

(

(inputStream

(if (resource'exists)

(java.io.FileInputStream'new resource)

((nexj.core.runtime.InvocationContext'classLoader)'getResourceAsStream

resourceName

)

resourceDirectory is a java.io.File which you can obtain as: (java.io.File’new ‘path to dir’)

Don’t forget your import:

(import
   '(
      java.io.File
      java.io.FileInputStream)
)

(import

java.io.File

java.io.FileInputStream)

)

I’ve attached a screen snippet of the project path in Navigator incase it helps.

Attachments:

You must be logged in to view attached files.

July 14, 2016 at 12:50 am #7071

Tommy Butt
Participant

Thanks Rob for the response. We will investigate if your suggested method is workable in our implementation.

0

July 14, 2016 at 1:06 pm #7087

Peter Murray

Participant

For stored procedures, we recommend using the .datasource metadata to manage them using datasource schema objects. These support arbitrary create/drop scripts that will be automatically executed by recreate calls and included in the generated create/drop sql scripts. There are examples in the DefaultRelationalDatasource. You can use the Main.upgrade to include changes automatically in the upgrade scripts if desired. They also support prerequisites (somewhat hidden – use the outline view) to ensure they are created in dependency order (otherwise they will be created in alphabetical order).

If, for some reason, the datasource schema objects are not an option, one can add files to the “meta” folder to have them included in the deployment. Everything under this folder will be packaged into the .ear. (Do not re-use an existing metadata extension such as .scm or .meta as all files with a matching metadata extension will be loaded regardless of the directory.) Once it’s in the meta directory, you can find its location using metadata loading helper functions. Also, there is no need to resort to the raw java file APIs as the scheme I/O functions such as “call-with-input-file” are preferred. For example, if you put a file “test.sql” under a folder called “custom” under the “meta” directory, you can access it this way:

(let ((filePath ((((((invocation-context)'metadata)'helper)'getResource "custom/test.sql")'url)'toString)))
   (call-with-input-file filePath
      (lambda (in)
         (let loop ((line (in'readLine)))
            (unless (null? line)
               (logger'info "line:" line)
               (loop (in'readLine))
            )
         )
      )
   )
)

(let ((filePath ((((((invocation-context)'metadata)'helper)'getResource "custom/test.sql")'url)'toString)))

(call-with-input-file filePath

(lambda (in)

(let loop ((line (in'readLine)))

(unless (null? line)

(logger'info "line:" line)

(loop (in'readLine))

)

July 14, 2016 at 1:46 pm #7097

vi
Participant

Here is how to read the whole stream with pure Scheme API:
(define (read-string-all in)
(do ((ch (read-char in) (read-char in)) (out (open-output-string)))
((eof-object? ch) (get-string-all out)) (write-char ch out)))

(call-with-input-file url read-string-all)

0

Author

Posts

Viewing 4 reply threads

You must be logged in to reply to this topic.