| -!!Data Migration And Sharing |
| +!!!Data Migration And Sharing |
| |
| @@@ |
| |
| -!1. [General Hints|DataMigrationAndSharing_GeneralHints] |
| -Status: |
| -;:It's currently possible to migrate a MySQL or PostgreSQL database to a SQLite database, using either the keximigratetest program, or, with the right configure options, File->Import->Project. |
| +!!1. Introduction |
| +There are two kinds of data migration: |
| +* Migration for entire projects. |
| +* Data migration related to a single data table (importing, exporting). |
| |
| -!2. Migration API |
| -See the [Migration API|http://www.kexi-project.org/docs/cvs-api/html/namespaceKexiMigration.html] |
| +Read [general hints|DataMigrationAndSharing_GeneralHints]. |
| |
| -!3. Data Migration Tools |
| -** Importing |
| -** Exporting |
| -(TODO) |
| +!!2. Project Migration |
| +Project migration allows to migrate the database schema and table data in one go. It's currently possible to migrate a MySQL, PostgreSQL or MS Access database to a SQLite database, using either the keximigratetest program or using "Tools->Migration->Import database" menu command. |
| |
| -!4. Data Sharing |
| -;: By __Data Sharing__ we usually mean dynamic data exchanging features that do not require to importing and exporting. For example: connecting with external (not KexiDB-derived) database to share some data between Kexi project and that external data source. |
| +See also: |
| +* [Migration API|http://www.kexi-project.org/docs/cvs-api/html/namespaceKexiMigration.html] |
| |
| -!5. Cross-DB-Engine Queries |
| +!2.1. Reusing SQL/XML |
| +See [Using SQLXML|KexiDataMigrationAndSharing_UsingSQLXML]. |
| + |
| +!!3. Data Migration |
| +Text tabular data can be imported from file (File->Import), exported to file (File->Export) or pasted to the clipboard (Edit->Paste Special) or copied to the clipboard (Edit->Copy Special). |
| + |
| +!3.1. Importing |
| + |
| +! 3.1.1. Text formats for tabular data |
| +There are a number of text formats for tabular data. See [MS Excel Import formats|http://www.icehouse.net/jim_d/excel3.html#Formats] for naming scheme. |
| +* __[Comma Separated Values (CSV) Data|DataMigrationTools_Importing_CSV]__ |
| +* __[Fixed Width Text|DataMigrationTools_Importing_FixedWidthText]__ |
| + |
| +See also http://en.wikipedia.org/wiki/Flat_file_database |
| +!3.2. Exporting |
| + |
| +! 3.2.1. Text formats for tabular data |
| + |
| +* __[Comma Separated Values (CSV) Data|DataMigrationTools_Exporting_CSV]__ |
| +* __[Fixed Width Text|DataMigrationTools_Exporting_FixedWidthText]__ |
| + |
| +!!4. Data Sharing |
| +By __Data Sharing__ we usually mean dynamic data exchanging features that do not require to importing and exporting. For example: connecting with external (not KexiDB-derived) database to share some data between Kexi project and that external data source. |
| + |
| +!!5. Cross-DB-Engine Queries |
| |
| jstaniek> mart: btw, another hard way is to add simple querying |
| - engine on top of kexidb (not so hard since we've got schemas parsed out |
| - of query statements) and retrieve simple one-table records from mdbtools and |
| - then combine them using our engine. Advantage: we can later even provide a way |
| - for making relations for loosely connected data sources, as eg. , say, google |
| - result set and mysql dictionary |
| + engine on top of kexidb (not so hard since we've got schemas parsed |
| + out of query statements) and retrieve simple one-table records from |
| + mdbtools and then combine them using our engine. Advantage: |
| + we can later even provide a way for making relations for loosely |
| + connected data sources, as eg. , say, google result set and mysql |
| + dictionary |
| |
| - mart> jstaniek: I have had some ideas about 'partial evaluation' of SQL before |
| - partial evaluation - so I could write SELECT int1, int2, str1,str2 FROM |
| - t WHERE int1<int2 and string_edit_distance(str1, str2) < 5, so the DB goes |
| - away and finds records where int1<int2 then kexi would execute some |
| - plugin-loaded function to filter the rest etc... |
| + mart> jstaniek: I have had some ideas about 'partial evaluation' |
| + of SQL before partial evaluation - so I could write SELECT int1, int2, |
| + str1,str2 FROM t WHERE int1<int2 and string_edit_distance(str1, |
| + str2) < 5, so the DB goes away and finds records where int1<int2 |
| + then kexi would execute some plugin-loaded function to filter |
| + the rest etc... |
| |
| - jstaniek> btw, it is great tool for data integration... only not so robust... |
| - but who cares... that's ALMOST ready! |
| + jstaniek> btw, it is great tool for data integration... only not |
| + so robust... but who cares... that's ALMOST ready! |
| |
| mart> jstaniek: it is? |
| |
| jstaniek> mart: we've got a lists of builtin (provided at the backend) |
| functions, so you iterate through |
| - PARSE tree of the query and ... split the query to "backed" and "frontend" |
| - parts... |
| + PARSE tree of the query and ... split the query to "backed" and |
| + "frontend" parts... |
| |
| (yes, we know these things could be nicer on server level, not client...) |
| |
| mart, jan 2005 |
| |
| Speaking of which, it might not be a bad idea to store these functions in Java JAR files. JNI or CNI calls could be made to these functions using GCC's Java support (if available). The JAR could also be deployed on a PosgreSQL server with [pljava|http://gborg.postgresql.org/project/pljava/projdisplay.php], allowing the functions to be executed on the server for greater performance. |
| + |
| +!5.1. By the way: a word from MSA team |
| +[From 'Access 12's new data engine' article|http://blogs.msdn.com/access/archive/2005/10/13/480870.aspx]: |
| + |
| +[[..] ''Jet is unique in Microsoft because it provides both a data storage mechanism with a heterogeneous query processor. This ability to run queries across a number of different data stores is a key reason people use Access, so it was super important for us to retain that ability going forward. The SQL team is de-investing in Jet, and telling developers to code against SQL Server Express, but as it lacks the heterogeneous query functionality we needed, Access stuck with Jet. '' [[..] |
| + |
| +!6. Links |
| +* [VistaDB: Migration Wizard Screnshots|http://www.vistadb.net/screenshots_dmw.asp] - usable to learn what options are used when migrating real-world projects. |
