The schema xml file (<module>/schemas/<schema-name>.xml) is metadata about the database tables for things like custom labels, URLs, buttons, and lookups. It can be created for you automatically, but you will most likely need to customize the xml to add labels and buttons. As you write sql upgrade scripts, you'll need to manually keep the schema xml file in sync with the changes you make to the underlying database tables.

So when I try to generate this xml schema file I get a file with just one line:

<ns:tables xmlns:ns="http://labkey.org/data/xml"/>


and under <labkey>/server/customModules/<web part name>/src/schmeas/dbscripts/postgresql I have the file named: <schema name>-0.00-0.01.sql

The version of my web part as specified in the config file is 0.01

Here is an example of what the sql file contains:

CREATE SCHEMA QUALIFIER;

CREATE TABLE QUALIFIER.qaTaskList
(
    qaId SERIAL NOT NULL,
    qaName VARCHAR(45) NOT NULL,
    description VARCHAR(45) NOT NULL,
    qaLevel VARCHAR(45) NOT NULL,
    pop VARCHAR(45) NOT NULL,
    formula VARCHAR(45) NOT NULL,
    plotType VARCHAR(45) NOT NULL,

    CONSTRAINT PK_qaTaskList PRIMARY KEY (qaId)
);

So if I understand correctly, the schema description generator does not pick up the sql file and so I do not see anything in the schema browser pertaining to my new schema.

Please, advise, how do I create some custom tables?

Thanks.
-Leo

Hi Leo,

It sounds like the server likely ran your script when it only contained "CREATE SCHEMA QUALIFIER". Once the server has run your script, it won't rerun it, even if you make edits. You can verify this by checking the schema directly in a tool like PGAdmin to see if it has any tables.

If it doesn't, I'd recommend:

1. Move the CREATE TABLE statement into a new script file, with that's called <schema name>-0.01-0.02.
2. Bump your module version to 0.02. Depending on how your module is set up, this number will either be in the Module class or its module.properties file.
3. Build and restart your Tomcat instance.

The server should say that it needs to upgrade the module, and then run your script. If you revisit the link, you should get XML that includes info about your table.

Be sure to review this page, which gives details on how the server decides what scripts need to be run as part of an upgrade:

https://www.labkey.org/wiki/home/Documentation/page.view?name=schemaUpdates

Thanks,
Josh

Hi Josh, thanks for your reply.

So I actually first just had the sql script named <schema name>.sql, so I was assuming it was not being run, since it does not conform to the naming standards.

Then, I renamed it to the above, and I think at the time the script was missing the "create schema" statement, so it just had the "create tables" part :) So the server must have run that script (though, I did not rebuild the server), but it did not produce any errors. Or may be it did not run it, since I did not rebuild it all.

Anyways, after that I posted my question, then I tried following your suggestions.

When connecting the actual db with DBVisualizer, I do not see a table named <my schema name> there. Though, in the schema browser I see such a schema as "Samples", but I do not see a table for it in the db, when exploring it with DBVisualizer.

I updated the module version to 0.02 and created another script <schema name>-0.01-0.02: its header looks like this:

DROP SCHEMA IF EXISTS QUALIFIER CASCADE;
CREATE SCHEMA QUALIFIER;

DROP TABLE IF EXISTS QUALIFIER.qaTaskList;
CREATE TABLE QUALIFIER.qaTaskList
(
    qaId SERIAL NOT NULL,
    qaName TEXT NOT NULL,
    description TEXT NOT NULL,
    qaLevel TEXT NOT NULL,
    pop TEXT NOT NULL,
    formula TEXT NOT NULL,
    plotType TEXT NOT NULL,

    CONSTRAINT PK_qaTaskList PRIMARY KEY (qaId)
);

I also rebuild the web part and rebuilt the server and it seemed to have run the "upgrade script", since it said at the log in page that only admins can log in, since there is an upgrade going on. When I logged in, it did display the message that web parts were being upgraded and it actually first threw a 500 error. I think my sql file was incorrect (I was referencing something before it was defined), I corrected that and then the upgrade script went through. Now my web part's version in the admin console is stated as 0.02.

But when I try to generate the corresponding xml, I still get that same 50 bytes long bare and short file.

And another question is: the XML file generator is indifferent to where the schema is sitting, right? Because the schema browser for the entire server is different from the schema browser for a specific project...

I also tried to create the XML file with the corresponding columns and it shows up in the schema browser, but if and when I try to examine the individual tables by clicking on the tables links, I get an error message is red: "Error in query: Communication failure." So the XML file is "not seeing" the actual tables - I am not even sure if they get/got created properly or not.

When connecting with DBVisualizer I still do not see a table there, named <my schema name>

What am I doing wrong and how do I fix this?

If you are in or around the Arnold building at the Hutch, feel free to stop by: B168 on the 2nd floor...

Thanks.
-Leo

Hello again, everyone!

Still no luck for me in setting up those custom tables we are in dire need of...

By the way, the first part of the sql file names does not have to be named as the web part, does it? May be that's my problem?
But after looking at that table above, that does not seem to be the case: multiple schema are associated with the same web part and even if it's just one schema, its name sometimes shows up different than the web part name...

I was advised by Kevin to examine the core.sqlscripts table and I do see that my file name QUALIFIER-0.01-0.02.sql got run on Friday and QUALIFIER-0.02-0.03.sql got run yesterday, both belonging to modulename FlowGraph.

I was also advised to delete the module and the associated schema of the module. There was no link for the schema (, which makes me think that the schema was not created after all), so I deleted just the module, (the 2 lines in the core.sqlscripts table mentioned above from yesterday and Friday are still present, so I'm not sure if they should have gotten removed or not, may be I'm not doing something right here); then I rebuilt the server and the module, restarted it, it indicated that it ran the transition script, and another line got added to core.sqlscripts

But again, the associated with the module generated XML is the basic one and I do not see the QUALIFIER schema neither in the DBVisualizer, nor from within Labkey itself...

Hi Leo,

I've discovered that the schema XML file name is case-sensitive. In addition, so is the 'dcSchema=' property on the URL for

http://localhost:8080/labkey/admin/getSchemaXmlDoc.view?dbSchema=<schemaName>

This is causing the server to not read the metadata file correctly resulting in errors when looking at specific tables.

My recommendation is to change both your sql upgrade script and your schema XML file to lowercase (e.g. qualifier.xml). This way both the action described above will work and the schema file will get picked up properly.

Thanks,
Nick

All right, case closed, thanks to Nick.

The schemaName parameter passed to the XML generating URL as well as the name for the schema's XML file (that gets generated) MUST be lower-case!