==============================================================================
INTERNET SQL 1.25  -  COMMAND AND OPTION REFERENCE
==============================================================================

Section 1 is generated from the engine's own help table (the commands
registered with AddHelp in help/help.cpp), so it lists what the binaries
really answer to. Sections 2-4 cover the vector-table commands, the
command-line options of every shell and server, and the configuration file.
Regenerate with:  python tests/gen_helpfile.py

SQL commands ......... 39
Vector commands ...... 2 (VINSERT, VECTOR) plus the 'storage' column type
Programs documented .. 7 (5 shells, 2 servers)

------------------------------------------------------------------------------
1. SQL COMMANDS
------------------------------------------------------------------------------

Type 'help <command>' at any shell prompt for the same text.

  add  alter  api  change  close  commit  connect  create  delete
  disconnect  download  drop  export  grant  help  import  include  insert
  list  lock  login  logout  object  open  pack  recover  reindex  revoke
  rollback  run  select  server  set  show  undelete  unlock  update
  upload  zap

..............................................................................
ADD
..............................................................................
  ADD
  1) Add user new user name , new user password, user privilege;
  User privileges are a , m, o for administrator, manager and ordinary users.
  2) Add group groupname,managername;
  It will create a new user group in the current database.
  3) Add host host name, hostip , host port;
  4) Add database databasename;
  5) Add background command commandname, minuteinterval, 'internetsql commands;';

..............................................................................
ALTER
..............................................................................
  1) ALTER TABLE table name ADD ( FIELD NAME DATA TYPE [key]|[primary|foreign key] [auto increment [initial value] ] [not null] [default '[=]value'] | [formula 'value'],KEY(fieldname) | FIELDNAME .....);
  2) ALTER TABLE table name DROP (FIELD NAME, .. FIELD NAME);
  3) ALTER TABLE table name MODIFY ( FIELD NAME NEW DATA TYPE,... FIELD NAME NEW DATA TYPE );
  4) ALTER TABLE table name RENAME ( FIELD NAME =NEW FIELD NAME ,... FIELD NAME = NEW FIELD NAME );

..............................................................................
API
..............................................................................
  KAMRANGA RDBMS ENGINE CORE API
  unsigned long ISQL_StartInternetSql(char *dbhomedir,char *serverip,char * serverport);
  long ISQL_StopInternetSql( unsigned long startid);
  int ISQL_ExecSql(long id, char *cmdstring);
  long ISQL_CurrentQueryRowCount(void);
  unsigned long ISQL_GetRunTimeQueryItems(void);
  long ISQL_LoadSqlScript(char *scriptfilename,
  char *logfilename ,
  int stopinerror,
  int stoplog,
  int stopdisplay,
  long displayfunc,
  long userfunc);
  void ISQL_EnableSingleStepSqlScript(void);
  void ISQL_DisableSingleStepSqlScript(void);
  void ISQL_StartSqlScript(void);
  void ISQL_StopSqlScript(void);
  void ISQL_QuitSqlScript(void);
  long ISQL_GetRunTimeQueryCount(void);
  long ISQL_GetRunTimeQueryRowLength(void);
  long ISQL_GetQueryItemsCount(unsigned long itemlist);
  char * ISQL_AllTrim(char *buf);
  char * ISQL_Ltrim(char *buf);
  char * ISQL_Rtrim(char *buf);
  unsigned long ISQL_GetQueryResultObject(void);
  long ISQL_GetQueryResultCount( unsigned long qry);
  long ISQL_GetQueryResultRowLength(unsigned long qry);
  unsigned long ISQL_GetQueryResultRow(unsigned long qry,long rowno);
  unsigned long ISQL_GetQueryResultItems(unsigned long qry);
  void ISQL_FreeQueryResultObject(unsigned long *);
  void ISQL_FreeCurrentQueryResultObject(void);
  char ISQL_CopyItemString(unsigned long qitemlist,char * fieldname,char *buffer);
  char ISQL_GetItem(unsigned long qitemlist,char *fieldname,char *buffer);
  char * ISQL_GetItemString(unsigned long qitemlist,char *fieldname);
  long ISQL_GetItemInt(unsigned long qitemlist,char *fieldname);
  double ISQL_GetItemDouble(unsigned long qitemlist,char *fieldname);
  char ISQL_CopyItemStringByFieldNo(unsigned long qitemlist,int fieldno,char *buffer);
  char ISQL_GetItemByFieldNo(unsigned long qitemlist,int fieldno,char *buffer);
  char * ISQL_GetItemStringByFieldNo(unsigned long qitemlist,int fieldno);
  long ISQL_GetItemIntByFieldNo(unsigned long qitemlist,int fieldno);
  double ISQL_GetItemDoubleByFieldNo(unsigned long qitemlist,int fieldno);
  char * ISQL_GetItemNameByFieldNo(unsigned long rec,int itemno);
  char ISQL_GetItemTypeByFieldNo(unsigned long rec,int itemno);
  long ISQL_GetItemLengthByFieldNo(unsigned long rec,int itemno);
  long ISQL_GetItemDecPosByFieldNo(unsigned long rec,int itemno);
  char ISQL_GetItemType(unsigned long rec,char *fieldname);
  long ISQL_GetItemLength(unsigned long rec,char *fieldname);
  long ISQL_GetItemDecPos(unsigned long rec,char *fieldname);
  long ISQL_GetItemNo(unsigned long rec,char *fieldname);
  void ISQL_InstallQueryFunction(long queryfunction );
  int ISQL_ThisYear(void);
  int ISQL_ThisDay(void);
  int ISQL_ThisWeek(void);
  int ISQL_ThisCentury(void);
  int ISQL_ThisMonth(void);
  char * ISQL_GetSystemDateUS(char *string);
  char * ISQL_GetSystemDateEnglish(char *string);
  int ISQL_EscapeSingleQuote(char *in,char *out);
  void ISQL_NoNetworkMode(void);
  void ISQL_NetworkMode(void);
  void ISQL_DisableMessage(void);
  void ISQL_EnableMessage(void);
  int ISQL_InitNetwork(void);
  void ISQL_SetServerIP(long ipaddr);
  void ISQL_SetServerPort(int port);
  long ISQL_GetServerIP(void);
  long ISQL_GetServerPort(void);
  char * ISQL_GetHomeDir(void);
  int ISQL_SetHomeDir(char *buf);
  unsigned long ISQL_GetQueryRow(unsigned long qry,long rowno);
  unsigned long ISQL_GetQueryItems(unsigned long qry);
  unsigned long ISQL_GetCursorRow(unsigned long qry,long rowno);
  unsigned long ISQL_GetCursorItems(unsigned long qry);
  int ISQL_MoveRow(unsigned long qry ,long from,long to);
  int ISQL_SwapRow(unsigned long qry, long from,long to);
  int ISQL_SendDeleteCursorMessageToServer(unsigned long qryobject);
  unsigned long ISQL_GetCursorRowFromServer(long qry,long i);
  Show and get commands places their output in query status buffer.
  Please call GetSqlQueryStat() function to get the buffer address of these results.
  Example: 'Show Database' will place the name of the current database in query buffer.
  Example: 'Show version' will place version number in query status buffer.
  List , Select, Desc, Open and Describe commands places their results in Query Objects
  and places the summary of the operation in the query status buffer.
  So to get the summary of the List , Select, Desc and Describe commands call GetSqlQueryStat() function.
  Example: 'Select * from employee_record' will place all employee records in Query Object
  and it places 'Total 100 records retrieved' in the query status buffer, if it retrieves 100 records .

