{ Analytica Model DB_Library, encoding="UTF-8" }
SoftwareVersion 6.2.0

{!-60000|Attribute AcpStyles}


LinkLibrary DB_Library
Title: DB Library
Description: Contains functions to create, read, and write to an SQL database.
DefaultSize: 72,24
NodeSize: 72,24
DiagState: 2,0,39,1325,792,17,10
FontStyle: Century Gothic,16
FileInfo: 0,LinkLibrary DB_Library,2,2,0,0,DB Library.ana

Text Te103574371
Title: This library currently supports:
Description: - SQL Server
NodeLocation: 248,60,-1
NodeSize: 192,28

Function DB_add_table(tableName: Text; fields: Index; key: Optional; fieldType: Optional Index)
Title: DB_add_table(name, fields, key)
Units: 2
Description: Add a table with specified tableName and fields (names of fields or columns), and fieldType giving the type of each field.~
~
Optional parameter fieldType gives the data type for each field (Boolean, Int, Real, Char, Text) [not yet implemented]~
Has side effects so you must call this function from a button onClick or other script.
Definition: LocalIndex fType :=IF IsNotSpecified(fieldType)~
   THEN CopyIndex(Array(fields, 'TEXT'))~
   ELSE CopyIndex(fieldType);~
Local query := Helper_DB_add_table(tableName,fields,fType);~
DbWrite(DSN, query);~
Add_Delay(SQL_Delay);~
'Created table: ' & tableName;
NodeLocation: 184,152,1
NodeSize: 136,24
WindState: 2,298,47,1237,698

