Archives

You are currently viewing archive for August 2007
Category: Linux tips
Posted by: hajime osako
Category: MS tips
Posted by: hajime osako
Category: Web dev
Posted by: hajime osako
$a &= $b      $a = $a & $b      Bitwise And
$a |= $b      $a = $a | $b      Bitwise Or
$a ^= $b      $a = $a ^ $b      Bitwise Xor
$a <<= $b     $a = $a << $b     Left shift
$a >>= $b     $a = $a >> $b     Right shift
Category: Study
Posted by: hajime osako
Every table has a format file in its database directory. The format file is created by the server and contains the definition, or structure, of the table. The format filename is the save as the table name, plus an .frm suffix.

The MyISAM engine creates a data file (.MYD) and index file (.MYI) for each table.

By default, the InnoDB engine shares files for multiple tables. If Country is an InnoDB table, there will e a Country.frm format file but the InnoDB engine stores the table data and index information in the InnoDB shared table space. (InnoDB table space is shared among all databases.)

The MEMORY engine does not use any disk storage.
Category: Study
Posted by: hajime osako
The INFORMATION_SCHEMA database has a SCHEMATA table that contains database metadata.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'searching database';

The output of the SHOW DATABASES statement depends on whether you have the SHOW DATABASES privilege. It can take a LIKE clause.

SHOW DATABASES LIKE 'm%';

SHOW CREATE DATABASE shows the CREATE DATABASE statement.
Category: Study
Posted by: hajime osako
Each database directory has a default character set and collation. The properties are stored in a file named db.opt in the database directory.
MySQL does not place any limits on the number of databases.
In MySQL, the word "database" is same as "schema". So "SHOW SCHEMAS" instead of "SHOW DATABASES" is doable.

The CREATE/ALTER DATABASE statements have two optional clauses, CHARACTER SET and COLLATE.
Example:
CREATE DATABASE database_name CHARACTER SET utf8 COLLATE utf8_japanese_ci;
ALTER DATABASE (database_name) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

ALTER DATABASE affects only creation of new tables. You cannot use ALTER DATABASE to rename a database.

Any warning generated when IF EXISTS is used can be displayed with SHOW WARNINGS.
Category: Web dev
Posted by: hajime osako
Category: Study
Posted by: hajime osako
By default, a function name and the opening parenthesis that follows it must be written with no intervening space. If the IGNORE_SPACE SQL mode is enabled, the server allows spaces.

Reserved words and function names are not case sensitive.
Category: Study
Posted by: hajime osako
An identifier cannot consist entirely of digits, if it's not quoted. If the ANSI_QUOTES SQL modes is enabled, double quotes are acceptable to quote an identifier.
Database and table names can't contain '.', '/', or '\'.

For database and table identifiers, case sensitivity depends on the OS and file system of the server host, and on the setting of the lower_case_table_names system variable.
Column, index, stored routine and trigger identifiers are not case sensitive.
Column aliases are not case sensitive.

A table name may be qualified with the name of the database to which it belongs.
For example:
SELECT db1t_a.id, db2t_b.name FROM db1.table_a db1t_a
   INNER JOIN db2.table_b db2t_b
   ON db1t_a.id = db2t_b.id;
Category: Z. etc
Posted by: hajime osako
Category: Study
Posted by: hajime osako
Using INSERT IGNORE or UPDATE IGNORE causes MySQL to use non-strict behavior for the statement (producing warnings rather than errors).

Before MySQL 5, date values were required only to have month and day values in the range from 1 to 12 and 1 to 31, respectively. This means that MySQL accepted dates such as '2009-02-31'.
MySQL 5 requires that month and day values correspond to an actual legal date, so '2009-02-31' is not considered a valid date. MySQL 5 converts it to '0000-00-00' and generates a warning (in strict mode, error).
Enabling ALLOW_INVALID_DATES allows you to use the respective ranges of 1 to 12 and 1 to 31.

SET sql_mode = 'STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Category: Study
Posted by: hajime osako
Strict mode is enabled by using STRICT_TRANS_TABLES and STRICT_ALL_TABLES.

SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';
SET sql_mode = 'TRADITIONAL';

STRICT_TRANS_TABLES:
If an error occurs for a transactional table, the statement rolls back. For a non-transactional table, the statement could be adjusted by MySQL if possible. (partial updates might occur)