..............................................................................
CHANGE
..............................................................................
  CHANGE
  1) CHANGE PASSWORD new password;
  2) CHANGE GROUP username newgroup name;
  3) CHANGE MANAGER username new manager name;
  4) CHANGE USER PRIVILEGE username new privilege;
  5) CHANGE ADMINISTRATOR adminmane new adminname new password;
  6) CHANGE SUPERUSER newsuperusername newpassword;

..............................................................................
CLOSE
..............................................................................
  CLOSE
  CLOSE CURSOR cursorname;

..............................................................................
COMMIT
..............................................................................
  COMMIT
  COMMIT;

..............................................................................
CONNECT
..............................................................................
  CONNECT
  CONNECT databasename;

..............................................................................
CREATE
..............................................................................
  1) Create TABLE table name (FIELD NAME DATA TYPE [key]|[primary|foreign key] [not null] [auto increment [initial value] ] | [default '[=]value'] | [formula 'value'],KEY(fieldname) | FIELDNAME,....);
  Table name should be 200 letters long maximum. Table name may contain '.' in that case
  table name must be within single quotes e.g. 'tablname.tbl'
  FIELD NAME should be 200 letters long maximum.
  DATA TYPE should be one of the followings:
  char , char(array size) , varchar (array size), text(size), enum('string','string',...) ,storage, time,date , number(total number of digits),
  number(total number of digits , number of digits after decimal point), dir, subdir, file, int , long, int(total number of digits)
  Type storage is for storing files,large binary data. images including blobs, etc
  Type dir, subdir, and file, creates a virtual file system where you can create directories and upload/download files
  2) Create INDEX index name ON table name( FIELD NAME,FIELD NAME,.. FIELD NAME) [asc|desc];
  Index name should be 200 letters long maximum. Index name may contain '.' in that case
  index name must be within single quotes e.g. 'indxname.ndx'
  FIELD NAME should be 200 letters long maximum. Maximum 48 field names can be used
  Sum of all field values must be less than 2048 bytes.
  Index is done always in ascending order in this release.
  3) Create SUPERUSER username, password.
  Once superuser is created this command should not be used anyomore.
  4) Create DATABASE database name
  Only superuser should use this command
  5) Create CURSOR cursorname as select .....
  6) Create VIEW viewname as select .....
  7) Create TABLE table name AS select.....

..............................................................................
DELETE
..............................................................................
  DELETE
  1) DELETE FROM tablename
  [
  rowid = number
  |rowid [in | not in] ( number, number, ....)
  | rowid [between | not between] number and number
  | rowid [within | not within ] (number .. number)
  ]
  [ WHERE condtions] ;
  2) DELETE TABLE [-force] table name ;
  3) DELETE INDEX index name
  4) DELETE VIEW view name
  5) DELETE BACKGROUND command commandname; - this is same as drop background command.
  6) DELETE CURSOR cursorname;
  7) DELETE database cursorname;

