Pingdom has put together a list of some signs from the real world that are “geek + graffiti”. Here are our faves and check out the entire list on the Pingdom blog.

This first one is about rebooting life…

 Grate not found?

During the first half of day 3, we finished up the material that will be present on the MySQL Developer I examination. I’ve included my notes below:

Day 3

Manipulating Table Structure

Creating Tables

You can create tables from existing tables by adding “SELECT” OR “LIKE” queries to your CREATE TABLE statement.

Some examples:

• CREATE TABLE `myTable` SELECT * FROM `myExistingTable`;
  • This will create a duplicate of the data in the existing table, but will not copy additional properties of the table, indices, etc. It will copy the column names, data types, etc., though.
• CREATE TABLE `myTable` SELECT * FROM `myExistingTable` WHERE Population > 2000000;
  • This will create a duplicate of the existing table (with the same caveats listed above), but will only populate it with specific data that is pulled from the SELECT query
• CREATE TABLE `myTable` SELECT * FROM `myExistingTable` WHERE 0;
  • This will create a duplicate of the existing table without populating any data. Again, it will not copy additional properties, etc.
• CREATE TABLE `myTable` SELECT `col1`, `col2` FROM `myExistingTable`;
  • This will create a new table using only the columns that are requested in the SELECT query and will populate the table
  • You can specify “table-wide” features in the CREATE TABLE statement (ENGINE, CHARSET, COLLATE, etc., but cannot specify column-specific information, like PRIMARY KEY, INDEX, etc.)
• CREATE TABLE `myTable` LIKE `myExistingTable`;
  • This will create an exact duplicate of the structure of your existing table
  • Foreign key constraints are not copied with LIKE statements

Temporary Tables

You can create a temporary table for temporary use.

• Temporary tables are only available during the current session. They will be removed from the server when your session is terminated for any reason (time-out, server shuts down, you close your connection, etc.)
• Temporary tables are only visible to the current session
  • They do not show up in the SHOW TABLES statement
• The name of a temporary table can be the same as the name of an existing table in your database
  • Doing so will cause you not to be able to access the original table while the temporary table exists

Altering Table Structures

• You can add new columns to your table using the ADD statement
  • ALTER TABLE EU_Countries ADD Id INT(6) NOT NULL;
  • ALTER TABLE EU_Countries ADD Id INT(6) NOT NULL AFTER Name;
  • http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
• You can remove columns from your table using the DROP statement
  • ALTER TABLE EU_Countries DROP Id;
• You can modify column properties
  • ALTER TABLE EU_Countries MODIFY NewPopulation BIGINT(12) NOT NULL;

You can perform multiple alterations at the same time, adding, dropping, modifying, etc. all in one statement.

If you have multiple operations to perform, you should make sure to consolidate them into a single ALTER statement, because each ALTER statement completely rebuilds the table.

During execution of an ALTER statement, a write-lock is enforced on the table, but users can still read the information.

You can also use the CHANGE clause instead of the MODIFY clause, but it has a different syntax:

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Renaming Tables

You can rename a table with an ALTER TABLE statement, as well.

ALTER TABLE t1 RENAME to t2;

Or, you can simply use the RENAME TABLE statement:

RENAME TABLE t1 TO t2;

Using the RENAME TABLE statement, you can rename multiple tables at once:

RENAME TABLE t1 TO t2, t2 TO t1, tmp TO t2;

Removing Tables

Using the DROP command, you can remove a table:

DROP TABLE t1

You can drop multiple tables at once

Foreign Keys

A foreign key is a column in a table that points/refers to a column in a parent table

