|
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
|
|
|
|
|
Hi Paul
As I recall, the problem with the GUID data type is that the .net provider treats it as a binary field and not as a text field. That was the reason why I preferred to convert GUID columns to varchar columns.
If I remember correctly - I never used "guid" for GUID types, so you shouldn't be surprised to see the result ..
Maybe I should give the user the option to select which type to use.
I'll incorporate your other suggestions in the next version.
Thanks for your input
Liron Levi
|
|
|
|
|
The Guid type appears to be fully supported in the 1.065 version of System.Data.SQLite. They show up in the designer correctly formatted as guids, and work normally. I have to use them for my project.
|
|
|
|
|
I've updated the software to include GUID support. Enjoy
|
|
|
|
|
It might be a little late, but just one more message saying excellent work. This is definitely one of the best (working) pieces I've found.
|
|
|
|
|
Thanks
|
|
|
|
|
I have attached an .mdf file to SQLExpress but I don't see it in the dropdownlistof your application. Why is this?
Checked with the command line utility SSEUtil.exe !LIST and the db file is attached.
Nestor LEONE, buenos aires
|
|
|
|
|
I have no idea. IF you can send the MDF file to me then maybe I can check this for you (send to liron.levi@gmail.com)
Best Regards
Liron Levi
|
|
|
|
|
Liron,
Thanks for the wonderful program. I am having the same issue as Leonen, however. The attached MDF is a SQL Server Express 2008 file but when I connect to ".\SQLEXPRESS" it does not show up in the list of tables. Did you ever find a fix for this?
Thanks,
Joseph Leedy, President/Lead Developer
Leedsoft Solutions
http://www.leedsoft.net
|
|
|
|
|
Hi Joseph
I've tested this via the Studio Manager and it worked fine. Maybe there is a permission problem with the attached DB file. Check if you can see the attached database with the Studio Manager software (from Microsoft).
Basically I'm doing a simple "SELECT DISTINCT [name] FROM sysdatabases" to get the list of databases to show.
From what I know this should be enough, but maybe I'm wrong .. If you have better idea - please tell me.
Best Regards
Liron
|
|
|
|