..............................................................................
DISCONNECT
..............................................................................
  DISCONNECT
  DISCONNECT;

..............................................................................
DOWNLOAD
..............................................................................
  DOWNLOAD
  1) Download file from table tablename fields(fieldname,....) values (value,value....);
  2) Download proc procedurename downloadpath
  3) Download binary from table tablename field fieldname uniquetableidid=id filenamepath

..............................................................................
DROP
..............................................................................
  DROP
  1) DROP TABLE table name;
  2) DROP VIEW view name;
  3) DROP INDEX index name;
  4) DROP USER username;
  5) DROP GROUP groupname;
  6) DROP HOST hostname;
  7) DROP BACKGROUND command commandname
  8) DROP database databasename

..............................................................................
EXPORT
..............................................................................
  EXPORT
  EXPORT from tablename to tablename mode=append|overwrite format=xBase | ISQL

..............................................................................
GRANT
..............................................................................
  GRANT
  Syntax:
  1) GRANT CHANGE TO username;
  2) GRANT REINDEX to username;
  3) GRANT RECOVER table|view tablename|viewname TO username;
  4) GRANT INSERT into tablename TO username;
  5) GRANT INCLUDE table TO username;
  6) GRANT PACK TO username;
  7) GRANT DELETE from tablename [record number =number] |
  [record number between (number and number)] TO username;
  8) GRANT DELETE view viewname TO username;
  9) GRANT DELETE table tablename TO username;
  10) GRANT DELETE index indexname TO username;
  11) GRANT UNDELETE from tablename [record number =number] |
  [record number between (number and number)] TO username;
  12) GRANT ADD user TO username;
  13) GRANT SELECT * | all | field,.. from tablename TO username;
  14) GRANT DROP view|table|index|user viewname|tablename|indexname TO
  username;
  15) GRANT ALTER table tablename TO username;
  16) GRANT LOCK tablename TO user name;
  17) GRANT UNLOCK tablename TO user name;
  18) GRANT CREATE table|view|index|database|cursor TO username;
  19) GRANT GRANT TO username;
  20) GRANT REVOKE TO username;
  21) GRANT LOGIN TO username;
  22) GRANT UPDATE tablename [record number =number] |
  [record number between (number and number) ] set [ALL | * ] | fieldname,
  fieldname,...... TO username;

..............................................................................
HELP
..............................................................................
  help <topic>
  CREATE
  ALTER
  SELECT
  UPDATE
  DELETE
  UNDELETE
  INSERT
  DROP
  RECOVER
  REINDEX
  LOCK
  UNLOCK
  GRANT
  REVOKE
  LOGIN
  LOGOUT
  ADD
  CHANGE
  DISCONNECT
  CONNECT
  COMMIT
  ROLLBACK
  ZAP
  LIST
  SHOW
  UPLOAD
  DOWNLOAD
  EXPORT
  IMPORT
  PACK
  INCLUDE
  SET
  OPEN
  CLOSE
  OBJECT
  API

..............................................................................
IMPORT
..............................................................................
  IMPORT
  Import from tablename to tablename;

..............................................................................
INCLUDE
..............................................................................
  INCLUDE
  1) Include table tablename;
  2) Include Database databasename;

..............................................................................
INSERT
..............................................................................
  INSERT
  INSERT INTO table name [(fieldname,fieldname,..)]
  VALUES ( number | 'string' , number | 'string' , ....) ;

..............................................................................
LIST
..............................................................................
  LIST
  1) list database;
  2) list lock all | tablename [where locktype =read|add|update|alter|delete];
  3) list privilege username;
  4) list index on tablename|all [where indexname='choose a index name']
  5) list user all;
  6) list user username;
  7) list team
  8) list index key indexname [where key = keyname]
  9) list index info indexname
  10) list lock all;
  11) list lock tablename;
  12) list table;
  13) list table attributes;
  14) list host;
  15) list background command [commandname];
  16) list logged users;
  16) list primary relation tablename;
  16) list foreign relation tablename;

..............................................................................
LOCK
..............................................................................
  LOCK
  LOCK TABLE table name INSERTLOCK/READLOCK/ALTERLOCK/TABLELOCK/UPDATELOCK/DELETELOCK
  [FIELDS(fieldname,fieldname,..)][FROM record number TO record number];
  LOCK DATABASE databasename;
  LOCK GROUP groupname;
  LOCK BACKGROUND COMMAND [commandname];
  LOCK SYSTEM;

..............................................................................
LOGIN
..............................................................................
  LOGIN
  LOGIN username, password;

..............................................................................
LOGOUT
..............................................................................
  LOGOUT
  LOGOUT;

