Page 1 of 1
SQLite support on the way
Posted: Sun Feb 14, 2016 4:21 pm
by Administrator
Going to keep this very brief for the time being. I've been
working on a chunk of code to integrate SQLite databases into MicroMacro. The results are fairly pleasing so far.
First I created a database with table "test" which has fields: 'id' (integer size 11, auto-increment, not-NULL, primary key), and 'name' (type text, not-NULL).
I inserted a few test rows to work with:
- table.png (4.7 KiB) Viewed 14387 times
Then I wrote up some simple code to test with:
Code: Select all
db = sqlite.open("test.db");
results = sqlite.execute(db, "SELECT * from `test`");
print("Results:");
print("\tID", "Name");
for id,result in pairs(results) do
printf("\t%s\t%s\n", result.id, result.name);
end
sqlite.close(db);
This should grab all ("SELECT *") fields from all rows table "test". The output is:
Code: Select all
Running './scripts/test/sqlite.lua'
Results:
ID Name
1 row1
2 row2
3 row3
Great! It works! And what if we wanted to just select a specific field (name) on a specific row ("WHERE `id` = 2")?
Lets just use table.print() for the results this time, just to clean things up.
Code: Select all
results = sqlite.execute(db, "SELECT `name` from `test` WHERE `id` = 2");
table.print(results);
Output:
Code: Select all
Running './scripts/test/sqlite.lua'
1: table: 0x027F7228
name: row2
So, that's a big success. With this code, we now have access to relational databases! No more need for XML files or the like, and no more crappy look-up code. Very exciting stuff.
If you want to try it out, it is available as of
version 1.93 from the main download page.
Re: SQLite support on the way
Posted: Mon Feb 15, 2016 5:12 am
by lisa
Sounds very interesting, the download is missing libsqlite3.dll though
Re: SQLite support on the way
Posted: Mon Feb 15, 2016 11:54 am
by Administrator
Sorry about that. Looks like for some reason the x64 wants libsqlite3.dll and x86 wants sqlite3.dll. I've fixed the 64-bit build and it's up on the download page.
Re: SQLite support on the way
Posted: Mon Feb 15, 2016 6:56 pm
by lisa
Yup that did the trick, it's funny but I have been playing a game called Wurm Unlimited and when you have your own server it uses .db files to store all the data, so this is kind of interesting timing for me =)
I currently use SQLite DB browser for all my viewing and editing of the db files.
Re: SQLite support on the way
Posted: Mon Feb 15, 2016 7:29 pm
by lisa
Ok I'll add a db file from the game and also a little script that just does a print, so if anyone wants to try it that doesn't have any db files handy they can.
Need to unzip the db file as they can't be posted on forum, just put both files in the scripts folder and type SQLite on MM to start it.
Re: SQLite support on the way
Posted: Tue Feb 16, 2016 10:46 am
by beanybabe
nice the database should be easier to work with than those text files.
Re: SQLite support on the way
Posted: Tue Feb 16, 2016 7:21 pm
by Administrator
I'm also working on a small library to help build queries (because writing raw SQL everywhere can be a huge pain, messy, and error/security-issue prone). There's a ton of work to go yet, but here's what I've got so far:
Code: Select all
local update = QueryBuilder()
:update('people')
:set('name', 'Jane Doe')
:where('id', 1)
:get();
print(update);
local select = QueryBuilder()
:select('*')
:from('people')
:where('id', '>', 1)
:where('name', 'Jane Doe')
:limit(5)
:get();
print(select);
Output:
UPDATE `people` SET `age` = '25',`name` = 'Jane Doe' WHERE `id` = '1'
SELECT `*` FROM `people` WHERE `id` > '1' AND `name` = 'Jane Doe' LIMIT 5
Obviously those are just some silly examples, but they get the point across.
Re: SQLite support on the way
Posted: Wed Feb 17, 2016 2:50 pm
by BlubBlab
Well I would use PDOs anyway , about the usefulness I think for the database it is a good ideas but not so great for other stuff so long there is no tool to edit manually the stuff.
Other Ideas
- From my own work I could suggest to make MM2 minimizable to an trayicon I wrote a HP watchman in c# which do that
- Well I started with more regex support then Lua has.
-
https://en.wikipedia.org/wiki/List_of_s ... n_software was also an idea.
- take a look what AutoIt can and we can't easy stealing of ideas.
Re: SQLite support on the way
Posted: Wed Feb 17, 2016 3:04 pm
by Administrator
Well I would use PDOs anyway
Yup. But before we can get that far, we need a foundation to build on.
BlubBlab wrote:I think for the database it is a good ideas but not so great for other stuff so long there is no tool to edit manually the stuff.
Yeah, but I would strongely recommend using a 3rd party tool rather than MicroMacro for browsing, querying, or modifying the database. I use Navicat Pro for work to access MySQL databases, and it supports SQLite which is a nice bonus. There's
lots of tools for opening and editing SQLite DBs.
- From my own work I could suggest to make MM2 minimizable to an trayicon I wrote a HP watchman in c# which do that
I thought that was already posssible with the showWindow() stuff.
Not entirely sure what we'd use speech recognition for. I'm open to any ideas, but I just can't see anything practical.
Re: SQLite support on the way
Posted: Thu Feb 18, 2016 8:23 am
by BlubBlab
Administrator wrote:Well I would use PDOs anyway
Yup. But before we can get that far, we need a foundation to build on.
BlubBlab wrote:I think for the database it is a good ideas but not so great for other stuff so long there is no tool to edit manually the stuff.
Yeah, but I would strongely recommend using a 3rd party tool rather than MicroMacro for browsing, querying, or modifying the database. I use Navicat Pro for work to access MySQL databases, and it supports SQLite which is a nice bonus. There's
lots of tools for opening and editing SQLite DBs.
Well because you said waypoints I thought off an extra tool to do so I recently saw some for the romBot in the depths of this forum. I general I would visualize the content. in addition to the stuff which is in it.
Administrator wrote:
- From my own work I could suggest to make MM2 minimizable to an trayicon I wrote a HP watchman in c# which do that
I thought that was already posssible with the showWindow() stuff.
I searched the code for something with that name I haven't found it but it looks like you wanted to write it back at some point because you switched to native programming. It is in c++ more complicate then in c# but you can loook up it here I think
http://www.dreamincode.net/forums/topic ... ay-with-c/
Administrator wrote:
Not entirely sure what we'd use speech recognition for. I'm open to any ideas, but I just can't see anything practical.
i know that there are some problem with it especially mapping voice commands with the action inside a script I admit it is for lazy people