Sign in to follow this  
Followers 0
AliceDemetri

_SQLite multiple scripts.

20 posts in this topic

Hello I've done some searching around both here and on google and not found a good solid answer.

I have mutiple scripts running, 2+ writing to databases 1 reading those databases and writting to another (basically merging the 2+ databases with a little bit of coding in between). My problem is that when I have my database open in a third party viewer, autoit cannot acces it. I was under the impression that SQLite could have one writing and one reading to each database but I can't really test this with my script since there are only milliseconds and I don't see how I can time that properly. My question is will my scripts be able to access the same database by default or do I need to do something special?

I can supply some code if you want but the question is somewhat independant to my script and my code is across four or five scripts working together so I didn't really see the point in it.

Share this post


Link to post
Share on other sites



Thank you I'm reading through it now and it looks like what I need.

Share this post


Link to post
Share on other sites

When you have to merge data from several DBs, you can also ATTACH them to the main DB. Go read about attach and see if that makes your life simpler (it generally does).

Anyway come back if you have problems.


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

Thanks for the suggestion I'll be looking into it a little further today. I was going to wait till I had some coding to show for it (I hate seeing people who only ask and don't attempt), but since you already replied I'll ask while the topic is fresh. Is there a workaround to be able to share an in-memory database? Really the only reason being speed. We're building a script that grabs data puts it in a database then grabs the updated data repeat, time is a factor so I'm trying to think of any ways to speed the process up.

Share this post


Link to post
Share on other sites

I don't recommend trying to share a memory DB, since that requires multithreading and that isn't a native feature of AutoIt. You may try the WAL mode (good for concurrency) and set chache size large enough.


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

I've been working on this script for some time and read through these two topics again. I've gotten to the point where it would be benificial if all my scripts wrote to one database. Problem with that is from what you've said and I've read elsewhere theres no way to have multiple writes. I was wondering if you had suggestions for the best work around? Alternatively I can go back to what I said earlier of having several databases that get attached to a main database however I was wondering if you had a workaround that could skip that step somehow. Also if I use one of the other UDFs out there and connect to a remote database what limitations does that have as far as writers? (eventually I'd like it to be remote so multiple computers can work on the same database.

Share this post


Link to post
Share on other sites

Contrary to what client-server SQL engines trick you to believe, there is no such possibility for a DB engine to allow for multiple writers simultaneously: this would violate the mere fundations of the relational model. High-end (i.e. client-server architectures) engines must serialize writes and while a write transaction is taking place, subsequent writes are "merged" after first one termination. Else operations like simple money transfer could result in real money loss or virtual money created, a no-no.

SQLite supports a decent amount of concurrency and, provided you take simple appropriate measures, you never have to worry about write concurrency.

You seem to believe you're going to be CPU- or disk-bound in practice, but is that based on real-world benchmarks or just out-of-the-blue fear?

One issue with SQLite (here I mean raw DLL) is that it relies on available file-locking protocols to insure DB integrity. Experience shows that all available protocols for all available OSes are buggy as hell in this precise area, and SQLite can do nothing against that.

I'm working on a reliable solution to bypass this limitation but don't hole your breath and if you need a client-server engine, you're best bet is MySQL (with the good and dark sides) or, better, Postgres.

It's always difficult to give sound avdice without a clear idea of what the context, constraints and specifications are.


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

#9 ·  Posted (edited)

I understand that fully the issue is that the script is 10+ Processes that communicate with each other and most of them are not to a point where they are working fully. I'm pretty much working with bare scripts trying to get this to work (writing a new script with every bit of information you give me). That being said I am beyond grateful for your help so let me outline with a little more detail.

What I'm working on is a bitcoin trader. Speed is my priority with this so I have split it into a multiple processes:

Parent: Prepare environment (databases, a few memory locations, some scripts are generated here, ect.)

Tickers: Generated by parent, there will be multiple scripts pulling prices from multiple APIs which the end goal is to have all that stored in one database (this is where my issue lays)

Logger: Self explainitory

Debugger:: Self explanitory

Predicter: Read current tickers and log and compare scenarios to try and predict future prices

Analyzer: Decide on which trades to commit to

Trader: Preform actual trades

There are a few others but hopefully gives you a better idea what i'm trying to do.

I'd rather not give my whole source just because of what it is that I'm doing but if theres a spacific script you want to look at I'd be happy to write up an example that shows whats going on so you can see whats happening.

Edit: Not fully awake, fixed typos.

Edited by AliceDemetri

Share this post


Link to post
Share on other sites

You can post example script privately and I'll do my best to help you.


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

That would be very helpful. I just started a re-write of it if I'll contact you through PM when I have some examples (may be a bit, I only do this as a hobbie and haven't had much time lately)

