SQLite support on the way

Post Reply
Message
Author
User avatar
Administrator
Site Admin
Posts: 5305
Joined: Sat Jan 05, 2008 4:21 pm

SQLite support on the way

#1 Post by Administrator » Sun Feb 14, 2016 4:21 pm

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
table.png (4.7 KiB) Viewed 14096 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.

User avatar
lisa
Posts: 8332
Joined: Tue Nov 09, 2010 11:46 pm
Location: Australia

Re: SQLite support on the way

#2 Post by lisa » Mon Feb 15, 2016 5:12 am

Sounds very interesting, the download is missing libsqlite3.dll though
Remember no matter you do in life to always have a little fun while you are at it ;)

wiki here http://www.solarstrike.net/wiki/index.php?title=Manual

User avatar
Administrator
Site Admin
Posts: 5305
Joined: Sat Jan 05, 2008 4:21 pm

Re: SQLite support on the way

#3 Post by Administrator » Mon Feb 15, 2016 11:54 am

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.

User avatar
lisa
Posts: 8332
Joined: Tue Nov 09, 2010 11:46 pm
Location: Australia

Re: SQLite support on the way

#4 Post by lisa » Mon Feb 15, 2016 6:56 pm

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.
Remember no matter you do in life to always have a little fun while you are at it ;)

wiki here http://www.solarstrike.net/wiki/index.php?title=Manual

User avatar
lisa
Posts: 8332
Joined: Tue Nov 09, 2010 11:46 pm
Location: Australia

Re: SQLite support on the way

#5 Post by lisa » Mon Feb 15, 2016 7:29 pm

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.
Attachments
wurmdb file.zip
(17.27 KiB) Downloaded 388 times
sqlite.lua
(365 Bytes) Downloaded 396 times
Remember no matter you do in life to always have a little fun while you are at it ;)

wiki here http://www.solarstrike.net/wiki/index.php?title=Manual

User avatar
beanybabe
Posts: 647
Joined: Wed Mar 06, 2013 1:27 am

Re: SQLite support on the way

#6 Post by beanybabe » Tue Feb 16, 2016 10:46 am

nice the database should be easier to work with than those text files.

User avatar
Administrator
Site Admin
Posts: 5305
Joined: Sat Jan 05, 2008 4:21 pm

Re: SQLite support on the way

#7 Post by Administrator » Tue Feb 16, 2016 7:21 pm

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.

User avatar
BlubBlab
Posts: 948
Joined: Fri Nov 30, 2012 11:33 pm
Location: My little Pony cafe

Re: SQLite support on the way

#8 Post by BlubBlab » Wed Feb 17, 2016 2:50 pm

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.
Jack-of-all-trades, but master-of-only of a few :D

My Reps:
https://github.com/BlubBlab/Micromacro-with-OpenCV (My version of MM2 with OpenCV and for MS Visual Studio)
https://github.com/BlubBlab/rom-bot (rombot with no stop WP and advanced human emulation mode and some other extensions)
https://github.com/BlubBlab/Micromacro-2-Bot-Framework ( A work in progress )
My Tools : viewtopic.php?f=10&t=6226

User avatar
Administrator
Site Admin
Posts: 5305
Joined: Sat Jan 05, 2008 4:21 pm

Re: SQLite support on the way

#9 Post by Administrator » Wed Feb 17, 2016 3:04 pm

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.

User avatar
BlubBlab
Posts: 948
Joined: Fri Nov 30, 2012 11:33 pm
Location: My little Pony cafe

Re: SQLite support on the way

#10 Post by BlubBlab » Thu Feb 18, 2016 8:23 am

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
Jack-of-all-trades, but master-of-only of a few :D

My Reps:
https://github.com/BlubBlab/Micromacro-with-OpenCV (My version of MM2 with OpenCV and for MS Visual Studio)
https://github.com/BlubBlab/rom-bot (rombot with no stop WP and advanced human emulation mode and some other extensions)
https://github.com/BlubBlab/Micromacro-2-Bot-Framework ( A work in progress )
My Tools : viewtopic.php?f=10&t=6226

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest