How to make build system for PostgreSQL

Making build system for MS SQL Server is easy:

{
    "cmd": ["sqlcmd", "-S", ".", "-i", "$file"],
    "selector": "source.sql",
    "shell": true
}

and for PostgreSQL I tried this:

{
    "env": {"PGPASSWORD": "password"},
    "cmd": ["C:/PostgreSQL/9.3/bin/psql.exe", "-U", "postgres", "-f", "$file"],
    "selector": "source.postgresql",
    "shell": true
}

But, while with MS SQL I can reference database from the script with USE keyword, there is no such feature in Postgre so above build can't work, and it seems to me I'll must hardcode each database to build system which is non-sensical in this case.

Any ideas how to make this work - run sql script from Sublime Text on arbitrary PostgreSQL database?

回答1:

The contents of the build system's "cmd" array need to be what you would type on the command line to run that command - DBs are no exception. I don't know anything about Postgres internals, but if you need to specify the DB's name on the command line, then you'll have to do it in the build system.

However, there is an alternative to having potentially dozens of build systems lying around - defining the build system in a .sublime-project file. According to the documentation, the .sublime-project file can have three base groups: "folders", "settings", and "build_systems". To create a project, open the folder(s) you want to include in the sidebar, then click on Project -> Save Project As.... Put in its name and save it in a logical place. Then, select Project -> Edit Project, which will open up the .sublime-project file with JSON syntax. It should look something like this:

{
    "folders":
    [
        {
            "follow_symlinks": true,
            "path": "C:\\Users\\MattDMo\\Development\\DB\\my_postgres_db1"
            // by default, Sublime uses double-backslashes :(
        }
    ],
}

Add a "build_systems": identifier after the closing square bracket from "folders", and put in your build system:

{
    "folders":
    [
        {
            "follow_symlinks": true,
            "path": "C:\\Users\\MattDMo\\Development\\DB\\my_postgres_db1"
        }
    ],
    "build_systems":
    [
        {
            "name": "my_postgres_db1",
            "cmd": ["C:/PostgreSQL/9.3/bin/psql.exe", "-W", "-U", "postgres", "-d", "my_postgres_db1", "-f", "$file"],
            // you can use either double backslashes or forward slashes on Windows. Forward is better :)
            "selector": "source.postgresql",
            "shell": true
        }
    ]
}

and you should be all set. Now that you have your template, you can make as many copies of the file as you want, customizing the DB name and build system name as needed. The "name" field will show up in the Tools -> Build System menu, and you can either select it outright, or use Automatic if you don't have any other build systems with "selector": "source.postgresql" in them.

It does involve a little bit of work for each DB that you have, and you'll have to remember to switch the active project when switching DBs (I just keep one window open for each project I have active), but other than that it should solve your problem. You'll note that I removed the "env": {"PGPASSWORD": "password"}, line (it should have been in square brackets, anyways) and instead added the -W command-line option to psql.exe to prompt for the password. There's no way of encrypting .sublime-project files, so anybody that can read them will see your server's password. I don't know if using "shell": true imports your shell's environment variables or not, I suspect it doesn't, but you'll have to test. It'll be one thing to enter when running the build system, but it will make your overall system more secure. Of course, feel free to revert if you don't want/need the extra security, but if it's a public-facing server I wouldn't.

Good luck!

回答2:

A work around:

{"cmd": ["psql", "-d", "$file_base_name", "-U", "username", "-f", "$file"]}

give the file you use in Sublime to interact with psql the same name as your database.