_SQLite multiple scripts.

[AliceDemetri]

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.

[JohnOne]

I asked a similar question not so long ago >here.

Got some great advice off a resident expert.

[AliceDemetri]

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

[jchd]

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.

[AliceDemetri]

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.

[jchd]

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.

[AliceDemetri]

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.

[jchd]

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.

[AliceDemetri]

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 February 7, 2014 by AliceDemetri

[jchd]

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

[AliceDemetri]

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)

[Gianni]

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.

[AliceDemetri]

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 February 10, 2014 by AliceDemetri

[JohnOne]

Have you looked at these

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

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

[nullschritt]

@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)

[AliceDemetri]

@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.

[nullschritt]

@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.

[AliceDemetri]

@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.

[willichan]

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.

[AliceDemetri]

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