*******************************************************************************
6/16/04 (1) 2pm-3pm
*******************************************************************************
ACE Meeting
  -introductions & big picture of summer projects

*******************************************************************************
6/16/04 (5.5) 3pm-8:30pm
*******************************************************************************
ACE Lab with Steve Brown
  -overview of the current state of the ActiveCampus project
    step1 to make the upgrade/switch from MySQL to PostGreSQL
    step2 work on geolocation & mime type issues
    step3 modularize everything, steal profusely from Wiisard
    step4 ... put out fires as they come up
  -creation of PostGres database under ActiveCampus/~jpachura called ActiveCampus-postgres
  -new branch checkout of ActiveCampus login:jpachura password:jpachura
  -new branch checkout of Wiisard login:guest password:n/a
  -crash course in Subversion
    commonly used commands
      svn checkout
      svn info
      svn switch ~~~
      svn update
      svn commit
      svn status
      svn add ~~~
      svn diff
      svn annotate <filename>
      svn log -r <change#>
      svn log -v | less
    do's and dont's for branch checkout and checkin
      do NOT reformat other's coding style to make cleaner code
        makes revision tracking a mess
      do not checkin code that does not compile
      do a diff before every checkin
        ensure changes are clear enough for a third party to understand
      limit use of checkin to completed modifications
        we are not using Subversion as a backup utility, just for project revision tracking
      perform a separate checkin for each modification
        do not checkin multiple modifications at the same time
        easier to determine why specific lines of code were changed
  -walk through of bootstrap code
    only called upon if database does not already exist
    it creates the ActiveCampus database by creating a bunch of predefined tables
    other code then populates the tables with data
    the whole purpose of ACE seems to be to make queries of the different fields in these tables
  -step by step'd through README.SUBVERSION
    actually performed the install with my local branch of ActiveCampus
  -created a configuration script for login 'configure-jpachura'
  -step by step'd through README
  -always use dropschema() to remove a table to make sure sequence is also deleted
  -importance of ensuring all data input by user is escaped
    input string can contain embedded code
    activated by using that variable as part of a string concatenation within a line of code
    prevented by safing all variables with escape characters
    applies to all variables that have the potential of coming from an external source (meaning ALL variables)
    implement using Wiisard's wrapper for sprintf() called pgsprintf() [pg for PostGres]

///////////////
// CHECKLIST //
///////////////
-learn Subversion
-learn vim
-learn MySQL
-learn PostGreSQL
-rewriting ace_install/schema.dmp to build PostGres databases
-change all 'auto-increment' code to 'id serial primary key' for database indexing
-conversion to applicable datatype whenever possible
  int for sec's from Linux's epoch to type timestamp
    extract (epoch) from timestamp
    See WIISARD code for how to insert and extract the time as the UNIX "epoch"
-safe [only newly generated code, for now] code by escaping
  before use within concatenations all variables safed with the pgsprintf() wrapper

*******************************************************************************
6/17/04 (6) <- all day but calling it just 6 since working from home
*******************************************************************************
Home
  -last day of school for Jessica & Charlene
  -setup SSH Secure Shell Client accounts for ActiveCampus
    copied entire ActiveCampus trunk onto my laptop
    not proficient using vim
      walked through vimtutor
      requires memorization of all the useful key commands before any real work can get done
      frustrating waste of time at this point
    much more efficient to peruse through this many files using TextPad (Windows-style editor)
  -transported mother-in-law to hospital for post-back surgery appointment
  -starting work in ace_install
    configured TextPad for php syntax highlighting
    read all code in directory to grasp bootstrap process
    witing a schema.dump file to generate PostGreSQL tables
    database tables converted to PostGreSQL
    removed auto-increment code and replaced with id serial
    bootstrap.php compiles past createDb()

*******************************************************************************
6/18/04 (4)
*******************************************************************************
Home
  -continued schema.dump to PostGreSQL
    difficulty with the table generation syntax
    ?KEY, UNIQUE, PRIMARY
    ?columns with NULL and a default value
    ?what is a REFERENCE to what
  -read PostGreSQL documentation
    datatypes similar to java and C
    cannot find purpose of KEY

*******************************************************************************
6/21/04 (6) 9am-4:30pm with breaks
*******************************************************************************
Home
  -having trouble with PHP
    reading web tutorials
    unable to run php files on ActiveCampus2
    temp fix using IE6 to load bootstrap.php so that I can track errors
    requires a wipe each time with startover.php
  -continued conversion of schema.dump to PostGreSQL

///////////////
// CHECKLIST //
///////////////
-why is there a separate 'ids' table?
  shouldn't this be included in 'users'?
