[sqlite] Is Column UNIQUE? How To
Hi,
Is any possible way to know if a column is UNIQUE without PRAGMA
index_info('index_name')? Seems PRAGMA table_info('table_name')
doesn't returns the unique flag of column.
Thanks for advice.
--
Firman Wandayandi http://firmanw.org/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Is Column UNIQUE? How To
Hi Jay,
On 12/17/06, Jay Sprenkle [EMAIL PROTECTED] wrote:
On 12/16/06, Firman Wandayandi [EMAIL PROTECTED] wrote:
Hi,
Is any possible way to know if a column is UNIQUE without PRAGMA
index_info('index_name')? Seems PRAGMA table_info('table_name')
doesn't returns the unique flag of column.
Thanks for advice.
this will show you the count of duplicated values for a column:
select mycol, count(*)
from mytable
group by mycol
I meant, I want retrieve the information of the column or schema as
much as possible, so I can build a new schema based on it. Then I need
to know if column is a UNIQUE or not and so on. Such as:
mycol INTEGER NOT NULL UNIQUE
There is another idea, that is parse the sql column from
sqlite_master table. But I'm searching for a right solution.
--
Firman Wandayandi http://firmanw.org/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Is Column UNIQUE? How To
On 12/17/06, Jay Sprenkle [EMAIL PROTECTED] wrote: On 12/16/06, Firman Wandayandi [EMAIL PROTECTED] wrote: I meant, I want retrieve the information of the column or schema as much as possible, so I can build a new schema based on it. Then I need to know if column is a UNIQUE or not and so on. select * from sqlite_master; It tells you about the schema Yeah, as I thought before. Well nevermind, I should parse the table schema then. Thanks -- Firman Wandayandi http://firmanw.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] List of web hosting providers who provide/support SQLite?
On 8/26/06, Louis P. Santillan [EMAIL PROTECTED] wrote: This is probably a good topic for a wiki page... Does anybody have a list (actually a matrix would be even better) of web hosting providers who have SQLite available to their customers? I decided to look for some cheap web hosting service for a personal project using LASP (Linux, Apache, SQLite, PHP) and realized a list of providers would probably be ideal. A matrix of common features would be good also (Windows, Linux, Apache, IIS, PHP, Perl, Python, Price, etc.) I guess for start just find the hosting companies who provide PHP5, because it has bundled SQLite 2.1.17 on it (expect they turn off it). Better if they provides PDO. But I develop my apps with SQLite2 backend, it's enough for me, since PDO is a PECL stuff. It's not so hard to find the PHP5 hosting companies i think, except the local companies in my country :-( -- Firman Wandayandi http://firmanw.dotgeek.org/ Diskusi Praktisi Web: PHP http://groups.google.com/group/php-indo, http://groups.yahoo.com/group/id-php JavaScript http://groups.google.com/group/js-id Teknologi Web http://www.diskusiweb.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Open sqb database in php?
On 7/6/06, Albert van Bergen [EMAIL PROTECTED] wrote: Hi All, I pretty new into sqlite. Is it possible to open a sqb database in php? Use PDO_SQLITE for sqlite2 and sqlite3 PHP5 only, or use SQLite extension for sqlite2. SQLite extension already built-in in PHP5, for PHP4 you must load the php_sqlite extension first. When I use sqlite_open I do get the following warning: Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database in C:\Program Files\Apache Group\Apache2\htdocs\sql.php on line 2 file is encrypted or is not a database I suspect if your file is sqlite database, but it's a sqlite file format version 3 and you tried to open it with sqlite_open() which is support only sqlite file format version3. You should use PDO_SQLITE for open it. Also notes PDO currently support sqlite file format version 3.2, no update yet. So be carefull with your database or your data will lost. -- Firman Wandayandi http://firman.dotgeek.org/
Re: [sqlite] Database locks up on AMD64/PHP
On 3/7/06, Stefan de Konink [EMAIL PROTECTED] wrote: On Tue, 7 Mar 2006, Firman Wandayandi wrote: On 3/7/06, Stefan de Konink [EMAIL PROTECTED] wrote: Hello, We are running SQLite 2.8.16 together with PHP5 on Linux. And it runs our TV Newspaper System. It was running on a PIII-1GHz but due some shifts for rendering performance increase (Inkscape SVG - PNG) we moved the complete application to an AMD64. The last weeks it seems the database got locked very often, and made the apache/php system 'hang', not on query but on insert. Restarting Apache solves the problem making me wonder what I can do to visualize the problem or even fix it. As temporary fix I already tried to decrease the database size (now at 7MB). But this only increase speed (for now). What's this meant? What part isn't clear? Nothing, it's just like annoucement. Well, sorry I didn't read carefully the second paragraph. I have no idea it could be happened, seems something has vacummed your databases, well not sure. -- Firman Wandayandi, Tarzilla Foundation. Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] Database locks up on AMD64/PHP
On 3/7/06, Stefan de Konink [EMAIL PROTECTED] wrote: Hello, We are running SQLite 2.8.16 together with PHP5 on Linux. And it runs our TV Newspaper System. It was running on a PIII-1GHz but due some shifts for rendering performance increase (Inkscape SVG - PNG) we moved the complete application to an AMD64. The last weeks it seems the database got locked very often, and made the apache/php system 'hang', not on query but on insert. Restarting Apache solves the problem making me wonder what I can do to visualize the problem or even fix it. As temporary fix I already tried to decrease the database size (now at 7MB). But this only increase speed (for now). What's this meant? -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] DEFAULT values replace explicit NULLs?
On 3/4/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Is the DEFAULT value for a column suppose to replace an explicit NULL value? Or does the DEFAULT value only get used if no values for an insert is specified? What is the correct SQL behavior? SQLite does the latter - the DEFAULT value is only used if no value is given for the column. If you insert an explicit NULL value then a NULL value is inserted instead of the DEFAULT value. Ticket #1705 says this is incorrect. Which is right? The current SQLite implementation or ticket #1705? IMO the NOTNULL keyword should be a clue for this, if the column has DEFAULT value and NOTNULL flag that should be inserted of DEFAULT value if no value is given, if column has no NOTNULL and has DEFAULT value that should be DEFAULT value is inserted otherwise NULL value is inserted. If no value given into the NOTNULL column that should be raised the syntax error. -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] Oracle's SQLite???
On 3/1/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: As ssen at http://www.linuxdevices.com/news/NS3880195342.html The Acme Fox also supports PHP development, optionally with Oracle's open source SQLite database. Amusing, but not true. Heh, Oracle's SQLite? crap -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] OT: Donations?
On 2/27/06, Chethana, Rao (IE10) [EMAIL PROTECTED] wrote: Hello! That's a good idea! I think u can post ur question to www.sqlite.org see. They'll b able to tell u. Post to sqlite.org? why not here? that's nice to get some donations. -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] File format changed !!
On 2/28/06, Pavan Savoy [EMAIL PROTECTED] wrote: I am trying to open a database created in SQLite 3.3.1 in SQlite 3.1.2 and I get the following err when I try to see the sqlite_master table .. SQL error: unsupported file format I suppose the file format has been changed over the versions, but the reverse does work, i.e db created on 3.1.2 can be opened on 3.3.1. I just wanted some sort of document which suggests changes in file format over the versions, changes page doesnt represent the full picture though http://sqlite.org/formatchng.html Thank you -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] OT: Donations?
On 2/28/06, Chethana, Rao (IE10) [EMAIL PROTECTED] wrote: Hi! Did u enquire about donations to sqlite.org ? what reply u got? No didn't. And I dunno exactly about donations, you need to wait the sqlite team or you can ask to D. Ricard Hipp for that. -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] using sqlite3 in php
On 2/20/06, Lloyd Thomas [EMAIL PROTECTED] wrote: sqlite3 is available in 5.1 using the pdo extensions. have a look at www.php.net/pdo I had the same problem with an app I wrote in delphi to insert records and used php front end to select records The lastest SQLite library on PDO is 3.2.8, the file format of SQLite 3.3.0 has been changed, take a look http://www.sqlite.org/formatchng.html for detail. Lloyd -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] SQLite 3 file format changed?
On 2/20/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Version 3.3.0, 4th bullet: More efficient coding of boolean values resulting in a smaller database file -- D. Richard Hipp [EMAIL PROTECTED] Oh that's it! But seems doesn't like file format log changed :-) On 2/20/06, Jens Miltner [EMAIL PROTECTED] wrote: 3.3 changed the database format, but can read/write any 3.x format. However, once you create or vacuum a database with 3.3.x, the format is updated to the newest format and older implementations can't read it any more. See http://sqlite.org/formatchng.html for details I just found it. Nice 3.3 can read/write 3.x, but no PDO_SQLITE updated yet ATM, the latest is 3.2.8, so the problem is PDO_SQLITE then I need to wait someone updated it. Thanks for the infos. -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
[sqlite] SQLite 3 file format changed?
Hi, I use PHP PDO_SQLITE extension with SQLite 3.2.8 library, I try to open a database version 3 which I use SQLiteSpy 1.5.2 with SQLite 3.3.4 library, then PDO said error message like unsupported file format. I tried to use sqlite3 console which is version 3.2.6 for open it, same error message given. I suspected there was file format has been changed again, but I can't found any log on http://sqlite.org/changes.html told about that. What's wrong exactly? -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] :memory: and sessions with PHP
On 2/14/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: CrazyChris [EMAIL PROTECTED] writes: Hi there, I have a need to create a :memory: sqlite database, but save it into the user session (PHP) but can't see a way to access the data to save. Looking for a sqlite version of serialize() I guess. Has anyone managed to do this? Is it even possible? Wanting to be able to maintain a large chunk of data across a users session on a website, and the array's are getting tedious to manage and search through! The PHP session information has to be persistent, so it's not going to be easy to use a :memory: database. There is lots of information about how to save session information to a database, though, on the PHP web site. I haven't looked at it in a couple of years, but I'd guess that you'll get some good pointers if you look at the documentation for session_set_save_handler(). Also, IIRC, PHP provides functions to do serialization. You won't need them if you go the session_set_save_handler() route, but if you want to serialize data yourself, those functions should be available. Derrell I think Derrel is right. If you use a :memory: database, maybe you succeed on first page, but I guarrante your session will be destroyed on the other page, why? cause you create a brand new :memory: database. In order if you want to use sqlite session as save handler you can use it on php5, or you can create your own session rules. Take a look a php session documentation for it. -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] Book recommendation
On 11/10/05, Joe Halpin [EMAIL PROTECTED] wrote: I'm looking for a good tutorial (online or paper) to help me get the concepts behind using the C/C++ API. Could anyone recommend the book by Chris Newman SQLite: A practical guide to using, administering and programming the database bundled with PHP5? I'm looking primarily to use the API rather than the command line tool. Thanks Joe Any links for it? -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] Schemas
On 9/28/05, Vivien Malerba [EMAIL PROTECTED] wrote:
2005/9/28, Firman Wandayandi [EMAIL PROTECTED]:
On 9/27/05, Vivien Malerba [EMAIL PROTECTED] wrote:
Hi!
I'm working on the SQLite interface between the Libgda library and the
SQLite database. For the job, I need to know information about the
database schema, and I'm using the PRAGMA statements regarding
schemas.
However I can't find the information about the AUTO INCREMENT
attribute of a column. Can anyone help me?
here they are,
http://sqlite.org/datatypes.html
http://sqlite.org/datatype3.html
I don't mean finding information about how to create AUTO INCREMENT
fields but about how to get that information for each field of an
already existing table in the database (like when using PRAGMA
table_info('table')).
Oh, sorry, you can use
SELECT * FROM sqlite_master
and find yourself where the auto increment column form sql column
with AUTOINCREMENT as the wild card, I dunno another solution.
Thanks,
Vivien
Hope help you,
--
Firman Wandayandi
Never Dreamt Before (http://php.hm/~firman/)
Re: [sqlite] Schemas
On 9/27/05, Vivien Malerba [EMAIL PROTECTED] wrote: Hi! I'm working on the SQLite interface between the Libgda library and the SQLite database. For the job, I need to know information about the database schema, and I'm using the PRAGMA statements regarding schemas. However I can't find the information about the AUTO INCREMENT attribute of a column. Can anyone help me? here they are, http://sqlite.org/datatypes.html http://sqlite.org/datatype3.html Thanks a lot, Vivien -- Firman Wandayandi Never Dreamt Before (http://php.hm/~firman/)
[sqlite] Supported data types
Can anyone describe me, more detail list of which are data types currently supported by SQLite, on both http://sqlite.org/datatypes.html http://sqlite.org/datatype3.html had gave me some or maybe all (not sure), because when I create a table with a SMALLINT column, seems SQLite is ok. So I figured out there are more data types supported? please give me a list of it. Regards, -- Firman Wandayandi Never Dreamt Before (http://php.hm/~firman/)
Re: [sqlite] Data Manipulation ?
On 9/26/05, Richard Nagle [EMAIL PROTECTED] wrote: Does SQLite support any direct built-in commands, that can manipulate data? ie. suppose the boss wanted a report of cars honda in CA, but he wants to see the California instead of CA, so you need to convert all the state fields into full names, how would this be done... There's no built-in function for this cases, you need something like replace(), see http://sqlite.org/lang_expr.html for the built-in functions. You need the aggregate functions. I use SQLite with PHP, so it's easy to create the aggregate functions. Fields are as follows: recid qty make model year engine color state price Thanks - Richard Regards, -- Firman Wandayandi Never Dreamt Before (http://php.hm/~firman/)
Re: [sqlite] SQLITE Documentation
On 9/12/05, ShepherdHill DB Subscriptions [EMAIL PROTECTED] wrote: Hi, Can anyone point me to how I can get the full download of the SQLITE documentation in html? I will like to compile and maintain the CHM version of the document so that users can reference the documentation offline. Dunno, I never found it. Might be the SQLite official team have it. Currently I downloaded by myself all documentation, faqs and syntax pages from sqlite.org, then fix the links and now it available online on my box. Best regards. Chris. -- Firman Wandayandi Never Dreamt Before (http://php.hm/~firman/)
[sqlite] SQLite3 backward compatibility
Does SQLite3 compatible with SQLite2 databases? If yes, so I can manage the SQLite2 database using SQLite3 engine. -- Firman Wandayandi Never Dreamt Before (http://php.hm/~firman/)
Re: [sqlite] SQLite3 backward compatibility
On 8/25/05, Will Leshner [EMAIL PROTECTED] wrote: On Aug 24, 2005, at 7:41 PM, Firman Wandayandi wrote: Does SQLite3 compatible with SQLite2 databases? If yes, so I can manage the SQLite2 database using SQLite3 engine. No, and no. Ah, yes, I see. That's the reason why i can't open SQLite2 databases using SQLite3. Thanks, -- Firman Wandayandi Never Dreamt Before (http://php.hm/~firman/)
Re: [sqlite] Another SQLite documentation/manual
On 7/5/05, Roger Binns [EMAIL PROTECTED] wrote: Anything about sqlite_master table information I guess, or maybe I missed it? http://www.sqlite.org/faq.html#q9 Ok, I missed the faqs. Thanks. I prefer using the pragmas: http://www.sqlite.org/pragma.html#schema Roger -- :: Never Dreamt Before :: http://php.hm/~firman/ firman(@)php.net | firmanw(@)gmail.com
[sqlite] Another SQLite documentation/manual
Hi Guys. Any SQLite documentation/manual out there? There's not enough with the official documentation for me, I need more docs for exploring SQLite. Thanks, -- :: Never Dreamt Before :: http://php.hm/~firman/ firman(@)php.net | firmanw(@)gmail.com
Re: [sqlite] Another SQLite documentation/manual
On 7/5/05, Roger Binns [EMAIL PROTECTED] wrote: Any SQLite documentation/manual out there? http://www.sqlite.org/docs.html http://www.sqlite.org/cvstrac/wiki That's the official docs one. There's not enough with the official documentation for me, I need more docs for exploring SQLite. What specifically is missing? Anything about sqlite_master table information I guess, or maybe I missed it? Roger Regards, -- :: Never Dreamt Before :: http://php.hm/~firman/ firman(@)php.net | firmanw(@)gmail.com
