Live in southern Ontario? Attending dinner meetings is free.

PegaSoft's September Dinner Meeting

Attendence

Ken B, Mike H, Ian Z, Giles O, Lorie L, Alan C
Dan B was unable to attend due to work.
Colin M was unable to attend.


Meeting Business


PegaSoft Orientation

There had been little PegaSoft activity over the summer.  Ken opened the
meeting by talking about PegaSoft's activity in Toronto over the past 5
years.  Each person around the table took a turn to introduce themselves.


Meeting Location

Since PegaSoft moved out to the west end of the city, Ken had been trying
to find a location that served good food and had an atmosphere quiet enough
for discussion.  David Patrick of the Linux Caffe agreed to keep his cafe open
after-hours for PegaSoft to conduct a meeting.  A round of applause was
given to Mr. Patrick.

Alan C said that the Toronto Asterisk group meets in the North York city
hall for free and goes cross the road to a restaurant to eat afterward.
The location was off the 401 near Yonge street.  Ken B said he would
bring up the suggestion on the mailing list.

Mr. Patrick said that there was a location near the Linux Caffe that could
handle up to 100 people.

Alan C said he might have work for PegaSoft members.  He would talk with
Ken later about the details.

Ian Z said he might be available to do a Perl talk at an upcoming PegaSoft
meeting.


PegaSoft Pin-on Buttons

Thanks to Colin M for designing and printing up personalized PegaSoft pin-on
buttons for meetings.  The buttons will be given out at a future meeting.


Open Forum - Industry Quote

[The views are those of the participants.]


"Open source software companies are great at telling their customers about
issues, but we're not so good at telling people when they're not affected.
We wrote up a proposal for NIST, and they thought it was a great idea."
-- Mark Cox, security response team director for Red Hat, on the proposal
to create a "vendor statement" database to inform users when software is
not affected by a security advisory (ZDNet UK)

The usefulness of a vendor statement was questioned.  Ian Z pointed out that
security statements go out in mailing lists for the various distributions.
When an open source project has a bug, it's the responsibility of the
distribution vendors to report the bug.  Although people contacting Red Hat
about potential bugs can be directed by Red Hat to the vendor statement
database, sending people announcements that bugs don't affect them isn't
useful.

Ken B proposed that online update tools could send warnings for software
packages that contained bugs that had not been patched yet.  Ian Z said that
a user depending on the server only had the choice of taking down the server
(or temporarily disabling the package) or wait for a patch.  Since disabling
the server meant that the server was unavailable for the business, this is
seldom an acceptable option...they have to wait for the patch anyway.


Open Forum - Linux in the News

[The views are those of the participants.]


OpenOffice has said only one actual bug was discovered, which has been
fixed already. But the research, by the French Ministry of Defence, also points
out that many security flaws have already been discovered in Microsoft Office
applications, claiming that these are "easily transportable to OpenOffice".
(ZDNet UK)

Ken B said to create compatibility with another product, a project like
OpenOffice must duplicate the bugs as well as the features.  This aspect
of OpenOffice was discussed previously at PegaSoft meetings.

Alan C reminded everyone of the adage "we have no bugs, only features".

Ian Z said that part of the problem was training users to use the software
properly and be aware of security risks.

Ken B pointed out that any macro virus or similar attack depended on the
underlying operating system.  Linux is more secure than Windows but it is
also a niche market.  As long as Linux is a niche market, it's not
worth the trouble of virus writers to attack it.  This might be an argument
against the Linux Standard Base project.


Software Demonstration - Unison (Ken B)


Ken B demonstrated the unison file synchronization program on his laptop.
Unlike rsync, unison is bi-directional, copying files between two machines
so the contents of both machines are identical.  Unison has a graphical
display showing what it will do before it does it.  When there is a conflict
caused by one file being changed on both machine, unison will take no action
unless the user specifies what to do (to prevent data loss).  Unison ignores
timestamps making it incompatible with source control tools like CVS or
Subversion.

Other synchronization tools include rsync and rdiffbackup.

Alan C asked about mirroring a drive with rsync which sometimes reported
errors when files were in use.  Ken B said unless RAID 1 was used so the
drive is mirrored at a driver level, there was always a chance some files
might be missed when mirroring.  However, the risk was minimal for a
non-critical backup--perhaps a lost email message or two.

Ken B said he had his Window's "My Documents" folder as a short-cut to
a DVD-sized partition so he was guaranteed to be able to back up his
personal files on a writable DVD.  Most of his personal Linux files were
duplicated on his laptop.  Ian Z pointed out software packages could
always be reinstalled but lost personal data was irretrievable.


