Skip to main content
Fachgebiete
Automobil + Motoren Bauwesen + Immobilien Business IT + Informatik Elektrotechnik + Elektronik Energie + Nachhaltigkeit Finance + Banking Management + Führung Marketing + Vertrieb Maschinenbau + Werkstoffe Versicherung + Risiko
DE
EN
Themenseiten
Organisationspsychologie Projektmanagement Marketing Smart Manufacturing
Bücher
Zeitschriften
Weitere Formate
Podcasts Webinare Technik Webinare Wirtschaft Kongresse Veranstaltungskalender Awards
Jetzt Einzelzugang starten
Zugang für Unternehmen
Referenzkunden
ATZ-Fachkongress

Chassis.tech plus 2024


4 Kongresse in einer Veranstaltung

Treffen Sie in München die Fachleute von Chassis, Lenkung, Bremsen sowie Reifen/Räder.
Erweiterte Suche
Anmelden
nach oben

2010 | Buch

Introducing SQLite Kapitel lesen Erstes Kapitel lesen

The Definitive Guide to SQLite

verfasst von: Grant Allen, Mike Owens

Verlag: Apress

Enthalten in: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Einloggen, um Zugang zu erhalten
insite
SUCHEN

Über dieses Buch

Outside of the world of enterprise computing, there is one database that enables a huge range of software and hardware to flex relational database capabilities, without the baggage and cost of traditional database management systems. That database is SQLite—an embeddable database with an amazingly small footprint, yet able to handle databases of enormous size. SQLite comes equipped with an array of powerful features available through a host of programming and development environments. It is supported by languages such as C, Java, Perl, PHP, Python, Ruby, TCL, and more.

The Definitive Guide to SQLite, Second Edition is devoted to complete coverage of the latest version of this powerful database. It offers a thorough overview of SQLite’s capabilities and APIs. The book also uses SQLite as the basis for helping newcomers make their first foray into database development. In only a short time you can be writing programs as diverse as a server-side browser plug-in or the next great iPhone or Android application!

Learn about SQLite extensions for C, Java, Perl, PHP, Python, Ruby, and Tcl. Get solid coverage of SQLite internals. Explore developing iOS (iPhone) and Android applications with SQLite.

SQLite is the solution chosen for thousands of products around the world, from mobile phones and GPS devices to set-top boxes and web browsers. You almost certainly use SQLite every day without even realizing it!

Inhaltsverzeichnis

