2

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?

vlad
  • 771
  • 2
  • 10
  • 21
  • 2
    I have no idea what this "build system is, but: you can pass the database to which you want to connect in `psql` with the `-d` parameter. You can also use `\connect` inside `psql` to connect to a different database. –  Sep 17 '14 at 11:23
  • I wanted to avoid use of `-d` switch as build system is build template. However I didn't know about `\connect` which seems equivalent to `USE` in MS SQL and that solves the problem, so both systems work. Thanks! – vlad Sep 17 '14 at 11:32
  • `\connect` is quiet different from `use` because you create a completely new connection. You don't just "change" the current one. Postgres does not have the concept of "switching databases". Each database is separated and you need different physical connections to work with different databases. –  Sep 17 '14 at 11:36
  • All right, thanks for the explanaion. – vlad Sep 17 '14 at 11:51

2 Answers2

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!

MattDMo
  • 100,794
  • 21
  • 241
  • 231
  • OK, thanks for your answer, but I'll pass. Selecting database from script file as suggested in comments is the way it suits me most - simple and nonconforming. I searched for possibilities to pass variable parameter to ST's build system, but it seems that's a strange limitation - you can't pass variable parameter (regular script argument) to ST's build command. – vlad Sep 17 '14 at 18:56
  • @klo that's fine, hopefully this can help somebody in the future. So there **is** a way to select the DB from the script? Could you point me towards some directions for doing it, or outline the steps in a comment so I can add them to my answer? – MattDMo Sep 17 '14 at 20:54
  • sure - user @a_horse_with_no_name pointed that I can use `\connect` [meta-command](http://www.postgresql.org/docs/9.2/static/app-psql.html#APP-PSQL-META-COMMANDS) and select single database without using `-d` switch. So at a first line in your script you can type `\connect my_db` and do the rest, same as you can use any other meta-commands, like single `\l` or `\t`, etc. – vlad Sep 17 '14 at 23:54
0

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.

ingo
  • 117
  • 10