PegaSoft Member Projects

 
Business Shell (BUSH) (Ken B)
                                                                                
Working on support for dynamically loadable, user-defined modules and, in
particular, the Ada use command so that the names of modules could be
optional in subprogram references.  The effort is hampered because the byte
code has no notion of types: it is difficult to know in "p.x" if "p" is a
loaded module, built-in module or a record reference...and how that affects
"use".  Modules must be handled differently in the symbol table because they
represent scope changes.

Ken gave a very brief demonstration of BUSH.

Giles O pointed out that Ada's strong typing features meant more work for
programmers.  Ken B said that Ada programmers considered that a feature, not
a bug.  (If someone is creating a missile guidance system, do you really want
to reverse the X and Y axis without receiving an error message?)

Ian Z said that there were many shells available.  Ken explained the history
of BUSH, detailing how it was different because it was a shell with a
high-reliability, secure procedural language behind it and how it made
scripts upgradable to GCC applications or Java byte code.  He also described
BUSH's built-in debugger features.

Ken also said that BUSH had become a bit of an albatross around his neck:
he was torn between "finishing" BUSH and leaving it as a personal project
that was open for other people to use.  Although BUSH contains hundreds
of built-in functions, there were hundreds of other features that could be
added.  The project had been going on for 5 years and it was simply too
big for a single developer to manage effectively.

There had been an email from a user wanting SuSE 10.1 support.  The
development version of BUSH supports AMD Athlon and DEC Alpha hardware as
well as SuSE 10.1.  However, the symbol table is in disarray because of the
work on loadable modules so it was impossible to immediately distribute a
new version of BUSH with SuSE 10.1 support.

Giles O said BUSH may need a feature plan.  It had already grown far beyond
its initial concept.

Ken mentioned there were other attempts to create language-based shells,
such as the Perl shell and the Bean shell (based on Java).  Only BUSH had
the high-reliability and high-security of GCC Ada, necessary for serious
business, scientific and military applications.

Lorie L asked about GCC Ada's availability.  It was built into Red Hat,
an option in SuSE and was available for Debian.  It is a native GCC language
and is included in the GCC sources: it can be enabled like any other GCC
language when building GCC from sources.


Online Game Project (Ken B)

Ken has been experimenting with OpenGL under C.  OpenGL is difficult to use
because there is little definitive documentation on the web: many of the
examples are toy examples that don't assume a complex display system with
a movable camera, etc.  An application must patch together information from
different sources to get a complete working example.

Ian Z asked about OpenGL.  Ken B said he might do an OpenGL discussion at
a later PegaSoft meeting.  OpenGL was very low-level in the sense that it
had no camera operations and no operations to position object: instead the
user was expected to reposition the "universe" as appropriate to create
the desired effect.  This also meant that operations in OpenGL must be
executed in a specific order or the display would not be correct.  Ken's
simple animated demo required over 1500 lines of C.


Discussion: Introduction to SQL (Ken B)


- very simple first lesson in SQL.  many other topics that could be covered. 

- databases (or, more properly, DBMS)
  - DBMS - database management system
    - MySQL, PostgreSQL, Firebird, dbm and others available for Linux
  - MySQL is the most popular.  PHP used to native support for MySQL.  Many
    pre-built applications, particularly PHP, support only MySQL.
  - PostgreSQL has the most features, easiest to use database professionals,
    several Canadians working on the project.
  - both MySQL and PostgreSQL have good performance and are widely available
  - common software setup mistakes
    - failure to index the tables to speed up searches
    - failure to configure database server for your hardware (RPM files are
      configured for minimal systems)

