Hello, source-owners [?]

I''ve made a change in the *CreateTable* method of the *TCustomSqliteDataset *class to include the *AutoInc *field type working ok. The current implementation don't of the type ftAutoInc doesn't work.

This is the source on the file *CustomSQLiteDS.pas* *
*
*Could this be updated in the sources?*
*
*
*Thanks.
*

function TCustomSqliteDataset.CreateTable(const ATableName: String):
Boolean;
var
 SQLTemp: String;
 i, StrSize: Integer;
begin
 {$ifdef DEBUG_SQLITEDS}
 WriteLn('##TCustomSqliteDataset.CreateTable##');
 if ATableName = '' then
   WriteLn('  TableName Not Set');
 if FieldDefs.Count = 0 then
   WriteLn('  FieldDefs Not Initialized');
 {$endif}
 if (ATableName  '') and (FieldDefs.Count > 0) then
 begin
   SQLTemp := 'CREATE TABLE ' + ATableName + ' (';
   for i := 0 to FieldDefs.Count - 1 do
   begin
     //todo: add index to autoinc field
     SQLTemp := SQLTemp + FieldDefs[i].Name;
     case FieldDefs[i].DataType of
       ftInteger:
         SQLTemp := SQLTemp + ' INTEGER';
       ftString:
       begin
         StrSize := FieldDefs[i].Size;
         if StrSize = 0 then
           StrSize := DefaultStringSize;
         SQLTemp := SQLTemp + ' VARCHAR(' + IntToStr(StrSize) + ')';
       end;
       ftBoolean:
         SQLTemp := SQLTemp + ' BOOL_INT';
       ftFloat:
         SQLTemp := SQLTemp + ' FLOAT';
       ftWord:
         SQLTemp := SQLTemp + ' WORD';
       ftDateTime:
         SQLTemp := SQLTemp + ' DATETIME';
       ftDate:
         SQLTemp := SQLTemp + ' DATE';
       ftTime:
         SQLTemp := SQLTemp + ' TIME';
       ftLargeInt:
         SQLTemp := SQLTemp + ' LARGEINT';
       ftCurrency:
         SQLTemp := SQLTemp + ' CURRENCY';
       ftAutoInc:
         SQLTemp := SQLTemp + ' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT';
       ftMemo:
         SQLTemp := SQLTemp + ' TEXT';
     else
       DatabaseError('Field type "' + FieldTypeNames[FieldDefs[i].DataType]
+
         '" not supported', Self);
     end;
     if (UpperCase(FieldDefs[i].Name) = UpperCase(FPrimaryKey)) and
        (FieldDefs[i].DataTypeftAutoInc) then
       SQLTemp := SQLTemp + ' PRIMARY KEY';
     if i  FieldDefs.Count - 1 then
       SQLTemp := SQLTemp + ' , ';
   end;
   SQLTemp := SQLTemp + ');';
   {$ifdef DEBUG_SQLITEDS}
   WriteLn('  SQL: ',SqlTemp);
   {$endif}
   ExecSQL(SQLTemp);
   Result := FReturnCode = SQLITE_OK;
 end
 else
   Result := False;
end;

Hi, ftAutoInc field is a type that is incremented at TSqliteDataset side so there's no need to set in sqlite field name.
But why not use the field type you propose (INTEGER AUTOINC)?

1. It will not be possible to determine that's an ftAutoInc field when dataset is loaded because it's recognized as INTEGER
2. It's already possible to get the behavior you want:
- Set PrimaryKey to your integer field
- (Optional) If you want that this field is autoincremented in TSqliteDataset side set AutoIncrementKey to true
- The table will be created with a INTEGER PRIMARY KEY field that by definition is an autoincrement field. See the sqlite.org FAQ

Luiz

Well

This code:

//  sqlite is a TSQLite3DataSet
  sqlite.FieldDefs.Clear;
  sqlite.FieldDefs.Add('id',ftAutoInc);
  sqlite.FieldDefs.Add('name',ftString);
  sqlite.FileName:='test.sq3';
  sqlite.TableName:='test';
  sqlite.CreateTable;

produces a table with this code

CREATE TABLE test (id AUTOINC_INT , name VARCHAR(255))

When I browse the code, using an external application like SQLiteAdministrator the ID field isn't "auto_inced" when I append a new line to the table and commit.

But, checking the DDL that the SQLiteAdministrator and SQLiteStudio produces when I define an autoinc field, they result in:
CREATE TABLE test (id INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, name VARCHAR(255))

And this works perfectly.

2010/3/17 Luiz Americo Pereira Camara

Try
  sqlite.PrimaryKey := 'id';
  sqlite.FieldDefs.Clear;
  sqlite.FieldDefs.Add('id',ftInteger);
  sqlite.FieldDefs.Add('name',ftString);
  sqlite.FileName:='test.sq3';
  sqlite.TableName:='test';
  sqlite.CreateTable;

Luiz