..............................................................................
OBJECT
..............................................................................
  KAMRANGA OBJECTS AND C++ INTERFACES:
  Internet SQL is developed on Kamranga RDBMS engine core library, which provides front end interface API to client programs
  KAMRANGA is a trade mark of Kamranga Software Limited
  KAMRANGA objects are called Internet SQL objects in the Internet SQL environment.
  Following commands result in creating Internet-SQL QUERY RESULT objects:
  1) LISTCOMMAND
  2) DESCRIBECOMMAND
  3) SELECTCOMMAND
  4) READSTORAGECOMMAND
  5) OPENCOMMAND
  Internet-SQL CURSORS
  Internet-SQL server creates and maintains a cursor object for each client for each queriy in the server machine.
  In this mode server will send the status of the query back to the client and all data will be stored in the cursor
  in the server machine. Sever will remove the cursor if client waits too long to retrieve all data from the cursor.
  Using cursor mode can slow down the database access and memory and disk space shortage when too many clients are using cursors.
  Cursor mode can be turned on by 'set cursor mode on' command. It can be turned off by 'set cursor mode off' command.
  By default cursor mode is turned off. See cursor object for detatils on how to retireve data from the cursor.
  Object QUERYRESULT is the pointer the object created as result of any select,list or other query related commands.
  Once these commands are executed a result object is created in the memory. All data rows are stored in this object.
  Use GetQueryResultObject(); function immediately after executing the query command. Stored data rows contain the
  field items retireved as a result of the query. After using the query object it must be freed using
  FreeQueryResultObject() function.
  Object QUERYITEMSLIST To access field items in each data row user have to get a pointer to the object that has
  field item information. This object is the QUERYITEMSLIST.
  To get the pointer to a query item row use function GetQueryRow(RowNumber);
  By default data is stored as array of characters in the database table as well as in the query result object.
  To retrieve data value of a particular field item from the query result row use function CopyItemString()
  Object CURSOR Cursor objects are same as query result object , except it retireves row from the cursor in the server machine.
  In order to use cursors client program should set the cursor mode ON.
  Use GetQueryResultObject(); function immediately after executing the query command, to get the pointer of the cursor object.
  To access field items in each data row user has to get a pointer to the object that has field item information.
  This object is the QUERYITEMSLIST. To get the pointer to a query item row use function GetCursorRow(RowNumber);
  By default data is stored as array of characters in the database table as well as in the query result object.
  To retrieve data value of a particular field item from the query result row use function CopyItemString()
  Data type 'INTERNET_SQL_OBJECT' is the heart of all data manipulation activities in the database.
  Following methods are used to manipulate data in the object and do communication with the server.
  QUERYOBJECT Exec(char *commandstring);
  KINT64 Count(void);
  KINT64 RowLength(void);
  QUERYITEMSLIST Next(void);
  QUERYITEMSLIST Prev(void);
  QUERYITEMSLIST Bottom(void);
  QUERYITEMSLIST Top(void);
  char *Data(char *fieldname);
  KINT64 IntData(char *fieldname);
  int CopyField(char *fieldname,char *databufer);
  QUERYITEMSLIST Row(KINT64 rowno);
  double DoubleData(char *fieldname);
  char * DataByFno(long fieldno);
  char * FieldName(long fieldno);
  int FieldCount(void);
  char *FieldType(char *fieldname);
  int FieldLength(char *fieldname);
  char *Status(void);
  KINT64 InsertRowId(void);
  INTERNET_SQL_OBJECT *NewInternetSqlObject(long startid); allocates an INTERNET_SQL_OBJECT
  void FreeInternetSqlObject(INTERNET_SQL_OBJECT *object) frees the allocated object
  Example:
  long startid=StartInternetSql(char *dbhomedir,char *serverip,char * serverport);
  INTERNET_SQL_OBJECT * object=NewInternetSqlObject(long startid);
  object->Exec("Select * from employee;")
  long rowcount=object->Count();
  while(object->Next())
  {
  char *employee_name=object->Data("empname");
  printf("employee=%s",employee_name);
  }
  long success=object->Exec("Insert into employee (empname) values('rahat');");
  if(!sucess)
  printf("error=%s",object->Status());
  FreeInternetSqlObject(object);

..............................................................................
OPEN
..............................................................................
  OPEN
  OPEN CURSOR cursorname;
  This command will open a already created cursor saved in the server cache.
  Data is stored in this cursor as rows
  It will return a cursor object similar to those objects returned by select command.
  Major difference a select and a open cursor is that select retrievs all rows in to local machine.
  while cursor rows are retrieved on demand from the server.
  See help on OBJECTS for more information on cursor objects

..............................................................................
PACK
..............................................................................
  PACK
  1) Pack table tablename;
  2) Pack database databasename;
  3) Pack database all;
  4) Pack dblist;

..............................................................................
RECOVER
..............................................................................
  RECOVER
  1) RECOVER TABLE table name
  2) RECOVER VIEW view name
  3) RECOVER INDEX index name

..............................................................................
REINDEX
..............................................................................
  REINDEX
  REINDEX index name;

..............................................................................
REVOKE
..............................................................................
  REVOKE
  1) REVOKE CHANGE FROM username;
  2) REVOKE REINDEX FROM username;
  3) REVOKE RECOVER table|view tablename|viewname FROM username;
  4) REVOKE INSERT into tablename FROM username;
  5) REVOKE INCLUDE table FROM username;
  6) REVOKE PACK FROM username
  7) REVOKE DELETE from tablename [record number =number] |
  [record number between (number and number)] FROM username;
  8) REVOKE DELETE view viewname FROM username;
  9) REVOKE DELETE table tablename FROM username;
  10) REVOKE DELETE index indexname FROM username;
  11) REVOKE UNDELETE from tablename [record number =number] |
  [record number between (number and number)] FROM username;
  12) REVOKE ADD user FROM username;
  13) REVOKE SELECT * | all | field,.. from tablename FROM username;
  14) REVOKE drop view|table|index|user viewname|tablename|indexname FROM
  username;
  15) REVOKE ALTER table tablename FROM username;
  16) REVOKE LOCK tablename FROM user name;
  17) REVOKE UNLOCK tablename FROM user name;
  18) REVOKE CREATE table|view|index|database|cursor FROM username;
  19) REVOKE GRANT FROM username;
  20) REVOKE REVOKE FROM username;
  21) REVOKE LOGIN FROM username;
  22) REVOKE UPDATE tablename [record number =number] |
  [record number between (number and number) ] set [ALL | * ] | fieldname,
  fieldname,...... FROM username;

