Topic: Update

Hi all,
in case I want to update my software in a new version which has also new forms and  new records in the database (with also some relationships between the tables and calculatefields) is it possible to do it? I have asked it in the past but couldn't get it through. I really want to move all the information from the database to the new (updated) but the calculated fields and the new relationships makes it very difficult . is there an example for a little help ? Thakn you

2 (edited by sparrow 2023-05-13 17:01:45)

Re: Update

Hi


Everything is mixed up in your question: program, forms, calculated fields and database.
From all this, we can conclude that you need to transfer information from the old database to the new one.

Your question is too general.
What you need for this you have already written.
There are no generic examples. Everything is only by hand. Only you know about your bases.

as one of the options. IMPORTANT:Make a copy of the old database.
Take a copy and try to bring it to a new state in the SQLite manager by adding new tables, relationships, data to the old one by creating or copying from a new database. It certainly sounds simple.

3 (edited by reteinformatica 2023-05-13 19:36:24)

Re: Update

Hi v_pozidis and sparrow,
I find this discussion very interesting
I wonder how one could go about creating an automatic update.
Let's say someone updates a project and a table with related fields has been added to the new project:

INITIAL SITUATION

Table name = A
Table field name A = 1

CHANGES

Added table with name = B
Added field to table B with name = 2

Theoretically it could be:

1) in database 'name' create table 'B'

2) in table 'name' create the field '2', text, mandatory etc.

The only thing is that I really don't know how to develop it programmatically.

Re: Update

Dear sparrow yes i want to transfer the information from the one databsse to a new one which contains more records. My question is how to transfere them so everybody who use my software can continue with the new version of my software .  How can we tramsfer the related and especially the cslculated fields??

Re: Update

reteinformatica wrote:

Hi v_pozidis and sparrow,
I find this discussion very interesting
I wonder how one could go about creating an automatic update.
Let's say someone updates a project and a table with related fields has been added to the new project:

INITIAL SITUATION

Table name = A
Table field name A = 1

CHANGES

Added table with name = B
Added field to table B with name = 2

Theoretically it could be:

1) in database 'name' create table 'B'

2) in table 'name' create the field '2', text, mandatory etc.

The only thing is that I really don't know how to develop it programmatically.


const
  DB_TEXT_FIELD = 1;

procedure DB_AddField(ATableName: string; AFieldName:string; AType:integer );
// добавление поля
var
  tmpSQL:string;
begin
  tmpSQL := 'ALTER TABLE ['+ATableName+'] ADD COLUMN ['+AFieldName+'] ';
  case dbType of
  DBT_UNKNOW: RaiseException('DB_AddField - не поддерживается для типа базы DBT_UNKNOW');
  DBT_SQLITE: begin
  case AType of
  DB_TEXT_FIELD: tmpSQL := tmpSQL + ' TEXT';
  else RaiseException('DB_AddField - не поддерживается тип поля: '+IntToStr(AType));
  end;
  end;
  DBT_MYSQL: RaiseException('DB_AddField - не поддерживается для типа базы DBT_MYSQL');
  end;
  SQLExecute(tmpSQL);
end;

function DB_FieldExists( ATableName: string; AFieldName:string ):boolean;
// проверяет существование таблицы
var
  tmpDataSet:TDataSet;
begin
  case dbType of
  DBT_UNKNOW: RaiseException('DB_TableExists - не поддерживается для типа базы DBT_UNKNOW');
  DBT_SQLITE: begin
    // Result := SQLExecute('SELECT COUNT(*) AS CNTREC FROM ( pragma_table_info('+ATableName+') WHERE name="'+AFieldName+'" )' ) = 1; // SQLite - не работает в MVDB, но работает в SQLiteStudio
    Result := False;
    SQLQuery('PRAGMA table_info(['+ATableName+']) ',tmpDataSet);
    while not tmpDataSet.EOF do
    begin
      if UpperCase( tmpDataSet.FieldByName('name').asString ) = UpperCase( AFieldName ) then
      begin
        Result := True;
        break;
      end;
      tmpDataSet.Next;
    end;
    tmpDataSet.Free;
  end;
  DBT_MYSQL: RaiseException('DB_TableExists - не поддерживается для типа базы DBT_MYSQL');
  end;
end;

function DB_TableExists( ATableName: string ):boolean;
// проверяет существование таблицы
begin
  case dbType of
  DBT_UNKNOW: RaiseException('DB_TableExists - не поддерживается для типа базы DBT_UNKNOW');
  DBT_SQLITE: Result := SQLExecute('SELECT COUNT(*) FROM sqlite_master WHERE type="table" AND tbl_Name="'+ATableName+'"') = 1; // SQLite
  DBT_MYSQL: RaiseException('DB_TableExists - не поддерживается для типа базы DBT_MYSQL');
  end;
end;

Exaples:

  if not DB_TableExists('blank') then
  begin
    tmpSQL := //
      '  CREATE TABLE blank ( '+CR+//
      '    id     INTEGER PRIMARY KEY ASC AUTOINCREMENT, '+CR+//
      '    length INTEGER NOT NULL '+CR+//
      '                   DEFAULT 0, '+CR+//
      '    qty    INTEGER NOT NULL '+CR+//
      '                   DEFAULT 0, '+CR+//
      '    basic  INTEGER NOT NULL '+CR+//
      '                   DEFAULT 0 '+CR+//
      '); ';
    SQLExecute(tmpSQL);
  end;

  if not DB_FieldExists( 'order', 'comment' ) then
    DB_AddField('order','comment',DB_TEXT_FIELD);
Визуальное программирование: блог и библиотека.

6 (edited by sparrow 2023-05-15 15:24:14)

Re: Update

v_pozidis wrote:

Dear sparrow yes i want to transfer the information from the one databsse to a new one which contains more records. My question is how to transfere them so everybody who use my software can continue with the new version of my software .  How can we tramsfer the related and especially the cslculated fields??


Let's sort the question.
New forms, objects on forms, reports, scripts, calculated fields and database structure - all this belongs to the program part. To update them, you need to rewrite everything in the program folder and subdirectories, with the exception of *.exe and sqlite.dll (if you did not change the version).


New tables, new columns, triggers, views, queries - database(sqlite.db). Unless some of the above are created in the script. If the base has not changed, you can not rewrite.


How to try to automate the update has already been described more than once by Konstantin, Vladimir and other forum users.

Re: Update

Hi everyone,
Thanks to Kostantin for code, I'll study it and try to apply it because it's very interesting even if I don't need it for now.

Re: Update

Thanks to all