• Sign In
  1. Messages

SQL script not being executed for file based module

LabKey Support Forum (Inactive)

View Message

view list print
SQL script not being executed for file based module Will Holtz  2014-06-25 09:45
Status: Closed
 
I have a fairly minimal file based module that consists of three files:

will@lims:/usr/local/labkey-dev/externalModules/Fermentation$ cat module.properties
Name: Fermentation
ModuleClass: org.labkey.api.module.SimpleModule
RequiredServerVersion: 14.10
Version: 1.00
SupportedDatabases: pgsql
will@lims:/usr/local/labkey-dev/externalModules/Fermentation$ cat resources/schemas/fermentation.xml
<?xml version="1.0" encoding="UTF-8"?>
<ns:tables xmlns:ns="http://labkey.org/data/xml">

  <ns:table tableName="Antifoam" tableDbType="TABLE">
    <ns:description>Antifoams used in fermentations.</ns:description>
    <ns:pkColumnName>Key</ns:pkColumnName>
    <ns:titleColumn>Name</ns:titleColumn>
    <ns:columns>
      <ns:column columnName="Key">
        <ns:columnTitle>Antifoam Id</ns:columnTitle>
        <ns:datatype>int</ns:datatype>
        <ns:isAutoInc>true</ns:isAutoInc>
        <ns:scale>10</ns:scale>
        <ns:nullable>false</ns:nullable>
        <ns:isReadOnly>true</ns:isReadOnly>
        <ns:isKeyField>true</ns:isKeyField>
        <ns:isHidden>true</ns:isHidden>
      </ns:column>
      <ns:column columnName="Name">
        <ns:datatype>varchar</ns:datatype>
        <ns:scale>100</ns:scale>
      </ns:column>
    </ns:columns>
  </ns:table>
</ns:tables>
will@lims:/usr/local/labkey-dev/externalModules/Fermentation$ cat resources/schemas/dbscripts/postgresql/fermentation-0.00-1.00.sql
CREATE SCHEMA fermentation;
CREATE TABLE fermentation.Antifoam (
    Key SERIAL,
    "Name" VARCHAR(100) NOT NULL,

    CONSTRAINT PK_Antifoam primary key (Key)
);
will@lims:/usr/local/labkey-dev/externalModules/Fermentation$

When the module is enabled for a folder, I am able to see the fermentation schema in the schema browser, but the table does not exist. And in the Admin Console, I see it listed with the modules (although it shows up as version 0.00). However, when I go to the Admin Console->Module Details page, it does not show up there. If I go to Admin Console->SQL Scripts, fermentation-0.00-1.00.sql is listed under 'Scripts that have not run on this server - All'.

Any ideas where I'm messing this up?

thanks,
-Will
 
 
jeckels responded:  2014-06-25 10:47
Hi Will,

You may have added your SQL script after your server had already upgraded your module to version 1.0. The server keeps track of the last version of the module that was deployed, and doesn't run scripts that are added after that point.

Try renaming your script to fermentation-1.00-1.01.sql, and bumping the Version in module.properties to 1.01. Then build (if you're doing that as a deployment step) and start Tomcat. The server should automatically notice that it needs to upgrade the module, find your script, and run it as part of the startup process.

Thanks,
Josh
 
Will Holtz responded:  2014-06-25 11:12
Hi Josh,

Thanks for the help. I tried your approach and unfortunately nothing changed. My module and sql script are not showing up in the core tables, so I don't think it is a version number problem:
labkeydev=> SELECT * FROM core.sqlscripts WHERE filename LIKE 'fermentation%';
 _ts | createdby | created | modifiedby | modified | modulename | filename
-----+-----------+---------+------------+----------+------------+----------
(0 rows)

labkeydev=> SELECT * FROM core.modules WHERE name LIKE 'Fermentation';
 name | classname | installedversion | enabled | autouninstall | schemas
------+-----------+------------------+---------+---------------+---------
(0 rows)

Any other ideas would be greatly appreciated!

-Will
 
adam responded:  2014-06-25 20:07
Will,

You're very close. The server actually loads the key properties (module version, supportedDatabases, etc.) from module.xml, not module.properties. The standard build process will translate module.properties into module.xml (via substitution into module.template.xml), but you aren't building this module (and you don't need to)... just copy content like the below to /resources/config/module.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:util="http://www.springframework.org/schema/util"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">
    <bean id="moduleBean" class="org.labkey.api.module.SimpleModule">
        <property name="name" value="Fermentation"/>
        <property name="version" value="1.0"/>
        <property name="requiredServerVersion" value="14.10"/>
        <property name="supportedDatabases" value="pgsql"/>
    </bean>
</beans>

Your other files are perfect... I copied my module.xml plus your fermentation.xml & fermentation-0.00-1.00.sql to the appropriate locations in my externalModules directory and the module correctly created the new schema.

Adam
 
Will Holtz responded:  2014-06-25 22:59
Hi Adam,

Thanks for the correction. I don't think I ever would have figured that detail out from the docs. Unfortunately that change didn't seem to impact my system. The fermentation schema is still not being created, my module still shows up as version 0.00, my module is not in the core.modules table nor is my sql script in core.sqlscripts.

Any other thoughts? Thanks much!

-Will
 
jeckels responded:  2014-06-27 17:02
Hi Will,

How are you building your module? Can you run "jar tf fermentation.module" from the command line and paste its output into a reply? That will give us the full set of files and their layout.

Thanks,
Josh
 
Will Holtz responded:  2014-06-27 17:49
Hi Josh,

My build process is just to zip up the module directory. Generally, during my development cycle I haven't even been doing that. I just edit the module directory in place and I manually tell tomcat to reload my labkey instance when needed. I have attached the module file.

will@lims:/usr/local/labkey-dev/externalModules/Fermentation$ zip -r ../Fermentation.module *
  adding: resources/ (stored 0%)
  adding: resources/config/ (stored 0%)
  adding: resources/config/module.xml (deflated 56%)
  adding: resources/schemas/ (stored 0%)
  adding: resources/schemas/dbscripts/ (stored 0%)
  adding: resources/schemas/dbscripts/postgresql/ (stored 0%)
  adding: resources/schemas/dbscripts/postgresql/fermentation-0.00-1.00.sql (deflated 18%)
  adding: resources/schemas/fermentation.xml (deflated 61%)
will@lims:/usr/local/labkey-dev/externalModules/Fermentation$ cd ..
will@lims:/usr/local/labkey-dev/externalModules$ jar tf Fermentation.module
resources/
resources/config/
resources/config/module.xml
resources/schemas/
resources/schemas/dbscripts/
resources/schemas/dbscripts/postgresql/
resources/schemas/dbscripts/postgresql/fermentation-0.00-1.00.sql
resources/schemas/fermentation.xml
will@lims:/usr/local/labkey-dev/externalModules$
 
tgaluhn responded:  2014-07-02 18:04
Hi Will-

Apologies for the continued confusion. I believe Adam misspoke above. The config directory (with module.xml) goes at the top level of your module, next to the resources directory.

I was able to reproduce the problem you're seeing with the Fermentation.module file you provided, but moving the config folder to the correct location made it work.

I've made note for us to improve the documentation around this.

Let us know if have any further questions.

-Tony