Add ability to convert arbitrary object to SQLite table definition

Coordinator
Jan 25, 2012 at 3:45 PM

Proposed on Google+ by Shanon Olsson.  Add to the module a way to convert an arbitrary object into a valid table specification for a SQLite database.

Initial thoughts on implementation options:

1. A ConvertTo-SQLiteTableSpecification module member that will create a SQLite table specification Hashtable for an object type.

Proposed usage:
<input> | ConvertTo-SQLiteTableSpecification | new-item db:\MyTable

2. Expand new-item to accept arbitrary input objects when creating new tables. 

Option A:

When the user supplies pipeline input that is not recognized as one of the currently valid table specifications - that is, a hashtable, a DDL string, or dynamic parameters - new-item will assume that the table(s) should be created or expanded to include the fields in the objects.  If no table name is supplied, new tables will be created for each type of object encountered in the input; however, each table will reflect only one level of the object graph - complex property types will be ignored (at least for the first implementation).

In addition, a new table record is created for each input object.

Example:

get-eventlog -log application -newest 50 | new-item db:\;

This example creates a single new table named 'EventLogEntry' (derived from the input object typename) with the following DDL:

 

Create Table EventLogEntry (
  Category text,
  CategoryNumber integer,
  Data blob,
  EntryType integer,
  Index integer,
  InstanceId integer,
  MachineName  text,
  Message text,
  ReplacementStrings text.
  Source Text,
  TimeGenerated text,
  TimeWritten  text,
  UserName  text
);

 

Option B:

A new-item -itemtype parameter value that will indicate the user wishes to auto-define table columns from the object graph.  This option is identical to A with the exception that it requires an explicit request from the user to avoid getting a parameter binding error.

Example:

get-eventlog -log application -newest 50 | new-item db:\ -itemType autotable;

In addition to the implementation chosen, an Out-SQLite cmdlet should be added to the module to keep things simple:

<input> | out-sqlite -datasource <datasourcespec> -include <wildcard-property-name-pattern>

<input> | out-sqlite -path <path-to-sqlite-drive> -include <wildcard-property-name-pattern>

Jan 25, 2012 at 3:55 PM

Talk about low barrier to entry for database creation! This would rock. I somewhat prefer option B. The reason is that it's so easy to pipe things around in PowerShell and I'd hate to accidentally screw up a database. Or is there perhaps a way to constrain a database at table creation time to either allow or disallow this autotable behavior on a table by table basis?