Hi All,
I'd had a go at creating a plugin along these lines before. See these links.
http://forum.thegamecreators.com/?m=forum_view&t=202527&b=18
and
http://forum.thegamecreators.com/?m=forum_view&t=202548&b=8
Those links also contain lots of useful information about SQLITE3.
I've stripped that one down to basics and created a new version with a simplified command set.
However, it still very much needs testing and honing.
[edit]
Current version 0.5 (17/11/2013) -
I've added 16 new commands/functions. I've really tried to add to the functionality and make it easier to use the pragmas and sqlite_master table for information etc.
Also, with the helpfiles I've poured in everything I know about SQLITE3 and the background stuff about the pragmas, sqlite_master and SQL statements...
With functions, Rows start at 1 in commands but columns start at 0.
A column count of 7 would indicate there were columns 0,1,2,3,4,5,6 - also example project now attached. One more new command and some tweaks.
Command Set
Command Set:-
SQLITE OPEN DATABASE - a function returning an INTEGER value from (DatabaseID INTEGER, DatabaseFilename STRING, Username STRING, Password STRING)
SQLITE CLOSE DATABASE - a function returning an INTEGER value from (DatabaseID INTEGER,[ flagVacuumTheDatabase INTEGER])
SQLITE SAVE DATABASE - a function returning an INTEGER value from (DatabaseID INTEGER, DatabaseFileName STRING, Username STRING, Password STRING)
SQLITE VACUUM DATABASE - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE DATABASE EXIST - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE GET LAST ERROR$ - a function returning a STRING value from <*no parameters*>
SQLITE TABLE EXIST - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE TABLE COUNT - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE GET TABLE NAME$ - a function returning a STRING value from (DatabaseID INTEGER, NumberOfTableUpToTableCount INTEGER)
SQLITE GET TABLE CREATION SQL$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING)
SQLITE TRUNCATE TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE EMPTY TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE DROP TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE DELETE TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE CREATE TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnsDefinitionsAndConstraints STRING)
SQLITE RENAME TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, OriginalTableName STRING, NewTableName STRING)
SQLITE TABLE ROW COUNT - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE TABLE NAMED COLUMN EXIST - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE TABLE COLUMN NAME$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER)
SQLITE TABLE COLUMN INDEX - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE TABLE COLUMN DEFINITION$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER)
SQLITE TABLE COLUMN SIZE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER)
SQLITE TABLE COLUMN COUNT - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE TABLE CREATE FIELDSTRING OF COLUMNS$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING,[ DelimeterForFieldString STRING])
SQLITE TABLE CELL STRING$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER, RowNumber INTEGER)
SQLITE TABLE CELL INTEGER - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER, RowNumber INTEGER)
SQLITE TABLE CELL FLOAT - a function returning a FLOAT value from (DatabaseID INTEGER, TableName STRING, ColumnNumber INTEGER, RowNumber INTEGER)
SQLITE FIND TABLE CELL STRING$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, FindInColumnA STRING, WhereColumnB1 STRING, HasValueB2 STRING,[ AndWhereColumnC1 STRING,[ HasValueC2 STRING])
SQLITE FIND TABLE CELL INTEGER - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, FindInColumnA STRING, WhereColumnB1 STRING, HasValueB2 STRING,[ AndWhereColumnC1 STRING,[ HasValueC2 STRING])
SQLITE FIND TABLE CELL FLOAT - a function returning a FLOAT value from (OldString INTEGER, DatabaseID INTEGER, TableName STRING, FindInColumnA STRING, WhereColumnB1 STRING,[ HasValueB2 STRING,[ AndWhereColumnC1 STRING,[ HasValueC2 STRING])
SQLITE BEGIN SQL QUERY - a function returning an INTEGER value from (DatabaseID INTEGER, SQLQuery STRING)
SQLITE BEGIN SQL QUERY SQLITEMASTER - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE BEGIN SQL QUERY TABLELIST - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE BEGIN SQL QUERY DATABASE INDEXLIST - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE BEGIN SQL QUERY PRAGMA TABLEINFO - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE BEGIN SQL QUERY PRAGMA INDEXLIST - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE BEGIN SQL QUERY PRAGMA INDEXINFO - a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING)
SQLITE BEGIN SQL QUERY PRAGMA FOREIGNKEYLIST - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE BEGIN SQL QUERY SPECIFIC - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName1 STRING, ColumnValue1 STRING,[ ColumnName2 STRING,[ ColumnValue2 STRING,[ ColumnName3 STRING,[ ColumnValue3 STRING])
SQLITE FURTHER SQL QUERY - a function returning an INTEGER value from (DatabaseID INTEGER, AdditionalSQLQuery STRING)
SQLITE QUERY RECORDS COUNT - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE QUERY COLUMNS COUNT - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE QUERY COLUMN NAME$ - a function returning a STRING value from (DatabaseID INTEGER, ColumnNumber INTEGER)
SQLITE FIRST RECORD ROW - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE PREVIOUS RECORD ROW - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE NEXT RECORD ROW - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE GOTO RECORD ROW - a function returning an INTEGER value from (DatabaseID INTEGER, RowToGoTo INTEGER)
SQLITE LAST RECORD ROW - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE RECORD COLUMN NAME$ - a function returning a STRING value from (DatabaseID INTEGER, ColumnNumber INTEGER)
SQLITE RECORD COLUMN COUNT - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE RECORD ROW COUNT - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE RECORD CURRENT ROW STRING$ - a function returning a STRING value from (DatabaseID INTEGER, ColumnNumber INTEGER)
SQLITE RECORD CURRENT ROW FIELDSTRING$ - a function returning a STRING value from (DatabaseID INTEGER,[ DelimeterForFieldString STRING])
SQLITE RECORD CURRENT ROW INTEGER - a function returning an INTEGER value from (DatabaseID INTEGER, ColumnNumber INTEGER)
SQLITE RECORD CURRENT ROW FLOAT - a function returning a FLOAT value from (DatabaseID INTEGER, ColumnNumber INTEGER)
SQLITE FINISH SQL QUERY - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE UPDATING SQL STATEMENT - a function returning an INTEGER value from (DatabaseID INTEGER, UpdatingSQLStatement STRING)
SQLITE BEGIN TRANSACTION - a function returning an INTEGER value from <*no parameters*>
SQLITE ADD TO TRANSACTION - a function returning an INTEGER value from (SQLStatementToAddToTransaction STRING)
SQLITE GET CURRENT TRANSACTION STRING$ - a function returning a STRING value from <*no parameters*>
SQLITE COMMIT TRANSACTION - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE ADD COLUMN TO TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnDefinitionAndConstraint STRING)
SQLITE INSERT ROW INTO TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, SpecifiedNamedColumns STRING, SpecifiedValuesForThoseColumns STRING)
SQLITE DELETE ROWS FROM TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, WhereConditions STRING)
SQLITE CREATE INDEX - a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING, TableName STRING, ColumnName STRING)
SQLITE GET INDEX CREATION SQL$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, IndexName STRING)
SQLITE DROP INDEX - a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING)
SQLITE DELETE INDEX - a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING)
Total Number of Commands = 68
Please I would be really grateful if anyone who downloads would consider testing this plugin to destruction.
Aside from any bugs found, would be interested in any additional commands / functions people would be interested in seeing?
a long time dabbler with DBC and DBPro with no actual talent but lots of enthusiasm...
