SQLite 到 Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5190660/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite to Oracle
提问by DUrs
I have a SQLite database in one system, I need to extract the data stored in SQLite to Oracle database. How do I do this?
我在一个系统中有一个 SQLite 数据库，我需要将存储在 SQLite 中的数据提取到 Oracle 数据库中。我该怎么做呢？
回答by dsegleau
Oracle provides product called the Oracle Database Mobile Server(previously called Oracle Database Lite) which allows you to synchronize between a SQLite and an Oracle database. It provides scalable bi-directional sync, schema mapping, security, etc. The Mobile Server supports both synchronous and asynchronous data sync. If this is more than a one-time export and you need to keep your SQLite and Oracle Databases in sync, this is a great tool!
Oracle 提供名为Oracle Database Mobile Server（以前称为 Oracle Database Lite）的产品，它允许您在 SQLite 和 Oracle 数据库之间进行同步。它提供可扩展的双向同步、模式映射、安全性等。移动服务器支持同步和异步数据同步。如果这不仅仅是一次导出，并且您需要保持 SQLite 和 Oracle 数据库同步，这是一个很棒的工具！
Disclaimer: I'm one of the Product Managers for Oracle Database Mobile Server, so I'm a bit biased. However, the Mobile Server really is a great tool to use for keeping your SQLite (or Berkeley DB) and Oracle Databases in sync.
免责声明：我是 Oracle 数据库移动服务器的产品经理之一，所以我有点偏见。但是，Mobile Server 确实是一个很好的工具，可用于使您的 SQLite（或 Berkeley DB）和 Oracle 数据库保持同步。
回答by vapcguy
If you are a developer, you could develop an application to perform the sync. You would do
如果您是开发人员，则可以开发一个应用程序来执行同步。你会做
SELECT name FROM sqlite_master WHERE type='table'
SELECT name FROM sqlite_master WHERE type='table'
to get the table names, then you could re-create them in Oracle (you can do DROP TABLE tablenamein Oracle first, to avoid a conflict, assuming SQLite will be authoritative) with CREATE TABLEcommands. Getting the columns for each one takes
要获取表名，然后您可以DROP TABLE tablename使用CREATE TABLE命令在 Oracle 中重新创建它们（您可以先在 Oracle 中进行，以避免冲突，假设 SQLite 将具有权威性）。获取每一列需要
SELECT sql FROM sqlite_master WHERE type='table' and name='MyTable'
SELECT sql FROM sqlite_master WHERE type='table' and name='MyTable'
And then you have to parse the result:
然后你必须解析结果：
string columnNames = sql.replace(/^[^\(]+\(([^\)]+)\)/g, '').replace(/ [^,]+/g, '').split(',');
string[] columnArray = columnNames.Split(',');
foreach (string s in columnArray)
{
// Add column to table using:
// ALTER TABLE MyTable ADD COLUMN s NVARCHAR(250)
}
A StringBuildercan be used to collect the table name with its columns to create your INSERTcommand. To add the values, it would just be a matter of doing SELECT * FROM MyTablefor each of the tables during your loop through the table names you got back from the initial query. You would iterate the columns of the rows of the datatable you were returned and add the values to the StringBuilder:
AStringBuilder可用于收集表名及其列以创建您的INSERT命令。要添加值，只需SELECT * FROM MyTable在循环遍历从初始查询返回的表名期间对每个表执行操作即可。您将迭代返回的数据表的行的列，并将值添加到StringBuilder：
INSERT INTO MyTable (+ columnA, columnB, etc. + ) VALUES (datarow[0], datarow[1], etc. + ).
INSERT INTO MyTable (+ columnA、columnB 等 + datarow ) VALUES ([0]、datarow[1] 等 + ).
Not exactly like that, though - you fill in the data by appending the column name and its data as you run through the loops. You can get the column names by appending sin that foreachloop, above. Each column value is then set using a foreachloop that gives you each object obj in drData.ItemArray. If all you have are string fields, it's easy, you just add obj.ToString()to your StringBuilderfor each column value in your query like I have below. Then you run the query after collecting all of the column values for each row. You use a new StringBuilderfor each row - it needs to get reset to INSERT INTO MyTable (+ columnA, columnB, etc. + ) VALUES (prior to each new row, so the new column values can be appended.
但是，并非完全如此 - 您可以在运行循环时通过附加列名及其数据来填充数据。您可以通过s在foreach上面的循环中附加来获取列名。然后使用foreach循环设置每个列值，该循环为您提供每个object obj in drData.ItemArray. 如果您只有字符串字段，这很容易，您只需将查询中的每个列值添加obj.ToString()到您StringBuilder的查询中，如下所示。然后在收集每一行的所有列值后运行查询。您StringBuilder为每一行使用一个新的 - 它需要在每个新行之前重置为INSERT INTO MyTable (+ columnA、columnB 等 + ) VALUES (，以便可以附加新的列值。
If you have mixed datatypes (i.e. DATE, BLOB, etc.), you'll need to determine the column types along the way, store it in a list or array, then use a counter to determine the index of that list/array slot and get the type, so you know how to translate your object into something Oracle can use - whether that means simply adding to_date()to the result, with formatting, for a date (since SQLite stores these as date strings with the format yyyy-MM-dd HH:mm:ss), or adding it to an OracleParameterfor a BLOBand sending that along to a RunOracleCommandfunction. (I did not go into this, below.)
如果您有混合数据类型（即DATE，BLOB等），您需要确定列类型，将其存储在列表或数组中，然后使用计数器确定该列表/数组插槽的索引并获取类型，所以你知道如何将你的对象转换成 Oracle 可以使用的东西——这是否意味着简单地添加to_date()到结果、格式化、日期（因为 SQLite 将这些存储为带有格式的日期字符串yyyy-MM-dd HH:mm:ss），或者将它添加到OracleParameterfor aBLOB并将其发送到一个RunOracleCommand函数。（我没有进入这个，下面。）
Putting all of this together yields this:
将所有这些放在一起会产生以下结果：
string[] columnArray = null;
DataTable dtTableNames = GetSQLiteTable("SELECT name FROM sqlite_master WHERE type='table'");
if (dtTableNames != null && dtTableNames.Rows != null)
{
if (dtTableNames.Rows.Count > 0)
{
// We have tables
foreach (DataRow dr in dtTableNames.Rows)
{
// Do everything about this table here
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO " + tableName + " ("); // we will collect column names here
string tableName = dr["NAME"] != null ? dr["NAME"].ToString() : String.Empty;
if (!String.IsNullOrEmpty(tableName))
{
RunOracleCommand("DROP TABLE " + tableName);
RunOracleCommand("CREATE TABLE " + tableName);
}
DataTable dtColumnNames = GetSQLiteTable("SELECT sql FROM sqlite_master WHERE type='table' AND name='"+tableName+"'");
if (dtColumnNames != null && dtColumnNames.Rows != null)
{
if (dtColumnNames.Rows.Count > 0)
{
// We have columns
foreach (DataRow drCol in dtTableNames.Rows)
{
string sql = drCol["SQL"] != null ? drCol["SQL"].ToString() : String.Empty;
if (!String.IsNullOrEmpty(sql))
{
string columnNames = sql.replace(/^[^\(]+\(([^\)]+)\)/g, '').replace(/ [^,]+/g, '').split(',');
columnArray = columnNames.Split(',');
foreach (string s in columnArray)
{
// Add column to table using:
RunOracleCommand("ALTER TABLE " + tableName + " ADD COLUMN " + s + " NVARCHAR(250)"); // can hard-code like this or use logic to determine the datatype/column width
sb.Append("'" + s + "',");
}
sb.TrimEnd(",");
sb.Append(") VALUES (");
}
}
}
}
// Get SQLite Table data for insertion to Oracle
DataTable dtTableData = GetSQLiteTable("SELECT * FROM " + tableName);
if (dtTableData != null && dtTableData.Rows != null)
{
if (dtTableData.Rows.Count > 0)
{
// We have data
foreach (DataRow drData in dtTableData.Rows)
{
StringBuilder sbRow = sb; // resets to baseline for each row
foreach (object obj in drData.ItemArray)
{
// This is simplistic and assumes you have string data for an NVARCHAR field
sbRow.Append("'" + obj.ToString() + "',");
}
sbRow.TrimEnd(",");
sbRow.Append(")");
RunOracleCommand(sbRow.ToString());
}
}
}
}
}
}
All of this assumes you have a RunOracleCommand()void function that can take a SQL command and run it against an Oracle DB, and a GetSQLiteTable()function that can return a DataTable from your SQLite DB by passing it a SQL command.
所有这些都假设您有一个RunOracleCommand()void 函数，它可以接受 SQL 命令并针对 Oracle DB 运行它，还有一个GetSQLiteTable()函数可以通过向 SQLite DB 传递 SQL 命令来从它返回 DataTable。
Note that this code is untested, as I wrote it directly in this post, but it is based heavily on code I wrote to sync Oracle into SQLite, which has been tested and works.
请注意，此代码未经测试，因为我在这篇文章中直接编写了它，但它主要基于我编写的将 Oracle 同步到 SQLite 的代码，该代码已经过测试且有效。
回答by Rafe Kettler
You'll have to convert the SQLite to a text file (not certain of the format) and then use Oracle to load the database from text (source is http://www.orafaq.com/wiki/SQLite). You can use the .dumpcommand from the SQLite interactive shell to dump to a text file (see the docsfor syntax).
您必须将 SQLite 转换为文本文件（不确定格式），然后使用 Oracle 从文本加载数据库（来源是http://www.orafaq.com/wiki/SQLite）。您可以使用.dumpSQLite 交互式 shell 中的命令转储到文本文件（有关语法，请参阅文档）。
回答by Michael Ballent
SQL Loader is a utility that will read a delimited text file and import it into an oracle database. You will need to map out how each column from your flat file out of sqlite matches to the corresponding one in the Oracle database. Here is a good FAQthat should help you get started.
SQL Loader 是一个实用程序，它将读取一个带分隔符的文本文件并将其导入到 oracle 数据库中。您需要从 sqlite 的平面文件中找出每一列如何与 Oracle 数据库中的相应列匹配。这是一个很好的常见问题解答，可以帮助您入门。
