Sign in to follow this  
Followers 0
monax

Autoit UDF for databases

5 posts in this topic

Hi, I have no experience in databases at all.

I will use a free web hosting service (I dont know if I can place the link, readed the forum's rules, but better to ask first. I will link it if it's needed and allowed).

The host support mysql and sqlite. To manage mysql, the host include the phpMyAdmin tool (I have no experience in PHP neither. As far as I understand, I will not need to learn it, since the UDF will handle the database, but if it's needed, I will try to learn it). I didn't find a native tool on the server to manage the sqlite.

I'm trying to build a client for 5-10 users with autoit, the client need to read and write the database.

Looking in the forum, I found this UDF for mysql '?do=embed' frameborder='0' data-embedContent>>

I don't need to create tables or fields with the client, I did that with the tools of the server, I just need to read and rewrite the values inside the table.

My questions are:

1) The UDF SQLite included with autoit can use a file when the file is on the server?

(with mysqludf I see its designed to make a connection to a database on a server, but reading the sqlite udf functions , it looks like it open a local file)

2) If the SQLite database need to be on local machine so the udf works, it's good idea (and possible) to download the file from the server, rewrite it, and upload it again?

3) Should I use the mysqludf to avoid those problems, or simply im missunderstanding how the databases really work?

4) Any advise for a database begginer?

If something have no sense, let me know, since my english can be horrible.
THANKS :D

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

MySql and Sqlite are two separated things.

If you are using MySql on your server, a good method to perform CRUD (Create (Write), Read, Update, Delete) on the database is using a php script as a bridge between client and server.

A php script which gets the request via either GET or POST http methods (There are other methods as well), perform them on server and response the result (For example 1 for success and 0 for failure).

These are just simple ideas to give you a point to start.

Edited by D4RKON3

Share this post


Link to post
Share on other sites

First, almost no web hosting service allows direct access to a DB. This is by far too dangerous. The only way (unless you administer the server yourself) are using a web-hosted tool (like phpMyAdmin) for generic purpose or using ad-hoc PHP (or whatever) service for regular user interface. Never let SQL statements issued by users or client-side application.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

For tutorials, check this out: http://www.tutorialspoint.com/sqlite/index.htm

If you are planning on having multiple users accessing the database simultaneously (concurrency), I'd advise you to use MYSQL, although it's quite feasible to have multiple processes performing searches on a SQLITE db at the same time, but not inserts (http://sqlite.org/faq.html#q5). It's a very bad idea to have the file being shared as proposed in question 2 above. To work effectively in SQLITE with concurrency you'd need to have a thorough understanding of how transactions (commits and rollbacks) work http://www.sqlite.org/lockingv3.html.

I built a messaging queue for multiple processes sharing the same file in SQLITE. Unfortunately I can't find that old code, so no example from me today. I never used a remote file. I'm sure it's quite possible though, and I am quite interested in seeing how that can turn out.

Hope that helps.

Share this post


Link to post
Share on other sites

In practice SQLite supports a fair amount of concurrency, especially in WAL journalling mode (allowing one writer and multiple readers at the same time). Dr Hipp (SQLite author) just posted today on the mailing list about that topic:

 

The www.sqlite.org site uses SQLite, of course.  That site gets between
200K and 300K HTTP requests per day, depending on the day of the week.
About 3/4ths of the requests are for static content but the other 1/4th
(23.73% over the previous two weeks) are dynamic content which must consult
an SQLite database, usually via Fossil (http://www.fossil.org/).  The
server is leased from Linode (http:/www.linode.com/) and is a 1/16th slice
of a real machine.  The load average is typically about 0.05.  You can
visit one of the dynamically generated web pages like
http://www.sqlite.org/src/timeline to see that response-time is very fast.
The aforementioned timeline page requires about 102 differ separate queries
to render.

Add this all up and SQLite is doing about 75 queries/second every second,
all day long, on 1/16th of a server with a load average of less than 10%.

That kind of performance covers a lot of websites.

One rule of thumb is that if you and run your website from a single server,
SQLite is sufficient.  If you need multiple servers, then consider a
client/server database engine like MySQL or PostgreSQL.

 

SQLite strength on the server side is that it's so brain-dead simple to install, handle, backup, manipulate, extend and maintain. Being a serverless engine, it can't of course compete with client-server engines for the most demanding contexts. OTOH I've had to do work on a titanic 120 Tb (yes!) web-based SQLite DB seeing serious load. That required a whole lot of fine tuning but the beast runs on a single "standard" server with "only" some Tb of RAM. The Oracle solution was to use a costly custom Oracle huge server cluster (derived from Sun) and an undecent pile of $ for licences.

Indeed, due to various bugs still present in all available remote file locking protocols (whatever OS you look at), use of bare SQLite remotely is not to be considered for serious use.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0