Add option for customised TableName in TableMapping class

[olumide-oyetoke]

The table query method (and similar methods) always requires the table ORM class Type or a TableMapping object.

List<object> Query (TableMapping map, string query, params object[] args);
List<T> QueryScalars<T> (string query, params object[] args);

The problem is if the sqlite Table (and it's name) are dynamically created at runtime, there will be no suitable precompiled 'class' to be used as Type in the above code.

Example: My sqlite tables are created at runtime. The tables are identical but only differs in name (by appending an integer) MyTable1, MyTable2, MyTable3... Etc. Each table have same columns. For example Id, Name, Amount, Description

Even if I create a class MyTable, its default TableName will be "MyTable" If I want to query the content on MyTable2, or MyTable3, I cannot successfully pass in MyTable as class Type. Meanwhile at runtime, I wouldn't have MyTableX classes.

List<object> Query (TableMapping map, string query, params object[] args);
List<T> QueryScalars<T> (string query, params object[] args);

Having examined the way TableMapping is implemented, I think adding an optional parameter of string customTableName will solve this problem.

ORIGINAL CODE

public TableMapping (Type type, CreateFlags createFlags = CreateFlags.None)
{
	MappedType = type;
	CreateFlags = createFlags;

	var typeInfo = type.GetTypeInfo ();
#if ENABLE_IL2CPP
	var tableAttr = typeInfo.GetCustomAttribute<TableAttribute> ();
#else
	var tableAttr =
	typeInfo.CustomAttributes.Where (x => x.AttributeType == typeof (TableAttribute)).Select (x => (TableAttribute)Orm.InflateAttribute (x)).FirstOrDefault ();
#endif

TableName = (tableAttr != null && !string.IsNullOrEmpty (tableAttr.Name)) ? tableAttr.Name : MappedType.Name;
WithoutRowId = tableAttr != null ? tableAttr.WithoutRowId : false;
// other codes
} 

SUGGESTED

public TableMapping (Type type, CreateFlags createFlags = CreateFlags.None, string customTableName = null)
{
// other code
if (customTableName != null)
{
TableName = customTableName;
} 
else
{
TableName = (tableAttr != null && !string.IsNullOrEmpty (tableAttr.Name)) ? tableAttr.Name : MappedType.Name;
} 
// other code
} 

This will be useful in cases where there are identical tables that differ in name only, which do not have predefined classes being expected by TableMapping implementation.

[csm101]

There is one thing I do not understand in your proposal: all these functions

• QueryScalars<T>(string sql,...)
• Query<T>(string sql,...)
• Query(TableMapping map, string sql,...)

make absolutely no use of the TableName property of TableMapping.

These functions bindly trust the programmer about the fact that the provided sql query will return records whose column names are compatible to the column names declared by the type T you are providing, they don't care about how the query has generated those records: the TableMapping (or the generic type T) are used only to know what record type to instantiate for each record returned by the query and to know which columns must be assigned to which property.

To give you an example: this code works: it reads a record of type MyTable from a database that is completly empty and contains no tables at all: I just fed to Query<T> a query that returns some fixed values, aliasing them to the column names of the T type:

[Table("MyTable")]
  public class MyRec
  {
    public string Col1 { get; set; }
    public string Col2 { get; set; }
  }

  [Test]
  public void TestQuery()
  {
    using var conn = new SQLiteConnection(":memory:");
    List<MyRec> result = conn.Query<MyRec>("select 'FirstValue' AS Col1, 'SecondValue' as Col2");
    Assert.That(result.Count, Is.EqualTo(1));
    Assert.That(result[0].Col1, Is.EqualTo("FirstValue"));
    Assert.That(result[0].Col2, Is.EqualTo("SecondValue"));

  }

Am I missing something?

[olumide-oyetoke]

There is one thing I do not understand in your proposal: all these functions

• QueryScalars(string sql,...)
• Query(string sql,...)
• Query(TableMapping map, string sql,...)

make absolutely no use of the TableName property of TableMapping.

These functions bindly trust the programmer about the fact that the provided sql query will return records whose column names are compatible to the column names declared by the type T you are providing, they don't care about how the query has generated those records: the TableMapping (or the generic type T) are used only to know what record type to instantiate for each record returned by the query and to know which columns must be assigned to which property.

To give you an example: this code works: it reads a record of type MyTable from a database that is completly empty and contains no tables at all: I just fed to Query a query that returns some fixed values, aliasing them to the column names of the T type:

[Table("MyTable")]
  public class MyRec
  {
    public string Col1 { get; set; }
    public string Col2 { get; set; }
  }

  [Test]
  public void TestQuery()
  {
    using var conn = new SQLiteConnection(":memory:");
    List<MyRec> result = conn.Query<MyRec>("select 'FirstValue' AS Col1, 'SecondValue' as Col2");
    Assert.That(result.Count, Is.EqualTo(1));
    Assert.That(result[0].Col1, Is.EqualTo("FirstValue"));
    Assert.That(result[0].Col2, Is.EqualTo("SecondValue"));

  }

Am I missing something?

From your example above, the Query looks for a Table named "MyTable" within the Database, trusting that the returned columns will correspond with the MyRec class Col1 and Col2. This works because you already used [Table("MyTable")] when creating the MyRec class.

My point is, what if you need to Query other similar tables at runtime, which have the same columns but only differ in Table name. e.g. MyTable1, MyTable2, MyTable3

Even though all 3 Tables conform to the MyRec class, I don't think specifying MyRec as T parameter will work. The code will go looking for a Table called "MyTable" in the database always.

There are a lot of places in the SQLite.cs file where the "map.TableName" is being used to retrieve the name while performing a SELECT statement on the database. Where "map" is an instance of TableMapping.

Below is another example of how the code relies on TableName to retreive data from the appropriate table. This is from within the TableMapping class too.

if (PK != null) {
				GetByPrimaryKeySql = string.Format ("select * from \"{0}\" where \"{1}\" = ?", TableName, PK.Name);
			}
			else {
				// People should not be calling Get/Find without a PK
				GetByPrimaryKeySql = string.Format ("select * from \"{0}\" limit 1", TableName);
			}

My point is that, when I need data from a table, the SQLite SELECT statement must specify a Table name, and in this sqlite-net-pcl, the Table name relies on either the class name of the object or the manually specified table name used during the class declaration at compile time.

[csm101]

In my example Sqlite does not try to do anything regarding the table name: there isn't any table in the example database. sqlite-net needs to know the phisical table name only for operations where the SQL command to be executed is generated by sqlite-net itself, like these ones, for example:

• connection.Find<MyRec>(key);
• connection.Table<MyRec>().Where(...).ToList()
• connection.Insert(new MyRec {...})
• connection.Delete(rec);

in these cases, the sql command is generated by sqlite, so it needs to know the table name, none of the example methods you mentioned in your post does actually make any use of the table name, since the sql command to be executed is provided externally. Now I understand your point,.. you just picked by mistake the only functions that would not be impacted at all by your proposal

[olumide-oyetoke]

In my example Sqlite does not try to do anything regarding the table name: there isn't any table in the example database. sqlite-net needs to know the phisical table name only for operations where the SQL command to be executed is generated by sqlite-net itself, like these ones, for example:

• connection.Find(key);
• connection.Table().Where(...).ToList()
• connection.Insert(new MyRec {...})
• connection.Delete(rec);

in these cases, the sql command is generated by sqlite, so it needs to know the table name, none of the example methods you mentioned in your post does actually make any use of the table name, since the sql command to be executed is provided externally. Now I understand your point,.. you just picked by mistake the only functions that would not be impacted at all by your proposal

Exactly. The conn.Query method is not impacted (I mistakenly generalised by using it as my example).

conn.Get conn.Find Insert, Update etc are the examples I was meant to use since they try to act on a table whose name is retrieved from the TableMapping object.

As I just found out now (I didn't realise earlier too) , I will be able to use the conn.Query and conn.Execute methods to achieve my aim by supplying all my sql commands explicitly for all my row retrievals and CRUD transactions by manually supplying my Table names.

Though that will make me lose out of the ease of using most of the ORM advantages of sqlite-net-pcl especially for Insert and Update. Till hopefully this proposal is implemented.