marcodelpin / dblinq2007 Goto Github PK

In Sqlite the TINYINT field type maps to System.Byte.
Support for this has been started in Rev 142
Modified : /trunk/DbLinq/util/RowEnumeratorCompiler.cs
Modified : /trunk/DbLinq/vendor/IDataReader2.cs

Please verify that nothing is broken on other databases.

The class helps generating user friendly names from DbLinq.
At DbLinq level, we need to keep .NET types, not C# variants.
SqlMetal will then make some optimizations for code generation (but not
before).

What steps will reproduce the problem?
Running Test_NUnit.Linq_101_Samples.LinqToSqlObjectIdentity01()
sometimes give unpredictable results.
The SELECT string isn't always built in the same order. Sometimes
CustomerID and ContactName has changed places witch results in Failure in
the test because CompanyID now has the value "Bon Boss" and
ContactName="BONAP" 

This doesn't hapen always but usually the first time i run the tests and if
I run again repeatedly it happens almost 1 on 10 runs.


What is the expected output? What do you see instead?
Test_NUnit.Linq_101_Samples.Object_Identity.LinqToSqlObjectIdentity01 :
CustomerID must be BONAP - was: Bon Boss


Please use labels and text to provide additional information.
This is using SQLite don't know about the others.

I have a db with a table called font and fonts.

Metal will generatet two classes called Font and two tables called Fonts, 
which naturally doesn't compile.

This isn't a big deal for me since I can easily drop one of the tables.

Can't compile DbLinq.Mysql.Prototype.  Missing references that were in
System.Data.DLinq but not in System.Data.Linq.

Can't find reference for :
AssociationAttribute, ColumnAttribute, TableAttribute

Missing code for "double precision" in ParseType(string typeStr) in
PgsqlTypeConversions.cs

case "double precision":
   return NpgsqlDbType.Double;
case "time without time zone":
   return NpgsqlDbType.Timestamp;

MySQLMetal should have a command-switch for deactivation the Pluralize and 
Singularize Methods to prevent wrong renamings for languages different 
than english.

MySqlMetal needs support for enumerations

CodeFile: DbLinq\vendor\Pgsql\VendorPgsql.cs
Method:  ProcessPkField

There is an extra "public." attribute before the sequence name that I
removed with the following extra code line: 

sequenceName =
System.Text.RegularExpressions.Regex.Replace(sequenceName,"([^\\.]*\\.)","");

The is also a problem with sequences that are upper or mixed case, so I
again  added the extra quotes to this existing line:

sbIdentity.Append(";SELECT currval('\""+sequenceName+"\"')");

After these changes, it worked :) I did add an item.

...

What steps will reproduce the problem?
1. Checkout from the repository
2. Open "DbLinq_SqliteOnly.sln"


What is the expected output? What do you see instead?
I get a complaint about a missing project.
And it is actually missing in the checkout also.

What version of the product are you using? On what operating system?
VS 2008, XP, Rev 135 of the source

What steps will reproduce the problem?
1. Click on the link from http://code2code.net/DB_Linq/index.html
2. Use Tortoise SVN to download code from
tsvn:http://code.google.com/p/dblinq2007/source

What is the expected output? What do you see instead?
I get: 
Error: PROPFIND request failed on '/p/dblinq2007/source'  
Error: PROPFIND of '/p/dblinq2007/source': 400 Bad Request
(http://code.google.com)  

Create a new sqlite3 database
run the create_northwind.sql script in that database.

Patch is attached as well as the complete file.

The generated property for a column named "value0" is "value0", causing
conflict with the backing field name.

It sould be mentioned on the Project Home, that only MySQL5 is supported.

I am quite happy that this project exist :) !!!

A strange generated code:

[FunctionEx(Name = "pldbg_get_target_info", ProcedureOrFunction = "")]  
    //public L52_mapCsType_unprepared_for_postgresqltype_targetinfo
pldbg_get_target_info([Parameter(Name = "signature", DbType = "text")]
string signature
    //        ,[Parameter(Name = "targettype", DbType = "oid")] byte[]
targettype) 
    //{ 
    //    IExecuteResult result = base.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), signature, targettype);  
    //    return
(L52_mapCsType_unprepared_for_postgresqltype_targetinfo)result.ReturnValue; 
    //} 