Frontmatter
Chapter 1. Introducing SQLite
Abstract
SQLite is not to be confused with other larger databases like Oracle or PostgreSQL. Whereas dedicated relational databases such as these are electronic juggernauts, SQLite is a digital Swiss Army knife. Whereas large-scale dedicated relational databases are designed for thousands of users, SQLite is designed for thousands of uses. It is more than a database. Although a tool in its own right, it is a tool for making tools as well. It is a true utility, engineered to enable you—the developer, user, or administrator—to quickly and easily shape those disparate piles of data into order, and manipulate them to your liking with minimal effort.
SQLite is public domain software. Free. You can do anything with it or its source code you like. No licenses, no install programs, no restrictions. Just copy and run. It is also portable, well tested, and reliable. It has a clean, modular design that helps keep the system simple, easy to develop, and easy to debug. In addition to good design, it has good testing. There is more code written to test SQLite than there is SQLite code to test. It should not be too surprising, then, that SQLite has proven itself to be a solid, reliable database over its five-year history.
Finally, SQLite is fun. At least we think so, and we hope that you will find it equally useful and enjoyable.
If you have any comments or suggestions on this book or its examples, please feel free to contact the authors at sqlitebook@gmail.com (Michael) or grantondata@gmail.com (Grant).
Grant Allen, Mike Owens
Chapter 2. Getting Started
Abstract
No matter what platform you work on, SQLite is easy to install and build. Windows, Mac OS X, and Linux users can obtain binaries directly from the SQLite website. Users of many other operating systems can also obtain binaries using their native—or even third-party—package systems. The common way to work with SQLite across all platforms is using the SQLite command-line program (CLP). This program operates as both a command-line tool and an interactive shell. You can issue queries and do essential database administration tasks such as creating tables, indexes, and views as well as exporting and importing data. SQLite databases are contained in single operating system files, so doing things like backups are very simple—just copy the file. For long-term backups, however, it is always best to dump the database in SQL format, because this is portable across SQLite versions. In the next few chapters, you will be using the CLP to explore SQL and the database aspects of SQLite. We will start with the basics of using SQL with SQLite in Chapter 3 and move to more advanced SQL in Chapter 4.
Grant Allen, Mike Owens
Chapter 3. SQL for SQLite
Abstract
Congratulations, you have learned the select command for SQLite’s implementation of SQL. Not only have you learned how the command works, but you’ve learned some relational theory in the process. You should now be comfortable with using select statements to query your data, join, aggregate, summarize, and dissect it for various uses.
We’ll continue the discussion of SQL in the next chapter, where we’ll build on your knowledge of select by introducing the other members of DML, as well as DDL and other helpful SQL constructs in SQLite.
Grant Allen, Mike Owens
Chapter 4. Advanced SQL for SQLite
Abstract
SQL may be a simple language to use, but there is quite a bit of it, and it’s taken us two chapters just to introduce the major concepts for SQLite’s implementation of SQL. But that shouldn’t be too surprising, because it is the sole interface through which to interact with a relational database. Whether you are a casual user, system administrator, or developer, you have to know SQL if you are going to work with a relational database.
If you are programming with SQLite, then you should be off to a good start on the SQL side of things. Now you need to know a little about how SQLite goes about executing all of these commands. This is where Chapter 5 should prove useful. It will introduce you to the API and show you how it works in relation to the way SQLite functions internally.
Grant Allen, Mike Owens
Chapter 5. SQLite Design and Concepts
Abstract
The SQLite API is flexible, intuitive, and easy to use. It has two basic parts: the core API and the extension API. The core API revolves around two basic data structures used to execute SQL commands: the connection and the statement. Commands are executed in three steps: compilation, execution, and finalization. SQLite’s wrapper functions exec() and get_table() wrap these steps into a single function call, automatically handling the associated statement object for you. The extension API provides you with the means to customize SQLite in three difference ways: user-defined functions, user-defined aggregates, and user-defined collations.
Because SQLite’s concurrency model is somewhat different from other databases, it is important that you understand a bit about how it manages transactions and locks, how they work behind the scenes, and how they work within your code. Overall, the concepts are not difficult to understand, and there are just a few simple rules to keep in mind when you write code that uses SQLite.
The following chapters will draw heavily on what you’ve have learned here, because these concepts apply not only to the C API but to language extensions as well as they are built on top of the C API.
Grant Allen, Mike Owens
Chapter 6. The Core C API
Abstract
The core C API contains everything you need to process SQL commands and then some. It contains a variety of convenient query methods that are useful in different ways. These include sqlite3_exec() and sqlite3_get_table(), which allow you to execute commands in a single function call. The API includes many utility functions as well, allowing you to determine the number of affected records in a query, get the last inserted ROWID of an INSERT statement, trace SQL commands run on a connection, and conveniently format strings for SQL statements.
The sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize() methods provide you with a lot of flexibility and control over statement execution through the use of statement handles. Statement handles provide more detailed row and column information, the convenience of bound parameters, and the ability to reuse prepared statements, avoiding the overhead of query compilation.
SQLite provides a variety of ways to manage runtime events through the use of event filters. Its commit, rollback, and update hooks allow you to monitor and control specific changes in database state. You can watch changes to rows and columns as they happen and use an authorizer function to monitor and restrict what queries can do as they are compiled.
And believe it or not, you’ve seen only half of the API! Actually, you’ve seen more like three-quarters, but you are likely to find that what is in the next chapter—user-defined functions, aggregates, and collations—is every bit as useful and interesting as what is in this one.
Grant Allen, Mike Owens
Chapter 7. The Extension C API
Abstract
By now you’ve discovered that user-defined functions, aggregates, and collations can be surprisingly useful. The ability to add such functions using the extensions part of the C API is a great complement to the open nature of the core SQLite library. So, you’re free to dig in and modify SQLite to your heart’s content. We would even go so far to say that SQLite provides a friendly, easy-to-user interface that makes it possible to implement a wide range of powerful extensions and customizations, especially when combined with other features already present in SQLite.
In the next chapter, you will see how many extension languages take advantage of this. They use the C API to make it possible to implement all manner of capabilities and extensions for a given language.
Grant Allen, Mike Owens
Chapter 8. Language Extensions
Abstract
You’ve now experienced a brief survey of several different language extensions and how they work with SQLite. Although using SQLite with the C API is quite straightforward, using SQLite in language extensions is considerably easier. Many of the concepts are very similar, and many of the extensions map in a one-to-one fashion to the underlying SQLite C API. As you can see, there are many things in common even in cases where an extension conforms to a language-specific database API. All queries ultimately involve a connection object of some kind, which maps to an internal sqlite3 structure, and a statement or cursor object, which internally maps to a sqlite3_stmt structure.
These extensions make using SQLite convenient and easy, increasing accessibility to many more applications ranging from system administration to web site development. There has been an explosion in the number of extensions in recent years, and you can find out about the (literally) hundreds of available extensions on the SQLite wiki at www.sqlite.org/cvstrac/wiki?p=SqliteWrappers.
Grant Allen, Mike Owens
Chapter 9. iOS Development with SQLite
Abstract
You’ve now experienced the trials and tribulations of creating your first (or latest) SQLite-based iOS application. We know your friends and family will be amazed at your newfound trivia knowledge. In all honesty, they’ll probably be interested in just how easy it is to build your own iPad or iPhone application with SQLite.
If you do decide to make it big with your iSeinfeld application, be sure to let us know. We’d love to buy a copy of the application from iTunes to add to our collection of iSeinfeld variants.
Grant Allen, Mike Owens
Chapter 10. Android Development with SQLite
Abstract
You now have a firm understanding of the tools, classes, and practice of creating SQLite-based Android applications. The Android SQLite API is somewhat different from the APIs we’ve covered elsewhere, and it is evolving at a rapid rate, just as the rest of the Android platform is also evolving. Because Android is proving to be a runaway mobile platform success, we’re sure any interest you have in creating SQLite-based applications for Android will be well rewarded. Good luck!
Grant Allen, Mike Owens
Chapter 11. SQLite Internals and New Features
Abstract
That concludes our journey through SQLite, not only in this chapter but the book as well. We hope you have enjoyed it. As you saw in Chapter 1, SQLite is more than merely a free database. It is a well-written software library with a wide range of applications. It is a database, a utility, and a helpful programming tool.
What you have seen in this chapter barely scratches the surface of the internals, but it should give you a better idea about how things work nonetheless. And it also gives you an appreciation for how elegantly SQLite approaches a very complex problem. You know firsthand how big SQL is, and you’ve seen the complexity of the models behind it. Yet SQLite is a small library and manages to put many of these concepts to work in a small amount of code and excels!
Grant Allen, Mike Owens
Backmatter
Metadaten
Titel
The Definitive Guide to SQLite
verfasst von
Grant Allen
Mike Owens
Copyright-Jahr
2010
Verlag
Apress
Electronic ISBN
978-1-4302-3226-1
Print ISBN
978-1-4302-3225-4
DOI
https://doi.org/10.1007/978-1-4302-3226-1

Premium Partner

    Bildnachweise