Hi All,
- Please can you test this plugin to destruction and provide feedback -
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 but I set out below some of those here fore your ease of reference:-
http://www.w3schools.com/sql/sql_syntax.asp (this site is ruddy fantastic and you can learn most of what you need to know about SQL in general within say 30 mins)
http://www.sqlite.org/about.html (this is the SQLITE site)
SQLite (freeware) Database Manager GUI I found on the web:-
http://osenxpsuite.net/?xp=3 (see bottom of web page) (can import MS Access databases/tables!) download link:-
http://link.osenxpsuite.net/?uid=homepage&id=sqlite2009pro.zip
This is a new version (of the above rougher older version) of a SQLITE3 plugin with a simplified command set to start with. I've now added to that original command set with a huge number of new commands.
SQLITE3 is damned fast. With the SQL language (which is easy to use and understand btw) it is very flexible.
In short, I cannot understand why anyone would NOT use SQLITE (which has no dependencies) to store their game data...
The plugin as it stands still very much needs testing and honing. And I really would like people to feedback commands they would like to see.
[edit]
Current Version 0.9i (29/03/2014)
5 more functions to increase functionality (126 functions in total now).
Easier functions to change string, integer or float cell within a table.
Function to show number of rows affected by an updating SQL statement.
Current Version 0.9i (29/03/2014)
5 more functions to increase functionality (126 functions in total now).
Easier functions to change string, integer or float cell within a table.
Function to show number of rows affected by an updating SQL statement.
Version 0.9h (17/03/2014)
Functions reading integers (and possibly strings) from SQL recordsets were skewed. Trying to fix.
Version 0.9g (15/03/2014)
String return functions in SQLITE plugin now FIXED!
I therefore award myself - much [ic:beer] and go to bed, early.
Some name changes for functions returning strings - beware!
Also, nb SQLITE Array functions have columns starting at 1, not 0 as with other non-Array functions...
Version 0.9f (10/03/2014)
Trying to fix recurring faults with functions returning strings. Fingers crossed!
Version 0.9e (09/03/2014)
A failed version really - trying to fix recurring faults with functions returning strings...
Version 0.9d (17/02/2014)
Well, SQLITE GET TABLE NAME$ and SQLITE TABLE COUNT better work now!
Also, I've added 18 new functions/commands allowing you to create a unique SQLITE 'Array' from either a Table OR a SQL query Recordset, interrogate that Array, create a new Table from it or even export it as a simple CSV file. Thought that would come in handy. ;)
Version 0.9c (14/02/2014)
A little work and further improvement (hopefully) on SQLITE GET TABLE NAME$ and SQLITE TABLE COUNT...
Version 0.9b (09/02/2014)
A little work and improvement (hopefully) on SQLITE GET TABLE NAME$ and SQLITE TABLE COUNT...
Version 0.9a (09/02/2014)
Think I've sorted the the SQLITE MAKE TABLE FROM SQL QUERY RECORDS problem and I think I've sorted (but haven't tested) the SQLITE TABLE AS PRINTSTRING$ and the RECORDSET equivalent.
Version 0.9 (08/02/2014)
Thanks to Lukas W spotted (and hopefully fixed) a school boy error with functions/commands with optional parameters/flags...
Version 0.8 (14/12/2013)
Some tweaking to import and export csv commands
Additional command to list all tables in a Database as a fieldstring.
Version 0.7 (07/12/2013) -
I've added yet a further 7 new commands/functions over v0.6.
Even though you cannot normally use SQLITE3 to drop/delete columns or insert columns, whilst retaining types/definitions, contraints etc, I've added in that functionality. By tinkering around with writable schema pragma and sqlite_master, you have functions which allow you to get/reset/change the name, type/definition, primary key status, and foreign key attributes of any column.
For v0.7 I have added support for import from and export to csv with tables. I have also allowed for export/import retaining all table attributes including column types, definitions, constraints, foreign keys, indices, triggers and views. Import/Export commands will not work with tables containing 'blobs'. I am still working on functions/commands to support 'blobs' - I think the best I will be able to do is provide support for files - so you could store a graphics file or sound file as a blob column with a blob size column then as an integer, something like that.
Anyway, the import/export commands are still thoroughly untested.
I've also added some more Index/indices functionality.
I've also added a search within recordset type command.
I've also added some 'printstring' commands so you can generate a multi line string with your recordset results (for particular rows if you want) or your table - again, thoroughly untested...
With functions, Rows start at 1 in commands but columns start at 0 - except with SQLITE Array commands where columns start at 1.
A column count of 7 would indicate there were columns 0,1,2,3,4,5,6.
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 CURRENT DATABASE - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE GET LAST ERROR$ - a function returning a STRING value from <*no parameters*>
SQLITE SET FOREIGN KEY CONSTRAINTS ON - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE SET FOREIGN KEY CONSTRAINTS OFF - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE ARE FOREIGN KEY CONSTRAINTS ON - a function returning an INTEGER value from (DatabaseID INTEGER)
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 TABLES FIELDSTRING$ - a function returning a STRING 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 COPY TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, NewTableName STRING)
SQLITE EXPORT TABLE AS CSV - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, CSVFileName STRING,[ DetailFlag INTEGER])
SQLITE IMPORT TABLE FROM CSV - a function returning an INTEGER value from (DatabaseID INTEGER, CSVFileName STRING, NewTableName STRING,[ WithForeignKeysFlag INTEGER])
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 DROP NAMED COLUMN - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE DELETE NAMED COLUMN - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE RENAME COLUMN IN TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, NewColumnName STRING)
SQLITE INSERT COLUMN INTO TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, BeforeOrAfter INTEGER, InsertionIndex INTEGER, ColumnType STRING, PrimaryKeyFlag INTEGER, NotNullFlag INTEGER, DefaultValue STRING)
SQLITE INSERT COLUMN AT TABLE START - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, ColumnType STRING, PrimaryKeyFlag INTEGER, NotNullFlag INTEGER, DefaultValue STRING)
SQLITE INSERT COLUMN AT TABLE END - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, ColumnType STRING, PrimaryKeyFlag INTEGER, NotNullFlag INTEGER, DefaultValue STRING)
SQLITE ADD COLUMN TO TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnDefinitionAndConstraint 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 PRIMARY KEY COUNT - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE TABLE FOREIGN KEY COUNT - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE SET NAMED COLUMN TYPE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, NewType STRING)
SQLITE IS NAMED COLUMN PRIMARY KEY - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE SET NAMED COLUMN PRIMARY KEY - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, OnOff INTEGER)
SQLITE GET NAMED COLUMN NOTNULL - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE SET NAMED COLUMN NOTNULL - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, OnOff INTEGER)
SQLITE GET NAMED COLUMN DEFAULT VALUE EXIST - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE GET NAMED COLUMN DEFAULT VALUE$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE SET NAMED COLUMN DEFAULT VALUE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, NewDefaultValue STRING)
SQLITE DOES NAMED COLUMN HAVE FOREIGN KEY - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE GET NAMED COLUMN FK TABLE$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE GET NAMED COLUMN FK COLUMN$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE REMOVE FK FROM NAMED COLUMN - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING)
SQLITE SET FK ON NAMED COLUMN - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, ForeignTableName STRING, ForeignTableColumnName STRING)
SQLITE TABLE AS PRINTSTRING$ - a function returning a STRING value from (DatabaseID INTEGER, TableName STRING, RightSetColumnLength INTEGER,[ FirstRow INTEGER,[ EndRow INTEGER])
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 CHANGE TABLE CELL STRING - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, OriginalCellString STRING, NewCellString STRING,[ WhereAnotherColumnNamed STRING,[ HasAStringValueOf STRING])
SQLITE CHANGE TABLE CELL INTEGER - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, OriginalCellInteger INTEGER, NewCellInteger INTEGER,[ WhereAnotherColumnNamed STRING,[ HasAnIntegerValueOf INTEGER])
SQLITE CHANGE TABLE CELL FLOAT - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnName STRING, OriginalCellFloat FLOAT, NewCellFloat FLOAT,[ WhereAnotherColumnNamed STRING,[ HasAFloatValueOf FLOAT])
SQLITE FIND TABLE 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 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 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 MAKE TABLE FROM SQL QUERY RECORDS - a function returning an INTEGER value from (DatabaseID INTEGER, NewTableName 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 GOTO RECORD ROW WHERE - a function returning an INTEGER value from (DatabaseID INTEGER, WhereColumnNameA STRING, EqualsValueA STRING)
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 ROW STRING$ - a function returning a STRING value from (DatabaseID INTEGER, ColumnNumber INTEGER)
SQLITE RECORDSET AS PRINTSTRING$ - a function returning a STRING value from (DatabaseID INTEGER, RightSetColumnLength INTEGER,[ FirstRow INTEGER,[ EndRow INTEGER])
SQLITE RECORD CURRENT ROW FIELDSTRING$ - a function returning a STRING value from (DatabaseID INTEGER,[ DelimeterForFieldString STRING])
SQLITE RECORD ROW INTEGER - a function returning an INTEGER value from (DatabaseID INTEGER, ColumnNumber INTEGER)
SQLITE RECORD 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 ROWS AFFECTED BY UPDATE - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE SET COLUMN VALUE WHERE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING, ColumnToBeEffected STRING, NewValue STRING, WhereColumnA STRING, EqualsValueA STRING,[ AndWhereColumnB STRING,[ EqualsValueB 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 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 INDEX EXIST - a function returning an INTEGER value from (DatabaseID INTEGER, IndexName STRING, TableName STRING)
SQLITE GET INDEX OF COLUMN$ - a function returning a STRING value from (DatabaseID INTEGER, 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)
SQLITE MAKE ARRAY FROM TABLE - a function returning an INTEGER value from (DatabaseID INTEGER, TableName STRING)
SQLITE MAKE ARRAY FROM RECORDSET - a function returning an INTEGER value from (DatabaseID INTEGER)
SQLITE ARRAY EXIST - a function returning an INTEGER value from <*no parameters*>
SQLITE ARRAY COLUMN COUNT - a function returning an INTEGER value from <*no parameters*>
SQLITE ARRAY ROW COUNT - a function returning an INTEGER value from <*no parameters*>
SQLITE ARRAY GET COLUMN NAME$ - a function returning a STRING value from (ColumnNumber INTEGER)
SQLITE ARRAY GET COLUMN TYPE$ - a function returning a STRING value from (ColumnNumber INTEGER)
SQLITE ARRAY GET COLUMN NAME WITH TYPE$ - a function returning a STRING value from (ColumnNumber INTEGER)
SQLITE ARRAY COLUMN NAMES FIELDSTRING$ - a function returning a STRING value from <*no parameters*>
SQLITE ARRAY COLUMN TYPES FIELDSTRING$ - a function returning a STRING value from <*no parameters*>
SQLITE ARRAY COLUMN NAMES AND TYPES FIELDSTRING- a function returning a STRING value from <*no parameters*>
SQLITE ARRAY STRING$ - a function returning a STRING value from (ColumnNumber INTEGER, RowNumber INTEGER)
SQLITE ARRAY INTEGER - a function returning an INTEGER value from (ColumnNumber INTEGER, RowNumber INTEGER)
SQLITE ARRAY FLOAT - a function returning a FLOAT value from (ColumnNumber INTEGER, RowNumber INTEGER)
SQLITE MAKE TABLE FROM ARRAY - a function returning an INTEGER value from (DatabaseID INTEGER, NewTableName STRING,[ NumberedColumnAsPrimaryKey INTEGER,[ NamesColumnsToBeIndexed STRING])
SQLITE EXPORT SQLITE ARRAY AS CSV - a function returning an INTEGER value from (CSVFileName STRING)
SQLITE CLEAR SQLITE ARRAY - a command with <*no parameters*>
SQLITE DELETE SQLITE ARRAY - a command with <*no parameters*>
Total Number of Commands = 126
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...