The c# compiler reports:
Error   1   The type or namespace name
'L52_mapCsType_unprepared_for_postgresqltype_targetinfo' could not be found
(are you missing a using directive or an assembly reference?)   

I did comment 10 of these and it worked.

One will get all sorts of errors when running the example because of a 
missing database.

Patch i attached.

When creating a class of a database, which contains the PostgreSQL 
function 'plpgsql_call_handler', this function appears in the generated 
file.

Because this is  function for internal PostgreSQL use only, it should be 
ignored when generating the file. This can be accomplished by chancing 
the SQL command for retrieving the functions in the file Pg_Proc_Sql.cs 
in the function public List<Pg_Proc> getProcs(IDbConnection conn, string 
db). The following should be added to the SQL command: " AND pr.proname 
<> 'plpgsql_call_handler'".

Maybe there is a better way of filtering out this function, but I'm not 
aware of one.

The PostgreSQL type "timestamp with time zone" is not recognized by
SQLMetal. Instead the type is
"L52_mapCsType_unprepared_for_postgresqltype_timestamp with time zone".

Why is the type "timestamp with time zone" not included in the switch
statement of the mapSqlTypeToCsType function in the file
\DB_LINQ\trunk\SqlMetal\schema\pgsql\Mappings.cs?

Couldn't it be added just below the line "case "timestamp without time
zone": "?

I have difficulty using svn through my company's firewall.  Could you 
provide some zip files in your downloads of the metal classes such as 
OracleMetal?  I'd rather not be downloading every class separately using 
the svn web interface.

Missing Not Equal SQL implementation in code file: Operator.cs

method: public static string FormatBinaryOperator(ExpressionType nodeType)

Add the following line:

case ExpressionType.NotEqual: return "!=";

In the class VendorPgsql there is a function ProcessPkField which is
responsible for generating the statement which gets the current value of
the sequence which belongs to the primary key column of the table. This
function   only works if you name your sequences the default way.

Isn't there a way to get the name of the sequence from the column
properties DEFAULT nextval('SEQColumnID')? This works always, instead of
only when you use the default sequence name.

The last missing part: a collision detector in Parent members generator
(which is present in the current implementation)

If one tries to run the Sqlite example it will throw an error because a 
SQLITE compiler condition in RowEnumerator.cs is spelled SQLLITE.

A Patch is attached.

To reproduce, add the following unit test to tests and run it:

#if POSTGRES
public class Northwind1 : Northwind {
public Northwind1(System.Data.IDbConnection connection) : base(connection)
{ }

[System.Data.Linq.Mapping.Table(Name = "Cust1")]
public class Cust1 {
[DbLinq.Linq.Mapping.AutoGenId]
string _customerid;

[System.Data.Linq.Mapping.Column(Storage = "_customerid",
Name = "customerid", IsPrimaryKey = true,
DbType="char(10)",
IsDbGenerated = true,
Expression = "nextval('seq8')")]
public string CustomerId {
get { return _customerid; }
set { _customerid = value; }
}

// Dummy property is required only as workaround over empty insert list bug
// If this bug is fixed this may be removed
string _dummy;
[System.Data.Linq.Mapping.Column(Storage = "_dummy",
DbType = "text", Name = "dummy")]
public string Dummy {
get;
set;
}

}

public DbLinq.Linq.Table<Cust1> Cust1s {

get {
return base.GetTable<Cust1>();
}
}
}

[Test]
public void G11_InsertCharSerialPrimaryKey() {
Northwind dbo = CreateDB();
Northwind1 db = new Northwind1(dbo.DatabaseContext.Connection);
db.ExecuteCommand(@"create sequence seq8;
create temp table cust1 ( CustomerID char(10) DEFAULT nextval('seq8'),
dummy text
);
");

DbLinq.Linq.Table<Northwind1.Cust1> Cust1s =
   db.GetTable<Northwind1.Cust1>();

var Cust1 = new Northwind1.Cust1();
Cust1.Dummy = "";
db.Cust1s.Add(Cust1);
db.SubmitChanges();
db.ExecuteCommand("drop table cust1; drop sequence seq8;");
Assert.IsNotNull(Cust1.CustomerId);
}
#endif 


Observed:

ArgumentException occurs
in SetField(). This exception is not passed to caller. So insert command
fails without any notice to application code.


Proposed fix attached.

(see the title :))

