Jump to content
Sign in to follow this  
ZipleR

Ideas for storing large amount of device data

Recommended Posts

ZipleR

Hello,

I have been working on a script to gather a bunch of network device data and storing it in an INI file. I have it working for what I was trying to do, however I now hit the limit of 32767 characters, so there is no way I am going to be able to store information on 10,000+ devices in an INI file.

I had, by mistake thought that the 32767 limit was per section - not the whole file.

Does anyone have any suggestions on how I could store a large number of records in a human readable file?

A SQLLite database may be an option, but I do not have much SQL exposure, so building queries to extract the data I am looking for would be challenging.

Here is an example of a section that I am storing...

[ExampleList]

ExampleDevice=DeviceName;IPAddress;Maker;Model;Serial;FWVersion;Driver;Modified

I create Single array to handle this by doing this

$Array = BuildArray()

Func BuildArray()

$Local = IniReadSection($ConfigPath,"PrinterList")

$x = $Local[0][0] +1

ReDim $Local[$x][9]

For $x = 1 to $Local[0][0]

$Temp = StringSplit($Local[$x][1],";")

$Local[$x][1] = $Temp[1]

$Local[$x][2] = $Temp[2]

$Local[$x][3] = $Temp[3]

$Local[$x][4] = $Temp[4]

$Local[$x][5] = $Temp[5]

$Local[$x][6] = $Temp[6]

$Local[$x][7] = $Temp[7]

$Local[$x][8] = $Temp[8]

Next

Return($Local)

EndFunc

....

Like I said, the above works just fine, however an INI file with the built in INI functions are just not going to cut it as I can only store about 300 devices worth before reaching the character limit.

Any ideas or suggestions would be appreciated.

Thanks!

Share this post


Link to post
Share on other sites
jchd

SQLite is definitely the way to go.

Here are some advices:

Don't code anything [more] yet.

Download SQLite Expert freeware version: this is the best 3rd-party SQLite manager ever. The free version is enough for most purposes.

Download the sample DB I just made for you. Of course it won't fit your requirements but take it as a toy and see how it works.

Ask yourself what requirements you have and what are the relationships between the various entities you have to manage. E.g. will you have multiple same devices differing only by IP and serial?

Ask yourself which queries you're going to need and imagine all what you could possibly need over time. Write this down in plain english. This will be the basis for a more meaningful DB design.

Find some SQL tutorial to get the basics of the query language.

There are 3 tables:

Types store the type of devices. Use the Data tab in Expert to see how you can fit it to your needs.

Makers store the vendor names.

Devices store data for specific devices. Note that TypeID and MakerID are foreign keys to the above tables.

Devices.db3.txt

(Remove the .txt extension required for uploading it here)


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
ZipleR

jchd: Thank you for the example and recommendation.

I will download SQLite Expert right now and take a look at the database. I will then see if I can get a AutoItScript to connect to it and add/return what I am looking for.

Just curious - If I have 8 Processes connecting to the database retrieving data once every 5 minutes, and potentially 5 seperate processes randomly adding data (mostly adds, with rare updates and even less removals) do you see a problem with SQL Lite?

Like I was saying - I had it working with INI files, and now SQLLite is going to add a different dynamic that I wasn't planning for. ;)

I will let you know when I get something working!

Thanks again!

Share this post


Link to post
Share on other sites
kylomas

ZipleR,

SQLite Expert is worth it's weight in gold. I'm not a DB guy. Using SQLite Expert I can see hopw al;l the peices fit together and model SQL statements "on the fly". I liked it so much that I ordered the professional version at jchd's recommendation. It was money well spent (thanks jchd!).

jchd is the expert here but I would think that you could serialize access at the DB level with _winapi_fileinuse, if you can stand the momentary lockout for the other processes.

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
jchd

Again, don't rush to coding. Planning and designing things needs time, which can be a complete waste if aimed at the wrong target.

You were talking of .INI data organization so I guessed (maybe incorrectly) you wouldn't be sharing the data container over a network. SQLite is not a client/server engine yet it offers decent concurrency among local processes or threads.

There are several steps to improve concurrency and efficiency when multiple processes use a DB concurrently. We can talk about such details later.

What you should accept nonetheless is that you can have at best ONE writer AND multiple readers at the same time by using the WAL feature (included in recent SQLite releases), but there is no way to allow for multiple simultaneous writers. Relax, this is fairly common in DB engines and most client/server engines silently serialize writes to a given DB. Also, compared to a .INI design, you're going to gain ACID properties and some concurrency for almost free.


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
jchd

@kylomas,

All flattering apart, yes, Expert is a must have partner.

OTOH, no, don't invent a fragile serialization for accessing the DB. The plain old "delete" journal mode already allow for ONE writer OR many readers, but the WAL mode goes beyond that limitation and allows ONE writer AND many readers, all simultaneously (whatever that actually means). Messing with the DB file or files by your own isn't going to make things better nor simpler.


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
DicatoroftheUSA

Just curious - If I have 8 Processes connecting to the database retrieving data once every 5 minutes, and potentially 5 seperate processes randomly adding data (mostly adds, with rare updates and even less removals) do you see a problem with SQL Lite?

While sqlite does not have a client/server engine, you can use autoit to talk through tcp/ip for that purpose. Have one instance of autoit that writes to sqlite, then the other processes, talk to the autoit server. Another method, is have the processes dump temp files, then a server that reads and deletes the temp files and drops them into the database. Of course that would cause unnecessary hard drive time.

Also, as far as readwrite permission and lockout issues, sqlite will be at least as functional as an INI file in that regards.

I also disagree with the other posters, rush into coding. Make as many mistakes as possible in the shortest amount of time. Just remember to learn from them. If all else fails, read the instructions.

Edited by DicatoroftheUSA

Share this post


Link to post
Share on other sites
kylomas

Understood, thanks jchd,

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
kylomas

@DictatoroftheUSA,

Also, as far as readwrite permission and lockout issues, sqlite will be just as functional as an INI file in that regards

Of course, but I would bet that over time the ini file would be corrupted.

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
jchd

Even if I never recommend using bare SQLite over a LAN, I've used several processes spread over a few desktops concurrently using the same DB but never experienced any kind of issue. In my case the practical concurrency was fairly low at any given time and I took only simple steps (immediate transactions and very large timeout). Contexts where, say, 20 PCs regularly launch concurrent read/write transactions at high rate could lead to DB corruption.


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
ZipleR

Ok... Finally.... I have been working in the SQLite GUI thing since last night trying to get a database to work with 1 Main table, and 7 linked tables,... I could not get it working no matter what I tried. I even tried to build just a simple 2 table database and link them together with no succeess.... No matter what I tried I would get an error indicating "foreign key mismatch"

After just about giving up I took the DDL code from the 3 table database that jchd created and used it as a template to manually create the database table structure that I want and finally got it working!

Now the next step is to get a connection from Autoit to the database and sucessfully query and store something into it... Once I get that working I hope I wont have much of a problem updating my existing functions to store into the database instead of the INI files.

Thanks for everyones help and suggestions!

I will let you know when I get past the next step.

Edited by ZipleR

Share this post


Link to post
Share on other sites
jchd

One non-obvious thing you need to perform to use foreign keys. By default, current (and older) release of the SQLite library simply parse foreign key statements but don't enforce them unless you issue a pragma for every new DB connection (including :memory: DBs).

So insert the following right after your $hDB = _SQLite_Open(...) statement(s):

_SQLite_Exec($hDB, "pragma foreign_keys=ON;")

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  

×