..............................................................................
ROLLBACK
..............................................................................
  ROLLBACK
  ROLLBACK;

..............................................................................
RUN
..............................................................................
  RUN
  1) Run command script scriptname;
  2) Run ispl filename
  3) Run stored procedure procedurename arguments

..............................................................................
SELECT
..............................................................................
  SELECT
  SELECT [DISTINCT | UNIQUE ] ITEM|(select statement) ,ITEM|(select statement) ,....... FROM
  TABLE NAME | (select statement) table alias, TABLE NAME | (select statement) table alias, ........
  ;|TABLE NAME | (select statement) table alias inner join | left outer join | right outer join| full outer join TABLE NAME | (select statement) table alias ON condition
  ;|WHERE condition
  ;| GROUP BY group item, group item,........
  ;| HAVING condition
  ;| ORDER BY order item,order item,.....[ASC | DESC]
  ;| [UNION | INTERSECT | MINUS select statement;]
  ;| INTO [TEMP] tablename
  ;
  Note: Words enclosed in [] are optional . '|' means 'or'.
  DISTINCT,UNIQUE: These key words are used when unique record values are to
  be retrieved.
  ITEM: An item may be a mathematical expression, field name , string constant,
  function, constant number, key word ALL , an asterisk '*' or (select statemnt)
  Syntax: ALL
  | *
  | [table name | table alias.]field name [ alias]
  | [table name | table alias.]recno() [ alias]
  | [table name | table alias.]*
  | string constant | number | function | math expression [ alias ]
  A table name/table alias may be within single quotes. Another way of looking at the
  ITEM may be as follows:
  ALL
  | *
  | identifier
  | identifier.identifier
  | string constant.identifier
  | identifier.*
  | string constant.*
  | identifier alias
  | identifier.identifier alias
  | string constant.identifier alias
  | table alias.identifier
  | table alias.identifier alias
  | number
  | number alias
  | string
  | string alias
  | function
  | function alias
  | mathematical expression
  | mathematical expression alias
  | rowid
  | rowid alias
  | table alias.rowid
  | table alias.rowid alias
  | identifier.rowid
  | identifier.rowid alias
  | string.rowid
  | string.rowid alias
  |(select statement)
  Internet-SQL reserved function names: Avg(),Max(),Min(),Sum(),Count(*),Len(),
  DateToChar(date fieldname, 'format') format should be 'mm-dd-yyyy' | 'mm-dd-yy' | 'dd-mm-yyyy' | 'dd-mm-yy'
  TABLE NAME : A table name is a 200 characters long string which may or may not be within single quotes.
  ';' : Command terminator.
  WHERE: Where is the conditional expression statements. It may contain, function,field name,string consant, number constant , mathematical expressions, logical AND , OR,relational operators, sets.
  Syntax:
  Where expression = OR_expression OR OR_expression
  OR_expression = AND_expression AND AND_expression
  AND_expression =
  REL_expression RELATIONAL OPERATORS REL_expression
  REL_expression =
  MUL_DIV_expression MULDIV OPERATORS MUL_DIV_expression
  MUL_DIV_expression =
  ADD_SUB_expression ADDSUB OPERATORS ADD_SUB_expression
  ADD_SUB_expression = valid where ITEM
  | SUB SELECT STATEMENT
  | Where expression
  | Set
  Invalid where items are AVG(),MAX(),MIN(),COUNT(*),*,ALL
  GROUP BY : Group by items are field names those are stated in select items.
  SUB SELECT STATEMENT: This is a SELECT statement but must select one item only. It does not allow following clauses:
  a) GROUP BY .... HAVING
  b) ORDER BY
  c) INTO
  HAVING: Having is the condition by which grouping of records may be done
  and can only be used with GROUP BY statement. It may contain function,field name,string constant, number constant , mathematical expressions, logical AND , OR, relations operators, sets.
  Syntax:
  Same as WHERE expression syntax.
  ORDER BY: This clause is used to sort the selected outputs. An order item is a field name or an alias name in the SELECT item list.
  INTO: This clause is used if the query result is to be stored into a table.
  RELATIONAL OPERATORS :
  equal: =
  less than: <
  less than equal: <=
  greater than: >
  greater than equal: >=
  not equal: !=
  partial string match: =[n] where n is the number of characters to
  match from the begining of the string. This is used only in where
  clause.
  not partial string match: !=[n] where n is the number of
  characters not to match from the begining of the string. This is used
  only in where clause.
  partial string match: LIKE 'likestring'. like string can be combination
  of any mixture of '_' , % , A..Z , a..z.. This is used only in where
  clause.
  not partial string match: NOT LIKE 'likestring'. like string can be
  combination of any mixture of '_' , % , A..Z , a..z.. This is used
  only in where clause.
  set operations: in,not in
  ranges: between value1 and value2
  matching pattern to list of fields: MATCH(strinng) IN (fieldname, fieldname, fieldname,...)
  checking existence of records: EXISTS (select ....)
  EXISTS uses a subquery as a condition, where the condition is True if
  the subquery returns any rows, and False if the subquery does not return any rows;
  this is a nonintuitive feature with few unique uses.
  Example: SELECT OWNERFIRSTNAME, OWNERLASTNAME FROM ANTIQUEOWNERS
  WHERE EXISTS (SELECT * FROM ANTIQUES WHERE ITEM = 'Chair');
  If there are any Chairs in the Antiques column, the subquery would return a row or rows,
  making the EXISTS clause true, causing SQL to list the Antique Owners.
  If there had been no Chairs, no rows would have been returned by the outside query.
  Satisfying condition over all records: ALL (select .....)
  ALL is another unusual feature, as ALL queries can usually be done with different,
  and possibly simpler methods; let's take a look at an example query:
  SELECT BUYERID, ITEM FROM ANTIQUES WHERE PRICE >= ALL (SELECT PRICE FROM ANTIQUES);
  This will return the largest priced item (or more than one item if there is a tie),
  and its buyer. The subquery returns a list of all Prices in the Antiques table,
  and the outer query goes through each row of the Antiques table, and if its Price is greater
  than or equal to every (or ALL) Prices in the list, it is listed, giving the highest priced Item.
  The reason '=' must be used is that the highest priced item will be equal to the highest price on
  the list, because this Item is in the Price list.

