Documentation
The SQL Query Generator README file
usage:
$sqg {OPERATION} [OPTIONS] [CREATE TABLE FILE] [OUTPUT FILE]
-------------------------------------------------
Operation mode
-i, --insert-query Make an INSERT INTO query
-d, --delete-query Make a DELETE query
-u, --update-query Make an UPDATE query
-------------------------------------------------
OPTIONS
-a, --append-file append the output file
-p, --print-stdout print output to stdout
-v, --verbose-mode verbose mode
-g, --gpl show gpl terms and conditions
[CREATE TABLE FILE] is the file that contains the CREATE TABLE query(or queries) in plain text.
[OUTPUT FILE] is the file where the SQL queries will be stored, this is optional if you are using the -p option.
if file name(s) are not preceded by full path(i.e. /home/username/somefolder/), then current directory is assumed
--------------------------------------------------
SQL Datatypes supported by sqg:
CHAR
VARCHAR
TEXT
BIT
DECIMAL
SMALLINT
TINYINT
INT
REAL
FLOAT
DATE
TIME
TIMESTAMP
DATETIME
(see note 5 about other datatypes)
----------------------
NOTES:
1) the print-stdout option
2) operation modes and options
3) valid WHERE operators
4) defaults when verbose mode is OFF
5) unsupported datatypes
6) previously used values
7) tips and hints
1) If you specify an output file when using the --print-stdout
option then your queries will be stored to that file. If you only want
your queries to be displayed on the screen without storing them to a
text file, then simply do not specify an output file when using --print-stdout.
--------------------------------------------------
2) The options and operation mode may be placed in any order. For example,
if you wanted to append the output file and print the output to stdout for
an UPDATE query, you could do so with any of the following:
$sqg -uap createtablefile.txt output.txt
$sqg --print-stdout --update-query -a createtablefile.txt output.txt
$sqg --append-file -pu createtablefile.txt output.txt
$sqg -ua --print-sdtout createtablefile.txt output.txt
There are other possibilities, but that should give you the general idea.
Only one operation mode can be used at a time. If you enter multiple operation
modes only the last one(furthest right) will be used. All options will be used
even if they are placed before two operation modes: example:
%sqg -pv --insert-query -u createtablefile.txt
will make an UPDATE query. the "--insert-query" will be ignored, however the
two options -pv (print-stdout and verbose) *will* be used for that UPDATE query.
----------------------------------------------------
3) for the --verbose-where option, the valid "where" operators are
=, <>, !=, <, <=, !<, >, >=, !>, BETWEEN, LIKE, IS NULL
-------------------------------------------------
4) When verbose mode is *OFF* these are the defaults
for all datatypes:
If no input is given, column can contain a NULL value, and no DEFAULT value is specified
in the create table query, then NULL is used.
If no input is given and column has a DEFAULT value specified then column name is omitted
from INSERT INTO string and dbms will handle it. For UPDATE queries the word "DEFAULT" (without quotes)
is used.
for string datatypes(CHAR, TEXT, etc):
If no input given and column cannot contain a NULL value then an empty string is used.
for numerical datatypes(INT, FLOAT, etc):
If no input is given and column cannot contain a NULL value, 0 is used.
for DATE
If month is out of range:
high, it will be set to 12.
low, it will be set to 01
If day is out of range:
high, it will be set to highest day for month specified.
low, it will be set to 01
example: 2012-00-00 becomes 2012-01-01
2012-88-99 becomes 2012-12-31
for DATETIME
if date is not entered and column has a DEFAULT value specified, then column
will be omitted from INSERT queries
if date is entered correctly but time is not, then only the date will be used
for TIME
if hour greater than 23 it will be set to 23
if minutes and/or seconds are greater than 59 they/it will be set to 59
------------------------------------------------
5) Using verbose mode, any datatype not supported can be used, however no error checking will be done
nor will escaping backslashes will be added before \ or '. Your input will appear in the query *exactly*
as typed.
example: let's say the following line is what you want to enter for a string datatype that is not supported
don't forget the on\off switch
You would type
'don\'t forget the on\\off switch'
If you don't escape the apostrophe in the word "don't" then the query produced will most likely cause an error
when you try to run it on your dbms.
-----------------------------------------------
6) Remembering previously used values for INSERT queries
When using verbose mode to make INSERT queries you will have the option to have sqg remember values you
previously entered for use in the next query.
For primary keys that are numerical types, the number will be incremented and you will be asked if you would like to use that value.
For primary keys that are string types, if no number is on the end then a "1" will be placed at the end.
If you typed a value with leading zeros (ie. 'box003'), each '0' will be incremented to '1' when the digit to its immediate right
was previously '9' (ie. 'box009' becomes 'box010' and 'box099' becomes 'box100'). When there are no longer any leading zeros
to increment, another '0' is added to the end of the string(ie. 'box99' becomes 'box100').
After incrementing the number(or adding the first '1') you will be asked if you would like to use that value for the primary key.
--------------------------------------------------
7) Tips and hints
If you realized you made a mistake when entering data while making a INSERT or DELETE query, you can press 'R' when asked
if you are done making queries and that query wont be written to the output file(if one is being used).
When prompted to enter Y or N (yes/no), you can use 1 or 0 respectively.
When the program asks you a question and there are letters in braces (ie: Do you want to use [d]efault or [N]ull?)
the capital letter is the default option and you can use it just by pressing enter.