Share this post


Link to post
Share on other sites

Ah, bitcoin mining? :)
it sounds interesting from a thecnical point of view
You could share what you came out with.
Such technics could be used also to achieve some other kind of purposes where collaborative camputing would be required.


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

Not bitcoin mining. Rather bitcoin trading. Day trading bot with a few personal ideas to boost it along.

Similar to my understanding of a High Frequency Stock trading bot, only built for bitcoins.

I'd be willing to share parts of it however I would like to keep some of the code to myself as it is a financial software afterall. As I said it will be some time before I have much ready but I'm willing to document my ideas and supply bits of code as they evolve. This week looks like a pretty slow week so I should have some time to work on it. I'll post back when I do.

I'd also be willing to share the more secret parts of the source through PM, I don't want to restrict my project too much, I just don't want it posted openenly on a forum that anyone could find. Until it's done I don't know what implications other people knowing the source could have. It may end up in the end that it doesn't matter but I want to make the decision once it's done.

There are already examples out there and some open source bots if you do some googling. (I haven't looked at them but my friend thats helping me with this project found a few of them).

Edited by AliceDemetri

Share this post


Link to post
Share on other sites

@AliceDemetri

You should be able to hook into the file from more than one process.

If the file handle is locked you can unlock it with code from

You should then be able to write to the file from as many scripts as you want. cheers (:

(though I don't have this problem myself in my coding, maybe because my code is accessing the database from a child-process)

Share this post


Link to post
Share on other sites

@JohnOne: Again I am NOT trying to create a bitcoin miner. I'm trying to create a bitcoin TRADER. something that analyses patterns and trades between currencies based on that data.

Smething like this: https://bitcointalk.org/index.php?topic=391630.0

@nuttschritt: Wouldn't that prevent the previous process from finishing it's action on the database? I use unlocker (not the autoit one) and to my understanding if you unlock it from a process you could interrupt it's action on the file.

Share this post


Link to post
Share on other sites

@JohnOne: Again I am NOT trying to create a bitcoin miner. I'm trying to create a bitcoin TRADER. something that analyses patterns and trades between currencies based on that data.

Smething like this: https://bitcointalk.org/index.php?topic=391630.0

@nuttschritt: Wouldn't that prevent the previous process from finishing it's action on the database? I use unlocker (not the autoit one) and to my understanding if you unlock it from a process you could interrupt it's action on the file.

 

No, the lock is purely to prevent the file from being modified outside the current process. All disk writes are buffered. This means even if two processes try to write to the file at the exact same time, one will occur after the other. Unlocking the file handle should be fine.

Share this post


Link to post
Share on other sites

@nuttschritt: that might just be the solution to my problems here. Thank you for your information. I'm finishing up another project today then hopefully diving into this tomorrow. I'll report back with some coding examples.

Share this post


Link to post
Share on other sites

I wrote a UDF just for your exact situation.  You can use cooperative file locking, as long as all of the readers/writers use the same method.

'?do=embed' frameborder='0' data-embedContent>>

I use this when I need to share a database or a log file over a network, with no problems.

Share this post


Link to post
Share on other sites

@willichan: Thank you so much! I'll read through it later but sounds perfect.

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