-'id' is used both to represent user id and as a generic identifier?
  user id should be represented by 'user_id' rather than 'id'
  could require extensive ACE modifications (expect lots of references to user_id)
-I'm using timestamp to replace MySQL's integer time
  do we need date info or can we use the 'time' data type?
  if the date is important, are we concerned with the time zone 'timestamptz'
  this could also apply for just 'time' as in the 'timetz' data type

*******************************************************************************
6/22/04 (9) 11am-8:15pm
*******************************************************************************
ACE Lab
  -discussed 'text' datatype issue with Charles
    potential security/efficiency issue of using 'text' instead of 'varchar(n)'
    PostGeSQL supports both data types
    text is generic and unlimited size
      more readable code
      less efficient (do not know how much of an effect on ACE it will have)
    are there any security concerns (buffer overflow,etc) with unlimited size of user input?
    even if not a security risk, will this affect current code
      does anything depend on the strings being limited to a certain size?
    decision to stick with replacing ALL 'varchar(n)' datatypes with 'text'
      this is what Steve originally suggested
      the ACE code I have read does not seem to be overly concerned with efficiency
      using text for everything makes the code more interchangeable
  -coverting schema.dump to PostGreSQL
    extremely difficult to work out references in such unfamiliar code
    creating 'best guess' references
      hope is that error messages will point out any mistakes
    PostGreSQL server is down
      making changes blindly until Steve (root) can restart the services
    defaults in old code seem screwy
      sometimes specified, sometimes not
      no consistency
      I am setting defaults for all booleans
      I'm removing NOT NULL
        this could be an issue
        this is an idiot check to keep null values from being entered into the table
          doesn't it do this automatically if a default is specified?
      I am not setting defaults for timestamps
      I am not setting defaults to null
      I am setting defaults for integer (only '0' or '1' so far)
    http://developer.postgresql.org/docs/postgres/ddl-constraints.html
      NOT NULL is an important constraint
      having a default specified only uses the default value if nothing is put in that field
      it does not stop an explicit null value from being entered
      re-adding NOT NULL constraints in the assumption that the code depends on them
      the code is not taking advantage of CHECK constraints
        very useful feature of PostGreSQL that we probably want to make use of
    after looking at a number of documentation examples
      switching back to capitalized key words
      personal preference towards readability of unfamiliar code
      conforms to the standard used in PostGreSQL documentation
    important rule not followed in earlier code
      "A table can have at most one primary key (while it can have many unique and not-null 
      constraints). Relational database theory dictates that every table must have a primary key.
      This rule is not enforced by PostgreSQL, but it is usually best to follow it. "
  -attacked Steve with questions
    almost immediately found multiple flaws
    lowercase only
      conformity with bulk of current code
      code looks cleaner once experienced reading SQL and PHP
      examples used in documentation not really standards
    external creation of index's (to include unique constraints)
    inline primary key constraint
    inline check constraints

*******************************************************************************
6/23/04 (1.5) 1:30pm-3pm
*******************************************************************************
ACE Meeting
  -Postgres conversion (Steve & Jason)
    coverage detector
    record all queries that had to be corrected for post conversion use
    grep -r LAST_INSERT_ID
  -PHP5 upgrade (Steve & Jason)
    should be runnable as is
    minor changes to deal with
  -Locator Server refactoring to PlaceLab (Charles)
  -Lat/Long conversion (Jason)
    NAD27 to NAT83 was the previous conversion
    Lat/Long is the new standard
    currently X/Y -> Lat/Long
    put off feet to metric conversion until last
  -?Map Server upgrade
  -generalize reminders (Charles)
  -map adder (Charles)

*******************************************************************************
6/23/04 (6) 1pm-1:30pm 3pm-8:30pm
*******************************************************************************
ACE Lab
  -talked with Macneil about Aspect Browser
    has an interface with Eclipse
      not released, get from Macneil
    has a standalone called Nebulous
      downloadable from Bill's page
    gives visual of all files containing a regular expression
      like function names, class names, queries , etc.
      nice gui with colored history
        brings up file in question with a point and click
    only use for it I can think of is tracking down queries in ACE
      seeing which files rely on which other files?
    more work than worth
      installed Aspect Browser on laptop
        requires command line to run
        not useful in my environment
      no urge to reinstall (learn) eclipse right now
        good thing to add to my list of things to when bored
  -continued work on converting schema.dump
    goal is to finish by tonight
    this thing is huge & taking forever
    lots of code specific details that I could not have figured out without Steve's assistance
      very frustrating
    not going to finish by tonight
  -Steve & Charles tried to brain-bash me into learning VIM
    so far I have successfully fended their attacks
    we'll see how long I can last :)
  