..............................................................................
SERVER
..............................................................................
  SERVER
  server 'ip','port';
  This command will change the target server to new ip and port address.

..............................................................................
SET
..............................................................................
  SET
  1) Set cursor mode on|off
  2) Set table permission tablename (Permission type= y|n,..);
  Permission types are:
  groupread
  groupwrite
  groupalter
  grouplock
  groupdrop
  grouprevoke
  groupdelete
  allread
  allwrite
  allalter
  alllock
  alldrop
  allrevoke
  alldelete
  3) Set debug on|off
  4) Set server display on|off
  5) Set xbase mode on|off - OBSOLETE, NO LONGER SUPPORTED;
  6) Set network mode on|off - OBSOLETE, NO LONGER SUPPORTED;;
  7) Set useindex mode on|off - OBSOLETE, NO LONGER SUPPORTED;;
  8) Set compression on | off;
  9) Set encryption on | off;
  10) Set monitor on |off ;
  11) Set monitor on for userid databasename;
  12) Set monitor output screen | logfile;
  13) Set logfilesize megabytes;
  14) Set Database info ip|port = value;
  15) Set table group tablename groupname;
  16) Set table owner tablename ownername;
  17) Set background command attribute commandname (attribute name = value,..);
  Attribute are:
  CursorMode: valid values are 1 or 0
  startdate: 'year-month-day' e.g '1988-13-31'
  expiredate: 'year-month-day' e.g '1998-13-31'
  starttime: 'hh:mm:ss:00' e,g. '23:34:45:00'
  expiretime: 'hh:mm:ss:00' e,g. '12:34:45:00'
  execintervalunit: valid values are minute,hour,month,year
  enabled: valid values y or n
  transmitoutputflag: valid values are y or n
  transmitip: ip address 'xx.xx.xx.xx' where output will be transmitted
  transmitport: port number where output will be transmitted
  groupaccess: valid values are y or n
  allaccess: valid values are y or n
  DebugMode: valid values are 1 or 0
  18) Set background timer minute = minute
  19) Set User Info userid (infoname = values, ....)
  infonames are:
  firstname, lastname, contactinfo
  20) Set error on/off;

..............................................................................
SHOW
..............................................................................
  SHOW
  1) Show user account summary;
  2) Show version;
  3) Show database;
  4) Show home;
  5) Show Host;
  6) Show user;
  7) Show date;
  8) Show time;
  9) Show mode;

..............................................................................
UNDELETE
..............................................................................
  UNDELETE
  UNDELETE FROM tablename
  [
  rowid = number
  |rowid [in | not in] ( number, number, ....)
  | rowid [between | not between] number and number
  | rowid [within | not within ] (number .. number)
  ]
  [ WHERE condtions] ;

..............................................................................
UNLOCK
..............................................................................
  UNLOCK
  UNLOCK TABLE table name;
  UNLOCK DATABASE databasename;
  UNLOCK GROUP groupname;
  UNLOCK BACKGROUND COMMAND [commandname];
  UNLOCK SYSTEM;

..............................................................................
UPDATE
..............................................................................
  UPDATE
  UPDATE tablename
  [
  rowid = number
  |rowid [in | not in] ( number, number, ....)
  | rowid [between | not between] number and number
  | rowid [within | not within ] (number .. number)
  ]
  SET ALL=expressions
  | field name = expressions , field name =expressions .....
  [WHERE condtions] ;

..............................................................................
UPLOAD
..............................................................................
  UPLOAD
  1) Upload file to table tablename fields(fieldname,....) values (value,value....);
  2) Upload proc procedurepath
  3) Upload binary to table tablename field fieldname uniquetableidid=id filenamepath