... Yes, that's the best part

MySqlMetal should convert names with underscores to PascalCase

eg [Table(Name="account_login")] should produce AccountLogin instead of 
Account_login

The run_pgMetal.bat file in SqlMetal\bin contains errors:
1. PgsqlMetal.exe needs to be replaced by SqlMetal.exe
2. The commandline option -Provider=PostgreSQL needs to be added.

This also holds true for the other batch files I think

What steps will reproduce the problem?
1. Generate schema file for information_schema(mysql 5.0) using mysqlmetal
2.
3.

What is the expected output? What do you see instead?
no .cs file output instead a stack trace is visible

What version of the product are you using? On what operating system?
dblinq v0.15, Windows XP sp2, .net 3.5, mysql 5.0

Please provide any additional information below.

 MysqlMetal failed:System.Data.SqlTypes.SqlNullValueException: Data is 
Null. This method or property cannot be called on Null values.
   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, 
Boolean checkNull)
   at MySql.Data.MySqlClient.MySqlDataReader.GetString(Int32 column)
   at SqlMetal.schema.mysql.TableSql.fromRow(MySqlDataReader rdr)
   at SqlMetal.schema.mysql.TableSql.getTables(MySqlConnection conn, 
String db)
   at SqlMetal.schema.mysql.Vendor.LoadSchema()
   at SqlMetal.Program.Main(String[] args)

The type "timestamp with timzone" is not supported. Should be inserted in
the mappings.cs file just below the type "timestamp without time zone".

What steps will reproduce the problem?
use a database with a column called "object"

What is the expected output? What do you see instead?
resulting file has to be edited before being compiled

What version of the product are you using? On what operating system?
0.13

Please provide any additional information below.

 it's just necessary to add "object" to the csharp types

What steps will reproduce the problem?

1. Generate cs file using pgsqlmetal for a table with foreign 
key references.

What is the expected output? 

_toode must be in lower case like:

[Association(Storage="_toode", ThisKey="toode",Name="hetkesei_toode_fkey")]

What do you see instead?

Storage="_Toode" is in upper case:

[Association(Storage="_Toode", ThisKey="toode",Name="hetkesei_toode_fkey")]

Andrus Moor

To reproduce:

1. Run sqlmetal against nortwind database:

SQLMetal.exe -Provider=PostgreSql -forceUcaseID=false -
forceUcaseFieldName -forceUcaseTableName -verboseForeignKeys -
user:postgres -database:northwind -server:localhost -password:xxxxx

2. Look into northwind.cs file.

Expected output: 

Only first character in property and table names should be capitalized.

Observed:

1. English language word list is used for captialization. It it not 
possible to turn this off.

2. Some characters in some names are randomly capitalized:

[Column(Storage = "hiredAte", ...
HiredAte
QuantityPeruNit
UnitsInsToCK
[Association(Storage = "fKTeRrRegion", 
fKProdCatG

It happens during deleting rows from MySql 5.0.45 and PostgreSql 8.2 server
and DbLinq in version 0.13 and 0.14

in DbLinq,Linq.MTable.cs in line 326 was:
string sql = "DELETE FROM " + tableName + " WHERE " + proj.keyColumnName + 
" in (" + sbDeleteIDs + ")";
I changed this line to:
string sql = "DELETE FROM " + tableName + " WHERE " + proj.keyColumnName + 
" in ('" + sbDeleteIDs + "')";
and it works in both servers.

What steps will reproduce the problem?

Problem when using pgsqlmetal on a table with manually named primary key
constraint.

When we add a primary key in pgadmin3 which name we set ourselves we have
this problem: 

Missing data from 'constraint_column_usage' for foreign key col1_pk

col1_pk is the manually set primary key constraint name.

When we do not set a name and leave pgadmin3 set the name, there is no
problem when using pgsqlmetal.

Problem with pqsqlmetal.exe

Postgresql type "time without time zone" is being mapped as
"L52_mapCsType_unprepared_for_postgresqltype_time without time zone" which
is not compiling in the generated .NET class. 

Database encoding is 1251.

Please check this page http://msdn2.microsoft.com/en-
us/library/4e5xt97a.aspx to to the OraTypeMap correctly.

NUMBER is not a int is a decimal.


if you do that, the UnknownOracleType_20 will not be necessary...

It's all in the title :)

I implemented missing ExecuteQuery.

This patch contains also implementation with uses Marc Gravell 
HyperDescriptor. To use it #Define HyperDescriptor and add class from 
Codeproject link from source code.

This increases speed about 100x. Hyperdescriptor should also used in all 
other places where properties are set to increase reflection speed 
significantly.

Pleasde review and commit attached patch.

Andrus.

The databinding of a query made with DBLinq works only one way. Added and
deleted records don't get added and deleted to the Database.

Can this be fixed by supporting the IBindingList interface on the Table class?

Run the Sqlite example.

Patch is attached.

Currently pgsqlmetal adds schema names to every table name like 

public.customers

This does not allow to dynamically switch schemas.
npgsql2beta2  has SearchPath conection strings paramter which allows to 
switch schemas dynamically. So schema prefixes are not required in most 
cases.

pgsqlmetal should have command option to disable adding schemas names to 
table names in generated code.

1. See if all extra types are really needed (I hope not)
2. Remove them.

In Postgresql if your column name is not ONLY lowercase (it is upper case
or mix case) you need to put it in quotes in the query. 

I made a quick and unoptimized hack in RowEnumerator.cs, ExecuteSqlCommand
method. The column description is: something that starts with dot, then
everything different than space, comma or new line. Check if my regular
expression is correct.

cmd.CommandText =
System.Text.RegularExpressions.Regex.Replace(cmd.CommandText,
"\\.[^\\s,\\n]*", CapText);
XSqlDataReader _rdr = cmd.ExecuteReader();
rdr2 = new DataReader2(_rdr);

And added this method for the match evaluator:

static string CapText(System.Text.RegularExpressions.Match m)
        {
            // Get the matched string.
            string x = m.ToString();
            // If it is NOT lower case
            if (x != x.ToLower())
                x = ".\"" + x.Substring(1) + "\"";

            return x;
        }

And it worked! :)

Regarding Microsoft DBML, this should not be the case:
- nullable types are always stored as non nullable in "Type" column
- There are no C# optimizations there (because DBML is not C# related)

There is some errors on Oracle DB linq ver 0.14 :
the first and important problem is about generated the primary keys.
If table had more than 1 primary key It doesn’t work correct.
and second problem is about DataField Types that the beter types is here:
public static string mapSqlTypeToCsType(string dbType, decimal? precision)
{
switch(dbType)
{
case “NUMBER”:
return “decimal”;
case “VARCHAR2″:
return “string”;
case “TIMESTAMP”:
return “DateTime”;
case “CHAR”:
return “string”;
case “NVARCHAR2″:
return “string”;
case “DATE”:
return “DateTime”;
case “BLOB”:
return “byte[]”;
case “LONG”:
return “Int64″;
default:
return “UnknownOracleType_20 //(Unprepared for Oracle type “+dbType+”) 
\n”;
}
} 

MySqlMetal will create a short for an unsigned MEDIUMINT

mediumint is a 3 byte value.  Should either make it an int or create some 
new limited value type to properly support it.  Or have mysqlmetal 
error/warn saying it's not supported.

What steps will reproduce the problem?

1. Create a table containing integer column:

create table test ( id integer )

2. Generate cs file using pgsqlmetal

Observed:

DbType = "integer(32,0)"

attibute 

Expected:

DbType = "integer"

attibute 

Andrus Moor

What steps will reproduce the problem?
1. Connecting to oracle database
2.
3.

What is the expected output? What do you see instead?
Error

What version of the product are you using? On what operating system?
0.15

Please provide any additional information below.
Right syntax to write to open the Oracle connection in the OracleVendor.cs 
(Oracle Metal Project) is :

  public DlinqSchema.Database LoadSchema()
        {
            string connStr = string.Format("server={0};user id={1}; 
password={2}; Data Source={3}"
                , mmConfig.server, mmConfig.user, mmConfig.password, 
mmConfig.database);


(...)

instead of 

 public DlinqSchema.Database LoadSchema()
        {
            //string connStr = string.Format("server={0};user id={1}; 
password={2}; database={3}; pooling=false"
            string connStr = string.Format("server={0};user id={1}; 
password={2}"
                , mmConfig.server, mmConfig.user, mmConfig.password, 
mmConfig.database);

            OracleConnection conn = new OracleConnection(connStr);
            conn.Open();

(...)

M. LANGE