Re: [sqlite] controlling changes
You can do it as a constraint. -Original Message- From: David Bicking Sent: Saturday, October 10, 2009 9:08 AM To: sqlite-users@sqlite.org Subject: [sqlite] controlling changes I have a table: CREATE TABLE Assets ( ControlDate Date , Amt ) Now, the business rules are you can INCREASE the Amt if the Current Date is the ControlDate in the record. You can DECREASE the amount if the Current Date is greater than or equal to the ControlDate. Can this be enforced via a trigger, or must I enforce that business rule at the application layer? Thanks, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there an easy way to concatenate 2 views ?
Yw, anytime. -Original Message- From: Stef Mientki Sent: Saturday, July 25, 2009 4:16 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is there an easy way to concatenate 2 views ? thanks Woody ( Harold ?), that works perfect. cheers, Stef Harold Wood & Meyuni Gani wrote: > Concantenate or join? > > To concantenate use the > Select * from a > Union all > Select * from b > > Only use * if columns all columns in a are identical to all columns in b, if > not then build a column list and use that indtead of * > > Woody > > -Original Message- > From: Stef Mientki > Sent: Saturday, July 25, 2009 3:19 PM > To: SQLite List > Subject: [sqlite] Is there an easy way to concatenate 2 views ? > > hello, > > I've 2 views ( or maybe tables) with same columns, > is there an easy way to concatenate those 2 views to 1 new view ? > > thanks, > Stef Mientki > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there an easy way to concatenate 2 views ?
Concantenate or join? To concantenate use the Select * from a Union all Select * from b Only use * if columns all columns in a are identical to all columns in b, if not then build a column list and use that indtead of * Woody -Original Message- From: Stef Mientki Sent: Saturday, July 25, 2009 3:19 PM To: SQLite List Subject: [sqlite] Is there an easy way to concatenate 2 views ? hello, I've 2 views ( or maybe tables) with same columns, is there an easy way to concatenate those 2 views to 1 new view ? thanks, Stef Mientki ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database logic in TRIGGER and CONFLICT, or in software ?
Very well said. Harold Wood & Meyuni Gani -Original Message- From: Jay A. Kreibich Sent: Sunday, June 14, 2009 8:44 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database logic in TRIGGER and CONFLICT, or in software ? On Sat, Jun 13, 2009 at 11:42:21PM +0100, Simon Slavin scratched on the wall: > Do any of you have experience with doing this ? Are there some > conclusive points which will make me decide immediately that I should > do it one way or the other ? I accept reasoned argument, URLs, > anecdotes, or anything else relevant. Every system that uses a database must deal with this problem, and there are no real easy answers. But here are a few deeper points to consider. From the view point of Relational Model, the formal mathematical model that most RDBMS engines are based off (in theory, anyways), you should push as many constraints, rules, and general enforcement into the database as possible. The database is meant to represent "truth" and under the Model it should be impossible (or at least as difficult as possible) to make the database become inconsistent-- that is, be in a state that doesn't represent truth. There are two main issues with this. First, an modern SQL database is not a Relational Model database. SQL only offers a number of different constraints, but it is not as flexible or complete as the theoretical model presented by the Relational Model. Second, the Relational Model doesn't account for access methods or the concept of middle-ware or tiered applications. It is assumed that anyone and everything from applications to command-line systems will be accessing the database. If you have a user typing in raw SQL commands and you want your database to remain consistent, you have no choice but to push as much as possible into the database and do lots of audits. Thankfully, most real-world applications do not work this way. You provide some piece of software that accesses the database, and (in general) the only people that have command line access are DBAs and other admin types. In that case, you can relax some of the constraints or rules that are difficult to express in the database and move those out to the application. This can be good or bad. In the case of an old-school mainframe application, where you logged into a central system via your VT100 or TN3270 terminal and ran the application on the mainframe, the software was centralized and easy to maintain. A system-wide application upgrade consists of replacing one binary. This made it relatively safe to push rules and constraints into the application, as it was easy to keep the end-user application and the database -- including schema changes and format updates -- in sync. After all, both the DB and the application were sitting on one machine. Then came the era of desktop systems. Now it was common to have a GUI desktop application that was used to access and manipulate the database. The problem is, it is nearly impossible to keep every desktop system in perfect sync. If a schema change required an update to a query, that required a new version of the desktop application, which required a network-wide upgrade. Really fast, you learned to either make your upgrades backwards compatible OR you had an extremely simply automatic update system. Regardless, there were dangers associated with pushing too many high-level rules into the application. If someone managed to access the database with an older client that had a different set of rules, bad things could happen. To get around these problems people learned to push more and more logic into the database, including complex stored functions and procedures to do just about any update or adjustment and views for all but the most basic filtering of a query. This would allow for schema changes without client updates. As the web gained popularity, along with middle-ware and multi-tier architectures, we returned more or less to the model of "everything on one machine" or, at least, under one control. If the application is completely web driven, the problem of upgrading the SQL access application (e.g. the web server code) and the database are greatly reduced compared to the desktop model, allowing more and more logic to return to the code. So much of the answer to your question depends on the environment you're trying to support and how much control you have over all the bits of code that have direct access to the database. If you control the database AND the next tier down, pushing logic, rules, and constraints into that layer has fewer issues and ramifications than keeping them in the database. From a pure software engineering standpoint, the best solution is to keep the rules and constraints as close to the data as possible. Just
Re: [sqlite] how can we solve IF EXIST in SQLite
Hmm, I have a view, its strictly a bunch of bit columns. Default value is 0=false, this view has a huge trigger on it. I use the different columns to activate particular sections of the trigger code, within those I do inserts, deletes, updates etc. It was a design around not having stored procedures. Harold Wood & Meyuni Gani -Original Message- From: BareFeet Sent: Wednesday, June 03, 2009 6:21 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] how can we solve IF EXIST in SQLite Hi Harold, > SQL does have branching logic. > > (SELECT CASE > WHEN ((SELECT StoreId From History WHERE ItemId = NEW.ID LIMIT > 1) IS NULL) > THEN > 0 > ELSE > (SELECT StoreId FROM History WHERE ItemId = NEW.ID AND > UnitPrice = (SELECT MIN(UnitPrice) FROM HISTORY WHERE ItemId = > NEW.ID)) > END); > > i use it in my current project. Let me clarify. By "branching logic" I mean branching (eg if/then or loop) to perform an action such as update, insert, delete, create etc. The case/when/then construct is a function, not procedural branching (at least by my definition above). It will return different results depending on the test, but it can't be used to perform different actions based on the test. > you could modify this to meet the goal of insert x or update y. No, that won't work. You can't put an action (such as an update or an insert) inside a case statement. You can only put expressions (including select statements) within a case statement. Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting last inserted rowid?
If you are inserting in order then selecting the max value from an indexed column should work. Harold Wood -Original Message- From: Nikolaus Rath Sent: Wednesday, June 03, 2009 3:22 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Getting last inserted rowid? Nuno Lucas writes: > On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote: >> Nuno Lucas writes: >>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote: Hello, How can I determine the rowid of the last insert if I am accessing the db from different threads? If I understand correctly, last_insert_rowid() won't work reliably in this case. >>> >>> It should work if you do: >>> >>> BEGIN >>> INSERT ... >>> last_insert_rowid() >>> END >> >> That would be very nice. But does "it should work" mean that you know >> that it works (and it is documented and guaranteed)? The above sounds a >> bit uncertain to me... > > It just means I'm too old to assume anything is certain. The Universe > is always conspiring against you ;-) > > What I mean is that if it doesn't work, then you found a bug, most > probably in your own code. Well, now you are in direct contradiction to Igor who says that it does not work: , | >> If all threads share the same connection, it is your responsibility | >> to make "insert then retrieve last rowid" an atomic operation, using | >> thread synchronization mechanism of your choice. Just as with any | >> access to shared data. | > | > Is BEGIN ... COMMIT sufficient for that? | | No. Transaction is also maintained per connection. Starting a | transaction would prevent other connections from making concurrent | changes, but wouldn't block other threads using the same connection. ` Any third opinions or references to documentation? Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Precompiled SQLite Binaries for WinCE
newobjects Woody from his pda -Original Message- From: Roger Binns <[EMAIL PROTECTED]> Sent: Sunday, August 17, 2008 2:00 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Precompiled SQLite Binaries for WinCE -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: > I was looking for precompiled SQLite binaries for WinCE on the download page, Why do you need the binary? If you use the amalgamation (a single source file which includes the WinCE support) then you can just add that source file and header to your project. That way you'll get the right CPU and WinCE version for your binaries. I actually #include the amalgamation into my source file that interfaces with SQLite so it doesn't even mean I have to worry about linking! Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIp+iXmOOfHg372QQRAuxHAKCiQ7ImTNxcSsHcLhRePmvkYpvhIwCfeavB ZcS9eECrcuted0i/XpKAmwM= =uu57 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite support stored procedure?
Can you send examples? Thanks Woody from his pda -Original Message- From: John Stanton <[EMAIL PROTECTED]> Sent: Tuesday, July 29, 2008 11:39 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Does sqlite support stored procedure? Adding Javascript to Sqlite as a stored procedure language was a fairly simple operation. Try it if you need stored procedures. BareFeet wrote: > Hi John, > > >>I would like to know if SQLite supports stored procedures. > > > Technically, no it doesn't. > > For what purpose do you want to store procedures? > > You can store some procedures in triggers, if you want to have SQLite > trigger a task when some data is changed. > > You can simply create a "Procedures" table like this: > > create table "Procedures" (Name, SQL); > > and populate it with SQL procedures. You can call those procedures > later from within your program and sqlite3 command line and execute > them. > > Tom > BareFeet > > -- > Comparison of SQLite GUI applications: > http://www.tandb.com.au/sqlite/compare/ > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] text datatype matching functions, binding vs explicit insert
But you can use where 'AA' = upper(fld1) = to get around case issues. Woody from his pda -Original Message- From: D. Richard Hipp <[EMAIL PROTECTED]> Sent: Friday, July 25, 2008 3:11 PM To: [EMAIL PROTECTED]; General Discussion of SQLite Database Subject: Re: [sqlite] text datatype matching functions, binding vs explicit insert On Jul 25, 2008, at 8:50 AM, Chris Holbrook wrote: > I created a table with four text columns and a blob column, and > populated it using sqlite3 functions ~prepare, ~bind_text, > ~bind_blob. Now I can't query the data using, for example, "where > app = 'AA'", though "where app like 'AA'" works! The LENGTH() > function returns the number of characters which one would expect. > > If I insert data in the same table using explicit SQL, I can use "=" > with success. > The = operater is case sensitive. LIKE is not. 'aa' LIKE 'AA' is true but 'aa'='AA' is false. The previous paragraph is true by default. There are ways of changing the default. For example, if you declare a column to be COLLATE NOCASE then it will not be case sensitive (for US-ASCII characters). And there is a PRAGMA that will make LIKE case sensitive. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Convert the CURRENT_TIMESTAMP
Look at the wiki for date/time functions, specifically strft. Woody from his pda -Original Message- From: Joanne Pham <[EMAIL PROTECTED]> Sent: Friday, July 25, 2008 4:08 PM To: General Discussion of SQLite Database ; [EMAIL PROTECTED] Subject: [sqlite] Convert the CURRENT_TIMESTAMP Hi All, I ran the following sql statement select CURRENT_TIMESTAMP; and the output is : 2008-07-25 23:11:13 Is there any easy way or buildin function to convert this format to : TUE JULY 25 23:11:13 2008 Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
Lol. Thanks. If you want a schema I can attach and send to you. Woody from his pda -Original Message- From: flakpit <[EMAIL PROTECTED]> Sent: Tuesday, July 01, 2008 11:30 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multiple constraints per table? Harold Wood & Meyuni Gani wrote: > > U, hmm. The tips I gave you were from my pda based shopping program > that will be selling for 9.99 soon. > Good on you Woody, hope you sell a lot.! If my eyes were up to the challenge of reading my PDA's small screen, i'd buy a copy and save myself work (LOL!) With the amount of junk I've churned out of the years, i've yet to get anyone to buy anything yet (ROFL). Actually, not quite true. One registration out of 6,500 downloads of my address book. -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18231100.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple constraints per table?
U, hmm. The tips I gave you were from my pda based shopping program that will be selling for 9.99 soon. Its 6 for one, half dozen for the other. You can design the db so it does the work for you or you code the program to do the work for you. Either way, you will get things to work, it just depends upon how you want to partition your code. Woody from his pda -Original Message- From: flakpit <[EMAIL PROTECTED]> Sent: Tuesday, July 01, 2008 11:00 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multiple constraints per table? I understand your solution Woody, but it isn't practical for me, not for my home shopping list program. A full on relational database like that is an awful lot of work and it's only for home use (and any other family I can con into using it and testing it). I'd go for the relational route if I were designing an enterprise wide product, it's only sensible (as you intimated) but Igor's solution (that I also found independantly) will work fine for a small system for now. Thanks for the ideas, will keep proper design in mind if I ever get talked into doing something for a company (something I try mightily to avoid, believe me!!!) -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18230807.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] design question / discussion
I've done an app like that before with a different db foundation. Basically 2 different databases, same structure. The logging app hits an ini file before each write, if the current db is different than the name in the ini file then close the current db, open the new db and write the row to the new db, otherwise write the row to the current db. I had background app that ran as a service and would switch the fb name in the ini file when one hour had passed or the db was full. It worked great . Woody from his pda -Original Message- From: A.J.Millan <[EMAIL PROTECTED]> Sent: Wednesday, May 21, 2008 2:29 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] design question / discussion Rich Rattanni wrote:>Hi I have a general design question. I have the following senario... >In an embedded system running linux 2.6.2x I have a sqlite database >constantly being updated with data acquired by the system. I cant >lose data (hence why I am using sqlite in the first place). However >periodically I have download the data contain within the database to a >central server. The system cannot stall during the download and must >continue to record data. Also, after the download I need to shrink >the database size, simply because if the database is allowed to grow >to its max size (~50MB) then every download thereafter would be 50MB, >which is unacceptable. After thinking in your's problem, according to yours first exposition, it seems that you are using the SQLite dbase as a mere tampon or temporarybuffer to the acquired data. In that condition, with no further process of those data in the embedded system, perhaps you can consider simply write a flat file appending to it the incoming data (may be alternating between two or more files) and then compress and send the data to the host where they can be further processed or appended to a dbase. >From the security point of view, the data in the embedded device are not necesarily safer in a SQLite dbase that in a flat file. Perhaps that layer(SQLite) are not necessary at all in the embedded device. Just thinking out loud :-) Adolfo. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
Doesn't sqlite support the truncate table command Woody from his pda -Original Message- From: Carlo S. Marcelo <[EMAIL PROTECTED]> Sent: Tuesday, May 20, 2008 8:49 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] deleting 100,000 entries @Barefoot and Keith, Awesome! It took forever for the web interface to delete, and I had to restart httpd for the database to unlock.. the command you provided took less than a second to clear everything out, thanks! Carlo - Original Message From: BareFeet <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Wednesday, May 21, 2008 11:45:06 AM Subject: Re: [sqlite] deleting 100,000 entries Hi Carlo, > I want to clear the whole thing out(list)! Do you mean that you want to delete all rows from the "list" table? If so, do this: delete from list; See the syntax pages at: http://www.sqlite.org/lang.html http://www.sqlite.org/lang_delete.html Tom BareFeet http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE WHEN sample ?
Better would be
Insert into tablea(ida, value1a)
Select idb, value1b
from tableb
where idb not in(select ida from tablea);
Woody
from his pda
-Original Message-
From: Lothar Behrens <[EMAIL PROTECTED]>
Sent: Saturday, May 10, 2008 2:23 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] CASE WHEN sample ?
Hi,
I am searching for a sample that uses the case when expression.
I like to insert values into a table when these values are not in that
table before.
My tries to read, understand and try the documentation of expressions
failed.
Executing this statemen twice creates two rows:
replace into anwendungen (name) values ('lbDMF Manager')
This is pseudo code:
if not exsists (select id from mytable where name = 'some name')
insert into mytable (name) values ('some name')
Is this possible ?
Thanks
Lothar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
