PBMySQLChannel.m
#import "Aprica.h"
// Aprica2
// copyright Pirmin Braun 1997-2007 - pirmin@pirmin.de
// all Rights reserved;
@implementation PBMySQLChannel
ACCESSClassm(selectedAttributes,setSelectedAttributes,NSArray)
- init;
{if(!(self = [super init]))return nil;
sock = NULL;
isOpen = NO;
isFetchInProgress = NO;
dbNr = 0;
return self;
}
- (int)dbNr;
{return dbNr;
}
- (BOOL)isOpen;
{return isOpen;
}
- (BOOL)isFetchInProgress;
{return isFetchInProgress && isOpen;
}
- (BOOL)openChannel;
{isOpen = [self connect];
isUTF8 = NO;
if(isOpen){NSDictionary *d;
[self evaluateSQL:@"show variables like 'character_set_database%';"];
if(![self isFetchInProgress]){LOGS((@"show variables failed"));
return YES; //egal, kann ja auch alte MySQL sein
}
[self setSelectedAttributes:[self describeResults]];
d = [self fetchRow];
[self cancelFetch];
if(!d){LOGS((@"show variables returned no selectedAttributes"));
return YES;
}
if([[d ofk:@"Value"] iE:@"utf8"]){isUTF8 = YES;
[self evaluateSQL:@"set names utf8;"];
LOGS(@"detected UTF8 encoding, setting names to utf8");
}else{LOGS(@"using default encoding Latin1");
}
}
return isOpen;
}
- (void)dealloc;
{[selectedAttributes release];
[super dealloc];
}
- (void)closeChannel;
{ if (sock >= 0) { if ([self isFetchInProgress]) {[self cancelFetch];
}
pb_mysql_close(sock);
sock = NULL;
isOpen = NO;
}
}
- (BOOL)connect;
{NSString *hostname;
NSString *database;
NSString *user;
NSString *password;
NSString *hostnameName,*userName,*passwordName,*portName;
unsigned int port;
dbNr = [_APP dbNr];
if(dbNr){hostnameName = [NSSWF @"hostname%i",dbNr];
database = [[_APP configDict]ofk:[NSSWF @"database%i",dbNr]];
userName = [NSSWF @"dbuser%i",dbNr];
passwordName = [NSSWF @"dbpw%i",dbNr];
portName = [NSSWF @"dbport%i",dbNr];
}else{hostnameName = @"hostname";
database = [NSSWF @"%@_%@",[NSAPPNAME lowercaseString],[_APP db_mandant]];
userName = @"dbuser";
passwordName = @"dbpw";
portName = @"dbport";
}
hostname = [[_APP configDict]ofk:hostnameName];
user = [[_APP configDict]ofk:userName];
password = [[_APP configDict]ofk:passwordName];
port = [[[_APP configDict]ofk:portName]intValue]; // wenn nichts angegeben, 0
if(!FILLED(hostname))hostname = @"localhost";
if(!FILLED(user))user = @"root";
if(!FILLED(password))password = @"root";
sock = pb_mysql_init(sock);
sock = pb_mysql_connect(sock, (char *)[hostname cString], (char *)[user cString], (char *)[password cString], port);
if (!sock) {LOGS(([NSSWF @"keine Verbindung mit diesen Einstellungen: hostname %@ database %@ user %@ password %@ port %i",hostname,database,user,password,port]));
return NO;
}
if (pb_mysql_select_db(sock, (char *)[database cString]) < 0) {NSString *error = [NSString stringWithFormat:@"Could not open MySQL database %@", database];
LOGS(error);
pb_mysql_close(sock);
return NO;
}
return YES;
}
- (unsigned)insertRow:(NSDictionary *)row forTable:(PBDDTable *)t;
{NSMutableString *ms;
unsigned n;
if(!row || !t)return 0;
if(!(ms = [_APP sqlValuesStringFromDict:row forTable:t]))return 0;
[ms insertString:[NSSWF @"insert %@ set ",[t dbName]] atIndex:0];
[ms appendString:@";"];
n = [self evaluateSQL:ms];
//insert returned keinen resultset und muss daher auch nicht beenden [self endFetch];
return n;
}
- (unsigned)updateRow:(NSDictionary *)row forTable:(PBDDTable *)t pk:(NSString *)pk;
{//mit dem primary key updaten; wird hier extra uebergeben, da er in den delta-werten der row nicht drin ist
NSString *pkn = [t primaryKeyName];
NSMutableString *ms;
unsigned n;
if(!row || !t || !FILLED(pk))return 0;
if(!(ms = [_APP sqlValuesStringFromDict:row forTable:t]))return 0;
[ms insertString:[NSSWF @"update low_priority %@ set ",[t dbName]] atIndex:0];
[ms appendString:[NSSWF @" where %@=\"%@\";",pkn,pk]];
n = [self evaluateSQL:ms];
//update returned keinen resultset und muss daher auch nicht beenden [self endFetch];
return n;
}
- (unsigned)deleteRow:(NSDictionary *)dict forTable:(PBDDTable *)t;
{//mit dem primary key deleten;
NSString *pkn = [t primaryKeyName],*s;
unsigned n;
if(!dict || !t)return 0;
s = [NSSWF @"delete from %@ where %@=\"%@\";",[t dbName],pkn,[[dict ofk:pkn] mysqlEscapedString]];
n = [self evaluateSQL:s];
//delete returned keinen resultset und muss daher auch nicht beenden [self endFetch];
return n;
}
- (unsigned)selectQualifier:(PBSQLQualifier *)q forTable:(PBDDTable *)t tn:(NSString *)tn offset:(int)offset count:(int)count soa:(NSArray *)soa;
{//t ist fuer die Attribute
//von tn wird gelesen
//bei seq. access ist tn eine vorher erstellte temptable mit den abgegrenzten daten;
//diese wird dann satzweise verarbeitet; die echte table steht dann schon wieder zur verfuegung, waehrend die tn bis zum ende der verarbeitung gesperrt ist
NSString *wc,*s,*obyc,*limitc=EON;
BOOL useResult; //use or store Result; wenn tn != [t dbName] -> seq.Access -> useResult
LMAN(colNames);
LMAN(attributes);
NSArray *a;
int i,j;
if(!t)return 0;
[colNames addObjectsFromArray:[[t attributesDB] valuesForKey:@"dbName"]];
[attributes addObjectsFromArray:colNames];
a = [t attributesDBFetch]; // SQL: Felder
[colNames addObjectsFromArray:[a valuesForKey:@"dbName"]]; // SQL-Expression; nicht in DB;
if(![colNames count])return 0;
[self setSelectedAttributes:colNames];
for(i=0,j=[a count];i<j;i++){PBDDAttribute *pba = [a oai:i];
// weil bei sequential Access von temp-table gelesen wird, in subselects bei bezug auf eigene tabelle $t reinschreiben
// wird dann zur konkreten Tabelle (myTable.dbName bzw. tempTableName) expandiert
NSString *expandedExpression = [[[pba expression]substringFromIndex:4]replace:@"$t" with:tn];
[attributes addObject:[NSSWF @"%@ as %@",expandedExpression,[pba dbName]]];
}
wc = [q string];
if(!wc)wc=@"";
obyc = [_APP orderbyClauseFromSoa:soa];
if(FILLED(wc))wc = [NSSWF @" where %@",wc];
if(count)limitc = [NSSWF @"limit %i,%i",offset,count];
s = [NSSWF @"select %@ from %@ %@ %@ %@;",[attributes componentsJoinedByString:@","],tn,wc,obyc,limitc];
useResult = NO;
if (result){pb_mysql_free_result(result);
result = NULL;
}
return [self evaluateSQL:s useResult:useResult];
}
- (unsigned)deleteRowsDescribedByQualifier:(PBSQLQualifier *)q forTable:(PBDDTable *)t;
{NSString *wc,*s;
unsigned n;
if(!t)return 0;
wc = [q string];
if(!wc)wc=@"";
if(FILLED(wc))wc = [NSSWF @" where %@",wc];
s = [NSSWF @"delete from %@ %@;",[t dbName],wc];
n = [self evaluateSQL:s];
//delete returned keinen resultset und muss daher auch nicht beenden [self endFetch];
return n;
}
- (NSMutableDictionary *)fetchRow;
{NSString *s,*v;
NSData *data;
int i;
int nelem;
int len;
unsigned long *lens;
NSMutableDictionary *row=nil;
if(![self isFetchInProgress])return nil;
//wird nil returned, ist die Verbindung auch schon geschlossen
if(!result){[self endFetch];
return nil;
}
if(!(cur = pb_mysql_fetch_row(result))){const char *mysql_error = pb_mysql_error(sock);
if(mysql_error[0]){LOG(([NSSWF @"--- **** MySQL-Error: %s",mysql_error]));
}
[self endFetch];
// ist normal LOGS(@"no pb_mysql_fetch_row(result)");
return nil;
}
if(!selectedAttributes){[self endFetch];
LOGS(@"no selectedAttributes");
return nil;
}
lens = pb_mysql_fetch_lengths(result);
nelem = pb_mysql_num_fields(result);
row = [[NSMutableDictionary alloc]initWithCapacity:nelem];
//die strings wie sie aus der datenbank kommen; in eoFromDict werden evt. Konvertierungen z.B. f. Datum vorgenommen
for (i = 0; i < nelem; i++){s = [selectedAttributes oai:i];
len = lens[i];
if (cur[i] == NULL){[row setSecureObject:EON forKey:s];
continue;
}
data = [[NSData alloc]initWithBytes:cur[i] length:len];
v = [[NSString alloc] initWithData:data encoding:(isUTF8?NSUTF8StringEncoding:NSWindowsCP1252StringEncoding)];
[data release];
if(!v){[row setSecureObject:EON forKey:s];
continue;
}
[row setSecureObject:v forKey:s];
[v release];
}
//wozu? pb_mysql_field_seek(result, 0);
return [row autorelease];
}
- (void)cancelFetch;
{[self endFetch];
}
- (NSArray *)describeResults;
{// liefert nur noch dbNamen
LMA;
if(!result)return nil;
while ((curField = pb_mysql_fetch_field(result))) {[lma addObject:[NSString stringWithCString:curField->name]]; // feldnamen sollten also cString kompatibel sein!
}
return lma;
}
- (unsigned)evaluateSQL:(NSString *)s;
{return [self evaluateSQL:s useResult:NO]; //Default storeResult
}
- (unsigned)evaluateSQL:(NSString *)s useResult:(BOOL)useResult;
{NSData *data;
NSString *s1;
int sql_rc;
if(!FILLED(s))return 0;
if([_APP orbDebug])LOGS(s);
// if([_APP orbDebugStack])PRINTCURRENTSTACK;
data = [s dataUsingEncoding:(isUTF8?NSUTF8StringEncoding:NSWindowsCP1252StringEncoding) allowLossyConversion:YES];
[_APP setLastSQLError:EON];
sql_rc = (pb_mysql_real_query(sock, (char *)[data bytes], [data length]));
if(sql_rc){// nach 8 h inaktivitaet (default) schliesst MySQL den Socket; wird dann nochmal der Channel verwendet, gibt es einen lost Connection error
// danach geht der Channel aber wieder, allerdings mit Latin1 als Client-characterset
s1 = [NSSWF @"could not evaluate expression \"%@\"\n***Error: %i %s", s,sql_rc,pb_mysql_error(sock)];
LOGS((s1));
[_APP setLastSQLError:s1];
// PRINTCURRENTSTACK;
[self closeChannel]; //damit er wieder geoeffnet werden muss und setNames laeuft;
return 0;
}else{ if(pb_mysql_field_count(sock)){ //sollte resultset haben if (result){ //evt. alten result-set wegpb_mysql_free_result(result);
result = NULL;
}
//kein return 0 hier, da es auch statements gibt, die kein result liefern, z.B. "lock tables"
if(useResult){result = pb_mysql_use_result(sock); //zeile fuer zeile
if(!result){// if([_APP orbDebug])LOGS(@"no result");
} else if(result->eof){// if([_APP orbDebug])LOGS(@"result->eof");
}
}else{result = pb_mysql_store_result(sock); //alles auf einmal auf den client
if(!result){// if([_APP orbDebug])LOGS(@"no result");
}
}
if(result)[self beginFetch];
}
}
return 1;
}
- (void)beginFetch;
{// if([_APP orbDebug])LOGS(@"did beginFetch");
isFetchInProgress = YES;
}
- (void)endFetch;
{ if (isFetchInProgress) {isFetchInProgress = NO;
[self setSelectedAttributes:nil];
if (result) {pb_mysql_free_result(result);
result = NULL;
}
}
}
- (NSArray *)describeDatabase;
{//die PBDDTables meines conDict liefern;
//dieses muss aktiv sein und die Verbindung zur db muss stehen;
NSArray *a = [self describeTableNames];
int i,j;
LMA;
NSString *tn;
// LOGS(([NSSWF @"APP.utf8db = %@",[_APP utf8db]?@"J":@"N"]));
for(i=0,j=[a count];i<j;i++){tn = [a oai:i];
if([tn hasSecurePrefix:@"query_"] || [tn hasSecurePrefix:@"keyword_"])continue;
[lma addObject:[self describeTableWithTableName:tn]];
}
return lma;
}
- (NSArray *)describeTableNames;
{LMA;
if (result){pb_mysql_free_result(result);
result = NULL;
}
result = pb_mysql_list_tables(sock, NULL);
while ((cur = pb_mysql_fetch_row(result))) {if(strncmp(cur[0], "EO_", 3) != 0)
[lma addObject:[NSString stringWithCString:cur[0]]];
}
if(result){pb_mysql_free_result(result);
result = NULL;
}
return lma;
}
- (PBDDTable *)describeTableWithTableName:(NSString *)tableName;
{PBDDTable *t = [[PBDDTable alloc]init];
[t setGuiName:[tableName capitalizedString]];
[t setDbName:tableName];
[t setType:SC_Real];
[t addAttributes:[self describeAttributesForTableNameNew:tableName]];
return [t autorelease];
}
- (NSArray *)describeAttributesForTableNameNew:(NSString *)tableName;
{NSString *sql = [NSSWF @"show columns from %@",tableName];
LMA;
NSDictionary *d;
[self evaluateSQL:sql];
if(![self isFetchInProgress])return lma;
[self setSelectedAttributes:[self describeResults]];
while((d=[self fetchRow])){PBDDAttribute *pba;
NSString *type;
pba = [[PBDDAttribute alloc] init];
[pba setGuiName:[[d ofk:@"Field"] capitalizedString]];
[pba setDbName:[[d ofk:@"Field"]lowercaseString]];
type = [d ofk:@"Type"];
if([type hasSecurePrefix:@"varchar("]){[pba setDataTyp:DT_CHAR];
[pba setNak:0];
[pba setLength:[[type substringFromIndex:[@"varchar(" length]]intValue]]; }else if([type hasSecurePrefix:@"char("]){[pba setDataTyp:DT_CHAR];
[pba setNak:0];
[pba setLength:[[type substringFromIndex:[@"char(" length]]intValue]]; }else if([type hasSecurePrefix:@"char"]){[pba setDataTyp:DT_CHAR];
[pba setNak:0];
[pba setLength:1];
}else if([type iE:@"double"]){[pba setDataTyp:DT_FLOAT];
[pba setNak:5];
[pba setLength:15];
}else if([type hasSecurePrefix:@"decimal("]){ NSArray *a2 = [[type substringFromIndex:[@"decimal(" length]]componentsSeparatedByString:@","];[pba setDataTyp:DT_FLOAT];
[pba setNak:[[a2 lastObject]intValue]];
[pba setLength:[[a2 firstObject]intValue]];
}else if([type hasSecurePrefix:@"int("] || [type hasSecurePrefix:@"tinyint("]){[pba setDataTyp:DT_INT];
[pba setNak:0];
[pba setLength:[[type substringFromIndex:[@"int(" length]]intValue]]; }else if([type hasSecurePrefix:@"datetime"]){[pba setDataTyp:DT_DATETIME];
[pba setNak:0];
[pba setLength:14];
}else if([type hasSecurePrefix:@"date"]){[pba setDataTyp:DT_DATE];
[pba setNak:0];
[pba setLength:8];
}else if([type iE:@"text"] || [type iE:@"longtext"] || [type iE:@"mediumtext"]){// auch mal eigenen text typ machen
[pba setDataTyp:DT_CHAR];
[pba setNak:0];
[pba setLength:65535];
// FIELD_TYPE_NEWDECIMAL
/*
case 246:
nak = curField->decimals; //nachkomma
[pba setDataTyp:DT_FLOAT];
[pba setNak:nak];
[pba setLength:curField->length];
break;
*/
}else{LOGS(([NSSWF @"unbekannter Datentyp:%@ in table %@:\n%@",type,tableName,[d description]]));
[pba setDataTyp:DT_CHAR];
[pba setNak:0];
[pba setLength:20];
}
if([[d ofk:@"Key"]iE:@"PRI"]){[pba setKeyTyp:KT_PRIM];
}
// add to list of attributes
[lma addObject:pba];
[pba release];
}
[self cancelFetch];
return lma;
}
- (BOOL)isUTF8;
{return isUTF8;
}
@end