- relational databases terminology
  - data is arranged in lists called tables
  - each row is a record with columns/fields
  - tables are collected into databases
  - schema is a description of objects created under an account (informally,
    the table layouts without the data)
  - databases require accounts with permissions (nothing to do with Linux
    logins)
  - software usually has clients(s) which talk to the database server
    - the server manages the databases
    - commands or queries for results are sent to the client from the server
    - mysql is the built-in MySQL command line client
    - psql is the built-in PostgreSQL command line client
    - variety of 3rd party clients, including graphical or web-based ones
      (pgaccess, phpmyadmin, webmin's database modules)
    - examples here use the mysql program

- SQL
    - Structured Query Language, developed by IBM in 1970s
    - most popular language for retrieving data from a database
    - although based on an ANSI standard, many different variations
    - pronounced "sequel"...but many professionals refer to it "S.Q.L."
    - sometimes referred to 4GL - fourth generation language
      - dbase 2 0 had commands like move to top, next record, etc. describe
        the search
      - in SQL, describe what you are looking for, not how to find it or do it
      - reads somewhat like English, basics easy, making it popular
    - limitations and problems
      - some queries are harder to express in SQL than in other languages
        - there is a right way and a wrong way of creating trees
          - either way, SQL is based on lists not trees
      - interfacing data types with other computer languages (e.g. NULL)
      - no consistent syntax between similar commands (e.g. INSERT and UPDATE)
      - more info at http://www.dbdebunk.com/
    - procedural language - an extension of SQL for writing functions
      - adds subroutines, if statements, loops, variables, cursors (current
        position in tables)
      - usually executed by triggers, a condition attached to a table for
        running the function

- creating databases
  - format: create database db_name;
  - semi-colon is required to end the command

mysql> create database examples;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| examples           |
| mysql              |
| test               |
| tmp                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use examples;
Database changed

mysql> show tables;
Empty set (0.00 sec)

- creating tables
  - format: CREATE TABLE ( [name type] [,name type]);
  - type:
    - column type - varchar(n) variable length characters from 1 to n in
      length, integer, float, date, timestamp, boolean, blob (arbitrary data
      e.g. pictures) and others
    - not null - no empty positions
    - primary key - a unique column, the master index for the table
    - unique - column values must not be duplicated
    - default x - x is the default value


mysql> create table employees (
    -> employee_number integer unique not null,
    -> first_name varchar(32) not null,
    -> last_name varchar(32) not null,
    -> phone varchar(16),
    -> salary integer not null
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+--------------------+
| Tables_in_examples |
+--------------------+
| employees          |
+--------------------+
1 row in set (0.00 sec)

mysql> show create table employees;
+-----------+----------------------------------------------------------------------------------------------+
| Table     | Create Table                         |
+-----------+----------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
  `employee_number` int(11) NOT NULL,
  `first_name` varchar(32) NOT NULL,
  `last_name` varchar(32) NOT NULL,
  `phone` varchar(16) default NULL,
  `salary` int(11) NOT NULL,
  UNIQUE KEY `employee_number` (`employee_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


- common mistakes
  - failure to use "NOT NULL" - missing values are handled specially and can
    create unexpected results in some queries.  They are not the same as a
    blank or zero.

- adding rows
  - format: INSERT INTO table [(names)] VALUES ( value1 [,value2...])
  - NULL represents a missing value (or one that should use the default for
    the column if a default was assigned)

mysql> insert into employees (employee_number, first_name, last_name, phone, salary ) values (1, 'Ken', 'Burtch', '905-555-5555', 60000 );
Query OK, 1 row affected (0.08 sec)

mysql> insert into employees (employee_number, first_name, last_name, phone, salary ) values (2, 'Mel', 'Wilson', '416-555-5555', 50000 );
Query OK, 1 row affected (0.00 sec)

mysql> insert into employees (employee_number, first_name, last_name, phone, salary ) values (3, 'Dan', 'Braun', NULL, 70000 );
Query OK, 1 row affected (0.00 sec)

mysql> insert into employees (employee_number, first_name, last_name, phone, salary )
values (3, 'Dan', 'Braun', NULL, 70000 );
ERROR 1062 (23000): Duplicate entry '3' for key 1

mysql> insert into employees (employee_number, first_name, last_name, phone, salary ) values (4, NULL, 'Smith', NULL, 70000 );
ERROR 1048 (23000): Column 'first_name' cannot be null

- common mistakes
  - columns names are optional but in programs they should always be included
    in case the format of the table changes

- basic queries
  - a request to return data from tables
  - format: SELECT [columns or *] FROM table [WHERE condition];
  - * is a short-form for all columns and only works with a select
  - "Select" can be thought of as "Print"

mysql> select 2+2;
+-----+
| 2+2 |
+-----+
|   4 |
+-----+
1 row in set (0.00 sec)

mysql> select * from employees;
+-----------------+------------+-----------+--------------+--------+
| employee_number | first_name | last_name | phone        | salary |
+-----------------+------------+-----------+--------------+--------+
|               1 | Ken        | Burtch    | 905-555-5555 |  60000 |
|               2 | Mel        | Wilson    | 416-555-5555 |  50000 |
|               3 | Dan        | Braun     | NULL         |  70000 |
+-----------------+------------+-----------+--------------+--------+
3 rows in set (0.00 sec)

mysql> select first_name, last_name, phone from employees where first_name = 'Ken';
+------------+-----------+--------------+
| first_name | last_name | phone        |
+------------+-----------+--------------+
| Ken        | Burtch    | 905-555-5555 |
+------------+-----------+--------------+
1 row in set (0.00 sec)


mysql> select first_name, last_name, phone from employees where phone is not null;
+------------+-----------+--------------+
| first_name | last_name | phone        |
+------------+-----------+--------------+
| Ken        | Burtch    | 905-555-5555 |
| Mel        | Wilson    | 416-555-5555 |
+------------+-----------+--------------+
2 rows in set (0.00 sec)

- common mistakes
  - * is for all but is bad form in a computer program since the table format
    might change
  - "WHERE something = NULL" (wrong) instead of "WHERE something IS NOT NULL"
    (right)

- compound or nested queries
  - format: SELECT ... WHERE column IN ( SELECT ...)

mysql> select first_name, last_name from employees where salary in ( select salary from employees where salary > 50000 );
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Ken        | Burtch    |
| Dan        | Braun     |
+------------+-----------+
2 rows in set (0.05 sec)

- simple joins
  - combining the results from two tables
  - simple "inner join" cross-references multiple tables but discards
    items that don't match
  - since tables may have columns with the same name, it's a good idea to
    specify which table the column comes from using "table.column_name"
    instead of "column_name"
  - optional aliases allow short forms for the table names
  - format: SELECT [stuff] FROM table alias, [,table alias] WHERE [conditions]

mysql> select * from employee_rating;
+-----------------+--------+
| employee_number | rating |
+-----------------+--------+
|               1 |    3.5 |
|               2 |   NULL |
+-----------------+--------+
2 rows in set (0.00 sec)

mysql> select e.first_name, e.last_name, r.rating from employees e, employee_rating r where e.employee_number = r.employee_number;
+------------+-----------+--------+
| first_name | last_name | rating |
+------------+-----------+--------+
| Ken        | Burtch    |    3.5 |
| Mel        | Wilson    |   NULL |
+------------+-----------+--------+
2 rows in set (0.00 sec)

- functions
  - hundreds of functions, no standards between different databases
  - calculated columns can be named using AS
  - aggregate functions - used to create totals and summaries in the first part of the select

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2006-09-20 15:16:07 |
+---------------------+
1 row in set (0.05 sec)

mysql> select upper( concat('Mr. ', last_name) ) as formal from employees;
+------------+
| formal     |
+------------+
| MR. BURTCH |
| MR. WILSON |
| MR. BRAUN  |
+------------+
3 rows in set (0.00 sec)

mysql> select count(*) as matches from employees e, employee_rating r where e.employee_number = r.employee_number;
+---------+
| matches |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

- updates
  - format: UPDATE table SET name = value [, name = value] WHERE [condition]
  - the format is completely different from insert or select


- programming issues
  - complex queries can be several pages long
  - mixed languages: PHP, JavaScript, HTML, SQL in a typical web application
  - functions vary between databases but PHP, Perl, etc. have math and string
    functions
  - complicated queries can be several pages long
  - insert versus update - different format for essentially same operation


 PHP example:

  $db_host = 'localhost';
  $db_user = 'website';
  $db_password = 'sniffles12';
  $db_database = 'usoffice';

  // Establish a connection

  $db_connection = mysql_connect( $db_host, $db_user, $db_password );
  if ( ! $db_connection ) {
     print( "The database on $db_host is currently unavailable.  The system may be down for maintenance." );
     print( mysql_error() );
  } else if ( !  mysql_select_db( $db_database ) ) {
     print( "The database on $db_host is currently unavailable.  The system may be down for maintenance." );
     print( mysql_error() );
  }

  // Run a query to find users still logged in after a maximum time limit
  // And mark them as logged out

  $query = "SELECT u.id FROM users u,game_controls c WHERE TIME_TO_SEC(now()-u.last_page_access) > c.int_value AND c.control = 'Logout Time'";
  $res = mysql_query( $query );
  while ( $row = mysql_fetch_array( $res ) ) {
     $query = "UPDATE users SET online = 'N' WHERE id = " . $row[0];
     $res2 = mysql_query( $query );
     if ( ! $res ) {
        print "Logout expire failed\n";
     }
  }


Next Dinner

Unless otherwise notified, the next dinner meeting is Thursday,
October 19, 2006 at the Linux Caffe.  Topic: Introduction to JavaScript