Can a structure be defined dynamically by csv headers

[craig7]

Can a structure, such as the example below from the PureBasic book be defined by the column headers in a CSV imported into PureBasic... or in any way by the user of the software. I guess what I am asking is, can the code itself be made to alter in response to what the user does.

Structure FISH
Kind.s
Weight.s
Color.s

EndStructure

//I would then like to add each row from the csv - a more automated version of the following from the book?

Dim FishInTank.FISH(2)
FishInTank(0)\Kind = "Clown Fish"
FishInTank(0)\Weight = "4 oz."
FishInTank(0)\Color = "Red, White and Black"
FishInTank(1)\Kind = "Box Fish"
FishInTank(1)\Weight = "1 oz."
FishInTank(1)\Color = "Yellow"
FishInTank(2)\Kind = "Sea Horse"
FishInTank(2)\Weight = "2 oz."
FishInTank(2)\Color = "Green"

[DarkDragon]

Structures have to be defined at compile time. For that use case hashmaps are typically used.

[skywalk]

You can use in memory database tables created dynamically.

[the.weavster]

I think the JSON commands give you the closest thing to dynamically changing structures.

[mk-soft]

You can use LinkedList ...

Found an old example but it work
https://www.purebasic.fr/german/viewtop ... 82#p310782

[craig7]

You can use LinkedList ...

Found an old example but it work
https://www.purebasic.fr/german/viewtop ... 82#p310782

Thanks !

[craig7]

Structures have to be defined at compile time. For that use case hashmaps are typically used.

Do you mean hashtable / map?

[craig7]

You can use in memory database tables created dynamically.

I was thinking about using the DB library... but does that not then creates an external dependency on SQLite or whatever DB is being used? Would prefer to use something built into PureBasic for reliability.

[skywalk]

sqlite3.lib is statically loaded with your app.exe. No dependency, unless you need a feature not in the current lib. Then you would dynamically load sqlite3.dll of your compilation.
Fred said we can even statically load our own VS2022 versions of sqlite3.lib, but I have not succeeded yet.

[craig7]

sqlite3.lib is statically loaded with your app.exe. No dependency, unless you need a feature not in the current lib. Then you would dynamically load sqlite3.dll of your compilation.
Fred said we can even statically load our own VS2022 versions of sqlite3.lib, but I have not succeeded yet.

Ok great that is another option then thanks.

[infratec]

https://www.quackit.com/sqlite/tutorial ... v_file.cfm

[infratec]

A quick implementation with an user function:

Code: Select all

EnableExplicit

Structure IndexArray
  *Index[0]
EndStructure


PrototypeC.l Prototype_CallBack(*pCtx, argc.l, *argv.IndexArray, *colnames.IndexArray)

