From TXT to DB and no return... (=^‥^=)

[Mariasole]

Hello everyone!
I have just emerged from my submarine of peace (my submarine shoots just seagrass seeds and sing along with the whales hymns to the Lord) to ask your patient and kind help!

Until now I worked for my experiments on text files.
To record a data or load them on my applications, I always used, even with various difficulties, the ".txt" (I am primarily a Win7 user).
Now I understand that the text files have great limits, also because they slow down the speed of my experimental applications.
So I said to myself: we use the database!

Only... I do not know what it is exactly a DB and I am not a computer engineer (in fact, I use LC ).

My idea is to use a database instead of text files on Win7.... I repeat that.... because for me, initially, the DB must be "simple" how to save or load from a text file.

For this experiment I will use Win 7 and LC 6.7.7. But I do not know where to start, nor know how to install (and where!) a database, or how to create it, or how to communicate with the LC database.

When I have understood the mechanism, I try to repeat the operation on the server!


Thank you all!
Mariasole
(=^‥^=)

[AxWald]

Hi,

Only... I do not know what it is exactly a DB and I am not a computer engineer (in fact, I use LC :P ).

Oh oh. This smells like a lot of fun ;-)

Well, databases are a quite simple way to store data, actually similar to store data in text files. Some (older) databases in fact store data in text files!
The difference is that between you & your data resides a database engine, a program that is designed to help you working with your data in a proper way.

All this is no rocket science, provided you learn a few simple things:

1. A basic idea how to structure your data in the database. This is called normalization, and you should understand the idea of the "first normal form" at least.
   _
2. You will need to talk to your database engine, and this requires to learn the language. The one best suited for LC is SQL, and again, you don't need to learn all and anything - it's sufficient to understand the syntax for some basic actions.
   _
3. You'll do a lot of concatenations in LC:

   Code: Select all

         put "SELECT (([KHK_Price]/" & MyModVal & ")*100) " & \
               "FROM t_ArtPrice " & \
               "WHERE (((t_ArtPrice.KHK_Num)='" & MyArtNu & "') AND " & \
               "((t_ArtPrice.KHK_PList)=" & MyBasePL & "));" into MyStr
         put revDataFromQuery(",",,MyDB, MyStr) into MyResult

   Carefully crafting (and testing) such strings is the daily bread of database work. Else you'll hunt the one missing single quote all day long ;-)

So, after you have accepted the prerequisites above, what do you need else?

Not much:

1. LC comes with a SQLite driver already, and this is the db engine you'll want to try first: it's lightweight, fast, powerful, and Open Source.
   _
2. You'll want another tool to handle your db with a GUI, I recommend SQLite Manager, a Mozilla AddOn (for Firefox, Thunderbird, Seamonkey, Komodo etc.).
   Using this it's easy to create and manage your database, look at your data, make backups etc. This way you reduce your work in LC to the actual data selection & manipulation (that is the less difficult task).

That's all to get you started. :)

A few more thoughts:

There's AddOns for LC to make it more easy for you. I don't use them, so I don't know them, so I don't want to name one over another - but they are easy to find. Search for "SQL" in this forum ;-)

Should you have access to any version of MS Access, it's an awesome tool to play with & learn the database basics. Its query design view also makes it very easy to get the skeleton for the more complicated queries, and it connects to nearly all database engines out there.
Just remember - it's no database engine itself, it's a sophisticated front end (even if it comes with some database engines, and is very often cruelly abused as such ...)!

MySQL is by far overrated.

Another great tool (for MySQL, MS SQL or PostgreSQL) is HeidiSQL.

That's all. Now you should be well equipped for your journey.
(And don't forget, you will be helped here on your way!) ;-)

Have fun!

[Klaus]

Hola Maria,

in first place you should take a look at SQL!
This page got me started: http://www.w3schools.com/sql/default.asp


Best

Klaus

[FourthWorld]

How much data are you working with, Mariasole? It would be helpful to know:
- Total size of your current text file
- Approximate number of records (lines) in the file
- Approximate number of fields in each record
- Approximate average length of each record

It would also be useful to know the sorts of tasks you'll be doing with it. For example, do you need to search across all of the records frequently? When you do search, is it commonly by values in multiple fields or more often just a single field? How frequently do you write data relative to reading it? Is this for multiple concurrent users, or just you?

SQL is a great solution for many things, but far from the only one. Sometimes a non-SQL (what others call "NoSQL") solution can be a good choice, like MongoDB or CouchDB. And then there's LC-native online DBs like Canella's LiveCloud. And then there are graph DBs like Neo4J, though it's best for rather specialized use cases. And once in a while, though it may sound like heresy to some, a plain ol' text file is just fine. For some projects lately I've become enamored of storing collections of encoded array files with a subset of info as a materialized view for searching (rather Mongo-like, but works on a shared host nimbly and portably without a need for a dedicated DB server). All data storage options have strengths and weaknesses. If any one of them was a magic pony that did everything best there wouldn't be new ones created every month.

Knowing more about your data and how you want to work with it will help find an optimal solution. It may well be MySQL given its strong mix of features, licensing, and available tutorials. But sometimes other options can get a project out the door quite well using simpler means. Learning normalization is useful for a lot of problem-solving, but right now you also have your hands full learning server administration so maybe you don't need to take on learning everything at one time, and can evolve the system you're working on in stages.

[Mariasole]

I tried to study a bit to get an idea of ​​what is a database.

First, I want to thank AxWald for his valuable suggestions.

Even Klaus link gave me a hand, thanks!

And then he came Richard and put me in front of several tempting choices!

Including the one to throw in the Sea Sargassum the sql database to choose nosql, ie norel, ie the "non-relational databases" (or almost).

Also on the subject of nosql database I tried to study a bit and MongoDB (or CouchDB) seems to me the right solution!

But it is an instinctive choice since I do not know where to start!


In fact, you're right Richard, you need to explain better what I want to do.

It's actually a very experimental project to learn the potential to have under the seat of the submarine a database , and to explore the topos of children's literature . I speak of fables, legends, and so on.

I would like to begin uploading a thousand books (some 200, 300 pages), pull for each of these phrases that I think are important (or that are important to other scholars), also rank them, discover the concepts that are shared between them (through the processing steps and intermediate classification) and see what comes out!

Once the system has done this procedure, will be "extracted" the good and bad characters.

Between good and bad , for example, you will see if those living on earth or living in the sky are in general more good or bad than those that live in the sea, or if the bad guys have common eating habits (often eat snails, cockroaches , fire, children, etc.).

The problem of this design is that, besides having a large amount of data , it is still fully open (mentally ) and can not be drawn perfectly from now .

I need great flexibility on the database, as my research looks more like a jam session than a score to be performed.

I do not know and I do not want to know now where I will bring my research, I would like to "play" the database can give me the inspiration for other notes, other melodies, other progressions!

Here, for example, because I like to program LC, because I can add, remove, move, always with a human readable code and, in short, musical!

Richard, I know you understand me!

That's why I think the nosql solution could be good!

But where to start? MongoDB is better or CouchDB? And as these databases are "linking" to LC? Is there any sample-example stack?


Grazie davvero ragazzi!

Mariasole
(=^‥^=)


....'cos it's only knock and knowall, but I like it.......
....'cos it's only knock and knowall, but I like it.......
....'cos it's only knock and knowall, but I like it.......

It - Genesis

[FourthWorld]

What format are those stories/books in? Plain text, Word, RTF, ePub, something else, or a mix of formats?

Many options for solving the problem, but let's start with the source and work out from there.

Back inside
This chamber of so many doors;
I've nowhere to hide.
I'd give you all of my dreams, if you'd help me,
Find a door
That doesn't lead me back again
Take me away.
- Chamber of 32 doors