Sql – Problem about ActiveRecord Templates for SQLite

activerecordsqlitesubsonicsubsonic3

I try to using the SQLite in SubSonic.Examples.ActiveRecord project so:

Add a reference for System.Data.SQLite
Copy the SQLite.ttinclude and Settings.ttinclude from \T4 Templates\TemplateProviders\ to the Models_Generated\
Change the <#@ include file="SQLite.ttinclude" #> in the SQLite.ttinclude and Settings.ttinclude
Change the settings: Namespace, ConnectionStringName, DatabaseName to "Blog" in the Settings.ttinclude file.

I meet two errors when regenerate the code:
first one is because of missing a "break" at SQLite.ttinclude line #80

case "long":
case "integer":
    sysType= "long";
    break;  // missing a break
case "guid":

After add a "break" the code file "Context.cs" can generate successfully, but the ActiveRecord.cs can not generate, there is a Warning:

Multiple template directives were found in the template. All but the first one will be ignored. Multiple parameters to the template directive should be specified within one template directive. D:\LP\Study\SubSonic 3 Final\Examples\SubSonic.Examples.ActiveRecord\Models_Generated\Settings.ttinclude 1 4

Best Answer

I download the latest version from SubSonic's github and everything is OK!

The new version a new T4 file: Structs.tt

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

SQLite – UPSERT not INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Related Topic