Function DB_update_record(tableName: Text; record: [fields,row]; fields: Index; row: Index Optional; matchData: [matchFields]; matchFields: Index Optional)
Title: DB_update_record(tableName, record, fields)
Description: Add a record into tableName. ~
{ If you don't specify fields, it uses the index of record. Either way, }~
it will give a warning if the fields are not the table's column index.
Definition: Local cleanRecord:=Replace_X_with_Y(TextReplace(record, "'", "''",all: True), null, ' ');~
	Local queryHeader := "UPDATE "&tableName&" SET ";~
	Local queryBody1 := JoinText("["&fields&"] = CONVERT(varchar(8000),'"&cleanRecord&"')", fields, ", ");~
	Local queryBody2 := " WHERE " & IF Size(matchFields) > 1 THEN~
	JoinText("["&matchFields&"] LIKE CONVERT(varchar(8000),'"&matchData, matchFields, separator: "') AND ") & "');" ELSE "["&matchFields&"] LIKE CONVERT(varchar(8000),'"&matchData&"');";~
	Local query := queryHeader & queryBody1 & queryBody2;~
	DbWrite(DSN, query);~

NodeLocation: 184,208,1
NodeSize: 136,24
WindState: 2,213,5,1209,691

Function DB_delete_record(tableName: Text;  matchData: [matchFields]; matchFields: Index)
Title: DB_delete_record(tableName, matchData, matchFields)
Description: Delete all records from tableName with whose values in matchFields (a subset of the column index for this table) match those in matchData. If matchFields is an empty index, it deletes all records in the table.
Definition: Local queryBody := IF Size(matchFields) > 1 THEN~
JoinText("["&matchFields&"] LIKE CONVERT(varchar,'"&matchData, matchFields, separator: "') AND ") & "');" ELSE "["&matchFields&"] LIKE CONVERT(varchar,'"&matchData&"');";~
Local query := "DELETE FROM "&tableName&" WHERE "&queryBody;~
~
DbWrite(DSN, query)
NodeLocation: 184,488,1
NodeSize: 136,24
WindState: 2,755,66,720,482

Function DB_get_record(tableName: Text; matchData: [matchFields] Optional; matchFields: Index)
Title: DB_get_record(tableName, matchData, matchFields)
Description: Returns a table (with local index .rows and the column index for that table) containing the rows from table tableName that match the specified matchData for each item in matchFields(s). Index matchFields should contain one or more of the columns of the table. ~
It gives a warning if the tableName is not in the database or fields contains elements not in the column index of the table.~
If matchData is omitted, it returns the entire table.~
If it finds no matches, .rows is an empty list [].
Definition: Local queryBody := IF Size(matchFields) > 1~
   THEN JoinText("["&matchFields&"] LIKE CONVERT(varchar,'"&matchData, matchFields, separator: "') AND ") & "');" ~
   ELSE "["&matchFields&"] LIKE CONVERT(varchar,'"&matchData&"');";~
	Local query := "SELECT * FROM "&tableName&" WHERE "&queryBody;~
	LocalIndex rows := DbWrite(DSN, query);~
	Local cols := DbLabels(rows);~
	Local result := DbTable(rows, cols);~
	result
NodeLocation: 184,320,1
NodeSize: 136,24
WindState: 2,258,57,1140,541

Function DB_del_table(tableName: Text Atom)
Title: DB_del_table()
Description: Delete table with this name. It also deletes its row index, and its entries in DB_Tables, DB_col_indexes, and DB_row_indexes.
Definition: Local query :='DROP TABLE ' & tableName;~
DbWrite(DSN, query);~
Add_Delay(SQL_Delay);~
'Deleted: ' & tableName
NodeLocation: 184,544,1
NodeSize: 136,24
WindState: 2,223,67,719,350

Module DB_library_helper_fu
Title: DB library helper functions
Description: Functions used by the main DB functions. These typically hold the syntax being sent to a database.
Author: Max Henrion~
Lumina
Date: Sat, Jun 11, 2022 7:11 PM
NodeLocation: 736,160,1
NodeSize: 72,32
DiagState: 2,401,96,1188,634,17,10
Aliases: Alias Al520059107

Index DB_Potential_Field_d
Title: DB Potential Field data types
Description: Not in use yet.
Definition: ['Boolean','Integer','Real','Char','Char2','Text']
NodeLocation: 664,48,1
NodeSize: 96,24

Function Remove_nth(v: Variable; n: number)
Title: Remove_nth(list, n)
Description: Assuming v is an Index variable, remove the nth value.~
Gives a warning if it doesn't have at least n values.
Definition: IF Size(IndexesOf(v))<>1 OR n>Size(v) THEN MsgBox('You cannot remove the '&n&'th element of '&Identifier OF v&' because it has only '&Size(v)&' elements', 0, 'Warning')~
ELSE v := #SetDifference(\(v), \([Slice(v, n)]))
NodeLocation: 176,48,1
NodeSize: 128,24
WindState: 2,527,130,720,350

Button Test_Remove_nth
Title: Test Remove_nth
NodeLocation: 436,48,1
NodeSize: 76,24
OnClick: Remove_nth(Col_indexes_by_db_ta, 1)

Function Check_tableName(name: Text; warn: = True)
Title: Check_tableName(name)
Description: Returns the number in the database of the table with specified name (after adding prefix 'DB_' if needed), or 0 if its not a valid table. ~
If not valid and warn=True (default) it gives a warning message.
Definition: Local tableNum := IF Use_SQL_DB THEN~
	(~
	LocalIndex rows := DbQuery(DSN,"SELECT TABLE_NAME ~
	FROM master.INFORMATION_SCHEMA.TABLES ~
	WHERE TABLE_TYPE = 'BASE TABLE'");~
	Local cols := DbLabels(rows);~
	Local db_table := Unique(DbTable(rows, cols));~
	@[db_table=name];~
~
)~
ELSE~
(~
	IF SelectText(name, 1, 3)<>DB_prefix ~
		THEN name := DB_prefix&name;~
	@[DB_Tbl_names=name]~
);~
	IF tableNum=0 AND warn THEN MsgBox('You are trying to access table '&name&', ~
	which does not exist in the database.', 0, 'Warning');~
	tableNum~

NodeLocation: 176,104,1
NodeSize: 128,24
WindState: 2,310,117,720,514

Function Check_Cols_in_table(n: Number; fields: Index; warn: = True)
Title: Check_Cols_in_table(n: Number; fields: Index)
Description: Returns true if the column index of Table number n contains all elements of fields (including if fields is an empty list) otherwise false.  If false and warn=True, it gives a warning message.~
Usually you use Check_tableName(name) to find the table number n.
Definition: Local result := Size(fields)=0 ~
	OR Product(@[Slice(DB_Col_indexes,n)=fields], fields)>0;~
IF (NOT result) AND warn THEN MsgBox('Mismatch between fields searched by DB_get_record() or DB_add_record() and the columns of table '&Slice(DB_Tbl_names, n), 1, 'Warning');~
result
NodeLocation: 176,160,1
NodeSize: 128,24
WindState: 2,286,73,719,349

Variable Test_check_cols_in_T
Title: Test check_cols_in_Table()
Definition: INDEX fields := [];~
Check_Cols_in_table( 1, fields)
NodeLocation: 432,160,1
NodeSize: 104,24
ValueState: 2,1042,491,416,303,,MIDM

Variable Test_check_tableName
Title: Test check_tableName() DB
Definition: Check_tableName('cars')
NodeLocation: 432,104,1
NodeSize: 104,24
ValueState: 2,388,394,415,303,,MIDM

Function Helper_DB_add_table(tableName: Text; fields: Index; fieldType: Index)
Title: Helper_DB_add_table
Description: Forms query for DB_add_table().
Definition: Local queryHeader := 'CREATE TABLE [dbo].['&tableName&'] (~
     ';~
Local field_and_type := FOR y := 1..size(fields) DO~
(~
JoinText('['&fields[@fields=y]&'] ' &fieldType[@fieldType=y]&' NULL')~
);~
Local queryBody := JoinText(field_and_type, separator:',~
     ')&'~
);';~
queryHeader & queryBody
NodeLocation: 176,216,1
NodeSize: 128,24
WindState: 2,413,19,720,350

Function Helper_DB_insert_rec(tableName: Text; fields: Index; data; rows: Optional Index; selectRows: Optional Index)
Title: Helper_DB_insert_record
Description: Forms query for DB_insert_records().
Definition: Local queryHeader := "INSERT INTO "&tableName&" (" & JoinText("[" & fields & "]", fields,",") & ")~
VALUES~
";~
~
Local queryBody := ~
JoinText("('" & JoinText(data, fields, "', '") & "');~
", rows, "~
"& queryHeader & "");~
~
queryHeader & queryBody
NodeLocation: 176,272,1
NodeSize: 128,24
WindState: 2,155,363,721,485

Function Replace_X_with_Y(data, X, Y)
Title: Replace X with Y
Description: Searches data for X and replaces all occurences with Y.
Definition: IF data = X~
THEN Y~
ELSE data
NodeLocation: 176,384,1
NodeSize: 128,24

Function Helper_DB_Bracket_Co(cols: Index)
Title: Helper_DB_Bracket_Comma_List
Description: Surounds items in an index with brackets and separates them by commas. Helpful when forming the SELECT _____ portion of a query.
Definition: "[" & JoinText(cols, cols, Bracket_Comma_Separa) & "]"
NodeLocation: 176,328,1
NodeSize: 128,24
WindState: 2,344,421,721,485

Decision Bracket_Comma_Separa
Title: Bracket Comma Separator
Definition: "], ["
NodeLocation: 664,112,1
NodeSize: 80,32

Close DB_library_helper_fu

Function DB_insert_records(tableName: Atom Text; records: [fields, row]; fields: Index; row: Index Optional)
Title: DB_insert_records(tableName, records, fields, row)
Description: Add records indexed by fields and rows into tableName. ~
This is much faster than DB_Add_record() for adding multiple records, but does not offer matchData to check whether the table contains matching existing records that need to be updated.
Definition: Local cleanRecord:=Replace_X_with_Y(TextReplace(records, "'", "''",all: True), null, 'NULL');   {Replace Null and use escape chars for apostrophes}~
Local chunkSize:=300;~
Local totalRows:=IndexLength(row);~
IF totalRows > chunkSize {If more rows the SQL likes to write}~
THEN ( {Break the data into chunks and write them sequentially}~
   Local chunkCnt:=Ceil(Size(Row)/chunkSize);~
   Local startRow:=1;~
   Local endRow:=chunkSize;~
   FOR J:=1..chunkCnt DO (~
      ShowProgressBar('Writing to Database', 'Writing rows ' & startRow & ' through ' & endRow & ' of ' & totalRows &  ' total rows.', (endRow - .1) / totalRows);~
      LocalIndex truncRow:=startRow..endRow;~
	   LocalIndex orderedRow:=1..Size(truncRow);~
      Local truncRecord:=cleanRecord[row=truncRow, @truncRow=@orderedRow];~
      Local query := Helper_DB_insert_rec(tableName,fields,truncRecord,orderedRow);~
	  {ConsolePrint(query);}~
      IF debug_mode = Show_Script~
         THEN \query~
         ELSE DbWrite(DSN, query);     ~
		startRow:=endRow + 1;~
      endRow:=Min([startRow+ChunkSize-1, Size(row)]);~
      Add_Delay(SQL_Delay);~
   )~
)~
ELSE ( {Else if few enough rows to make one write operation}~
   Local query := Helper_DB_insert_rec(tableName,fields,cleanRecord,row);~
   IF debug_mode = Show_Script~
      THEN \query~
      ELSE DbWrite(DSN, query);~
   Add_Delay(SQL_Delay);~
)
NodeLocation: 184,264,1
NodeSize: 136,24
WindState: 2,26,14,1178,753

Function DB_find_records(tableName: Text; matchData: [matchFields] Optional; matchFields: Index Optional)
Title: DB_find_records(tableName, matchData, matchFields)
Description: Returns a list of the row numbers (positions) from tableName of records whose values match the specified matchData indexed by matchFields. Index matchFields should contain zero or more of the columns of the table.~
~
If matchData is not specified or [], it returns all rows from the table.~
If matchFields is omitted, it uses the index of matchData.~
If it finds no matches, it returns an empty list [].~
~
It gives a warning if the tableName is not in the database or matchFields contains elements not in the column index of the table.
Definition: Local queryBody := IF Size(matchFields) > 1 THEN~
JoinText("["&matchFields&"] LIKE CONVERT(varchar,'"&matchData, matchFields, separator: "') AND ") & "');" ELSE "["&matchFields&"] LIKE CONVERT(varchar,'"&matchData&"');";~
Local query := "SELECT * FROM "&tableName&" WHERE "&queryBody;~
LocalIndex rows := DbWrite(DSN, query);~
CopyIndex(rows)
NodeLocation: 184,376,1
NodeSize: 136,24
WindState: 2,416,28,720,649

Function DB_send_SQL(SQL_query: Text)
Title: DB_send_SQL(text)
Description: Send SQL statement(s) to the Database server. The function should only be used in niche cases where the provided functions are not applicable.
Definition: DbWrite(DSN,SQL_query)
NodeLocation: 184,656,1
NodeSize: 136,24
WindState: 2,25,-18,720,350

Function DB_grab_columns(tableName: Text; db_columns: Optional Index)
Title: DB_grab_columns(tableName, db_columns)
Description: Returns a list of the row numbers (positions) from tableName of records whose values match the specified matchData indexed by matchFields. Index matchFields should contain zero or more of the columns of the table.~
~
If db_columns is not specified or [], it returns all rows from the table.~
If it finds no matches, it returns an empty list [].~
~
It gives a warning if the tableName is not in the database or matchFields contains elements not in the column index of the table.
Definition: Local column := IF IsNotSpecified(db_columns) THEN "*" ELSE Helper_DB_Bracket_Co(db_columns);~
Local query :=	"SELECT "& column & " FROM " & tableName & ";";~
LocalIndex rows := DbWrite(DSN, query);~
LocalIndex cols := DbLabels(rows);~
Local result := DbTable(rows, cols);~
result
NodeLocation: 184,432,1
NodeSize: 136,24
WindState: 2,415,28,977,776

Function Add_Delay(cnt)
Title: Add_Delay(cnt)
Description: Function to add delay between commands. There are rare occurences when working with technologies such as databases that Analytica will complete an action and receive confirmation that said action has been completed when it hasn't actually, which can then result in an error when lines following are read. This function is a workaround to that issue and should only be used when necessary.
Definition: Local iter:=0;~
WHILE iter < cnt DO (~
   iter:=iter+1~
);~
iter
NodeLocation: 184,600,1
NodeSize: 136,24

Decision SQL_Delay
Title: SQL Delay
Description: Input to Add_Delay(). 10M equates to about 5 seconds.
Definition: 1
NodeLocation: 408,600,1
NodeSize: 72,24

Function DB_add_record(tableName: Atom Text; records: [fields, row]; fields: Index; row: Index Optional)
Title: DB_add_record(tableName, matchData, matchFields)
Description: ~
~
This function determines whether or not to use DB_insert_records OR DB_update_record.
Definition: /* Check data; if exists, call UPDATE func() else call INSERT func() */~
1
NodeLocation: 464,240,1
NodeSize: 136,24
WindState: 2,500,344,720,350

Decision Choose_Connection_St
Title: Choose Connection String
Description: Select the connection string to be used by all DB_Library functions.~
~
CURRENTLY DISABLED.
Definition: Choice(Connection_String_Li,1,0)
NodeLocation: 936,632,1
NodeSize: 72,32
Aliases: FormNode Fo2078633187

Index Connection_String_Li
Title: Connection String List
Description: List of connection strings added by the user.
Definition: []
NodeLocation: 936,560,1
NodeSize: 72,32

Decision SQL_Version
Title: SQL Version
Description: Select the version of SQL you'll be working with. This makes sure the correct syntax is used when making calls to the database.
Definition: Choice(Supported_SQL_Versio,1,0)
NodeLocation: 928,432,1
NodeSize: 72,32
Aliases: FormNode Fo1675589859

Index Supported_SQL_Versio
Title: Supported SQL Versions
Description: List of SQL Versions that have their syntax supported by the library.
Definition: ['SQL Server']
NodeLocation: 928,360,1
NodeSize: 72,32

Decision debug_mode
Title: Debug Mode
Description: Currently selected debug mode for DB_Library functions. "send query" is the equivalent of turning off debugging.
Definition: Choice(debug_options,3,0)
NodeLocation: 928,232,1
NodeSize: 72,32
Aliases: FormNode Fo1766389987

Index debug_options
Att_PrevIndexValue: ['show script','show script and send query','send query']
Title: Debug Options
Description: List of all possible debug modes for DB_Library functions.~
~
Note: "show script and send query" may not yet be supported by all functions.
Definition: ['show script','show script and send query','send query']
NodeLocation: 928,160,1
NodeSize: 72,32

FormNode Fo1766389987
Title: debug
Definition: 0
NodeLocation: 1056,288,1
NodeSize: 200,16
NodeInfo: 1,,,,,,,259,,,,,,0
Original: debug_mode

FormNode Fo1675589859
Title: SQL Version
Definition: 0
NodeLocation: 1056,488,1
NodeSize: 200,16
NodeInfo: 1,,,,,,,256,,,,,,0
Original: SQL_Version

Variable DSN
Title: Connection String
Description: Connection string to SQL Database.
Definition: {'DSN=analytica_db'}~
'DRIVER=SQL Server;SERVER=suan-stage.analytica.com,1433;Database=SQLLibrary;UID=SQLWriter;PWD=Ben$DB2022!'
NodeLocation: 736,240,1
NodeSize: 72,40
ValueState: 2,228,234,416,303,,MIDM

Module Debug_Options_Label_
Title:  Debug Options Label Variables
Description: Contains label variables pertaining to debug options.
NodeLocation: 1128,192,1
NodeSize: 72,40
DiagState: 2,0,0,296,304,17,10

Decision Show_Script
Title: Show Script
Description: Label variable for debug options.
Definition: Choice(debug_options,1,0)
NodeLocation: 120,56,1
NodeSize: 72,32

Decision Show_Script_Send_Que
Title: Show Script & Send Query
Description: Label variable for debug options.
Definition: Choice(debug_options,2,0)
NodeLocation: 120,128,1
NodeSize: 72,32

Decision Send_Query
Title: Send Query
Description: Label variable for debug options.
Definition: Choice(debug_options,3,0)
NodeLocation: 120,200,1
NodeSize: 72,32

Close Debug_Options_Label_

Function DB_List_Tables()
Title: DB_List_Tables()
Description: Shows all tables in database.
Definition: LocalIndex rows := DB_send_SQL("SELECT name~
FROM sys.objects~
WHERE type_desc = 'USER_TABLE'");~
LocalIndex cols := DbLabels(rows);~
Local result := DbTable(rows, cols);~
result
NodeLocation: 184,712,1
NodeSize: 136,24
WindState: 2,25,-18,720,350

FormNode Fo2078633187
Title: Choose Connection String
Definition: 0
NodeLocation: 1060,696,1
NodeSize: 196,16
NodeInfo: 1,,,,,,,152,,,,,,0
Original: Choose_Connection_St

Close DB_Library