What is Reddit's opinion of
SQLite?
From 3.5 billion Reddit comments
100 reviews of this app found across Reddit:
Personally, I like the disclaimer at the beginning of the SQLite source code, which is in the public domain:
** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give.
I am not a lawyer. Especially not a copyright lawyer.
In some jurisdictions, the WTFPL is just a sassy way of declaring your work as public domain. That's no problem, as long as you don't need the warranty disclaimers and such.
However - some jurisdictions do not respect the authors wish to declare their work public domain[1]. A German programmer for example would be well advised to not use your WTFPL licensed library, since it's probably a copyright violation to do so.
Another offensive license in that regard is the "unlicense".
If you commercially use WTFPL or Unlicense software in europe or ship libraries as part of your product to europe, I strongly recommend having your legal department check that.
There's a reason sqlite offers licensed versions.
Edit: Also, since the unlicense and WTFPL are legally void in these juristictions, someone living there is legally unable to contribute to the project, as the patch would remain under their copyright no matter what. So for example, I can not contribute to sqlite (yet again: IANAL).
Edit2: https://www.gesetze-im-internet.de/englisch_urhg/englisch_urhg.html - German Copyright law. See section 29.
^([1] With some exceptions. Again, IANAL)
I recommend reading this article first: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
We are going to provide a built in solution that works with recycler view but that got pushed back due to other priorities. (In terms of API, it is very tricky) We are not providing a cursor wrapper because there is some fundemental problems with it (which we will document). If interested in learning why, i recommend reading its C source code.
For now, your best option is to implement what that article suggests and then watch out for the paging artifact that will arrive soon.
The only thing I could think of that might have more than 64,000 lines is the sqlite amalgamation source code. Sqlite recommends using the "amalgamation" that is all of their source code thrown into a single file.
$ wc -l sqlite3.c 128415 sqlite3.c
But they are very clear about that pretty much everywhere in the documentation.
The library itself is extremely well written and tested. Check out their bug tracker
There are like 2 active issues and this year there has been something like 20 issues opened. For a library so heavily used by everything, that is really impressive.
As an aside: for an example of a project doing it right -- hell, they've been accused of overdoing it, look at what SQLite does:
- 100% test coverage
- no bug is fixed without a new test
- 1200 times as much test code than actual code
I don't think SQLite would ship a release with a failing test, let alone 200.
If you don't have to use MS Access, then SQLite is a good alternative and is extremely simple to get started with. There are several third party apps to view and modify the data in the SQLite database, and even a Firefox add-on that lets you do this. SQLite is also completely free (public domain).
How about the GNU Core Utilities? That's a collection of programs ranging from the very simple to the fairly simple, tried and tested, not overcomplicated, and with decent comments (in the bits i've looked at). You can read the source here:
http://git.savannah.gnu.org/cgit/coreutils.git/tree/src And download it nearby.
I've also heard good things about SQLite
SQL is a query language for relational databases. It's also used as an umbrella term for the family of databases that support it - MySQL, Postgres, Vertica, Microsoft Access, etc - there are many. A SQL database would be an ideal solution to your problem. I would recommend using SQLite for this (the other databases listed are typically accessed from servers, while SQLite is built for being used from client applications).
Whatever database you end up using, you'll need to download a java driver for that database (SQLite actually has multiple java drivers to choose from. They're listed here: http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers).
Phew... that's even tougher.
If I understand your question you're asking how to go about writing/implementing/creating/programming from scratch a database engine, similar to postgress, Oracle, etc.
A database engine is a really big project with lots of high level components (network communication, interprocess communication, SQL parsing, query planing and execution, etc etc etc).
Probably the smallest database engine I know of is http://www.sqlite.org/. It's source code is likely (perhaps, maybe, you never know) the least complicated. If you spent enough time study it that should give you some ideas of how to proceed.
If I was going to design and build a database engine I'd start with design of the SQL language variant I'd want to support. This would require considerable knowledge of parsing and compiler construction. In and of itself I'd expect this to take up to several months just to specify the language. The building of a parser who's output could be used for a query planner/execution engine would take several (maybe many many) months after that. And this would still leave all the low level nitty gritty handling of disk storage/retrieval (and how to accomplish ACID transaction in the face of power failures etc). And this wouldn't be multi-user, no networking layer, and none of the really advanced features (replication, sharding, high availability, etc).
Think of the biggest model train (eg. http://www.telegraph.co.uk/news/picturegalleries/howaboutthat/5043783/The-worlds-biggest-model-train-set.html) and what you'd have to do to make literally everything from the ground up (eg. paint, glue, nails, rails, wire, glass, not just the beautiful models that are visible). It's that complicated.
So as a long term hobby, say 10, 15, or 20 years, it certainly would be possible to create a full fledged database engine comparable to postgress/oracle/sql server/etc.
SQLite has some pretty damning words about compiler warnings (in particular Visual C++).
> VC++ often will generate a number of warnings from SQLite source code, but the experience of SQLite developers is that VC++ warnings are of lower quality and can be safely ignored. Users are encouraged not to stress over VC++ warnings.
> Static analysis has not proven to be helpful in finding bugs in SQLite. We cannot call to mind a single problem in SQLite that was detected by static analysis that was not first seen by one of the other testing methods described above. On the other hand, we have on occasion introduced new bugs in our efforts to get SQLite to compile without warnings.
Of course, your code is not tested as well as SQLite is.
http://www.sqlite.org/amalgamation.html says it's 90kloc if you remove comments and blanks.
Yeah, I also didn't realize it was so small. 140kloc is quite practical to read completely in a reasonable amount of time.
check out SQLite. It's not on github and it's not iOS or objective C, but for a project of it's complexity, SQLite is clean and nice.
the best way to step up your game is to refactor your existing code. If you have some iOS projects that you are working on, write some unit tests and start refactoring your code base. start with the part of the project that was the biggest pain in your ass. chances are that was the pain of a weak design. your refactoring efforts will be the most productive there.
you can start small. make changes that make the code more readable and "self documenting". next decouple classes and responsibilities. start incorporating design patterns. make lots of small changes, checked by unit tests every step of the way. Don't move on to a new refactoring until you are confident that your changes had a net zero effect.
~~Concurrent writes only, as I understand it~~ See edit and it could be a big deal for wiki. But I'm curious as to what real benchmarks say. This is what they claim.
> SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
EDIT: on currency:
> SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database. For many situations, this is not a problem. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.
I can't speak for Skyrim, but a very common way of doing this is to save the data in a SQLite database - that's what Firefox (for example) uses as its configuration database.
Anyway, the save format for Skyrim type games doesn't really matter - they will still be saved to an ofstream (assuming they were written in C++), just not as text a line at a time - instead, whole structures like dictionaries and trees will (probably) be written as binary blobs - the C++ streams are not limited to reading and writing lines of text.
Agreed with all the other posters: you should split up classes into modules based on whatever hierarchy makes sense....
...with one exception I can think of. Sometimes when you're distributing your program it's nice to have it in a single file. In this case you may want to lump it all into one file even if that's somewhat less readable. SQLite (written in C) is distributed as one huge file. This can make it easier for people to incorporate it into their own projects. They don't have to unzip anything or worry about directories, they just drop the file right in.
If you look on their page Airbus is using it in the A350 XWB, the rival of the Boeing 787 Dreamliner, both planes are pretty much the future of airplanes and one of them uses SQLite in its Flight Management Computer.
That is simply impressing for a piece of software so easily available to the public.
> Compare this to SQLite, which is one of the most-standards compliant databases
I'm going to need a citation for that. SQLite happily stores strings in number columns, and vice-versa. It even touts it as a feature. If you want data integrity, use a database that enforces it.
I can only speak from my own experience here, but the only way I was able to learn SQL is by learning a programming language and working on personal projects. I learned python over a couple months and used it to automate different parts of my digital life. After a short time one of my projects required me to learn SQLite. After completing that project, I got into Web design. Turns out the SQLite skills I'd learned with my last project translated readily into the MySQL skills I needed for Web design.
I'm by no means a SQL master, but I have a very firm understanding of it after 4 months of working this way.
These flow charts here helped me the most http://www.sqlite.org/syntaxdiagrams.html I hope they help you as well!
Not to disparage the author or project, because this does look pretty neat, but I think it's nearly insulting to call this a "SQLite equivalent."
The SQLite team puts a ridiculous emphasis on QA and testing. (http://www.sqlite.org/testing.html#coverage)
Simply put: SQLite is not simple. Or perhaps more accurately, its deceptively simple. -- It's going to be very hard to even approach their level of completeness.
I think by equivalence the OP must mean: stored to a file, and fairly "plug and play." -- Which is a fair assessment in some regards, but there's just so much more to SQLite than meets the eye.
sqlite is not a 'long running process': http://www.sqlite.org/serverless.html
I'm sorry you didn't take the time to look up other management tools: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
The security is taken care of by the file system. Since there's no 'server', that's the only access point you have to worry about, besides your application. If you have no control over what files are writable on your server, then good luck with security...
The biggest issue with using sqlite is the bad concurrency, and you didn't even bring that up.
Have you considered sqlite? You won't need to install a client/server to get rocking with it.
See if it will work for you: http://www.sqlite.org/whentouse.html
Note, you can't do SP directly with sqlite: http://stackoverflow.com/questions/3335162/creating-stored-procedure-and-sqlite
SQLite was designed for exactly this sort of thing. It used (last time I looked, which was years ago) to be rather difficult to use with C#, but this may have changed. Otherwise, SQL Server Compact would be the way to go.
To me, installing the SQLite ODBC driver and querying from desktop software (Access, Excel Power Query, MSQuery) would be the easiest way.
This will allow you to choose only the columns you want in the query, as well as filter by criteria.
http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc
Installing this will allow you to query your SQLite via ODBC using desktop software.
Wait, what? I thought SQLite was in C. The README says:
> (Historical note: SQLite began as a Tcl extension and only later escaped to the wild as an independent library.)
It looks like SQLite is C, with decent bindings in many languages, including TCL. Maybe he prefers TCL, but it doesn't seem to affect SQLite much. (Except maybe in some of the original design choices -- it doesn't surprise me that someone who likes TCL would design a database where you can store a string in an INT field.)
In the most polite manner possible : rtfm
edit : Downvote all you want, but
The answer was in the very first question of the FAQ.
The first link for the google search "sqlite get last primary key" contains the solution. The second link is a direct link to the sqlite3_last_insert_rowid(sqlite3) method. The google search "sqlite3_last_insert_rowid(sqlite3*) python" yielded this SO question in the first five results.
It took me longer to write the comment then it did to find the answer. Seriously...
Might be useful:
> SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. – www.sqlite.org
> As far as we can tell, the SQL language specification allows the use of manifest typing. Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python.
Is there some kind of claim that other databases never have bugs or something? I've worked with Oracle quite a bit, you have to pay them a -lot- of money to make emergency patches for when you experience undefined errors. And you also generally run a master-slave replication pair for failover. It's quite an investment. It'd be somewhat naive to think that MongoDB, something that is trying to scale across hundreds of machines, on commodity hardware, and is new, is never going to have problems.
The only database I can think of that is almost absolutely rock solid is SQLite, it has an extreme amount of automated testing and a limited scope. And even then, it's still had a few data losing bugs. Search for the word corrupt on the changes page. You'll see it's been a good couple of years for sqlite, and look how long it took to get to that level of stability.
See "ROWIDs and the INTEGER PRIMARY KEY" chapter of the CREATE TABLE documentation; one example used there is
> CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
The "INTEGER PRIMARY KEY" is how this concept works in SQLite; it is different in other database systems.
> 20k records
so, a database that easily fits in memory and that you can iterate over in a fraction of a second...
> and used by about 20 people
... and has no noticeable load.
You certainly wouldn't need a search engine powered by thousands of computers. You'd need a couple lines of code, that's all. http://www.sqlite.org/spellfix1.html for SQLite.
It's certainly not
> not technologically feasible at this point in human history
The full text is:
>Format: http://www.debian.org/doc/packaging-manuals/copyright-format/1.0/ Upstream-Name: sqlite3 Source: http://www.sqlite.org/cgi/src/dir?ci=trunk
>Files: * Copyright: D. Richard Hipp <> License: public-domain The files listed have been put on the public domain by the sqlite3 contributors.
>Files: debian/* Copyright: 2006- Laszlo Boszormenyi (GCS) <>, 2005-2006 Tomas Fasth <>, 2001-2005 Andreas Rottmann <> License: GPL-2+ This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License version 2 as published by the Free Software Foundation. . >This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. . You should have received a copy of the GNU General Public License along with this package; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA . On Debian systems, the full text of the GNU General Public License version 2 can be found in the file `/usr/share/common-licenses/GPL-2'.
In which countries isn't legal the public domain?
sqlite is a mySQL like derivative. It is a database system. Unlike mySQL, which uses a centralized daemon, sqlite require no daemon and is bound directly to applications (in most cases).
It comes standard on OS X and iOS and is popular for Linux as well.
Between the sqlite3 language docs and the sqlite3 python module docs you should be able to get the basics figured out.
For html and css I usually head to MDN.
To throw in another perspective: you probably only need to learn a tiny bit of SQL to use it for something simple. Take a look at the first couple of examples in the Python sqlite3 module docs. Those few statements can take you a long way:
- CREATE TABLE
- INSERT
- SELECT
There's a lot more you can do in SQL, but you don't need to know all of that to make use of it.
SQLite's docs have handy syntax diagrams, though they can be a bit daunting because they show every possible option.
If think SQlite is suitable for this situation.
What you'll have to do is to create a table based database with sqlite. Each user will have its own row in the table with the data you wish to save. Then later you can access this table by for example to user name and retrieve the information.
A table example would look like that: When ID is the primary key, and the progress is represented by an INT as for a stage. (Just an example ofc)
INT ID STRING NAME INT PROGRESS
1 John Snow 2
2 Patrick Star 6
3 Jack Rabbit 3
5 million rows isn't a lot or rows and is fine with SQL Lite's limits. I'm an MS-SQL guy, but I know that with some clever use of indexes, you can get some real performance gains.
It may be an idea if your WHERE clause targets a substring of a specific field regularly (for example the first three chars of the servers name), then create an additional field containing only those substrings from the original field and then create an index on that new field should increase performance. Wildcard table scans are always be poor performers.
I don't know the performance constraints of SQL Lite, but it may be worth spending some time seeing if the same DB performs better in another DBMS if it's really bogging down for you.
Personally, if you're going to target a wide range of Android devices it would be worth it to bundle your own SQLite binaries.
More on how to do that here: http://www.sqlite.org/android/doc/trunk/www/index.wiki
I don't know about "lightweight", but SQLServer Express is free for Windows, and gives you an excellent SQL implementation with lots of tools. For something truly lightweight, SQLite is also freely available.
If it's just for your family and friends to use, SQLite will probably be a great choice, unless you have thousands of close friends. Per the SQLite website:
>SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
The code and message are stored as part of the "connection" (the sqlite3 pointer) which means every thread sees the same values, as opposed to using something like thread local storage to ensure that the thread that caused the problem is the one that sees its code and message.
Consequently any code using SQLite that just willy nilly uses multiple threads has race conditions since the code and message can be different by the time they are read in the thread. Worst case the program can crash - if for example the pointer to the message is read and then the memory it points to is unmapped before the contents are copied. (Or random garbage until the first null is used.)
As I showed earlier, a wrapper around SQLite can only be safe if it ensures that no other threads run after an operation until after the code and message are copied. APSW does that. (py)sqlite does not which why it is not thread safe (amongst other reasons).
You should choose a Database over a flatfile when the amount of data is big (thousand of entries) and there'll be more than one operation happening at the same time over the data (many users updating the score).
Anyways you don't need an engine such MySQL or similar for a medium-sized project. You could use SQLite which is simpler and uses files on disk for the databases offering SQL for querying and a few features more.
> would there be a scenario where sqlite would not cut it in comparison with mysql?
Yes: because there is no centralized access handler on the db file in sqlite, each connection trying to write will attempt to lock the file. Furthermore the write can only happen when no other connection is active (not even trying to read something in DB). As a result, sqlite has a pretty bad write concurrency even compared to MyISAM (more generally it has a bad concurrency anytime writes are involved).
This may improve by activating the Write-Ahead Log (available since 3.7.0) but I have no experience whatsoever with that. I believe WAL makes reads and writes independent (so a read does not block a write, and a write does not block any read), which should tremendously improve concurrency in cases of non-infrequent writes. On the other hand, enabling WAL adds a bunch of restrictions and drawbacks.
You almost certainly want to use a flat file or standard relational database for that kind of data.
Relational databases are suitable for 99% of data storage needs. As you're aware, they are simply tables of columns and rows, which can link together, and which you can query with SQL.
You can also get XML databases, which (obviously!) store XML data. These are suitable for the semi-structured, hierarchical (rather than relational) data of things like...XHTML documents (which in a relational database would typically be stored in an unstructured, unquery-able text field or binary blob). You'd then have to use something like XPath or XQuery to locate and extract the information you need from the XML documents.
In your case, because the data doesn't change, if you wanted to use a simple file rather than a database (and then do sorting/querying in your code), then you'd be better off with a simple data serialization format like CSV or YAML.
In fact, a good compromise might be http://www.sqlite.org/ which is available as an extension for most languages. It's a 'serverless' database, which basically means it just uses flat files for storage, but you can query it using simple SQL. Best of both worlds, in some ways (unless you were going to store more data, and need better caching, etc).
There's also a third option, to use a key-value store like Redis (http://redis.io/), but for the sake of simplicity you might want to stick with a flat file or SQLite for your purpose, especially given that you have a small amount of unchanging data.
You might want to look into sqlite. It is completely free and much better than Access ever was. Especially for your usage it sounds perfect. Contrary to popular belief it does support multi-user concurrency. Although it is nowhere near as good or as fast at it as an actual "server" based SQL database, it will likely do the job for you. The only caveat would be if you plan on having the client programs access the database file directly using a network share, in which case there have been problems with the way sqlite locks files.
Data storage is one of the most important aspects of programming to get right, and also one of the hardest. You only have to look at the amount of effort that goes into testing SQLite to realise how seriously the problem should be taken.
I've scanned the code quickly and I'm afraid I really can't recommend developers use this as a replacement for SQLite, or for data persistance in general. Some issues I identified are:
- No consideration for concurrency issues (as far as I can see). If two threads/processes concurrently wrote to the store, one would lose their data as far as I can see.
- In memory design: Opening a store is implemented by loading the entire contents into memory with an ObjectInputStream, and dumping it to disk to save. This is only going to work for small data sets, and I can't really see the the advantage over just using a normal Map and serialising that directly.
- No tests in the codebase
- Important methods not implemented which fail silently (remove(), replace() in BTree.java. At least throw an exception so programmers know they're not meant to work.
I'm sorry to rain on the parade, but I'd hate to see someone get burned. I'd suggest the OP implements a key-value store on top of SQLite rather than implementing the data persistance yourself.
I have not yet come to this stage in my own project, but what I'm planning to do is to use SQLite for this purpose. Why? - It's very easy to use, has no other dependencies, saves to one file, allows me to query the data in various ways, etc. There seems to be a C# port aswell, but I'll be using the C version (project is in C++).
As a statistician, SQL is a good addition to your toolbox. I do some work in R, which by default loads all data into memory. This is a problem if you're working with data sets that are a few GB or more in size. If the data is in a relational DB (i.e., a DB that can be queried by SQL), then you may be able to write a query to select a subset of the data that fits in memory and proceed from there.
On that note, you may eventually want to learn a little about map-reduce, a technique for operating on data sets so large they don't fit on a single hard drive. I think the most popular open source implementation of map-reduce is hadoop.
Going back to SQL, I'm not familiar with MariaDB but a popular small relational database is sqlite. Unfortunately, you can't really do much (with sqlite or any database) until you've loaded in a some data to play around with. Does anybody know of any public data sets that are easily -- as in, for a novice -- loaded into a popular database?
json.org is unfortunately a poor specification for JSON -- people come away from reading that page with different interpretations, since the graphs appear to say one thing, yet the text says another. Unfortunately, everybody knows about that site. RFC4627 is more precise than json.org, and it only allows JSON to start with a list or hash.
Had Crockford used EBNF, and clearly stated that everything else aside this was the canonical parsing, we wouldn't be having this conversation. The graphs leave a little to be desired too; compare with what sqlite.org uses. Alas, the graphs on sqlite.org are also imperfect, since they don't clearly state that sql-stmt-list is the first production.
Ultimately what matters is what's out in the wild. In the wild I've seen both takes, and as a result JSON serialized by one implementation is not guaranteed to work with another (learned this the hard way). Given JSON's apparent simplicity, it's pretty ridiculous, isn't it? I blame it on the above.
Edit: I should be fair and note that parsing CSV is even more an adventure, despite being considerably simpler than JSON. It too has an RFC, which probably was a helpless attempt to bring sanity to the madness.
Sqlite. That page has a getting started guide that should have you connected to a database from a program in 5 minutes.
Other than that, I'd install a lamp or wamp stack for ease of installation and configuration then google specifically '<language> <backend DB> how to', i.e. "Java MySql how to".
> I have an idle hope that maybe Notch will consider adding this to vanilla Minecraft someday
He didn't seem to be too receptive to my gentle emailing (no response at all), but he probably gets so flooded with information that I really don't blame him. To tell you the truth all I wanted to do was approach Notch with my method, and to have him use it as a MIT licensed library (he already does this with several other java libs), because I actually sort of feel uncomfortable with the reverse engineering modding approach. This is actually mostly why I haven't written a mod yet, but I guess that's the best way to get noticed, though :)
> Also, lots of people write tools to read and write Minecraft chunks. A more complex format means fewer people will write tools for it, and I wanted to minimize this.
My ideal scenario was that Notch would adopt some kind of open source library for doing minecraft file io, and then that would be available to everyone. That might have been a bit unrealistic.
> The simplicity also makes reasoning about behavior if Minecraft crashes easier. In the worst case, at most one chunk is lost. Those statements are harder to make with more complex datastructures.
Oh God Yes. Writing a B+ tree store is not actually hard. Making anything that complex resistant to crashing is very hard. Everything like that is hard, though... for you, what happens when a disk write fails while writing the chunk count in a sector? As in, in the middle of the 4 byte write, or out of order and before the actual new chunk is written? Writing that library, and reading documents like this have made me ungodly paranoid.
> You make trade-offs depending on your situation. I'm happy with the ones I chose. :-)
I can understand why you chose the way you did. Most of the time, after everything is done, I wish I had chosen the simpler solution.
sqlite can also throw that error if it's exceeding maximum page limit
http://www.sqlite.org/pragma.html#pragma\_max\_page\_count
workaround is to set max_page_count with a pragma statement before doing the db recovery
PRAGMA max_page_count = 2147483646;.
> The thing that turns the SQL statement into the specific things on disk to look for is not the bottleneck in a well written query.
In general. For SQLite, I think it often is. They compile the SQL statement into a program in their custom VM, and they seem to redo this every time you bind parameters (not just when you prepare a statement). Then they run the VM through an interpreter.
SQLite is a hugely impressive piece of software in that it's rock-solid (breathtaking test coverage, and they argue quite convincingly it's the most widely used database engine in the world, and among the top five pieces of software of any kind). It's also easy to use. Its speed however is not so impressive in my experience.
If your file will keep growing in size, you might want to use a database instead, so that you can simply ask the database if the data is in there rather than having to manage the file and storage data structure yourself. SQLite comes built in with Python and doesn't require setting up a separate database system.
> Yeah, but to do anything with the data, you have to transform it into a format you can use anyway, and that format will be larger than your disk space.
No, as long as you roughly know where inside the file is the data that you need, you can only decompress the necessary fragment of the archive (which is why it is much better when it is sorted!). You can expect similar tricks from any major Relational DB engine. For examples, see ZIPVFS for Sqlite or PostgreSQL on ZFS.
I have struggled myself with mysql many years, as the syntax is non intuitive and not very orthogonal. However, I recently found syntax diagrams for sqlite which implied that I for the first time in my life could write a syntactically correct SQL query directly, without trial and error or checking some old query.
With these syntax diagrams it wouldn't be hard to write a syntax sensitive command shell, you know like in e.g. ckermit, the old modem program. In the late 80-ies when I was working as software developer at a big company I made a shell inspired by kermit for a debugging utility, it was very popular.
http://www.sqlite.org/lang.html
Edit: now see that my answer may be irrelevant for what you asked for, but may otherwise be useful.
Interesting idea! There are going to be a lot of components to this. Are you going to be using a paid service or a personal machine as the server? As far as representing the data, the app side is not critical. When the user submits a form for a lost item, I would have the form be in the same format as you choose to represent it on the server (type of object, approximate size, color(s), etc). Then when you view potential matches, have the server format the data using something like JSON then have it sent to the app. That way a change in how the server represents the data results strictly in changes on the server side, as the app will just read the new JSON sent to it.
As far as searching for matches, it will be very tricky because every search will need to have a lenience in the language used to describe an object as well as the actual physical attributes. The fastest and easiest thing I can recommend is a Custom Google Search which will also allow you to search images (a possible technique once a preliminary search is done and many matches are found). Another option is FTS on an SQLite database. This will allow you to get your hands a little dirty with search algorithms without having to start from scratch. I also believe that the GCSE will send the data off to a remote server, whereas this will run directly on your machine which will be an additional bonus. If you really want to build a search engine from scratch, then look into database structures and how querys work. Any other questions you have just let me know! I hope it gets the gears turning about what will be going into this.
Another option if you are storing locally is to use SQLite, which is a regular relational database, but not network based. You can think of it as the file format for your App, and then use SQL commands to store and access your data. It does not support things like stored procedures, but has pretty complete coverage of basic relational concepts. You can find out more about it at http://www.sqlite.org .
I don't know enough about Core Data, as it also will allow you to use SQLite as the back end storage, but you wouldn't access it with traditional relational database commands.
This is a problem because Python's Windows distribution does not include the current version -- or even a particularly recent version -- of Sqlite's DLL file. It's easily fixed, but you'll have to remember to do it with every python install.
Replace (rename the original first) sqlite3.dll in "c:\Python27\DLLs" with the latest on the SQLite site. The dll can be downloaded under "Precompiled binaries for Windows" from Sqlite's download page. I usually keep an extra copy of the DLL somewhere so I can easily repeat the process after an upgrade, etc.
There's an old discussion on the Python site that boils down to the maintainer of Python's sqlite3 library not wanting to use a different DLL out of fear of incompatibilities, so it doesn't look like it's something that will ever be updated; you'll have to do it yourself.
(feature) quality over (feature) quantity. If app is more than few thousands of LOC's, or you are working in a team you just can't store all code in your head. Good testing is critical. For example sqlite is ~90KLOC, and tests are ~91000KLOC, source.
Spreadsheets are not a good way to manage large amounts of data. Some sort of database would definitely be far more useful. Since you wouldn't need global data access or huge amounts of scalability for projects like this I'd recommend SQLite. Lots of languages have third party tools available for working with SQLite and most of them should be able to handle what you want to do.
I had to do an app like this once commercially. We used SQLite directly and tuned the hell out of it until it could free-text search about a ~1.5M record DB in under 2 seconds on 1st generation iPhone hardware.
The feature of SQLite you are looking for will be the "FTS" module, aka "full text search". It allows you to query across all the database, freeform, in a somewhat non-SQL manner. You'll have to download, compile, and link your own SQLite library, because at least as of iOS 4, the preinstalled Apple library did not include the FTS module.
The user interface you want is dead easy - use a UISearchController for iOS 8 and later, a UISearchBar for compatibility with iOS 7 and earlier. Or you could just use a text field and tableview beneath to show the results.
Good luck!
My harddrive is littered with half-abandoned projects. These are the ones I've currently working on (all in C89):
- A minimal font renderer (simple TrueType fonts only)
- A NES emulator and debugger
- A minimal game engine
- A md3 renderer
As for quality, I quite like the sqlite approach. That is, extensive testing and verbose inline documentation
As of just a couple versions ago, SQLite now has Recursive CTEs. One of the "outlandish query examples" on that page is a query that draws a mandelbrot set.
Mostly for giggles, I implemented a tool that mostly uses their query to do all the work, but lets you zoom into the set, resize the window for different resolutions, etc.
If you want to go the database route, Python comes with a module for SQLite. What you're describing sounds like it would be pretty simple and straightforward with one SQL table.
> You never know what issues you are going to run into in a library
One thing going for SQLite is how thorough its developer is about testing. For a storage layer, that's a good thing.
Not to say that nobody should consider using something different, but this is what others are going up against on the reliability side of things.
You do know that SQLite is not actually developed as a single, 140Kline-long C source file right? Why are you reading this version instead of the original source (svn)?
If you got confused by their download page, you might want to read about the amalgamation.
For a very light and easy to use SQL you can't beat http://www.sqlite.org withthe firefox SqliteManager addon.
For really understanding what you are doing... I agree with you, Date's book and Postgres is the way to go.....
Thanks. LIKE is pretty slow; if space isn't highly restricted you can take a look at FTS which speeds up the process extremely.
An example from the linked doc:
SELECT count() FROM enrondata1 WHERE content MATCH 'linux'; / 0.03 seconds (FTS) / SELECT count() FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds (Standard) */
If you aren't so worried about the security of your password as much as just stopping a casual user of your computer displaying the password (i.e. reading the txt file), you may want to consider using something like sqlite instead. Its very easy to develop against, it gives you some flexibility if you want to extend your application a bit, and I'm willing to bet that most of the users of your computer will have no idea on how to access the contents.
There are other DB solutions that can be installed on your machine that can offer even greater security (e.g. PostgreSQL) but those become a bit harder to migrate to a new computer (for sqlite you just copy a single file).
Anyways, just thought I'd throw that out there.
Also there's a Firefox add-on for SQLite that gives a UI for working with tables, queries, views, etc. https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/
And there's an installable command-line client for SQLite. http://www.sqlite.org/cli.html
Both of these can be very helpful while learning SQL itself before getting tied up in the Python API.
Find out the actual value of try_sql and compare it to the values found here: http://www.sqlite.org/c3ref/c_abort.html. If it's a large number, see if it matches one of http://www.sqlite.org/c3ref/c_abort_rollback.html.
If the message is "not an error", however, it's really weird. It seems like it isn't a valid error code, but if it's generated by sqlite3_prepare_v2, it should be.
Your tables really should have their own primary keys called ID and the foreign keys should be called schedule_id so at a glance it's obvious which table they relate to. Generally primary keys and foreign keys are put first in the column list, I'm not sure whether that affects performance or not.
For query performance the you should have a non-unique index on the schedule_id columns so the lookups are much faster. For insert performance you should wrap batches of inserts in a single transaction otherwise each insert is implicitly its' own transaction which incurs overhead (see http://www.sqlite.org/faq.html#q19).
sqlite can already do RAM-only databases, but I still think it makes sense to only persist when you have to, and then against a database closer to what you'll use in production.
edit: oops. You already pointed that out. I've used in-memory sqlite DBs in production and it was fast as hell, especially if you only commit every once in a while (as opposed after every query).
Each ship can be 5-100kb big, and eventually it adds up after a while, but 95MB does seem a bit excessive.
If you've got the "sqlite3 shell" program installed from http://www.sqlite.org/download.html, you could try running this, although this is something the server should be doing in the first place:
sqlite3 KMP_universe.db VACUUM
I'll find out if there is any other entries that can be deleted, having a database that big wasn't too uncommon in the past.
Here's an example you can try for yourself. Download SQLite's source: sqlite-amalgamation-3080401.zip.
# No warnings $ gcc -ldl -pthread -O0 -Wall sqlite3.c shell.c $
# Change to -O3 $ gcc -ldl -pthread -O3 -Wall sqlite3.c shell.c sqlite3.c: In function ‘balance’: sqlite3.c:57029:22: warning: array subscript is above array bounds [-Warray-bounds] pOld = apCopy[++j]; ^ $
On the download page, you want this one:
sqlite-dll-win32-x86-3080301.zip >(322.04 KiB) This ZIP archive contains a DLL for the SQLite library version 3.8.3.1 for 32-bit x86 processors using the Win32 API. The DLL is built using SQLITE_ENABLE_COLUMN_METADATA so that it is suitable for use with Ruby on Rails. (sha1: 51a60793c15bf76485a2d2917b5cc948a35f1e15)
Then you reference the DLL via your project references list and start binding to it.
You can get started here: http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/#/
I haven't done it but for the PC the db file appears to be here C:\Program Files (x86)\Plex\Plex Media Server\Resources You can probably use SQLite to open it in Windows.
Firstly, you shouldn't be proactively optimizing unless you have a performance problem. Any modern RDBMS will handle most queries far better than you can hope to. That code is written by people far smarter than you or I. The kinds of products I'm thinking of here are things like Oracle, SQL server, DB2, PostgreSQL, etc. Write it correctly and legibly, then optimize if you need to.
There are a few rules that are always good practice.
Use set logic, not cursors. Select only what you need; avoid select * Let the engine do the work.
A few comments on what I consider 2nd-tier SQL engines. They are fine for many purposes, but they need a bit of adult supervision. I'm think of things like SQLAnywhere, PervasiveSQL, MySQL and SQLite.
I actually like SQLite a lot, just remember what its design goals were. It's fast and small, but it was not designed for highly concurrent access and it has a small number of types. Some of the type it supports are just stored as text fields and although it parses most of the normal SQL constraints, it doesn't actually enforce them.
I would expect it to perform well under basic, flat table access. If I needed complex joins or subqueries, I would be looking at those query plans more closely. There is specific SQLite guidance here -
http://www.sqlite.org/optoverview.html
Again- pretty good, but there are a few things to be aware of.
I would do the following:
Iteration 2: Use Apache, PHP and flat files (XML or whatever custom format you want). After this step, though, you should switch to a SQL database because all of the below steps are using more complex data, and writing your own way of storing each of them will be too much overhead.
Iteration 3: Create a Mobile CSS file
Iteration 4: Create a Web XML interface and have the IOS app interact with that (Look at REST). This seems like a big dive for you; you may want to focus on the server side.
Iteration 5: Also complex although there are solutions already written out there. For anything serious, you need to make sure that you really understand the secruity requirements and how it works, and not write your own.
Iteration 6: Pretty complex, and how are you going to get this info? I would insert all of this location data into a sql database and use whatever algorithm to find out what things are nearby.
Iteration 7: Easier than most of these other tasks. Just add more object types into the database.
Have you looked into where you are going to host this?
I recommend you familiarize yourself with SQL after you pass iteration 2. SQLite is a pretty simple, free version that isn't fussy to set up. http://www.sqlite.org/
Best of Luck!
I have had a database locked issue in a Java program using SQLite. I resolved the issue by ensuring that simultaneous writes could not happen in multiple threads. At the time I assumed SQLite breaks if you try to do multi-threaded writes. Looking back, I'm not sure why as I referenced this and it doesn't say you can't try to write from multiple threads from the same program. Now I am now wondering if my sqlite libraries are properly compiled. Could you elaborate on how you checked?
I have the srpm that my sqlite library was built from and an environment similar to the one originally used to create the binary rpm. I checked the spec file and can see that configure does run. In my environment, configure detects the presence of usleep.
It's probably not the usleep thing, there were a host of other problems with the program, I'd just like to check.
You need to use an API for the database from C. An example would be the sqlite API.
This is an openmoko application which uses sqlite to store data
For question two:
Your best bet is something called SQLite, which is essentially a text file which mimics a SQL database in terms of interface (although not in terms of performance). I believe it comes with most standard Linux distributions (try "man sqlite3"). This will give you a lightweight method to practice your SQL
The simplest database I use is sqlite. It's a 300kb download. But is professional grade and used in everything from firefox, to android. I use with php and other languages.
You will need to learn some sql though :) But it's a good investment and can handle large datasets quite well.
There are also google apps which do this quite well. You can sign up for free with google.
https://www.google.com/enterprise/marketplace/categoryHome?categoryId=7
> but I want to know more about how to use it in a client-side application as more of a database.
A bad, bad idea. XML is not intended to be a database - if you want that, use something like SQLite.
Use emscripten to compile sqlite into javascript. Then you could have a real database running in all browsers.
Store the database in local storage using an abstraction layer like https://github.com/dreamdust/sticky
I'm not much of a SQLite user, so can't help you there but I've had that problem in the past ;) My guess would be that SQLite creates some temp files while performing operations on the DB (maybe while you're working on transactions it uses the temp until you commit?).
*edit: yup, that looks to be the reason for it - SQLite is creating a rollback jurnal (file with the name of the DB with -jurnal added)
Sqlite appears to have AVG, SUM, MAX, etc.: http://www.sqlite.org/lang_aggfunc.html .
The vast majority of websites are not "high traffic" enough to require something more than sqlite, according to everything I have read.
Submitter, you should definitely check out Redis and Riak.
Several points in the sqlite docs seem to suggest that may not be the best fit:
> If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite
Check. I have about 10 boxes that would need access, and the number continues to grow
> But if you website is so busy that you are thinking of splitting the database component off onto a separate machine, then you should definitely consider using an enterprise-class client/server database engine instead of SQLite.
My databases are already on dedicated servers
> there are some applications that require more concurrency, and those applications may need to seek a different solution.
Definitely a little scary.
The easiest thing to do is to use prepared statements.
So, glossing over some details, instead of writing something like
run_query("select name from employees where wage > " + wage)
you do something like
query = "select name from employees where wage > ?" run_query(query, wage)
This means that the wage variable there is never seen by the SQL parser -- it goes in afterwards -- and so you don't get injection attacks.
EDIT: to actually answer your question, it's likely that the people who developed this site have never even heard of prepared statements, even though they're in every half decent SQL engine (such as SQLite), and so never thought to use them, believing that some god awful hack was the only option.
The difference is that SQLite doesn't have the pretension of being a database for high-load systems. Its mission is extremely clear, and its code is some the best in the entire industry. It's probably also de best-tested code in all history.
Thanks for the tip. Looks like a powerful implementation, and I might use a GIS backend like that in the future. Using SQLite now, and figured this might save me time. SQLite R*Trees
So, did you read the SQLite FAQ?
.
.
.
.
Thought so. Let me copy it for you: > Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second. > Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..
The reason MySQL was much faster was because MySQL is not keeping your data safe. Use transactions, and you'll be fine.
There's a general framework of thought at work here. It applies to a broad class of problems you might call "system dependencies".
You want to work with sqlite3, so you install the sqlite3 gem. But stop to consider what that is. We can find out by looking at the output of gem info sqlite3 -r, or visiting the sqlite3 package on rubygems.org.
>This module allows Ruby programs to interface with the SQLite3 database engine (http://www.sqlite.org). You must have the SQLite engine installed in order to build this module. Note that this module is only compatible with SQLite 3.6.16 or newer.
It should be clear by this description that the gem doesn't actually install sqlite on your system.
Unfortunately, not all gems are clear about this, so there is another heuristic pattern we can use. Any time you see filename.h not found, you're missing something underlying. It might be the application or library indicated by filename, or it might be a gem's C extension that is broken.
Generally speaking, you want to look at the root filename and see if you can figure out what application it is associated with. When you see the .h extension, that refers to a header file, and header files are frequently associated with dev headers, which are included with -dev packages. So in this case, it was a short trip from seeing sqlite3.h to the application sqlite, then to the header files, which are probably associated with the package sqlite3-dev. Note that sometimes you have to do some web searches to figure out what application or library a file belongs to, but that's how you chase down the lead.
GalaXQLis an interactive SQL tutorial
GalaXQL is based on the SQLite database engine. 1.0 and 2.0 were written with wxWidgets for multi-platform capabilities, which is kind of ironic because wxWidgets seems to be the reason people can't build new versions for osx and linux.
Follow the instructions by your virtual teacher. The teacher can understand several possible mistakes that you may make, and gives hints on what may have gone wrong. You can skip to any chapter in the tutorial whenever you want.
Use the query editor to interact with the database, and to complete the assignments given by the virtual teacher. In case of syntax errors, SQLite (the database engine used in GalaXQL) returns helpful, human-readable error messages.
If you need more information, you can look things up in the integrated reference.
Oh, and the OpenGL-rendered galaxy map is not there just for show; you can alter the galaxy using SQL.
Table of Contents:
​
- Welcome..
- SELECT ..
- SELECT .. FROM ..
- SELECT .. FROM .. WHERE .. ORDER BY .. DESC
- MAX(), MIN(), COUNT(), AVG(), SUM(), NULL
- INSERT INTO .. VALUES ..
- INSERT INTO .. SELECT ..
- Transactions, DELETE FROM .. WHERE ..
- UPDATE .. SET .. WHERE ..
- SELECT FROM table1, table2.., DISTINCT
- SELECT .. FROM (SELECT .. FROM ..)
- SELECT FROM .. JOIN ..
- CREATE VIEW, DROP VIEW
- CREATE TABLE, DROP TABLE
- Constraints
- ALTER TABLE
- SELECT .. GROUP BY .. HAVING ..
- UNION, UNION ALL, INTERSECT, EXCEPT
- Triggers
- Indexes
The concept of SQLite not being robust enough for production is based on a few things (some of which are overblown a bit- you'll get fairly far with SQLIte, but you will definitely hit a wall at some point in the medium term.) Mainly it's based on SQLite (designed primarily as a lightweight database for embedded devices and phones, etc.), being bad on concurrent writes to the database. http://www.sqlite.org/lockingv3.html
It's part of sqlite (I think, they gave away everything so everyone can use it). http://www.sqlite.org/slt/vpatch?from=1d627f5850e271cf&to=f85e9769888f9e76 And, yes, that looks like the pattern they are using.
Sometimes old code hangs around a long time.
Thanks!
It is an interesting question. It really depends how much effort you put into it, and often we don't have enough time to test everything, so find a balance. First targets should be the code you wrote. I tend not to test the content of the templates that much, because the errors that can happen there are usually easily noticable when viewing the app. You want to test the parts that you may want to refactor, because you can refactor tested code more safely. Also when you find a bug, write a failing test first, and then fix the bug. Good example for test coverage would be SQLite(http://www.sqlite.org/testing.html) however it is not frontend example.
You can see the mechanics of how sqlite parses the SQL language here: https://www.sqlite.org/lang_select.html
The code generator compiles and optimizes the query into bytecode instructions for a virtual machine. You can see the architecture notes here http://www.sqlite.org/arch.html
The best book I would recommend to learn about compilers is (the final chapter of) SICP which is free online: https://mitpress.mit.edu/sicp/