I have made a custom report generator for our database (Oracle Berkeley DB engine). Now it's time for me to add more flexibility and I am in dilemma. Do a partial or a fundamental redesign? Lets assume that I have plenty of time. I can only read the database, I don't have the right to modify it. Inspired from Query Anything with SQLi...
I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist. This Stackoverflow entry suggests doing the select SELECT sql FROM sqlite_master WHERE tbl_name = 'table_name' AND type = 'table' and parse the result. Is that the common way? Alternatives...
I'm building a data loader utility application (Win 7 64bit, VS 2008, C#, .Net 3.5, Win Forms) using Subsonic 2.2. I've gotten everything working using SqlServer and now I need to get it working in Sqlite 3. I've worked through some issues but there is one I cannot resolve; I have 2 columns, Latitude and Longitude that are FLOAT data ...
I have a normalized database and need to produce web based reports frequently that involve joins across multiple tables. These queries are taking too long, so I'd like to keep the results computed so that I can load pages quickly. There are frequent updates to the tables I am summarising, and I need the summary to reflect all update so f...
I am building some Postgres tables from Python dictionaries where the {'key': 'value'} pairs correspond to column 'key' and field 'value'. These are generated from .dbf files -- I now pipe the contents of the .dbf files into a script that returns a list of dicts like: {'Warngentyp': '', 'Lon': '-81.67170', 'Zwatch_war': '0', 'State':......
I am writing a PHP script that runs on a cron and pulls JSON data from an API [ title (text), path (text), visitors (integer) ] and stores it in a SQLite database. Every time it runs, if it sees an existing title, it should add the new visitors count to the existing visitors. If not, it should add the new data as a new row. Here's a sim...
I know that SQLite does not enforce foreign keys natively, but that's not my primary concern. The question is: If I declare CREATE TABLE invoice ( invoiceID INTEGER PRIMARY KEY, clientID INTEGER REFERENCES client(clientID), ... ) will sqlite at least use the information that clientID is a foreign key to optimize queries and au...
I would like to have a column in my table that can store a variable amount of int values. What is the best way to do this? I would like to be able to select based on these ints so a csv list would not work. Basically I have a bunch of rows in my table that can belong to multiple different categories. I would like to store the category i...
Just wondering if it is possible to convert sql-server *.mdf file into sqlite file ? ...
I'm working in Microsoft Visual C# 2008 Express and with SQLite. I'm querying my database with something like this: SQLiteCommand cmd = new SQLiteCommand(conn); cmd.CommandText = "select id from myTable where word = '" + word + "';"; cmd.CommandType = CommandType.Text; SQLiteDataReader reader = cmd.ExecuteReader(); Then I do somethi...
I've got a sqlite table actions that looks something like this: uuid varchar (36) actiondate int username varchar (16) mood int bonus int status varchar (80) ... bunch of other similar fields (all short varchar or int fields) This design seems to be sufficiently performant for most types of queries, but struggles a bit with a particul...
Hi, A bit confused, I was watching some video where some guy was using sqllite as an in-memory database. From the sqllite.org site it seems as though it is a real db? Can it be both or is the in-memory db that I saw something else? (used with NUnit). ...
Is there any tool or script available? ...
I have these tables: media table - id int primary key, uri varchar. media_to_people - media_id int primary key, people_id int primary key people - id int primary key, name varchar, role int -- role specifies whether the person is an artist, publisher, writer, actor, etc relative to the media and has range(1-10) This is a many to many r...
Is there any way of getting the SQLite DB off my iPhone so I can view the data in it? I've managed to get the DB on my iPhone into an odd state, and rather than keep trying to replicate in simulator, I'd like to investigate the state of the DB from my dev install. Is this possible? ...
I have a sqlite3 db which i insert/select from in python. The app works great but i want to tweak it so no one can read from the DB without a password. How can i do this in python? note i have no idea where to start. ...
Hi All, I was wondering if there are any automated tools for displaying a database schema diagrammatically? My artistic side fails me and it would be nice to open my discussion with a diagram which doesn't scream ugly. Many thanks, Gav ...
I am using sqlite for my iphone and I anticipate the database schema might change over time. What are the gotchas, naming conventions and things to watch out for to do a successful migration each time? For example, I have thought of appending a version to the database name (e.g. Database_v1). ...
I have a purely academic question about SQLite databases. I am using SQLite.net to use a database in my WinForm project, and as I was setting up a new table, I got to thinking about the maximum values of an ID column. I use the IDENTITY for my [ID] column, which according to SQLite.net DataType Mappings, is equivalent to DbType.Int64. ...
I've the following DB structure in SQLite: I want to create a trigger that whenever I delete a country all the related districts, municipalities and parishes are also deleted (like MySQL InnoDB), I've tried using SQLite triggers and came up with this: Districts: CREATE TRIGGER [delete_country] BEFORE DELETE ON [countries] FOR EACH R...