STRICT_ALL_TABLES:
Similar to STRICT_TRANS_TABLES but the statement could be aborted for a non-transactional table. (partial updates might occur)

Additional Input Data Restrictions:
ERROR_FOR_DIVISION_BY_ZERO
* If no strict mode, division by zero results in a value of NULL and a warning, not an error.
NO_ZERO_DATE, NO_ZERO_IN_DATE
* By default, MySQL allows "zero" dates even if you enable strict mode.
TRADITIONAL
* Strict mode plus above additional restrictions
Category: Web dev
Posted by: hajime osako
第3回 変数の宣言とスコープ - @IT

参照型:
 配列(array)
 オブジェクト(object)
 関数(function)

ローカル変数は関数全体で有効である
ローカル変数は関数の先頭で宣言する

関数リテラルとFunctionコンストラクタにおけるスコープの違い

クロージャは一種の記憶域を提供する仕組み
クロージャを利用したトグル・ボタンの動作例
document.getElementById('btn').onclick = setButtonState();

function setButtonState() {
    var flag = false;
    var btn = document.getElementById('btn');

    return function() {
        flag = !flag;
        this.innerText = flag ? "有効" : "無効";
  };
}
Category: Web dev
Posted by: hajime osako
CodeZine:JavaFXによるGUIアプリケーションの作成(OpenJFX, JavaFX)
[実行]-[構成および実行]メニュー
    * 名前: JavaFX Application
    * [メイン]タブ
      プロジェクト: FXSample(実行するプロジェクトを選択)
      メイン・クラス: net.java.javafx.FXShell
      (デフォルトで設定されているので設定不要)
    * [引数]タブ
      プログラムの引数: ${resource_name}
      (「変数」ボタンで、resource_nameを選択)
Category: Web dev
Posted by: hajime osako
MySQL Performance Blog � COUNT(*) vs COUNT(col)
countでNULLを許可するカラム名を指定すると、NULLはカウントされないので結果が異なります。(スピードも遅くなる)
Category: Web dev
Posted by: hajime osako
Category: Web dev
Posted by: hajime osako
Category: Z. links
Posted by: hajime osako
Category: Z. links
Posted by: hajime osako
Category: Web dev
Posted by: hajime osako
MySQLのSQL_MODEとストリクトモード - hirohama.work

Manual:4.2.6. SQL モード
デフォルトの SQL モードを使用するには、--sql-mode="modes" オプションで mysqld を立ち上げます。Unix では my.cnf で、Window では my.iniで、sql-mode="modes" を使用します。
次のステートメントで、現行のsql_mode のグローバルまたはセッション値を読み取ることができます。

SELECT @@global.sql_mode;
SELECT @@session.sql_mode;

To check all variables (parameters):
SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_allowed_packet';
Category: Web dev
Posted by: hajime osako
# リファレンス仕様の変更 
# 無効な配列アクセス
# 関数定義仕様の変更
# PHPバイナリの名前
# 長い名前のシステム定義配列
# register_globals
# 参照カウンタの32ビット化
# オブジェクト関連の変更
# オブジェクト変数の代入
# オブジェクトのコピー作成
# $this変数の取り扱い
特集:残り一年! PHP4からPHP5への移行|gihyo.jp
Category: Web dev
Posted by: hajime osako
The MySQL table types

CREATE TABLE heap2 TYPE=HEAP SELECT name FROM myisam1;
ALTER TABLE heap2 ADD INDEX(name);

* HEAP indexes cannot be built on BLOB or TEXT fields
* HEAP tables cannot use partial keys (prefixes)
* HEAP tables do not support AUTO_INCREMENT fields
* HEAP indexes can only use the '=' and '<=>' operators
* HEAP indexes cannot be used to return an 'ORDER BY' result.
* HEAP indexes cannot provide information on how many records are between two results, which would assist the optimizer in its choice of index.
Category: Web dev
Posted by: hajime osako
Category: Web dev
Posted by: hajime osako
# mysqldump -Xd -u [userid] -p[password] [database] [table] > [output xml]
-X: XML output
-d: no data
Category: Web dev
Posted by: hajime osako
UML 2 クラス図の概要
黒い三角は関連を表す。
メソッドの下線はクラスレベルでの責任を表す