..............................................................................
ZAP
..............................................................................
  ZAP
  Zap table tablename;

------------------------------------------------------------------------------
2. VECTOR TABLE COMMANDS
------------------------------------------------------------------------------

Two commands, VINSERT and VECTOR, plus one column type, 'storage'. They are
reserved words: no table or column may be named vinsert or vector.

  storage                 column type holding a vector of floats

  VINSERT <col> INTO <table> VALUES '<f,f,f>' , <key>='<value>';
                          store a vector, together with the ordinary column
                          values that identify the row

  VECTOR <col> FROM <table> WHERE query='<f,f,f>' AND maxdistance=<float>;
                          nearest-neighbour search: returns the rows whose
                          vector lies within maxdistance of the query vector,
                          read back with the normal cursor calls

A worked sequence, with the output it really produces:

  create table vectors(id char(8), vec storage);
      Table creation successful.
  vinsert vec into vectors values '1,0,0' , id='a';
      1 vector (3 dims) inserted; key 'V1_1786730586'
  vinsert vec into vectors values '0,1,0' , id='b';
  vinsert vec into vectors values '0,0,1' , id='c';

  vector vec from vectors where query='1,0,0' and maxdistance=0.2;
      ID | VEC             | DISTANCE
      a  | V1_1786730586   | 0.0000
      (1 row)

The vector is a quoted, comma-separated list of floats. VINSERT stores it under
a generated key and puts that key, not the numbers, in the column. The result of
a VECTOR search carries an extra DISTANCE column that no other statement
produces.

A STORAGE COLUMN CANNOT BE SELECTED DIRECTLY. Only VECTOR reads it:

  select id from vectors;          works
  select id,vec from vectors;      ERROR: EvalPostLink: Invalid data type
                                          in the expression.

So a storage column is reachable through VECTOR and through nothing else. Plan
queries accordingly: keep whatever you need to display in ordinary columns
beside it, as the `id` above does, and let VECTOR return the key and distance.
(An earlier draft of this file said a plain SELECT showed the keys. It does not;
that sentence was inferred from the VECTOR output rather than tested.)

THE DISTANCE IS COSINE DISTANCE, 1 minus the cosine of the angle:

      0   same direction        1   at right angles        2   opposite

Measured, with query '1,0,0':

      '1,0,0'   -> 0.0000     '0,1,0'  -> 1.0000
      '5,0,0'   -> 0.0000     '-1,0,0' -> 2.0000

Two consequences worth knowing before choosing a maxdistance. MAGNITUDE IS
IGNORED - '5,0,0' is at distance 0 from '1,0,0', because only direction counts,
so there is no need to normalise vectors before inserting them. And the scale is
0..2, not 0..1: maxdistance=1 already admits everything at right angles or
closer, which for embedding vectors is most of the space. Start tight, near
0.1-0.3, and widen only if too little comes back.

maxdistance is a cut-off, not a row limit: every row inside it is returned, in
table order rather than sorted by distance. Rank the rows yourself if the
nearest one is what you want.

Note the distinction from CREATE INDEX. An index speeds up exact WHERE matching
on a scalar column; VECTOR compares the query against every stored vector and
does not use an index.


------------------------------------------------------------------------------
3. COMMAND-LINE OPTIONS
------------------------------------------------------------------------------

3.1 The common set
------------------

Every shell and server accepts these, and they mean the same thing everywhere:

  -dbhome <dir>     database home directory
  -ip <addr>        server address        (network tools; see 3.2)
  -port <n>         server port           (network tools; see 3.2)
  -u <user>         user name
  -pwd <pass>       password              (-p is accepted as an alias)
  -cmd <file>       read and execute commands from <file>, then exit
  -version          print the version and exit
  -?  /  --help     usage

They are case-insensitive and take either prefix, so -DBHOME and /dbhome are
the same option. Aliases accepted everywhere: -h for -dbhome, -s for -ip,
-user for -u, -p for -pwd, -f for -cmd. A bare argument with no leading dash
is taken as the database home.

The one exception is isqlserverd, which predates the shared parser and reads
-h as HELP, not as -dbhome. Write -dbhome there.

-pwd, not -p, is the documented name for the password. A parser can tell -p
from -port exactly; a person cannot, and isqlsh 1.23 shipped with -p meaning
the PORT. -p still works, but -pwd is what to write down.

-ip and -port are meaningful only to tools that talk to a server. A local tool
prints, rather than silently ignoring them:

  <program>: -ip/-port ignored - this tool runs the engine in-process,
             not over a network

Command files: statements end with ';' and may span lines; a ';' inside quotes
does not end one; lines starting with -- or # are comments; a UTF-8 byte-order
mark at the start of the file is skipped, so files saved by Notepad or by
PowerShell's Set-Content work unchanged.


3.2 Which programs are network tools
------------------------------------

  isqlsh            BOTH. Without -ip it runs the engine in this process
                    against -dbhome. With -ip it connects to a server over
                    TCP and needs no local home.
  isqlserver        network SERVER (windowed)
  isqlserverd       network SERVER (console/headless)
  shell             local only
  shelldev          local only
  locshell          local only
  shellonlib        local only

A remote isqlsh session is multi-user and will generally refuse statements
without -u and -pwd. A local session gets owner rights automatically.


