// // $Id$ // // SPDatabaseData.m // sequel-pro // // Created by Stuart Connolly (stuconnolly.com) on May 20, 2009 // Copyright (c) 2009 Stuart Connolly. All rights reserved. // // This program is free software; you can redistribute it and/or modify // it under the terms of the GNU General Public License as published by // the Free Software Foundation; either version 2 of the License, or // (at your option) any later version. // // This program is distributed in the hope that it will be useful, // but WITHOUT ANY WARRANTY; without even the implied warranty of // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the // GNU General Public License for more details. // // You should have received a copy of the GNU General Public License // along with this program; if not, write to the Free Software // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA // // More info at <http://code.google.com/p/sequel-pro/> #import "SPDatabaseData.h" #import "SPStringAdditions.h" @interface SPDatabaseData (PrivateAPI) - (NSMutableArray *)_getDatabaseDataForQuery:(NSString *)query; @end const CHAR_SETS charsets[] = { { 1, "big5","big5_chinese_ci", "Big5 Traditional Chinese"}, { 3, "dec8", "dec8_swedisch_ci", "DEC West European"}, { 4, "cp850", "cp850_general_ci", "DOS West European"}, { 6, "hp8", "hp8_english_ci", "HP West European"}, { 7, "koi8r", "koi8r_general_ci", "KOI8-R Relcom Russian"}, { 8, "latin1", "latin1_swedish_ci", "cp1252 West European"}, { 9, "latin2", "latin2_general_ci", "ISO 8859-2 Central European"}, { 10, "swe7", "swe7_swedish_ci", "7bit Swedish"}, { 11, "ascii", "ascii_general_ci", "US ASCII"}, { 12, "ujis", "ujis_japanese_ci", "EUC-JP Japanese"}, { 13, "sjis", "sjis_japanese_ci", "Shift-JIS Japanese"}, { 16, "hebrew", "hebrew_general_ci", "ISO 8859-8 Hebrew"}, { 18, "tis620", "tis620_thai_ci", "TIS620 Thai"}, { 19, "euckr", "euckr_korean_ci", "EUC-KR Korean"}, { 22, "koi8u", "koi8u_general_ci", "KOI8-U Ukrainian"}, { 24, "gb2312", "gb2312_chinese_ci", "GB2312 Simplified Chinese"}, { 25, "greek", "greek_general_ci", "ISO 8859-7 Greek"}, { 26, "cp1250", "cp1250_general_ci", "Windows Central European"}, { 28, "gbk", "gbk_chinese_ci", "GBK Simplified Chinese"}, { 30, "latin5", "latin5_turkish_ci", "ISO 8859-9 Turkish"}, { 32, "armscii8", "armscii8_general_ci", "ARMSCII-8 Armenian"}, { 33, "utf8", "utf8_general_ci", "UTF-8 Unicode"}, { 35, "ucs2", "ucs2_general_ci", "UCS-2 Unicode"}, { 36, "cp866", "cp866_general_ci", "DOS Russian"}, { 37, "keybcs2", "keybcs2_general_ci", "DOS Kamenicky Czech-Slovak"}, { 38, "macce", "macce_general_ci", "Mac Central European"}, { 39, "macroman", "macroman_general_ci", "Mac West European"}, { 40, "cp852", "cp852_general_ci", "DOS Central European"}, { 41, "latin7", "latin7_general_ci", "ISO 8859-13 Baltic"}, { 51, "cp1251", "cp1251_general_ci", "Windows Cyrillic"}, { 57, "cp1256", "cp1256_general_ci", "Windows Arabic"}, { 59, "cp1257", "cp1257_general_ci", "Windows Baltic"}, { 63, "binary", "binary", "Binary pseudo charset"}, { 92, "geostd8", "geostd8_general_ci", "GEOSTD8 Georgian"}, { 95, "cp932", "cp932_japanese_ci", "SJIS for Windows Japanese"}, { 97, "eucjpms", "eucjpms_japanese_ci", "UJIS for Windows Japanese"}, { 2, "latin2", "latin2_czech_cs", "ISO 8859-2 Central European"}, { 5, "latin1", "latin1_german_ci", "cp1252 West European"}, { 14, "cp1251", "cp1251_bulgarian_ci", "Windows Cyrillic"}, { 15, "latin1", "latin1_danish_ci", "cp1252 West European"}, { 17, "filename", "filename", "File Name"}, { 20, "latin7", "latin7_estonian_cs", "ISO 8859-13 Baltic"}, { 21, "latin2", "latin2_hungarian_ci", "ISO 8859-2 Central European"}, { 23, "cp1251", "cp1251_ukrainian_ci", "Windows Cyrillic"}, { 27, "latin2", "latin2_croatian_ci", "ISO 8859-2 Central European"}, { 29, "cp1257", "cp1257_lithunian_ci", "Windows Baltic"}, { 31, "latin1", "latin1_german2_ci", "cp1252 West European"}, { 34, "cp1250", "cp1250_czech_cs", "Windows Central European"}, { 42, "latin7", "latin7_general_cs", "ISO 8859-13 Baltic"}, { 43, "macce", "macce_bin", "Mac Central European"}, { 44, "cp1250", "cp1250_croatian_ci", "Windows Central European"}, { 45, "utf8", "utf8_general_ci", "UTF-8 Unicode"}, { 46, "utf8", "utf8_bin", "UTF-8 Unicode"}, { 47, "latin1", "latin1_bin", "cp1252 West European"}, { 48, "latin1", "latin1_general_ci", "cp1252 West European"}, { 49, "latin1", "latin1_general_cs", "cp1252 West European"}, { 50, "cp1251", "cp1251_bin", "Windows Cyrillic"}, { 52, "cp1251", "cp1251_general_cs", "Windows Cyrillic"}, { 53, "macroman", "macroman_bin", "Mac West European"}, { 58, "cp1257", "cp1257_bin", "Windows Baltic"}, { 60, "armascii8", "armascii8_bin", "armascii8"}, { 65, "ascii", "ascii_bin", "US ASCII"}, { 66, "cp1250", "cp1250_bin", "Windows Central European"}, { 67, "cp1256", "cp1256_bin", "Windows Arabic"}, { 68, "cp866", "cp866_bin", "DOS Russian"}, { 69, "dec8", "dec8_bin", "DEC West European"}, { 70, "greek", "greek_bin", "ISO 8859-7 Greek"}, { 71, "hebew", "hebrew_bin", "ISO 8859-8 Hebrew"}, { 72, "hp8", "hp8_bin", "HP West European"}, { 73, "keybcs2", "keybcs2_bin", "DOS Kamenicky Czech-Slovak"}, { 74, "koi8r", "koi8r_bin", "KOI8-R Relcom Russian"}, { 75, "koi8u", "koi8u_bin", "KOI8-U Ukrainian"}, { 77, "latin2", "latin2_bin", "ISO 8859-2 Central European"}, { 78, "latin5", "latin5_bin", "ISO 8859-9 Turkish"}, { 79, "latin7", "latin7_bin", "ISO 8859-13 Baltic"}, { 80, "cp850", "cp850_bin", "DOS West European"}, { 81, "cp852", "cp852_bin", "DOS Central European"}, { 82, "swe7", "swe7_bin", "7bit Swedish"}, { 93, "geostd8", "geostd8_bin", "GEOSTD8 Georgian"}, { 83, "utf8", "utf8_bin", "UTF-8 Unicode"}, { 84, "big5", "big5_bin", "Big5 Traditional Chinese"}, { 85, "euckr", "euckr_bin", "EUC-KR Korean"}, { 86, "gb2312", "gb2312_bin", "GB2312 Simplified Chinese"}, { 87, "gbk", "gbk_bin", "GBK Simplified Chinese"}, { 88, "sjis", "sjis_bin", "Shift-JIS Japanese"}, { 89, "tis620", "tis620_bin", "TIS620 Thai"}, { 90, "ucs2", "ucs2_bin", "UCS-2 Unicode"}, { 91, "ujis", "ujis_bin", "EUC-JP Japanese"}, { 94, "latin1", "latin1_spanish_ci", "cp1252 West European"}, { 96, "cp932", "cp932_bin", "SJIS for Windows Japanese"}, { 99, "cp1250", "cp1250_polish_ci", "Windows Central European"}, { 98, "eucjpms", "eucjpms_bin", "UJIS for Windows Japanese"}, {128, "ucs2", "ucs2_unicode_ci", "UCS-2 Unicode"}, {129, "ucs2", "ucs2_icelandic_ci", "UCS-2 Unicode"}, {130, "ucs2", "ucs2_latvian_ci", "UCS-2 Unicode"}, {131, "ucs2", "ucs2_romanian_ci", "UCS-2 Unicode"}, {132, "ucs2", "ucs2_slovenian_ci", "UCS-2 Unicode"}, {133, "ucs2", "ucs2_polish_ci", "UCS-2 Unicode"}, {134, "ucs2", "ucs2_estonian_ci", "UCS-2 Unicode"}, {135, "ucs2", "ucs2_spanish_ci", "UCS-2 Unicode"}, {136, "ucs2", "ucs2_swedish_ci", "UCS-2 Unicode"}, {137, "ucs2", "ucs2_turkish_ci", "UCS-2 Unicode"}, {138, "ucs2", "ucs2_czech_ci", "UCS-2 Unicode"}, {139, "ucs2", "ucs2_danish_ci", "UCS-2 Unicode"}, {140, "ucs2", "ucs2_lithunian_ci", "UCS-2 Unicode"}, {141, "ucs2", "ucs2_slovak_ci", "UCS-2 Unicode"}, {142, "ucs2", "ucs2_spanish2_ci", "UCS-2 Unicode"}, {143, "ucs2", "ucs2_roman_ci", "UCS-2 Unicode"}, {144, "ucs2", "ucs2_persian_ci", "UCS-2 Unicode"}, {145, "ucs2", "ucs2_esperanto_ci", "UCS-2 Unicode"}, {146, "ucs2", "ucs2_hungarian_ci", "UCS-2 Unicode"}, {147, "ucs2", "ucs2_sinhala_ci", "UCS-2 Unicode"}, {192, "utf8mb3", "utf8mb3_general_ci", "UTF-8mb3 Unicode"}, {193, "utf8mb3", "utf8mb3_icelandic_ci", "UTF-8mb3 Unicode"}, {194, "utf8mb3", "utf8mb3_latvian_ci", "UTF-8mb3 Unicode"}, {195, "utf8mb3", "utf8mb3_romanian_ci", "UTF-8mb3 Unicode"}, {196, "utf8mb3", "utf8mb3_slovenian_ci", "UTF-8mb3 Unicode"}, {197, "utf8mb3", "utf8mb3_polish_ci", "UTF-8mb3 Unicode"}, {198, "utf8mb3", "utf8mb3_estonian_ci", "UTF-8mb3 Unicode"}, {119, "utf8mb3", "utf8mb3_spanish_ci", "UTF-8mb3 Unicode"}, {200, "utf8mb3", "utf8mb3_swedish_ci", "UTF-8mb3 Unicode"}, {201, "utf8mb3", "utf8mb3_turkish_ci", "UTF-8mb3 Unicode"}, {202, "utf8mb3", "utf8mb3_czech_ci", "UTF-8mb3 Unicode"}, {203, "utf8mb3", "utf8mb3_danish_ci", "UTF-8mb3 Unicode"}, {204, "utf8mb3", "utf8mb3_lithunian_ci", "UTF-8mb3 Unicode"}, {205, "utf8mb3", "utf8mb3_slovak_ci", "UTF-8mb3 Unicode"}, {206, "utf8mb3", "utf8mb3_spanish2_ci", "UTF-8mb3 Unicode"}, {207, "utf8mb3", "utf8mb3_roman_ci", "UTF-8mb3 Unicode"}, {208, "utf8mb3", "utf8mb3_persian_ci", "UTF-8mb3 Unicode"}, {209, "utf8mb3", "utf8mb3_esperanto_ci", "UTF-8mb3 Unicode"}, {210, "utf8mb3", "utf8mb3_hungarian_ci", "UTF-8mb3 Unicode"}, {211, "utf8mb3", "utf8mb3_sinhala_ci", "UTF-8mb3 Unicode"}, {224, "utf8", "utf8_unicode_ci", "UTF-8 Unicode"}, {225, "utf8", "utf8_icelandic_ci", "UTF-8 Unicode"}, {226, "utf8", "utf8_latvian_ci", "UTF-8 Unicode"}, {227, "utf8", "utf8_romanian_ci", "UTF-8 Unicode"}, {228, "utf8", "utf8_slovenian_ci", "UTF-8 Unicode"}, {229, "utf8", "utf8_polish_ci", "UTF-8 Unicode"}, {230, "utf8", "utf8_estonian_ci", "UTF-8 Unicode"}, {231, "utf8", "utf8_spanish_ci", "UTF-8 Unicode"}, {232, "utf8", "utf8_swedish_ci", "UTF-8 Unicode"}, {233, "utf8", "utf8_turkish_ci", "UTF-8 Unicode"}, {234, "utf8", "utf8_czech_ci", "UTF-8 Unicode"}, {235, "utf8", "utf8_danish_ci", "UTF-8 Unicode"}, {236, "utf8", "utf8_lithuanian_ci", "UTF-8 Unicode"}, {237, "utf8", "utf8_slovak_ci", "UTF-8 Unicode"}, {238, "utf8", "utf8_spanish2_ci", "UTF-8 Unicode"}, {239, "utf8", "utf8_roman_ci", "UTF-8 Unicode"}, {240, "utf8", "utf8_persian_ci", "UTF-8 Unicode"}, {241, "utf8", "utf8_esperanto_ci", "UTF-8 Unicode"}, {242, "utf8", "utf8_hungarian_ci", "UTF-8 Unicode"}, {243, "utf8", "utf8_sinhala_ci", "UTF-8 Unicode"}, {254, "utf8mb3", "utf8mb3_general_cs", "UTF-8mb3 Unicode"}, { 0, NULL, NULL, NULL} }; @implementation SPDatabaseData @synthesize connection; /** * Initialize cache arrays. */ - (id)init { if ((self = [super init])) { characterSetEncoding = nil; collations = [[NSMutableArray alloc] init]; characterSetCollations = [[NSMutableArray alloc] init]; storageEngines = [[NSMutableArray alloc] init]; characterSetEncodings = [[NSMutableArray alloc] init]; } return self; } /** * Reset all the cached values. */ - (void)resetAllData { if (characterSetEncoding != nil) { [characterSetEncoding release], characterSetEncoding = nil; } [collations removeAllObjects]; [characterSetCollations removeAllObjects]; [storageEngines removeAllObjects]; [characterSetEncodings removeAllObjects]; } /** * Returns all of the database's currently available collations by querying information_schema.collations. */ - (NSArray *)getDatabaseCollations { if ([collations count] == 0) { // Try to retrieve the available collations from the database if ([connection serverMajorVersion] >= 5) [collations addObjectsFromArray:[self _getDatabaseDataForQuery:@"SELECT * FROM `information_schema.collations` ORDER BY `collation_name` ASC"]]; // If that failed, get the list of collations from the hard-coded list if (![collations count]) { const CHAR_SETS *c = charsets; do { [collations addObject:[NSString stringWithCString:c->collation encoding:NSUTF8StringEncoding]]; ++c; } while (c[0].nr != 0); } } return collations; } /** * Returns all of the database's currently available collations allowed for the supplied encoding by * querying information_schema.collations. */ - (NSArray *)getDatabaseCollationsForEncoding:(NSString *)encoding { if (encoding && ((characterSetEncoding == nil) || (![characterSetEncoding isEqualToString:encoding]) || ([characterSetCollations count] == 0))) { [characterSetEncoding release]; [characterSetCollations removeAllObjects]; characterSetEncoding = [[NSString alloc] initWithString:encoding]; // Try to retrieve the available collations for the supplied encoding from the database if ([connection serverMajorVersion] >= 5) [characterSetCollations addObjectsFromArray:[self _getDatabaseDataForQuery:[NSString stringWithFormat:@"SELECT * FROM `information_schema`.`collations` WHERE character_set_name = '%@' ORDER BY `collation_name` ASC", characterSetEncoding]]]; // If that failed, get the list of collations matching the supplied encoding from the hard-coded list if (![characterSetCollations count]) { const CHAR_SETS *c = charsets; do { NSString *charSet = [NSString stringWithCString:c->name encoding:NSUTF8StringEncoding]; if ([charSet isEqualToString:characterSetEncoding]) { [characterSetCollations addObject:[NSDictionary dictionaryWithObject:[NSString stringWithCString:c->collation encoding:NSUTF8StringEncoding] forKey:@"COLLATION_NAME"]]; } ++c; } while (c[0].nr != 0); } } return characterSetCollations; } /** * Returns all of the database's available storage engines. */ - (NSArray *)getDatabaseStorageEngines { if ([storageEngines count] == 0) { if ([connection serverMajorVersion] < 5) { [storageEngines addObject:[NSDictionary dictionaryWithObject:@"MyISAM" forKey:@"Engine"]]; // Check if InnoDB support is enabled MCPResult *result = [connection queryString:@"SHOW VARIABLES LIKE 'have_innodb'"]; [result setReturnDataAsStrings:YES]; if ([result numOfRows] == 1) { if ([[[result fetchRowAsDictionary] objectForKey:@"Value"] isEqualToString:@"YES"]) { [storageEngines addObject:[NSDictionary dictionaryWithObject:@"InnoDB" forKey:@"Engine"]]; } } // Before MySQL 4.1 the MEMORY engine was known as HEAP and the ISAM engine was included if (([connection serverMajorVersion] <= 4) && ([connection serverMinorVersion] < 100)) { [storageEngines addObject:[NSDictionary dictionaryWithObject:@"HEAP" forKey:@"Engine"]]; [storageEngines addObject:[NSDictionary dictionaryWithObject:@"ISAM" forKey:@"Engine"]]; } else { [storageEngines addObject:[NSDictionary dictionaryWithObject:@"MEMORY" forKey:@"Engine"]]; } // BLACKHOLE storage engine was added in MySQL 4.1.11 if (([connection serverMajorVersion] >= 4) && ([connection serverMinorVersion] >= 1) && ([connection serverReleaseVersion] >= 11)) { [storageEngines addObject:[NSDictionary dictionaryWithObject:@"BLACKHOLE" forKey:@"Engine"]]; // ARCHIVE storage engine was added in MySQL 4.1.3 if ([connection serverReleaseVersion] >= 3) { [storageEngines addObject:[NSDictionary dictionaryWithObject:@"ARCHIVE" forKey:@"Engine"]]; } // CSV storage engine was added in MySQL 4.1.4 if ([connection serverReleaseVersion] >= 4) { [storageEngines addObject:[NSDictionary dictionaryWithObject:@"CSV" forKey:@"Engine"]]; } } } // The table information_schema.engines didn't exist until MySQL 5.1.5 else { if (([connection serverMajorVersion] >= 5) && ([connection serverMinorVersion] >= 1) && ([connection serverReleaseVersion] >= 5)) { // Check the information_schema.engines table is accessible MCPResult *result = [connection queryString:@"SHOW TABLES IN information_schema LIKE 'ENGINES'"]; if ([result numOfRows] == 1) { // Table is accessible so get available storage engines [storageEngines addObjectsFromArray:[self _getDatabaseDataForQuery:@"SELECT Engine, Support FROM information_schema.engines WHERE support IN ('DEFAULT', 'YES');"]]; } } else { // Get storage engines NSMutableArray *engines = [self _getDatabaseDataForQuery:@"SHOW STORAGE ENGINES"]; // We only want to include engines that are supported for (NSDictionary *engine in engines) { if (([[engine objectForKey:@"Support"] isEqualToString:@"DEFAULT"]) || ([[engine objectForKey:@"Support"] isEqualToString:@"YES"])) { [storageEngines addObject:engine]; } } } } } return storageEngines; } /** * Returns all of the database's currently available character set encodings by querying * information_schema.character_sets. */ - (NSArray *)getDatabaseCharacterSetEncodings { if ([characterSetEncodings count] == 0) { // Try to retrieve the available character set encodings from the database // Check the information_schema.character_sets table is accessible if ([connection serverMajorVersion] >= 5) [characterSetEncodings addObjectsFromArray:[self _getDatabaseDataForQuery:@"SELECT * FROM `information_schema`.`character_sets` ORDER BY `character_set_name` ASC"]]; // If that failed, get the list of character set encodings from the hard-coded list if (![characterSetEncodings count]) { const CHAR_SETS *c = charsets; do { [characterSetEncodings addObject:[NSDictionary dictionaryWithObjectsAndKeys: [NSString stringWithCString:c->name encoding:NSUTF8StringEncoding], @"CHARACTER_SET_NAME", [NSString stringWithCString:c->description encoding:NSUTF8StringEncoding], @"DESCRIPTION", nil]]; ++c; } while (c[0].nr != 0); } } return characterSetEncodings; } /** * Deallocate ivars. */ - (void)dealloc { if (characterSetEncoding != nil) { [characterSetEncoding release], characterSetEncoding = nil; } [collations release], collations = nil; [characterSetCollations release], characterSetCollations = nil; [storageEngines release], storageEngines = nil; [characterSetEncodings release], characterSetEncodings = nil; [super dealloc]; } @end @implementation SPDatabaseData (PrivateAPI) /** * Executes the supplied query against the current connection and returns the result as an array of * NSDictionarys, one for each row. */ - (NSMutableArray *)_getDatabaseDataForQuery:(NSString *)query { NSMutableArray *array = [NSMutableArray array]; MCPResult *result = [connection queryString:query]; // Log any errors if ([[connection getLastErrorMessage] isEqualToString:@""]) { [result dataSeek:0]; for (NSInteger i = 0; i < [result numOfRows]; i++) { [array addObject:[result fetchRowAsDictionary]]; } } return array; } @end