///////////////////////////
// NOTES for schema.dump //
///////////////////////////
  -- FORMAT:
  -- name datatype [references] [primary key] [not null] [constraints] [default value]
  -- create index name_table_key on table (name);
  -- create unique index name_table_ukey on table (name);

  -- JUDGEMENT CALLS:
  -- ignoring default settings for datatypes converted to 'timestamp','macaddr'
  -- ignoring not null constraint for datatypes converted to 'serial'
  -- ignoring default settings and constraints for foreign keys (references)
  -- XXX interpretted 'int(11) unsigned' to mean positive 'integer check (name >= 0)'
  -- interpretted 'int(11) unsigned' as integer
  -- `backticks` changed to "double quotes"
  --    `to` -> "to"
  --    user -> "user" (keyword)
  -- limited indexing ignored
  --    from -> KEY index_location_index (location_index(20))
  --    to -> create index index_location_index_locations_areas_key on locations_areas (location_index(20));
  -- XXX 'double precision' datatype abbreviated as just 'double'
  -- 'double' not allowed using 'double precision'

  -- ENUMERATE CONSTRAINT:
  -- from -> transportType enum('ICQ','AIM','Yahoo','MSN') not null default 'ICQ',
  -- to -> transportType text check (transportType in ('ICQ','AIM','Yahoo','MSN')) not null default 'ICQ',

*******************************************************************************
6/25/04 (2.5) 12:30pm-3:00pm
*******************************************************************************
Home
  -completed translation of schema database from MySQL to PostGreSQL
  -discovered that backticks are used in Postgres to delineate commandline (shell command) code

*******************************************************************************
6/26/04 (2) 7:00am-9:00am
*******************************************************************************
Plane
	-finalizing format & style of schema.dump file
	-added comments tracking table references for future use
		hopefully they will be useful to someone later
		easier to do now while creating the references
		table structures are fresh now would be much harder to do at a future date

*******************************************************************************
7/12/04 (2.5) 9:30am-12:00pm
*******************************************************************************
Home
	-caught up on ton of developer email after being out of touch for two weeks
	-read up on PostGres and PHP
	-finished VIM tutorial
	-continued code conversion

*******************************************************************************
7/13/04 (8.5) 1:00pm-9:30pm
*******************************************************************************
ACE Lab
	-commenced troubleshooting of query errors
		queries not compatible with postgres syntax and/or datatypes
		using startover.php & bootstrap.php to locate errors
	-lots of difficulty with tags
		this entire project (ActiveCampus) seems to have no documentation
		tags mandatory for trying to figure out what the code is trying to do
		needs a javadoc style of available functions and the files they are defined in
		almost impossible to track manually
	-learning to use gvim through an Xwindow
		running Hummingbird Xmanager for Xwindow services
			pain in the ass to install - what a major waste of time
	-VIM quick reference quide printout appears to be in Egyptian
		programmers seem to be at least as good as lawyers in "job justification"
			unnecessarily creating confusion to the point 
			that only they can effectively operate the systems they produce
			thereby justifying their own continued existence
			god I hate lawyers
	-getting nowhere on this 2 hours and still trying to correct the first error
		sql_min_fn.php line 85
		got help from Charles
		php.net has documentation on standard php function
		trigger_error(varOB($query));
			uses ACE's error handler
				which creates its own errors because of its own timestamp
		print(varOB($query));
			prints out ALL the queries before it finally breaks on the error
	-?conversion from timestamp to integer?
			pg_query(pgsprintf("update client_connections set next_heartbeat_timestamp = timestamptz 'epoch' + '%s' * interval '1 second' where id = '%d'", $nextHeartbeatTimestamp, $this->id));
	-?conversion from integer to timestamp?
			function date_iso8601($timestamp) {
				$iso8601Date = date('Y-m-d\TH:i:s', $timestamp);
				$fractionalSeconds = ($timestamp - floor($timestamp)); // XXX modf
				$iso8601Date .= preg_replace("/^0\./", ".", sprintf("%.7f", $fractionalSeconds));
				$iso8601Date .= date('O', $timestamp);

				return $iso8601Date;
			}
	-useful command
		grep -r extract *

*PostgreSQL timestamp note

As mentioned above PostgreSQL does not have a timestamp, you must extract the epoch from the timestamp you stored in your database which is in PostgreSQL Timestamp format.

Getting the PostgreSQL Timestamp into your database from a form can be accomplished with a hidden form object having the value attribute set to "now()"
----------------------------------------
e.g.
<input type='hidden' name='pgsql_timestamp' value='now()'>
----------------------------------------

Now if you need to get the Unix timestamp from the PostgreSQL timestamp which is stored like
"2002-06-19 16:51:09.83662-07"
you can do the following SQL query
----------------------------------------
e.g.
"SELECT EXTRACT(EPOCH FROM pgsql_timestamp) AS unix_timestamp FROM my_pgsql_table;"

