vbRichClient5 SQLite Database Definition & Creation Helper Classes

[jpbro]

This is some older code of mine that I've just updated to work with vbRichClient5 and to support Serialization/Deserialization. It may even have some bug fixes over the last publicly available version since I think it was updated a bit over the years.

It came up in the following thread: http://www.vbforums.com/showthread.p...=1#post5135705

So I thought I would post it here for posterity: JPBDbFactory.zip

UPDATE February 20, 2017

Added JSON Serialization and Deserialization. This is a first pass, and I already think I will probably refactor bits, but it appears to be working as is. As usual, let me know if you run into any problems!

UPDATE February 6, 2017

Added preliminary support for Foreign Keys. Let me know if I've missed any use cases with my object model.

Currently you can add foreign keys to any table (including Deferrable FKs with optional ON DELETE and ON UPDATE declarations).

What Is This?

It is a collection of helper classes that let you easily define Tables, Fields, Indexes, and Foreign Keys in code, then create SQLite databases and connection objects in memory or on disk. For example:

   Dim lo_Db As JPBDbFactory.CDbFactory
   Dim lo_Table As JPBDbFactory.CDbTableDef
   Dim la_DbSerial() As Byte
   
   ' Create the main DB definition helper class
   Set lo_Db = New JPBDbFactory.CDbFactory
   
   ' Add a table
   Set lo_Table = lo_Db.TableDefinitions.Add("my_table")
   
   With lo_Table
      ' Add fields to the table
      .Fields.Add "field_1", vbInteger, True  ' Create a primary field
      .Fields.Add "field_2", vbString, , , fielddefault_CurrentDateAndTime   ' Add a field that defaults to the current date and time (UTC ISO 8601)
      .Fields.Add "field_3", vbString, , False, fielddefault_Literal, "NEW ITEM"   ' Add a field that defaults to the text "NEW ITEM" and does notdoes not allow NULL values
      
      ' Index a field on the table
      With .Indexes.Add("idx_my_table_field3")
         .Add lo_Table.Fields.Item("field_3"), fieldsortorder_Ascending
      End With
   End With
   
   ' Build the schema and save the DB to disk (overwriting any existing file)
   lo_Db.CreateFileDatabase App.Path & "\test.sqlite", , True

I've also included 3 optional "useful" fields with associated TRIGGERs that you can activate with Boolean properties - AutoRowGuid (this will generate a GUID for every newly created row), AutoCreatedDate (this will be set to the current UTC date and time in ISO8601 format on record INSERT), and AutoModifiedDate (this will be set to the current UTC data and time in ISO8601 format on INSERT and UPDATE). The field names for the above "auto" fields are jpbdbf_rowguid, jpbdbf_created, and jpbdbf_lastmodified repspectively (for use with your SELECT statements).

As per CarlosRocha's requirements in the thread linked above, the classes as now fully serializable/deserializable. Just call the Serialize method on the CDbFactory class to get a byte array for storing state, and call Deserialize on a new copy of the CDbFactory class passing the results of a previous Serialize call to restore the state.

The code should be fairly self-explanatory, but I'm happy to answer any questions about it here.