Creating Tables

qss Table Arguments

To create a table script (including triggers, constraints, indices, primary key sequences and stored procedures), you need to understand the command line arguments, qss offers.

qss db table [sp:crud] [tr:bi,bu,bd,ai,au,ad] [[fk|c]:name,type,n/nn[uU|xX],[default]] [j:table.column,...] [switches]

Wow, well that's complicated, isn't it?

Nope. It isn't. Let's take a closer look:

Set the DB to use

• You always start with qss followed by the db system (either mssql or mysql) followed by the table name, you want to create.

qss mssql Customer - will create the Customer table in MSSQL syntax.
qss mysql Customer - will do the same, but for mysql.

So far, so easy.

Set Stored Procedures and Triggers

• After that, you tell qss, which stored procedures and triggers you want to have. The details for each of those can be found in CRUD Stored Procedures and Triggers.

The quick info for this is:

• sp:* or sp:crud defines all CRUD Stored Procedures. Omit any letter to omit a procedure, like sp:cru will not create a (d)elete procedure
• tr:* creates all 6 possible triggers. Beware! This is a rare case! In most cases, you only want insert and update triggers, which will be created by default anyways to keep your table manipulation-safe. More on triggers at the page linked above.

Define Data Columns

• Next is your turn: Which columns shall the table have? You can SKIP all the foreign keys and the primary key! Just your "real" data columns.

To stick with the Customer example, for simplicity, let's say, you only need the name and a birthday and the latter shall be optional (nullable):

qss mssql Customer c:name,nvarchar(64),nn c:birthday,datetime,n

You define a data column with the c: command.
c:<name>,<dbtype>,<null>,<default>

• <name> is the name of the column
• <dbtype> is the datatype, exactly as your target db wants it (nvarchar in mssql, varchar in mysql), exactly as you would write it, if you code the CREATE TABLE statement by hand
• <null> contains informations about the field behaviour and indexing.
  • n - This field is nullable
  • nn - This field is NOT NULL
  • In addition, you may specify any index you want
  • x - Create a non-unique index, sorted ascending (start with smallest value, therefore lowercase x)
  • X - Create a non-unique index, sorted descending (start with highest value, therefore uppercase X)
  • u - Create a unique index, sorted ascending (start with smallest value, therefore lowercase u)
  • U - Create a unique index, sorted descending (start with highest value, therefore uppercase U)
  • Every combination is allowed, like nu or nx or nnU, nnX, nnx, ...
• Optionally you may even add a default value for the column as fourth element in the c: command
  • Put any value as default, exactly as the db wants it. Examples:
    • c:counter,int,nn,0
    • c:name,nvarchar(64),nn,'NewCustomer'
    • If you need a blank character in your default of a string column, don't forget, that you are working with a CLI tool and put the entire c: in quotes:
    • "c:name,nvarchar(64),nn,'Hello World'"

Define Foreign Keys

• After your data columns, you define the Foreign Keys, your table shall have:

qss mssql Customer c:name,nvarchar(64),nn c:birthday,datetime,n fk:address

You define a data column with the fk: command.

• Just supply the table name, where the key shall point to. Due to the Naming Conventions, qss knows exactly, how the key is named and where to put it in the CREATE statement. This is one of the points, where the naming scheme shines.
• It is thinkable, that you have some special requirements for the foreign key, like being nullable (optional) or having a specific default connection, so qss allows the same syntax for fk: commands as it does for c: commands:
  • qss mssql Customer c:name,nvarchar(64),nn c:birthday,datetime,n fk:address,bigint,n,1 is a valid foreign key definition of an optional foreign key (nullable), with default value 1

Recursive Tables

• Sometimes you need a table that references itself, to create a hierarchical structure (like the directory structure of your hard disk)

This is also done through the fk: command, by adding a * to the column name, like this:

fk:parent*,bigint,n

For simplicity of this example, lets imagine you want to design something like the directory structure of your hard disk and the table is named FOLDER.
by writing parent* as column name of the FK, qss will generate this column as PARENTFOLDERID. It takes your prefix (PARENT), and adds the own PK of the table (FOLDERID) to it.

It's worth to note, that you are free, where you place the * character. Setting it to fk:*parent would result in FOLDERIDPARENT to be created. It's up to you.

This column will then be a foreign key pointing to itself, the PARENTFOLDERID will point to the FOLDER table.
In addition to that, qss will now generate a FolderGetRecursive stored procedure, which executes a recursive query to select the entire hierarchy starting at a specific point (a FOLDERPARENTID) which you supply as first argument to the procedure. Supply NULL as parent id to retrieve the entire structure.

Multiple Foreign Keys to the same Table

• There are situations, where you'd like to have multiple foreign keys pointing to the same table, like a SenderUserID and a ReceiverUserID, both pointing to the USER table.

Due to the strict naming convention, that a foreign key is named as the primary key of the table, it points to, we need a way to prefix a foreign key. This is done with a + character:

fk:sender+user fk:receiver+user will create the two foreign keys mentioned above.
The same syntax (sender+table) also works for joins, see the next chapter.

Adding Joins for Select Procedures

• Next step is to give qss orders of which tables to join in the Select Procedures, it generates. This is done by the j: (Join) command and explained in detail in CRUD Stored Procedures.

The sort form is:

• j:table.column, like j:folder.name will join the folder tables' name column in all queries of tables that have a foreign key to the FOLDER table.

The j: command also supports the multiple key system from the fk: command.
j:sender+user.name will join the name column from the USER table, where the SENDERUSERID points to.

Additional Switches

• Finally, the switches. There are currently only two switches, which can speed up object creation, depending on your personal work style.

Those are purely optional.

• -clip will not only generate the .sql file but will also push the entire generated text to the clipboard, so can directly to a Ctrl-V in your database studio, if you have it already open and want to create the objects on-the-fly
• -clipx is the way, to have the script only in the clipboard, without any file being generated.

That's it.

I hope, with these explanations, the command line is a bit less spooky.
I recommend, that you just play around a bit with it, you will see, that you get the grip within a few minutes. As soon as you have understood the command line, qss will save you hours in data modelling!