intro_database


	How to create a database table using SQL
	========================================
	
	(Note: UPPER CASE represents a syntax word, please use lower case when
	       using the commands.)
	
	 1. First time only: Contact Denise Finstrom (x4688) or John DeVoy
	    (x4729) to get a login account on the ADBS SQL Server so you can
	    use the ISQL utility to execute your table creation script
	    (the .sql file you create in steps 5-8 below).
	
        2. Decide which database (appdb or apvdb) in which to create tables.
	    The appdb database keeps logs of each change to each row in the
	    table and these changes are saved each night.  The appdb database
	    is used for most tables.  The apvdb database is used for tables
	    that are more volatile (the changes may be bigger than the whole
	    table when one row is changed many times in one day).  A "snapshot"
	    of the apvdb database is copied to tape each night.
	    Use of an image or text field (see below) often indicates a table
	    should be created in apvdb, NOT appdb... Please consult an expert
	    if the choice is not clear to you.
	
	 3. Organize and decide what data needs to be stored, is it stored
	    once per program, once per program activation, or once per
	    "subpage"?
	
	 4. Decide what data fields are needed.  Remember to add the fields
	    needed to make each row in the table unique.
	
	 5. To document the new table, create a script file with the extension
	    ".sql" containing the commands needed to setup the table and any
	    stored procedures which access the table.  When you MECCA your
	    program, have this file in the same directory as your source code,
	    and MECCA will copy it along with the source.
	
	 6. The SQL syntax for creating a table is :
	      CREATE TABLE table_name (	
		field_name1 	field_type	not null,
		field_name2 	field_type	not null,
		...
		field_nameN 	field_type	not null
	      )
	      GO
	
	    The table name should be unique and represent the table.
	
	FIELD_TYPE	'C' TYPE	size	range
	 tinyint	unsigned char	1 byte	0 - 255
	 smallint	signed short	2 byte	-32768 - 32768
	 int		signed int	4 byte	-2147483648 - 2147483647
	 real		float		4 byte	n/a
	 float		double		8 byte	n/a
	 binary(n)	char[n] n<256	n byte	0x00 - 0xFF each byte
	 varbinary(n)	char[n] n<256	n byte*	0x00 - 0xFF each byte
	 char(n)	char[n] n<256	n byte	valid ascii each byte
	 varchar(n)	char[n] n<256	n byte*	valid ascii each byte
	 image		char[32768]	varies+	0x00 - 0xFF each byte
	 text		char[32768]	varies+	valid ascii each byte
	 smalldatetime	n/a		4 byte	1/1/1900 - 06/06/2079~
	 datetime	n/a		8 byte	1/1/1753 - 12/31/9999~
	
	* varbinary and varchar are stored as their actual length, but
	  the API zero fills to the declared maximum length.
	+ image and text data is readable ONLY using the db_nextrow API
	  function.  The data is stored and returned as actual size
	  (no zero fill is done).  So, any fields after the text/image
	  field will be shifted left.  If less data is available than
	  specified in the bytes_per_row argument, a positive (success)
	  ACNET status of SQL_SMALLROW is returned.
	  Use of an image or text field often indicates a table should be
	  created in apvdb, NOT appdb.  Please, consult an expert if the
	  choice is not clear to you.
	~ smalldatetime has a resolution of one minute.
	  datetime has a resolution of 1/300th of a second.
	  Please see the later section on date and time in the Sybase database.
	  To select a date field as text use: (count as 24 byte column)
		select convert(char(24),mydate,109)
	  OR select a date field as text use: (count as 10 byte column)
		select convert(char(10),mydate,101)
	    (see date conversion styles table for other options)
	
	 7. Create index(s) on the table. Note that an index is not needed
	    if the number of rows is small (less than 100). If you create
	    any index, the "most important" one should be defined as
	    clustered (meaning that the table data will be built using this
	    index). The syntax is:	
	      CREATE [UNIQUE] [CLUSTERED] index idxN on table_name
	      (field_name,...)
	      GO
	    where N starts at 1 and is incremented for each index on the table.
	
	    To remove an index the syntax is:
	      DROP INDEX table_name.index_name
	      GO
	
	 8. To grant read access to a table the syntax is:
	      GRANT SELECT ON table_name TO {user[,user]}
	      GO
	    To grant read/write access to a table the syntax is:
	      GRANT SELECT,INSERT,UPDATE,DELETE ON table_name TO {user[,user]}
	      GO
	
	    Where user=PAxxxx for an individual PA,
		  user=appdb_isql for all ISQL users,
		  user=appdb_console for all console applications,
		  user=appdb_central for all central services, and/or
		  user=appdb_readonly for unknown, INCLUDING WWW OFFSITE USERS.
	    Please don't use user=public anymore.  Thanks.

	 9. Execute your script using ISQL, either using cut and paste or
	    $ isql -S ADBS -U user -i script.sql
	
	10. The simplest approach to making changes in tables is to delete and 
	    recreate a table. If the table is populated, it's best to create a 
	    temporary table and move the data to it.
	    For moving data from one table to another the syntax is:
	      INSERT table_name_new
	        SELECT field_nameX,field_nameY,fill_data FROM table_name
	      GO
		(NOTE: DO NOT USE _1 or _2, etc. names ending like this are
		       special to sybase in some cases.  Thanks.)
	    The key here is that the select list must have exactly one
	    entry for each row in the new table.  Please check a SQL
	    reference or an expert if this is not clear.
	    To delete all of the data in a table the syntax is:
	      TRUNCATE TABLE table_name
	      GO
	    To delete the table structure and all indexes, the syntax is:
	      DROP TABLE table_name
	      GO
	    Then if you want you can rename back to the old table name:
	      SP_RENAME table_name_new,table_name
	      GO

	11. Date and time in the Sybase database
	    1. Sybase's text/string date formats don't include an explict
	       timezone, e.g., CST, CDT, or UTC.
	    2. Current Sybase best practice is to run the DB on a machine
	       that has its locale configured for UTC not CST/CDT or
	       whatever else might appear to be appropriate given the DB's
	       location.  For historical reasons the controls database
	       is not configured in this best practices way.
	    3. The only exception which did not originally exist is that
	       Sybase now has both getdate() and getutcdate() functions. If
	       one follows the best practice above, getdate() and
	       getutcdate() are equivalent.  Otherwise, getdate() will be a
	       local time per the underlying operating system's locale
	       configuration and getutcdate() will be a UTC time.
	    4. A date field initialized by getdate() or getutcdate() does
	       not and can not record which routine was used to initialize
	       it.  THEREFORE, A SYBASE DATE FIELD WHETHER INITIALIZED BY A
	       TEXT/STRING OR getdate() OR getutcdate() DOES NOT KNOW ITS
	       TIMEZONE.  FOR EACH DATE FIELD, A TIMEZONE MUST BE CHOSEN AND
	       USED CONSISTENTLY.  For our situation where getdate() and
	       getutcdate() are not equivalent, one has two potential options:
	       1. Use local time consistently:
	          a. Only use getdate() never getutcdate() to get the current time.
	          b. All time strings must implicitly be in local time.  Suppose
	             an alarm occured at "Jun 10 2009  1:46PM CDT" and that we want
	             to store that value in a DB field where we have chosen to use
	             local time consistently.  In such a case one has
	             alarm_occured_local = "Jun 10 2009  1:46PM".
	       2. Use UTC consistently:
	          a. Only use getutcdate() never getdate() to get the current time.
	          b. All time strings must implicitly be in UTC.  Suppose
	             an alarm occurred at "Jun 10 2009  1:46PM CDT" and that we want
	             to store that value in a DB field where we have chosen to use
	             UTC consistently.  In such a case one has
	             alarm_occurred_utc = "Jun 10 2009  6:46PM".
	       In the 1.b. and 2.b., the time of the alarm is the same, but the
	       string used to initialize the date field is different because
	       the timezone convention is different.  There is no way to tell
	       just from the field's type which convention is in use. So, it is
	       advisable to document the choice in some way.  The option chosen
	       in the example is an "_utc" or "_local" suffix.  BECAUSE LOCAL
	       TIMES SUFFER PATHOLOGIES - REPEATED AND DUPLICATED TIMES - CAUSED
	       BY DAYLIGHT SAVINGS TIME, IT IS RECOMMENDED THAT ALL NEW DATE FIELDS
	       USE THE UTC CONVENTION AND DOCUMENT THIS CHOICE WITH AN "_utc"
	       SUFFIX.   SYBASE's datediff, etc. DO NOT HANDLE LOCAL TIMES PROPERLY
	       EITHER BECAUSE OF THE DAYLIGHT SAVINGS TIME ISSUES.
	    5. ALMOST ALL OF THE EXISTING DB TABLES USE LOCAL TIMES AND DON'T
	       HAVE AN "_local" SUFFIX OR ANY OTHER SUCH MECHANISM TO IDENTIFY
	       THEIR USE OF THE LOCAL TIME CONVENTION.  This is because Sybase
	       did not orginally support getutcdate() and almost all of the
	       controls software has its origin on VAX/VMS where local time was
	       used pervasively and not just at the user interface level.
	    6. SO, TO USE UTC CONSISTENTLY FROM WITHIN C/C++ AND JAVA, ONE MUST
	       KNOW HOW TO GENERATE A TIME STRING IN UTC GIVEN A TIME VALUE IN
	       SOME OTHER SYSTEM, E.G., SECONDS SINCE THE EPOCH, A.K.A., UTC
	       CTIME, OR CLINKS (SEE clinks_now).  HERE IS HOW:
	       1. C/C++
	          a. clinks
	             Use clinks_to_date with the DATE_OPT_UTC_CTIME option.
	          b. seconds since the Epoch, a.k.a., UTC ctime
	             Use utc_ctime_to_date with its output_utc argument as true.
	       2. Java
	          a. Be careful because java.sql.Timestamp's toString() method
	             generates a local time.
	          b. John DeVoy has prepared documentation, which you may find
	             in the Javadoc for DbServer.
	    7. IF ONE USES UTC TIMES, THEN SYBASE'S datediff CAN BE USED TO GET
	       TIMES OUT OF THE DATABASE IN EITHER SECONDS SINCE THE EPOCH OR CLINKS:
	       a. seconds since the Epoch, a.k.a., UTC ctime
	          UTC_ctime = datediff(second,'1/1/1970',mydate)
	       b. clinks
	          clinks    = datediff(second,'1/1/1972',mydate)
	                    - datediff(second,'Jan 1 1972  0:00AM','Jan 1 1972  6:00AM')
	          The line immediately above is because clinks use midnight CST not
	          midnight UTC as the time part of their t=0 moment.
	    8. References on Sybase and daylight savings time:
	       Daylight Savings Time and ASE
	       Daylight Saving Time and Sybase Server Products

	In your application
	===================
	
	(Note:  All SQL commands must be sent via the CLIB routines db_select
		and db_modify.)
	
	 1. To select rows from a table, the SQL syntax is:
	    SELECT field_name1, field_name2 FROM table_name WHERE condition
	    e.g.: select di,name from device where name = "M:OUTTMP"
	
	 2. To insert a new row to a table, the SQL syntax is:
	    INSERT table_name values (value1, "value2", valueN)
	    e.g.: insert device values (27235, "M:OUTTMP")
	
	 3. To delete a row from a table, the SQL syntax is:
	    DELETE table_name WHERE condition
	    e.g.: delete device where name="M:OUTTMP"
	    Be careful, the database will delete ALL rows matching the
	    condition!  With no conditions, ALL the data rows from a table
	    will be deleted!!!
	
	 4. To UPDATE a row in a table, the SQL syntax is:
	    UPDATE table_name SET fld_1 = val_1, fld_2 = "val_2" WHERE condition
	    e.g.: update device set di=12345 where name="M:OUTTMP"
	    Be careful, the database will update ALL rows matching the
	    condition!  (you will usually want to specify conditions defining
	    exactly ONE row.)  If no condition is provided, ALL the rows in
	    the table will be updated!!!
	
	Additional notes:
	
	    See online help at http://www-ad.fnal.gov/help/ul_clib/db_open.html
	
	    Check with an SQL reference book to see what complex queries are
	    possible!
	
	    Multiple commands can be sent together in one db_select call.
	    In this case, the total number of rows returned by all of the
	    select statements will be returned in the number of rows argument.
	    It is best if all of the returned row sizes are the same.  If they
	    differ, a status of SQL_SMALLROW will be returned since some rows
	    will be smaller than the requested length.
	
	    All strings must be delimited by either single ' or double " quotes.
	    (Be careful if your strings contain quotes!)
	    To embed both quote types you must double the internal quotes:
		"I'll bet he said ""huh?""." OR 'I''ll bet he said "huh?".'
	
	    Conditions are of the form:
	    (field_name1 = 5 and field_name2 = 2) or field_name3 = 7
	
	    The correct index(s) can make a very large difference in access
	    times for large tables.  Ask an expert for advice if this may
	    apply to you.

	Related functions:

	db_select, db_modify, db_open, db_close, db_error,
	db_nextrow, db_users, db_result_column_info, db_send_c