Kexi/Migration

From KDE Community Wiki

Add support for appending CSV data to an existing table

Rationale

Appending CSV data to an existing table gives a simple means for merging CSV data chunks in one step.

Assumptions & Requirements

  • The data should be appended to the existing table, without changes made to the
  • Data is in largely the same format (regarding column or data types) as the data used to create the original table, to achieve this:
    • Data types for columns should be taken from the schema of the destination table
    • Column names stay untouched in the destination table, however autodetection should be still used to figure out value of the "First row contains column names" option; the option can be changed in the
  • Conflicts resolutions:
    • Data type violations (for example when importing string when numeric value is expected): TODO - for 2.6 we need to have friendly error message at least
      • Future: propose skipping conflicting values
    • Primary key violations: TODO - for 2.6 we need to have friendly error message at least
      • Future: propose skipping records with conflicting values or replacing the old records with new ones
    • Future: unique key violations: TODO

GUI Design

Existing GUI is based on single dialog as follows:

Switching to KAssistantDialog is needed. Proposed pages: 1. File selection (implementation already exists but uses separate modal dialog)

2. Data preview and options (implementation already exists but uses separate modal dialog)

3. Destination Selection page:

  • [KexiCommandLinkButton 3.1]: Import data into a new table
  • [KexiCommandLinkButton 3.2]: Append data to existing table

4.1. (when 3.1 clicked) Save Object As (implementation already exists but uses separate modal dialog)

4.2. (when 3.2 clicked) Select destination table

  • use KexiFieldListView for the list of tables
  • double click on name should do the same as clicking Next button

5. Importing

  • display progress bar
  • while importing disable all buttons but cancel
  • hide the progress when done with importing and display "Data has been successfully imported to table "%1" (implementation already exists but uses separate modal dialog)
  • available buttons should be: "Open..." and "Close"; "Cancel" should not be displayed after importing (implementation already exists but uses separate modal dialog)

Porting to non-modal assistants

  • Status: TODO

What is involved in porting to the new gui? <jstaniek>: We sit and design the given assistant and I am helping with using the API and possibly extending/fixing it. The difference is that many imports can be started concurrently so some extra checking may be needed but this is a side note. Also e.g. user could try to edit/remove a source table during export. So this is fun. But after the change the GUI would be a lot more natural I think.

Migration from file-based project to a MySQL database project

This is qlite3 to mysql conversion using python script sqlite3-to-mysql.py, see also this thread.

Steps for importing mydb project from file into a MySQL db server:

1. Execute:

% sqlite3 mydb.kexi .dump > mydb.sql

2. Download sqlite3-to-mysql.py and run:

% ./sqlite3-to-mysql.py mydb.sql > mydb.mysql

3. Create empty MySQL database

% mysqladmin -p -u ..... create mydb

4. Finally, import

% mysql -p -u ..... mydb < mydb.mysql

Notes:

  • This tool can fail if large photos where stored in the database.
  • This tool is pretty slow.
  • Final solution will be just one click away in Kexi.