• Foreign keys should be indexed
• InnoDB engine supports foreign key constraints, protecting the integrity of those foreign keys
  • In order to take advantage of the foreign key constraints, both the parent table and the child table must use the InnoDB engine
  • CREATE TABLE CountryParent
    (Code CHAR(3) NOT NULL,
    Name CHAR(52) NOT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB;CREATE TABLE CityChild (
    ID INT NOT NULL AUTO_INCREMENT,
    Name CHAR(35) NOT NULL,
    CountryCode CHAR(3) NOT NULL,
    PRIMARY KEY(ID),
    FOREIGN KEY(CountryCode)
    REFERENCES CountryParent(Code)
    ON UPDATE CASCADE
    ON DELETE CASCADE
    ) ENGINE = InnoDB;
  • Using ON UPDATE CASCADE will cause all of the child’s information to be updated automatically if the parent’s information is updated
  • Using ON DELETE CASCADE will cause all of the child’s information to be automatically removed if the parent’s information is removed
• Both columns (the parent table’s column and the child table’s foreign key column) should have the same data type
• http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Manipulating Table Data

Inserting information into tables

• Using the INSERT statement, you can insert new rows into a table
  INSERT INTO table_name [(column_list)] VALUES (value_list);
• You can insert multiple rows of information with a single INSERT statement:
  INSERT INTO table_name [(column_list)] VALUES (value_list1),(value_list2),(value_list3);
• You can use a different syntax for the INSERT statement:
  INSERT INTO table_name SET column_name=’value‘, column_name=’value‘…;
• You can also insert information from one table into another table:
  INSERT INTO table2 SELECT * FROM table1;
  INSERT INTO table2 [(column_list)] SELECT [(column_list)] FROM table1;
• LAST_INSERT_ID() will retrieve the last auto_incremented id from the last insert that you performed
  • If you perform a multiple row insert, LAST_INSERT_ID() will return the first ID from your insert statement, rather than retrieving the highest value

Removing information from tables

• Using the DELETE statement, you can remove existing rows from a table
• DELETE FROM table_name [WHERE where_condition][ORDER BY…][LIMIT row_count];

Altering information in tables

• Using the UPDATE statement, you can change the information in specific rows
• UPDATE table_name SET column_name = column_value [WHERE…] [ORDER BY…] [LIMIT…];
• You can use the REPLACE INTO command to perform updates and inserts at the same time if you name a PRIMARY KEY or UNIQUE element within your column list
  • If an item specified in your values already exists as a unique item in your table, that row will be deleted and a new one will be created with the new information you supply
    • It will not use any of the information that was in the previous version of the row. It will only insert the information you supply
  • If no match is found, it will insert a new row
• INSERT with ON DUPLICATE KEY command
  • http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Emptying a table

TRUNCATE TABLE will efficiently empty out a table

Transactions

What is a transaction?

• When concurrent statements are needed
• Mechanism for grouping multiple statements
  • groups multiple statements together that will all be executed together
• All or none succeed
  • If an error occurs on any of the statements within your transaction, then the entire transaction will fail
• Execute if all good
• Cancel if error or incomplete

A good example of transactions is a bank transfer

• In order to transfer funds from a checking account to a savings account, it needs to be handled as a single transaction:
  • First, you need to make sure that there are sufficient funds in the checking account
  • Next, you need to withdraw the money from the checking account
  • Finally, you need to add that money into the savings account
• If any of those actions fail, you need to void the entire transaction

You can use the SHOW ENGINES command to see which database engines support transactions

ACID

• Atomic
  • All statements execute successfully or are canceled as a unit
• Consistent
  • Database that is in a consistent state when a transaction begins, is left in a consistent state by the transaction
  • If you subtract something from one area and add it to another area, you need to be sure that the total is still the same as it was before you started
• Isolated
  • One transaction does not affect another
• Durable
  • All changes made by transaction that complete successfully are recorded properly in database
  • Changes are not lost

Transaction Commands

• START TRANSACTION
  • Begins the transaction, meaning that any statements that fall between “START TRANSACTION” and “COMMIT” will be treated as a single transaction
  • START TRANSACTION temporarily suspends AUTOCOMMIT, meaning that nothing will be committed until you explicitly commit the information
  • Is only necessary if AUTOCOMMIT is turned on
  • Can be aliased as “BEGIN” or “BEGIN WORK”
  • Whenever you use “START TRANSACTION”, it implicitly commits any items that came before
• COMMIT
  • Execute and finalize all of the statements in the transaction
• ROLLBACK
  • Cancel all of the statements that are part of the transaction
• SET AUTOCOMMIT
  • AUTOCOMMIT is, by default, set to 1
  • If AUTOCOMMIT is turned on, each statement is treated as a transaction and will be automatically committed if the statement executes successfully
    • Statements that include multiple commands will be rolled back entirely if anything within that statement fails

Isolation Levels

• Concurrent transactions can cause problems
• Storage engines implement isolation levels
  • Controls level of visibility between transactions
  • May vary per database servers
• Three common problems
  • “Dirty” read - seeing changes that have not been committed
  • Non-repeatable read
  • Phantom row - rows that have been added from another thread during your transaction
• InnoDB implements four isolation levels
  • Read uncommitted
    • You can see changes from another thread’s transaction that have not yet been committed
  • Read committed
    • You cannot see changes that have not been committed, but after it’s been committed, you will see the change, even if you have read the row during your transaction
  • Repeatable read
    • You will not see changes from others’ transactions on any rows that you have read during your transaction
  • Serializable
    • Other transactions will not affect your transaction in any way. Rows selected by one transaction cannot be changed by other transactions until the original transaction has completed
• Setting the isolation level
  • Use the –transaction-isolation option
    • SET TRANSACTION ISOLATION LEVEL
    • SET TX_ISOLATION
• MVCC - Multi-version concurrency control
  • A method of isolating sessions from each other during transactions
  • http://en.wikipedia.org/wiki/Multiversion_concurrency_control

Locking Concepts

• A locking mechanism prevents problems with concurrent data access
• Locks are managed by the server
  • Allows access to one client and locks others out
• InnoDB supports two types of locking
  • Lock in share mode - locks each row with a shared lock
    • Other people can still read the row(s), but no one (including yourself) can update anything within those rows until all locks are released
    • If you attempt to read a row by using a WHERE clause that includes something other than an index, then all rows in the table will be locked
    • SELECT * FROM Country WHERE Code=’AUS’ LOCK IN SHARE MODE\G
  • For update - locks each row with an exclusive lock
    • Other people can still read the row, but only you have permission to update the row until you release the lock
    • SELECT counter_field FROM child_codes FOR UPDATE;
      UPDATE child_codes SET counter_field = counter_field + 1;

I didn’t take any notes during the first day of class, as we really only went over a lot of introductory information. However, I started taking notes at the beginning of day two, and tried to be pretty thorough. I hope they help a bit.

Day 2

Data Type Overview

Three Major Categories:

• Numeric
  • Integer
    • Tinyint
      • 1 byte
    • Smallint
      • 2 bytes
    • Mediumint
      • 3 bytes
    • Int
      • 4 bytes
    • Bigint
      • 8 bytes
      • Bigints are used for all internal mathematic operations within MySQL
  • Floating-point (approximate numbers)
    • Float (4 bytes)
    • Double (8 bytes)
    • Floats are generally inaccurate. For instance, inserting a value of “0.99″ will actually create a value of 0.99000000953674
    • Floats will not be limited by the amount of digits you “set” when creating the column. The amount of digits you set is simply used for formatting
  • Fixed-point
    • Decimal
    • Fixed
    • Fixed-point digits will be limited by the amount of digits you “set” when creating the column. For instance, if you specify a column as DEC(4,2), it will not allow you to store the number 100. The largest value it can store is 99.99
  • BIT
    • Column width is the number of bits per value
• Character/Binary (strings)
  • Binary elements are made up of characters, although you don’t generally manipulate the individual characters
  • CHAR - Stores a fixed number of character locations, no matter what value you give it.
    • Limited to a length of 255 characters
    • More efficient in MyISAM, because you are storing a set number of characters each time - also helps prevent fragmentation issues
  • VARCHAR - Stores a variable number of characters, up to the limit that you set when creating the table
    • If the strictness mode is turned on, it will throw an error when attempting to insert a string longer than your limit
    • If strictness mode is turned off, it will truncate the string before inserting it into the database
    • Limited to a maximum length of 65,555 characters
  • TINYTEXT - up to 255 characters
  • TEXT - up to 65,535 characters
  • MEDIUMTEXT - up to 16,777,215 characters
  • LONGTEXT - up to 4,294,967,295 characters
  • ENUM - enumarated values
    • You specify the possible values, which are treated as their numerical equivalents when evaluated
    • You can store up to 65,535 possible values for an enumerated column
    • Only one of the enumerated values can be selected in each row
  • SET - list of string values
    • You specify the possible values, as you do in ENUM
    • However, with SET, you can select multiple values for each row
    • The numerical values of a SET are stored as BIT switches rather than consecutive numbers
• Temporal
  • TIME
    • HH:MM:SS
    • Is not necessarily an indication of time of day. This can accept times longer than 23:59:59, because it is simply an indication of amount of time
    • Can be negative or positive
    • ‘-838:59:59′ to ‘838:59:59′
  • YEAR
    • Two or four digits
    • Indicating a two-digit value will be interpreted in a range from 1970-2069
    • 1901-2155 (for year(4)), 1970-2069 (for year(2))
  • DATE
    • ‘YYYY-MM-DD’ - can only accept dates in this format
    • ‘1000-01-01′ TO ‘9999-12-31′
  • DATETIME
    • ‘YYYY-MM-DD HH:MM:SS’ - can only accept datetimes in this format
    • Time in this data type is limited to time of day. Highest value is 23:59:59 and lowest value is 0
    • ‘1000-01-01 00:00:00 to ‘9999=12-31 23:59:59′
  • TIMESTAMP
    • If given no value when creating or updating a timestamp column, it will automatically assign the current system date/time
    • No point in having more than one timestamp column, as they will all end up with the same value
    • ‘1970-01-01 00:00:00′ to mid-year 2037

You want to choose the most appropriate data type (whichever you will be using in that column the most)

Each table must have at least one column. The columns must have a name and a data type.

Precision and scale of numbers must be considered carefully when creating numeric data types

Numbers must be set up as either signed or unsigned. Signed numbers will use an equal amount of negative and positive values. In other words, a tinyint (1 byte) will range from -128 to 127 if set as signed. Unsigned will range from 0 to 255. If you will not be using negative values, you should set the column as unsigned.

To store a binary number in a column, you should specify it by beginning your value with “b”, then wrapping the value in single quotes. For example: INSERT INTO bits VALUES(b’0101′);

To store a hexidecimal number in a column, you should specify it by beginning your value with “x”.

Character Sets

A character set is a named encoded character

Each character is mapped to a specific number in a character set.

ASCII is the same as Latin1, which is close to the lower bit range of UTF-8

A character set is actually specified on a column-by-column basis within MySQL.

Character set controls sort order, based on the numerical equivalent of each character

Binary String Data Types

Binary strings are strings of characters that are intended to be interpreted as a whole, rather than by character

Binary Types

• Binary
• Variable Binary
• Tinyblob
• Blob
• Mediumblob
• Longblob

No character set or collation is associated with binary data

The Meaning of NULL

• NULL can set data types to allow missing values
• NULL can be an empty query result
• Conceptually has several different meanings
  • no value
  • unknown value
  • missing value
  • out of range
  • not applicable
  • no column
• Two categories
  • Unknown
  • Not applicable

Primary key columns cannot allow NULL

Unique columns can allow NULL

SQL Expressions

Numeric expressions

• Literal Values
  • Exact-value
  • Approximate value
  • Numerical expressions with NULL usually return NULL
• Expressions with NULL will return NULL
• Results depend on literal values
• SELECT 1.1 + 2.2 = 3.3, 1.1E0 + 2.2E0 = 3.3E0;
  • 1, 0 (true, false)

Mixing numbers with strings

• MySQL is capable of converting strings into numerical values, but it uses extra resources
  • You can compare numbers with strings (1=’1′)
  • You can perform mathematical operations on numbers and strings (1+’1′, 1-’1′, etc.)
  • You should avoid that if possible

Comparison Operations

• x >=5 AND x<=10 is not necessarily the same as x BETWEEN 5 AND 10
  • The first expression will not necessarily be optimized when querying the database
  • The second expression will always be optimized
• BETWEEN will include the indices (so it will return anything with 5 as the key and 10 as the key, and everything in between)
• When using BETWEEN, you must put the lowest value first

Literal strings are quoted

• You can generally use single or double quotes to wrap your literal strings
• If ANSI_QUOTES is enabled through MySQL, then values must be wrapped by single quotes and references to columns, etc. must be wrapped by double quotes
• If ANSI_QUOTES is enabled, wrapping values with double quotes will cause the query to fail

String comparisons

• Depends on character set and collation
• The default is set to be case-insensitive
• Therefore, an expression like “Hello”=”hello” will evaluate as true
• You can change the character encoding when establishing a connection
  • SET collation_connection = latin1_german2_ci;
  • To compare two strings that are not identical, but are equal (”Hello” vs. “hello” or “Dusseldorf” vs. “Dⁿsseldorf”), you can set the collation to latin1_bin (which will not convert binary information to characters when comparing) or latin1_general_cs (which will still consider accented characters to be the same as their unaccented equivalents, but will not consider lowercase characters to be equal to uppercase characters)
• Using the “LIKE” operator
  • Percent character (%) - Any character(s) wildcard - the wildcard can match multiple characters
    • LIKE ‘w%ll’ will match “well”, “wall”, “whole lot of hell”
  • Underscore character (_) - Will only match a single character
    • LIKE ‘d_g’ will match ‘dog’, but will not match ‘dung’
• You can use regular expressions to compare strings in MySQL
  • REGEXP
  • RLIKE

Temporal Operations

• INTERVAL - Add or subtract a specific amount of time to a TIME or DATETIME data type
  • http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_interval
• STR_TO_DATE - Attempts to convert an improperly formatted date into MySQL-formatted date
  • http://dev.mysql.com/doc/refman/6.0/en/date-and-time-functions.html#function_str-to-date
  • STR_TO_DATE(’Apr. 4, 1982′, ‘%b %e, %Y’)

Functions can be invoked in MySQL expressions

Within MySQL, functions should be followed directly by an open parens. By default, no space is allowed between function name and parens

• You can use an IF function

IF(1 > 0 , ‘YES’, ‘NO’);

SELECT name FROM country

ORDER BY IF(code=’USA’,1,2), name;

Will order by whether or not the name is equal to “USA” first, then will order by name

• You can also use “CASE” statements
  CASE case_expr WHEN when_expr THEN result WHEN when_expr THEN result ELSE result ENDSELECT name FROM country
  ORDER BY CASE code
  WHEN ‘USA’ THEN 1
  WHEN ‘CAN’ THEN 2
  WHEN ‘MEX’ THEN 3
  ELSE 4 END, nameSELECT CASE
  WHEN Code = ‘USA’ THEN ‘United States’
  WHEN Continent = ‘Europe’ THEN ‘Europe’
  ELSE ‘Rest of the world’
  END AS Area,
  SUM(GNP), SUM(Population) FROM Country GROUP BY Area;

Numerical Functions

• Mathematical operations (add, subtract, divide, etc.)
• Common functions
  • TRUNCATE()
  • FLOOR() - Returns the greatest integer that is less than the given value
  • CEILING() - Returns the smallest integer that is greater than the given value
  • ROUND()
    • By default, this will round to the nearest integer
    • If you specify a second parameter, it will round to the nearest number with the specified number of decimal places. If you insert a negative number, it will round away from the decimal (in other words, ROUND(15.75,-1) will round to 20 - the nearest 10)
  • RAND() - Returns a random number

String Functions

• LENGTH/CHAR_LENGTH
  • LENGTH returns the number of bytes in a character string
  • CHAR_LENGTH returns the number of characters in a character string
• ELT() - Allows you to select an item from a list
  • ELT(column_value,’1st’,'2nd’,'3rd’…)
  • If column_value is equal to 1, then the function will return “1st”, if column_value is equal to 2, then the function will return “2nd”, etc.
  • ELT is 1-based, not 0-based
• CONCAT()/CONCAT_WS()
  • CONCAT() will simply combine all of the given arguments with no separators
    • If NULL is returned by any of the given arguments, then the entire CONCAT string will return NULL
  • CONCAT_WS() will combine all of the given arguments with a separator, which is specified as the first argument in the function
    • This function will simply skip NULL elements and not insert a separator for NULL elements
    • To concatenate a group of elements that may contain NULL anywhere, you can use CONCAT_WS(”,your_values), using a blank separator
• STRCOMP (string compare) - compare two strings to see if they are equal, less than or equal to
  • Will return -1 if the first one is less than the second
  • Will return 0 if they are equal
  • Will return 1 if the first one is greater than the second

Temporal Functions

• NOW() - Returns the current MySQL timestamp from the system clock
• GET_FORMAT() - Shows us how dates and times are generally formatted in a specific region of the world
• CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

NULL-Related Functions

• ISNULL()/IFNULL()
  • ISNULL returns 0 if something is not null and 1 if it is
  • IFNULL accepts two parameters. The first parameter is the item that will be evaluated and the second item is the string that will be returned if the first returns as null
  • SELECT IFNULL(Continent,’Grand Total’) ContinentName, SUM(Population) FROM Country
    GROUP BY Continent WITH ROLLUP;

MySQL Comments

• You can use a hash character to create a comment that will end at the end of the line
• You can use C-style comments to comment multiple lines of code
• Within MySQL, you can set up conditional comments that will show up as comments in all other SQL DBMAs, but will be executed within MySQL
  • /*! This is not a comment to MySQL, but is in all other SQL DBMAs */
  • /*!50002 This comment will only be executed by MySQL version 5.0002 */

Metadata

Using “INFORMATION_SCHEMA”, you can gather various metadata information about your database. Some of the information you can select is:

+----------------------+
| COLUMN_NAME          |
+----------------------+
| TABLE_CATALOG        |
| TABLE_SCHEMA         |
| TABLE_NAME           |
| VIEW_DEFINITION      |
| CHECK_OPTION         |
| IS_UPDATABLE         |
| DEFINER              |
| SECURITY_TYPE        |
| CHARACTER_SET_CLIENT |
| COLLATION_CONNECTION |
+----------------------+

You can also use SHOW and DESCRIBE commands to show specific information from the database.

From the command prompt (outside of the MySQL client), you can call a program called “mysqlshow” to show you the information in your MySQL installation.

C:\Documents and Settings\Student>mysqlshow -u root -p
Enter password: ****
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| test               |
| test2              |
| world              |
+--------------------+ 

C:\Documents and Settings\Student>mysqlshow -u root -p world
Enter password: ****
Database: world
+-----------------+
|     Tables      |
+-----------------+
| city            |
| country         |
| countrylanguage |
| students        |
+-----------------+

Database Design and Information

A database is simply a directory within the filesystem

Schema is considered a synonym of database in MySQL

Two ways to approach database design:

1. You need to convert something else (another kind of database, a spreadsheet, etc.)
2. You are creating the database from scratch

Four basic relational types:

1. one pointing to one (1->1)
2. one pointing to many (1->M)
3. many pointing to one (M->1)
4. many pointing to many (M->M)

Within a database, you can only structurally manage a M->1 relationship (1->1 is a subset of that)

If you have a M->M relationship, then you should set up an intermediary table to relate between the two tables

Normalization

First Normal Form (1NF) - contains no repeating groups within rows

Second Normal Form (2NF) - normalize at the first level and every non-key (supporting) value is dependent on the primary key value

Third Normal Form (3NF) - normalized at the first and second level, dependent solely on the primary key and no other non-key (supporting) value

Identifier Syntax

• Alias
• Database
• Column
• Index

May be quoted or unquoted

Can use any alphanumeric characters, including $ and _. Anything using any characters other than that must be quoted.

You can access tables from other databases on the same server by qualifying the tablename:

SELECT * FROM world.Country;

Building Tables

General syntax for creating a table:

• CREATE TABLE <table> (
  <column_name> <column type> [<column options>],
  [<column_name> <column_type> [<column_options>],…,]
  [<index list>]
  )[<table options>];
• CREATE TABLE CountryLanguage (
  CountryCode CHAR(3) NOT NULL,
  Language CHAR(30) NOT NULL,
  IsOfficial TINYINT(1) NOT NULL DEFAULT 0,
  Percentage FLOAT(3,1) NOT NULL,
  PRIMARY KEY(CountryCode, Language)
  ) ENGINE = MyISAM COMMENT=’Lists Language Spoken’;
• Several options available
  • ENGINE
  • COMMENT
  • CHARACTER SET (CHARSET)
  • COLLATE
• Cannot create an auto_increment column that is not indexed

A few weeks ago, I took a week-long training course on MySQL development. The course was fantastic, but extremely intensive. I learned quite a bit about using MySQL.

In addition to all of the MySQL development I picked up during the course, I was also privy to some “inside” information about Sun’s possible acquisition of MySQL.

First of all, although it is 99.9% sure that Sun will acquire MySQL, it’s not an absolute certainty, yet. There is still quite a bit of negotiating to be done before the acquisition goes through.

Secondly, although a lot of people seem to think this acquisition spells doom for MySQL and its open-source, free software, it should be an extremely good thing for MySQL and Sun’s products. Rumor has it that Sun decided to acquire MySQL because of the incredible experience MySQL has monetizing open-source software. Sun has had trouble with that proposition since the beginning. MySQL has done fairly well with it.

Sun decided to pick up MySQL as much for their business sense in the open-source software world as for the product they produce.

On a side note, while this probably won’t do anything phenomenal for those of us interacting with MySQL through PHP, developers using Java will probably have a whole lot more support and tools at their fingertips than ever before.

Over the next week or two, I will be posting my notes from the MySQL developer course. They might not make a whole lot of sense, but hopefully they’ll do somebody some good. I know they’ll do a lot of good for me in the future. Hopefully I’ll be able to get certified in MySQL before too much longer.

I came across a cool, free utility the other day while I was searching for ways to get video prepared for my Zune (which I’ll review at a later date). The utility is simply called “Free YouTube to iPod Converter”. The utility was developed by DVDVideoSoft and it is extremely useful.

Basically, the software was designed with one purpose in mind, and it does that very well. It converts Flash Video files to video that’s compatible with your mobile devices. It’s main purpose is to convert YouTube videos to the iPod mp4 video format, but it does do a few other things, too.

First of all, not only can it convert FLV files from your hard drive into mp4 video files, it is also capable of extracting an FLV directly from YouTube. All you do is plug in the URL for the video you want to convert, and the utility accesses YouTube automatically, finds the video file embedded in the page, and extracts it for conversion. Unfortunately, it seem to only work on YouTube; it doesn’t seem to be capable of extracting FLV files from other video sites.

In addition to being able to convert FLV files to iPod video (giving you four options for the quality of the output video), it can also convert your FLV files to three quality levels of PSP video, three quality levels of mobile phone-compatible mp4 video and four different quality levels of mp3 audio.

The only issue I’ve come across in the software so far (and some people may consider this a pro for the software as opposed to a con), is that it doesn’t throw any errors out if it it fails to download the entire FLV file from YouTube. Instead, the progress indicator jumps from wherever it encountered the problem straight to 100%, and the utility tells you that it’s done converting the video. Sometimes you get the whole video, other times you only end up with a few seconds of video before it cuts off.

You can download the utility for free and view a tutorial explaining how to use the software from the main project Web site. If you have a digital media player, a PSP or a video-capable mobile phone, you should definitely get ahold of this application.

Microsoft has posted a new announcement regarding several updates to how they handle APIs, documentation and interoperability today. The announcement is regarding the following products: Windows Vista (including the .NET Framework), Windows Server 2008, SQL Server 2008, Office 2007, Exchange Server 2007, and Office SharePoint Server 2007. The four principles that have been declared are:

1. ensuring open connections
2. promoting data portability
3. enhancing support for industry standards
4. fostering more open engagement with customers and the industry, including open source communities.

Please check out all of the live call notes on our sister site CenterNetworks.

Toshiba announced today that they are pulling out of the race to win the HD format war. They will no longer be developing the HD-DVD format.

Toshiba’s president admitted that Warner Bros. decision to exclusively back the Blu-Ray format was basically the nail in the coffin for Toshiba.

Fortunately or unfortunately, that means that Sony’s Blu-Ray technology is the out-and-out winner in the format war. What will this mean for us as consumers? I really can’t say. There still seem to be rumors floating around that Sony is burdened by the cost of producing Blu-Ray compatible players and discs, even selling them at nearly 10 times the price of a standard DVD equipment. I’m hoping that the production costs will drop significantly now that they have no competition. Otherwise, it will most likely be a very long time before Blu-Ray overtakes the standard digital video disc.

At this point, will we begin to see more and more Blu-Ray drives being packaged with new computers? I guess we’ll just have to wait and see.

Are you either a beginner at PS, or are you familiar with most tools yet do not understand how to create custom shapes? If you are either of these two, this is the blog (tutorial *cough, cough*) for you.

It is 2008, so let’s do the Zodiac Tiger!

(more…)

If you’ve been looking to start up your own web app like many people, you may have heard about Ruby on Rails before. Ruby on Rails is a ruby framework made especially for implementation of websites on the web. In the last year or so Ruby on Rails has been on fire and only a few books have emerged that are really great. This book isn’t the best book on the topic that you will find but it’s really a decent book.

As with all of the other Wrox books I’ve read in the past, this book jumps right into the teaching. Now the only real prerequisite to reading this book is that you should probably know html before you get started. Otherwise you will be pretty confused in the later chapters.

The first chapters starts with a bang, and explains how to install Ruby on Rails on your local computer. This is followed by a long set of tutorials that teach you how to write ruby. Granted it is all very basic stuff the book makes sure that the reader understands everything before moving on. So by the time you actually get to the part of the book where the building of the web app is started you should understand and know how to use ruby.

The actual Rails part of the book starts on chapter 4 and it runs you through very basics of starting a Rails project. Once you’re through with that you will move on to the next chapter which teaches you how to make the most basic of a web app, something that you wouldn’t put out to the public but something that you should be able to learn from quite quickly.

After that you will learn how databases react with Rails. The book explains how the databases are connected, and goes over basic database design. It really explains things quite well.

The last few chapters are about improving the back-end of a Rails app, implementing ajax in all of your pages, and goes through and explains unit testing. The part about unit testing was a little bit confusing for me and hard to follow, but I eventually got what it was all about.

So this book is good for anyone who already knows html and really wants to get their own web application up and running. It is a little hard to follow at times, but if you keep reading on you will find that things make sense after a while. The book isn’t priced to high it rings in at a $35 price-tag, but that is cheap compared to some of the other Ruby on Rails books that are out there.

(more…)