ImportC "sqlite3.lib" 
  sqlite3_create_function.l(DatabaseID, zFunctionName.p-utf8, nArg.l, eTextRep.l, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_value_text16(*sqlite3_value)
  sqlite3_context_db_handle.i(*context)
  sqlite3_exec(*db, sql.p-utf8, callback.Prototype_CallBack, *firstArg, *errmsg)
EndImport

#SQLITE_UTF16 = 4




ProcedureC createTableFromCSV(*context, argc.i, *argv.IndexArray)
  
  Protected Filename$, File.i, Line$, FieldCount.i, Table$, SQL$, Field$, *DB, i.i
  
  
  Filename$ = PeekS(sqlite3_value_text16(*argv\Index[0]))
  File = ReadFile(#PB_Any, Filename$)
  If File
    Table$ = GetFilePart(Filename$, #PB_FileSystem_NoExtension)
    
    Line$ = ReadString(File)
    ;Debug Line$
    
    SQL$ = "CREATE TABLE " + Table$ + "("
    
    FieldCount = 1
    Repeat
      Field$ = StringField(Line$, FieldCount, ",")
      If Field$ <> ""
        SQL$ + Trim(Field$) + " TEXT,"
      EndIf
      FieldCount + 1
    Until Field$ = ""
    SQL$ = RTrim(SQL$, ",") + ")"
    ;Debug SQL$
    
    *DB = sqlite3_context_db_handle(*context)
    
    sqlite3_exec(*DB, SQL$, #Null, #Null, #Null)
    
    While Not Eof(File)
      Line$ = ReadString(File)
      SQL$ = "INSERT INTO " + Table$ + " VALUES ("
      
      For i = 1 To FieldCount
        Field$ = StringField(Line$, i, ",")
        SQL$ + Trim(Field$) + ","
      Next i
      SQL$ = RTrim(SQL$, ",") + ")"
      ;Debug SQL$
      
      sqlite3_exec(*DB, SQL$, #Null, #Null, #Null)
    Wend
    
    CloseFile(File)
  EndIf
  
EndProcedure




Define Filename$


UseSQLiteDatabase()

Filename$ = OpenFileRequester("Choose a CSV file", GetPathPart(ProgramFilename()), "CSV|*.csv", 0)
If Filename$

If OpenDatabase(0, ":memory:", "", "")
  
  sqlite3_create_function(DatabaseID(0), "createTableFromCSV",  1, #SQLITE_UTF16, #Null, @createTableFromCSV(), #Null, #Null)
  
  DatabaseUpdate(0, "SELECT createTableFromCSV('" + Filename$ + "')")
  
  If DatabaseQuery(0, "SELECT * FROM Test")
    While NextDatabaseRow(0)
      Debug GetDatabaseString(0, 0) + " " + GetDatabaseString(0, 1) + " " + GetDatabaseString(0, 2)
    Wend
    FinishDatabaseQuery(0)
  Else
    Debug DatabaseError()
  EndIf
  
  
  Debug ""
  Debug "Return as JSON Array:"
  Debug ""
  
  
  If DatabaseQuery(0, "SELECT json_group_array(json_object('ArtistId', ArtistId, 'ArtistName', ArtistName, 'Bio', Bio)) FROM Test")
    While NextDatabaseRow(0)
      Debug GetDatabaseString(0, 0)
    Wend
    FinishDatabaseQuery(0)
  Else
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(0)
Else
  Debug DatabaseError()
EndIf

EndIf

Sample CSV:

Code: Select all

ArtistId,ArtistName,Bio
1,"Joe Satriani",""
2,"Steve Vai",""
3,"The Tea Party",""
4,"Noiseworks",""
5,"Wayne Jury",""
6,"Mr Percival",""
7,"Iron Maiden",""
8,"Atmasphere","Australian jazz band centred around polyrhythms."
9,"Ian Moss",""
10,"Magnum",""
13,"Primus",""
14,"Pat Metheny",""
15,"Frank Gambale",""
16,"Mothers of Invention",""

Can be extended.
Detection of field types
...

[craig7]

A quick implementation with an user function:

Code: Select all

EnableExplicit

Structure IndexArray
  *Index[0]
EndStructure


PrototypeC.l Prototype_CallBack(*pCtx, argc.l, *argv.IndexArray, *colnames.IndexArray)

ImportC "sqlite3.lib" 
  sqlite3_create_function.l(DatabaseID, zFunctionName.p-utf8, nArg.l, eTextRep.l, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_value_text16(*sqlite3_value)
  sqlite3_context_db_handle.i(*context)
  sqlite3_exec(*db, sql.p-utf8, callback.Prototype_CallBack, *firstArg, *errmsg)
EndImport

#SQLITE_UTF16 = 4




ProcedureC createTableFromCSV(*context, argc.i, *argv.IndexArray)
  
  Protected Filename$, File.i, Line$, FieldCount.i, Table$, SQL$, Field$, *DB, i.i
  
  
  Filename$ = PeekS(sqlite3_value_text16(*argv\Index[0]))
  File = ReadFile(#PB_Any, Filename$)
  If File
    Table$ = GetFilePart(Filename$, #PB_FileSystem_NoExtension)
    
    Line$ = ReadString(File)
    ;Debug Line$
    
    SQL$ = "CREATE TABLE " + Table$ + "("
    
    FieldCount = 1
    Repeat
      Field$ = StringField(Line$, FieldCount, ",")
      If Field$ <> ""
        SQL$ + Trim(Field$) + " TEXT,"
      EndIf
      FieldCount + 1
    Until Field$ = ""
    SQL$ = RTrim(SQL$, ",") + ")"
    ;Debug SQL$
    
    *DB = sqlite3_context_db_handle(*context)
    
    sqlite3_exec(*DB, SQL$, #Null, #Null, #Null)
    
    While Not Eof(File)
      Line$ = ReadString(File)
      SQL$ = "INSERT INTO " + Table$ + " VALUES ("
      
      For i = 1 To FieldCount
        Field$ = StringField(Line$, i, ",")
        SQL$ + Trim(Field$) + ","
      Next i
      SQL$ = RTrim(SQL$, ",") + ")"
      ;Debug SQL$
      
      sqlite3_exec(*DB, SQL$, #Null, #Null, #Null)
    Wend
    
    CloseFile(File)
  EndIf
  
EndProcedure


UseSQLiteDatabase()



If OpenDatabase(0, ":memory:", "", "")
  
  sqlite3_create_function(DatabaseID(0), "createTableFromCSV",  1, #SQLITE_UTF16, #Null, @createTableFromCSV(), #Null, #Null)
  
  DatabaseUpdate(0, "SELECT createTableFromCSV('c:\tmp\PureBasic\csvtest\Test.csv')")
  Debug DatabaseError()
  
  If DatabaseQuery(0, "SELECT * FROM Test")
    While NextDatabaseRow(0)
      Debug GetDatabaseString(0, 0) + " " + GetDatabaseString(0, 1) + " " + GetDatabaseString(0, 2)
    Wend
    FinishDatabaseQuery(0)
  Else
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(0)
Else
  Debug DatabaseError()
EndIf

Sample CSV:

Code: Select all

ArtistId,ArtistName,Bio
1,"Joe Satriani",""
2,"Steve Vai",""
3,"The Tea Party",""
4,"Noiseworks",""
5,"Wayne Jury",""
6,"Mr Percival",""
7,"Iron Maiden",""
8,"Atmasphere","Australian jazz band centred around polyrhythms."
9,"Ian Moss",""
10,"Magnum",""
13,"Primus",""
14,"Pat Metheny",""
15,"Frank Gambale",""
16,"Mothers of Invention",""

Can be extended.
Detection of field types
...

Oh thanks for the example, that will be very useful.

[skywalk]

Wow, another great example from infratec.
I wondered how to return an array from SQLite in a userfunction.
This could help solve a complicated 2D query.

[infratec]

I extended the listing above.

It returns now also the content of the table as JSON array.