mORMot and Open Source friends
Check-in [8cca20d7e0]
Check-in [8cca20d7e0]
Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | {554} handle per-row column type in SynDB remote access (SQLite3 only) e.g. select coalesce(column,0) from .. |
|---|---|
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
8cca20d7e05ec73311359ca892ec8a4a |
| User & Date: | ab 2014-11-21 19:15:45 |
|
2014-11-22
| ||
| 10:41 | {555} added new project publishing Cross-Platform wrappers for sample 28 check-in: b0fd38d077 user: ab tags: trunk | |
|
2014-11-21
| ||
| 19:15 | {554} handle per-row column type in SynDB remote access (SQLite3 only) e.g. select coalesce(column,0) from .. check-in: 8cca20d7e0 user: ab tags: trunk | |
| 18:19 | {553} fixed Delphi XE7 specific issue our read/only TDataSet check-in: ac81632ed2 user: ab tags: trunk | |
Changes to SynDB.pas.
2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 .... 6134 6135 6136 6137 6138 6139 6140 6141 6142 6143 6144 6145 6146 6147 6148 6149 6150 6151 6152 6153 6154 6155 6156 6157 6158 6159 6160 6161 6162 6163 6164 6165 6166 6167 6168 6169 6170 6171 6172 6173 6174 6175 6176 6177 6178 6179 6180 6181 6182 6183 6184 6185 6186 6187 6188 6189 6190 6191 6192 6193 6194 6195 6196 6197 6198 6199 .... 6221 6222 6223 6224 6225 6226 6227 6228 6229 6230 6231 6232 6233 6234 .... 7212 7213 7214 7215 7216 7217 7218 7219 7220 7221 7222 7223 7224 7225 7226 7227 7228 7229 7230 7231 7232 7233 7234 7235 7236 7237 7238 7239 7240 7241 7242 7243 7244 7245 7246 7247 7248 7249 7250 7251 7252 7253 7254 7255 7256 7257 7258 7259 7260 7261 7262 7263 7264 7265 7266 7267 7268 7269 7270 7271 7272 7273 7274 7275 7276 7277 7278 7279 7280 7281 7282 7283 7284 7285 7286 7287 7288 7289 7290 .... 7291 7292 7293 7294 7295 7296 7297 7298 7299 7300 7301 7302 7303 7304 7305 7306 7307 7308 .... 7313 7314 7315 7316 7317 7318 7319 7320 7321 7322 7323 7324 7325 7326 7327 7328 7329 7330 7331 7332 .... 7348 7349 7350 7351 7352 7353 7354 7355 7356 7357 7358 7359 7360 7361 7362 7363 7364 7365 7366 7367 7368 7369 7370 7371 7372 7373 7374 |
// - will handle an internal binary buffer when the statement returned rows
// data, as generated by TSQLDBStatement.FetchAllToBinary()
TSQLDBProxyStatementAbstract = class(TSQLDBStatementWithParamsAndColumns)
protected
fDataRowCount: integer;
fDataRowReaderOrigin, fDataRowReader: PByte;
fDataRowNullSize: cardinal;
fDataCurrentRowIndex: integer;
fDataCurrentRowNull: TSQLDBProxyStatementColumns;
fDataCurrentRowNullLen: cardinal;
fDataCurrentRowValues: array of pointer;
fDataCurrentRowValuesStart: pointer;
fDataCurrentRowValuesSize: Cardinal;
function IntColumnType(Col: integer; out Data: PByte): TSQLDBFieldType;
{$ifdef HASINLINE}inline;{$endif}
procedure IntHeaderProcess(Data: PByte; DataLen: integer);
procedure IntFillDataCurrent(var Reader: PByte);
public
/// the Column type of the current Row
function ColumnType(Col: integer; FieldSize: PInteger=nil): TSQLDBFieldType; override;
................................................................................
procedure TSQLDBStatement.ColumnsToBinary(W: TFileBufferWriter;
const Null: TSQLDBProxyStatementColumns; const ColTypes: TSQLDBFieldTypeDynArray);
var F: integer;
VDouble: double;
VCurrency: currency absolute VDouble;
VDateTime: TDateTime absolute VDouble;
begin
for F := 0 to length(ColTypes)-1 do
if not (F in Null) then
case ColTypes[F] of
ftInt64:
W.WriteVarInt64(ColumnInt(F));
ftDouble: begin
VDouble := ColumnDouble(F);
W.Write(@VDouble,sizeof(VDouble));
end;
ftCurrency: begin
VCurrency := ColumnCurrency(F);
W.Write(@VCurrency,sizeof(VCurrency));
end;
ftDate: begin
VDateTime := ColumnDateTime(F);
W.Write(@VDateTime,sizeof(VDateTime));
end;
ftUTF8:
W.Write(ColumnUTF8(F));
ftBlob:
W.Write(ColumnBlob(F));
else raise ESQLDBException.CreateUTF8(
'%.ColumnsToBinary: invalid ColTypes[%]=%',[self,F,ord(ColTypes[F])]);
end;
end;
const
FETCHALLTOBINARY_MAGIC = 1;
function TSQLDBStatement.FetchAllToBinary(Dest: TStream; MaxRowCount: cardinal;
DataRowPosition: PCardinalDynArray): cardinal;
var F, FMax, FieldSize, NullRowSize: integer;
StartPos: cardinal;
Null: TSQLDBProxyStatementColumns;
W: TFileBufferWriter;
ColTypes: TSQLDBFieldTypeDynArray;
begin
result := 0;
W := TFileBufferWriter.Create(Dest);
try
FMax := ColumnCount;
if FMax>0 then
// write column description
W.WriteVarUInt32(FETCHALLTOBINARY_MAGIC);
W.WriteVarUInt32(FMax);
SetLength(ColTypes,FMax);
dec(FMax);
for F := 0 to FMax do begin
W.Write(ColumnName(F));
ColTypes[F] := ColumnType(F,@FieldSize);
W.Write(@ColTypes[F],sizeof(ColTypes[0]));
W.WriteVarUInt32(FieldSize);
end;
// initialize null handling
NullRowSize := (FMax shr 3)+1;
if NullRowSize>sizeof(Null) then
raise ESQLDBException.CreateUTF8('%.FetchAllToBinary: too many columns',[self]);
// save all data rows
................................................................................
W.Write(@Null,NullRowSize);
// then write data values
ColumnsToBinary(W,Null,ColTypes);
inc(result);
if (MaxRowCount>0) and (result>=MaxRowCount) then
break;
until not Step;
W.Write(@result,SizeOf(result)); // fixed size at the end for row count
W.Flush;
finally
W.Free;
end;
end;
................................................................................
fProxy.Process(cStartTransaction,self,self);
end;
{ TSQLDBProxyStatementAbstract }
procedure TSQLDBProxyStatementAbstract.IntHeaderProcess(Data: PByte; DataLen: integer);
var Magic,F: integer;
begin
fDataCurrentRowValuesStart := nil;
fDataCurrentRowValuesSize := 0;
fDataCurrentRowIndex := -1;
repeat
if DataLen<=5 then
break;
fDataRowCount := PInteger(PtrInt(Data)+DataLen-sizeof(Integer))^;
Magic := FromVarUInt32(Data);
if Magic<>FETCHALLTOBINARY_MAGIC then
break;
for F := 1 to FromVarUInt32(Data) do
with PSQLDBColumnProperty(fColumn.AddAndMakeUniqueName(FromVarString(Data)))^ do begin
ColumnType := TSQLDBFieldType(Data^);
inc(Data);
ColumnValueDBSize := FromVarUInt32(Data);
end;
if fColumnCount=0 then
exit; // no data returned
if (fColumnCount>sizeof(TSQLDBProxyStatementColumns)shl 3) or
(cardinal(fDataRowCount)>=cardinal(DataLen) div cardinal(fColumnCount)) then
break;
fDataRowReaderOrigin := Data;
fDataRowReader := Data;
fDataRowNullSize := ((fColumnCount-1) shr 3)+1;
SetLength(fDataCurrentRowValues,fColumnCount);
exit;
until false;
fDataRowCount := 0;
fColumnCount := 0;
raise ESQLDBException.CreateUTF8('Invalid %.IntHeaderProcess',[self]);
end;
procedure TSQLDBProxyStatementAbstract.IntFillDataCurrent(var Reader: PByte);
var F, Len: Integer;
begin
if fDataCurrentRowNullLen>0 then
FillChar(fDataCurrentRowNull,fDataCurrentRowNullLen,0);
fDataCurrentRowNullLen := FromVarUInt32(Reader);
if fDataCurrentRowNullLen>fDataRowNullSize then
raise ESQLDBException.CreateUTF8('Invalid %.IntFillDataCurrent',[self]);
if fDataCurrentRowNullLen>0 then begin
Move(Reader^,fDataCurrentRowNull,fDataCurrentRowNullLen);
inc(Reader,fDataCurrentRowNullLen);
end;
fDataCurrentRowValuesStart := Reader;
for F := 0 to fColumnCount-1 do
if F in fDataCurrentRowNull then
fDataCurrentRowValues[F] := nil else begin
fDataCurrentRowValues[F] := Reader;
with fColumns[F] do
case ColumnType of
ftInt64:
Reader := GotoNextVarInt(Reader);
ftDouble, ftCurrency, ftDate:
inc(Reader,SizeOf(Int64));
ftUTF8, ftBlob: begin
Len := FromVarUInt32(Reader);
if Len>ColumnDataSize then
ColumnDataSize := Len;
inc(Reader,Len); // jump string/blob content
end;
else raise ESQLDBException.CreateUTF8('%.IntStep: Invalid ColumnType(%)=%',
[self,ColumnName,ord(ColumnType)]);
end;
end;
fDataCurrentRowValuesSize := PtrUInt(Reader)-PtrUInt(fDataCurrentRowValuesStart);
end;
procedure TSQLDBProxyStatementAbstract.ColumnsToJSON(WR: TJSONWriter);
var col, DataLen: integer;
................................................................................
Data: PByte;
begin
if WR.Expand then
WR.Add('{');
for col := 0 to fColumnCount-1 do begin
if WR.Expand then
WR.AddFieldName(fColumns[col].ColumnName); // add '"ColumnName":'
Data := fDataCurrentRowValues[col];
if Data=nil then
WR.AddShort('null') else
case fColumns[col].ColumnType of
ftInt64:
WR.Add(FromVarInt64Value(Data));
ftDouble:
WR.Add(PDouble(Data)^);
ftCurrency:
WR.AddCurr64(PInt64(Data)^);
ftDate: begin
................................................................................
ftUTF8: begin
WR.Add('"');
DataLen := FromVarUInt32(Data);
WR.AddJSONEscape(Data,DataLen);
WR.Add('"');
end;
ftBlob:
if fForceBlobAsNull then
WR.AddShort('null') else begin
// WrBase64(..,withMagic=true)
DataLen := FromVarUInt32(Data);
WR.WrBase64(PAnsiChar(Data),DataLen,true);
end;
end;
WR.Add(',');
end;
WR.CancelLastComma; // cancel last ','
if WR.Expand then
WR.Add('}');
end;
................................................................................
begin
if (fDataRowCount>0) and (cardinal(Col)<cardinal(fColumnCount)) then
if Col in fDataCurrentRowNull then
result := ftNull else
with fColumns[Col] do begin
if FieldSize<>nil then
FieldSize^ := ColumnDataSize; // true max size as computed at loading
result := ColumnType;
end else
raise ESQLDBException.CreateUTF8('Invalid %.ColumnType()',[self]);
end;
function TSQLDBProxyStatementAbstract.IntColumnType(Col: integer; out Data: PByte): TSQLDBFieldType;
begin
if (cardinal(Col)>=cardinal(fColumnCount)) or (fDataCurrentRowValues=nil) then
result := ftUnknown else begin
Data := fDataCurrentRowValues[Col];
if Data=nil then
result := ftNull else
result := fColumns[Col].ColumnType;
end;
end;
function TSQLDBProxyStatementAbstract.ColumnCurrency(Col: integer): currency;
var Data: PByte;
begin
case IntColumnType(Col,Data) of
|
| | > > > | | > > > > > | | | | | | | | | | | | | | | | | | | < > > > > > | < < | > | | > > > > < | | > > > > > < < > | | | | | | | | | | | | | |
2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 .... 6136 6137 6138 6139 6140 6141 6142 6143 6144 6145 6146 6147 6148 6149 6150 6151 6152 6153 6154 6155 6156 6157 6158 6159 6160 6161 6162 6163 6164 6165 6166 6167 6168 6169 6170 6171 6172 6173 6174 6175 6176 6177 6178 6179 6180 6181 6182 6183 6184 6185 6186 6187 6188 6189 6190 6191 6192 6193 6194 6195 6196 6197 6198 6199 6200 6201 6202 6203 6204 6205 6206 6207 6208 6209 .... 6231 6232 6233 6234 6235 6236 6237 6238 6239 6240 6241 6242 6243 6244 6245 .... 7223 7224 7225 7226 7227 7228 7229 7230 7231 7232 7233 7234 7235 7236 7237 7238 7239 7240 7241 7242 7243 7244 7245 7246 7247 7248 7249 7250 7251 7252 7253 7254 7255 7256 7257 7258 7259 7260 7261 7262 7263 7264 7265 7266 7267 7268 7269 7270 7271 7272 7273 7274 7275 7276 7277 7278 7279 7280 7281 7282 7283 7284 7285 7286 7287 7288 7289 7290 7291 7292 7293 7294 7295 7296 7297 7298 7299 7300 7301 7302 7303 7304 7305 7306 7307 7308 .... 7309 7310 7311 7312 7313 7314 7315 7316 7317 7318 7319 7320 7321 7322 7323 7324 7325 7326 .... 7331 7332 7333 7334 7335 7336 7337 7338 7339 7340 7341 7342 7343 7344 7345 7346 7347 7348 7349 7350 .... 7366 7367 7368 7369 7370 7371 7372 7373 7374 7375 7376 7377 7378 7379 7380 7381 7382 7383 7384 7385 7386 7387 7388 7389 7390 7391 7392 |
// - will handle an internal binary buffer when the statement returned rows
// data, as generated by TSQLDBStatement.FetchAllToBinary()
TSQLDBProxyStatementAbstract = class(TSQLDBStatementWithParamsAndColumns)
protected
fDataRowCount: integer;
fDataRowReaderOrigin, fDataRowReader: PByte;
fDataRowNullSize: cardinal;
fDataCurrentRowNullLen: cardinal;
fDataCurrentRowNull: TSQLDBProxyStatementColumns;
fDataCurrentRowIndex: integer;
fDataCurrentRowValues: array of pointer;
fDataCurrentRowValuesStart: pointer;
fDataCurrentRowValuesSize: Cardinal;
// per-row column type (SQLite3 only) e.g. select coalesce(column,0) from ..
fDataCurrentRowColTypes: array of TSQLDBFieldType;
function IntColumnType(Col: integer; out Data: PByte): TSQLDBFieldType;
{$ifdef HASINLINE}inline;{$endif}
procedure IntHeaderProcess(Data: PByte; DataLen: integer);
procedure IntFillDataCurrent(var Reader: PByte);
public
/// the Column type of the current Row
function ColumnType(Col: integer; FieldSize: PInteger=nil): TSQLDBFieldType; override;
................................................................................
procedure TSQLDBStatement.ColumnsToBinary(W: TFileBufferWriter;
const Null: TSQLDBProxyStatementColumns; const ColTypes: TSQLDBFieldTypeDynArray);
var F: integer;
VDouble: double;
VCurrency: currency absolute VDouble;
VDateTime: TDateTime absolute VDouble;
colType: TSQLDBFieldType;
begin
for F := 0 to length(ColTypes)-1 do
if not (F in Null) then begin
colType := ColTypes[F];
if colType<ftInt64 then begin
colType := ColumnType(F); // per-row column type (SQLite3 only)
W.Write1(ord(colType));
end;
case colType of
ftInt64:
W.WriteVarInt64(ColumnInt(F));
ftDouble: begin
VDouble := ColumnDouble(F);
W.Write(@VDouble,sizeof(VDouble));
end;
ftCurrency: begin
VCurrency := ColumnCurrency(F);
W.Write(@VCurrency,sizeof(VCurrency));
end;
ftDate: begin
VDateTime := ColumnDateTime(F);
W.Write(@VDateTime,sizeof(VDateTime));
end;
ftUTF8:
W.Write(ColumnUTF8(F));
ftBlob:
W.Write(ColumnBlob(F));
else raise ESQLDBException.CreateUTF8('%.ColumnsToBinary: Invalid ColumnType(%)=%',
[self,ColumnName(F),ord(colType)]);
end;
end;
end;
const
FETCHALLTOBINARY_MAGIC = 1;
function TSQLDBStatement.FetchAllToBinary(Dest: TStream; MaxRowCount: cardinal;
DataRowPosition: PCardinalDynArray): cardinal;
var F, FMax, FieldSize, NullRowSize: integer;
StartPos: cardinal;
Null: TSQLDBProxyStatementColumns;
W: TFileBufferWriter;
ColTypes: TSQLDBFieldTypeDynArray;
begin
FillChar(Null,sizeof(Null),0);
result := 0;
W := TFileBufferWriter.Create(Dest);
try
W.WriteVarUInt32(FETCHALLTOBINARY_MAGIC);
FMax := ColumnCount;
W.WriteVarUInt32(FMax);
if FMax>0 then begin
// write column description
SetLength(ColTypes,FMax);
dec(FMax);
for F := 0 to FMax do begin
W.Write(ColumnName(F));
ColTypes[F] := ColumnType(F,@FieldSize);
W.Write1(ord(ColTypes[F]));
W.WriteVarUInt32(FieldSize);
end;
// initialize null handling
NullRowSize := (FMax shr 3)+1;
if NullRowSize>sizeof(Null) then
raise ESQLDBException.CreateUTF8('%.FetchAllToBinary: too many columns',[self]);
// save all data rows
................................................................................
W.Write(@Null,NullRowSize);
// then write data values
ColumnsToBinary(W,Null,ColTypes);
inc(result);
if (MaxRowCount>0) and (result>=MaxRowCount) then
break;
until not Step;
end;
W.Write(@result,SizeOf(result)); // fixed size at the end for row count
W.Flush;
finally
W.Free;
end;
end;
................................................................................
fProxy.Process(cStartTransaction,self,self);
end;
{ TSQLDBProxyStatementAbstract }
procedure TSQLDBProxyStatementAbstract.IntHeaderProcess(Data: PByte; DataLen: integer);
var Magic,F,colCount: integer;
begin
fDataCurrentRowValuesStart := nil;
fDataCurrentRowValuesSize := 0;
fDataCurrentRowIndex := -1;
repeat
if DataLen<=5 then
break;
fDataRowCount := PInteger(PtrInt(Data)+DataLen-sizeof(Integer))^;
Magic := FromVarUInt32(Data);
if Magic<>FETCHALLTOBINARY_MAGIC then
break;
colCount := FromVarUInt32(Data);
SetLength(fDataCurrentRowColTypes,colCount);
SetLength(fDataCurrentRowValues,colCount);
for F := 0 to colCount-1 do
with PSQLDBColumnProperty(fColumn.AddAndMakeUniqueName(FromVarString(Data)))^ do begin
ColumnType := TSQLDBFieldType(Data^);
fDataCurrentRowColTypes[F] := ColumnType;
inc(Data);
ColumnValueDBSize := FromVarUInt32(Data);
end;
if fColumnCount=0 then
exit; // no data returned
if (fColumnCount>sizeof(TSQLDBProxyStatementColumns)shl 3) or
(cardinal(fDataRowCount)>=cardinal(DataLen) div cardinal(fColumnCount)) then
break;
fDataRowReaderOrigin := Data;
fDataRowReader := Data;
fDataRowNullSize := ((fColumnCount-1) shr 3)+1;
exit;
until false;
fDataRowCount := 0;
fColumnCount := 0;
raise ESQLDBException.CreateUTF8('Invalid %.IntHeaderProcess',[self]);
end;
procedure TSQLDBProxyStatementAbstract.IntFillDataCurrent(var Reader: PByte);
var F,Len: Integer;
begin // format match TSQLDBStatement.FetchAllToBinary()
if fDataCurrentRowNullLen>0 then
FillChar(fDataCurrentRowNull,fDataCurrentRowNullLen,0);
fDataCurrentRowNullLen := FromVarUInt32(Reader);
if fDataCurrentRowNullLen>fDataRowNullSize then
raise ESQLDBException.CreateUTF8('Invalid %.IntFillDataCurrent',[self]);
if fDataCurrentRowNullLen>0 then begin
Move(Reader^,fDataCurrentRowNull,fDataCurrentRowNullLen);
inc(Reader,fDataCurrentRowNullLen);
end;
fDataCurrentRowValuesStart := Reader;
for F := 0 to fColumnCount-1 do
if F in fDataCurrentRowNull then
fDataCurrentRowValues[F] := nil else begin
fDataCurrentRowColTypes[F] := fColumns[F].ColumnType;
if fDataCurrentRowColTypes[F]<ftInt64 then begin
fDataCurrentRowColTypes[F] := TSQLDBFieldType(Reader^);
inc(Reader);
end;
fDataCurrentRowValues[F] := Reader;
case fDataCurrentRowColTypes[F] of
ftInt64:
Reader := GotoNextVarInt(Reader);
ftDouble, ftCurrency, ftDate:
inc(Reader,SizeOf(Int64));
ftUTF8, ftBlob: begin
Len := FromVarUInt32(Reader);
if Len>fColumns[F].ColumnDataSize then
fColumns[F].ColumnDataSize := Len;
inc(Reader,Len); // jump string/blob content
end;
else raise ESQLDBException.CreateUTF8('%.IntStep: Invalid ColumnType(%)=%',
[self,fColumns[F].ColumnName,ord(fDataCurrentRowColTypes[F])]);
end;
end;
fDataCurrentRowValuesSize := PtrUInt(Reader)-PtrUInt(fDataCurrentRowValuesStart);
end;
procedure TSQLDBProxyStatementAbstract.ColumnsToJSON(WR: TJSONWriter);
var col, DataLen: integer;
................................................................................
Data: PByte;
begin
if WR.Expand then
WR.Add('{');
for col := 0 to fColumnCount-1 do begin
if WR.Expand then
WR.AddFieldName(fColumns[col].ColumnName); // add '"ColumnName":'
Data := fDataCurrentRowValues[col];
if Data=nil then
WR.AddShort('null') else
case fDataCurrentRowColTypes[col] of
ftInt64:
WR.Add(FromVarInt64Value(Data));
ftDouble:
WR.Add(PDouble(Data)^);
ftCurrency:
WR.AddCurr64(PInt64(Data)^);
ftDate: begin
................................................................................
ftUTF8: begin
WR.Add('"');
DataLen := FromVarUInt32(Data);
WR.AddJSONEscape(Data,DataLen);
WR.Add('"');
end;
ftBlob:
if fForceBlobAsNull then
WR.AddShort('null') else begin
// WrBase64(..,withMagic=true)
DataLen := FromVarUInt32(Data);
WR.WrBase64(PAnsiChar(Data),DataLen,true);
end;
end;
WR.Add(',');
end;
WR.CancelLastComma; // cancel last ','
if WR.Expand then
WR.Add('}');
end;
................................................................................
begin
if (fDataRowCount>0) and (cardinal(Col)<cardinal(fColumnCount)) then
if Col in fDataCurrentRowNull then
result := ftNull else
with fColumns[Col] do begin
if FieldSize<>nil then
FieldSize^ := ColumnDataSize; // true max size as computed at loading
result := fDataCurrentRowColTypes[Col]; // per-row column type (SQLite3)
end else
raise ESQLDBException.CreateUTF8('Invalid %.ColumnType()',[self]);
end;
function TSQLDBProxyStatementAbstract.IntColumnType(Col: integer; out Data: PByte): TSQLDBFieldType;
begin
if (cardinal(Col)>=cardinal(fColumnCount)) or (fDataCurrentRowValues=nil) then
result := ftUnknown else begin
Data := fDataCurrentRowValues[Col];
if Data=nil then
result := ftNull else
result := fDataCurrentRowColTypes[Col]; // per-row column type (SQLite3)
end;
end;
function TSQLDBProxyStatementAbstract.ColumnCurrency(Col: integer): currency;
var Data: PByte;
begin
case IntColumnType(Col,Data) of
|
Changes to SynopseCommit.inc.
1 |
'1.18.553'
|
| |
1 |
'1.18.554'
|