More detail:
UML basics: An introduction to the Unified Modeling Language
UML basics: The class diagram
UML basics: The component diagram
UML's Sequence Diagram
Category: Study
Posted by: hajime osako
If MySQL is not operating in strict mode, it adjusts invalid input values to legal values when possible and generates warning messages.

For a single-row INSERT which is assigning NULL to a NOT NULL column, an error occurs and the statement fails. However, for a multiple-row INSERT, MySQL assigns the column the implicit default value for its data type.

If a column is changed to NOT NULL using ALTER TABLE, MySQL converts NULL values to the implicit default value for the data type. (Warning but No error!)
Category: Web dev
Posted by: hajime osako
Category: Study
Posted by: hajime osako
For numeric columns:
UNSIGNED
ZEROFILL (causes UNSIGNED)
AUTO_INGREMENT (may be only one per table, and must be indexed, and must be defined as NOT NULL)
LAST_INSERT_ID() returns the most recently generated AUTO_INCREMENT value.
If you update an AUTO_INCREMENT column to NULL or 0, the column is set to 0.
By default, inserting 0 in an AUTO_INCREMENT column has the same effect as inserting NULL unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled.
MyISAM storage engine supports composite indexes. In composite indexes, AUTO_INCREMENT value could be reused.
For string columns:
CHARACTER SET (= CHARSET) ex. utf-8
COLLATE (if CHARSET is omitted, set to the collation's character set.)
BINARY
For general columns:
NOT NULL
DEFAULT value (can't use for TEXT, BLOB and AUTO_INCREMENT (integer) columns)
A default value must be a constant except CURRENT_TIMESTAMP for one TIMESTAMP column.
Implicit default values are:
0 for numeric columns
"" for string columns except ENUM
the first enumeration member for ENUM
zero value for temporal columns
Category: Web dev
Posted by: hajime osako
"ROLLUP 使用時のその他の考慮事項
以下に、MySQL における ROLLUP の実装固有の動作について、いくつか説明します。 ROLLUP の使用時には、ORDER BY 節を使用して結果をソートすることはできません(ROLLUP と ORDER BY は相互排他的です)。しかし、ソート順序をある程度制御することは可能です。 MySQL で GROUP BY を使用すると結果がソートされます。また、GROUP BY リストに指定したカラムに明示的な ASC または DESC キーワードを付けることによって、個々のカラムのソート順序を指定できます(この場合も、ROLLUP によって追加される上位レベルの集計レコードは、ソート順序とはかかわりなく、それぞれの計算の対象となったレコードの後に表示されます)。"
MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.3.7.2 GROUP BY の修飾子
Category: Web dev
Posted by: hajime osako
@IT:オブジェクト指向言語に生まれ変わるPHP5[前編](2/2)
    * オブジェクトは参照渡しがデフォルト
    * プロパティにアクセス制限を導入
    * メソッドにアクセス制限を導入
    * abstractクラスとabstractメソッド
    * インターフェイス
    * final宣言
    * 名前空間
    * クラス内定数
    * クラス変数
    * 統一コンストラクタ
    * デストラクタ
    * アクセサ

08/03: PHPの罠

Category: Web dev
Posted by: hajime osako

dechexはintegerじゃないものを渡すと正しい値を返さない。そこで0を足す。
echo(dechex("2724838310"+0));
なぜか、
echo(dechex((int) "2724838310"));
はうまくいかない。

array_mergeは数字がインデックスの配列を渡すと、なぜかインデックスを作り直してしまう。(つまり数字と値の関係が失われてしまう。)

isset() does not return TRUE for array keys that correspond to a NULL value, while array_key_exists() does.

Category: Web dev
Posted by: hajime osako
COALESCE(param1, param2, ...)
最初にNULLでない値を返す。
CONCAT_WS(separator, str1, str2,...)
区切り文字付きの CONCAT(CONCAT With Separator)。separatorがNULLだとNULLを返すが、CONCATと違って、strXがNULLだとその文字列はスキップされる。
カラムタイプ DATEとDATETIME
変換しないでそのまま比較すると、結果がおかしい
JOIN句の比較とWhere句の比較は結果が異なる
select * from a left join b on a.id=b.id and b.id is null;
select * from a left join b on a.id=b.id where b.id is null;
テーブルの定義を表示するには
SHOW CREATE TABLE table_name;
SELECTで変数をセットするときLIMITに注意
select @val := col_a from a limit 0, 1;
とすると、なぜか@valには最後のcol_aが代入される
SELECTでWHERE句に変数を使用するとき宣言に注意
SELECT * FROM a WHERE @myVal IS NULL OR a.col = @myVal;
で、SET @myVal = NULL; が事前に有ると無いのでは結果が異なる
Unsigned subtraction
In some cases where an unsigned subtraction would have resulted in a signed integer, it now results in an unsigned integer.
For example, "SELECT column_int - 1 FROM t;" If column column_int is unsigned integer and value is 0, the result is 18446744073709551615, not -1.
To get the older behavior, use CAST(i - 1 AS SIGNED) to convert the expression result to a signed value.
Category: Study
Posted by: hajime osako
DATE      3 bytes '1000-01-01' to '9999-12-31'
TIME      3 bytes '-838:59:59' to '838:59:59'*
DATETIME  8 bytes '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP 4 bytes '1970-01-01 00:00:00' to mid-year 2037
YEAR      1 byte  1901 to 2155 or (19)70 to (20)69
* TIME columns can be sued to represent elapsed time.

The date format corresponds to the ANSI SQL (ISO 8601) format. This can be changeable by using DATE_FORMAT() and TIME_FORMAT().
TIMESTAMP column has two special attributes, DEFAULT CUURENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, and both are assigned if those are ommitted when the table is created (after version 4.1), also NOT NULL is default.
MySQL does not allow to use DEFAULT CURRENT_TIMESTAMP with one column and ON UPDATE CURRENT_TIMESTAMP with another. This is achievable by creating two TIMESTAMP columns defining one column, that shold hold the creation time, with DEFAULT 0 and explicitly set it to NULL when a new recored is inserted, and another column, that should hold the update time, with DEFAULT CURRENT_TIMESTAMP:
CREATE TABLE test (
   created  TIMESTAMP DEFAULT 0,
   modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   aaaaa    VARCHAR(10)
);

INSERT INTO test (created, aaaaa) VALUES (NULL, 'test');
UPDATE test SET aaaaa = 'test 2';
MySQL is possible to set the current time zone on a per-connection basis.
1. Signed hour/minute offset ('+hh:mm' or '-hh:mm')
2. Named time zone ('US/Eastern')
3. SYSTEM time zone

Examples:
SELECT @@global.time_zone, @@session.time_zone;
SET [SESSION] time_zone = '+00:00';
SELECT CONVERT_TZ('2007-08-02 12:00:00', '+01:00', '+03:00');
Category: Web dev
Posted by: hajime osako
Category: Study
Posted by: hajime osako
CHAR       Fixed length up to 255 characters
VARCHAR    Variable length up to approx. 65,535 characters
           (+ 1 byte for a less than 256, 2 bytes otherwise*)
TINYTEXT   Variable length up to 255 characters (+ 1 byte*)
TEXT       Variable length up to 65,535 characters (+ 2 bytes*)
MEDIUMTEXT Variable length up to 16,777,215 chars (+ 3 bytes*)
LONGTEXT   Variable length up to 4,294,967,295 chars (+ 4 bytes*)
* extra storage space for character length

For variable-length (VARCHAR, TEXT) columns, MySQL allocates only the required amount of space. That means if the character is triple-byte, one character use 3 bytes plus a length byte.
Non-binary strings have a character set and a character set might have many collations (string order).
The collation decides if uppercase and lowercase versions and/or accent marks of a given character are equivalent.
A collation can be a binary collation. One effect of this is that for character sets with uppercase and lowercase characters or accented characters, the collation is case sensitive and accent sensitive.

mysql# SHOW COLLATION LIKE 'latin1%';

Each collation name ends with _ci, _cs or _bin, signifying that the collation is Case Insensitive, Case Sensitive or BINary. The binary columns are suitable for raw data such as images or compressed data.

BINARY     Fixed length, binary string
VARBINARY  Variable length, binary string
BLOB       variable length, binary string
           (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB)

ENUM       A fixed set of legal values up to 65,535
SET        A fixed set of legal values up to 64
MySQL represents ENUMerations as integers internally.
Illigal value is stored as 0 and display as the empty string. (in strict SQL mode, an error occurs when you try to store an invalid value.)
MySQL represents SET as a bitmap using one bit per member.
It's ignored if you try to store an invalid value into a SET column (only valid values are stored)
When you do SELECT, INSERT or UPDATE, you can use either string or internal integer value (it could be confusing though.)