3.3 isqlsh - the scriptable shell
---------------------------------

  isqlsh [options] [home]

  -dbhome <dir>     database home            (-h, or a bare argument, also work)
  -ip <addr>        connect to a server      (-s also works)
  -port <n>         server port, default 1973
  -u <user>         user name
  -pwd <pass>       password                 (-p also accepted)
  -l user,pass      the same login in one argument
  -t <ms>           how long to wait for the port, default 5000
  -cmd <file>       run a file of statements (-f also works)
  -d <db>           database to USE after connecting, default 'default'
  -q                quiet: values only, no banner, prompt or row counts
  -e                stop at the first failing statement
  -v                echo each statement before running it
  -config <file>    take DatabaseHome / ServerIpAddress / ServerPortNo /
                    UseNetwork from a config file. The file is only READ;
                    nothing on disk is written or replaced. Explicit
                    command-line options win over it.
  -version, -?

  Exit codes:  0 every statement succeeded
               1 at least one statement failed
               2 could not connect, or could not open -cmd / -config

  For scripting:
      isqlsh -q -e -dbhome C:\isqlhome -cmd schema.sql
      if errorlevel 1 echo SCHEMA FAILED

  One surprise when reading -q output: INSERT returns the row it inserted, as
  a result set, so an insert contributes a line of values just as a select
  does. That is the engine's behaviour, not the shell's. A script that counts
  lines should either put its inserts in a separate run from its queries, or
  count only what it selects.

  Against a server:
      isqlsh -ip 127.0.0.1 -port 1973 -u kamranga -pwd manager -cmd report.sql


3.4 shell, shelldev, locshell, shellonlib - the local shells
------------------------------------------------------------

  <program> [options]

  -dbhome <dir>     database home for this run. Without it each falls back to
                    a home compiled into the binary, which is c:\isql and is
                    wrong on any machine that did not install there.
  -u <user> -pwd <pass>
                    log in as that user; otherwise the shell takes owner rights
  -cmd <file>       run a file of statements and exit
  -version, -?
  -ip, -port        accepted and reported as ignored

  With no -cmd each presents its own iSql> prompt; 'quit' leaves.


3.5 isqlserverd - the headless server
-------------------------------------

  isqlserverd [-dbhome DIR] [-port N] [-config FILE] [-dll DIR] [-name NAME]

  -dbhome <dir>     database home
  -port <n>         port to listen on
  -config <file>    run on that configuration file instead of the KAMRANGA.CFG
                    already here; it is installed as KAMRANGA.CFG and the
                    previous one kept as KAMRANGA.CFG.bak
  -dll <dir>        where to find isqldll.dll (default: the exe's own
                    directory, then the current one)
  -name <name>      server name for the network agent, default "Kamranga"
  -ip <addr>        accepted and reported as ignored - a server listens
                    locally and takes its address from [ServerIpAddress]
  -version, -?

  Exit codes:  0 clean shutdown          2 isqldll.dll not found
               1 startup failure         3 -config/-dbhome could not be applied

  IMPORTANT - why -dbhome writes to the config. The server does not take a
  home on the command line in the ordinary sense: the engine reads
  KAMRANGA.CFG itself, and a value in that file BEATS one passed to the
  engine. Measured:

      no config file, home passed to the engine   ->  the passed home is used
      config file names a home                    ->  the CONFIG wins, silently

  A server always has a [DatabaseHome], so a -dbhome that was merely handed to
  the engine would be ignored every single time. -dbhome and -port therefore
  write their value into KAMRANGA.CFG, keeping the previous file as
  KAMRANGA.CFG.bak. Only the entry...end block is touched.

  Starting it:
      cd bin
      isqlserverd.exe -dbhome C:\isqlhome -port 1973

  It prints "engine started" and "listening - press Ctrl+C to stop".


3.6 isqlserver - the windowed server
------------------------------------

  isqlserver [-dbhome DIR] [-port N] [-version] [-?]

  The same server with a window. Being a GUI program it has no console, so it
  reports through a message box. -dbhome and -port behave exactly as in 3.5,
  writing into KAMRANGA.CFG before the engine starts.


------------------------------------------------------------------------------
4. CONFIGURATION FILE
------------------------------------------------------------------------------

The engine reads kamranga.cfg when running as a server and isql.cfg otherwise,
always from the working directory, and it cannot be told to read any other
filename - which is why -config exists.

  Anything out here is prose and is ignored.

  entry
  [DatabaseHome] C:\isqlhome
  [ServerPortNo] 1973
  [ServerName]   localhost
  end

ONLY the entry...end block is read. This matters: the shipped KAMRANGA.CFG
explains itself with a sentence containing "[DatabaseHome] MUST point at ...",
and that sentence is a comment, not a setting.

  [DatabaseHome]           database home directory
  [ServerPortNo]           port the server listens on / a client connects to
  [ServerName]             server name
  [ServerIpAddress]        server address for a client
  [UseNetwork]             1 to connect remotely, 0 for in-process
  [ParallelServerPortNo]   auxiliary port
  [MutexServerPortNo]      auxiliary port

A database home is a directory containing a 'db' subdirectory. Create those two
directories; the engine populates db on first use.

------------------------------------------------------------------------------
end of reference
------------------------------------------------------------------------------
