|
Thanks
If you need to compare and merge sqlite databases you can download and use another tool that I wrote called SQLite Compare (it's free now) from http://www.sqlitecompare.com
Best Regards
Liron
|
|
|
|
|
I think voting is not enough.
This is the best tool i have encountered in codeproject.
thank you very much.
you saved me many days before reinventing the jet.
|
|
|
|
|
Thanks for your comment.
It is nice to know that something I wrote as a weekend project turned out to be helpful for so many people
Liron
|
|
|
|
|
Is it possible to call it from command line?
|
|
|
|
|
Hi
Currently - the utility does not support command line invocation. however - the conversion code belongs to a single class in the code and this class has no dependencies on the GUI code, so it should be very easy to adapt it to do this via the command line.
Best Regards
Liron
|
|
|
|
|
Thank you for the reply, I've done it.
By the way I'm trying to remove the window when you have to select wich tables inside the database you want to export... do you have any tips on how could I remove that window and directly export all the tables in the database selected?
|
|
|
|
|
I've commented the "Invoke(new MethodInvoker(delegate{ ... }));"
inside the selectionHandler and now all tables inside a database are exported without using the selection window.
|
|
|
|
|
I have a problem:
I've implemented the feature to call converter.exe also from command line (passing db_host, db_name, db_user and db_password).
I've tested directly from command line (inserting it in a batch) and it works.
But I have another personal application .exe that opens a cmd and execute the same batch tested before:
when the converter.exe is called it starts, it connects to the database, it show the tables inside the database selected, but when you click the OK to convert the selected tables it freezes until you don't close the application that has launched the cmd and the batch.
After you've closed the starting application the conversion process completes regularly.
Do you have any suggestion about this behavior?
|
|
|
|
|
I could add that last echoed text before freeze is:
"parsed tables dtproperties"
Then if I don't close the application that has launched the cmd batch that calls the converter.exe it doesn't de-freeze and finish the conversion.
|
|
|
|
|
Hi
I have no idea what the problem is .. maybe there is a db locking issue (sqlite is single writer/multiple readers kind of database) ..
You are a smart guy, you'll figure this out
Best Regards
Liron
|
|
|
|
|
I used your tool to convert a sql express 2005 database to SQLite. It did a fantastic job. I then plugged this database into a VS2008 website using Subsonic as a DAL generator.
One issue that I picked was that during the conversion the tool converts a SQL image to a SQLite blob. When Subsonic generates the code it does not recognize blob as a valid data type and changes the field type to string resulting in a compiler error. Cannot convert byte[] to string. I fixed this by changing the blob data type in the SQLite database to Image. Everything worked fine after that.
Otherwise a sterling job. Much appreciated.
|
|
|
|
|
Thanks. That's why I always prefer to work with open source projects .. It's much easier to modify them a little to get exactly what you want.
Best Regards
Liron
|
|
|
|
|
Hey,
Thanks for this useful software!
I've made some minor modifications to support some SQL Server 2008 types i.e date, datetime2 and geometry (SQL Server automatically converts geometry to string data in WKT format). I can make a patch for this but it's almost no code... however I'm interested in what your plans are for SQL Server 2008 support in general?
|
|
|
|
|
Hi Björn
Currently I'm swamped with other work so I can't really support SQL Server 2008 at the moment.
As for my plans ... I feel that my work here is done so I don't really have any, but I'll be happy to incorporate your patch into a new version if you'll send it to me (liron.levi@gmail.com).
Best Regards
Liron
|
|
|
|
|
I'd be interested in what you did for this support. I'm in the process of implementing this code into my :memory: DB app, and need a workaround for TimeSpan data types...
|
|
|
|
|
Hi John
It's a long time since I last touched this code. I suggest you simply take a look at the code and adjust it for your needs.
It should be relatively easy to do so.
If you have specific questions about the code I'll be happy to provide you with answers.
Best Regards
Liron Levi
|
|
|
|
|
Hi Bjorn,
Could you send me the updated code supporting SQL2008 please? (chezbox at gmail)
|
|
|
|
|
It does't transfer user define function and Store Procedure of MSSQL to SQLITe
|
|
|
|
|
I haven't tried this code yet, and am just new to the SQLite scene, but as far as I know SQLite doesn't support stored procedures, so you can't really transfer them across (nowhere to go)
|
|
|
|
|
I said in the article that this utility knows how to convert tables and indexes. It does not support triggers, stored procedures and has only limited support for views.
Liron
|
|
|
|
|
For sqlite 3.6.19 and higher: There is an error in the converter on cascade operations and foreign key clauses. Each foreign key must reference the primary key of the parent table, otherwise you get a 'foreign key mismatch' error. The create table has to be right when the table is created because you can't change the table later. In the example below, the P_item table's primary key is compound, ItemID and ColumnID, and only the first create table statement is correct. I hacked up a version of the converter to support this, but it's really ugly code, perhaps you can figure out how to do this.
-- no foreign key mismatch:
CREATE TABLE [P_Item_2] (
[ItemID] integer NOT NULL,
[ColumnID] integer NOT NULL,
[ParentID] integer NOT NULL,
PRIMARY KEY ([ItemID], [ColumnID]),
FOREIGN KEY ([ColumnID] ,[ParentID])
REFERENCES [P_item]([ColumnID],[ItemID])
ON DELETE CASCADE ON UPDATE CASCADE)
-- causes foreign key mismatch error:
CREATE TABLE [P_Item_2] (
[ItemID] integer NOT NULL,
[ColumnID] integer NOT NULL,
[ParentID] integer NOT NULL,
PRIMARY KEY ([ItemID], [ColumnID]),
FOREIGN KEY([ColumnID])
REFERENCES [P_item] ([ColumnID])
ON UPDATE CASCADE ON DELETE CASCADE
FOREIGN KEY([ParentID])
REFERENCES [P_item] ([ItemID])
ON UPDATE CASCADE ON DELETE CASCADE)
|
|
|
|
|
Hi, i'm tryng to build a little filtered sqlite db from a big sql server db.
The problem is that your program generate foreign key also for tables that aren't check in the TableSelectionWindow.
I'm working to modify it to genereta the DB without create foreign key for the table not selected but i'm stuck.
If you can help me
|
|
|
|
|
Hi
Please send me a sample sql server database so I can check this myself.
Thanks in advance
Liron
|
|
|
|
|
Since SQLite now supports foreign keys it's time to think about adding support in the converter. System.Data.SQLite isn't using the latest version of SQLite, but when they do you'll be ready. (I have my own hacked up version of System.Data.SQLite that I updated to the new SQLite.) When you build up the foreign key phrase here, you would add ON DELETE CASCADE, etc. or whatever was required. I don't know how to obtain the sql server schema to do set this up automatically but that would be the way to do it.
string stmt = string.Format("FOREIGN KEY ([{0}]) REFERENCES [{1}]([{2}])",
foreignKey.ColumnName, foreignKey.ForeignTableName, foreignKey.ForeignColumnName);
What I have started to do is add a button column to the grid in the table selector dialog which goes to a dialog for the table. You can choose ON DELETE, ON UPDATE, and all the sub options for those, for each foreign key. The settings will be saved in the foreign key object for use later.
|
|
|
|
|
Around v1.8 I gave you the autoincrement suggestion, but there was also a fix for guids in the file I sent you. I think you need this around line 921.
else if (dataType == "uniqueidentifier")
dataType = "guid";
I was surprised when running the latest version yesterday and all my guids were coming out varchar. You said before that guids made indexing slow, but my goal is just to convert the database, not redesign it.
Also recommend updating assemblyinfo.cs version numbers, and use latest version of system.data.sqlite.
Thank you for doing this project!
modified on Thursday, December 3, 2009 4:06 PM
|
|
|
|