now unix_timestamp = "1024815600"
----------------------------------------
If not clear, "pgsql_timestamp" would be the name of the column which holds the PostgreSQL timestamp, it would be returned as "unix_timestamp" and would be the Unix Timestamp.

*Note your table still has the timestamp in PostgreSQL format 

	-moved date_iso8601 from bootstrap.php to main_conf
		needs to be in its own function file
		need Steve's help
	-useful code for line 85 of sql_min_fn.php to see error causing query
		print(varOB($query)); //only for troubleshooting
	-modified profiles_sql_fn.php
	-lots of datatype corrections of timestamps and booleans
	-completely rewrote ids_sql_fn
	-modified dbAddNewIds function of log_data_access_fn
	-talked with Steve
		no need for iso8601 conversion function
			can be accomplished with timestamp 'epoch' + seconds...
			grep WIISARD code for epoch to find examples
			remove iso8601 from main_conf
		defaut values for inet and timestamp
			doesn't really matter they 'should' be overwritten in every case
			timestamp -> 'epoch'
			inet -> 0.0.0.0
	-create a timestamp from an integer
		timestamptz 'epoch' + '%s' * interval '1 second'
	-create an integer from a timestamp
		extract(epoch from connect_timestamp) as connect_timestamp
	-cleaner if these were made into separate function which performed the conversions
	-after reading documentation and standards I'm going with
		current_timestamp to replace the MySQL time() function calls
	-commented out zip code insertions 'zctas' in bootstrp.php to aid TS&R
	-current_timestamp does not work as expected
		changed to 'timestamp now()'

*******************************************************************************
7/14/04 (8) 2pm-10pm
*******************************************************************************
ACE Lab

	-boolean to integer conversion
		select CASE when <field_name> THEN 1 ELSE 0 END as <field_name>
	
	-created schema.dump.PATCH file to fix code after completion of port to PG

*******************************************************************************
7/15/04 (11) 1:30pm-11:30pm
*******************************************************************************
ACE Lab
	
	-see hard-copy notes and subversion revision history

*******************************************************************************
7/16/04 (7.5 6.5) 8:00am-3:30pm 6:30pm-1am
*******************************************************************************
HOME

	-last table done jabber_sessions
	
	-revision problems
	
*******************************************************************************
7/19/04 (4) 10:00am-2pm
*******************************************************************************
HOME
	-rolling back to square one
	-removing new datatypes from schema.dump
	-focusing on code transition to PG
	-conversion to PG datatypes will be completed as a separate step
	
	-much difficulty with README to do re-install
		no descriptions of how anything is 'supposed' to work
			no way to know when there is a problem with the install
		assumes full installation of a variety of Unix developer tools 
			requires stopping to search and download each app from the web if on windows
	-installed Cygwin autoconf + automake
	-installed Cygwin lynx
	-I think it worked, no way to tell
	-nope still getting unexplained errors in setup process
	
Warning: main(/home/jpachura/public_html/ActiveCampus-postgresql//active_campus_explorer/conf/main_sql_conf.php): failed to open stream: No such file or directory in /home/jpachura/public_html/ActiveCampus-pg/ace_install/installLib.php on line 45

Fatal error: main(): Failed opening required '/home/jpachura/public_html/ActiveCampus-postgresql//active_campus_explorer/conf/main_sql_conf.php' (include_path='.:/usr/share/php:/usr/share/pear') in /home/jpachura/public_html/ActiveCampus-pg/ace_install/installLib.php on line 45

	-damn I'm getting tired of wasting my time trying to figure out how to use these non-intuitive Unix tools
		frustrating
		frustrating
		frustrating
	
*******************************************************************************
7/21/04 (8) 1:30pm-9:30pm
*******************************************************************************

*******************************************************************************
7/22/04 (9) 1:00pm-10:00pm
*******************************************************************************

*******************************************************************************
7/26/04 (2) 8:30am-10:30am
*******************************************************************************
Charlene has swimmer's ear
Jessica has an inner ear infection

	-nothing interesting with the code
	-taking the frustrating steps of becoming proficient with vi
		why oh why have an editor that does not make use of the mouse??
		it is so painful to not be able to drag and drop
		no simple highlight,cut, and paste
	-stuck using GVIM executed from an SSH Secure Shell terminal through X11 tunneling
		necessary to edit code on the server with vi
		which is necessary for all of the leaping about through the code required
		code has to be on server for access to php rendering services (since Apache with php services has been a pain getting to work right on laptop)

*******************************************************************************
7/28/04 () 7:30am-
*******************************************************************************
wife in surgery

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************

*******************************************************************************
7//04 () 
*******************************************************************************